CODEWITHSUNDEEP
javasql-servert-sqljdbctransactions
IAmYourFaja
IAmYourFaja

Reputation: 56874

Sequential JDBC statement executions not transacting

I have the following tables:

all_app_users
=============
all_app_user_id         INT PRIMARY KEY NOT NULL
is_enabled              BOOLEAN NOT NULL

myapp_users
===========
myapp_user_id           INT PRIMARY KEY NOT NULL
all_app_user_id         INT FOREIGN KEY (all_app_users)
myapp_user_stage        INT NOT NULL

And the following JDBC code:

Long allAppUserId = null;
Long myAppId = null;
Connection pooledConn = getPooledDBConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
    ps = pooledConn.prepareStatement("INSERT INTO all_app_users ( is_enabled ) VALUES ( ? )");
    ps.setBoolean(1, true);
    ps.execute();
    ps.close();

    ps = pooledConn.prepareStatement("SELECT IDENT_CURRENT('all_app_users')");
    rs = ps.executeQuery();
    allAppUserId = (long)rs.getInt(0);
    rs.close();

    ps = pooledConn.prepareStatement("INSERT INTO myapp_users ( all_app_user_id, myapp_user_stage ) VALUES( ?, ? )");
    ps.setInt(1, Ints.checkedCast(allAppUserId));
    ps.setInt(2, 5);
    ps.execute();
    ps.close();

    ps = pooledConn.prepareStatement("SELECT IDENT_CURRENT('myapp_users')");
    rs = ps.executeQuery();
    myAppId = (long)rs.getInt(0);

    pooledConn.commit();

    System.out.println("Ping");
} catch(SQLException sqlExc) {
    logger.error(ExceptionUtils.getStackTrace(sqlExc));
    if(pooledConn != null) {
        try {
            pooledConn.rollback();
        } catch (SQLException e) {
            logger.error(ExceptionUtils.getStackTrace(e));
        }
    }
} finally {
    try {
        if(rs != null) {
            rs.close();
        }

        if(ps != null) {
            ps.close();
        }

        if(pooledConn != null) {
            pooledConn.close();
        }
    } catch (SQLException e) {
        logger.error(ExceptionUtils.getStackTrace(e));
    }
}

System.out.println("Pong");

When I run that code, I don't get any exceptions, and the "Ping" and "Pong" messages print to STDOUT, however myAppId is NULL. I'm wondering why?

Perhaps it has to do with my use of transactions/commits? Should I be committing after each of the 4 sequential SQL statements? Am I using the JDBC API incorrectly?

Upvotes: 1

Views: 108

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

Unlike SCOPE_IDENTITY, IDENT_CURRENT does not care about transactions or scope: it returns the last identity key that has been generated for the table.

IDENT_CURRENT can return NULL when the table has no identity column, never contained rows, or has been truncated. None of this applies in your situation. However, it looks like you are running the scalar query incorrectly: you never call rs.next() before calling rs.getInt(...):

ps = pooledConn.prepareStatement("SELECT IDENT_CURRENT('all_app_users')");
rs = ps.executeQuery();
rs.next(); // <<== Add this line
allAppUserId = (long)rs.getInt(0);
rs.close();

ps = pooledConn.prepareStatement("INSERT INTO myapp_users ( all_app_user_id, myapp_user_stage ) VALUES( ?, ? )");
ps.setInt(1, Ints.checkedCast(allAppUserId));
ps.setInt(2, 5);
ps.execute();
ps.close();

ps = pooledConn.prepareStatement("SELECT IDENT_CURRENT('myapp_users')");
rs = ps.executeQuery();
rs.next(); // <<== Add this line
myAppId = (long)rs.getInt(0);

Upvotes: 1

Related Questions