[Java知识库]java(十二) —— JDBC(二) 增删改通用函数、针对于表的通用查询方法、blob类型的添加与查询、事务相关内容

1.增删改通用函数

 /**
     * 通用的增删改操作
     *
     * @param sql
     * @param args
     */
    public void update(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1.获取连接
            connection = DBUtil.getConnection();

            //2.预编译sql语句
            preparedStatement = connection.prepareStatement(sql);

            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            //4.执行sql语句
            preparedStatement.execute();

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            DBUtil.closeResource(connection, preparedStatement);
        }

    }

2.针对于某个表的查找通用方法

import org.junit.Test;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.sql.Connection;

/**
 * @author ym
 * @create 2022-01-30 23:00
 * @description 测试查询
 */
public class SelectTest {

    @Test
    public void testQueryForOrder() {
        //使用了别名的方法来查找，此处注意，需要使对应的order bean类的属性名与别名一致
        String querySql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id  = ?";
        Order select = select(querySql, 1);
        System.out.println(select.toString());
    }

    /**
     * Order表的通用查询方法
     *
     * @param sql
     * @param args
     * @return
     */
    public Order select(String sql, Object... args) {
        //1.获取连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();

            //2.预编译sql语句
            preparedStatement = connection.prepareStatement(sql);

            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            //4.执行查询，获得结果集以及结果集的元数据
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            //5.根据列名获取结果
            if (resultSet.next()) {

                Order order = new Order();
                for (int i = 0; i < columnCount; i++) {
                    //1.获取列名，此时使用别名
                    String columnLabel = metaData.getColumnLabel(i + 1);

                    //2.获取数据
                    Object object = resultSet.getObject(i + 1);

                    //3.根据反射获取属性
                    Field declaredField = Order.class.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    //4.属性赋值
                    declaredField.set(order, object);
                }

                return order;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return null;
    }

}

3.针对各种表的查找通用方法

import org.junit.Test;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ym
 * @create 2022-01-30 23:00
 * @description 测试查询
 */
public class SelectTest {

    @Test
    public void testQueryForOrder() {
        //使用了别名的方法来查找，此处注意，需要使对应的order bean类的属性名与别名一致
        String querySql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id  < ?";
//        Order select = select(querySql, 1);
//        System.out.println(select.toString());

//        Order order = selectAllTable(Order.class, querySql, 4);
//        System.out.println(order.toString());

        List<Order> orders = selectAllTableList(Order.class, querySql, 4);
        orders.forEach(System.out::println);
    }

