I have created a database connection with SQLite using JDBC in Java. My SQL statements execute properly, but sometimes I get the following error while I use conn.commit()
:
java.sql.SQLException: SQL logic error or missing database
Can anyone please help me how to avoid this type of problem. Is there a better approach of calling JDBC programs?
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:/home/Data/database.db3");
conn.setAutoCommit(false);
开发者_开发问答String query = "Update Chits set BlockedForChit = 0 where ServerChitID = '" + serverChitId + "' AND ChitGatewayID = '" + chitGatewayId + "'";
Statement stmt = conn.createStatement();
try {
stmt.execute(query);
conn.commit();
stmt.close();
stmt = null;
}
Can your variables serverChitId & chitGatewayId contain characters that would corrupt the SQL? It is usually safer to use PreparedStatements:
PreparedStatement ps = conn.prepareStatement("Update Chits set BlockedForChit = 0 where ServerChitID = ? AND ChitGatewayID = ?");
ps.setString(1, serverChitId);
ps.setString(2, chitGatewayId);
ps.executeUpdate();
This way the JDBC driver is responsible for making sure the necessary escapes are made to the strings.
Try setting conn.setAutoCommit
to true. Also you need to delete conn.commit();
.
If you are doing this inside of a function, make your function synchronized.
It's even more better if you use PreparedStatement instead of Statement.
All this is happening because sometimes you are trying to connect and modify your database at a same time and since the last connection hasn't commited yet, it throws that exception. When you set it to autoCommit it will handle the flow by itself.(it was really painful for me cause it says nothing more, I read all of the org.sqlite.DB files to find this out)
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:/home/Data/database.db3");
conn.setAutoCommit(true);
PreparedStatement ps = "Update Chits set BlockedForChit = 0 where ServerChitID = ? AND ChitGatewayID = ? ";
ps.setString(1, serverChitId);
ps.setString(2, chitGatewayId);
try {
ps.executeUpdate();
}
精彩评论