68. JDBC操作MySQL数据库

2017-12-25

java

对于熟悉PHP的人来说，最熟悉的莫过于PDO(PHP Data Object)这个词了。而且Web后端更多的时候也确实是存取数据，反而对代码性能和封装没太大的讲究。虽然偶尔会有人问我，你觉得一个人，代码写的漂亮，特别好；另一个人代码写的一般，你更倾向于哪一种？我说，如果非要选一种，那肯定是写的漂亮又好的还稳定的。但事实上，并不是这么回事。对很多人来说，二者并不是统一的。

有段时间我还学过Python，但是对于Python操作MySQL数据库相当的泄气，以至于后来我就放弃这门语言了。说实话，这门语言比PHP还有实用的多，而且也是一种新潮的趋势，越来越多的人开始使用这门语言。而且这门语言本身的能力也比PHP要强大的多，可以做很多PHP做不了的事情。

这几天在疯狂的苦学Java，由于之前学过一段时间的Go，觉得差别不是很大，相对来说还是挺容易的。终于学到操作数据库这一节，这应该是对于使用动态语言的人来说，最为期待的一幕了。在动态语言的世界里，数据库相关的操作，占据了业务的大多数，也占据了代码量相当的一部分。觉得静态语言会不会也很难，当然Go使用MySQL数据库是相当的简单，但是Go的DB包不是特别的好用，不过已经非常完美了。

让我感到惊讶的是，Java操作MySQL也是如此的简单，得益于jar包的便利，加入一个扩展是如此的方便和快捷。

一、用户基类

package test;

/**
 * 用户信息
 * 
 * @author zhgxun
 *
 */
public class User {
    public long id;
    public String name;
    public int age;

    public User() {

    }

    public User(long id, String name, int age) {
        this.id = id;
        this.name = name.trim();
        this.age = age;
    }

    public User(String name, int age) {
        this.name = name.trim();
        this.age = age;
    }

    @Override
    public String toString() {
        return "<String: id= " + this.id + ", name= " + this.name + ", age= " + this.age + ">";
    }
}

二、基本查询

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 使用JDBC连接MySQL数据库
 * 
 * @author zhgxun
 *
 */
public class DB {

    static final String JDBC_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    static final String JDBC_USER = "root";
    static final String JDBC_PASSWORD = "";

    public static void main(String[] args) {
        try {
            // 获取所有用户信息
            List<User> users = new ArrayList<>();
            users = getAllUsers();
            for (User user : users) {
                System.out.println(user);
            }

            // 更新数据记录
            User u = users.get(0);
            u.name = "张十三";
            update(u);

            // 删除跟更新数据类似

            // 新增数据
            long id = insert(new User("关晓彤", 50));
            System.out.printf("新增数据主键：%d\n", id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取用户信息, 注意try()和try...catch() {}的语法格式
     * 
     * @return
     * @throws SQLException
     */
    private static List<User> getAllUsers() throws SQLException {
        try (Connection conn = getConnection()) {
            // 总是优先使用PreparedStatement
            try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM user")) {
                // 查询结果是ResultSet
                try (ResultSet rs = ps.executeQuery()) {
                    List<User> list = new ArrayList<>();
                    while (rs.next()) {
                        long id = rs.getLong("id");
                        String name = rs.getString("name");
                        int age = rs.getInt("age");
                        User std = new User(id, name, age);
                        list.add(std);
                    }
                    return list;
                }
            }
        }
    }

    /**
     * 更新记录
     * 
     * @param u
     * @throws SQLException
     */
    private static void update(User u) throws SQLException {
        try (Connection conn = getConnection()) {
            // 使用PreparedStatement的executeUpdate()进行更新
            try (PreparedStatement p = conn.prepareStatement("UPDATE user SET name=? WHERE id = ?")) {
                // 占位符从1开始
                p.setObject(1, u.name);
                p.setObject(2, u.id);
                // 更新操作包括UPDATE、INSERT和DELETE语句
                // 更新结果是int
                int n = p.executeUpdate();
                System.out.println("Update rows: " + n);
            }
        }
    }

    /**
     * 新增数据
     * 
     * @param u
     * @return
     * @throws SQLException
     */
    private static long insert(User u) throws SQLException {
        try (Connection conn = getConnection()) {
            try (PreparedStatement ps = conn.prepareStatement("INSERT INTO user (name, age) VALUES (?, ?)",
                    Statement.RETURN_GENERATED_KEYS)) {
                ps.setObject(1, u.name);
                ps.setObject(2, u.age);
                int n = ps.executeUpdate();
                System.out.println(n + " inserted.");
                try (ResultSet rs = ps.getGeneratedKeys()) {
                    if (rs.next()) {
                        long id = rs.getLong(1);
                        u.id = id;
                        return id;
                    }
                }
            }
        }
        return 0;
    }

    /**
     * 连接数据库
     * 
     * @return
     * @throws SQLException
     */
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
    }
}

输出

<String: id= 1, name= 张三, age= 18>
<String: id= 2, name= lisi, age= 20>
<String: id= 3, name= wangwu, age= 29>
<String: id= 4, name= maliu, age= 45>
<String: id= 5, name= 田七, age= 30>
<String: id= 6, name= 马云, age= 50>
Update rows: 1
1 inserted.
新增数据主键：7

三、事务处理

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 事务处理
 * 
 * @author zhgxun
 *
 */
public class DBTx {
    static final String JDBC_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    static final String JDBC_USER = "root";
    static final String JDBC_PASSWORD = "";

