【JAVA基础】JDBC编程——基于JAVA访问数据库

0 前言

Java为关系数据库定义了一套标准的访问接口：JDBC（Java Database Connectivity），使我们可以基于JDBC访问数据库，实现数据的增删改查。

本文将以PostgreSQL为例，展示JDBC的一些基本操作，其中以实操代码为主，关于基本概念的讲解详见参考文献。

提示：在开始学习JDBC编程之前，务必先学习下SQL基础。

1 配置MAVEN

<dependency>
   <groupId>org.postgresql</groupId>
   <artifactId>postgresql</artifactId>
   <version>42.2.18</version>
   <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>3.4.5</version>
</dependency>

配置项包括两个jar包：
1、postgresql：PostgreSQL的JDBC驱动
2、HikariCP：JDBC连接池（javax.sql.DataSource）的实现类

注意:
postgresql中的scope应该为runtime，因为只在运行期才使用。如果把runtime改成compile，虽然也能正常编译，但是在IDE里写程序的时候，会多出来一大堆类似com.mysql.jdbc.Connection这样的类，非常容易与Java标准库的JDBC接口混淆，所以坚决不要设置为compile。

2 连接数据库

import java.sql.*;
public class Test {
    // JDBC连接的URL, 不同数据库有不同的格式:
    static final String URL = "jdbc:postgresql://localhost:3306/test";
    static final String USER = "sde";
    static final String PASSWORD = "123456";
    public static void main(String[] args) {
        // 获取连接:
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("数据库连接成功！");
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
}

核心代码是DriverManager提供的静态方法getConnection()。DriverManager会自动扫描classpath，找到所有的JDBC驱动，然后根据我们传入的URL自动挑选一个合适的驱动。因为JDBC连接是一种昂贵的资源，所以使用后要及时释放（通过try-with-resources的方式）。

注意：代码中的URL、USER、PASSWORD是数据库的信息，填写你自己的。

3 创建表

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
    // 不同数据库有不同的格式
    static final String URL = "jdbc:postgresql://localhost:3306/test";
    static final String USER = "sde";
    static final String PASSWORD = "123456";
    public static void main(String[] args) {
        // 获取连接:
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            // Statement对象用于执行SQL语句
            try (Statement stmt = conn.createStatement()) {
                String sql = "CREATE TABLE public.javaBuild" +
                        " (id serial PRIMARY KEY NOT NULL," +
                        " name text ," +
                        " age  int," +
                        " gender char(1))";
                stmt.executeUpdate(sql);
            }
            System.out.println("JAVA建表成功！");
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
}

Postgre 中主键设定为serial类型，在后续插入时，主键就会自增。

4 增删改查

import java.sql.*;
public class Test {
    // 不同数据库有不同的格式
    static final String URL = "jdbc:postgresql://localhost:3306/test";
    static final String USER = "sde";
    static final String PASSWORD = "123456";
    public static void main(String[] args) {
        // 获取连接:
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            // Statement对象用于执行SQL语句
            try (Statement stmt = conn.createStatement()) {
                // 插入
                String sql = "INSERT INTO public.javaBuild (name,age,gender) VALUES ('Tom',12,'M'),('Ming',13,'W'),('Wang',12,'M')";
                stmt.executeUpdate(sql);
                // 删除
                String delete = "DELETE FROM public.javaBuild WHERE name='Tom'";
                stmt.executeUpdate(delete);
                // 查询
                String query= "SELECT * FROM public.javaBuild";
                try (ResultSet rs = stmt.executeQuery(query)) {
                    while (rs.next()) {
                        System.out.println(rs.getInt("id") + rs.getString("name") + rs.getInt("age") + rs.getInt("gender"));
                    }
                }
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
}

注意：

• Statment和ResultSet都是需要关闭的资源。
• 数据库的索引是从1开始，而不是0。

使用Statement拼字符串非常容易引发SQL注入的问题，这是因为SQL参数往往是从方法参数传入的（案例详见参考文献2）。因此，使用Java对数据库进行操作时，必须使用PreparedStatement，严禁任何通过参数拼字符串的代码！

具体代码如下：

import java.sql.*;
public class Test {
    // JDBC连接的URL, 不同数据库有不同的格式:
    static final String URL = "jdbc:postgresql://localhost:3306/test";
    static final String USER = "sde";
    static final String PASSWORD = "123456";
    public static void main(String[] args) {
        // 初始化需要插入的数据
        Student[] students=new Student[]{new Student("Tom",12,"M"),
                new Student("Ming",13,"W"),new Student("Wang",12,"M")};
        // 获取连接:
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            // 插入，其中?表示占位符
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO public.javaBuild (name,age,gender) VALUES (?,?,?)")) {
                // 对同一个PreparedStatement反复设置参数并调用addBatch():
                for (Student student:students){
                    // 设置对应占位符的值
                    ps.setObject(1,student.getName());
                    ps.setObject(2,student.getAge());
                    ps.setObject(3,student.getGender());
                    // 将每个完整的插入语句放进Batch
 ps.addBatch();
                }
                // 执行batch:
                int[] ns = ps.executeBatch();
                for (int n : ns) {
                    // batch中每个SQL执行的结果数量
                    System.out.println(n + " has been inserted.");
                }
            }
            // 删除
            try (PreparedStatement ps = conn.prepareStatement("DELETE FROM public.javaBuild WHERE name='Tom'")) {
                int n = ps.executeUpdate();
                System.out.println(n + " has been deleted.");
            }
            // 查询
            try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM public.javaBuild")) {
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getInt("id") + rs.getString("name") + rs.getInt("age")+rs.getString("gender"));
                    }
                }
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
}
class Student{
    private String name;
    private int age;
    private String gender;
    public Student(String name, int age, String gender) {
        this.name = name;
        this.age = age;
        this.gender = gender;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
}

5 事务

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。

package com.navinfo.csu.domain.broker.htf.jdbcTest;
import scala.xml.Null;
import java.sql.*;
public class Test {
    // JDBC连接的URL, 不同数据库有不同的格式:
    static final String URL = "jdbc:postgresql://localhost:3306/test";
    static final String USER = "sde";
    static final String PASSWORD = "123456";
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Savepoint savepoint =null;
        try{
            // 获取连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            // 设定隔离级别
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            // 关闭自动提交，相当于SQL语句中的BEGAIN
            conn.setAutoCommit(false);
            // 插入操作1
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO public.javaBuild (name,age,gender) VALUES ('Huang',12,'M')")) {
                ps.executeUpdate();
            }
            // 设置保存点
            savepoint = conn.setSavepoint("Savepoint");
            // 插入操作2
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO public.javaBuild (name,age,gender) VALUES ('Dong',12,11111)")) {
                ps.executeUpdate();
            }
            // 提交事务
 conn.commit();
            // 查询
 try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM public.javaBuild")) {
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getInt("id") + rs.getString("name") + rs.getInt("age")+rs.getString("gender"));
                    }
                }
            }
        }catch(SQLException e){
            e.printStackTrace();
            // 回滚事务
 if (conn != null){
                conn.rollback(savepoint);
            }
        }finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
        }
    }
}

