今天试了JDBC的数据查询和插入,发现奇怪问题,请各路高手帮忙解决。
1)先查询,再插入
查询成功,插入失败
ResultSet rs = stmtNew.executeQuery("select * from student");
while (rs.next()) {
int mystr1 = rs.getInt("number");
String mystr2 = rs.getString("name");
}String sqlStr =
"insert into student values(998,'helloworld')";
if(!stmtNew.execute(sqlStr))
System.out.println("error");
2)先插入,再查询
插入成功,且查询成功
String sqlStr =
"insert into student values(998,'helloworld')";
if(!stmtNew.execute(sqlStr))
System.out.println("error");ResultSet rs = stmtNew.executeQuery("select * from student");
while (rs.next()) {
int mystr1 = rs.getInt("number");
String mystr2 = rs.getString("name");
1)先查询,再插入
查询成功,插入失败
ResultSet rs = stmtNew.executeQuery("select * from student");
while (rs.next()) {
int mystr1 = rs.getInt("number");
String mystr2 = rs.getString("name");
}String sqlStr =
"insert into student values(998,'helloworld')";
if(!stmtNew.execute(sqlStr))
System.out.println("error");
2)先插入,再查询
插入成功,且查询成功
String sqlStr =
"insert into student values(998,'helloworld')";
if(!stmtNew.execute(sqlStr))
System.out.println("error");ResultSet rs = stmtNew.executeQuery("select * from student");
while (rs.next()) {
int mystr1 = rs.getInt("number");
String mystr2 = rs.getString("name");
你最好还是查询的时候用executeQuery(),然后插入的时候用executeUpdate()
再就是请检查一下开发环境或数据库的配置有没有问题/*数据库连接模块*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.swing.JOptionPane;
import com.mysql.jdbc.Driver;
public class ConnectBean { private Connection dbConnect = null;
private ResultSet result = null;
private PreparedStatement ps = null; // 数据库批处理执行对象
private String driver = "com.mysql.jdbc.Driver";
private String url =
"jdbc:mysql://localhost/error?useUnicode=true&characterEncoding=gb2312";
private String user = "root";
private static String password = null; public ConnectBean() {
// System.out.println("创建ConnectionBean成功");
} public boolean openConnection() {
// 打开数据库连接
// System.out.println("Open Connection...");
if (password==null) {
password=JOptionPane.showInputDialog(null,"请输入MySQL数据库的登录密码:");
}
try {
DriverManager.registerDriver(new Driver()); } catch (SQLException e) {
System.out.println("打开数据库连接错误:" + e.getMessage());
return false;
}
try {
if (dbConnect == null)
this.dbConnect = DriverManager.getConnection(this.url,
this.user, this.password);
// System.out.println("连接数据库成功");
} catch (SQLException e2) {
System.out.println("Open Connection_SQLException:" + e2.getMessage());
password=JOptionPane.showInputDialog(null,"数据库密码不正确,请重新输入MySQL数据库的登录密码:");
return false;
}
return true;
} public Connection getConnection() {
return dbConnect;
} public void setConnection(Connection conn) {
this.dbConnect = conn;
} public void setAutoCommit(boolean bl) {
// 设置对数据库的操作是否自动提交
try {
dbConnect.setAutoCommit(bl);
} catch (SQLException ex) {
System.out.println("设置自动提交失败,原因:" + ex.getMessage());
}
} public void commit() {
// 对JDBC事务进行提交
try {
dbConnect.commit();
} catch (SQLException ex) {
System.out.println("数据库提交操作失败,原因:" + ex.getMessage());
}
} public PreparedStatement createPreparedStatement(String sqlString,
boolean isScrollAndUpdateTable) throws SQLException {
// 建立数据库Statement对象
if (isScrollAndUpdateTable) {
// 建立可修改数据的滚动集Statement
ps = dbConnect.prepareStatement(sqlString,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
} else {
// 如果只读取数据
ps = dbConnect.prepareStatement(sqlString);
}
return ps;
} public PreparedStatement createPreparedStatement(String sqlString) throws
SQLException {
ps = dbConnect.prepareStatement(sqlString);
return ps;
} public ResultSet executeQuery() throws SQLException {
// 执行批处理sql语句
this.result = ps.executeQuery();
return result;
} public int executeUpdate() throws SQLException {
return ps.executeUpdate(); // 返回被sql语句影响的行数
} public boolean next() throws SQLException {
return result != null ? result.next() : false; // 好!
} // 下面这些set和get方法用的很好,既避免了直接使用ResultSet,又可以灵活设置数据
// 获取数据库中的信息
public String getStringData(int index) throws SQLException {
// 获取结果集中的字符串内容
return result.getString(index);
} public int getIntData(int index) throws SQLException {
// 获取结果集中的整型内容
return result.getInt(index);
} public float getData(int index) throws SQLException {
// 获取结果集中的浮点型内容
return result.getFloat(index);
} // 设置要对数据库执行的信息
public void setStringData(int index, String data) throws SQLException {
// 设置SQL语句占位符的字符串内容
ps.setString(index, data);
} public void setIntData(int index, int data) throws SQLException {
// 设置SQL语句占位符的整型内容
ps.setInt(index, data);
} public void setData(int index, float data) throws SQLException {
// 设置SQL语句占位符的浮点型内容
ps.setFloat(index, data);
} public void resetResult() throws SQLException {
// 将结果集置空
this.result = null;
} public void close() throws SQLException {
// 关闭数据库连接,将结果集置空
// System.out.println("DB Connection Close()");
this.dbConnect.close();
} // 显示JVM回收信息
public void finalize() throws Throwable {
// System.out.println("DB Connection finalize() bu JVM...");
} public static void main(String[] args){
ConnectBean testBean=new ConnectBean();
System.out.println("测试数据库连接..");
testBean.openConnection();
System.out.println("测试成功..");
}}