JDBC - 《MySql》 - 极客文档

数据的持久化
持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，持久化的实现过程大多通过各种关系数据库来完成。

获取数据库连接
常用接口
Driver接口：装载Mysql驱动 eg:Class.forName(“com.mysql.jdbc.Driver”);
DriverManager接口：JDBC的管理层，作用于用户和驱动程序之间。跟踪可用的驱动程序，并在数据库和相应的驱动程序之间建立连接
Connection接口: 与特定数据库连接，在连接上下文中执行sql语句并返回结果

JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接

#最初
public void testConnection() throws SQLException {
        Driver driver=new com.mysql.jdbc.Driver();
        String url ="jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai";
        //mysql连接对应的用户名和密码封装在Properties中
        Properties info =new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "mysqlabc");
        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }
#迭代后
public void testConnection3() throws Exception {
        //1 获取driver实现类的对象
        //driver内部的静态块实现了registerDriver
        Class.forName("com.mysql.jdbc.Driver");
        //使用driverManager管理driver
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai", "root", "mysqlabc");
        System.out.println(connection);
    }
#最终
//将数据库连接所需要的4个基本信息放到配置文件中 通过配置文件 获取连接
public void testConnection4() throws Exception {
    //读取配置文件 使用类加载器 自定义的类都是系统类加载器帮忙加载的
    InputStream stream = ConnTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    //读取Java的配置文件
    Properties pros = new Properties();
    pros.load(stream);
    //加载驱动
    Class.forName(pros.getProperty("driverClass"));
    //获取连接
    Connection connection = DriverManager.getConnection(
            pros.getProperty("url"),
            pros.getProperty("user"),
            pros.getProperty("password"));
    System.out.println(connection);
}

使用PreparedStatement实现CRUD操作
Statement接口：用于执行静态sql语句并且返回它所生成结果的对象
三种statement类：
1 Statement：由createStatement创建，用于发送简单的sql语句（不带参数的）
2 PreparedStatement：继承自Statement接口，它表示一条预编译过的 SQL 语句，用于发送含有一个或多个输入参数的sql语句。比statement对象的效率更高，并且可以防止sql注入。一般使用它
3 CallableStatement：继承自PreparedStatement，用于调用存储过程

注：SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user, password FROM user_table WHERE user=’a’ OR 1 = ‘ AND password = ‘ OR ‘1’ = ‘1’) ，从而利用系统的 SQL 引擎完成恶意行为的做法。

DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。

//JdbcUtils类
public class DButils {
    /**
     * 获取数据库的连接
     * @return
     * @throws Exception
     */
    public static Connection getConnections() throws Exception {
        //读取配置文件 使用类加载器 自定义的类都是系统类加载器帮忙加载的
        InputStream stream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        //读取Java的配置文件
        Properties pros = new Properties();
        pros.load(stream);
        //加载驱动
        Class.forName(pros.getProperty("driverClass"));
        //获取连接
        Connection connection = DriverManager.getConnection(
                pros.getProperty("url"),
                pros.getProperty("user"),
                pros.getProperty("password"));
        return connection;
    }
    public static void close(Connection conn, Statement statement) {
        try {
            if(conn!=null) {
                conn.close();
            }
        } catch (SQLException e) {
                e.printStackTrace();
        }
        try {
            if(statement!=null) {
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

#测试PreparedStatement
 public static void testPrepared(){
        Connection conn = null;
        PreparedStatement preparedStatement=null;
        try {
            conn = DButils.getConnections();
            String sql="update users set user_name =? where user_name =?;";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setObject(1, "H");
            preparedStatement.setObject(2, "h");
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(conn, preparedStatement);
        }
    }

#实现通用的 增删改 操作
public static void update(String sql,Object...args) {    //Object...args 可变参数
        Connection conn = null;
        PreparedStatement preparedStatement=null;
        try {
            conn = DButils.getConnections();
            preparedStatement = conn.prepareStatement(sql);
            for (int i =1; i <= args.length; i++) {
                preparedStatement.setObject(i,args[i-1]);    //args数组下标从0开始
            }
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(conn, preparedStatement);
        }
    }

ORM(Object Relationship Mapping)基本思想：
表结构和类对应；表中字段和类的属性对应；表中的记录和对象对应

@Test
    public void testQuery(){
        Connection connections = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connections = DButils.getConnections();
            String sql="select * from users;";
            preparedStatement = connections.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            //处理结果集
            while(resultSet.next()){
                System.out.println(new User(    //封装一个User的bean对象
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4)
                ));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(connections,preparedStatement,resultSet);
        }
    }

 @Test
    public void test4(){
        //sql查询字段名要和数据库中的一致 且order与关键字冲突 要用着重号标识区别
        String sql="select order_id orderId,order_name orderName,order_date orderDate 
                    from `order` where order_id>?";
        order order = testQuery4(sql, 2);
        System.out.println(order);
    }
#针对order表的通用查询操作
 public order testQuery4(String sql,Object...args){
        Connection connections = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connections = DButils.getConnections();
            preparedStatement = connections.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()){
                order order = new order();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = resultSet.getObject(i + 1);
                    //获取列的列名
//                    String columnName = metaData.getColumnName(i+1);
                    //获取列的别名  推荐使用
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Field declaredField = pojo.order.class.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    declaredField.set(order, columnValue);
                }
                return order;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(connections, preparedStatement, resultSet);
        }
        return null;
    }

*针对不同表的查询操作

