jdbc 好坑啊

最近在写一个自己的项目,底层db 想用dbutils + c3p0 顺便研究下底层的代码,结果立马跌坑里了。

GET GENERATED_KEY

mysql 的主键如果选择 autogenrated 的话,那么会自动生成id。如果想到获得这个 id 的话在 12 年的dbutils是不支持的,除非你再查一次或者修改源码比如这个patch

不过今年出了个 1.6 版本,(撇开下话题,话说我 google 的是否发现连“看看新闻网”都报道了这个消息),他多了个insert接口,代码入下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 /**
* Executes the given INSERT SQL without any replacement parameters.
* The <code>Connection</code> is retrieved from the
* <code>DataSource</code> set in the constructor.
* @param <T> The type of object that the handler returns
* @param sql The SQL statement to execute.
* @param rsh The handler used to create the result object from
* the <code>ResultSet</code> of auto-generated keys.
* @return An object generated by the handler.
* @throws SQLException if a database access error occurs
* @since 1.6
*/

public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException {
return insert(this.prepareConnection(), true, sql, rsh, (Object[]) null);
}

// 内部实现

try {
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
this.fillStatement(stmt, params);
stmt.executeUpdate();
ResultSet resultSet = stmt.getGeneratedKeys();
generatedKeys = rsh.handle(resultSet);
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return generatedKeys;

如果是插入语句的话自己实现一个回调函数,他会帮你拿generated key,然后调用你然后层层返回generatedKey。

然后就坑了。。。。

好久没写jdbc了,我忘记拿 resultSet 之前要先 resultSet.next()下了(而且即使是第一条数据也是必须要先next下的)。。。。各种debug看数据明明是有的。。。

于是研究了下代码,next到底干了啥事。。。

c3p0 也挺坑的。com.mchange.v2.c3p0.impl.NewProxyResultSet 这个class是生成的。源码是没的。。。

反编译结果,跟踪next(),于是定位刀inner

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public final boolean next() throws SQLException
{

try
{
if (this.proxyConn != null) this.proxyConn.maybeDirtyTransaction();

return this.inner.next();
}
catch (NullPointerException exc)
{
if (isDetached())
{
throw SqlUtils.toSQLException("You can't operate on a closed ResultSet!!!", exc);
}
throw exc;
}
catch (Exception exc)
{
if (!isDetached())
{
throw this.parentPooledConnection.handleThrowable(exc);
}
throw SqlUtils.toSQLException(exc);
}
}

跟踪inner,这个是mysql-connector => ResultSetImpl.class 实现的。。

1
2
3
4
public NewProxyResultSet(ResultSet inner)
{

this.inner = inner;
}

继续跟踪 mysql-connector => ResultSetImpl.class => inner

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
public boolean next()
throws SQLException
{

synchronized (checkClosed().getConnectionMutex())
{
if (this.onInsertRow) {
this.onInsertRow = false;
}

if (this.doingUpdates) {
this.doingUpdates = false;
}

if (!reallyResult()) {
throw SQLError.createSQLException(Messages.getString("ResultSet.ResultSet_is_from_UPDATE._No_Data_115"), "S1000", getExceptionInterceptor());
}

if (this.thisRow != null)
this.thisRow.closeOpenStreams();
boolean b;
boolean b;
if (this.rowData.size() == 0) {
b = false;
} else {
// 这里
this.thisRow = this.rowData.next();
boolean b;
if (this.thisRow == null) {
b = false;
} else {
clearWarnings();

b = true;
}

}

setRowPositionValidity();

return b;
}
}

总之,吧 thisRow 作为那一条数据的指针了。

还有个问题,按照正常人的理解,第一条数据为何要next呢?

那么还要看下 rowdata 的结构

RowDataCursor.class 注意currentPositionInEntireResult这个变量, 初始值是-1 也是醉了。。。 next下后就变成0了。

然后,就可以取到 Field[0]了。。。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
public class RowDataCursor
implements RowData
{

private static final int BEFORE_START_OF_ROWS = -1;
private List<ResultSetRow> fetchedRows;
private int currentPositionInEntireResult = -1;

private int currentPositionInFetchedRows = -1;
private ResultSetImpl owner;
private boolean lastRowFetched = false;
private Field[] metadata;
private MysqlIO mysql;
private long statementIdOnServer;
private ServerPreparedStatement prepStmt;
private static final int SERVER_STATUS_LAST_ROW_SENT = 128;
private boolean firstFetchCompleted = false;

private boolean wasEmpty = false;

private boolean useBufferRowExplicit = false;

public RowDataCursor(MysqlIO ioChannel, ServerPreparedStatement creatingStatement, Field[] metadata)
{

this.currentPositionInEntireResult = -1;
this.metadata = metadata;
this.mysql = ioChannel;
this.statementIdOnServer = creatingStatement.getServerStatementId();
this.prepStmt = creatingStatement;
this.useBufferRowExplicit = MysqlIO.useBufferRowExplicit(this.metadata);
}

public ResultSetRow next()
throws SQLException
{

if ((this.fetchedRows == null) && (this.currentPositionInEntireResult != -1)) {
throw SQLError.createSQLException(Messages.getString("ResultSet.Operation_not_allowed_after_ResultSet_closed_144"), "S1000", this.mysql.getExceptionInterceptor());
}

if (!hasNext()) {
return null;
}

this.currentPositionInEntireResult += 1;
this.currentPositionInFetchedRows += 1;

if ((this.fetchedRows != null) && (this.fetchedRows.size() == 0)) {
return null;
}

if (this.currentPositionInFetchedRows > this.fetchedRows.size() - 1) {
fetchMoreRows();
this.currentPositionInFetchedRows = 0;
}

ResultSetRow row = (ResultSetRow)this.fetchedRows.get(this.currentPositionInFetchedRows);

row.setMetadata(this.metadata);

return row;
}

最后就串起来了,总之他这么坑人的设计其实是为了防止查询到不存在的数据 ,防止 Filed 数组越界,特别是如果 query 出来0个结果然后不 next 检查顺便定位下,而是直接 getObject(0) 然后数组就越界了。。。

avatar

lelouchcr's blog