JPA number generators without primary key and general use

Question

Environment: OpenJPA2.x, Tomcat webapp, Mariadb but may be changed. This is not Hibernate or Spring webapp.

I have read few topics already such as this: Hibernate JPA Sequence (non-Id)

I have few entity classes with someNumber non-primary key field, some have someNumber and someNumberB twin columns. Fields have a constraint UNIQUE(someNumber) and UNIQUE(someNumberB), primary key composite is PRIMARY(server_id, code). I need a numeric value before commiting row inserts.

If I understood other topics I cannot use JPA @generator tags. I am forced to implement an old-school utility method. This is a method I did, it takes a fresh db connection so it's always run in a separate transaction.

public synchronized static long getGeneratorValue(String genName, int incStep) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        if (genName==null) genName="app";
        // try few times before give up, another node may have updated a value in-between. Create a new transaction from db connection pool.
        for(int idx=0; idx<3; idx++) {
            conn = createConnection(false); // autocommit=false
            stmt = conn.createStatement();              
            rs = stmt.executeQuery(String.format("Select value From generators Where name='%s'", genName));
            if (!rs.next()) throw new IllegalArgumentException("Invalid generator name " + genName);

            if (incStep==0)
                return rs.getLong("value"); // return an existing value

            long oldValue = rs.getLong("value");
            long newValue = oldValue+incStep;
            int rowCount = stmt.executeUpdate(String.format("Update generators Set value=%d Where name='%s' and value=%d", newValue, genName, oldValue));
            if (rowCount>0) {
                conn.commit();
                return newValue;                    
            }
            close(rs, stmt, conn);
            conn=null;
        }
        throw new IllegalArgumentException("Obtaining a generator value failed " + genName);
    } catch(Exception ex) {
        try { conn.rollback(); } catch(Exception e){}
        if (ex instanceof IllegalArgumentException) throw (IllegalArgumentException)ex;
        else throw new IllegalArgumentException(ex.getMessage(), ex);
    } finally {
        if (conn!=null) close(rs, stmt, conn);
    }
}

I am not fully happy with this, especially a failsafe foreach_loop against another Tomcat node updated generator value in-between concurrently. This loop may fail on busy workloads.

Could I use DB auto_increment column as a general purpose number generator, I suppose it tolerates better concurrency? If this locks a database to MariaDB,MySQL or similar I can live with that for now.

Value must be a numeric field for legacy purpose, I cannot use GUID string values.

Answer 1

I was thinking about using DB auto_increment column and came up with this utility function. I probably go with this implementation or do StackOverflow community have better tricks available?

CREATE TABLE generatorsB (
  value bigint UNSIGNED NOT NULL auto_increment,
  name varchar(255) NOT NULL,
  PRIMARY KEY(value)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_swedish_ci;

// name=any comment such as an entityClass name, no real application use
public static long getGeneratorBValue(String name) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        String sql = String.format("Insert Into generatorsB (name) Values('%s')", name);
        conn = createConnection(false); // autocommit=false
        stmt = conn.createStatement();
        int rowCount = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        if (rowCount>0) {
            rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                long newValue = rs.getLong(1);
                if (newValue % 5 == 0) {
                    // delete old rows every 5th call, we don't need generator table rows
                    sql = "Delete From generatorsB Where value < "+ (newValue-5);
                    stmt.close();
                    stmt = conn.createStatement();
                    stmt.executeUpdate(sql, Statement.NO_GENERATED_KEYS);
                }
                conn.commit();
                return newValue;
            }
        }
        throw new IllegalArgumentException("Obtaining a generator value failed");
    } catch(Exception ex) {
        try { conn.rollback(); } catch(Exception e){}
        if (ex instanceof IllegalArgumentException) throw (IllegalArgumentException)ex;
        else throw new IllegalArgumentException(ex.getMessage(), ex);
    } finally {
        if (conn!=null) close(rs, stmt, conn);
    }
}