JDBC实现MySQL连接与CRUD

一、JDBC与数据库的关系

首先，我们知道数据库有很多种类。不同的数据库厂商拥有不同的数据库，也就有不同的数据库驱动程序。为了让Java程序连接数据库，我们可以让我们的Java程序直接调用数据库的驱动程序，让驱动程序帮助我们完成数据库的操作。

我们会注意到，驱动程序是由很多复杂的文件构成的。我们需要让java程序采用不同的格式与规范来书写，以便访问不同的驱动程序。这样，java程序与数据库驱动程序之间的耦合程度就很高。为了降低耦合程度，Sun公司制定了数据库驱动程序的一种规范，它要求所有的数据库驱动程序必须实现一些接口类。而这些接口类的总称就是JDBC。

二、连接数据库

方法一：使用JDBC手动连接数据库

1. 加载数据库的驱动

我们可以new com.mysql.jdbc.Driver()来加载驱动driver，但推荐用反射，调用类加载器，动态加载该类。代码如下：

String className = "com.mysql.jdbc.Driver";
Driver driver = Class.forName(className);

2. 注册驱动

调用java.sql.DriverManager类来完成。这很明显是sun公司写的类。

DriverManager.registerDriver(driver);

由于com.mysql.jdbc.Driver类在加载时用static就帮我们注册好了该driver，所以这行代码可以不用写。当然，mysql的包，该导的还是要导。如果想用Maven，要在pom中加入以下依赖：

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.35</version>
</dependency>

3. 填上数据库连接参数，连接数据库

用DriverManager类进行连接。连接的参数分别为url、username、password。

代码如下（要导的包我这里省掉了）：

@Test
public void test1() throws ClassNotFoundException, SQLException {
    /* connect to MySQL */

    /* 1. load driver
     * 2. regist driver(omit)
     */
    String className = "com.mysql.jdbc.Driver";
    // reflection
    Class.forName(className);

    /* 3. connect with information */
    String url = "jdbc:mysql://localhost:3306/book";
    String user = "root";
    String password = "123456";
    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println(connection);
}

下面我们把连接时的数据库url和账户信息username、password写到配置文件中去：

@Test
public void test2() throws ClassNotFoundException, SQLException, IOException {
    /* connect to MySQL using properties */
    Properties pro = new Properties();
    // For idea maven project, the default loader path is src/main/resources */
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
    pro.load(is);

    String url = pro.getProperty("url");
    String user = pro.getProperty("user");
    String password = pro.getProperty("password");
    String className = pro.getProperty("className");

    Class.forName(className);

    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println(connection);
}

方法二：借助数据库连接池Datasource来连接数据库

本例中使用阿里巴巴公司开源的druid数据库连接池。如果用maven导包，需要加入以下依赖：

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>

1. 从配置文件中获取数据库连接配置

首先创建Properties类的对象，然后让该对象加载配置文件对应的文件流。文件流可以用系统类构造器来创建。

2. 创建Datasource，并加载配置

在加载完配置文件之后，我们就可以创建DataSource了。每一个DataSource会包含一些属性，如驱动程序、数据库位置、用户名、密码、最大连接数量等。这些属性就是配置文件里中的内容。
配置文件druid.properties：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/book
username=root
password=123456
maxActive=10

3. 连接数据库

这里是说，先创建数据库连接池，然后再从连接池中获取一条连接。

代码如下：

public class DruidTest {
    @Test
    public void test1() throws Exception {
        // properties
        Properties pro = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);

        // create a datasource(connection pool) by properties
        DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);

        // connect
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }
}

4. 释放连接

获得的资源要记得释放哦。

三、构建JDBCUtils类

既然咱们每次在使用数据库的时候都需要创建连接池、建立连接以及释放连接，那么我们不妨把这些操作封装起来，放到一个工具类里面。这个工具类就叫做JDBCUtils。

可以用static代码块实现当加载JDBCUtils时，创建连接池。然后在类里面加上两个静态方法：建立连接和释放连接。

JDBCUtils类的代码如下：

