问题:
我有一些代码连接到数据库,检查是否根据标准找到了记录,如果找到了记录则返回3,并为用户生成一条消息,告诉用户已经存在具有这些详细信息的记录。如果记录不存在,则执行插入查询以创建该记录。
问题是,在短时间内连续第二次执行选择查询时,当结果是找到一条记录时,选择查询似乎会挂起(返回3,向用户生成消息),因为它没有执行代码,没有报告错误,程序上的GUI也没有响应(无法点击任何其他对象,关闭窗口时也没有响应)。Netbeans仍然运行良好,我可以通过它停止程序的运行。
测试:
A) 如果在第一次成功执行后在同一数据库上运行另一个查询,但在不同的表上运行,则代码会正确执行,但返回尝试再次执行原始查询会导致它挂起。
B) 执行代码而不返回任何结果不会导致它挂起。(参见下面的代码)
C) 在SE上浏览了其他类似的问题,但似乎找不到任何解决我问题的方法。
使用的资源:ucanacess-2.0.9.3,Netbeans IDE 8.1,JDK 1.8(由于与公司旧版本的兼容性问题而编译为1.6),公司网络上的MS Access数据库
代码:
public int insertAddPeriod(String strProjectNumber, String strYear, String strPeriod){
System.out.println("Checking to see if record already exists: " + strProjectNumber + ", Year: " + strYear + ", Period: " + strPeriod);
String strSQLString = null;
try{
strSQLString = "SELECT Program_No, Year, Period FROM tblCost WHERE Program_No = ? AND Year = ? AND Period = ?";
//SETTING PREPARED STATEMENT
PreparedStatement preStatement = con.prepareStatement(strSQLString, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
//SETTING FIRST CONDITION OF PREPARED STATEMENT IE ?
preStatement.setString(1, strProjectNumber);
preStatement.setString(2, strYear);
preStatement.setString(3, strPeriod);
ResultSet rs = preStatement.executeQuery();
if(rs.next()){
//CLOSES CONNECTIONS
rs.close();
preStatement.close();
System.out.println("Check Complete; Period Already Exists");
return 3;
}
else{
System.out.println("Check complete; No Previous Period");
System.out.println("Inserting record: " + strProjectNumber + ", Year: " + strYear + ", Period: " + strPeriod);
strSQLString = null;
try{
strSQLString = "INSERT INTO tblCost (Program_No, Year, Period) VALUES (?, ?, ?) ";
//SETTING PREPARED STATEMENT
PreparedStatement preStatement1 = con.prepareStatement(strSQLString, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
//SETTING FIRST CONDITION OF PREPARED STATEMENT IE ?
preStatement1.setString(1, strProjectNumber);
preStatement1.setString(2, strYear);
preStatement1.setString(3, strPeriod);
//EXECUTE QUERY
preStatement1.executeUpdate();
//CLOSES CONNECTIONS
preStatement1.close();
System.out.println("Insert Complete");
return 1;
}catch(Exception ex){
strEXMessage=ex.getMessage();
System.out.println(strEXMessage);
return 2;
}
}
}catch(Exception ex){
strEXMessage=ex.getMessage();
System.out.println(strEXMessage);
System.out.println("Check error; Unable to check for previous period");
return 2;
}
}
private void AddPeriod(){
//Create Connection
Database db = new Database();
int r = 0;
try {
r = db.CreateConnection();
} catch (SQLException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
//CONNECTION COMPLETE
if(r==1){
int q = 0;
q = db.insertAddPeriod((String)cbProjectNumber.getSelectedItem(),(String)cbCostYear.getSelectedItem(),(String)cbCostPeriod.getSelectedItem());
if(q==1){
System.out.println("Record");
JOptionPane.showMessageDialog(null,"Record added for the period: " + (String)cbCostPeriod.getSelectedItem(),"Complete",JOptionPane.WARNING_MESSAGE);
}
if(q==2){
System.out.println("Error");
JOptionPane.showMessageDialog(null,"There was an error processing the insert query","Error",JOptionPane.WARNING_MESSAGE);
}
if(q==3){
System.out.println("Missing");
JOptionPane.showMessageDialog(null,"Record already exists for the selected period","Error",JOptionPane.WARNING_MESSAGE);
}
}
//CONNECTION ERROR
if(r==2){
System.out.println("Error");
JOptionPane.showMessageDialog(null,"No connection made","Error",JOptionPane.WARNING_MESSAGE);
}
//CONNECTION RETURNS EMPTY
if(r==3){
System.out.println("No Records");
JOptionPane.showMessageDialog(null,"No records found in table","Missing Records",JOptionPane.WARNING_MESSAGE);
}
}
public int CreateConnection() throws SQLException, ClassNotFoundException{
try{
con = DriverManager.getConnection(strDBCon,strDBUser,strDBPass);
System.out.println("Connection Passed");
return 1;
}catch(SQLException ex){
strEXMessage=ex.getMessage();
System.out.println("Connection Failed: " + ex);
return 2;
}
}
public int CloseConnection(){
try{
con.close();
System.out.println("Connection Closed by User");
return 1;
}catch(SQLException ex){
strEXMessage=ex.getMessage();
return 2;
}
}
请忽略不整洁的地方(除非是原因),清理它目前是在制品。
在这种情况下,非响应程序通常是由未提交的插入、更新、删除导致的数据库行锁定引起的。
正如我在代码中看到的,您没有提交准备好的语句。您是否将自动提交设置为true?如果没有,请在执行更新后使用con.commit()进行尝试,该方法的第二次调用应该可以正常工作。