 @Test
    public void test6(){
        String sql="select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id>?";
        List<order> list = getForList(order.class, sql, 1);
        list.forEach(System.out::println);
    }
    //范型方法  一切都是通过反射去实现的
    public<T> List<T> getForList(Class<T> clazz, String sql, Object...args){
        Connection connections = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connections = DButils.getConnections();
            preparedStatement = connections.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<>();
            while (resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集的每一行数据 给T对象赋值的过程
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = resultSet.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Field declaredField = t.getClass().getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    declaredField.set(t, columnValue);
                    list.add(t);
                }
                return list;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(connections, preparedStatement, resultSet);
        }
        return null;
    }

操作BLOB类型字段
BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。

MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)

类型	大小(字节)
TinyBlob	最大：255
Blob	最大：65K
MediumBlob	最大：16M
LongBlob	最大：4G

注：如果存储的文件过大，数据库的性能会下降

#向数据表中插入blob数据
@Test
    public void testInsert(){
        Connection connections = null;
        PreparedStatement pst = null;
        try {
            connections = DButils.getConnections();
            String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?);";
            pst = connections.prepareStatement(sql);
            pst.setObject(1, "H");
            pst.setObject(2, "2437@163.com");
            pst.setObject(3, "2021-6-17");
            BufferedInputStream fi = new BufferedInputStream(new FileInputStream("2.jpg"));
            pst.setBlob(4, fi);
            pst.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(connections, pst);
        }
    }