public class JDBCUtils {
    /* One datasource was created when loading this class */
    public static DruidDataSource druidDataSource;

    static {
        Properties properties = new Properties();
//        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("JDBCUtils.properties");
        InputStream is =
                JDBCUtils.class.getClassLoader().getResourceAsStream("JDBCUtils.properties");

        try {
            if(is != null) {
                properties.load(is);
            } else {
                System.out.println("Doesn't get stream of file JDBCUtils.properties.");
            }
            druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @return connection created by druidDataSource, not null if succeed, null if fail
     * @throws SQLException
     */
    public static Connection getConnection() {
        DruidPooledConnection connection = null;
        try {
            connection = druidDataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    public static void closeConnection(Connection connection) {
        try {
            if(connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

到目前位置，我们已经可以连接数据库了。进入了数据库的大门之后，我们可以做哪些事情呢？CRUD！

四、借助QueryRunner做CRUD

QueryRunner的全类名为org.apache.commons.dbutils.QueryRunner。如果使用maven导包，需要加上以下依赖：

<!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
<dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.6</version>
</dependency

1. update, delete, insert

为了实现增、删、改操作，我们可以使用QueryRunner的update方法。该方法有三个参数：分别为连接、sql、可变形参。

可变形参需要配合占位符?来使用。比如如下sql语句：

String sql = "select * from User where id = ? and name = ?"

这里的两个?就是占位符。如果可变形参分别为3、"XiaoMing"的话，这个sql语句的含义就是在User表中查找id=3且name="XiaoMing"的记录。

2. select

为了实现select语句，我们可以使用query方法。QueryRunner中的query方法有四个参数：分别为连接、sql、handler、可变形参。值得注意的是，该方法是泛型方法。它返回的对象的类型为handler参数中的泛型类的参数类型。

为什么要多一个handler参数呢？因为查询语句是带返回结果的。这个返回的结果如果是在数据库中是以表格的形式显示出来的，而传到java程序里，我们就要给它指定类型。通常我们喜欢用JavaBean的类型来保存。

handler是个泛型类：ResultSetHandler<T>，它的实现类可以把查询返回的结果转换成T的类型。（这里的表述应该不对，想想为什么）

handler有许多的实现类，我们主要关注以下五个：

• BeanHandler
• BeanListHandler
• MapHandler
• MapListHandler
• ScalarHandler

在实际的使用中，我们一般使用BeanHandler、BeanListHandler和ScalarHandler。我们这里只看BeanListHandler。

咱们系统地对QueryRunner的query方法进行探究：

public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException;

该方法返回sql的查询结果，并将查询结果转换为T的类型。由于T本身和QueryRunner是不是泛型类，带不带泛型方法等都无关，所以query方法是个泛型方法。该方法中rsh参数不太好理解。下面我们对其进行探究：

1. 点进QueryRunner的query方法，通过源码查看参数类型，发现ResultSetHandler<T> rsh是对目标结果类型T的封装

2. 查看ResultSetHandler源码，探究它是如何对T进行封装

3. 发现它是一个接口，具有唯一的抽象方法handle，它把ResultSet转换成T的类型（注意这里的T可以看成已知的类型）

4. 既然它是一个接口，那么我们就查看它的实现类。按住Ctrl+H，我们从中挑选BeanListHandler进行查看

5. BeanListHander也是一个泛型类：public class BeanListHandler<T> implements ResultSetHandler<List<T>>
   它能够实现接口的方法——把resultSet转换成List<T>的类型。
   思考：我们这里不一定要弄懂，但是可以尽可能地探究，还可以看看它写得对不对。

6. 现在，我们应该是可以预测BeanListHandler<T>的handle方法会是怎么写的

7. 由于BeanListHandler本身是泛型类，所以在实例化的时候可以把T确定下来，这样也就知道了List<T>。而handle返回的对象也就是List<T>，handle的形参至少也要有sql的结果集。handle应该不是泛型方法，因为我们不需要额外的参数类型。

8. Ctrl+F搜索handle方法，果不其然：

@Override
public List<T> handle(ResultSet rs) throws SQLException {
 return this.convert.toBeanList(rs, type);
}

9. 现在我们可以回到QueryRunner的query方法。我们自己已经写好了一个类，叫做User。里面有user的id、name、mail信息。我们希望查询结果保存在User这样的JavaBean对象中，因此可以知道，我们现在需要传入BeanListHandler<User>类型的对象。这个对象可以通过new BeanHandler<>(User.class)得到。

/* 写好的User类 */
public class User {
    private Integer id;
    private String name;
    private String password;
    private String mail;

    public User() {
    }

    public User(Integer id, String name, String password, String mail) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.mail = mail;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }
    
    /* 此处省略get方法、set方法和toString方法 */
}

可以发现，泛型接口的T，不会在其实例化的时候确定，而会在其实现的时候通过实现类确定。我们使用了BeanListHandler<User>的类型。它实现了ResultSetHandler<List<T>>。handle方法将返回<List<T>>类型的对象，成功实现了handle的接口。

因此之前说“handler是个泛型类：ResultSetHandler<T>，它的实现类可以把查询返回的结果转换成T的类型。（这里的表述应该不对，想想为什么）”，是因为，实现类返回的结果不一定是T。就拿BeanListHandler<T>implements ResultSetHandler<List<T>>的例子来说， 在实现的时候，已经把这个T确定为List<T>了。

对于一般的查询语句，返回的是数据库表中的某一行。因此类型T一般为JavaBean的类型，在我们的代码中，User类就是一个JavaBean。于是在使用query方法前，我们需要先把JavaBean的类写好，然后再去调用方法。

至于JavaBean是什么，我引用下百度百科的定义：

​ JavaBean 是一种JAVA语言写成的可重用组件。为写成JavaBean，类必须是具体的和公共的，并且具有无参数的构造器。JavaBean 通过提供符合一致性设计模式的公共方法将内部域暴露成员属性，set和get方法获取。众所周知，属性名称符合这种模式，其他Java 类可以通过自省机制(反射机制)发现和操作这些JavaBean 的属性。

简单来说，就是属性为private，想要获得或改变属性就必须要用get、set方法的类。这些类往往能够清晰地描述现实中的某一事物。

五、Dao持久层

现在来看，我们已经可以通过query方法实现CRUD了。但是呢，还需要写存储过程。于是啊，光是在User表上的存储过程就有好多个。并且，我们能够发现，如果要在Java语言中写这些存储过程，我们将会反反复复调用QueryRunner类的query或update方法，而且是每写一个sql必调一个。于是，可以想，我们不如把query和update方法各自额外封装成新的方法，这样可以省去new QueryRunner这个类以及制造handler对象的麻烦。但是，仅此这样还是不够的。不仅在User表上要调用query或update方法，而且还要再其它表上调用这样的方法。如果只封装成函数，那不免会出现函数重复的情况。因此，我们把query方法、update方法封装成类，然后再让其它需要使用它们的类继承这个类就可以了。

这个父类就是BaseDao。它是持久层的一个基本的类。持久层还有其它的接口或类，如我们这里还要有UserDao和UserDaoImpl。其中UserDaoImpl继承BaseDao，并实现UserDao中定义的接口。

BaseDao的代码：

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @description Finish some basic CRUD functions, which I like to called "engine".
 * @author Zheng Xunzhe
 * @date 2021-07-31
 */
public class BaseDao {
    QueryRunner queryRunner = new QueryRunner();

    /**
     * update, insert, delete sql
     * @param conn connection created by user
     * @param sql required sql
     * @param objs specify '?' in sql
     * @return the number of rows affected
     * @throws SQLException
     */
    public int update(Connection conn, String sql, Object ... objs) throws SQLException {
        int count = queryRunner.update(conn, sql, objs);
        return count;
    }


    /**
     * query for only one result
     * @param conn connection created by user
     * @param sql required sql
     * @param clazz object of Class<T>
     * @param objs specify '?' in sql
     * @param <T> type that results will be transformed
     * @return one JavaBean objects of type T
     * @throws SQLException
     */
    public <T> T getInstance(Connection conn, String sql, Class<T> clazz, Object ... objs) throws SQLException {
        BeanHandler<T> handler = new BeanHandler<T>(clazz);
        T query = queryRunner.query(conn, sql, handler, objs);
        return query;
    }

    /**
     * query for several results
     * @param conn connection created by user
     * @param sql required sql
     * @param clazz object of Class<T>
     * @param objs specify '?' in sql
     * @param <T> type that results will be transformed
     * @return a list of JavaBean objects of type T
     * @throws SQLException
     */
    public <T> List<T> getInstances(Connection conn, String sql, Class<T> clazz, Object ... objs) throws SQLException {
        BeanListHandler<T> handler = new BeanListHandler<>(clazz);
        List<T> query = queryRunner.query(conn, sql, handler, objs);
        return query;
    }

    /**
     *
     * @param conn connection created by user
     * @param sql required sql
     * @param objs specify '?' in sql
     * @return sql results, whose type is corresponded to sql
     * @throws SQLException
     */
    public Object scalarInstance(Connection conn, String sql, Object ... objs) throws SQLException {
        ScalarHandler<Object> handler = new ScalarHandler<>();
        Object query = queryRunner.query(conn, sql, handler, objs);
        return query;
    }
}

然后再UserDao中写上存储过程的声明，也就是java的接口：

import com.zhengxunzhe.bean.User;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author Zheng Xunzhe
 * @date 2021-07-31
 */
public interface UserDao {
    /**
     * @param conn connection created by user
     * @param name name in table User, which is unique
     * @return JavaBean type of User
     * @throws SQLException
     */
    public User getUserByName(Connection conn, String name) throws SQLException;

    public User getUserById(Connection conn, Integer id) throws SQLException;
    public int addUser(Connection conn, User user) throws SQLException;
}

最后再用UserDaoImpl实现UserDao中的接口：

package com.zhengxunzhe.dao.impl;

import com.zhengxunzhe.bean.User;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author Zheng Xunzhe
 * @date 2021-07-31
 */
public class UserDaoImpl extends BaseDao implements UserDao{
    @Override
    public User getUserByName(Connection conn, String name) throws SQLException {
        String sql = "select * from `User` where name = ?";
        return getInstance(conn, sql, User.class, name);
    }

    @Override
    public User getUserById(Connection conn, Integer id) throws SQLException {
        String sql = "select * from `User` where id = ?";
        return getInstance(conn, sql, User.class, id);
    }

    @Override
    public int addUser(Connection conn, User user) throws SQLException {
        String sql = "insert into `User`(id, name, password, mail) values(?, ?, ?, ?)";
        return update(conn, sql, user.getId(), user.getName(), user.getPassword(), user.getMail());
    }
}

最后的最后，别忘了测试：

package com.zhengxunzhe.test;

import com.zhengxunzhe.bean.User;
import com.zhengxunzhe.dao.impl.UserDaoImpl;
import com.zhengxunzhe.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @author Zheng Xunzhe
 * @date 2021-07-31
 */
public class UserDaoImplTest {
    Connection conn = JDBCUtils.getConnection();
    @Test
    public void test1() {
        User user = new User(4, "Hello4", "sfaj", "ddd@qq.com");

        UserDaoImpl userDao = new UserDaoImpl();
        int count = 0;
        try {
            count = userDao.addUser(conn, user);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(conn);
            System.out.println(count);
        }
    }

    @Test
    public void test2() {
        User user = null;

        UserDaoImpl userDao = new UserDaoImpl();
        try {
            user = userDao.getUserByName(conn, "Hello");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(conn);
            System.out.println(user);
        }
    }

    @Test
    public void test3() {
        User user = null;

        UserDaoImpl userDao = new UserDaoImpl();
        User count = null;
        try {
            user = userDao.getUserById(conn, 1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeConnection(conn);
            System.out.println(user);
        }
    }
}

现在，我们可以快乐地用Java在MySQL数据库中增删查改了！