# Mybatis深入之獲取數據庫連接 ### 簡介 主要記錄Mybatis何時獲取數據庫連接以及獲取數據庫連接的過程。難點在于明白在使用Mybatis數據庫連接池情況下的數據庫連接的獲取過程。 ### 何時獲取數據庫連接 Mybatis只有在真正執行sql操作的時候才會去獲取數據庫連接。至于如何驗證： ### 不深入源碼 簡單來講就是有意將數據庫配置信息寫成、在一個sql執行過程中看哪一步拋數據庫連接異常。 ~~~ public static void main(String[] args) throws Exception { String mybatisConfigPath = "config/mybatis/mybatis.xml"; InputStream inputStream = Resources.getResourceAsStream(mybatisConfigPath); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); int count = (Integer)sqlSession.selectOne("org.alien.mybatis.samples.mapper.AuthorMapper.getAllAuthorsCount"); System.out.println(count); } ~~~ - 上面是一段Mybatis執行代碼 - 我們可以將Mybatis連接數據庫的信息有意寫錯 - 再DEBUG模式下一步一步調試看哪一步會拋異常 - 拋異常的那一步就是真正獲取數據庫連接的一步 異常信息：  ### 深入源碼 這里簡單提一下、具體后面會有。最有迷惑性的是覺得在`openSession()`的時候會獲取數據庫連接、其實不然： openSession()最終只是返回一個操作數據庫的會話、并不包含數據庫連接，DefaultSqlSession（這個是Mybatis初始化的時候返回的一個SqlSession）中的方法： ~~~ private SqlSession openSessionFromDataSource(ExecutorType execType, TransactionIsolationLevel level, boolean autoCommit) { Transaction tx = null; try { final Environment environment = configuration.getEnvironment(); final TransactionFactory transactionFactory = getTransactionFactoryFromEnvironment(environment); tx = transactionFactory.newTransaction(environment.getDataSource(), level, autoCommit); final Executor executor = configuration.newExecutor(tx, execType); return new DefaultSqlSession(configuration, executor, autoCommit); } catch (Exception e) { closeTransaction(tx); // may have fetched a connection so lets call close() throw ExceptionFactory.wrapException("Error opening session. Cause: " + e, e); } finally { ErrorContext.instance().reset(); } } ~~~ - 主要裝配DefaultSqlSession中執行Sql的Excutor、后面關于一個Sql完整的執行過程會有對其的詳細分析 顯然真正獲取數據庫連接的操作是在`sqlSession.selectOne("org.alien.mybatis.samples.mapper.AuthorMapper.getAllAuthorsCount");`進行的。 ### 獲取數據庫連接 在真正的獲取數據庫連接代碼之前、還有許多為sql執行而生的代碼、這里暫時忽略或者一些必要的說明、主要重心放在如何獲取數據庫連接。 書接上回、從前面執行sql代碼開始： ~~~ int count = (Integer)sqlSession.selectOne("org.alien.mybatis.samples.mapper.AuthorMapper.getAllAuthorsCount"); ~~~ 下圖是上面代碼一系列方法調用過程：  經過一系列調用到SimpleExecutor——》doQuery()： ~~~ public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException { Statement stmt = null; try { Configuration configuration = ms.getConfiguration(); //RoutingStatementHandler StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, resultHandler, boundSql); //MappedStatement、這一句是關鍵 stmt = prepareStatement(handler, ms.getStatementLog()); /* *參數： * stmt: PreparedStatementLogger * resultHandler: null */ return handler.<E>query(stmt, resultHandler); } finally { closeStatement(stmt); } } ~~~ - 上面經過一系列跳轉之后各個引用的實例我加了注釋、有興趣的可以自己跟一下、有時候可能要多跟幾遍 - 關鍵代碼`stmt=prepareStatement(handler,ms.getStatementLog());`、這一句根據方法名就能猜測、是根據Connection來獲取執行Sql的PrepareStatement - 但是到現在為止我們都沒有看到方法的參數中有關于數據庫連接的、在此方法中 SimpleExecutor——》prepareStatement(); ~~~ private Statement prepareStatement(StatementHandler handler, Log statementLog) throws SQLException { Statement stmt; //獲取數據庫連接。statementLog:org.apache.ibatis.loggin.slf4j.Slf4Impl Connection connection = getConnection(statementLog); //獲取執行Sql的Statement——PrepareStatementLogger //PrepareStatementLogger是PrepareStatement的代理、多了對使用Mybatis執行sql語句時記錄sql語句的功能 stmt = handler.prepare(connection); //將執行Sql需要的參數設置到PrepareStatement中。 handler.parameterize(stmt); return stmt; } ~~~ 其他的不關注、這里只看數據庫連接代碼：`Connection connection = getConnection(statementLog);`BaseExecutor——》getConnection(): ~~~ protected Connection getConnection(Log statementLog) throws SQLException { //如果關于數據庫連接的日志記錄級別是DEBUG級別、則為獲取的Connection進行代理、新增日志記錄功能、這里不是重點。 if (statementLog.isDebugEnabled()) { return ConnectionLogger.newInstance(connection, statementLog, queryStack); } else { return connection; } } ~~~ - Mybatis中關于事務配置項的值是”JDBC”、所以從[Mybatis深入之事務管理 ](http://blog.csdn.net/crave_shy/article/details/46595391)知道這里的transaction其實是：JdbcTransaction - 最終到JdbcTransaction獲取連接的方法中 JdbcTransaction——》openConnection() ~~~ protected void openConnection() throws SQLException { if (log.isDebugEnabled()) { log.debug("Opening JDBC Connection"); } connection = dataSource.getConnection(); if (level != null) { connection.setTransactionIsolation(level.getLevel()); } setDesiredAutoCommit(autoCommmit); } ~~~ - 上面的dataSource從[ Mybatis深入之DataSource實例化過程 ](http://blog.csdn.net/crave_shy/article/details/46584803)知道當使用數據庫連接池的時候實例化的是PooledDataSource PooledDataSource——》getConnection()： ~~~ public Connection getConnection() throws SQLException { /* * 為理解方便、將原來代碼拆分如下： */ PooledConnection pooledConnection = popConnection(dataSource.getUsername(), dataSource.getPassword()); Connection connection = pooledConnection.getProxyConnection(); return connection ; //return popConnection(dataSource.getUsername(), dataSource.getPassword()).getProxyConnection(); } ~~~ - 從拆分代碼看分兩步 - 獲取數據庫真正連接 - 獲取真正數據庫連接的代理類作為最終返回結果、至于代理是做什么、后面繼續 PooledDataSource——》popConnection(): ~~~ private PooledConnection popConnection(String username, String password) throws SQLException { boolean countedWait = false; PooledConnection conn = null; long t = System.currentTimeMillis(); int localBadConnectionCount = 0; while (conn == null) { synchronized (state) { if (state.idleConnections.size() > 0) { // Pool has available connection conn = state.idleConnections.remove(0); if (log.isDebugEnabled()) { log.debug("Checked out connection " + conn.getRealHashCode() + " from pool."); } } else { // Pool does not have available connection if (state.activeConnections.size() < poolMaximumActiveConnections) { // Can create new connection conn = new PooledConnection(dataSource.getConnection(), this); @SuppressWarnings("unused") //used in logging, if enabled Connection realConn = conn.getRealConnection(); if (log.isDebugEnabled()) { log.debug("Created connection " + conn.getRealHashCode() + "."); } } else { // Cannot create new connection PooledConnection oldestActiveConnection = state.activeConnections.get(0); long longestCheckoutTime = oldestActiveConnection.getCheckoutTime(); if (longestCheckoutTime > poolMaximumCheckoutTime) { // Can claim overdue connection state.claimedOverdueConnectionCount++; state.accumulatedCheckoutTimeOfOverdueConnections += longestCheckoutTime; state.accumulatedCheckoutTime += longestCheckoutTime; state.activeConnections.remove(oldestActiveConnection); if (!oldestActiveConnection.getRealConnection().getAutoCommit()) { oldestActiveConnection.getRealConnection().rollback(); } conn = new PooledConnection(oldestActiveConnection.getRealConnection(), this); oldestActiveConnection.invalidate(); if (log.isDebugEnabled()) { log.debug("Claimed overdue connection " + conn.getRealHashCode() + "."); } } else { // Must wait try { if (!countedWait) { state.hadToWaitCount++; countedWait = true; } if (log.isDebugEnabled()) { log.debug("Waiting as long as " + poolTimeToWait + " milliseconds for connection."); } long wt = System.currentTimeMillis(); state.wait(poolTimeToWait); state.accumulatedWaitTime += System.currentTimeMillis() - wt; } catch (InterruptedException e) { break; } } } } if (conn != null) { if (conn.isValid()) { if (!conn.getRealConnection().getAutoCommit()) { conn.getRealConnection().rollback(); } conn.setConnectionTypeCode(assembleConnectionTypeCode(dataSource.getUrl(), username, password)); conn.setCheckoutTimestamp(System.currentTimeMillis()); conn.setLastUsedTimestamp(System.currentTimeMillis()); state.activeConnections.add(conn); state.requestCount++; state.accumulatedRequestTime += System.currentTimeMillis() - t; } else { if (log.isDebugEnabled()) { log.debug("A bad connection (" + conn.getRealHashCode() + ") was returned from the pool, getting another connection."); } state.badConnectionCount++; localBadConnectionCount++; conn = null; if (localBadConnectionCount > (poolMaximumIdleConnections + 3)) { if (log.isDebugEnabled()) { log.debug("PooledDataSource: Could not get a good connection to the database."); } throw new SQLException("PooledDataSource: Could not get a good connection to the database."); } } } } } if (conn == null) { if (log.isDebugEnabled()) { log.debug("PooledDataSource: Unknown severe error condition. The connection pool returned a null connection."); } throw new SQLException("PooledDataSource: Unknown severe error condition. The connection pool returned a null connection."); } return conn; } ~~~ - 先解釋一下邏輯、再關注具體的數據庫連接方法 1. 先看是否有空閑(idle)狀態下的PooledConnection對象，如果有，就直接返回一個可用的PooledConnection對象；否則進行第2步。 2. 查看活動狀態的PooledConnection池activeConnections是否已滿；如果沒有滿，則創建一個新的PooledConnection對象，然后放到activeConnections池中，然后返回此PooledConnection對象；否則進行第三步； 3. 看最先進入activeConnections池中的PooledConnection對象是否已經過期：如果已經過期，從activeConnections池中移除此對象，然后創建一個新的PooledConnection對象，添加到activeConnections中，然后將此對象返回；否則進行第4步。 4. 線程等待，循環2步 具體的創建數據庫連接代碼`conn = new PooledConnection(dataSource.getConnection(), this);` - 上面代碼中的dataSource為UnpooledDataSource、可以從[ Mybatis深入之DataSource實例化過程 ](http://blog.csdn.net/crave_shy/article/details/46584803)了解原因。 所以先要看UnpooledDataSource——getConnection() 經過一系列跳轉到同類如下方法： ~~~ private Connection doGetConnection(Properties properties) throws SQLException { initializeDriver(); Connection connection = DriverManager.getConnection(url, properties); configureConnection(connection); return connection; } ~~~ - 上面我們可以看到很熟悉的加載驅動、獲取數據庫連接 ### 補充 其實關于數據庫部分還有很多要寫的、比如數據庫連接池工作原理、數據庫連接何時關閉。這里暫不準備一次將所有的東都放在一起。覺得分開點更容易說清楚理解起來不是那么費勁。 后面會有一篇Mybatis數據庫連接池原理來分析它。 更多內容[Mybatis 目錄](http://blog.csdn.net/crave_shy/article/details/23932803)