JDBC入门

#1. JDBC概述

• JDBC(Java Database Cnonnectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库

#2. 获取数据库连接

1. 方式一：

Driver driver = new com.mysql.cj.jdbc.Driver();

//jdbc:mysql://{ip}:{port}/{db}?characterEncoding=utf8&useSSL=false
&serverTimezone=UTC&rewriteBatchedStatements=true
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";

        //将用户名和密码封装在Properties中
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "aA200011");

        Connection conn = driver.connect(url, info);

        System.out.println(conn);

2. 方式二：

• 对方式一的迭代：在程序中不出现第三方的api，使程序具有更好的可移植性

//1. 获取Driver实现类对象
        Class clazz = Class.forName("com.mysql.jdbc.Driver");

        Driver driver = (Driver)clazz.getDeclaredConstructor().newInstance();

        //2.提供要连接的数据库
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";

        //3.提供连接需要的用户名和密码
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "aA200011");

        Connection conn = driver.connect(url, info);

        System.out.println(conn);

3. 方式三：

• 使用DriverMannager替换Driver

Class clazz = Class.forName("com.mysql.jdbc.Driver");

        Driver driver = (Driver)clazz.getDeclaredConstructor().newInstance();

        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
        String user = "root";
        String password = "aA200011";
        //注册驱动
        DriverManager.registerDriver(driver);

        //获取连接
        Connection conn = DriverManager.getConnection(url, user, password);

        System.out.println(conn);

4. 方式四：

• 可以只加载驱动，不用显示的注册驱动了

String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
        String user = "root";
        String password = "aA200011";

        Class.forName("com.mysql.jdbc.Driver");
        //可以省略以下的操作，因为在mysql的Driver实现类的静态代码块中已经帮我们实现了
        //注册驱动
//        Driver driver = (Driver)clazz.getDeclaredConstructor().newInstance();
//        DriverManager.registerDriver(driver);

        //获取连接
        Connection conn = DriverManager.getConnection(url, user, password);

        System.out.println(conn);

5. 方式五(final)：

• 将数据库连接需要的4个基本信息声明在配置文件中，通过读取配置文件的方式，获取连接

//1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
 
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //2.加载驱动    
        Class.forName(driverClass);

        //3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        
        System.out.println(conn);

• 优点：

  1.实现了数据与代码的分离。实现了解耦

  2.如果需要修改配置文件信息，可以避免程序重新打包

#3. 使用PreparedStatement实现CRUD操作

• 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题

• 如何避免出现sql注入：只要用PreparedStatement（从Statement扩展而来）代替Statement

• 除了解决Statement的拼串、sql问题之外，PreparedStatement还可以操作Blob的数据

  还可以实现更高效的批量插入

1. 增

Connection conn = null;
        PreparedStatement ps = null;

        try {

            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

            Properties pros = new Properties();

            pros.load(is);

            String user = pros.getProperty("user");
            String url = pros.getProperty("url");
            String password = pros.getProperty("password");
            String driverClass = pros.getProperty("driverClass");

            Class.forName(driverClass);

            //1. 获取连接
            conn = DriverManager.getConnection(url, user, password);

            //2.预编译sql语句，返回PreparedStatement的实例
            String sql = "insert into customers(name, email, birth) values (?, ?, ?)";//?是占位符
            ps = conn.prepareStatement(sql);

            //3.填充占位符
            ps.setString(1, "娜扎");
            ps.setString(2, "nezha@gmail.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("1000-01-01");
            ps.setDate(3, new java.sql.Date(date.getTime()));

            //4. 执行操作
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.资源的关闭
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }

            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }

            }
        }

2. 改

• 这里将固定的方法封装了

Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库连接
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句，返回PreparedStatement的实例
            String sql = "update customers set name = ? where id = ?";
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            ps.setObject(1, "莫扎特");
            ps.setObject(2, 18);
            //4.执行
            ps.execute();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //5.资源的关闭
            JDBCUtils.closeResource(conn, ps);
        }

3. 查

• 通用的针对Order表的查询操作

• 针对表的字段名与类的属性名不相同的情况：

  1.必须声明sql时，使用类的属性名来命名字段的别名

  使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName()，获取列的别名

@Test
    public void testOrderForQuery(){
        String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
        Order order = orderForQuery(sql, 1);
        System.out.println(order);
    }


    public Order orderForQuery(String sql, Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++){
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            //获取列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                Order order = new Order();
                for(int i = 0; i < columnCount; i++){
                    //获取每个列的列值： 通过ResultSet
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名:通过ResultSetMetaData
                    //获取列的别名：getColumnLabel()
                    String columnName = rsmd.getColumnLabel(i + 1);

                    //通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
                    Field field = Order.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(order, columnValue);
                }

                return order;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }
        return null;
    }

• 通用

@Test
    public void test2(){

        String sql = "select id, name, email from customers where id = ?";
        Customer customer = getInstance(Customer.class, sql, 12);
        System.out.println(customer);


        String sql1 = "select order_id orderId, order_name orderName from `order` where order_id = ?";
        Order order = getInstance(Order.class, sql1, 1);
        System.out.println(order);
    }

    public <T> T getInstance(Class<T> clazz, String sql, Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++){
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();


            if(rs.next()){
                T t = clazz.getDeclaredConstructor().newInstance();
                for(int i = 0; i < columnCount; i++){
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }

                return t;
                
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }
		
        return null;
        
    }

