JDBC 数据库编程

JDBC (Java Database Connectivity) ，属于 Java 应用编程中比较基础的一块，虽然你可以直接使用 MyBatis，但是了解一下 JDBC 可以帮你更好地理解 Java 的数据库编程。

# 连接数据库

# 驱动初始化

# 基于 Class.forName 的初始化

想要开始 JDBC 编程，第一步是需要把 数据库驱动程序 的代码加载进来。

可以利用 Class.forName 函数，它原本的功能是返回一个类或者接口的 Class 对象，也就是相当于初始化了一个类，一般用它执行这个类的静态代码段。

1
2
3
4
5
try {
    Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

这样，com.mysql.jdbc.Driver 类中的静态代码段就会被执行，进行初始化。

不过，驱动初始化还有另外一种写法。

# 基于 registerDriver 的初始化

我们知道 Class.forName 是执行类的静态代码段，那我们把 com.mysql.jdbc.Driver 里面的静态代码段照着样子抄一遍不也可以实现初始化吗?

这是 com.mysql.jdbc.Driver 静态代码段的源代码:

1
2
3
4
5
6
7
static {
    try {
        java.sql.DriverManager.registerDriver(new Driver());
    } catch (SQLException E) {
        throw new RuntimeException("Can't register driver!");
    }
}

这段代码可以完全代替上一小节的代码，我们可以认为 DriverManager.registerDriver(new Driver()) 和 Class.forName 的写法是等效的。

什么是 数据库驱动程序 ？
JDK 提供了一个 JDBC 的接口(Interface)。但是，因为是接口，所以还是没有实现的，每个数据库的厂商需要自己实现这个接口，这样用户才能正常调用。
以 mysql 为例，下载地址: dev.mysql.com/downloads/connector/j
接口变动
mysql5 和 mysql6 的驱动程序接口有区别，从 com.mysql.jdbc.Driver 换成了 com.mysql.cj.jdbc.Driver，再往上的版本同 mysql6 。

# 建立连接

数据库打开后会在本地开一个端口，运行进程，我们可以通过这个端口的 URL 来访问数据库。

当然，还需要数据库的用户名和密码。

1
java.sql.DriverManager.getConnection(url, user, password);

JDBC 的 URL 格式: jdbc:[数据库连接名]://localhost:[端口号]/[数据库名]

数据库连接名 mysql、sqlserver
端口号 3306(mysql)、1433(sqlserver)
数据库名业务相关的数据库名，自定义

一个 JDBC 连接数据库的例子:

1
2
3
4
5
6
try {
    java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/booklib",
            "root", "123456");
} catch (SQLException e) {
    e.printStackTrace();
}

# JDBC 基础

# Statement

Statement 可以根据给出的一条 SQL 字符串，然后调用运行。

借用上一节连接得到的 conn 对象，它有一个 createStatement 函数，可以创建一个 Statement。

1
2
3
4
5
6
7
try {
    java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/booklib",
            "root", "123456");
    java.sql.Statement st =  conn.createStatement();
} catch (SQLException e) {
    e.printStackTrace();
}

# execute、executeQuery、executeUpdate

创建完 Statement，就要把 SQL 语句交给 Statement 对象去执行了。

# executeQuery

一般如果是运行查询(select) 语句，推荐使用第一节中获得的conn 对象的 executeQuery 函数，executeQuery只能返回单个结果集，但是应对大部分的查询已经足够。

添加下面的代码到上面的 try 代码块中。

1
2
String sql = "select * from BeanBook";
st.executeQuery(sql);

# executeUpdate

如果你需要运行 insert、update、delete 等等语句，则可以使用 executeUpdate 函数，它不会返回结果集，但是会返回一个整数，代表受到影响的行数，如果是 0，代表了你没有改变任何数据库的内容，即调用失败了。

使用 executeUpdate :

1
2
String sql = "delete from BeanBook where price>50";
st.executeUpdate(sql);

# execute

execute 是更加通用和强大的函数，但是它也比较复杂。它的返回值类型有很多。

execute 不仅可以做到 executeQuery 能做的事，也能做到 executeUpdate 能做到的事情。而且，它还能返回多个结果集。

