JDBC(一)

Jdbc学习,以mysql为例!

一、Jdbc开发步骤

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

二、导入驱动jar包

  • mysql-connector-java-6.0.6.jar(不要使用,有bug)
  • mysql-connector-java-5.1.37-bin.jar

三、Jdbc的实践

  • 详细代码:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    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();
    }
    }
  • 如果遇见下面问题,问题有可能是下面说明的原因:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    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

解决办法:

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

  • 如果遇见下面问题,问题有可能是下面说明的原因:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    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

解决办法:

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

四、jdbc进阶

  • 执行sql时需要注意sql注入攻击,可以通过预编译sql避免。
  • 可以通过配置文件读取数据库连接信息。
  • 还可以将数据库连接操作实装为工具类,并放在静态方法中可避免多次创建连接。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    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();
    }
    }