2020-02-21

JDBC学习笔记

关于 JAR 包

用到的 JAR 包

mysql-connector-java-8.0.17.jar
c3p0-0.9.5.5.jar
mchange-commons-java-0.2.19.jar
commons-dbcp2-2.7.0.jar
commons-pool2-2.8.0.jar
commons-logging-1.2.jar
commons-dbutils-1.7.jar

说明：第 1 个必须要有，是 JDBC 与 MySQL 数据库之间的连接，需要通过它连接数据库从而进行增删改查的操作。第 2 个和第 3 个是使用 C3P0 数据库连接池的时候需要用到的，缺少会报错。4、5、6 是使用 DBCP 数据库连接池的时候需要用到的，缺少会报错。第 7 个是一个工具包，封装了一些对于数据库的基本操作。

IDEA配置JAR包的方法

在工程下面建 lib 文件夹，将 JAR 包复制进去，然后 File - Project Structure - Modules - Dependencies - 右侧的+号 - JARs or directories - 选中JAR包即可

结构设计

src 目录下面主要包括四部分：工具类、DAO 类、Bean 类、以及各种配置文件。

工具类（ JDBCUtil ）主要负责创建与数据库的连接以及资源的关闭操作，连接数据库的方法主要分为两种：一种是利用配置文件 JDBC.properties 进行操作，另一种是利用 C3P0、DBCP 等连接池进行连接。

DAO 类是面向表的数据访问层，封装了对于表的各项操作，主要包括三个部分：BaseDAO、具体类的 DAO 接口、具体类的 DAO 接口的实现。BaseDAO 是一个抽象类，封装了数据表的基本操作，不包含任何业务相关的信息，表对应的具体 DAO 类会继承 BaseDAO。具体类的 DAO 接口用于规范对于表格转存对象 Bean 类的常用操作。具体类的 DAO 接口的实现中会写明具体操作，包括SQL语句。

Bean 类主要用来转移从数据库中查询得到的信息，封装成对象。一般情况下，一张表会对应有一个 Bean 类、一个具体类的 DAO 接口、以及一个具体类的 DAO 接口的实现。

另外需要说明的是，在使用 JDK 11、MySQL 8.0.17 的情况下，配置文件的驱动设置为 com.mysql.jdbc.Driver 会报错，显示已过时，需要设置为 com.mysql.cj.jdbc.Driver。而且配置文件中的 URL 需要加上 ?serverTimezone=UTC，否则会报错。

源码

JDBC.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/database_test?serverTimezone=UTC
user=root
password=***

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="c3p0">
        <!-- 4个基本信息 -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/database_test?serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">***</property>

        <!-- 当数据库连接池中的连接数不够时，c3p0一次性向数据库服务器申请的连接数 -->
        <property name="acquireIncrement">5</property>
        <!-- c3p0数据库连接池中初始化时的连接数 -->
        <property name="initialPoolSize">10</property>
        <!-- c3p0数据库连接池维护的最少连接数 -->
        <property name="minPoolSize">10</property>
        <!-- c3p0数据库连接池维护的最多的连接数 -->
        <property name="maxPoolSize">100</property>
        <!-- c3p0数据库连接池最多维护的Statement的个数 -->
        <property name="maxStatements">50</property>
        <!-- 每个连接中可以最多使用的Statement的个数 -->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>
</c3p0-config>

DBCP.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/database_test?serverTimezone=UTC
username=root
password=***

JDBCUtil

