jdbc 好坑啊

December 09 2014

最近在写一个自己的项目，底层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