java JDBC SQL 数据库被锁定？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15593170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JDBC SQL Database is locked?
提问by user1113827
I am writing a program that uses a local SQL database to store data.
我正在编写一个使用本地 SQL 数据库来存储数据的程序。
I am using the driver found here: https://bitbucket.org/xerial/sqlite-jdbc
我正在使用此处找到的驱动程序：https: //bitbucket.org/xerial/sqlite-jdbc
I am trying to read from the database and put the contents of tableName into a JTable like this:
我正在尝试从数据库中读取并将 tableName 的内容放入 JTable 中，如下所示：
public Object[][] getTable(String tableName){
int columns = getColumnNumber(tableName);
int rows = getRowNumber(tableName);
String[] columnNames = getColumnNames(tableName);
Object[][] table = new Object[rows][columns];
try{
Connection connection = DriverManager.getConnection("jdbc:sqlite:" + dbName + ".db");
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select * from " + tableName);
for(int r = 0; r < rows; r++){
rs.next();
for (int c = 0; c < columns; c++){
table[r][c] = rs.getString(columnNames[c]);
}
}
return table;
}catch(Exception e){
System.out.println("ERROR CREATING TABLE ARRAY");
e.printStackTrace();
return null;
}
}
Then later I try to come back and add a row to the table:
然后我尝试回来并在表中添加一行：
public boolean addRow(String tableName, Object[] values){
if(!isDataAcceptable(tableName, values))
return false;
try{
String stmt = "insert into " + tableName + " values (";
for(int c = 0; c < values.length; c++){
if(values[c] instanceof String)
stmt += "'" + values[c] + "'";
else
stmt += values[c];
if(c == (values.length - 1))
stmt += ");";
else
stmt += ", ";
}
System.out.println(stmt);
Connection connection = DriverManager.getConnection("jdbc:sqlite:" + dbName + ".db");
Statement statement = connection.createStatement();
statement.executeUpdate(stmt);
return true;
}catch(Exception e){
System.out.println("ERROR INSERTING ROW");
e.printStackTrace();
return false;
}
}
I then want to update the JTable I created before with the new row.
然后我想用新行更新我之前创建的 JTable。
When I try to add the row however it causes an exception:
但是，当我尝试添加行时，它会导致异常：
java.sql.SQLException: database is locked
that points to the line:
指向这条线：
statement.executeUpdate(stmt);
...in the addRow() method.
...在 addRow() 方法中。
Why is the database locked and how do I unlock it to write to it?
为什么数据库被锁定，我如何解锁它以写入它？
采纳答案by Stephen C
Why is the database locked and how do I unlock it to write to it?
为什么数据库被锁定，我如何解锁它以写入它？
The database is most likely locked in addRowbecause the previous call to getTabledid not close the resultset. The code also fails to close the database connection objects which will cause a resource leak.
数据库很可能被锁定，addRow因为之前的调用getTable没有关闭结果集。该代码也无法关闭数据库连接对象，这将导致资源泄漏。
The basic fix is to call close()on the rsand connectionobjects, but you need to do it the right way to make your code reliable.
基本的解决方法是调用close()在rs和connection对象，但你需要做的是正确的方法，使你的代码可靠。
Here's the recommended way to do it in Java 7 using "try with resources" syntax:
这是在 Java 7 中使用“尝试资源”语法的推荐方法：
try (Connection connection =
DriverManager.getConnection("jdbc:sqlite:" + dbName + ".db"),
Statement stmt = connection.createStatement(),
ResultSet rs = stmt.executeQuery("select * from " + tableName)) {
for (int r = 0; r < rows; r++) {
rs.next();
for (int c = 0; c < columns; c++) {
table[r][c] = rs.getString(columnNames[c]);
}
}
return table;
} catch (Exception e) {
System.out.println("ERROR CREATING TABLE ARRAY");
e.printStackTrace();
return null;
}
You can also do this by calling closeexplicitly in a finallyblock, but it is more verbose, and the code can be difficult to get right if you have related resources that need to be closed ... like here.
您也可以通过close在finally块中显式调用来做到这一点，但它更冗长，如果您有需要关闭的相关资源，代码可能很难正确……就像这里。