正因为如此，它一般被用在一些执行未知 SQL 字符串的情况下，如果 SQL 语句能够确定，请尽可能不用 execute。

它的返回值比较复杂，我们一般使用 getResultSet 和 getUpdateCount 获取，而不是直接把 st.execute(sql); 的结果拿来。

以下两段代码和上面两小节的 demo 的效果是一样的。

1
2
3
4
5
6
7
8
9
//代码段1
String sql = "select * from BeanBook";
st.execute(sql);
st.getResultSet();

//代码段2
String sql = "delete from BeanBook where price>50";
st.execute(sql);
st.getUpdateCount();

# ResultSet

前面一直没提 ResultSet，ResultSet 就是 executeQuery 的返回值。

举个例子，使用 ResultSet 遍历 SQL 的结果:

1
2
3
4
5
String sql = "select * from BeanBook";
java.sql.ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
    System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getDouble(4));
}

ResultSet 通过 next 函数来遍历，next 从一条记录跳转到下一条记录。

getString、getDouble 等等函数接受一个数字 n 作为参数，获得当前记录的第 n 个属性的值，并对这个值进行转换。

比如 getString(1) 获取第一个属性，转换成 String 类型；getDouble(4) 获取第四个属性，转换成 Double 类型。

下面是一些 get 函数 (不全):

原始类型相关 getString、getBoolean、getByte、getShort、getInt、getLong、getFloat、getDouble
日期相关 getDate、getTime、getTimestamp

# PreparedStatement

通过 conn.prepareStatement 可以来创建一个 PrepareStatement 对象 (conn 是一个 java.sql.Connection)。

但是这个函数必须要给出一个 SQL 语句作为参数。

这里也可以看出 PrepareStatement 与 Statement 的一个比较大的区别。Statement 可以一直被复用，但是 PrepareStatement 每执行一次 SQL，都要创建新的 PrepareStatement。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
//Statement
java.sql.Statement st =  conn.createStatement();
Int limit = 50;
String sql = "delete from BeanBook where price>50";
st.executeUpdate(sql);
String sql2 = "select * from BeanBook";
st.executeQuery(sql2);

//PrepareStatement
String sql = "delete from BeanBook where price>50";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
st.executeUpdate();
String sql2 = "select * from BeanBook";
java.sql.PreparedStatement pst2 = conn.prepareStatement(sql2);
st.executeQuery();

PreparedStatement 和 Statement 一样是用来执行 SQL 语句的，但是 Statement 有很多问题。

# 字符串拼接问题

假如我们希望可以动态地设置 SQL 语句，比如，动态改变 where 从句的条件，在 Statement 中，我们需要这样写:

1
2
3
4
java.sql.Statement st =  conn.createStatement();
Int limit = 50;
String sql = "delete from BeanBook where price>"+limit;
st.executeUpdate(sql);

PrepareStatement 允许一种可读性非常好的参数设置语法:

1
2
3
4
String sql = "delete from BeanBook where price>?";
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
pst.setInt(1, 50);
st.executeUpdate(sql);

这个 ? 语法可不止可读性好，还有一个更重要的是，它支持了 预编译，这在我们接下来提到的性能问题中会被具体讨论。

还需要提一下的是 setInt 等等的 set 相关的函数，前面已经提过 get 函数了，基本上把 get 改成 set 就行了。

# 性能问题

Statement 的想法是对象只需要创建一次，后续只要传入不同的 SQL 就行了。但是在面对重复都比较高的工作的时候，这可能并不是很好。

比如，我执行了一次 insert into Beanbook(barcode, bookname, pubid, price, state) values(1, 'Java', 3, 56, '在库')，

现在我稍微变一下，要插入 insert into Beanbook(barcode, bookname, pubid, price, state) values(2, 'C++', 2, 34, '在库')。

这两条命令几乎一样，但是我却要编译两次!!!

我们来看看 PrepareStaement，我们完全可以使用 ? 语法，创建一次模版，因为存在预编译机制，当我们第二次插入的时候节省了一次编译的开销。

也就是说，在可以使用 ? 语法替换的一系列 SQL 操作中，使用 PrepareStatement 将会节省一大笔开销。

