How to insert record to database in Java threads?

Question

I am trying to deal with multitheading in Java.

I have read many articles and question(here on StackOverflow) but didn't find any clear examples how to use it.

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.

So, what did I get.

This is my configuration of Database

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

This is my logic:

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

Get count of number in the table

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

Insert and update

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.

What am I doing wrong?

Here is my logs

[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

you are only getting into the rollback because you already have an exception - what is it? I guess that you need to create/begin a transaction before commiting. — Scary Wombat
– Scary Wombat, Commented Dec 8, 2015 at 7:07
I just guess that if query has thrown an exception, then the transaction must rollback. I will try transaction and report here. — Sanat Serikuly
– Sanat Serikuly, Commented Dec 8, 2015 at 7:46
You are getting a unique constraint violation i.e. The record that you are trying to insert already exists (or the primary key anyway) — Scary Wombat
– Scary Wombat, Commented Dec 8, 2015 at 8:45
You should try as hard as you can to execute the entire transaction in a single statement. — user207421
– user207421, Commented Dec 8, 2015 at 9:04

duckstep · Accepted Answer · 2015-12-08 20:51:16Z

the non-transactional way

Do the increment first

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

No transaction handling (setAutoCommit(false), commit() or rollback()) reqired.

the transactional way

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

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.

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.

Collectives™ on Stack Overflow

How to insert record to database in Java threads?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related