JDBC 的简单使用

JDBC是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如更新数据库中数据的方法。
本文开始之前先准备一个数据 test，新建一个 student 表并插入数据

CREATE TABLE `student` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `course` varchar(10) NOT NULL,
  `grade` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '张三', '语文', '74');
INSERT INTO `student` VALUES ('2', '张三', '数学', '83');
INSERT INTO `student` VALUES ('3', '张三', '物理', '93');
INSERT INTO `student` VALUES ('4', '李四', '语文', '79');
INSERT INTO `student` VALUES ('5', '李四', '数学', '86');
INSERT INTO `student` VALUES ('6', '李四', '物理', '88');

JDBC 的使用步骤

1. 注册驱动
2. 建立连接
3. 创建运行 sql 语句
4. 运行语句
5. 处理运行结果
6. 释放资源

JDBC最基础的写法

public class Test {
    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String dbUrl = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    private static final String username = "root";
    private static final String password = "root";
    @org.junit.Test
    public void test(){
        Connection connection = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            //1.注册驱动
            Class.forName(driver);
            //2.建立连接
            connection = DriverManager.getConnection(dbUrl, username, password);
            //3.创建sql语句
            String sql = "select * from student where 1";
            stmt = connection.createStatement();
            //4.运行语句
            resultSet = stmt.executeQuery(sql);
            //5.处理结果
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String course = resultSet.getString("course");
                int grade = resultSet.getInt("grade");
                System.out.printf("id => %d, name => %s, course => %s, grade => %d",id, name, course, grade);
                System.out.println();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.释放资源
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}
// 运行结果
id => 1, name => 张三, course => 语文, grade => 74
id => 2, name => 张三, course => 数学, grade => 83
id => 3, name => 张三, course => 物理, grade => 93
id => 4, name => 李四, course => 语文, grade => 79
id => 5, name => 李四, course => 数学, grade => 86
id => 6, name => 李四, course => 物理, grade => 88

对上边这个类进行优化，将数据库用户名和密码、数据库驱动等信息提取出来放在一个db.properties 文件中。statement 也改为使用更加安全的 prepareStatement。
db.properties 文件如下

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
username=root
password=root

Java 代码

public class Test_02 {
    @Test
    public void test() throws Exception {
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(is);

        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");

        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "select * from student where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, 3);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String course = resultSet.getString("course");
            int grade = resultSet.getInt("grade");
            System.out.printf("id => %d, name => %s, course => %s, grade => %d",id, name, course, grade);
            System.out.println();
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}
// 运行结果
id => 3, name => 张三, course => 物理, grade => 93

创建一个公共的增、删、改方法

/**
     * 通用的sql操作方法，除了查询操作之外
     * @param sql
     * @param args
     */
    public void operate(String sql, Object ...args){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = JdbcUtil.getConnection();
            ps = connection.prepareStatement(sql);
            if(args.length > 0){
                for (int i = 0; i < args.length; i++) {
                    ps.setObject(i + 1, args[i]);
                }
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResources(connection, ps);
        }
    }

PrepareStatement 与 Statement 的区别

1. Statement 用于执行静态SQL语句，在执行时，必须指定一个事先准备好的SQL语句；
2. PrepareStatement 是预编译的SQL对象，sql语句被预编译并保存在对象中。被封装的sql语句代表某一类操作，语句中可以包含动态参数“？”，在执行时可以为“？”动态设置参数值；
3. 使用 PrepareStatement 对象执行 sql 语句时，sql 被数据库进行解析和编译，然后被放到命令缓冲区，每当执行同一个 PrepareStatement 对象时，它就会被解析一次，但不会被再次编译。在缓冲区可以发现预编译的命令，并且可以重用；
4. PrepareStatement 可以减少编译次数提高数据库性能。