    public static void main(String[] args) {
        // 打印用户列表
        List<User> users = null;
        try {
            users = getUsers();
            for (User user : users) {
                System.out.println(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 开启更新事务
        System.out.println("开启更新事务");
        Connection conn = null;
        try {
            // 连接数据库
            conn = getConnection();
            // 设置事务隔离级别为提交读
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            // 开启事务, 开启事务即是关闭自动提交即可
            conn.setAutoCommit(false);
            // 更新用户信息
            update(conn, new User(users.get(0).id, "杨幂", 25));
            update(conn, new User(users.get(1).id, "李思思", 20));
            // 提交事务
            conn.commit();
            // 事务执行成功
            System.out.println("事务执行成功");
        } catch (SQLException e) {
            // 回滚事务
            try {
                conn.rollback();
            } catch (SQLException e1) {
                System.out.println("回滚事务失败");
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            // 尝试关闭数据库连接
            try {
                // 恢复数据库自动提交记录
                conn.setAutoCommit(true);
                conn.close();
            } catch (SQLException e) {
                System.out.println("数据库关闭失败");
                e.printStackTrace();
            }
        }

        // 事务后重新打印所有用户列表
        System.out.println("事务后重新打印所有用户列表");
        try {
            users = getUsers();
            for (User user : users) {
                System.out.println(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 模拟事务回滚
        System.out.println("模拟事务回滚");
        try {
            // 连接数据库
            conn = getConnection();
            // 设置事务隔离级别为提交读
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            // 开启事务, 开启事务即是关闭自动提交即可
            conn.setAutoCommit(false);
            // 更新用户信息
            update(conn, new User(users.get(0).id, "周迅", 28));
            update(conn, new User(users.get(1).id, "卓依婷", 24));
            // 提交事务
            conn.commit();
            // 人为制造异常终止事务使其回滚, 不过后续代码也无法正常执行了
            throw new RuntimeException("强制数据库回滚");
        } catch (SQLException e) {
            // 回滚事务
            try {
                conn.rollback();
            } catch (SQLException e1) {
                System.out.println("回滚事务失败");
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            // 尝试关闭数据库连接
            try {
                // 恢复数据库自动提交记录
                conn.setAutoCommit(true);
                conn.close();
            } catch (SQLException e) {
                System.out.println("数据库关闭失败");
                e.printStackTrace();
            }
        }

        // 回滚事务后再次查看用户列表
        System.out.println("回滚事务后再次查看用户列表");
        try {
            users = getUsers();
            for (User user : users) {
                System.out.println(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据库连接
     * 
     * @return
     * @throws SQLException
     */
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
    }

    /**
     * 获取用户列表
     * 
     * @return
     * @throws SQLException
     */
    private static List<User> getUsers() throws SQLException {
        try (Connection conn = getConnection()) {
            try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM user")) {
                try (ResultSet rs = ps.executeQuery()) {
                    List<User> users = new ArrayList<>();
                    while (rs.next()) {
                        long id = rs.getLong("id");
                        String name = rs.getString("name");
                        int age = rs.getInt("age");
                        users.add(new User(id, name, age));
                    }
                    return users;
                }
            }
        }
    }

    /**
     * 根据id更新用户姓名
     * 
     * @param conn
     * @param user
     * @throws SQLException
     */
    private static void update(Connection conn, User user) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement("UPDATE user SET name = ? WHERE id = ?")) {
            ps.setObject(1, user.name);
            ps.setObject(2, user.id);
            ps.executeUpdate();
        }
    }

}

输出

<String: id= 1, name= 周迅, age= 18>
<String: id= 2, name= 卓依婷, age= 20>
<String: id= 3, name= wangwu, age= 29>
<String: id= 4, name= maliu, age= 45>
<String: id= 5, name= 田七, age= 30>
<String: id= 6, name= 马云, age= 50>
<String: id= 7, name= 关晓彤, age= 50>
开启更新事务
事务执行成功
事务后重新打印所有用户列表
<String: id= 1, name= 杨幂, age= 18>
<String: id= 2, name= 李思思, age= 20>
<String: id= 3, name= wangwu, age= 29>
<String: id= 4, name= maliu, age= 45>
<String: id= 5, name= 田七, age= 30>
<String: id= 6, name= 马云, age= 50>
<String: id= 7, name= 关晓彤, age= 50>
模拟事务回滚
Exception in thread "main" java.lang.RuntimeException: 强制数据库回滚
    at test.DBTx.main(DBTx.java:98)

四、连接池

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库连接池
 */
public class DBSource {
    // 配置MySQL连接属性
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    private static final String JDBC_USER = "root";
    private static final String JDBC_PASSWORD = "";

    /**
     * 使用连接池查询数据
     *
     * @param args 输入参数
     */
    public static void main(String[] args) {
        // 创建连接池
        DataSource dataSource = create();
        // 保持线程实例
        List<Thread> threads = new ArrayList<>();
        // 启动4个线程来查询用户列表
        for (int i = 1; i <= 4; i++) {
            // 准备线程池
            Thread thread = new Thread() {
                public void run() {
                    // 暂缓一段时间
                    try {
                        Thread.sleep((long) (Math.random() * 1000));
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                    try {
                        List<User> users = getUsers(dataSource);
                        for (User user : users) {
                            System.out.println(user);
                        }
                    } catch (SQLException e) {
                        e.getStackTrace();
                    }
                }
            };
            threads.add(thread);
        }
        // 启动线程
        for (Thread thread : threads) {
            thread.start();
        }
        for (Thread thread : threads) {
            try {
                thread.join();
            } catch (InterruptedException e) {
                e.getStackTrace();
            }
        }
        // 关闭连接池
        ((HikariDataSource) dataSource).close();
    }

    /**
     * 配置数据库连接池
     *
     * @return DataSource
     */
    private static DataSource create() {
        // HikariCP是JDBC连接池组件
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(JDBC_URL);
        config.setUsername(JDBC_USER);
        config.setPassword(JDBC_PASSWORD);
        // 连接超时
        config.addDataSourceProperty("connectionTimeout", 1000);
        // 空闲超时
        config.addDataSourceProperty("idleTimeout", 1000);
        // 最大连接数
        config.addDataSourceProperty("maximumPoolSize", 10);

        return new HikariDataSource(config);
    }

    /**
     * 通过连接池获取用户列表
     *
     * @param dataSource 数据库连接池
     * @return List<User>
     * @throws SQLException 异常处理
     */
    private static List<User> getUsers(DataSource dataSource) throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            System.out.println("Using connection: " + conn);
            try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM user")) {
                try (ResultSet rs = ps.executeQuery()) {
                    return handleUsers(rs);
                }
            }
        }
    }

    /**
     * 获取用户列表
     *
     * @param rs ResultSet 结果集
     * @return List<User>
     * @throws SQLException 异常处理
     */
    private static List<User> handleUsers(ResultSet rs) throws SQLException {
        List<User> users = new ArrayList<>();
        while (rs.next()) {
            long id = rs.getLong("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            users.add(new User(id, name, age));
        }
        return users;
    }
}

输出

Using connection: HikariProxyConnection@419263385 wrapping com.mysql.cj.jdbc.ConnectionImpl@a0deed5
Using connection: HikariProxyConnection@1809139274 wrapping com.mysql.cj.jdbc.ConnectionImpl@6512b540
<User: id = 1, name= 周迅, age = 18>
<User: id = 2, name= 卓依婷, age = 20>
<User: id = 3, name= wangwu, age = 29>
<User: id = 4, name= maliu, age = 45>
<User: id = 5, name= 田七, age = 30>
<User: id = 6, name= 马云, age = 50>
<User: id = 7, name= 关晓彤, age = 50>
<User: id = 1, name= 周迅, age = 18>
<User: id = 2, name= 卓依婷, age = 20>
<User: id = 3, name= wangwu, age = 29>
<User: id = 4, name= maliu, age = 45>
<User: id = 5, name= 田七, age = 30>
<User: id = 6, name= 马云, age = 50>
<User: id = 7, name= 关晓彤, age = 50>
Using connection: HikariProxyConnection@1352891292 wrapping com.mysql.cj.jdbc.ConnectionImpl@a0deed5
<User: id = 1, name= 周迅, age = 18>
<User: id = 2, name= 卓依婷, age = 20>
<User: id = 3, name= wangwu, age = 29>
<User: id = 4, name= maliu, age = 45>
<User: id = 5, name= 田七, age = 30>
<User: id = 6, name= 马云, age = 50>
<User: id = 7, name= 关晓彤, age = 50>
Using connection: HikariProxyConnection@1970931927 wrapping com.mysql.cj.jdbc.ConnectionImpl@a0deed5
<User: id = 1, name= 周迅, age = 18>
<User: id = 2, name= 卓依婷, age = 20>
<User: id = 3, name= wangwu, age = 29>
<User: id = 4, name= maliu, age = 45>
<User: id = 5, name= 田七, age = 30>
<User: id = 6, name= 马云, age = 50>
<User: id = 7, name= 关晓彤, age = 50>