    /**
     * Order表的通用查询方法
     *
     * @param sql
     * @param args
     * @return
     */
    public Order select(String sql, Object... args) {
        //1.获取连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();

            //2.预编译sql语句
            preparedStatement = connection.prepareStatement(sql);

            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            //4.执行查询，获得结果集以及结果集的元数据
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            //5.根据列名获取结果
            if (resultSet.next()) {

                Order order = new Order();
                for (int i = 0; i < columnCount; i++) {
                    //1.获取列名，此时使用别名
                    String columnLabel = metaData.getColumnLabel(i + 1);

                    //2.获取数据
                    Object object = resultSet.getObject(i + 1);

                    //3.根据反射获取属性
                    Field declaredField = Order.class.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    //4.属性赋值
                    declaredField.set(order, object);
                }

                return order;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return null;
    }


    public <T> List<T> selectAllTableList(Class<T> t, String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1.建立连接
            connection = DBUtil.getConnection();

            //2.预编译sql语句
            preparedStatement = connection.prepareStatement(sql);

            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            //4.执行查询语句，返回结果集
            resultSet = preparedStatement.executeQuery();
            //查询结果元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //查询列数
            int columnCount = metaData.getColumnCount();


            List<T> ts = new ArrayList<T>();
            while (resultSet.next()) {
                //5.新建对象
                T t1 = t.newInstance();
                //使用循环给每一个对象赋予每个列的属性值
                for (int i = 0; i < columnCount; i++) {
                    //6.获取每列数据
                    Object object = resultSet.getObject(i + 1);

                    //7.获取列名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //8.赋值
                    Field declaredField = t.getDeclaredField(columnLabel);
                    //使用反射时，这一步千万不要忘记
                    declaredField.setAccessible(true);
                    declaredField.set(t1, object);
                }
                ts.add(t1);
            }
            return ts;

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return null;

    }


    /**
     * 查询所有表的通用查询方法
     *
     * @param t
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    public <T> T selectAllTable(Class<T> t, String sql, Object... args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1.建立连接
            connection = DBUtil.getConnection();

            //2.预编译sql语句
            preparedStatement = connection.prepareStatement(sql);

            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            //4.执行查询语句，返回结果集
            resultSet = preparedStatement.executeQuery();
            //查询结果元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //查询列数
            int columnCount = metaData.getColumnCount();

            if (resultSet.next()) {
                //5.新建对象
                T t1 = t.newInstance();
                //使用循环给每一个对象赋予每个列的属性值
                for (int i = 0; i < columnCount; i++) {
                    //6.获取每列数据
                    Object object = resultSet.getObject(i + 1);

                    //7.获取列名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //8.赋值
                    Field declaredField = t.getDeclaredField(columnLabel);
                    //使用反射时，这一步千万不要忘记
                    declaredField.setAccessible(true);
                    declaredField.set(t1, object);
                }
                return t1;
            }


        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return null;

    }

}

4.blob类型的添加与查询

package com.ym.study;

import org.junit.Test;

import java.io.*;
import java.sql.*;

/**
 * @author ym
 * @create 2022-01-31 15:07
 * @description 测试Blob图片的增删改查
 */
public class BlobTest {

    @Test
    public void testBlob() {
//        String insertSql = "insert into customers(photo,name,email,birth) values(?,?,?,?)";
//        String filePath = "x.jpg";
//        insertBlob(insertSql,filePath,"关羽","guanyu@163.com","1998-08-15");
    }

    /**
     * 测试插入图片类型数据的通用类
     */
    @Test
    public void insertBlob() throws SQLException, IOException, ClassNotFoundException {

        //1.获取连接
        Connection connection = DBUtil.getConnection();

        //2.预编译sql
        String insertSql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(insertSql);

        //3.图片输入流
        File blobFile = new File("x.jpg");
        FileInputStream fileInputStream = new FileInputStream(blobFile);

        //4.填充sql
        preparedStatement.setObject(1, "李四");
        preparedStatement.setObject(2, "lisi@163.com");
        preparedStatement.setObject(3, "1992-09-08");
        preparedStatement.setBlob(4, fileInputStream);


        //5.执行
        preparedStatement.execute();

        DBUtil.closeResource(connection, preparedStatement);

    }

    /**
     * blob类型的查询
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    @Test
    public void queryBlob() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = DBUtil.getConnection();
        String querySql = "select photo from customers where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(querySql);
        preparedStatement.setObject(1, 22);
        ResultSet resultSet = preparedStatement.executeQuery();
        FileOutputStream fileOutputStream = null;
        if (resultSet.next()) {
            Blob blob = resultSet.getBlob(1);
            InputStream binaryStream = blob.getBinaryStream();
            fileOutputStream = new FileOutputStream("z.jpg");
            byte[] bytes = new byte[1024];
            int len = 0;
            while ((len = binaryStream.read(bytes)) != -1) {
                fileOutputStream.write(bytes, 0, len);
            }
        }

        fileOutputStream.close();
//        binaryStream.close();
        DBUtil.closeResource(connection, preparedStatement, resultSet);
    }
}

5.事务例子

package com.ym.study;

import org.junit.Test;

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

/**
 * @author ym
 * @create 2022-01-31 21:33
 * @description 考虑到事务的增删改查
 * 对事务有影响的两个需要注意的：
 * 1.增删改查操作是默认自动提交的
 * 2.关闭连接时也是默认自动提交的
 */
public class Transaction {

    @Test
    public void testTransaction() {
        //测试事务的提交
        Connection connection = null;
        try {
            connection = DBUtil.getConnection();

            //从AA账户取出100元，并将这一百元给BB
            String AASql = "update user_table set balance = balance-100 where user = ?";
            System.out.println(connection.getAutoCommit() + "1");
            //关闭自动提交
            connection.setAutoCommit(false);
            System.out.println(connection.getAutoCommit() + "2");
            updateOfTransaction(connection, AASql, "AA");


            //模拟交易出现异常
            System.out.println(10 / 0);

            //将从AA账户取出的100元放入BB账户
            System.out.println(connection.getAutoCommit() + "3");
            String BBSql = "update user_table set balance = balance+100 where user = ?";
            updateOfTransaction(connection, BBSql, "BB");
            System.out.println(connection.getAutoCommit() + "4");


            System.out.println("交易成功");

        } catch (Exception e) {
            //如果跳转到此处，应该是出现异常，此时需要进行回滚
            try {
                System.out.println("进行回滚");
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            //关闭连接之前，先将自动提交改回true，此处是为了数据库连接池做准备
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            //关闭连接
            DBUtil.closeResource(connection, null);
        }

    }

    /**
     * 通用的增删改操作
     *
     * @param sql
     * @param args
     */
    public void updateOfTransaction(Connection conn, String sql, Object... args) {
        PreparedStatement preparedStatement = null;
        try {
            //此处必须要注释，这样才能使得事务内所有sql操作的连接是同一个
//            //1.获取连接
//            conn = DBUtil.getConnection();

            //2.预编译sql语句
            preparedStatement = conn.prepareStatement(sql);

            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            //4.执行sql语句
            preparedStatement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源  此处关闭connection资源需要在需要的时候关闭
            DBUtil.closeResource(null, preparedStatement);
        }

    }
}

6.事务的ACID属性

1.原子性（Atimicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2.一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3.隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。
4.持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

7.数据库隔离性中常出现的问题

Read_uncommited 这个隔离级别解决不了脏读
1.脏读
脏读是指T1事务在读取数据时，读取到了T2事务修改了的但是未提交的数据。
Read_commited 这个隔离级别解决了脏读
2.不可重复读
不可重复读是指T1事务在读取数据时，读取到了T2事务修改了的并且提交了的数据
Repeated_Read 这个隔离级别可以解决脏读和不可重复读问题
3.幻读
幻读是指T1事务在读取数据时，读取到了T2事务添加的几条数据
Serializable 这个隔离基本可以解决脏读、不可重复读、幻读问题，但是并行化就很差了
oracle支持 Read_commited 和 Serializable
一般项目应用中，脏读是不可接受的，其他两个是可以接受的