How to insert in database inside multi threads java

Question

I have a task. I need to randomly generate numbers between 1 and 1000000 and insert it to table called 'numbers' in database. Table 'numbers' has two columns: 'number' and 'quantity' If the number already existing in database, I need to increment quantity in the table. I have tried it in this way:

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

getCount checking if number is existing in database:

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;
    }

Inserting to database:

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;
    }

And updating:

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 method gets current quantity of number

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;
    }

When I run this code I always get BatchUpdateException: integrity constraint violation. But some number's quantity increases. Why is it happening? How to deal with it? Database is HSQLDB.

This is my Database constructor and getConnection method

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;
    }

fredt · Accepted Answer · 2015-12-03 12:10:55Z

1

You rely too much on Java code. Your code can be simplified, but you can use a completely different approach with the MERGE statement and allow HSQLDB to handle the whole thing. This statement can be used with auto-commit and is thread safe.

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;

See the Guide http://hsqldb.org/doc/guide/dataaccess-chapt.html#dac_merge_statement

edited Dec 3, 2015 at 12:10

answered Dec 2, 2015 at 13:47

fredt

24.5k3 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

duffymo Over a year ago

Not ANSI SQL. Will this work with databases other than Hypersonic? Are there analogous constructs in other relational databases? If yes, it could just be a matter of database-dependent SQL.

fredt Over a year ago

This is ANSI syntax and supported by many other databases. For MySQL there is INSERT with ON DUPLICATE KEY clause.

duffymo · Accepted Answer · 2015-12-01 21:16:00Z

0

Your problem is that JDBC package classes are not thread safe. If you're sharing connections between threads you've got a problem.

The details around connection acquisition are crucial. You hide them in your getConnection() method. Are they pooled? Does every operation get a fresh new Connection instance? Where are those being closed?

Your second problem is isolation. You need to set the connection isolation to the highest level possible: SERIALIZABLE. You can't interleave reads and writes properly.

edited Dec 1, 2015 at 21:16

answered Dec 1, 2015 at 21:10

duffymo

310k46 gold badges376 silver badges570 bronze badges

2 Comments

user3128979 Over a year ago

thanks for your answer. I updated my question with getConnection method. I am sorry, but I am really newbie with multithreading. Can I create connection pool with my hsqldb driver?

duffymo Over a year ago

Find a connection pool class. Apache has one.

Collectives™ on Stack Overflow

How to insert in database inside multi threads java

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related