jdbc

java data base connectivity java数据库链接

流程

指定数据库驱动程序

加载数据库驱动

创建连接对象

创建statement对象

执行statement对象的sql语句

关闭连接

//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库的连接
Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD);
//3.通过数据库的连接操作数据库，实现增删改查
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select user_name,age from imooc_goddess");//选择import java.sql.ResultSet;
while(rs.next()){//如果对象中有数据，就会循环打印出来
  System.out.println(rs.getString("user_name")+","+rs.getInt("age"));
}


String sql = "delete from t_employee where id=? ";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, employee.getId());
int result = pstmt.executeUpdate();（execute、 executeQuery 和 executeUpdate）

厂商的driver实现获取连接

com.mysql.jdbc.NonRegisteringDriver#connect
		Connection newConn = com.mysql.jdbc.ConnectionImpl.getInstance(host(props), port(props), props, database(props), url);

preparedStatement

1. PreparedStatement的预编译，需要在url中指定开启预编译useServerPrepStmts=true，才会在创建PreparedStatement对象时向mysql发送预编译的请求。

   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(getMultiHostSafeProxy(), 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 necessary
                                   if (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(getMultiHostSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency);
      
                           pStmt.setResultSetType(resultSetType);
                           pStmt.setResultSetConcurrency(resultSetConcurrency);
                       } catch (SQLException sqlEx) {
                           // Punt, if necessary
                           if (getEmulateUnsupportedPstmts()) {
                               pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
                           } else {
                               throw sqlEx;
                           }
                       }
                   }
               } else {
                   pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
               }
      
   

2. 每次向mysql发送预编译请求，不管之前当前链接有没有编译过此SQL语句，mysql都会预编译一次，并返回此链接当前唯一的Statement ID，后续执行SQL语句的时候，程序只需拿着StatementID和参数就可以了。

3. 若预编译的SQL语句有语法错误，则mysql的响应会携带错误信息，但此错误信息JDBC感知不到(或者说mysql-connetor-java.jar包里的实现将其忽略掉了)，此时还会继续往下执行代码，当执行到executeXxx()方法时，由于没有Statement ID，所以就会将拼接完整的SQL语句值已经将占位符(?)替换掉发给mysql请求执行，此时mysql响应有语法错误，然后JDBC就会抛出语法错误异常，所以检查语法那一步实在mysql-server中做的。

4. PreparedStatement对性能的提高是利用缓存实现的，此缓存是mysql-connetor-java.jar包里实现的，而非mysql-server中的缓存，缓存需要在url中指定开启cachePrepStmts=true才会有效，缓存的key是完整的sql语句，value是PreparedStatement对象。

5. mysql-connetor-java.jar包里缓存的实现：只有在PreparedStatement对象关闭时close()才会将PreparedStatement对象放到缓存中，所以只要缓存PreparedStatement对象没有关闭，你不管调用多少次connection.prapareStatement(sql)对相同的sql语句进行预编译，都会将预编译的请求发给mysql，mysql也会对每一个sql语句不管是否相同进行预编译，并生成一个唯一的Statement ID并返回

6. mysql-connetor-java.jar实现的缓存是针对链接的，每个链接都是独立的，不共享缓存