JDBC编写步骤

最新推荐文章于 2022-07-19 15:08:28 发布

奥斯卡_8023

最新推荐文章于 2022-07-19 15:08:28 发布

阅读量431

点赞数

文章标签：数据库 mysql java jdbc

本文链接：https://blog.csdn.net/qq_41986239/article/details/109327424

版权

本文详细介绍了JDBC在Java项目中的应用步骤，包括数据库连接的获取与关闭，使用PreparedStatement进行查询和操作BLOB数据，批量插入，事务管理以及接口封装规范化的增删查改操作。还对比了不同事务处理的案例。

摘要由CSDN通过智能技术生成

JDBC编写步骤

在项目中创建Bean目录，里面放入Java类，类中的属性要与数据表中对应。
（1）导入相应的jar包
（2）加载、注册sql驱动
（3）获取Connection连接对象

编写jdbc.properties文件

user=root
password=root
url=jdbc:mysql://localhost:3307/test
driverClass=com.mysql.jdbc.Driver

里面的url的格式是jdbc:数据库://localhost:端口号/数据库名

	//将数据库连接需要的4个基本信息声明在配置文件中，通过读取配置文件的方式获取连接
    /*
       此种方式的好处：
       1.实现了数据与代码的分离，实现了解耦
       2.如果需要修改配置文件信息，可以避免程序重新打包
     */
    @Test
    public void testConnection5() throws Exception{
        //1.读取配置文件中的4个基本信息
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("com/atguigu/statement/crud/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);
    }

另一种方式：德鲁伊数据库连接池
创建druid.properties配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3307/test
username=root
password=root

initialSize=10
maxActive=10

 @Test
    public void getConnection() throws Exception{
       Properties pros = new Properties();
       InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
       pros.load(is);
       DataSource source = DruidDataSourceFactory.createDataSource(pros);
       Connection conn = source.getConnection();
       System.out.println(conn);
    }

在工具类JDBCUtils里面，编写德鲁伊数据库连接池获取连接和关闭连接的代码

//使用Druid数据库连接池技术
    private static DataSource source1;
    static{
        try {
            Properties pros = new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("com/atguigu/connection2/druid.properties");
            pros.load(is);
            source1 = DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws Exception{

        Connection conn = source1.getConnection();
        return conn;
    }

//两种关闭连接的方法
public static void closeResource(Connection conn, Statement ps){
        try {
            if(ps!=null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeResource1(Connection conn, Statement ps, ResultSet rs){

        try {
            DbUtils.close(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            DbUtils.close(ps);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            DbUtils.close(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

（4）创建Statement对象并执行SQL语句

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

	public static void main(String[] args) {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入用户名：");
		String user = scanner.next();
		System.out.println("请输入密码：");
		String password = scanner.next();
		String sql = "select user,password from user_table where user = '"+user+"'and password = '"+password+"'";
		User returnUser = get(sql,User.class);
		if(returnUser != null){
			System.out.println("登录成功");
		}else{
			System.out.println("用户名不存在或密码错误");
		}

	}

	// 使用Statement实现对数据表的查询操作
	public static <T> T get(String sql, Class<T> clazz) {
		T t = null;

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 1.加载配置文件
			InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("com/atguigu/statement/crud/jdbc.properties");
			Properties pros = new Properties();
			pros.load(is);

			// 2.读取配置信息
			String user = pros.getProperty("user");
			String password = pros.getProperty("password");
			String url = pros.getProperty("url");
			String driverClass = pros.getProperty("driverClass");

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

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

			st = conn.createStatement();

			rs = st.executeQuery(sql);

			// 获取结果集的元数据
			ResultSetMetaData rsmd = rs.getMetaData();

			// 获取结果集的列数
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {

				t = clazz.newInstance();

				for (int i = 0; i < columnCount; i++) {
					// //1. 获取列的名称
					// String columnName = rsmd.getColumnName(i+1);

					// 1. 获取列的别名
					String columnName = rsmd.getColumnLabel(i + 1);

					// 2. 根据列名获取对应数据表中的数据
					Object columnVal = rs.getObject(columnName);

					// 3. 将数据表中得到的数据，封装进对象
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnVal);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

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

		return null;
	}

（5）使用ResultSet对象获取查询结果集
（6）依次关闭ResultSet、Statement、Connection对象
因为经常需要关闭ResultSet、Statement、Connection对象，所以后期我就直接写成了一个工具类，写成方法，最后关闭对象的时候直接调用即可。

获取数据库连接、关闭连接

/**
     * 获取数据库的连接
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception{
        //1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("com/atguigu/statement/crud/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);
        return conn;
    }

    /**
     * 关闭连接和Statement的操作
     * @param conn
     * @param ps
     */
    public static void closeResource(Connection conn, Statement ps){
        try {
            if(ps!=null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeResource(Connection conn, Statement ps,ResultSet rs){
        try {
            if(ps!=null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

使用PreparedStatement替换掉上面的Statement对象

重新实现增删查操作

//使用PreparedStatement来替换Statement，实现对数据库的增删查改操作
public class PreparedStatementTest {
    //通用的增删改操作
    public void update(String sql ,Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //sql中占位符的个数与可变形参的长度一致
            //1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            //2.预编译SQL语句返回PreparedStatement实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i = 0; i<args.length;i++){
                ps.setObject(i+1,args[i]);//小心参数声明错误
            }
            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.资源的关闭
            JDBCUtils.closeResource(conn,ps);
        }

    }

    //向customers表添加一条记录
    public static void main(String[] args)  {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //向customs表中添加一条记录
            //1.读取配置文件中的4个基本信息
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("com/atguigu/statement/crud/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.获取连接
            conn = DriverManager.getConnection(url, user, password);
            System.out.println(conn);
            //4.预编译sql语句，返回PreparedStatement的实例
            String sql = "insert into customers(name,email,birth) values(?,?,?)";//?:占位符
            ps = conn.prepareStatement(sql);
            //5.填充占位符
            ps.setString(1,"奥斯卡");
            ps.setString(2,"2836440891@qq.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = sdf.parse("1999-12-21");
            ps.setDate(3, new Date(date.getTime()));

            //6.执行操作
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            //7.资源管理
            try {
                if(ps!=null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn!=null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    //修改customers表的一条记录
    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement ps = null;//预编译Sql语句
        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) {
            e.printStackTrace();
        }finally {
            //5.资源的关闭
            JDBCUtils.closeResource(conn,ps);
        }

    }
    //测试增删查
    @Test
    public void testCommonUpdate(){
//        String sql = "delete from customers where id =?";
//        update(sql,3);
        String sql = "update `order` set order_name = ? where order_id = ?";
        update(sql,"DD","2");
    }
}

使用PreparedStatement实现查询操作（查询多行、查询单行）

//使用PreparedStatement实现针对于不同表的通用查询操作
public class PreparedStatementQueryTest {

    @Test
    public void testGetForList(){
        String sql = "select order_id orderId,order_name orderName from `order` where order_id < ?";
        List<Order> list = getForList(Order.class, sql, 4);
        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
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<T>();
            while(rs.next()){
                //处理结果集一行数据中的每一个列
//                Customer cust = new Customer();
                T t = clazz.newInstance();
                //拿到多少个列
                for(int i=0;i<columnCount;i++){
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // System.out.println(columnName);

                    //给cust对象指定的某个属性，赋值为columnName
                    Field field = clazz.getDeclaredField(columnLabel);//todo
                    field.setAccessible(true);
                    field.set(t,columnValue);
                    //System.out.println(cust);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }

        return null;
    }

    @Test
    public void testGetInstance() throws  Exception{
        String sql = "select id,name,email,birth 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);
    }

    /**
     * @Description 针对于不同的表的通用的查询操作，返回表中的一条记录
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return
     */
    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
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                //处理结果集一行数据中的每一个列
//                Customer cust = new Customer();
                T t = clazz.newInstance();
                //拿到多少个列
                for(int i=0;i<columnCount;i++){
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // System.out.println(columnName);

                    //给cust对象指定的某个属性，赋值为columnName
                    Field field = clazz.getDeclaredField(columnLabel);//todo
                    field.setAccessible(true);
                    field.set(t,columnValue);
                    //System.out.println(cust);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }

        return null;

    }
}

使用PreparedStatement操作BLOB类型的数据

public class BlobTest {

    //向数据表Customer中插入BLOB类型的字段
    @Test
    public void testInsert() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,"奥斯卡");
        ps.setObject(2,"2836440891@qq.com");
        ps.setObject(3,"1999-12-21");
        FileInputStream fis = new FileInputStream(new File("E:\\Java_JDBC\\src\\com\\atguigu\\blob\\1.jpg"));
        ps.setBlob(4,fis);
        ps.execute();
        JDBCUtils.closeResource(conn,ps);
    }

    //查询数据表中的BLOB字段
    @Test
    public void testQuery() {
        Connection conn = null;
        PreparedStatement ps = null;
        InputStream is = null;
        FileOutputStream fos = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth,photo from customers where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,19);

            ps.executeQuery();
            rs = ps.executeQuery();
            if(rs.next()){
                //方式1：
    //            int id = rs.getInt(1);
    //            String name = rs.getString(2);
    //            String email = rs.getString(3);
    //            Date birth = rs.getDate(4);
                //方式2：
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birth = rs.getDate("birth");
                Customer cust = new Customer(id,name,email,birth);
                System.out.println(cust);
                //将BLOB类型的字段以文件的方式保存在本地
                Blob photo = rs.getBlob("photo");
                is = photo.getBinaryStream();
                fos = new FileOutputStream(new File("美女.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 {
            try {
                if(is!=null){
                    is.close();
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if(fos!=null){
                    fos.close();
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResource(conn,ps,rs);
        }


    }
}

批量插入（最高效的插入方式）

 //批量插入方式四：设置连接不允许提交数据
    @Test
    public void testInsert3() {
        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= 0;i<=20000;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) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps);
        }
    }

考虑到事务的增删查操作

public abstract class  BaseDAO {
    //通用的增删改操作（version2.0）
    public int update(Connection conn, String sql , Object ...args){

        PreparedStatement ps = null;
        try {
            //sql中占位符的个数与可变形参的长度一致

            //1.预编译SQL语句返回PreparedStatement实例
            ps = conn.prepareStatement(sql);
            //2.填充占位符
            for(int i = 0; i<args.length;i++){
                ps.setObject(i+1,args[i]);//小心参数声明错误
            }
            //3.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.资源的关闭
            JDBCUtils.closeResource(null,ps);
        }
        return 0;
    }


    //通用的查询操作，用于返回数据表中的一条记录（考虑到事务）
    public <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object...args) {

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据：ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                //处理结果集一行数据中的每一个列
//                Customer cust = new Customer();
                T t = clazz.newInstance();
                //拿到多少个列
                for(int i=0;i<columnCount;i++){
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // System.out.println(columnName);

                    //给cust对象指定的某个属性，赋值为columnName
                    Field field = clazz.getDeclaredField(columnLabel);//todo
                    field.setAccessible(true);
                    field.set(t,columnValue);
                    //System.out.println(cust);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,ps,rs);
        }

        return null;

    }
    //通用的查询操作，用于返回数据表中多条记录构成的集合（考虑到事务）
    public <T> List<T> getForList(Connection conn,Class<T> clazz, String sql, Object...args){

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据：ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<T>();
            while(rs.next()){
                //处理结果集一行数据中的每一个列
//                Customer cust = new Customer();
                T t = clazz.newInstance();
                //拿到多少个列
                for(int i=0;i<columnCount;i++){
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // System.out.println(columnName);

                    //给cust对象指定的某个属性，赋值为columnName
                    Field field = clazz.getDeclaredField(columnLabel);//todo
                    field.setAccessible(true);
                    field.set(t,columnValue);
                    //System.out.println(cust);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,ps,rs);
        }

        return null;
    }
    //用于查询特殊值的通用方法
    public <E> E  getValue(Connection conn ,String sql ,Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i = 0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }

            rs = ps.executeQuery();
            if(rs.next()){
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,ps,rs);
        }

        return null;
    }
}

编写接口，规范化增删查改操作

/**
 * 此接口用于规范针对于Customers表的常用操作
 * @author lijiaao
 * @version 1.0
 * @date 2020/10/26 11:01
 */
public interface CustomerDAO {
    //将cust对象添加到数据库中
    void insert(Connection conn, Customer cust);
    //针对指定的id,删除表中一条记录
    void deleteByID(Connection conn,int id);
    //针对内存中的cust对象，去修改数据表中指定的记录
    void update(Connection conn,Customer cust);
    //针对指定的id查询得到对应的Customer对象
    Customer getCustomerById(Connection conn,int id);
    //查询表中的所有记录构成的集合
    List<Customer> getAll(Connection conn);
    //返回数据表中数据的条目数
    Long getCount(Connection conn);
    //返回数据表中最大的生日
    Date getMaxBirth(Connection conn);
}

重写接口，自定义SQL语句

/**
 * @author lijiaao
 * @version 1.0
 * @date 2020/10/26 11:23
 */
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
    @Override
    public void insert(Connection conn, Customer cust) {
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
    }

    @Override
    public void deleteByID(Connection conn, int id) {
        String sql = "delete from customers where id = ?";
        update(conn,sql ,id);
    }

    @Override
    public void update(Connection conn, Customer cust) {
        String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
        update(conn,sql ,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
    }

    @Override
    public Customer getCustomerById(Connection conn, int id) {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = getInstance(conn, Customer.class, sql, id);

        return customer;
    }

    @Override
    public List<Customer> getAll(Connection conn) {
        String sql = "select id,name,email,birth from customers";
        List<Customer> list = getForList(conn,Customer.class,sql);
        return list;
    }

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

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

common-utils 封装了增删查改


/**
 * common-dbutils 是Apache组织提供的一个开源JDBC工具类库，封装了针对于数据库的增删查改操作
 *
 * @author lijiaao
 * @version 1.0
 * @date 2020/10/27 11:28
 */
public class QueryRunnerTest {
    //测试插入
    @Test
    public void testInsert() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "insert into customers(name,email,birth) values(?,?,?)";

            int insertCount = runner.update(conn, sql, "蔡徐坤", "cxk@qq.com", "1999-12-21");
            System.out.println("添加了" + insertCount + "条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }

    }

    /**
     * 测试查询
     * BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录
     */
    @Test
    public void testQuery1() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            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, 20);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }


    }

    /**
     * BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合
     *
     * @throws Exception
     */
    @Test
    public void testQuery2() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            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, 20);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /**
     * MapHandler:是ResultSetHandler接口的实现类，对应表中的一个记录
     * 将字段及相应字段的值作为map中的key和value
     */
    @Test
    public void testQuery3() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id = ?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = runner.query(conn, sql, handler, 20);
            System.out.println(map);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /**
     * MapListHandler:是ResultSetHandler接口实现类，对应表中的一条记录
     * 将字段及相应字段的值作为map中的key和value
     * 将这些map添加到list中
     */
    @Test
    public void testQuery4() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id < ?";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> list = runner.query(conn, sql, handler, 20);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /**
     * ScalarHandler用于查询特殊值
     */
    @Test
    public void testQuery5() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select max(birth) from customers";
            ScalarHandler handler = new ScalarHandler();
            Date maxBirth = (Date) runner.query(conn, sql, handler);
            System.out.println(maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

考虑到事务的案例对比


/**
 * @author lijiaao
 * @version 1.0
 * @date 2020/10/23 17:48
 *  哪些操作会导致数据的自动提交？
 *      DDL操作一旦执行，都会自动提交
 *          set autocommit = false 对DDL操作失效
 *      DML默认情况下，一旦执行，就会自动提交
 *          我们可以通过set autocommit = false的方式取消DML操作的自动提交
 *      默认在关闭连接时，会自动的提交数据
 */
public class TransactionTest {
    /*针对于数据表User_table来说：
     * AA用户给BB用户转账100
     * update user_table set balance = balance - 100 where user = 'AA'
     * update user_table set balance = balance + 100 where user = 'BB'
     */


    //未考虑数据库事务情况下的转账操作
    @Test
    public void testUpdate(){
        String sql1 = "update user_table set balance = balance - 100 where user = ?";
        update(sql1,"AA");



        String sql2 = "update user_table set balance = balance + 100 where user = ?";
        update(sql2,"BB");
        System.out.println("转账成功");
    }
    public int update(String sql ,Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //sql中占位符的个数与可变形参的长度一致
            //1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            //2.预编译SQL语句返回PreparedStatement实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i = 0; i<args.length;i++){
                ps.setObject(i+1,args[i]);//小心参数声明错误
            }
            //4.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.资源的关闭
            JDBCUtils.closeResource(conn,ps);
        }
        return 0;
    }

    //考虑数据库事务以后的转账操作
    @Test
    public void testUpdateWithTx() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            //1.取消数据的自动提交
            conn.setAutoCommit(false);
            String sql1 = "update user set balance = balance - 100 where name = ?";
            update(conn,sql1,"admin1");
            //模拟网络异常
            //System.out.println(10/0);
            String sql2 = "update user set balance = balance + 100 where name = ?";
            update(conn,sql2,"admin2");
            System.out.println("转账成功");
            //2.提交数据
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //3.回滚数据
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            JDBCUtils.closeResource(conn,null);
        }

    }

    //通用的增删改操作（version2.0）
    public int update(Connection conn,String sql ,Object ...args){

        PreparedStatement ps = null;
        try {
            //sql中占位符的个数与可变形参的长度一致

            //1.预编译SQL语句返回PreparedStatement实例
            ps = conn.prepareStatement(sql);
            //2.填充占位符
            for(int i = 0; i<args.length;i++){
                ps.setObject(i+1,args[i]);//小心参数声明错误
            }
            //3.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4.资源的关闭
            JDBCUtils.closeResource(null,ps);
        }
        return 0;
    }


    /**
     * @Description 针对于不同的表的通用的查询操作，返回表中的一条记录
     */
    @Test
    public void testTransactionSelect() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        //打印隔离级别
        System.out.println(conn.getTransactionIsolation());
        //设置数据库的隔离级别
       conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//读已提交
        //取消自动提交数据
        conn.setAutoCommit(false);
        String sql = "select user,password,balance from user_table where user = ?";

        User user = getInstance(conn, User.class, sql, "CC");
        System.out.println(user);
    }
    @Test
    public void testTransactionUpdate() throws Exception{
        Connection conn = JDBCUtils.getConnection();
        //取消自动提交数据
        conn.setAutoCommit(false);
        String sql = "update user_table set balance = ? where user = ?";
        update(conn,sql ,5000,"CC");
        Thread.sleep(15000);
        System.out.println("修改结束");
    }
    //通用的查询操作，用于返回数据表中的一条记录（考虑到事务）
    public <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object...args) {

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据：ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if(rs.next()){
                //处理结果集一行数据中的每一个列
//                Customer cust = new Customer();
                T t = clazz.newInstance();
                //拿到多少个列
                for(int i=0;i<columnCount;i++){
                    Object columnValue = rs.getObject(i + 1);

                    //获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // System.out.println(columnName);

                    //给cust对象指定的某个属性，赋值为columnName
                    Field field = clazz.getDeclaredField(columnLabel);//todo
                    field.setAccessible(true);
                    field.set(t,columnValue);
                    //System.out.println(cust);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,ps,rs);
        }

        return null;

    }
}