JDBC（一）

1. Jdbc开发步骤

1. 注册驱动
2. 获得连接
3. 获得语句执行平台
4. 执行sql语句
5. 处理结果
6. 释放资源

2. 导入驱动jar包

1. mysql-connector-java-6.0.6.jar（不要使用，有bug）
2. mysql-connector-java-5.1.37-bin.jar

3. 示例

1. 代码

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   public class JdbcStudy {
       public JdbcStudy() {
           // TODO Auto-generated constructor stub
       }
   
       public static void main(String[] args) throws SQLException, ClassNotFoundException {
           // 1. 注册驱动
           //    告知jvm使用的是哪一个数据库的类型
           // 2. 获得连接
           //    使用JDBC中的类，完成对MySql数据库的连接
           // 3. 获得语句执行平台
           //    通过连接对象获得对sql语句的执行对象
           // 4. 执行sql语句
           //    使用执行对象，向数据库实行sql语句
           // 5. 处理结果
           // 6. 释放资源
           //    close()
           // 注册驱动
           // com.mysql.cj.jdbc中已经注册过对象了，所以下面的注册可以省略，直接利用反射添加该类
           // DriverManager.registerDriver(new Driver());
           Class.forName("com.mysql.jdbc.Driver");
   
           // 获得连接
           String url = "jdbc:mysql://localhost:3306/MyStudy";
           String user = "root";
           String password = "password?";
           Connection con = DriverManager.getConnection(url, user, password);
   
           // 获得语句执行平台
           Statement state = con.createStatement();
   
           // 执行sql语句，并返回结果
           // String insertSql = "insert into myStudy001(mid, mname) values('1', 'ccblogs01')";
           // int rst = state.executeUpdate(insertSql);
           // System.out.println(rst);
           String querySql = "select * from myStudy001";
   
           // 结果集处理
           ResultSet rs = state.executeQuery(querySql);
           while (rs.next()) {
               System.out.println(String.format("mid:%d, mname:%s", rs.getInt("mid"), rs.getString("mname")));
           }
           rs.close();
   
           // 释放资源
           state.close();
           con.close();
       }
   }
   

2. 如果遇到下面的问题：

   Exception in thread "main" com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
   The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
       at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:590)
       at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:57)
       at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1606)
       at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
       at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
       at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
       at java.sql.DriverManager.getConnection(Unknown Source)
       at java.sql.DriverManager.getConnection(Unknown Source)
       at com.ccblogs.jdbc.JdbcStudy.main(JdbcStudy.java:38)
   Caused by: com.mysql.cj.core.exceptions.CJCommunicationsException: Communications link failure
   The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
       at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
       at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
       at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
       at java.lang.reflect.Constructor.newInstance(Unknown Source)
       at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
       at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93)
       at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133)
       at com.mysql.cj.core.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:149)
       at com.mysql.cj.mysqla.io.MysqlaSocketConnection.connect(MysqlaSocketConnection.java:83)
       at com.mysql.cj.mysqla.MysqlaSession.connect(MysqlaSession.java:122)
       at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1726)
       at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1596)
       ... 6 more
   Caused by: java.net.ConnectException: Connection timed out: connect
       at java.net.DualStackPlainSocketImpl.connect0(Native Method)
       at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
       at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
       at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
       at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
       at java.net.PlainSocketImpl.connect(Unknown Source)
       at java.net.SocksSocketImpl.connect(Unknown Source)
       at java.net.Socket.connect(Unknown Source)
       at com.mysql.cj.core.io.StandardSocketFactory.connect(StandardSocketFactory.java:202)
       at com.mysql.cj.mysqla.io.MysqlaSocketConnection.connect(MysqlaSocketConnection.java:57)
       ... 9 more
   

   解决办法：

   # 在mysql的配置文件my.cnf中注释掉下面这句，然后重新启动mysql服务
   # bind-address          = 127.0.0.1
   

3. 如果遇到下面的问题：

   Exception in thread "main" java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:526)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505)
       at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479)
       at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1779)
       at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1596)
       at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
       at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
       at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
       at java.sql.DriverManager.getConnection(Unknown Source)
       at java.sql.DriverManager.getConnection(Unknown Source)
       at com.ccblogs.jdbc.JdbcStudy.main(JdbcStudy.java:38)
   Caused by: java.lang.ArrayIndexOutOfBoundsException: 90
       at com.mysql.cj.mysqla.io.Buffer.readInteger(Buffer.java:271)
       at com.mysql.cj.mysqla.io.MysqlaCapabilities.setInitialHandshakePacket(MysqlaCapabilities.java:62)
       at com.mysql.cj.mysqla.io.MysqlaProtocol.readServerCapabilities(MysqlaProtocol.java:482)
       at com.mysql.cj.mysqla.io.MysqlaProtocol.beforeHandshake(MysqlaProtocol.java:367)
       at com.mysql.cj.mysqla.io.MysqlaProtocol.connect(MysqlaProtocol.java:1412)
       at com.mysql.cj.mysqla.MysqlaSession.connect(MysqlaSession.java:132)
       at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1726)
       ... 7 more
   

   解决办法：

   更换驱动jar包，驱动的bug。
   可以使用mysql-connector-java-5.1.37-bin.jar

4. jdbc进阶

1. 执行sql时需要注意sql注入攻击，可以通过预编译sql避免。
2. 可以通过配置文件读取数据库连接信息。
3. 还可以将数据库连接操作实装为工具类，并放在静态方法中可避免多次创建连接。

   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.util.Properties;
   
   public class Jdbc_study {
       public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
           // 从编译后的class路径中加载配置文件
           InputStream in = Jdbc_study.class.getClassLoader().getResourceAsStream("database.properties");
           Properties pro = new Properties();
           pro.load(in);
           String url = pro.getProperty("url");
           String user = pro.getProperty("user");
           String password = pro.getProperty("password");
           String driver = pro.getProperty("driver");
           in.close();
           Class.forName(driver);
           Connection con = DriverManager.getConnection(url, user, password);
           String querySql = "select * from myStudy001";
           // 预编译sql文，可以避免sql注入攻击
           PreparedStatement state = con.prepareStatement(querySql);
           ResultSet rs = state.executeQuery(querySql);
           while (rs.next()) {
               System.out.println(String.format("mid:%d, mname:%s", rs.getInt("mid"), rs.getString("mname")));
           }
           rs.close();
           state.close();
           con.close();
       }
   }