# SQL 注入式攻击

SQL 注入式攻击其实很简单，这是完全可以避免的，但是使用 Statement 的时候你要格外小心。

假设不怀好意的用户对你的数据库请求删除一些合法的东西，比如删除 bookname 等于 'Java' 的书，但是他传给你的字符串做了一些手脚:

1
2
3
4
5
//用户的数据
String name="'Java' OR price>0";

//你的代码
String sql = "delete from BeanBook where bookname="+name;

好了，你完了，因为字符串拼接的时候，后面的 OR price>0 没有被当作是 bookname 的一部分，而是被当成是 SQL 命令的一部分!!! 在这里，你的数据库已经被清空了。

但是使用 ? 语法你完全不用担心，因为 PrepareStatement 是预编译的，后面只会插入数据，插入的内容不会被当作是 SQL 命令。

# close

在数据库的最后不能忘记，关闭连接。

在原先代码的基础上，在最后的 finally 语句块中加入 close() 函数。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
public static void main(String[] args) throws SQLException {
    java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/booklib", "root",
            "123456");
    try {
        conn.setAutoCommit(false);
        String sql = "select * from BeanBook";
        java.sql.PreparedStatement pst = conn.prepareStatement(sql);
        pst.executeQuery();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
}

# JDBC时间类型的处理

从之前的 get 函数中，我们看到了日期相关的 API。

JDBC 中一共有三种时间类型:

Date
Time
Timestamp

# System.currentTimeMillis()

这个函数可以获得系统当前的一个时间戳。

时间戳是 1970年1月1日0点0分到现在的毫秒数。

# 设置Timestamp

数据库里面存的都是 Timestamp，一般建议存取都用 Timestamp。

设置当前的时间为 Timestamp:

1
pst.setTimestamp(5, new java.sql.Timestamp(System.currentTimeMillis()));

通过 Date类来创建 Timestamp:

1
2
java.utl.Date date = new java.util.Date();
pst.setTimestamp(5, new java.sql.Timestamp(date.getTime()));

事实上，一般这里直接用字符串也能设置，但对于数据库存在性能问题，一般不建议这样做:

1
pst.setString(5, "2020-06-27 00:00:00");

# 取出Timestamp

因为从数据库直接取出的是 Timestamp，需要使用 SimpleDateFormat 来格式化，才能打印出我们可以识别的时间字符串。

1
2
3
Timestamp timestamp = rs.getTimestamp(5);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println(sdf.format(timestamp));

# 事务控制

# 为什么需要事务

一个事务是一系列用户定义的数据库操作序列(CRUD)。它的目的是把数据库的多个操作合并抽象成一个操作。

事务的设计哲学: 要么都成功，要么都失败。这就是事务的原子性。

事务是隔离的，并发执行的事务之间不互相干扰。

# 如何实现

事务的实现靠的是回滚机制。

当你做完一个操作的时候，都有日志文件记录下你修改的数据。如果你接下来的操作出现了问题，那么数据库就能根据日志文件，运行逆操作，回到原来的状态。

# JDBC中的事务编程

可以在最开始使用 setAutoCommit(false) 来关闭自动提交。

所谓的自动提交就是，mysql 的 JDBC实现默认是一旦运行了 execute 相关的那三个函数，就会自动运行 commit() 函数，以更新数据库。

然后在 try 语句的最后使用 commit() 提交。

最后不要忘记，异常处理，如果发生了异常，就要使用 rollback() 函数回滚，使前面的操作全部无效。

我们来看一个结合来前面所有知识的例子:

 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
import java.sql.SQLException;

public class Test {
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
        java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/booklib", "root",
                "123456");
        try {
            conn.setAutoCommit(false);
            String sql = "select * from BeanBook";
            java.sql.PreparedStatement pst = conn.prepareStatement(sql);
            java.sql.ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getDouble(4));
            }
            sql = "delete from BeanBook where price>50";
            pst = conn.prepareStatement(sql);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.rollback();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

}

mysql 中的存储引擎有: InnoDB、MyISAM等等，但是事务控制只有 InnoDB 支持。

# 连接池

# 连接池的概念

