JDBC的Statement和PreparedStatement的使用

一、添加maven依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>

二、通过驱动获得连接

1、Driver

java定义了自己的java.sql.Driver接口，调用Drver接口connect获得数据库连接

数据库厂商按照java.sql.Driver实现自己的Driver类（如：mysql实现com.mysql.jdbc.Driver）

    public void testDriver() throws Exception {
        String url = "jdbc:mysql://localhost:3306/jdbc_test?serverTimezone=GMT%2B8";
        Properties info = new Properties();
        info.put("user", "root");
        info.put("password", "root123");

//        1.创建driver实现类的对象
        Driver driver = new com.mysql.jdbc.Driver();
//        Driver d = (Driver) Class.forName("com.mysql.jdbc.Driver").newInstance();

//        2.调用Drver接口connect获得数据库连接
        Connection connection = driver.connect(url, info);
        System.out.println(connection);
    }

2.DriverManager

DriverManager是驱动的管理类，把驱动注册到DriverManager，通过DriverManager调用getConnection获得连接

    public void testDriverManager() throws Exception {
        String url = "jdbc:mysql://localhost:3306/jdbc_test?serverTimezone=GMT%2B8";
        String user = "root";
        String password = "root123";

//       1.加载数据库驱动程序(对应的Driver实现类中有注册驱动的静态代码块)
        Class.forName("com.mysql.jdbc.Driver");

//        2.通过DrivetManager的getConnection()方法获取数据库连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }

执行Class.forName("com.mysql.jdbc.Driver")时，加载并初始化com.mysql.jdbc.Driver类

通过com.mysql.jdbc.Driver类的源码可以看出static静态代码块，是把驱动注册到DriverManager中

// Driver.java
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    //
    // Register ourselves with the DriverManager
    //
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

    /**
     * Construct a new driver and register it with DriverManager
     * 
     * @throws SQLException
     *             if a database error occurs.
     */
    public Driver() throws SQLException {
        // Required for Class.forName().newInstance()
    }
}

三、Statement

1.插入

1. 获得数据库连接
2. 准备插入的SQL语句
3. 获得Statement对象
4. 执行插入（使用Statement的executeUpdate(sql)方法，传入SQL，返回整数）
5. 关闭连接

    public void testStatementInsert() throws Exception {
        String url = "jdbc:mysql://localhost:3306/jdbc_test?serverTimezone=GMT%2B8";
        String user = "root";
        String password = "root123";
        Class.forName("com.mysql.jdbc.Driver");

//        1.获得数据库连接
        Connection conn = DriverManager.getConnection(url, user, password);
//        2.准备插入的SQL语句
        String sql = "INSERT INTO student(sname, sage) VALUES('zhang', '18')";
//        3.获得Statement对象
        Statement statement = conn.createStatement();
//        4.执行插入
        int i = statement.executeUpdate(sql);
        System.out.println("影响的行数：" + i);
//        5.关闭连接
        statement.close();
        conn.close();
    }

2.查询

1. 获得数据库连接
2. 准备查询的SQL语句
3. 获得Statement对象
4. 执行查询（使用Statement的executeQuery(sql)方法，传入SQL，返回ResultSet）
5. 处理ResultSet
6. 关闭连接

    public void testStatementSelect() throws Exception{
        String url = "jdbc:mysql://localhost:3306/jdbc_test?serverTimezone=GMT%2B8";
        String user = "root";
        String password = "root123";
        Class.forName("com.mysql.jdbc.Driver");

//        1.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
//        2.准备sql
        String sql = "select * from student";
//        3.获取statement
        Statement statement = conn.createStatement();
//        4.执行查询，得到ResultSet
        ResultSet resultSet = statement.executeQuery(sql);
//        5.处理ResultSet
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String sname = resultSet.getString("sname");
            int sage = resultSet.getInt(3);
            System.out.println("id:"+id + " sname:"+sname + " sage:"+sage);
        }
//        6.关闭连接
        resultSet.close();
        statement.close();
        conn.close();
    }

四、PreparedStatement

使用statement需要拼写SQL语句；使用PreparedStatement，可以设置占位符的值

1.插入

1. 获得数据库连接
2. 准备插入的SQL语句
3. 获得PreparedStatement对象（使用Connection的prepareStatement(sql)方法，传入SQL；PreparedStatement对象设置占位符的值）
4. 执行插入（使用PreparedStatement的executeUpdate()方法）
5. 关闭连接

    public void testPreparedStatementInsert() throws Exception{
        String url = "jdbc:mysql://localhost:3306/jdbc_test?serverTimezone=GMT%2B8";
        String user = "root";
        String password = "root123";
        Class.forName("com.mysql.jdbc.Driver");

//        1.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);

//        2.准备sql
        String sql = "INSERT INTO student(sname, sage) VALUES(?, ?)";

//        3.获取PreparedStatement
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, "li");
        preparedStatement.setInt(2,20);

//        4.执行插入
        int i = preparedStatement.executeUpdate();
        System.out.println("影响的行数：" + i);

//        5.关闭连接
        preparedStatement.close();
        conn.close();
    }

2.查询

1. 获得数据库连接
2. 准备插入的SQL语句
3. 获得PreparedStatement对象（使用Connection的prepareStatement(sql)方法，传入SQL；PreparedStatement对象设置占位符的值）
4. 执行查询（使用PreparedStatement的executeQuery()方法，返回ResultSet）
5. 处理ResultSet
6. 关闭连接

    public void testPreparedStatementSelect() throws Exception{
        String url = "jdbc:mysql://localhost:3306/jdbc_test?serverTimezone=GMT%2B8";
        String user = "root";
        String password = "root123";
        Class.forName("com.mysql.jdbc.Driver");

//        1.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
//        2.准备sql
        String sql = "select * from student where sname=?";
//        3.获取PreparedStatement
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, "li");
//        4.执行查询，得到ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();
//        5.处理ResultSet
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String sname = resultSet.getString("sname");
            int sage = resultSet.getInt(3);
            System.out.println("id:"+id + " sname:"+sname + " sage:"+sage);
        }
        //        6.关闭连接
        preparedStatement.close();
        conn.close();
    }