## Java专题十四：JDBC [TOC] ### 14.1.JDBC框架  ### 14.2.JDBCAPI > java.sql.Driver - Connection connect(String url, java.util.Properties info) > java.sql.DriverManager - static Connection getConnection(String url, String user, String password) > java.sql.Connection - Statement createStatement() - PreparedStatement prepareStatement(String sql) - CallableStatement prepareCall(String sql) > java.sql.Statement - ResultSet executeQuery(String sql) - int executeUpdate(String sql) - boolean execute(String sql) > java.sql.PreparedStatement - ResultSet executeQuery() - int executeUpdate() - boolean execute() - void setInt(int parameterIndex, int x) - void setString(int parameterIndex, String x) > java.sql.ResultSet - boolean next() - String getString(int columnIndex) - String getString(int columnLabel) **例，从user表中查询name为张三的用户信息：** ```java String url = "jdbc:mysql://localhost:3306/mail_manager?useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true"; String user = "root"; String password = "root"; Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, username, password); PreparedStatement ps = conn.prepareStatement("select name, email from user where name=?"); ps.setString(1, "张三"); ResultSet rs = ps.executeQuery(); while(rs.next()){ String username = rs.getString("name"); String email = rs.getString("email"); System.out.println(username); System.out.println(email); } }catch (ClassNotFoundException ignored){ } ``` ### 14.3.JDBC Driver > com.mysql.jdbc.NonRegisteringDriver ```java // com.mysql.jdbc.NonRegisteringDriver public Connection connect(String url, Properties info) { // ... com.mysql.jdbc.Connection newConn = ConnectionImpl.getInstance(this.host(props), this.port(props), props, this.database(props), url); // ... } protected static Connection getInstance(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) throws SQLException { return (Connection)(!Util.isJdbc4() ? new ConnectionImpl(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url) : (Connection)Util.handleNewInstance(JDBC_4_CONNECTION_CTOR, new Object[]{hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url}, (ExceptionInterceptor)null)); } ``` > com.mysql.jdbc.ConnectionImpl ```java // com.mysql.jdbc.ConnectionImpl public ConnectionImpl(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) throws SQLException { // ... this.createNewIO(false); // ... } public void createNewIO(boolean isForReconnect) throws SQLException { synchronized(this.getConnectionMutex()) { Properties mergedProps = this.exposeAsProperties(this.props); if (!this.getHighAvailability()) { this.connectOneTryOnly(isForReconnect, mergedProps); } else { this.connectWithRetries(isForReconnect, mergedProps); } } } private void connectOneTryOnly(boolean isForReconnect, Properties mergedProps) throws SQLException { // ... this.coreConnect(mergedProps); // ... } private void coreConnect(Properties mergedProps) throws SQLException, IOException { // ... this.io = new MysqlIO(newHost, newPort, mergedProps, this.getSocketFactoryClassName(), this.getProxy(), this.getSocketTimeout(), this.largeRowSizeThreshold.getValueAsInt()); this.io.doHandshake(this.user, this.password, this.database); // ... } ``` > com.mysql.jdbc.MysqlIO > 通过Socket与MysqlServer通信，通过mysqlInput与mysqlOutput传输数据 ```java // com.mysql.jdbc.MysqlIO public MysqlIO(String host, int port, Properties props, String socketFactoryClassName, MySQLConnection conn, int socketTimeout, int useBufferRowSizeThreshold) throws IOException, SQLException { // ... this.mysqlConnection = this.socketFactory.connect(this.host, this.port, props); this.mysqlConnection = this.socketFactory.beforeHandshake(); if (this.connection.getUseReadAheadInput()) { this.mysqlInput = new ReadAheadInputStream(this.mysqlConnection.getInputStream(), 16384, this.connection.getTraceProtocol(), this.connection.getLog()); } else if (this.connection.useUnbufferedInput()) { this.mysqlInput = this.mysqlConnection.getInputStream(); } else { this.mysqlInput = new BufferedInputStream(this.mysqlConnection.getInputStream(), 16384); } this.mysqlOutput = new BufferedOutputStream(this.mysqlConnection.getOutputStream(), 16384); // ... } ``` > com.mysql.jdbc.PreparedStatement > 通过发送和接受Packet完成数据库查询 ```java // com.mysql.jdbc.PreparedStatement public ResultSet executeQuery() throws SQLException { synchronized(this.checkClosed().getConnectionMutex()) { // ... Buffer sendPacket = this.fillSendPacket(); // ... this.results = this.executeInternal(this.maxRows, sendPacket, this.createStreamingResultSet(), true, metadataFromCache, false); // ... return this.results; } } protected ResultSetInternalMethods executeInternal(int maxRowsToRetrieve, Buffer sendPacket, boolean createStreamingResultSet, boolean queryIsSelectOnly, Field[] metadataFromCache, boolean isBatch) throws SQLException { synchronized(this.checkClosed().getConnectionMutex()) { // ... ResultSetInternalMethods rs; rs = locallyScopedConnection.execSQL(this, (String)null, maxRowsToRetrieve, sendPacket, this.resultSetType, this.resultSetConcurrency, createStreamingResultSet, this.currentCatalog, metadataFromCache, isBatch); // ... } } ``` > com.mysql.jdbc.ConnectionImpl ```java // com.mysql.jdbc.ConnectionImpl public ResultSetInternalMethods execSQL(StatementImpl callingStatement, String sql, int maxRows, Buffer packet, int resultSetType, int resultSetConcurrency, boolean streamResults, String catalog, Field[] cachedMetadata, boolean isBatch) throws SQLException { synchronized(this.getConnectionMutex()) { // ... ResultSetInternalMethods var36 = this.io.sqlQueryDirect(callingStatement, (String)null, (String)null, packet, maxRows, resultSetType, resultSetConcurrency, streamResults, catalog, cachedMetadata); // ... } } ``` > com.mysql.jdbc.MysqlIO > 通过Socket与MysqlServer通信，通过mysqlInput与mysqlOutput传输数据 ```java // com.mysql.jdbc.MysqlIO final ResultSetInternalMethods sqlQueryDirect(StatementImpl callingStatement, String query, String characterEncoding, Buffer queryPacket, int maxRows, int resultSetType, int resultSetConcurrency, boolean streamResults, String catalog, Field[] cachedMetadata) throws Exception { // ... this.sendPacket.writeByte((byte)3); if (characterEncoding != null) { if (this.platformDbCharsetMatches) { this.sendPacket.writeStringNoNull(query, characterEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), this.connection); } else if (StringUtils.startsWithIgnoreCaseAndWs(query, "LOAD DATA")) { this.sendPacket.writeBytesNoNull(StringUtils.getBytes(query)); } else { this.sendPacket.writeStringNoNull(query, characterEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), this.connection); } } else { this.sendPacket.writeStringNoNull(query); } Buffer resultPacket = this.sendCommand(3, (String)null, queryPacket, false, (String)null, 0); // ... } final Buffer sendCommand(int command, String extraData, Buffer queryPacket, boolean skipCheck, String extraDataCharEncoding, int timeoutMillis) throws SQLException { // ... this.send(this.sendPacket, this.sendPacket.getPosition()); // ... } private final void send(Buffer packet, int packetLen) throws SQLException { // ... Buffer packetToSend = packet; packet.setPosition(0); packet.writeLongInt(packetLen - 4); packet.writeByte(this.packetSequence); // ... this.mysqlOutput.write(packetToSend.getByteBuffer(), 0, packetLen); this.mysqlOutput.flush(); // ... } ``` ### 14.4.Mysql Server 参考[https://dev.mysql.com/doc/internals/en/mysql-server.html#mysqld\_stmt\_fetch](https://dev.mysql.com/doc/internals/en/mysql-server.html#mysqld_stmt_fetch)