我有以下表格:
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
以及以下JDBC代码:
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");
运行该代码时,没有任何异常,“ Ping”和“ Pong”消息会打印到STDOUT,但是myAppId
为NULL。我想知道为什么吗?
也许与我对事务/提交的使用有关?我应该在4个顺序SQL语句中的每一个之后提交吗?我是否错误地使用了JDBC API?
与不同SCOPE_IDENTITY
,IDENT_CURRENT
它不在乎事务或范围:它返回为该表生成的最后一个身份密钥。
IDENT_CURRENT
NULL
当表没有标识列,从不包含行或被截断时,可以返回。这些都不适用于您的情况。但是,您似乎在错误地运行了标量查询:您永远不会在调用rs.next()
之前调用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);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句