package Util;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {

    //创建与数据库的连接
    public static Connection getConnection() throws Exception {

        //1.创建输入流获取配置文件
        String config = "JDBC.properties";
        InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream(config);

        //2.加载配置文件
        Properties properties = new Properties();
        properties.load(inputStream);

        //3.读取配置信息
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        //4.加载驱动到JVM中，不用注册驱动，因为mysql的驱动实现类会自动注册
        Class.forName(driver);

        //5.创建连接并返回
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    //使用C3P0数据库连接池技术获取连接，连接池建在方法之外，避免每次执行都要新建一个池
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("c3p0");
    public static Connection getConnectionByC3P0() throws Exception {
        Connection connection = cpds.getConnection();
        return connection;
    }

    //创建一个DBCP数据库连接池
    private static DataSource source;
    static{
        try {
            Properties properties = new Properties();
            FileInputStream is = new FileInputStream(new File("src/DBCP.properties"));
            properties.load(is);
            source = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnectionByDBCP() throws Exception{
        Connection connection = source.getConnection();
        return connection;
    }

    //关闭资源的方法，适用于数据库的增删改
    public static void closeResource(Connection c, PreparedStatement ps) {
        try {
            if (ps != null) ps.close();
            if (c != null) c.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //关闭资源的方法，适用于数据库的查询
    public static void closeResource(Connection c, PreparedStatement ps, ResultSet rs) {
        try {
            if (ps != null) ps.close();
            if (c != null) c.close();
            if (rs != null) rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

BaseDAO

package DAO;

import Util.JDBCUtil;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

//DAO: Data Access Object，面向表的数据访问层，封装对于表的增删改查的操作方法，先在数据库设计所有表，再为每个表设计DAO类
//BaseDAO封装了数据表的基本操作，不包含任何业务相关的信息，表对应的具体DAO类会继承BaseDAO
public abstract class BaseDAO<T> {

    private Class<T> clazz;

    public BaseDAO() {}

    {
        //获取当前BaseDAO的子类继承的父类中的泛型
        Type geneticSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parameterizedType = (ParameterizedType) geneticSuperclass;
        Type[] typeArguments = parameterizedType.getActualTypeArguments();
        clazz = (Class<T>) typeArguments[0];
    }

    //通用的数据库的增删改操作，考虑到事务与连接池，则connection由外导入，不在内创建也不关闭
    public static void update(Connection connection, String sql, Object...args) {

        PreparedStatement preparedStatement = null;

        try {
            //1.预编译SQL语句，返回PreparedStatement实例
            //由于Statement存在SQL注入的问题，应避免使用，另外PreparedStatement对于批量操作也更好
            preparedStatement = connection.prepareStatement(sql);
            //2.填充占位符
            for (int i=0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            //3.执行
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.关闭资源
            JDBCUtil.closeResource(null, preparedStatement);
        }
    }

    //可用于不同表的通用的查询操作，返回表中的一条记录，返回值类型T由输入值决定
    public T getInstance(Connection connection, String sql, Object...args) {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //预编译SQL语句
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for (int i=0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            //执行
            resultSet = preparedStatement.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过元数据获取结果集中的列数
            int columnCount = rsmd.getColumnCount();

            //一开始指针指向第一行之前，使用next()将指针指向第一行
            if (resultSet.next()) {
                //实例化t对象
                T t = clazz.getDeclaredConstructor().newInstance();
                //遍历每一列
                for (int i=0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i+1);
                    //获取列名，如果数据库的列名与转存类的属性名不一致，要用getColumnLabel；一致的话可以用getColumnName
                    String columnLabel = rsmd.getColumnLabel(i+1);
                    //通过反射给t对象指定列名的属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtil.closeResource(null, preparedStatement, resultSet);
        }
        return null;
    }

    //可用于不同表的通用的查询操作，返回表中的多条记录，返回值类型T由输入值决定
    public List<T> getAsList(Connection connection, String sql, Object...args) {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //预编译SQL语句
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for (int i=0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            //执行
            resultSet = preparedStatement.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //通过元数据获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<T>();

            //一开始指针指向第一行之前，使用next()将指针指向第一行，while实现指针顺序下滑
            while (resultSet.next()) {
                //实例化t对象
                T t = clazz.getDeclaredConstructor().newInstance();
                //遍历每一列
                for (int i=0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = resultSet.getObject(i+1);
                    //获取列名，如果数据库的列名与转存类的属性名不一致，要用getColumnLabel；一致的话可以用getColumnName
                    String columnLabel = rsmd.getColumnLabel(i+1);
                    //通过反射给t对象指定列名的属性赋值
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtil.closeResource(null, preparedStatement, resultSet);
        }
        return null;
    }

    //用于查询特殊值的通用方法
    public <E> E getValue(Connection connection, String sql, Object...args) {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i=0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return (E) resultSet.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(null, preparedStatement, resultSet);
        }
        return null;
    }

}

TestClassDAO

package DAO;

import Bean.TestClass;

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

//此接口用于规范对于测试类TestClass表格的常用操作
public interface TestClassDAO {

    //将testClass对象添加到数据库中
    void insert(Connection connection, TestClass testClass);

    //删除表中指定id的一条记录
    void deleteById(Connection connection, int id);

    //针对testClass对象，修改表中的记录
    void update(Connection connection, TestClass testClass);

    //查询指定id的testClass对象
    TestClass getTestClassById(Connection connection, int id);

    //查询表中的所有记录构成的集合
    List<TestClass> getAll(Connection connection);

    //返回数据表中数据的条目数
    Long getCount(Connection connection);

    //返回数据表中最大的生日
    Date getMaxBirth(Connection connection);
}

TestClassDAOImpl

package DAO;

import Bean.TestClass;

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

//此类是对于testclass表的具体操作，每一个表都应该对应一个bean、一个接口DAO、以及一个实现类impl
public class TestClassDAOImpl extends BaseDAO<TestClass> implements TestClassDAO {

    @Override
    public void insert(Connection connection, TestClass testClass) {
        String sql = "insert into testclass(id,name,email,birth)values(?,?,?,?)";
        update(connection, sql, testClass.getId(), testClass.getName(), testClass.getEmail(), testClass.getBirth());
    }

    @Override
    public void deleteById(Connection connection, int id) {
        String sql = "delete from testclass where id = ?";
        update(connection, sql, id);
    }

    @Override
    public void update(Connection connection, TestClass testClass) {
        String sql = "update testclass set name=?,email=?,birth=? where id=?";
        update(connection, sql, testClass.getName(), testClass.getEmail(), testClass.getBirth(), testClass.getId());
    }

    @Override
    public TestClass getTestClassById(Connection connection, int id) {
        String sql = "select id,name,email,birth from testclass where id=?";
        TestClass testClass = getInstance(connection, sql, id);
        return testClass;
    }

    @Override
    public List<TestClass> getAll(Connection connection) {
        String sql = "select id,name,email,birth from testclass";
        List<TestClass> list = getAsList(connection, sql);
        return list;
    }

    @Override
    public Long getCount(Connection connection) {
        String sql = "select count(*) from testclass";
        return getValue(connection, sql);
    }

    @Override
    public Date getMaxBirth(Connection connection) {
        String sql = "select max(birth) from testclass";
        return getValue(connection, sql);
    }
}

TestClass

package Bean;

import java.sql.Date;

//定义一个JavaBean，用来转移从数据库查询得到的信息，封装成对象
//JavaBean是一种符合特定规范的类，主要包括private的属性，以及相应的public的getter和setter方法
//此测试类包含4个属性：id，name，email，birth
public class TestClass {

    private int id;
    private String name;
    private String email;
    private Date birth;

    public TestClass() {
        super();
    }

    public TestClass(int id, String name, String email, Date birth) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "TestClass{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }
}

Main（用于部分测试）

import Bean.TestClass;
import DAO.TestClassDAOImpl;
import Util.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.Connection;
import java.sql.Date;

public class Main {

    public static void main(String[] args) {
        Connection connection = null;
        TestClassDAOImpl testClassDAOImpl = new TestClassDAOImpl();
        try {
            connection = JDBCUtil.getConnectionByDBCP();
            System.out.println(connection);
            TestClass testClass = new TestClass(7,"Mike","def@qq.com",new Date(43534646435L));
            testClassDAOImpl.insert(connection, testClass);
            System.out.println("操作成功");

            //使用dbutils进行数据库的操作
            QueryRunner runner = new QueryRunner();
            String sql = "insert into testclass(id,name,email,birth)values(?,?,?,?)";
            runner.update(connection, sql, 8,"Amy","aaa@qq.com",new Date(43534646035L));
            System.out.println("dbutils操作成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(connection, null);
        }
    }
}

YQ

My Hexo Blog