• 多行查询通用操作

@Test
    public void test3(){
        String sql = "select id, name, email from customers where id < ?";
        List<Customer> list = getForList(Customer.class, sql, 12);

        list.forEach(System.out::println);

    }

    public <T> List<T> getForList(Class<T> clazz, String sql, Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();

            ps = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++){
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();

            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<T>();
            while(rs.next()){
                T t = clazz.getDeclaredConstructor().newInstance();
                //处理结果集一行数据中的每一列：给t对象指定的属性赋值
                for(int i = 0; i < columnCount; i++){
                    Object columnValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
        }
    }

#4. 操作BLOB类型字段

1. 插入

Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql = "insert into customers(name, email, birth, photo) values (?, ?, ?, ?)";

            ps = conn.prepareStatement(sql);

            ps.setObject(1, "石雨晨");
            ps.setObject(2, "rgshiyuchen@163.com");
            ps.setObject(3, "2000-11-16");

            FileInputStream is = new FileInputStream(new File("sky123.jpg"));
            ps.setBlob(4, is);

            ps.execute();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }

2. 查询

Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        InputStream is = null;
        FileOutputStream fos = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql = "select id, name, email, birth, photo from customers where id = ?";

            ps = conn.prepareStatement(sql);

            ps.setObject(1, 20);

            rs = ps.executeQuery();

            if(rs.next()){
                int id = (int) rs.getObject(1);
                String name = (String) rs.getObject(2);
                String email = (String) rs.getObject(3);
                Date birth = (Date) rs.getObject(4);

                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);

                //将Blob类型的字段下载下来，以文件的方式保存在本地
                Blob photo = rs.getBlob(5);
                is = photo.getBinaryStream();

                fos = new FileOutputStream("download.jpg");
                byte[] buffer = new byte[1024];
                int len;

                while((len = is.read(buffer)) != -1){
                    fos.write(buffer, 0, len);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, ps, rs);
            if(is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
            if(fos != null){
                try {
                    fos.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }

        }

• 如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数：max_allowed_packet=16M。

#5. 批量插入

• 使用PreparedStatement实现批量插入
• 方式一：

Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql = "insert into goods(name) values(?)";
            ps = conn.prepareStatement(sql);

            for(int i = 1; i <= 20000; i++){
                ps.setObject(1, "name_" + i);

                ps.execute();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }

• 方式二：addBatch(), executeBatch(), clearBatch()

• mysql服务器是默认关闭批处理的，需要通过一个参数让mysql开启批处理的支持

  mysql8.0 以下?rewriteBatchedStatements=true 写在配置文件的url后面

  mysql8.0 以上&&rewriteBatchedStatements=true

Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql = "insert into goods(name) values(?)";
            ps = conn.prepareStatement(sql);

            for(int i = 1; i <= 20000; i++){
                ps.setObject(1, "name_" + i);

                //1.攒sql
                ps.addBatch();

                if(i % 500 == 0){
                    //2.执行
                    ps.executeBatch();

                    //3.清空batch
                    ps.clearBatch();
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }

• 方式三：设置不允许自动提交

Connection conn = null;
        PreparedStatement ps = null;
        try {

            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();

            //设置不允许自动提交数据
            conn.setAutoCommit(false);

            String sql = "insert into goods(name) values(?)";
            ps = conn.prepareStatement(sql);

            for(int i = 1; i <= 1000000; i++){
                ps.setObject(1, "name_" + i);

                //1.攒sql
                ps.addBatch();

                if(i % 500 == 0){
                    //2.执行
                    ps.executeBatch();

                    //3.清空batch
                    ps.clearBatch();
                }
            }

            //提交数据
            conn.commit();

            long end = System.currentTimeMillis();

            System.out.println(end - start);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }

#6. 数据库事务

• 事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态
• 事务处理：保证所有事务都作为一个工作单元来执行，要么所有的事务都被提交(commit)，要么数据库放弃所作的所有的操作，整个事务回滚(rollback)到初始状态
• 数据一旦提交，就不可回滚

//考虑数据库事务后的转账操作
    @Test
    public void test2(){
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            System.out.println(conn.getAutoCommit());

            //取消数据的自动提交功能
            conn.setAutoCommit(false);

            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            update(conn, sql1, "AA");

            //模拟网络异常
//        System.out.println(10 / 0);

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            update(conn, sql2, "BB");


            System.out.println("转账成功");
            
            conn.commit();
        } catch (Exception e) {
            throw new RuntimeException(e);
//            try {
//                conn.rollback();
//            } catch (SQLException ex) {
//                throw new RuntimeException(ex);
//            }
        } finally {
            JDBCUtils.closeResource(conn, null);
        }

    }


    //通用的增删改操作-- version 2.0 （考虑上事务）
    public int update(Connection conn, String sql, Object ...args){//sql中占位符的个数与可变形参的长度一致
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);

            for(int i = 0; i < args.length; i++){
                ps.setObject(i + 1, args[i]);
            }

            return ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(null, ps);
        }

    }