之前每次我们使用数据库 CRUD 的时候，我们每次都需要新建一个连接 Connection。

创建连接和关闭连接的过程也是比较消耗时间的，当线程数量很大的时候，系统就会变得卡顿。

连接池就是为了解决这个问题。连接池的设计哲学是: 总是借，而不创建。

我们在一开始先创建一定数量的连接 Connection，然后每次有请求连接的时候，就找空闲的连接分配过去。如果没有空闲，则需要等待。

# 实现连接池

 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
51
52
53
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ConnectionPool {
    int size;
    List<Connection> conns = new ArrayList<Connection>();

    public ConnectionPool(int size) {
        //构造器
        this.size = size;
        init();
    }

    public void init() {
        //初始化连接池
        try {
            // Class.forName("com.mysql.jdbc.Driver"); //mysql5
            Class.forName("com.mysql.cj.jdbc.Driver"); // mysql6+
            for (int i = 0; i < size; i++) {
                Connection conn = DriverManager
                        .getConnection("jdbc:mysql://127.0.0.1:3306/booklib", "root", "123456");
                conns.add(conn);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public synchronized Connection getConnection() {
        //获得一个连接
        while (conns.isEmpty()) {
            try {
                this.wait();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        Connection conn = conns.remove(0);
        return conn;
    }

    public synchronized void returnConnection(Connection conn) {
        //返还一个连接
        conns.add(conn);
        this.notifyAll();
    }
    
}

# 使用开源连接池(以 C3P0 为例)

一些著名的开源连接池

DBCP
C3P0

C3P0连接池 的使用:

 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
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class ConnectionPoo {
    private static final String url="jdbc:mysql://localhost:3306/booklib";
    private static final String user="root";
    private static final String password="123456";
    private static ComboPooledDataSource dataSource;

    static{
        try {
            dataSource = new ComboPooledDataSource();
            dataSource.setUser(user);
            dataSource.setPassword(password);
            dataSource.setJdbcUrl(url);
            //dataSource.setDriverClass("com.mysql.jdbc.Driver");
            dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            dataSource.setInitialPoolSize(5);
            dataSource.setMinPoolSize(1);
            dataSource.setMaxPoolSize(10);
            dataSource.setMaxStatements(50);
            dataSource.setMaxIdleTime(60);
        } catch (PropertyVetoException e) {
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();
    }
}

c3p0 v0.9.2版本之后，从中分离了一个 mchange-commons-java 包，作为使用 c3p0 的辅助包。我们这里调用的就是辅助包。

# OR映射

# JavaBean

一个 JavaBean 对象需要满足的条件:

提供一个默认的无参构造函数。
需要被序列化并且实现 Serializable 接口。
一系列可读写属性。
一系列的 getter 或 setter 方法。

所有对 JavaBean 属性的访问都应当使用 getter 和 setter 方法。

JavaBean 是一个可复用的组件，把应用的业务逻辑和显示逻辑分离开，降低了开发的复杂程度和维护成本。

# POJO (Plain Ordinary Java Object)

POJO 是纯粹的 JavaBean。

JavaBean除了满足上面的条件，没有规定你不能定义其他东西，就算你把一些业务的代码加入进来也没关系。

POJO 不允许有业务方法，也不能携带 Connection 之类的方法。

一个简单的 POJO 对象:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public class BeanPublisher {
    private String pubid;
    private String publisherName;
    private String address;
    public String getPubid() {
        return pubid;
    }
    public void setPubid(String pubid) {
        this.pubid = pubid;
    }

    public String getPublisherName() {
        return publisherName;
    }
    public void setPublisherName(String publisherName) {
        this.publisherName = publisherName;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

# EntityBean 与 OR映射

OR映射是把数据库对象和编程语言中的对象映射在一起，他们拥有一样的属性。

EntityBean 一般用于ORM对象关系映射，一个实体映射成一张表。

它能执行很多自动化操作:

创建一个 EntityBean 对象相当于创建一条记录
删除一个 EntityBean 对象会同时从数据库中删除对应记录
修改一个 EntityBean 时，容器会自动将 EntityBean 的状态和数据库同步

一些流行的 ORM 框架:

Hibernate
MyBatis