I'm using HSQLDB. Inside a java function I'm creating a temporary table and populating it with some data. Right after I'm updating a regular table with the data from the temporary table.
我正在使用HSQLDB。在java函数中,我正在创建一个临时表并用一些数据填充它。在我用临时表中的数据更新常规表之后。
If I debug the code inside the function and perform SELECT
I can see that the data was indeed updated in both tables, but after committing the changes the table is no longer updated! (the regular table, not the temporary one).
如果我调试函数内部的代码并执行SELECT,我可以看到两个表中的数据确实已更新,但在提交更改后,表不再更新! (常规表,而不是临时表)。
When I created the temp table I used: ON COMMIT PRESERVE ROWS
but it doesn't help.
当我创建我使用的临时表时:ON COMMIT PRESERVE ROWS但它没有帮助。
Does anyone know why is this happening?
有谁知道为什么会这样?
Thanks!
谢谢!
Here is the code:
这是代码:
public synchronized void foo(String[] objectsToMark) throws Exception {
String createTempTableQuery = "CREATE TEMP TABLE "+TEMP_TABLE_NAME+"(OBJECTID VARCHAR) " +
"ON COMMIT PRESERVE ROWS";
String dropTempTableQuery = "DROP TABLE "+TEMP_TABLE_NAME;
String updateQueryString = "update " + TABlE_A + " set " +TABlE_A+".X = 'N' where "+TABlE_A+".RESULT_ID in (select "+TABlE_A+".RESULT_ID FROM "+TABlE_A
+" inner join "+TABLE_B+" on "+TABLE_B+".OBJECTID = "+TABlE_A+".OBJECTID"
+" inner join TEMP_TABLE on "+TABLE_B+".OBJECTID = TEMP_TABLE.OBJECTID"
+" where "+TABlE_A+".IS_X = 'Y')";
String insertObjectIDs = "INSERT INTO " + TEMP_TABLE_NAME + " (OBJECTID) VALUES (?)";
Connection conn = null;
Statement stmt = null;
PreparedStatement psInsertObjectIDs = null;
try {
conn = getConnection(getClass().getName() + "");
stmt = conn.createStatement();
stmt.execute(createTempTableQuery);
// insert object's IDs into table
psInsertObjectIDs = conn.prepareStatement(insertObjectIDs);
for (String id : objectsToMark) {
int i = 0;
psInsertObjectIDs.setString(++i, id);
psInsertObjectIDs.addBatch();
}
psInsertObjectIDs.executeBatch();
stmt.execute(updateQueryString);
} catch (Exception e) {
} finally {
// close resources
close(stmt);
stmt = null;
// drop the temporary tables
if (conn != null) {
try {
stmt = conn.createStatement();
stmt.execute(dropTempTableQuery);
} catch (SQLException e) {
}
close(stmt);
}
close(psInsertObjectIDs);
closeConnection(conn);
}
}
Notice that there is no commit here but the connection is configured to perform auto commit.
请注意,此处没有提交,但连接已配置为执行自动提交。
1 个解决方案
#1
1
As the code works when you debug, the problem must be outside this code.
由于代码在您调试时有效,因此问题必须在此代码之外。
If you are running this code as an isolated test on a file: database, and you have not specified a write delay or shutdown option in the jdbc connection url, then the engine does not get enough time to write the data to disk.
如果您将此代码作为文件:数据库的隔离测试运行,并且您没有在jdbc连接URL中指定写入延迟或关闭选项,则引擎没有足够的时间将数据写入磁盘。
The default settings of HSQLDB are for production, where a SHUTDOWN is issued by the application program. For tests, use either of the properties below.
HSQLDB的默认设置用于生产,其中SHUTDOWN由应用程序发出。对于测试,请使用以下任一属性。
This one writes the changes after each commit or auto-commit, therefore slows down production databases:
这个在每次提交或自动提交后写入更改,因此减慢了生产数据库的速度:
jdbc:hsqldb:file:mydb;hsqldb.write_delay=false
This one shuts down the database when the only remaining connection is closed:
当唯一剩余的连接关闭时,这个关闭数据库:
jdbc:hsqldb:file:mydb;shutdown=true
As this issue comes up quite often, future versions of HSQLDB from 2.2.9 will always write the data to disk when the only remaining connection is closed.
由于此问题经常出现,因此2.2.9的HSQLDB的未来版本将始终在剩余的唯一连接关闭时将数据写入磁盘。
#1
1
As the code works when you debug, the problem must be outside this code.
由于代码在您调试时有效,因此问题必须在此代码之外。
If you are running this code as an isolated test on a file: database, and you have not specified a write delay or shutdown option in the jdbc connection url, then the engine does not get enough time to write the data to disk.
如果您将此代码作为文件:数据库的隔离测试运行,并且您没有在jdbc连接URL中指定写入延迟或关闭选项,则引擎没有足够的时间将数据写入磁盘。
The default settings of HSQLDB are for production, where a SHUTDOWN is issued by the application program. For tests, use either of the properties below.
HSQLDB的默认设置用于生产,其中SHUTDOWN由应用程序发出。对于测试,请使用以下任一属性。
This one writes the changes after each commit or auto-commit, therefore slows down production databases:
这个在每次提交或自动提交后写入更改,因此减慢了生产数据库的速度:
jdbc:hsqldb:file:mydb;hsqldb.write_delay=false
This one shuts down the database when the only remaining connection is closed:
当唯一剩余的连接关闭时,这个关闭数据库:
jdbc:hsqldb:file:mydb;shutdown=true
As this issue comes up quite often, future versions of HSQLDB from 2.2.9 will always write the data to disk when the only remaining connection is closed.
由于此问题经常出现,因此2.2.9的HSQLDB的未来版本将始终在剩余的唯一连接关闭时将数据写入磁盘。