java - 如何在多线程java中插入数据库

Question

我有一个任务。我需要随机生成 1 到 1000000 之间的数字并将其插入到数据库中名为“numbers”的表中。表“numbers”有两列:“number”和“quantity”如果数据库中已存在该数量，我需要增加表中的数量。我已经尝试过这样的方法:

public void insertRow(String number) throws SQLException {
        int cnt = getCount(number);
        if (cnt == 0) {
            insert(number);
        } else if (cnt > 0) {
            update(number);
        }
    }

getCount 检查数据库中是否存在数字:

private int getCount(String number) throws SQLException {
        int cnt = 0;
        String sql = "select count(number) as cnt from \"PUBLIC\".UNIQUE_NUMBER where number='" + number + "'";
        Statement sta = getConnection().createStatement();
        ResultSet rs = sta.executeQuery(sql);
        if (rs.next()) {
            cnt = rs.getInt("cnt");
        }

        return cnt;
    }

插入数据库:

private boolean insert(String number) throws SQLException {
        String sql = "insert into \"PUBLIC\".UNIQUE_NUMBER (number, qty) values(?, ?)";
        PreparedStatement ps = getConnection().prepareStatement(sql);
        synchronized (this) {
            try {
                getConnection().setAutoCommit(false);
                ps.setString(1, number);
                ps.setInt(2, 0);
                ps.addBatch();
                ps.executeBatch();
                getConnection().commit();

            } catch (Exception e) {
                LOGGER.error(e.toString());
                try {
                    getConnection().rollback();
                } catch (Exception ex) {
                    LOGGER.error(ex.toString());
                }
                return false;
            } finally {
                if (ps != null) {
                    ps.close();
                }
                getConnection().setAutoCommit(true);
            }
        }
        return true;
    }

更新:

private boolean update(String number) throws SQLException {
        String sql = "update \"PUBLIC\".UNIQUE_NUMBER set (qty) = (?) where number = ?";
        int qty = selectQtyByNumber(number) + 1;
        PreparedStatement ps = getConnection().prepareStatement(sql);
        synchronized (this) {
            try {
                getConnection().setAutoCommit(false);
                ps.setInt(1, qty);
                ps.setString(2, number);
                ps.executeUpdate();
                getConnection().commit();
            } catch (Exception e) {
                LOGGER.error(e.toString());
                try {
                    getConnection().rollback();
                } catch (Exception ex) {
                    LOGGER.error(ex.toString());
                }
                return false;
            } finally {
                if (ps != null) {
                    ps.close();
                }
                getConnection().setAutoCommit(true);
            }
        }
        return true;
    }

SelectQtyByNumber方法获取当前数量数量

private int selectQtyByNumber(String number) throws SQLException {
        int qty = 0;
        String sql = "select qty from \"PUBLIC\".UNIQUE_NUMBER where number='" + number + "'";
        Statement sta = getConnection().createStatement();
        ResultSet rs = sta.executeQuery(sql);
        if (rs.next()) {
            qty = rs.getInt("qty");
        }
        return qty;
    }

当我运行此代码时，我总是收到 BatchUpdateException:完整性约束违规。但有些数字的数量增加了。为什么会发生这种情况？怎么处理呢？数据库为HSQLDB。

这是我的数据库构造函数和 getConnection 方法

public Database(String url, String user_name, String password) {
        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver");

            this.connection = DriverManager.getConnection(url, user_name, password);

        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            LOGGER.error("Database initialization exception: " + e.toString());
        }
    }

    public Connection getConnection() {
        return this.connection;
    }

Answer 1

您过于依赖 Java 代码。您的代码可以简化，但您可以对 MERGE 语句使用完全不同的方法，并允许 HSQLDB 处理整个事情。该语句可以与自动提交一起使用，并且是线程安全的。

MERGE INTO public.unique_number
USING VALUES (CAST(? AS VARCHAR(100)), CAST(? AS INTEGER)) AS vals(number, quantity)
ON unique_number.number = vals.number
WHEN MATCHED THEN
UPDATE SET number = vals.number
WHEN NOT MATCHED THEN
INSERT (number, quantity) VALUES vals.number, valse.quantity;

查看指南 http://hsqldb.org/doc/guide/dataaccess-chapt.html#dac_merge_statement