如何在Java线程中将记录插入数据库? [英] How to insert record to database in Java threads?
问题描述
我正在尝试处理Java中的多头处理.
I am trying to deal with multitheading in Java.
我已经阅读了很多文章和问题(在StackOverflow上)，但是找不到如何使用它的清晰示例.
I have read many articles and question(here on StackOverflow) but didn't find any clear examples how to use it.
我在HsqlDB数据库中有Unique_Numbers表.有2列:NUMBER和QTY. 我的任务是检查是否存在数字，如果是，则增加数字的数量，如果没有，则插入此数字.
I have Unique_Numbers table in HsqlDB database. There are 2 columns: NUMBER and QTY. My task is to check if number exsists and increase QTY of number if yes and insert this number if not.
那么，我得到了什么.
这是我对数据库的配置
private final ComboPooledDataSource dataSource;
public Database(String url, String userName, String password) throws PropertyVetoException {
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("org.hsqldb.jdbcDriver");
dataSource.setJdbcUrl(url);
dataSource.setUser(userName);
dataSource.setPassword(password);
dataSource.setMaxPoolSize(10);
dataSource.setMaxStatements(180);
dataSource.setMinPoolSize(5);
dataSource.setAcquireIncrement(5);
}
这是我的逻辑:
public void insertRow(String number) throws SQLException {
int cnt = getCount(number);
if (cnt == 0) {
insert(number);
} else if (cnt > 0) {
update(number);
}
}
获取表格中的数字计数
private int getCount(String number) {
int cnt = 0;
String sql = "select count(number) as cnt from \"PUBLIC\".UNIQUE_NUMBER where number='" + number + "'";
try {
Statement sta;
try (Connection connection = dataSource.getConnection()) {
sta = connection.createStatement();
ResultSet rs = sta.executeQuery(sql);
if (rs.next()) {
cnt = rs.getInt("cnt");
}
}
sta.close();
} catch (Exception e) {
LOGGER.error("error select cnt by number" + e.toString());
}
return cnt;
}
插入并更新
private boolean insert(String number) throws SQLException {
String sql = "insert into \"PUBLIC\".UNIQUE_NUMBER (number, qty) values(?, ?)";
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, number);
ps.setInt(2, 0);
ps.addBatch();
ps.executeBatch();
try {
connection.commit();
} catch (Exception e) {
connection.rollback();
LOGGER.error(e.toString());
return false;
}
}
}
return true;
}
private boolean update(String number) throws SQLException {
String sql = "update \"PUBLIC\".UNIQUE_NUMBER set (qty) = (?) where number = ?";
int qty = selectQtyByNumber(number) + 1;
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, qty);
ps.setString(2, number);
ps.executeUpdate();
try {
connection.commit();
} catch (Exception e) {
connection.rollback();
LOGGER.error(e.toString());
return false;
}
}
}
return true;
}
我阅读时，必须使用池连接".每个线程建立一个连接很重要. 启动应用程序时，出现约束异常或回滚异常:序列化失败.
As I read, I must use Pool Connection. It is important to give one connection to each thread. When I start my application, I got constraint exception or exception with Rollback: serialization failed.
我在做什么错了?
这是我的日志
[INFO] [generate38] ERROR se.homework.hwbs.tasks.un.server.threads.InsertRowThread - exception while inserting numberintegrity constraint violation: check constraint; SYS_CT_10114 table: UNIQUE_NUMBER
[INFO] [generate38] ERROR se.homework.hwbs.tasks.un.server.database.Database - error select cnt by number java.sql.SQLTransactionRollbackException: transaction rollback: serialization failure
[INFO] [generate38] ERROR se.homework.hwbs.tasks.un.server.threads.InsertRowThread - exception while inserting numbertransaction rollback: serialization failure
[INFO] [generate38] ERROR se.homework.hwbs.tasks.un.server.database.Database - error select cnt by number java.sql.SQLTransactionRollbackException: transactionrollback: serialization failure
推荐答案
非交易方式
先进行增量
update UNIQUE_NUMBER set qty = qty + 1 where number = ?
检查是否更新了任何行，如果没有更新，请插入数字
Check if it did update any row, insert the number if it didn't
int rowsMatched = ps.executeUpdate();
if(rowsMatched == 0) {
try {
insert into UNIQUE_NUMBER (number, qty) values(?, 0)
} catch(Exception e) {
// the insert will fail if another thread has already
// inserted the same number. check if that's the case
// and if so, increment instead.
if(isCauseUniqueConstraint(e)) {
update UNIQUE_NUMBER set qty = qty + 1 where number = ?
} else {throw e;}
}
}
不需要事务处理(setAutoCommit(false)，commit()或rollback()).
No transaction handling (setAutoCommit(false), commit() or rollback()) reqired.
交易方式
如果您仍然希望以事务方式执行此操作，则需要在单个事务中执行所有步骤，例如@EJP建议:
If you still want to do this in a transactional way, you need to do all steps within a single transaction, like @EJP suggested:
connection.setAutoCommit(false);
// check if number exists
// increment if it does
// insert if it doesn't
// commit, rollback & repeat in case of error
connection.setAutoCommit(true);
如果此代码与其他代码共享连接池(因为这是其他人期望连接所处的默认状态)，或者将池中的连接始终设置为事务模式，则将auto commit设置为true.
Set auto commit back to true if this code shares the connection pool with other code (as that's the default state others will expect the connection to be in) or make it clear that connections in the pool will always be in transactional mode.
在您的代码中，getCount有时会在自动提交模式下获得连接(首次使用)，有时会在事务模式下获得连接(在insert和/或update之后重用)-这就是为什么您看到回滚的原因getCount中的异常.
In your code, getCount will sometimes get a connection in auto commit mode (first use) and sometimes get a connection in transactional mode (reused after insert and/or update) - that's why you see rollback exceptions in getCount.
这篇关于如何在Java线程中将记录插入数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