事务的ACID属性

1. 原子性(Atomicity)

   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

2. 一致性(Consistency)

   事务必须使数据库从一个一致性状态变换到另一个一致性状态

3. 隔离性(Isolation)

   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务的内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰

4. 持久性(Durability)

   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何的影响

数据库的并发问题

• 脏读：对两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段。之后，若T2回滚，T1读取的内容就是临时且无效的
• 不可重复读：对于两个事务T1T2，T1读取了一个字段，然后T2更新了该字段。之后，T1再次读取同一个字段，值就不同了
• 幻读：对于两个事务T1T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行

四种隔离级别

• READ UNCOMMITTED(读未提交数据):允许事务读取未被其他事务提交的变更，脏读、不可重复读和幻读的问题都会出现
• READ COMMITTED(读已提交数据):只允许事务读取已经被其他事务提交的变更。可以避免脏读。
• REPEATABLE READ(可重复读):确保事务可以多次从一个字段中读取相同的值。在事务的持续期间，禁止其他事务对这个字段进行更新。幻读的问题仍然存在
• SERIALIZABLE(串行化):确保事务可以从一个表中读取相同的行。在事务的持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能非常低下
• 一般从第二和第三个级别中选择
• Oracle支持第二种和第四种事务隔离级别：默认是第二种
• MySQL支持四种事务隔离级别。默认是第三种

//获取当前连接的隔离级别
System.out.println(conn.getTransactionIsolation());
//设置数据库的隔离级别
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

#7. DAO及相关实现类

• 查看idea中jdbc2工程下的DAO及DAO2包

#8. 数据库连接池

基本思想

• 为数据库连接建立一个缓冲池。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从缓冲池中取出一个，使用完毕之后再放回去。
• 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个

多种开源的数据库连接池技术

• DBCP：tomcat服务器自带的。速度快，但不稳定
• C3P0：速度较慢，但比较稳定
• Druid：阿里提供的数据库连接池，兼顾了以上的优点

C3P0的两种实现方法

• 方式一：

//获取c3p0数据库连接池
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" ); //loads the jdbc driver
        cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
        cpds.setUser("root");
        cpds.setPassword("aA200011");

        //通过设置相关的参数，对数据库连接池进行管理
        //设置初始时数据库连接池中的连接数
        cpds.setInitialPoolSize(10);

        Connection conn = cpds.getConnection();

        System.out.println(conn);
        //销毁c3p0数据库连接池
        DataSources.destroy(cpds);

• 方式二：

<c3p0-config>

    <named-config name="helloc3p0">
        <!-- 提供获取连接的四个基本信息 -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
        <property name="user">root</property>
        <property name="password">aA200011</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>

ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");

Connection conn = cpds.getConnection();

System.out.println(conn);

DBCP数据库连接池的两种实现方法

• 方式一：

//创建了DBCP的数据库连接池
        BasicDataSource source = new BasicDataSource();

        //设置基本信息
        source.setDriverClassName("com.mysql.cl.jdbc.Driver");
        source.setUrl("jdbc:mysql://localhost:3306//test");
        source.setUsername("root");
        source.setPassword("aA200011");

        //设置其他涉及数据库连接池管理的信息
        source.setInitialSize(10);

        Connection conn = source.getConnection();

• 方式二：

Properties pros = new Properties();
        
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");

        pros.load(is);

        DataSource source = BasicDataSourceFactory.createDataSource(pros);

        Connection connection = source.getConnection();

DRUID数据库连接池的两种实现方法

• 方法一：略
• 方法二：使用配置文件

Properties pros = new Properties();

        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");

        pros.load(is);

        DataSource source = DruidDataSourceFactory.createDataSource(pros);

        Connection conn = source.getConnection();

#9. Apache-DBUtils实现CRUD操作

• commons-dbutils 是Apache组织提供的一个开源JDBC工具类库，封装了针对于数据库的增删改查操作

增删改

Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection();

            String sql = "insert into customers(name, email, birth)values(?, ?, ?)";
            int insertCount = runner.update(conn, sql, "蔡徐坤", "caixukun@126.com", "1997-09-08");
            System.out.println(insertCount);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }

查询

• BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录

//返回一条记录
QueryRunner runner = new QueryRunner();

        Connection conn = JDBCUtils.getConnection();

        String sql = "select id, name, email, birth from customers where id = ?";

        BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);

        Customer customer = runner.query(conn, sql, handler, 12);

        System.out.println(customer);

• BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合

//返回多条记录
QueryRunner runner = new QueryRunner();

        Connection conn = JDBCUtils.getConnection();

        String sql = "select id, name, email, birth from customers where id < ?";

        BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);

        List<Customer> list = runner.query(conn, sql, handler, 12);

        list.forEach(System.out::println);

• MapHandler:是ResultSetHandler接口的实现类，对应表中的一条记录

  将字段以及相应字段的值作为map中的key和value

• ScalarHandler:用于查询特殊值