深入理解JDBC設(shè)計模式: DriverManager 解析
JDBC 是java中的一個數(shù)據(jù)連接技術(shù)，它提供了統(tǒng)一的 API 允許用戶訪問任何形式的表格數(shù)據(jù)，尤其是存儲在關(guān)系數(shù)據(jù)庫中的數(shù)據(jù)。
雖然目前JDBC已經(jīng)基本被隱藏在了許多數(shù)據(jù)庫框架之后，但是其底層原理從未變過。所以，多一點了解JDBC還是有意義的。
JDBC 之所以能提供統(tǒng)一的API，是基于對所有數(shù)據(jù)庫的抽象及合理的定義。但是每個數(shù)據(jù)庫廠家畢竟是不一樣的，JDBC自然要屏蔽這種不一樣，它是如何做到的呢？這就是本文討論的 DriverManager, 它是一個橋接模式的完美應(yīng)用。其調(diào)用圖可表示為如下:
0：JDBC的編程模型
JDBC的編程模式是固定的，也就說操作步驟基本是一定的，如下：
public class JdbcDriverManagerTest {private static final String URL = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";private static final String USER = "root";private static final String PASSWORD = "123456";@Testpublic void testJdbcRaw() throws Exception {//1.加載驅(qū)動程序Class.forName("com.mysql.jdbc.Driver");//2. 獲得數(shù)據(jù)庫連接Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);//3.操作數(shù)據(jù)庫，實現(xiàn)增刪改查, 連接模式有2種: createStatement / prepareStatementStatement stmt = conn.createStatement();// PreparedStatement ptmt = conn.prepareStatement(sql); //預(yù)編譯SQL，減少sql執(zhí)行 //預(yù)編譯ResultSet rs = stmt.executeQuery("SELECT username, age FROM user");//如果有數(shù)據(jù)，rs.next()返回truewhile(rs.next()){System.out.println(rs.getString("username")+" 年齡："+rs.getInt("age"));}// 4. 關(guān)閉連接conn.close();}}
所以，總體來說，就是4個步驟:
1. 加載驅(qū)動程序;
2. 獲得數(shù)據(jù)庫連接;
3. 操作數(shù)據(jù)庫，實現(xiàn)增刪改查, 連接模式有2種: createStatement / prepareStatement;
4. 關(guān)閉數(shù)據(jù)庫連接;
有同學可能要說了，這么簡單的事，有什么好分析的？
我們要分析的點：
1. 驅(qū)動是如何加載的？
2. 連接是如何獲取的？
3. 數(shù)據(jù)操作是如何傳遞給數(shù)據(jù)庫的？
4. 連接是如何關(guān)閉的？
其實可以看出就是針對每個功能，我們都來問個如何實現(xiàn)就行了。
1. 驅(qū)動是如何加載的？
如果我們不考慮統(tǒng)一各數(shù)據(jù)庫的統(tǒng)一性，比如需要創(chuàng)建一個 mysql 的連接，那么我們只需要將mysql 的連接工具類，new一個對象出來就可以了。然而，jdbc卻是不可以這么干的，因為它要成為一種標準。實現(xiàn)很簡單，直接通過一個反射方法，就可以加載驅(qū)動了，那么具體是如何加載的呢？
以mysql 為例，使用反射方法去找到 驅(qū)動類 Class.forName("com.mysql.jdbc.Driver"); 所以，如何驅(qū)動起來，也是這個驅(qū)動類應(yīng)該做的事了。
// mysql 的驅(qū)動類如下// 重點1: 該驅(qū)動類必須實現(xiàn) java.sql.Driver 接口public class Driver extends NonRegisteringDriver implements java.sql.Driver {//// Register ourselves with the DriverManager//static {try {// 重點2: 必須在加載時，就將自身注冊到 DriverManager 中java.sql.DriverManager.registerDriver(new Driver());} catch (SQLException E) {throw new RuntimeException("Can't register driver!");}}/*** Construct a new driver and register it with DriverManager** @throws SQLException* if a database error occurs.*/public Driver() throws SQLException {// Required for Class.forName().newInstance()}}// java.sql.DriverManager#registerDriver/*** Registers the given driver with the {@code DriverManager}.* A newly-loaded driver class should call* the method {@code registerDriver} to make itself* known to the {@code DriverManager}. If the driver is currently* registered, no action is taken.** @param driver the new JDBC Driver that is to be registered with the* {@code DriverManager}* @exception SQLException if a database access error occurs* @exception NullPointerException if {@code driver} is null*/public static synchronized void registerDriver(java.sql.Driver driver)throws SQLException {// driverAction 為null, 連接建立后不做任何事, 有的數(shù)據(jù)庫需要進一步的操作registerDriver(driver, null);}/*** Registers the given driver with the {@code DriverManager}.* A newly-loaded driver class should call* the method {@code registerDriver} to make itself* known to the {@code DriverManager}. If the driver is currently* registered, no action is taken.** @param driver the new JDBC Driver that is to be registered with the* {@code DriverManager}* @param da the {@code DriverAction} implementation to be used when* {@code DriverManager#deregisterDriver} is called* @exception SQLException if a database access error occurs* @exception NullPointerException if {@code driver} is null* @since 1.8*/public static synchronized void registerDriver(java.sql.Driver driver,DriverAction da)throws SQLException {/* Register the driver if it has not already been added to our list */if(driver != null) {// CopyOnWriteArrayList<DriverInfo> registeredDrivers// 使用線程安全的容器來存放驅(qū)動，對于讀多寫少的場景，CopyOnWriteArrayList 是比較擅長的// 多次注冊不影響結(jié)果// 使用 DriverInfo 將 Driver 包裝起來registeredDrivers.addIfAbsent(new DriverInfo(driver, da));} else {// This is for compatibility with the original DriverManagerthrow new NullPointerException();}println("registerDriver: " + driver);}
這樣，mysql 的驅(qū)動就注冊到 DriverManager 中了，也就是可以接受 DriverManager 的管理了，需要注意的是，這里的類加載是特別的，它是違背“雙親委托加載模型”的一個案例，使用的是 contextClassLoader 進行加載驅(qū)動的。接下來我們要講的統(tǒng)一的API獲取數(shù)據(jù)庫連接。
2. 如何獲取數(shù)據(jù)庫連接？
通過注冊的方式，我已經(jīng)將數(shù)據(jù)庫的實例，交給了 DriverManager, 此時再要獲取數(shù)據(jù)庫連接，也就只需要問 DriverManager 要就行了。
我們以一個時序圖總覽全局：
// java.sql.DriverManager#getConnection(java.lang.String, java.lang.String, java.lang.String)/*** Attempts to establish a connection to the given database URL.* The <code>DriverManager</code> attempts to select an appropriate driver from* the set of registered JDBC drivers.*<p>* <B>Note:</B> If the {@code user} or {@code password} property are* also specified as part of the {@code url}, it is* implementation-defined as to which value will take precedence.* For maximum portability, an application should only specify a* property once.** @param url a database url of the form* <code>jdbc:<em>subprotocol</em>:<em>subname</em></code>* @param user the database user on whose behalf the connection is being* made* @param password the user's password* @return a connection to the URL* @exception SQLException if a database access error occurs or the url is* {@code null}* @throws SQLTimeoutException when the driver has determined that the* timeout value specified by the {@code setLoginTimeout} method* has been exceeded and has at least tried to cancel the* current database connection attempt*/// CallerSensitive 是為了避免獲取反射獲取實例時忽略該調(diào)用棧@CallerSensitivepublic static Connection getConnection(String url,String user, String password) throws SQLException {java.util.Properties info = new java.util.Properties();if (user != null) {info.put("user", user);}if (password != null) {info.put("password", password);}// 統(tǒng)一將必要信息封裝到 Properties 中，方便各自的驅(qū)動按需獲取return (getConnection(url, info, Reflection.getCallerClass()));}// Worker method called by the public getConnection() methods.private static Connection getConnection(String url, java.util.Properties info, Class<?> caller) throws SQLException {/** When callerCl is null, we should check the application's* (which is invoking this class indirectly)* classloader, so that the JDBC driver class outside rt.jar* can be loaded from here.*/// callerCL 可能為空，因為加載不到外部調(diào)用的類，此處違反了 雙親委派模型ClassLoader callerCL = caller != null ? caller.getClassLoader() : null;synchronized(DriverManager.class) {// synchronize loading of the correct classloader.if (callerCL == null) {// 通過 ContextClassLoader 進行加載callerCL = Thread.currentThread().getContextClassLoader();}}if(url == null) {throw new SQLException("The url cannot be null", "08001");}println("DriverManager.getConnection(\"" + url + "\")");// Walk through the loaded registeredDrivers attempting to make a connection.// Remember the first exception that gets raised so we can reraise it.SQLException reason = null;for(DriverInfo aDriver : registeredDrivers) {// If the caller does not have permission to load the driver then// skip it.// 檢查 classloader 是否相同，從而確認是否可以進行加載if(isDriverAllowed(aDriver.driver, callerCL)) {try {println(" trying " + aDriver.driver.getClass().getName());// 其實是一個個驅(qū)動地嘗試連接，直到找到第1個可用的連接// 其實一般是通過 連接協(xié)議來自行判定的，稍后我們以 mysql 的連接示例看一下Connection con = aDriver.driver.connect(url, info);if (con != null) {// Success!println("getConnection returning " + aDriver.driver.getClass().getName());return (con);}} catch (SQLException ex) {if (reason == null) {reason = ex;}}} else {println(" skipping: " + aDriver.getClass().getName());}}// if we got here nobody could connect.if (reason != null) {println("getConnection failed: " + reason);throw reason;}println("getConnection: no suitable driver found for "+ url);throw new SQLException("No suitable driver found for "+ url, "08001");}// 檢查 driver 屬于 classLoader 的管理范圍private static boolean isDriverAllowed(Driver driver, ClassLoader classLoader) {boolean result = false;if(driver != null) {Class<?> aClass = null;try {aClass = Class.forName(driver.getClass().getName(), true, classLoader);} catch (Exception ex) {result = false;}result = ( aClass == driver.getClass() ) ? true : false;}return result;}
DriverManager 通過遍歷所有驅(qū)動列表的形式，查找是否是某種類型的數(shù)據(jù)庫操作。雖然看起來好像有點費事，但是畢竟是做通用的框架，這樣做可以保證正確性，況且?guī)状握{(diào)用對性能影響也不大。雖然各驅(qū)動可以自行處理或拒絕某協(xié)議請求，但是一般都是以url前綴作為判斷接受與否的。我們來看下 mysql 如何處理？
// Mysql 的實現(xiàn)中是以 NonRegisteringDriver 作為實現(xiàn)類的// com.mysql.jdbc.NonRegisteringDriver#connect// 根據(jù) url 的和各屬性配置信息，創(chuàng)建一個真實的連接到mysql的網(wǎng)絡(luò)通道// url格式如: jdbc:mysql://host:port/databasepublic java.sql.Connection connect(String url, Properties info) throws SQLException {if (url != null) {// 負載均衡式訪問mysql, jdbc:mysql:loadbalance://if (StringUtils.startsWithIgnoreCase(url, LOADBALANCE_URL_PREFIX)) {return connectLoadBalanced(url, info);}// 多副本式訪問mysql, jdbc:mysql:replication://else if (StringUtils.startsWithIgnoreCase(url, REPLICATION_URL_PREFIX)) {return connectReplicationConnection(url, info);}}Properties props = null;// 解析各屬性，解析不正確，則說明不是標準的mysql協(xié)議請求if ((props = parseURL(url, info)) == null) {return null;}// 以下處理只針對一個 mysql-host 的情況處理if (!"1".equals(props.getProperty(NUM_HOSTS_PROPERTY_KEY))) {return connectFailover(url, info);}try {// 這個就是 mysql 的底層的連接的實現(xiàn)了// 大概就是按照mysql的協(xié)議，打開一個socket連接之類的，我們可以稍微看看Connection newConn = com.mysql.jdbc.ConnectionImpl.getInstance(host(props), port(props), props, database(props), url);return newConn;} catch (SQLException sqlEx) {// Don't wrap SQLExceptions, throw// them un-changed.throw sqlEx;} catch (Exception ex) {SQLException sqlEx = SQLError.createSQLException(Messages.getString("NonRegisteringDriver.17") + ex.toString() + Messages.getString("NonRegisteringDriver.18"),SQLError.SQL_STATE_UNABLE_TO_CONNECT_TO_DATASOURCE, null);sqlEx.initCause(ex);throw sqlEx;}}// 解析 url 的各項參數(shù)，全組裝到 urlProps 中返回// 相比于普通的簡單前綴判定多了些工作public Properties parseURL(String url, Properties defaults) throws java.sql.SQLException {Properties urlProps = (defaults != null) ? new Properties(defaults) : new Properties();if (url == null) {return null;}// 連接協(xié)議驗證:if (!StringUtils.startsWithIgnoreCase(url, URL_PREFIX) && !StringUtils.startsWithIgnoreCase(url, MXJ_URL_PREFIX)&& !StringUtils.startsWithIgnoreCase(url, LOADBALANCE_URL_PREFIX) && !StringUtils.startsWithIgnoreCase(url, REPLICATION_URL_PREFIX)) {return null;}// ... 解析各附加參數(shù)及配置文件參數(shù) ...return urlProps;}// 創(chuàng)建連接到 mysql-server/*** Creates a connection instance -- We need to provide factory-style methods* so we can support both JDBC3 (and older) and JDBC4 runtimes, otherwise* the class verifier complains when it tries to load JDBC4-only interface* classes that are present in JDBC4 method signatures.*/protected static Connection getInstance(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url)throws SQLException {// 此處會檢測 java.sql.NClobif (!Util.isJdbc4()) {return new ConnectionImpl(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url);}// 所以我們分析這個創(chuàng)建方式// 使用 com.mysql.jdbc.JDBC4Connection 新建一個實例返回// JDBC_4_CONNECTION_CTOR 是 JDBC4Connection 的構(gòu)造方法return (Connection) Util.handleNewInstance(JDBC_4_CONNECTION_CTOR, new Object[] { hostToConnectTo, Integer.valueOf(portToConnectTo), info,databaseToConnectTo, url }, null);}// com.mysql.jdbc.JDBC4Connection#JDBC4Connectionpublic JDBC4Connection(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) throws SQLException {// 父類將會創(chuàng)建到mysql-server 的連接super(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url);}
JDBC4Connection 類繼承圖如下:
ConnectionImpl負責建立連接到mysql-server，它主要處理各種連接準備和異常處理
// com.mysql.jdbc.ConnectionImpl/*** Creates a connection to a MySQL Server.** @param hostToConnectTo* the hostname of the database server* @param portToConnectTo* the port number the server is listening on* @param info* a Properties[] list holding the user and password* @param databaseToConnectTo* the database to connect to* @param url* the URL of the connection* @param d* the Driver instantation of the connection* @exception SQLException* if a database access error occurs*/public ConnectionImpl(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) throws SQLException {this.connectionCreationTimeMillis = System.currentTimeMillis();...try {// 元數(shù)據(jù)信息初始化this.dbmd = getMetaData(false, false);initializeSafeStatementInterceptors();// 創(chuàng)建io到mysqlcreateNewIO(false);unSafeStatementInterceptors();} catch (SQLException ex) {cleanup(ex);// don't clobber SQL exceptionsthrow ex;} catch (Exception ex) {cleanup(ex);... 封裝錯誤信息 ...throw sqlEx;}NonRegisteringDriver.trackConnection(this);}// com.mysql.jdbc.ConnectionImpl#createNewIO/*** Creates an IO channel to the server** @param isForReconnect* is this request for a re-connect* @return a new MysqlIO instance connected to a server* @throws SQLException* if a database access error occurs* @throws CommunicationsException*/public void createNewIO(boolean isForReconnect) throws SQLException {synchronized (getConnectionMutex()) {// Synchronization Not needed for *new* connections, but defintely for connections going through fail-over, since we might get the new connection up// and running *enough* to start sending cached or still-open server-side prepared statements over to the backend before we get a chance to// re-prepare them...Properties mergedProps = exposeAsProperties(this.props);// 非高可用狀態(tài)，只連接一次，失敗即失敗if (!getHighAvailability()) {connectOneTryOnly(isForReconnect, mergedProps);return;}connectWithRetries(isForReconnect, mergedProps);}}// 連接到mysql-server, 不重試private void connectOneTryOnly(boolean isForReconnect, Properties mergedProps) throws SQLException {Exception connectionNotEstablishedBecause = null;try {// 核心連接操作，實例放到 this.io 中coreConnect(mergedProps);this.connectionId = this.io.getThreadId();this.isClosed = false;...this.io.setStatementInterceptors(this.statementInterceptors);// Server properties might be different from previous connection, so initialize again...initializePropsFromServer();...return;} catch (Exception EEE) {... 異常資源處理throw chainedEx;}}// 真正的連接動作，將連接實例體現(xiàn)到 this.io 中private void coreConnect(Properties mergedProps) throws SQLException, IOException {int newPort = 3306;String newHost = "localhost";String protocol = mergedProps.getProperty(NonRegisteringDriver.PROTOCOL_PROPERTY_KEY);// 通常protocol 為空if (protocol != null) {// "new" style URLif ("tcp".equalsIgnoreCase(protocol)) {newHost = normalizeHost(mergedProps.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY));newPort = parsePortNumber(mergedProps.getProperty(NonRegisteringDriver.PORT_PROPERTY_KEY, "3306"));} else if ("pipe".equalsIgnoreCase(protocol)) {setSocketFactoryClassName(NamedPipeSocketFactory.class.getName());String path = mergedProps.getProperty(NonRegisteringDriver.PATH_PROPERTY_KEY);if (path != null) {mergedProps.setProperty(NamedPipeSocketFactory.NAMED_PIPE_PROP_NAME, path);}} else {// normalize for all unknown protocolsnewHost = normalizeHost(mergedProps.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY));newPort = parsePortNumber(mergedProps.getProperty(NonRegisteringDriver.PORT_PROPERTY_KEY, "3306"));}} else {String[] parsedHostPortPair = NonRegisteringDriver.parseHostPortPair(this.hostPortPair);newHost = parsedHostPortPair[NonRegisteringDriver.HOST_NAME_INDEX];newHost = normalizeHost(newHost);if (parsedHostPortPair[NonRegisteringDriver.PORT_NUMBER_INDEX] != null) {newPort = parsePortNumber(parsedHostPortPair[NonRegisteringDriver.PORT_NUMBER_INDEX]);}}this.port = newPort;this.host = newHost;// reset max-rows to default valuethis.sessionMaxRows = -1;// MysqlIO 承載連接this.io = new MysqlIO(newHost, newPort, mergedProps, getSocketFactoryClassName(), getProxy(), getSocketTimeout(),this.largeRowSizeThreshold.getValueAsInt());// 連接到數(shù)據(jù)庫，以測試連接的有效性this.io.doHandshake(this.user, this.password, this.database);if (versionMeetsMinimum(5, 5, 0)) {// error messages are returned according to character_set_results which, at this point, is set from the response packetthis.errorMessageEncoding = this.io.getEncodingForHandshake();}}
MysqlIO 類是專門負責與mysql-server 進行網(wǎng)絡(luò)交互的一個工具類，它基于Socket的長鏈接進行交互，綁定輸入輸出流等。其構(gòu)造方法如下:
/*** Constructor: Connect to the MySQL server and setup a stream connection.** @param host* the hostname to connect to* @param port* the port number that the server is listening on* @param props* the Properties from DriverManager.getConnection()* @param socketFactoryClassName* the socket factory to use* @param conn* the Connection that is creating us* @param socketTimeout* the timeout to set for the socket (0 means no* timeout)** @throws IOException* if an IOException occurs during connect.* @throws SQLException* if a database access error occurs.*/public MysqlIO(String host, int port, Properties props, String socketFactoryClassName, MySQLConnection conn, int socketTimeout,int useBufferRowSizeThreshold) throws IOException, SQLException {// JDBC4Connectionthis.connection = conn;if (this.connection.getEnablePacketDebug()) {this.packetDebugRingBuffer = new LinkedList<StringBuffer>();}this.traceProtocol = this.connection.getTraceProtocol();this.useAutoSlowLog = this.connection.getAutoSlowLog();this.useBufferRowSizeThreshold = useBufferRowSizeThreshold;this.useDirectRowUnpack = this.connection.getUseDirectRowUnpack();this.logSlowQueries = this.connection.getLogSlowQueries();this.reusablePacket = new Buffer(INITIAL_PACKET_SIZE);this.sendPacket = new Buffer(INITIAL_PACKET_SIZE);this.port = port;this.host = host;// com.mysql.jdbc.StandardSocketFactorythis.socketFactoryClassName = socketFactoryClassName;// 創(chuàng)建socketFactory 實例this.socketFactory = createSocketFactory();this.exceptionInterceptor = this.connection.getExceptionInterceptor();try {// 創(chuàng)建真實的socket連接到 mysql-server, 與遠程進行網(wǎng)絡(luò)IO通信this.mysqlConnection = this.socketFactory.connect(this.host, this.port, props);if (socketTimeout != 0) {try {this.mysqlConnection.setSoTimeout(socketTimeout);} catch (Exception ex) {/* Ignore if the platform does not support it */}}this.mysqlConnection = this.socketFactory.beforeHandshake();// 轉(zhuǎn)換輸入流到 this.mysqlInput 中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);}// 轉(zhuǎn)換輸出流到 this.mysqlOutput 中this.mysqlOutput = new BufferedOutputStream(this.mysqlConnection.getOutputStream(), 16384);this.isInteractiveClient = this.connection.getInteractiveClient();this.profileSql = this.connection.getProfileSql();this.autoGenerateTestcaseScript = this.connection.getAutoGenerateTestcaseScript();this.needToGrabQueryFromPacket = (this.profileSql || this.logSlowQueries || this.autoGenerateTestcaseScript);if (this.connection.getUseNanosForElapsedTime() && Util.nanoTimeAvailable()) {this.useNanosForElapsedTime = true;this.queryTimingUnits = Messages.getString("Nanoseconds");} else {this.queryTimingUnits = Messages.getString("Milliseconds");}if (this.connection.getLogSlowQueries()) {calculateSlowQueryThreshold();}} catch (IOException ioEx) {throw SQLError.createCommunicationsException(this.connection, 0, 0, ioEx, getExceptionInterceptor());}}
3. 如何執(zhí)行sql操作
主要有兩種方式, statement 和 prepareStatement. PreparedStatement繼承自Statement，兩者都是接口。區(qū)別是：PreparedStatement是預(yù)編譯的(mysql提供的能力)，比Statement效率高，可以使用占位符，可防止SQL注入。
過程比較冗長，還是以一個時序圖來總覽下：
//獲取預(yù)處理對象statement = getConnection().prepareStatement(sql);statement.executeUpdate(sql);// com.mysql.jdbc.ConnectionImpl#prepareStatement(java.lang.String)/*** A SQL statement with or without IN parameters can be pre-compiled and* stored in a PreparedStatement object. This object can then be used to* efficiently execute this statement multiple times.* <p>* <B>Note:</B> This method is optimized for handling parametric SQL statements that benefit from precompilation if the driver supports precompilation. In* this case, the statement is not sent to the database until the PreparedStatement is executed. This has no direct effect on users; however it does affect* which method throws certain java.sql.SQLExceptions* </p>* <p>* MySQL does not support precompilation of statements, so they are handled by the driver.* </p>** @param sql* a SQL statement that may contain one or more '?' IN parameter* placeholders* @return a new PreparedStatement object containing the pre-compiled* statement.* @exception SQLException* if a database access error occurs.*/public java.sql.PreparedStatement prepareStatement(String sql) throws SQLException {return prepareStatement(sql, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY);}// com.mysql.jdbc.ConnectionImpl#prepareStatement(java.lang.String, int, int)/*** JDBC 2.0 Same as prepareStatement() above, but allows the default result* set type and result set concurrency type to be overridden.** @param sql* the SQL query containing place holders* @param resultSetType* a result set type, see ResultSet.TYPE_XXX* @param resultSetConcurrency* a concurrency type, see ResultSet.CONCUR_XXX* @return a new PreparedStatement object containing the pre-compiled SQL* statement* @exception SQLException* if a database-access error occurs.*/public java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {synchronized (getConnectionMutex()) {// 檢查連接是否已關(guān)閉checkClosed();//// FIXME: Create warnings if can't create results of the given type or concurrency//PreparedStatement pStmt = null;boolean canServerPrepare = true;// 解析 ? 處理String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql) : sql;if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);}if (this.useServerPreparedStmts && canServerPrepare) {if (this.getCachePreparedStatements()) {synchronized (this.serverSideStatementCache) {pStmt = (com.mysql.jdbc.ServerPreparedStatement) this.serverSideStatementCache.remove(sql);if (pStmt != null) {((com.mysql.jdbc.ServerPreparedStatement) pStmt).setClosed(false);pStmt.clearParameters();}if (pStmt == null) {try {pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql, this.database, resultSetType,resultSetConcurrency);if (sql.length() < getPreparedStatementCacheSqlLimit()) {((com.mysql.jdbc.ServerPreparedStatement) pStmt).isCached = true;}pStmt.setResultSetType(resultSetType);pStmt.setResultSetConcurrency(resultSetConcurrency);} catch (SQLException sqlEx) {// Punt, if necessaryif (getEmulateUnsupportedPstmts()) {pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);if (sql.length() < getPreparedStatementCacheSqlLimit()) {this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);}} else {throw sqlEx;}}}}} else {try {pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);pStmt.setResultSetType(resultSetType);pStmt.setResultSetConcurrency(resultSetConcurrency);} catch (SQLException sqlEx) {// Punt, if necessaryif (getEmulateUnsupportedPstmts()) {pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);} else {throw sqlEx;}}}} else {// 生成 PreparedStatementpStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);}return pStmt;}}
執(zhí)行更新操作，其實就是將statement中的sql與參數(shù)，根據(jù)協(xié)議要求，寫入遠程即可，如下：
// com.mysql.jdbc.PreparedStatement#executeUpdate()/*** Execute a SQL INSERT, UPDATE or DELETE statement. In addition, SQL* statements that return nothing such as SQL DDL statements can be* executed.** @return either the row count for INSERT, UPDATE or DELETE; or 0 for SQL* statements that return nothing.** @exception SQLException* if a database access error occurs*/public int executeUpdate() throws SQLException {return executeUpdate(true, false);}/** We need this variant, because ServerPreparedStatement calls this for* batched updates, which will end up clobbering the warnings and generated* keys we need to gather for the batch.*/protected int executeUpdate(boolean clearBatchedGeneratedKeysAndWarnings, boolean isBatch) throws SQLException {synchronized (checkClosed().getConnectionMutex()) {if (clearBatchedGeneratedKeysAndWarnings) {clearWarnings();this.batchedGeneratedKeys = null;}// 代入?yún)?shù)執(zhí)行return executeUpdate(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull, isBatch);}}// com.mysql.jdbc.PreparedStatement#executeUpdate(byte[][], java.io.InputStream[], boolean[], int[], boolean[], boolean)/*** Added to allow batch-updates** @param batchedParameterStrings* string values used in single statement* @param batchedParameterStreams* stream values used in single statement* @param batchedIsStream* flags for streams used in single statement* @param batchedStreamLengths* lengths of streams to be read.* @param batchedIsNull* flags for parameters that are null** @return the update count** @throws SQLException* if a database error occurs*/protected int executeUpdate(byte[][] batchedParameterStrings, InputStream[] batchedParameterStreams, boolean[] batchedIsStream, int[] batchedStreamLengths,boolean[] batchedIsNull, boolean isReallyBatch) throws SQLException {synchronized (checkClosed().getConnectionMutex()) {MySQLConnection locallyScopedConn = this.connection;if (locallyScopedConn.isReadOnly()) {throw SQLError.createSQLException(Messages.getString("PreparedStatement.34") + Messages.getString("PreparedStatement.35"),SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());}if ((this.firstCharOfStmt == 'S') && isSelectQuery()) {throw SQLError.createSQLException(Messages.getString("PreparedStatement.37"), "01S03", getExceptionInterceptor());}implicitlyCloseAllOpenResults();ResultSetInternalMethods rs = null;// 轉(zhuǎn)換參數(shù)為 Buffer 形式Buffer sendPacket = fillSendPacket(batchedParameterStrings, batchedParameterStreams, batchedIsStream, batchedStreamLengths);String oldCatalog = null;if (!locallyScopedConn.getCatalog().equals(this.currentCatalog)) {oldCatalog = locallyScopedConn.getCatalog();locallyScopedConn.setCatalog(this.currentCatalog);}//// Only apply max_rows to selects//locallyScopedConn.setSessionMaxRows(-1);boolean oldInfoMsgState = false;if (this.retrieveGeneratedKeys) {oldInfoMsgState = locallyScopedConn.isReadInfoMsgEnabled();locallyScopedConn.setReadInfoMsgEnabled(true);}// 執(zhí)行更新rs = executeInternal(-1, sendPacket, false, false, null, isReallyBatch);if (this.retrieveGeneratedKeys) {locallyScopedConn.setReadInfoMsgEnabled(oldInfoMsgState);rs.setFirstCharOfQuery(this.firstCharOfStmt);}if (oldCatalog != null) {locallyScopedConn.setCatalog(oldCatalog);}this.results = rs;this.updateCount = rs.getUpdateCount();if (containsOnDuplicateKeyUpdateInSQL() && this.compensateForOnDuplicateKeyUpdate) {if (this.updateCount == 2 || this.updateCount == 0) {this.updateCount = 1;}}int truncatedUpdateCount = 0;if (this.updateCount > Integer.MAX_VALUE) {truncatedUpdateCount = Integer.MAX_VALUE;} else {truncatedUpdateCount = (int) this.updateCount;}this.lastInsertId = rs.getUpdateID();return truncatedUpdateCount;}}
4. 如何獲取查詢結(jié)果？
ResultSet 的處理。
// com.mysql.jdbc.StatementImpl#executeQuery/*** Execute a SQL statement that returns a single ResultSet** @param sql* typically a static SQL SELECT statement** @return a ResulSet that contains the data produced by the query** @exception SQLException* if a database access error occurs*/public java.sql.ResultSet executeQuery(String sql) throws SQLException {synchronized (checkClosed().getConnectionMutex()) {MySQLConnection locallyScopedConn = this.connection;this.retrieveGeneratedKeys = false;resetCancelledState();checkNullOrEmptyQuery(sql);boolean doStreaming = createStreamingResultSet();// Adjust net_write_timeout to a higher value if we're streaming result sets. More often than not, someone runs into an issue where they blow// net_write_timeout when using this feature, and if they're willing to hold a result set open for 30 seconds or more, one more round-trip isn't// going to hurt//// This is reset by RowDataDynamic.close().if (doStreaming && this.connection.getNetTimeoutForStreamingResults() > 0) {executeSimpleNonQuery(locallyScopedConn, "SET net_write_timeout=" + this.connection.getNetTimeoutForStreamingResults());}if (this.doEscapeProcessing) {// 避免sql注入Object escapedSqlResult = EscapeProcessor.escapeSQL(sql, locallyScopedConn.serverSupportsConvertFn(), this.connection);if (escapedSqlResult instanceof String) {sql = (String) escapedSqlResult;} else {sql = ((EscapeProcessorResult) escapedSqlResult).escapedSql;}}char firstStatementChar = StringUtils.firstAlphaCharUc(sql, findStartOfStatement(sql));if (sql.charAt(0) == '/') {if (sql.startsWith(PING_MARKER)) {doPingInstead();return this.results;}}checkForDml(sql, firstStatementChar);implicitlyCloseAllOpenResults();CachedResultSetMetaData cachedMetaData = null;if (useServerFetch()) {this.results = createResultSetUsingServerFetch(sql);return this.results;}CancelTask timeoutTask = null;String oldCatalog = null;try {if (locallyScopedConn.getEnableQueryTimeouts() && this.timeoutInMillis != 0 && locallyScopedConn.versionMeetsMinimum(5, 0, 0)) {timeoutTask = new CancelTask(this);locallyScopedConn.getCancelTimer().schedule(timeoutTask, this.timeoutInMillis);}if (!locallyScopedConn.getCatalog().equals(this.currentCatalog)) {oldCatalog = locallyScopedConn.getCatalog();locallyScopedConn.setCatalog(this.currentCatalog);}//// Check if we have cached metadata for this query...//Field[] cachedFields = null;if (locallyScopedConn.getCacheResultSetMetadata()) {cachedMetaData = locallyScopedConn.getCachedMetaData(sql);if (cachedMetaData != null) {cachedFields = cachedMetaData.fields;}}locallyScopedConn.setSessionMaxRows(this.maxRows);statementBegins();this.results = locallyScopedConn.execSQL(this, sql, this.maxRows, null, this.resultSetType, this.resultSetConcurrency, doStreaming,this.currentCatalog, cachedFields);if (timeoutTask != null) {if (timeoutTask.caughtWhileCancelling != null) {throw timeoutTask.caughtWhileCancelling;}timeoutTask.cancel();locallyScopedConn.getCancelTimer().purge();timeoutTask = null;}synchronized (this.cancelTimeoutMutex) {if (this.wasCancelled) {SQLException cause = null;if (this.wasCancelledByTimeout) {cause = new MySQLTimeoutException();} else {cause = new MySQLStatementCancelledException();}resetCancelledState();throw cause;}}} finally {this.statementExecuting.set(false);if (timeoutTask != null) {timeoutTask.cancel();locallyScopedConn.getCancelTimer().purge();}if (oldCatalog != null) {locallyScopedConn.setCatalog(oldCatalog);}}// lastInsertIdthis.lastInsertId = this.results.getUpdateID();if (cachedMetaData != null) {locallyScopedConn.initializeResultsMetadataFromCache(sql, cachedMetaData, this.results);} else {if (this.connection.getCacheResultSetMetadata()) {locallyScopedConn.initializeResultsMetadataFromCache(sql, null /* will be created */, this.results);}}return this.results;}}// 獲取結(jié)果通過 ResulSet.next()/*** A ResultSet is initially positioned before its first row, the first call* to next makes the first row the current row; the second call makes the* second row the current row, etc.** <p>* If an input stream from the previous row is open, it is implicitly closed. The ResultSet's warning chain is cleared when a new row is read* </p>** @return true if the new current is valid; false if there are no more rows** @exception SQLException* if a database access error occurs*/public boolean next() throws SQLException {synchronized (checkClosed().getConnectionMutex()) {if (this.onInsertRow) {this.onInsertRow = false;}if (this.doingUpdates) {this.doingUpdates = false;}boolean b;// 是否有數(shù)據(jù)if (!reallyResult()) {throw SQLError.createSQLException(Messages.getString("ResultSet.ResultSet_is_from_UPDATE._No_Data_115"), SQLError.SQL_STATE_GENERAL_ERROR,getExceptionInterceptor());}// 可以在數(shù)據(jù)不使用前觸發(fā) closeOpenStreams()// BufferRow 會處理事務(wù)，ByteArrayRow 則會空處理if (this.thisRow != null) {this.thisRow.closeOpenStreams();}if (this.rowData.size() == 0) {b = false;} else {// 通過 RowDataStatic 進行數(shù)據(jù)迭代this.thisRow = this.rowData.next();// 沒有數(shù)據(jù)了，返回falseif (this.thisRow == null) {b = false;} else {clearWarnings();b = true;}}// 設(shè)置位置描述信息setRowPositionValidity();return b;}}// com.mysql.jdbc.RowDataStatic#nextpublic ResultSetRow next() throws SQLException {// 移動到下一個數(shù)據(jù)點即可this.index++;if (this.index < this.rows.size()) {ResultSetRow row = this.rows.get(this.index);return row.setMetadata(this.metadata);}return null;}
5. 如何關(guān)閉數(shù)據(jù)庫連接？
這自然了也對應(yīng)的數(shù)據(jù)庫驅(qū)動實現(xiàn)的東西。
// com.mysql.jdbc.ConnectionImpl#close/*** In some cases, it is desirable to immediately release a Connection's* database and JDBC resources instead of waiting for them to be* automatically released (cant think why off the top of my head) <B>Note:</B>* A Connection is automatically closed when it is garbage collected.* Certain fatal errors also result in a closed connection.** @exception SQLException* if a database access error occurs*/public void close() throws SQLException {synchronized (getConnectionMutex()) {// 關(guān)閉前如果有攔截器，先調(diào)用攔截器處理if (this.connectionLifecycleInterceptors != null) {new IterateBlock<Extension>(this.connectionLifecycleInterceptors.iterator()) {@Overridevoid forEach(Extension each) throws SQLException {((ConnectionLifecycleInterceptor) each).close();}}.doForAll();}realClose(true, true, false, null);}}// com.mysql.jdbc.ConnectionImpl#realClose/*** Closes connection and frees resources.** @param calledExplicitly* is this being called from close()* @param issueRollback* should a rollback() be issued?* @throws SQLException* if an error occurs*/public void realClose(boolean calledExplicitly, boolean issueRollback, boolean skipLocalTeardown, Throwable reason) throws SQLException {SQLException sqlEx = null;if (this.isClosed()) {return;}this.forceClosedReason = reason;try {if (!skipLocalTeardown) {if (!getAutoCommit() && issueRollback) {try {rollback();} catch (SQLException ex) {sqlEx = ex;}}// 埋點上報reportMetrics();if (getUseUsageAdvisor()) {if (!calledExplicitly) {String message = "Connection implicitly closed by Driver. You should call Connection.close() from your code to free resources more efficiently and avoid resource leaks.";this.eventSink.consumeEvent(new ProfilerEvent(ProfilerEvent.TYPE_WARN, "", this.getCatalog(), this.getId(), -1, -1, System.currentTimeMillis(), 0, Constants.MILLIS_I18N, null, this.pointOfOrigin, message));}long connectionLifeTime = System.currentTimeMillis() - this.connectionCreationTimeMillis;if (connectionLifeTime < 500) {String message = "Connection lifetime of < .5 seconds. You might be un-necessarily creating short-lived connections and should investigate connection pooling to be more efficient.";this.eventSink.consumeEvent(new ProfilerEvent(ProfilerEvent.TYPE_WARN, "", this.getCatalog(), this.getId(), -1, -1, System.currentTimeMillis(), 0, Constants.MILLIS_I18N, null, this.pointOfOrigin, message));}}try {// 關(guān)閉所有 statementcloseAllOpenStatements();} catch (SQLException ex) {sqlEx = ex;}if (this.io != null) {try {// 關(guān)閉io流，斷開與遠程的連接this.io.quit();} catch (Exception e) {}}} else {this.io.forceClose();}if (this.statementInterceptors != null) {for (int i = 0; i < this.statementInterceptors.size(); i++) {this.statementInterceptors.get(i).destroy();}}if (this.exceptionInterceptor != null) {this.exceptionInterceptor.destroy();}} finally {this.openStatements = null;if (this.io != null) {this.io.releaseResources();this.io = null;}this.statementInterceptors = null;this.exceptionInterceptor = null;ProfilerEventHandlerFactory.removeInstance(this);synchronized (getConnectionMutex()) {if (this.cancelTimer != null) {this.cancelTimer.cancel();}}this.isClosed = true;}if (sqlEx != null) {throw sqlEx;}}///*** Closes this statement, and frees resources.** @param calledExplicitly* was this called from close()?** @throws SQLException* if an error occurs*/protected void realClose(boolean calledExplicitly, boolean closeOpenResults) throws SQLException {MySQLConnection locallyScopedConn = this.connection;if (locallyScopedConn == null) {return; // already closed}synchronized (locallyScopedConn.getConnectionMutex()) {// additional check in case Statement was closed while current thread was waiting for lockif (this.isClosed) {return;}if (this.useUsageAdvisor) {if (!calledExplicitly) {String message = Messages.getString("Statement.63") + Messages.getString("Statement.64");this.eventSink.consumeEvent(new ProfilerEvent(ProfilerEvent.TYPE_WARN, "", this.currentCatalog, this.connectionId, this.getId(), -1, System.currentTimeMillis(), 0, Constants.MILLIS_I18N, null, this.pointOfOrigin, message));}}if (closeOpenResults) {closeOpenResults = !(this.holdResultsOpenOverClose || this.connection.getDontTrackOpenResources());}if (closeOpenResults) {if (this.results != null) {try {this.results.close();} catch (Exception ex) {}}if (this.generatedKeysResults != null) {try {this.generatedKeysResults.close();} catch (Exception ex) {}}closeAllOpenResults();}if (this.connection != null) {if (!this.connection.getDontTrackOpenResources()) {this.connection.unregisterStatement(this);}}this.isClosed = true;this.results = null;this.generatedKeysResults = null;this.connection = null;this.warningChain = null;this.openResults = null;this.batchedGeneratedKeys = null;this.localInfileInputStream = null;this.pingTarget = null;}}// com.mysql.jdbc.MysqlIO#quit/*** Log-off of the MySQL server and close the socket.** @throws SQLException*/final void quit() throws SQLException {try {// we're not going to read the response, fixes BUG#56979 Improper connection closing logic leads to TIME_WAIT sockets on servertry {if (!this.mysqlConnection.isClosed()) {try {// socket 輸入流關(guān)閉this.mysqlConnection.shutdownInput();} catch (UnsupportedOperationException ex) {// ignore, some sockets do not support this method}}} catch (IOException ioEx) {this.connection.getLog().logWarn("Caught while disconnecting...", ioEx);}Buffer packet = new Buffer(6);this.packetSequence = -1;this.compressedPacketSequence = -1;packet.writeByte((byte) MysqlDefs.QUIT);// 向遠程寫入退出標識后send(packet, packet.getPosition());} finally {// 強制關(guān)閉本地連接forceClose();}}// com.mysql.jdbc.MysqlIO#forceClose/*** Forcibly closes the underlying socket to MySQL.*/protected final void forceClose() {try {// 將所有socket資源放到 NetworkResources, 統(tǒng)一釋放getNetworkResources().forceClose();} finally {this.mysqlConnection = null;this.mysqlInput = null;this.mysqlOutput = null;}}// com.mysql.jdbc.NetworkResources#forceClose/*** Forcibly closes the underlying socket to MySQL.*/protected final void forceClose() {// 依次調(diào)用 close() 方法關(guān)閉流try {try {if (this.mysqlInput != null) {this.mysqlInput.close();}} finally {if (this.mysqlConnection != null && !this.mysqlConnection.isClosed() && !this.mysqlConnection.isInputShutdown()) {try {this.mysqlConnection.shutdownInput();} catch (UnsupportedOperationException ex) {// ignore, some sockets do not support this method}}}} catch (IOException ioEx) {// we can't do anything constructive about this}try {try {if (this.mysqlOutput != null) {this.mysqlOutput.close();}} finally {if (this.mysqlConnection != null && !this.mysqlConnection.isClosed() && !this.mysqlConnection.isOutputShutdown()) {try {this.mysqlConnection.shutdownOutput();} catch (UnsupportedOperationException ex) {// ignore, some sockets do not support this method}}}} catch (IOException ioEx) {// we can't do anything constructive about this}try {if (this.mysqlConnection != null) {this.mysqlConnection.close();}} catch (IOException ioEx) {// we can't do anything constructive about this}}
6. 其他數(shù)據(jù)庫驅(qū)動的注冊
sqlite 驅(qū)動類: org.sqlite.JDBC，協(xié)議前綴: jdbc:sqlite:
public class JDBC implements Driver{public static final String PREFIX = "jdbc:sqlite:";static {try {// 注冊驅(qū)動DriverManager.registerDriver(new JDBC());}catch (SQLException e) {e.printStackTrace();}}// 檢測是不是sqlite支持的協(xié)議，前綴驗證即可/*** Validates a URL* @param url* @return true if the URL is valid, false otherwise*/public static boolean isValidURL(String url) {return url != null && url.toLowerCase().startsWith(PREFIX);}/*** @see java.sql.Driver#connect(java.lang.String, java.util.Properties)*/public Connection connect(String url, Properties info) throws SQLException {return createConnection(url, info);}/*** Creates a new database connection to a given URL.* @param url the URL* @param prop the properties* @return a Connection object that represents a connection to the URL* @throws SQLException* @see java.sql.Driver#connect(java.lang.String, java.util.Properties)*/public static Connection createConnection(String url, Properties prop) throws SQLException {if (!isValidURL(url))return null;url = url.trim();return new SQLiteConnection(url, extractAddress(url), prop);}}
Hive 驅(qū)動類: org.apache.hive.jdbc.HiveDriver,協(xié)議前綴: jdbc:hive2://
public class HiveDriver implements Driver {static {try {java.sql.DriverManager.registerDriver(new HiveDriver());} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}// 驗證是否是支持的協(xié)議，判斷前綴即可/*** Checks whether a given url is in a valid format.** The current uri format is: jdbc:hive://[host[:port]]** jdbc:hive:// - run in embedded mode jdbc:hive://localhost - connect to* localhost default port (10000) jdbc:hive://localhost:5050 - connect to* localhost port 5050** TODO: - write a better regex. - decide on uri format*/public boolean acceptsURL(String url) throws SQLException {return Pattern.matches(Utils.URL_PREFIX + ".*", url);}/** As per JDBC 3.0 Spec (section 9.2)* "If the Driver implementation understands the URL, it will return a Connection object;* otherwise it returns null"*/public Connection connect(String url, Properties info) throws SQLException {return acceptsURL(url) ? new HiveConnection(url, info) : null;}}
DB2 驅(qū)動類: com.ibm.db2.jcc.DB2Driver, 協(xié)議前綴: jdbc:db2:// ;
Oracle 驅(qū)動類: oracle.jdbc.OracleDriver, 協(xié)議前綴: jdbc:oracle:thin: ;
7. jdbc 之后
jdbc設(shè)計確實是很成功的，定義了使用數(shù)據(jù)的規(guī)范，各廠商只需實現(xiàn)自己的驅(qū)動即可接入到j(luò)ava中。
然而，jdbc這樣的操作畢竟太過于模板化，如果在每個項目里反復寫這些模板代碼，那就是太傷了。所以，涌現(xiàn)出大量的orm框架，如: hibernates, mybatis. 將我們從模板代碼中解放出來。底層受益出jdbc的設(shè)計，高層高效服務(wù)于開發(fā)人員。
騰訊、阿里、滴滴后臺面試題匯總總結(jié) — （含答案）
面試：史上最全多線程面試題 ！
最新阿里內(nèi)推Java后端面試題
JVM難學？那是因為你沒認真看完這篇文章
關(guān)注作者微信公眾號 —《JAVA爛豬皮》
了解更多java后端架構(gòu)知識以及最新面試寶典
看完本文記得給作者點贊+在看哦~~~大家的支持，是作者源源不斷出文的動力
作者：等你歸去來
出處：https://www.cnblogs.com/yougewe/p/12460685.html
評論
圖片
表情