#blob文件的获取
@Test
    public void testGet(){
        Connection connections = null;
        PreparedStatement pst = null;
        ResultSet resultSet = null;
        InputStream is=null;
        FileOutputStream fos=null;
        try {
            connections = DButils.getConnections();
            String sql = "select id,name,email,birth,photo from customers where id=?";
            pst = connections.prepareStatement(sql);
            pst.setObject(1, 19);
            resultSet = pst.executeQuery();
            if(resultSet.next()){
                System.out.println(
                    new customers(
                            resultSet.getInt(1),
                            resultSet.getString(2),
                            resultSet.getString(3),
                            resultSet.getDate(4)
                    ));
                Blob blob = resultSet.getBlob("photo");
                is = blob.getBinaryStream();
                fos = new FileOutputStream("1.jpg");
                byte[] buffer = new byte[1024];
                int len;
                while ((len=is.read(buffer))!=-1){
                    fos.write(buffer,0,len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(connections, pst, resultSet);
        }
    }

注：如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

批量插入
JDBC的批量处理语句包括下面三个方法：
addBatch(String)：添加需要批量处理的SQL语句或是参数；
executeBatch()：执行批量处理语句；
clearBatch():清空缓存的数据

@Test
    public void testBatch(){
        Connection connections = null;
        PreparedStatement pst = null;
        try {
            connections = DButils.getConnections();
            //设置不自动提交数据
            connections.setAutoCommit(false);
            String sql = "insert into goods(name) values(?)";
            pst = connections.prepareStatement(sql);
            long star = System.currentTimeMillis();
            for (int i = 0; i <10000 ; i++) {
                pst.setObject(1, "name"+i);
                //1 攒sql
                pst.addBatch();
                if(i%1000==0){
                    //2 执行batch
                    pst.executeBatch();
                    //3 清空batch
                    pst.clearBatch();
                }
            }
            //统一提交数据
            connections.commit();
            long end = System.currentTimeMillis();
            System.out.println("花费时间："+(end-star));
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(connections, pst);
        }
    }

数据库事务
事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

数据一旦提交，就不可以回滚。
当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

@Test
    public void testTrans(){
        Connection conn = null;
        try {
            //1 把多个操作串接在一个连接下
            conn = DButils.getConnections();
            //2 取消自动提交 避免dml操作自动提交事务
            conn.setAutoCommit(false);
            String sql1 = "update user_table set balance=balance-100 where user=?";
            testTX(conn,sql1,"AA");
            System.out.println(1/0);//模拟网络异常
            String sql2 = "update user_table set balance=balance+100 where user=?";
            testTX(conn,sql2,"BB");
            System.out.println("转账成功");
            //提交数据
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //3 出现错误时 回滚数据
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }finally {
            DButils.close(conn,null);
        }
    }
    public int testTX(Connection conn, String sql,Object...args){
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                pst.setObject(i+1,args[i]);
            }
            return pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DButils.close(null, pst );
        }
        return 0;
    }

数据库连接池
普通的JDBC数据库连接：使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏（连接没有断开对象没有被回收），最终将导致重启数据库。
不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
JDBC - 图1
JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口

DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。hibernate官方推荐使用
Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
BoneCP 是一个开源组织提供的数据库连接池，速度快
Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快

C3P0

#C3P0连接
//配置文件
<c3p0-config>
    <named-config name="hellc3p0">
        <!-- 提供获取连接的4个基本信息 -->
        //注意 这4个基本信息得和它系统文件定义的一样 不然不能读出
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai</property>
        <property name="user">root</property>
        <property name="password">abc123</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>
    //注意数据库连接池对象放外面 保证只创造一个连接池对象
    private ComboPooledDataSource cpds = new ComboPooledDataSource("hellc3p0");
    public void testConn2() throws Exception {
        //使用配置文件连接
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }

注：

DBCP

#DBCP连接池
public class dbcpTest {
    private static DataSource source;
    static {
        try {
            Properties pros = new Properties();
            InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
            pros.load(resource);
            source = BasicDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @Test
    public void getConn() throws Exception {
        Connection connection = source.getConnection();
        System.out.println(connection);
    }
}

Druid

#配置文件
url=jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai
username=root
password=abc123
driverClassName=com.mysql.cj.jdbc.Driver
initialSize=10
//最大连接池数量
maxActive=10

#druid数据库
public class druidTest {
    private static DataSource dataSource;
    static {
        try {
            Properties properties = new Properties();
            InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            properties.load(resource);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @Test
    public void testConn() throws Exception {
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }
}

Apache-DBUtils实现CRUD操作
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

#增删改操作
public void test1(){
       Connection connection = null;
       try {
           QueryRunner queryRunner = new QueryRunner();
           connection = DButils.druidConn();
           String sql="insert into customers(name,email,birth) values(?,?,?)";
           queryRunner.update(connection,sql,"H","H@163.com",new Date(2021-6-17));
       } catch (SQLException throwables) {
           throwables.printStackTrace();
       }finally {
           DButils.close(connection, null);
       }
    }

查询

# BeanHandler  返回一个对象
public void test2(){
    Connection connection = null;
    try {
        QueryRunner queryRunner = new QueryRunner();
        connection = DButils.druidConn();
        String sql="select * from customers where id=?";
        BeanHandler<customers> handler = new BeanHandler<customers>(customers.class);
        customers customer = queryRunner.query(connection, sql, handler, 13);
        System.out.println(customer);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        DButils.close(connection, null);
    }
}

# BeanListHandler 返回多个对象
public void test3(){
    Connection connection = null;
    try {
        QueryRunner queryRunner = new QueryRunner();
        connection = DButils.druidConn();
        String sql="select * from customers where id>?";
        BeanListHandler<customers> handler = new BeanListHandler<>(customers.class);
        List<customers> query = queryRunner.query(connection, sql, handler, 3);
        query.forEach(System.out::println);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        DButils.close(connection, null);
    }
}

#  MapHandler MapListHandler 以键值对的形式返回对象信息
public void test4(){
    Connection connection = null;
    try {
        QueryRunner queryRunner = new QueryRunner();
        connection = DButils.druidConn();
        String sql="select * from customers where id>?";
        MapListHandler handler = new MapListHandler();
        List<Map<String, Object>> query = queryRunner.query(connection, sql, handler, 3);
        query.forEach(System.out::println);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        DButils.close(connection, null);
    }
}

# ScalarHandler 返回表中的单个值(如聚合函数一类)
public void test5(){
    Connection connection = null;
    try {
        QueryRunner queryRunner = new QueryRunner();
        connection = DButils.druidConn();
        String sql="select count(*) from customers";
        ScalarHandler<Object> handler = new ScalarHandler<>();
        Object query = queryRunner.query(connection, sql, handler);
        System.out.println(query);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        DButils.close(connection, null);
    }
}

#自定义 ResultSetHandler
public void test6(){
    Connection connection = null;
    try {
        QueryRunner queryRunner = new QueryRunner();
        connection = DButils.druidConn();
        String sql="select max(birth) from customers";
        //匿名内部类实现自定义查询操作
        ResultSetHandler<customers> handler=new ResultSetHandler<customers>() {
            @Override
            public customers handle(ResultSet resultSet) throws SQLException {
                return new customers(17,"H7","H@163.com",new Date(2021-6-20));
            }
        };
        customers query = queryRunner.query(connection, sql, handler);
        System.out.println(query);
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        DButils.close(connection, null);
    }
}

关闭资源

# DbUtils 下有实现资源关闭的方法
DbUtils.closeQuietly(connection);