默认情况下，我们获取到Connection连接后，总是处于“自动提交”模式，也就是每执行一条SQL都是作为事务自动执行的，即默认为“隐式事务”。只要关闭了Connection的autoCommit，那么就可以在一个事务中执行多条语句，事务以commit()方法结束。

MySQL的默认隔离级别是REPEATABLE READ。

setSavepoint()可以记录保存点，当出现异常时，可以回滚到指定的保存点。

6 连接池

在执行JDBC的增删改查的操作时，如果每一次操作都来一次打开连接，操作，关闭连接，那么创建和销毁JDBC连接的开销就太大了。为了避免频繁地创建和销毁JDBC连接，我们可以通过连接池（Connection Pool）复用已经创建好的连接。

JDBC连接池有一个标准的接口javax.sql.DataSource，注意这个类位于Java标准库中，但仅仅是接口。要使用JDBC连接池，我们必须选择一个JDBC连接池的实现。常用的JDBC连接池有：

• HikariCP
• C3P0
• BoneCP
• Druid

目前使用最广泛的是HikariCP，代码案例如下：

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.*;
public class Test {
    // JDBC连接的URL, 不同数据库有不同的格式:
    static final String URL = "jdbc:postgresql://localhost:3306/test";
    static final String USER = "sde";
    static final String PASSWORD = "123456";
    static public HikariConfig config = new HikariConfig();
    public static void main(String[] args) {
        // 初始化连接池
        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.addDataSourceProperty("connectionTimeout", "1000"); // 连接超时：1秒
        config.addDataSourceProperty("idleTimeout", "60000"); // 空闲超时：60秒
        config.addDataSourceProperty("maximumPoolSize", "10"); // 最大连接数：10
        DataSource ds = new HikariDataSource(config);
        
        // 获取连接，把DriverManage.getConnection()改为ds.getConnection()
        try (Connection conn = ds.getConnection()) {
            // 查询
            try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM public.javaBuild")) {
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getInt("id") + rs.getString("name") + rs.getInt("age")+rs.getString("gender"));
                    }
                }
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }
}

当我们调用conn.close()方法时（在try(resource){...}结束处），不是真正“关闭”连接，而是释放到连接池中，以便下次获取连接时能直接返回。

因此，连接池内部维护了若干个Connection实例，如果调用ds.getConnection()，就选择一个空闲连接，并标记它为“正在使用”然后返回，如果对Connection调用close()，那么就把连接再次标记为“空闲”从而等待下次调用。这样一来，我们就通过连接池维护了少量连接，但可以频繁地执行大量的SQL语句。

7 参考目录

【1】菜鸟教程：https://www.runoob.com/postgr...
【2】廖雪峰的官方网站：https://www.liaoxuefeng.com/w...