{"action":"opened","issue":{"url":"https://api.github.com/repos/guevara/read-it-later/issues/1729","repository_url":"https://api.github.com/repos/guevara/read-it-later","labels_url":"https://api.github.com/repos/guevara/read-it-later/issues/1729/labels{/name}","comments_url":"https://api.github.com/repos/guevara/read-it-later/issues/1729/comments","events_url":"https://api.github.com/repos/guevara/read-it-later/issues/1729/events","html_url":"https://github.com/guevara/read-it-later/issues/1729","id":389758538,"node_id":"MDU6SXNzdWUzODk3NTg1Mzg=","number":1729,"title":"谈谈JDBC","user":{"login":"guevara","id":228309,"node_id":"MDQ6VXNlcjIyODMwOQ==","avatar_url":"https://avatars1.githubusercontent.com/u/228309?v=4","gravatar_id":"","url":"https://api.github.com/users/guevara","html_url":"https://github.com/guevara","followers_url":"https://api.github.com/users/guevara/followers","following_url":"https://api.github.com/users/guevara/following{/other_user}","gists_url":"https://api.github.com/users/guevara/gists{/gist_id}","starred_url":"https://api.github.com/users/guevara/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/guevara/subscriptions","organizations_url":"https://api.github.com/users/guevara/orgs","repos_url":"https://api.github.com/users/guevara/repos","events_url":"https://api.github.com/users/guevara/events{/privacy}","received_events_url":"https://api.github.com/users/guevara/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-12-11T13:14:21Z","updated_at":"2018-12-11T13:14:21Z","closed_at":null,"author_association":"OWNER","body":"谈谈JDBC<br>\n<br>\nhttps://ift.tt/2C42J5T<br>\n<br>\n<br>\n<br>\n\n<div>\n<div>\n    <p>JDBC是Java Database Connectivity的简称，它定义了一套访问数据库的规范和接口。但它自身不参与数据库访问的实现。因此对于目前存在的数据库（譬如Mysql、Oracle）来说，要么数据库制造商本身提供这些规范与接口的实现，要么社区提供这些实现。</p>\n\n<p>对于Mysql数据库来说，我们通常使用如下包来访问数据库：</p>\n\n<figure><pre><code data-lang=\"xml\"><span>&lt;dependency&gt;</span>\n    <span>&lt;groupId&gt;</span>mysql<span>&lt;/groupId&gt;</span>\n    <span>&lt;artifactId&gt;</span>mysql-connector-java<span>&lt;/artifactId&gt;</span>\n    <span>&lt;version&gt;</span>5.1.39<span>&lt;/version&gt;</span>\n<span>&lt;/dependency&gt;</span></code></pre></figure>\n\n<p>JDBC的类包含在<a href=\"https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html\">java.sql</a>和<a href=\"https://docs.oracle.com/javase/8/docs/api/javax/sql/package-summary.html\">javax.sql</a>。这里岔开一句，javax是sun公司提供的一个扩展包，提供原java包的一些扩展处理，随着时间发展，javax在很多处理上已经成为java核心架构的一部分，譬如javax的swing包。</p>\n\n<p>在对数据库进行操作时，我们需要先获取一个连接，如下：</p>\n\n<figure><pre><code data-lang=\"java\"><span>Connection</span> <span>conn</span> <span>=</span> <span>DriverManager</span><span>.</span><span>getConnection</span><span>(</span><span>\"jdbc:somejdbcvendor:other data needed by some jdbc vendor\"</span><span>,</span> <span>\"myLogin\"</span><span>,</span> <span>\"myPassword\"</span><span>);</span>\n\n<span>try</span> <span>{</span>\n    <span>//使用连接访问数据库</span>\n<span>}</span> <span>finally</span> <span>{</span>\n    <span>try</span> <span>{</span>\n        <span>//关闭连接</span>\n        <span>conn</span><span>.</span><span>close</span><span>();</span>\n    <span>}</span> <span>catch</span> <span>(</span><span>Throwable</span> <span>t</span><span>)</span> <span>{</span>\n        <span>//关闭连接失败, 失败处理</span>\n        <span>logger</span><span>.</span><span>warn</span><span>(</span><span>\"关闭数据库连接失败\"</span><span>,</span><span>e</span><span>);</span>\n    <span>}</span>\n<span>}</span></code></pre></figure>\n\n<p>对于Mysql数据库来说，获取连接格式如下：</p>\n\n<figure><pre><code data-lang=\"java\"><span>Connection</span> <span>conn</span> <span>=</span> <span>DriverManager</span><span>.</span><span>getConnection</span><span>(</span><span>\"jdbc:mysql://localhost:3306/test\"</span><span>,</span> <span>\"myLogin\"</span><span>,</span> <span>\"myPassword\"</span><span>);</span></code></pre></figure>\n\n<p>DriverManager是JDBC提供的驱动管理类，它负责根据参数中的连接url查找合适的驱动。对于诸如“jdbc:mysql:”这样的连接查找Mysql驱动，对于“jdbc:oracle:”这样的连接查找Oracle驱动。DriverManager.getConnection的核心逻辑如下：</p>\n\n<figure><pre><code data-lang=\"java\"><span>for</span><span>(</span><span>DriverInfo</span> <span>aDriver</span> <span>:</span> <span>registeredDrivers</span><span>)</span> <span>{</span>\n    <span>// 检查调用DriverManager.getConnection的代码是否有权限加载驱动</span>\n    <span>if</span><span>(</span><span>isDriverAllowed</span><span>(</span><span>aDriver</span><span>.</span><span>driver</span><span>,</span> <span>callerCL</span><span>))</span> <span>{</span>\n        <span>try</span> <span>{</span>\n            <span>println</span><span>(</span><span>\"    trying \"</span> <span>+</span> <span>aDriver</span><span>.</span><span>driver</span><span>.</span><span>getClass</span><span>().</span><span>getName</span><span>());</span>\n            <span>Connection</span> <span>con</span> <span>=</span> <span>aDriver</span><span>.</span><span>driver</span><span>.</span><span>connect</span><span>(</span><span>url</span><span>,</span> <span>info</span><span>);</span>\n            <span>if</span> <span>(</span><span>con</span> <span>!=</span> <span>null</span><span>)</span> <span>{</span>\n                <span>// 获取成功</span>\n                <span>println</span><span>(</span><span>\"getConnection returning \"</span> <span>+</span> <span>aDriver</span><span>.</span><span>driver</span><span>.</span><span>getClass</span><span>().</span><span>getName</span><span>());</span>\n                <span>return</span> <span>(</span><span>con</span><span>);</span>\n            <span>}</span>\n        <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>ex</span><span>)</span> <span>{</span>\n            <span>if</span> <span>(</span><span>reason</span> <span>==</span> <span>null</span><span>)</span> <span>{</span>\n                <span>reason</span> <span>=</span> <span>ex</span><span>;</span>\n            <span>}</span>\n        <span>}</span>\n\n    <span>}</span> <span>else</span> <span>{</span>\n        <span>println</span><span>(</span><span>\"    skipping: \"</span> <span>+</span> <span>aDriver</span><span>.</span><span>getClass</span><span>().</span><span>getName</span><span>());</span>\n    <span>}</span>\n\n<span>}</span>\n\n<span>//如果没有合适的驱动，则抛出异常</span>\n<span>if</span> <span>(</span><span>reason</span> <span>!=</span> <span>null</span><span>)</span>    <span>{</span>\n    <span>println</span><span>(</span><span>\"getConnection failed: \"</span> <span>+</span> <span>reason</span><span>);</span>\n    <span>throw</span> <span>reason</span><span>;</span>\n<span>}</span>\n\n<span>println</span><span>(</span><span>\"getConnection: no suitable driver found for \"</span><span>+</span> <span>url</span><span>);</span>\n<span>throw</span> <span>new</span> <span>SQLException</span><span>(</span><span>\"No suitable driver found for \"</span><span>+</span> <span>url</span><span>,</span> <span>\"08001\"</span><span>);</span></code></pre></figure>\n\n<p>可以看到，DriverManager只是遍历它的驱动列表registeredDrivers，检查每个驱动是否能处理该url，如果能处理则返回连接，否则检查完所有的驱动都没找到合适驱动则抛出异常。</p>\n\n<p>而registeredDrivers被定义为静态变量：</p>\n\n<figure><pre><code data-lang=\"java\"><span>private</span> <span>final</span> <span>static</span> <span>CopyOnWriteArrayList</span><span>&lt;</span><span>DriverInfo</span><span>&gt;</span> <span>registeredDrivers</span> <span>=</span> <span>new</span> <span>CopyOnWriteArrayList</span><span>&lt;&gt;();</span></code></pre></figure>\n\n<p>每个数据库驱动在加载的时候会调用DriverManager.registerDriver方法来将自身注册在DriverManager的registeredDrivers中。mysql-connector-java的驱动注册代码如下：</p>\n\n<figure><pre><code data-lang=\"java\"><span>package</span> <span>com</span><span>.</span><span>mysql</span><span>.</span><span>jdbc</span><span>;</span>\n\n<span>import</span> <span>java.sql.SQLException</span><span>;</span>\n\n<span>public</span> <span>class</span> <span>Driver</span> <span>extends</span> <span>NonRegisteringDriver</span> <span>implements</span> <span>java</span><span>.</span><span>sql</span><span>.</span><span>Driver</span> <span>{</span>\n<span>//</span>\n<span>// Register ourselves with the DriverManager</span>\n<span>//</span>\n<span>static</span> <span>{</span>\n    <span>try</span> <span>{</span>\n        <span>java</span><span>.</span><span>sql</span><span>.</span><span>DriverManager</span><span>.</span><span>registerDriver</span><span>(</span><span>new</span> <span>Driver</span><span>());</span>\n    <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>E</span><span>)</span> <span>{</span>\n        <span>throw</span> <span>new</span> <span>RuntimeException</span><span>(</span><span>\"Can't register driver!\"</span><span>);</span>\n    <span>}</span>\n<span>}</span>\n\n<span>/*\n * Construct a new driver and register it with DriverManager\n * \n * @throws SQLException\n *             if a database error occurs.\n */</span>\n<span>public</span> <span>Driver</span><span>()</span> <span>throws</span> <span>SQLException</span> <span>{</span>\n    <span>// Required for Class.forName().newInstance()</span>\n<span>}</span>\n<span>}</span></code></pre></figure>\n\n<p>通过DriverManager.getConnection获取到连接后，我们通过创建和执行Statement来对数据库进行增删改查。JDBC有如下三种Statement：</p>\n\n\n\n<p>下面分析下mysql-connect-java包的PreparedStatement创建过程。当我们调用conn.prepareStatement()方法时，它的核心逻辑如下：</p>\n\n<figure><pre><code data-lang=\"java\"><span>public</span> <span>java</span><span>.</span><span>sql</span><span>.</span><span>PreparedStatement</span> <span>prepareStatement</span><span>(</span><span>String</span> <span>sql</span><span>,</span> <span>int</span> <span>resultSetType</span><span>,</span> <span>int</span> <span>resultSetConcurrency</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>\n    <span>//获取连接互斥锁</span>\n    <span>synchronized</span> <span>(</span><span>getConnectionMutex</span><span>())</span> <span>{</span>\n        <span>//检查连接是否关闭</span>\n        <span>checkClosed</span><span>();</span>\n\n        <span>PreparedStatement</span> <span>pStmt</span> <span>=</span> <span>null</span><span>;</span>\n\n        <span>boolean</span> <span>canServerPrepare</span> <span>=</span> <span>true</span><span>;</span>\n\n        <span>//根据需要本地化sql语句</span>\n        <span>String</span> <span>nativeSql</span> <span>=</span> <span>getProcessEscapeCodesForPrepStmts</span><span>()</span> <span>?</span> <span>nativeSQL</span><span>(</span><span>sql</span><span>)</span> <span>:</span> <span>sql</span><span>;</span>\n\n        <span>//判断数据库服务器是否支持sql预处理</span>\n        <span>if</span> <span>(</span><span>this</span><span>.</span><span>useServerPreparedStmts</span> <span>&amp;&amp;</span> <span>getEmulateUnsupportedPstmts</span><span>())</span> <span>{</span>\n            <span>canServerPrepare</span> <span>=</span> <span>canHandleAsServerPreparedStatement</span><span>(</span><span>nativeSql</span><span>);</span>\n        <span>}</span>\n\n        <span>if</span> <span>(</span><span>this</span><span>.</span><span>useServerPreparedStmts</span> <span>&amp;&amp;</span> <span>canServerPrepare</span><span>)</span> <span>{</span>\n            <span>//检查连接是否启用PreparedStatement的缓存，启用则先在缓存中查找该sql对应的PreparedStatement，在缓存中找不到或者缓存没启用都会创建新的PreparedStatement</span>\n            <span>if</span> <span>(</span><span>this</span><span>.</span><span>getCachePreparedStatements</span><span>())</span> <span>{</span>\n                <span>synchronized</span> <span>(</span><span>this</span><span>.</span><span>serverSideStatementCache</span><span>)</span> <span>{</span>\n                    <span>pStmt</span> <span>=</span> <span>(</span><span>com</span><span>.</span><span>mysql</span><span>.</span><span>jdbc</span><span>.</span><span>ServerPreparedStatement</span><span>)</span> <span>this</span><span>.</span><span>serverSideStatementCache</span><span>.</span><span>remove</span><span>(</span><span>sql</span><span>);</span>\n\n                    <span>//在本地缓存中找到对应的PreparedStatement，则把它从缓存中移除，以便在本次调用中使用</span>\n                    <span>if</span> <span>(</span><span>pStmt</span> <span>!=</span> <span>null</span><span>)</span> <span>{</span>\n                        <span>((</span><span>com</span><span>.</span><span>mysql</span><span>.</span><span>jdbc</span><span>.</span><span>ServerPreparedStatement</span><span>)</span> <span>pStmt</span><span>).</span><span>setClosed</span><span>(</span><span>false</span><span>);</span>\n                        <span>pStmt</span><span>.</span><span>clearParameters</span><span>();</span>\n                    <span>}</span>\n\n                    <span>//在本地缓存中找不到对应的PreparedStatement，创建新的PreparedStatement</span>\n                    <span>if</span> <span>(</span><span>pStmt</span> <span>==</span> <span>null</span><span>)</span> <span>{</span>\n                        <span>try</span> <span>{</span>\n                            <span>//创建新的PreparedStatement，会在ServerPreparedStatement.getInstance中会发送语句到数据库服务器进行预处理</span>\n                            <span>pStmt</span> <span>=</span> <span>ServerPreparedStatement</span><span>.</span><span>getInstance</span><span>(</span><span>getMultiHostSafeProxy</span><span>(),</span> <span>nativeSql</span><span>,</span> <span>this</span><span>.</span><span>database</span><span>,</span> <span>resultSetType</span><span>,</span>\n                                    <span>resultSetConcurrency</span><span>);</span>\n                            <span>if</span> <span>(</span><span>sql</span><span>.</span><span>length</span><span>()</span> <span>&lt;</span> <span>getPreparedStatementCacheSqlLimit</span><span>())</span> <span>{</span>\n                                <span>((</span><span>com</span><span>.</span><span>mysql</span><span>.</span><span>jdbc</span><span>.</span><span>ServerPreparedStatement</span><span>)</span> <span>pStmt</span><span>).</span><span>isCached</span> <span>=</span> <span>true</span><span>;</span>\n                            <span>}</span>\n\n                            <span>pStmt</span><span>.</span><span>setResultSetType</span><span>(</span><span>resultSetType</span><span>);</span>\n                            <span>pStmt</span><span>.</span><span>setResultSetConcurrency</span><span>(</span><span>resultSetConcurrency</span><span>);</span>\n                        <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>sqlEx</span><span>)</span> <span>{</span>\n                            <span>if</span> <span>(</span><span>getEmulateUnsupportedPstmts</span><span>())</span> <span>{</span>\n                                <span>pStmt</span> <span>=</span> <span>(</span><span>PreparedStatement</span><span>)</span> <span>clientPrepareStatement</span><span>(</span><span>nativeSql</span><span>,</span> <span>resultSetType</span><span>,</span> <span>resultSetConcurrency</span><span>,</span> <span>false</span><span>);</span>\n\n                                <span>if</span> <span>(</span><span>sql</span><span>.</span><span>length</span><span>()</span> <span>&lt;</span> <span>getPreparedStatementCacheSqlLimit</span><span>())</span> <span>{</span>\n                                    <span>this</span><span>.</span><span>serverSideStatementCheckCache</span><span>.</span><span>put</span><span>(</span><span>sql</span><span>,</span> <span>Boolean</span><span>.</span><span>FALSE</span><span>);</span>\n                                <span>}</span>\n                            <span>}</span> <span>else</span> <span>{</span>\n                                <span>throw</span> <span>sqlEx</span><span>;</span>\n                            <span>}</span>\n                        <span>}</span>\n                    <span>}</span>\n                <span>}</span>\n            <span>}</span> <span>else</span> <span>{</span>\n                <span>//不启用本地缓存，直接发送语句到数据库服务器预处理并创建新的PreparedStatement，</span>\n                <span>try</span> <span>{</span>\n                    <span>pStmt</span> <span>=</span> <span>ServerPreparedStatement</span><span>.</span><span>getInstance</span><span>(</span><span>getMultiHostSafeProxy</span><span>(),</span> <span>nativeSql</span><span>,</span> <span>this</span><span>.</span><span>database</span><span>,</span> <span>resultSetType</span><span>,</span> <span>resultSetConcurrency</span><span>);</span>\n\n                    <span>pStmt</span><span>.</span><span>setResultSetType</span><span>(</span><span>resultSetType</span><span>);</span>\n                    <span>pStmt</span><span>.</span><span>setResultSetConcurrency</span><span>(</span><span>resultSetConcurrency</span><span>);</span>\n                <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>sqlEx</span><span>)</span> <span>{</span>\n                    <span>// Punt, if necessary</span>\n                    <span>if</span> <span>(</span><span>getEmulateUnsupportedPstmts</span><span>())</span> <span>{</span>\n                        <span>pStmt</span> <span>=</span> <span>(</span><span>PreparedStatement</span><span>)</span> <span>clientPrepareStatement</span><span>(</span><span>nativeSql</span><span>,</span> <span>resultSetType</span><span>,</span> <span>resultSetConcurrency</span><span>,</span> <span>false</span><span>);</span>\n                    <span>}</span> <span>else</span> <span>{</span>\n                        <span>throw</span> <span>sqlEx</span><span>;</span>\n                    <span>}</span>\n                <span>}</span>\n            <span>}</span>\n        <span>}</span> <span>else</span> <span>{</span>\n            <span>//数据库不支持语句预处理，则在本地创建一个client类型的PreparedStatement，使得方法调用者不用关心数据库预处理支持程度</span>\n            <span>pStmt</span> <span>=</span> <span>(</span><span>PreparedStatement</span><span>)</span> <span>clientPrepareStatement</span><span>(</span><span>nativeSql</span><span>,</span> <span>resultSetType</span><span>,</span> <span>resultSetConcurrency</span><span>,</span> <span>false</span><span>);</span>\n        <span>}</span>\n\n        <span>return</span> <span>pStmt</span><span>;</span>\n    <span>}</span>\n<span>}</span></code></pre></figure>\n\n<p>上面代码中已加了注释，希望能够帮助大家理解。可以看到，如果启用了本地缓存，那么先会在本地缓存中查找sql对应的已经预处理过的PreparedStatement，如果找到则复用此PreparedStatement。</p>\n\n<p>Statement（包括PreparedStatement）使用完之后，需要调用close方法。对于PreparedStatement，如果经过数据库预处理的并且启用了本地缓存，那么会在close方法中写进缓存中，使得此连接的后续sql执行可以复用同样的预处理。</p>\n\n<figure><pre><code data-lang=\"java\"><span>public</span> <span>void</span> <span>close</span><span>()</span> <span>throws</span> <span>SQLException</span> <span>{</span>\n    <span>MySQLConnection</span> <span>locallyScopedConn</span> <span>=</span> <span>this</span><span>.</span><span>connection</span><span>;</span>\n\n    <span>if</span> <span>(</span><span>locallyScopedConn</span> <span>==</span> <span>null</span><span>)</span> <span>{</span>\n        <span>return</span><span>;</span> <span>// already closed</span>\n    <span>}</span>\n\n    <span>synchronized</span> <span>(</span><span>locallyScopedConn</span><span>.</span><span>getConnectionMutex</span><span>())</span> <span>{</span>\n\n        <span>if</span> <span>(</span><span>this</span><span>.</span><span>isCached</span> <span>&amp;&amp;</span> <span>!</span><span>this</span><span>.</span><span>isClosed</span><span>)</span> <span>{</span>\n            <span>clearParameters</span><span>();</span>\n\n            <span>this</span><span>.</span><span>isClosed</span> <span>=</span> <span>true</span><span>;</span>\n\n            <span>this</span><span>.</span><span>connection</span><span>.</span><span>recachePreparedStatement</span><span>(</span><span>this</span><span>);</span>\n            <span>return</span><span>;</span>\n        <span>}</span>\n\n        <span>realClose</span><span>(</span><span>true</span><span>,</span> <span>true</span><span>);</span>\n    <span>}</span>\n<span>}</span></code></pre></figure>\n\n<p>使用JDBC的Connection、Statement访问Mysql数据库的完整例子如下：</p>\n\n<figure><pre><code data-lang=\"java\"><span>import</span> <span>java.sql.Connection</span><span>;</span>\n<span>import</span> <span>java.sql.DriverManager</span><span>;</span>\n<span>import</span> <span>java.sql.Statement</span><span>;</span>\n\n<span>/*\n * Created by shenyuan on 2017/6/24.\n */</span>\n<span>public</span> <span>class</span> <span>MysqlTest</span> <span>{</span>\n    <span>public</span> <span>static</span> <span>void</span> <span>main</span><span>(</span><span>String</span><span>[]</span> <span>args</span><span>)</span> <span>{</span>\n        <span>Connection</span> <span>conn</span> <span>=</span> <span>null</span><span>;</span>\n        <span>Statement</span> <span>stmt</span> <span>=</span> <span>null</span><span>;</span>\n\n        <span>try</span> <span>{</span>\n            <span>conn</span> <span>=</span> <span>DriverManager</span><span>.</span><span>getConnection</span><span>(</span><span>\"jdbc:mysql://localhost:3306/test\"</span><span>,</span> <span>\"myLogin\"</span><span>,</span> <span>\"myPassword\"</span><span>);</span>\n            <span>stmt</span> <span>=</span> <span>conn</span><span>.</span><span>createStatement</span><span>();</span>\n            <span>String</span> <span>sql</span> <span>=</span> <span>\"INSERT INTO test VALUES ('dengshenyu','java')\"</span><span>;</span>\n            <span>stmt</span><span>.</span><span>executeUpdate</span><span>(</span><span>sql</span><span>);</span>\n\n        <span>}</span> <span>catch</span> <span>(</span><span>Exception</span> <span>ex</span><span>)</span> <span>{</span>\n            <span>ex</span><span>.</span><span>printStackTrace</span><span>();</span>\n        <span>}</span> <span>finally</span> <span>{</span>\n            <span>try</span> <span>{</span>\n                <span>if</span> <span>(</span><span>stmt</span> <span>!=</span> <span>null</span><span>)</span>\n                    <span>stmt</span><span>.</span><span>close</span><span>();</span>\n                <span>if</span> <span>(</span><span>conn</span> <span>!=</span> <span>null</span><span>)</span>\n                    <span>conn</span><span>.</span><span>close</span><span>();</span>\n            <span>}</span> <span>catch</span> <span>(</span><span>Exception</span> <span>e</span><span>)</span> <span>{</span>\n                <span>e</span><span>.</span><span>printStackTrace</span><span>();</span>\n            <span>}</span>\n            \n        <span>}</span>\n\n    <span>}</span>\n<span>}</span></code></pre></figure>\n\n\n  </div>\n\n</div><br>\n<br>\n<br>\n<br>\n技术<br>\n<br>\nvia www.dengshenyu.com https://ift.tt/2Ei3N7s<br>\n<br>\nDecember 11, 2018 at 09:14PM"}}
{"actor":{"display_login":"guevara"}}