第二章 jdbc深入编程 ② 代码

KQC 2020/7/22

实现图书的添加和查询。图书表: 编号，书名，作者，价格，出版日期

#图书表: 编号，书名，作者，价格，出版日期
create table book
(
		bid int primary key auto_increment,
		bname varchar(20),
		author varchar(20),
		price decimal(5,2),
		produceDate date
);
//插入部分也可省略
insert into book
(bname,author,price,produceDate)
values
('zuotian','ta',10.00,'1999-09-09'),
('jintian','TA',11.00,'2022-01-01');

select * from book;

插入和查询操作：

package com.yzh70722.keqiance;

import org.junit.Test;

import java.sql.*;

/**
 * @author: XYT
 * @create-date: 2022/7/22 8:48
 */
public class Test1 {
    //四大金刚
    private String driver="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/70713_db5?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private String user="root";
    private String password="123456";
    //课前测：实现图书的添加和查询
    @Test
    public void Test1Insert(){
        System.out.println("插入图书");

        Connection con=null;
        Statement st=null;

        String sql="insert into book"+  //注意：拼接字符串的时候，+换行时记得加空
                "    (bname,author,price,produceDate)"+
                "    values"+
                "    ('mingtian','ni',12.00,'2022-09-09')";
        //将输出打印的结果复制粘贴到对应的数据库连接中，进行验证
        System.out.println(sql);

        try {  //try-catch 同时选中多行异常代码，ctrl+alt+t
            Class.forName(driver);
            con=DriverManager.getConnection(url,user,password);
            st=con.createStatement();

            //更新条数？？？还有插入操作吧？？
            int count=st.executeUpdate(sql);
            System.out.println("zhixingcishu:"+count);

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if(st!=null){
                try {
                    st.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }

    }
    @Test
    public void Test1Query(){
        System.out.println("查询图书");

        Connection con=null;
        Statement st=null;
        ResultSet rs=null;

        String sql="select bid,bname,author,price,produceDate from book";
        System.out.println(sql);


        try {
            Class.forName(driver);
            con=DriverManager.getConnection(url,user,password);
            st=con.createStatement();
            rs=st.executeQuery(sql);

            //判断表格的下一行是否存在
            while (rs.next()){
                //遍历行内的列
                for (int i = 1; i < 6; i++) {
                    System.out.print(rs.getObject(i)+"  ");
                }
                System.out.println();
            }

//            int count=st.executeUpdate(sql);
//            System.out.println("zhixingcishu:"+count);

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

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

    }
}

①.封装BaseDao（待完善）获取连接、关闭数据库对象

package com.yzh7.util;

import java.sql.*;

/**
 * @author: hy
 * @create: 2022-07-22 09:37:54
 */
public class BaseDAO {
    //定义四大金刚
    //驱动类
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    //连接字符串
    private static final String URL = "jdbc:mysql://localhost:3306/0720_db?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    //用户名
    private static final String USER = "root";
    //密码
    private static final String PASSWORD = "123456";

    //获取连接
    public static Connection getConnection(){
        Connection con = null;
        try {
            //加载驱动类
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return con;
    }

    //关闭数据库对象
    public static void closeAll(Connection con , Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //设置参数
    public static void setParams(PreparedStatement pst,Object[] params){
        //设置参数

    }

    //通用增删改

    //通用查询
}

2.调用封装BaseDao实现数据库的插入和查询操作

package com.yzh7.test1;

import com.yzh7.util.BaseDAO;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author: hy
 * @create: 2022-07-22 09:36:30
 */
public class MyTest {
    @Test
    public void test1(){
        //插入操作
        Connection con = null;
        Statement st = null;

        //1.定义插入sql语句
        String bookName="图书2";
        String author = "作者2";
        double price = 30.5;
        String pubDate = "2020-01-02";
        String sql = "insert into book " +
                "     (bookName,author,price,pubDate)" +
                "     values" +
                "     ('"+bookName+"','"+author+"',"+price+",'"+pubDate+"')";
        try {
            //2.获取连接
            con = BaseDAO.getConnection();
            //3.创建命令对象
            st = con.createStatement();
            //4.执行sql
            int count = st.executeUpdate(sql);
            System.out.println("记录数:"+count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.关闭数据库对象
            BaseDAO.closeAll(con,st,null);
        }

    }

    @Test
    public void test2(){
        //图书查询
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        //1.定义查询sql
        String name = "' or 1=1 #";
        String sql = "select bookId,bookName,author,price,pubDate " +
                     " from book " +
                "    where bookName = '"+name+"' ";

        System.out.println(sql);
        try {
            //2.获取连接
            con = BaseDAO.getConnection();
            //3.创建命令执行对象
            st = con.createStatement();
            //4.执行查询sql，并返回结果集
            rs = st.executeQuery(sql);
            //5.使用结果集遍历数据
            while (rs.next()){ //读取一行
                //循环遍历每行的各个列数据
                for (int i=1;i<=5;i++){
                    System.out.print(rs.getObject(i)+" ");
                }
                System.out.println("\n=============");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.关闭数据库对象
            BaseDAO.closeAll(con,st,rs);
        }


    }
}

3.(优化)预编译命令执行对象PreparedStatement和？参数

// A code block
package com.yzh7.test1;

import com.yzh7.util.BaseDAO;
import org.junit.Test;

import java.sql.*;

/**
 * @author: hy
 * @create: 2022-07-22 09:36:30
 */
public class MyTest2 {
    @Test
    public void test1(){
        //插入操作
        Connection con = null;
        //Statement st = null;
        //预编译命令执行对象PreparedStatement
        //1.优化sql提升查询效率
        //2.可以用参数占位的方式设置参数数据，避免sql注入问题
        PreparedStatement pst = null;

        //1.定义插入sql语句
        String bookName="'图书3";
        String author = "作者3";
        double price = 30.6;
        String pubDate = "2020-01-02";


        String sql = "insert into book " +
                "     (bookName,author,price,pubDate)" +
                "     values" +
                "     (?,?,?,?)";
        System.out.println(sql);
        try {
            //2.获取连接
            con = BaseDAO.getConnection();
            //3.创建命令对象
            //st = con.createStatement();
            //创建预编译命令执行对象:需要提供sql做参数
            pst = con.prepareStatement(sql);
            //使用预编译命令对象设置参数:就是用数据替换?,每个?有自己的次序，从1开始
            /*pst.setString(1,bookName);
            pst.setString(2,author);
            pst.setDouble(3,price);
            pst.setDate(4,java.sql.Date.valueOf(pubDate));*/
            Object[] params = {bookName,author,price,pubDate};
            for (int i=0;i<params.length;i++){
                pst.setObject(i+1,params[i]);
            }

            //4.执行sql
            //int count = st.executeUpdate(sql);
            //使用预编译命令执行增删改，此时不需要sql
            int count = pst.executeUpdate();
            System.out.println("记录数:"+count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5.关闭数据库对象
            BaseDAO.closeAll(con,pst,null);
        }

    }

    @Test
    public void test2(){
        //图书查询
        Connection con = null;
        //Statement st = null;
        //预编译命令对象
        PreparedStatement pst = null;
        ResultSet rs = null;

        //1.定义查询sql
        String name = "' or 1=1 #";

        String sql = "select bookId,bookName,author,price,pubDate " +
                     " from book " +
                "    where bookName = ? ";
        System.out.println(sql);
        try {
            //2.获取连接
            con = BaseDAO.getConnection();
            //3.创建命令执行对象
            //st = con.createStatement();
            //获取预编译命令对象
            pst = con.prepareStatement(sql);
            pst.setObject(1,name);

            //调试技巧:直接打印pst对象，就能看到设置参数之后的sql语句
            System.out.println(pst);

            //4.执行查询sql，并返回结果集
            //rs = st.executeQuery(sql);
            //用预编译命令对象执行查询
            rs = pst.executeQuery();
            //5.使用结果集遍历数据
            while (rs.next()){ //读取一行
                //循环遍历每行的各个列数据
                for (int i=1;i<=5;i++){
                    System.out.print(rs.getObject(i)+" ");
                }
                System.out.println("\n=============");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.关闭数据库对象
            BaseDAO.closeAll(con,pst,rs);
        }


    }
}

②.封装BaseDao（待完善）设置参数、通用增删改

package com.yzh7.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author: hy
 * @create: 2022-07-22 09:37:54
 */
public class BaseDAO {
    //定义四大金刚
    //驱动类
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    //连接字符串
    private static final String URL = "jdbc:mysql://localhost:3306/0720_db?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    //用户名
    private static final String USER = "root";
    //密码
    private static final String PASSWORD = "123456";

    //获取连接
    public static Connection getConnection(){
        Connection con = null;
        try {
            //加载驱动类
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return con;
    }

    //关闭数据库对象
    public static void closeAll(Connection con , Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //设置参数
    public static void setParams(PreparedStatement pst,Object[] params){
        //没有参数则不设置
        if(params==null)
            return;

        //设置参数
        for(int i=0;i<params.length;i++){
            try {
                pst.setObject(i+1,params[i]);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    //通用增删改
    public static int executeUpdate(String sql,Object[] params){
        //连接
        Connection con = null;
        //命令对象
        PreparedStatement pst = null;
        //返回结果
        int res = -1;
        try{
            //获取连接
            con = getConnection();
            //获取命令对象
            pst = con.prepareStatement(sql);
            //设置参数
            setParams(pst,params);
            //执行增删改
            res = pst.executeUpdate();
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            closeAll(con,pst,null);
        }
        return res;
    }

    //通用查询
    public static List<Map<String,Object>> exeucteQuery(String sql, Object[] params){

        List<Map<String,Object>> rows = new ArrayList<>();

        //实现查询封装

        return rows;

    }

}

4.插入数据并获取自增列

package com.yzh7.test2;

import com.yzh7.util.BaseDAO;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author: hy
 * @create: 2022-07-22 10:59:20
 */
public class MyTest {
    @Test
    public void test1(){
        //插入数据并获取自增列
        String sql = "insert into book" +
                "     (bookName,author,price,pubDate)" +
                "     values" +
                "     (?,?,?,?)";
        Object[] params = {"西游记","吴承恩",20,"2000-01-01"};

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null; //获取插入的编号

        try{
            con = BaseDAO.getConnection();
            //创建预编译命令对象，并制定附加行为(返回生成的自增值)
            pst = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            //设置参数
            BaseDAO.setParams(pst,params);
            //执行插入
            int count = pst.executeUpdate();
            System.out.println("记录数:"+count);
            //通过命令对象，获取返回自增值结果
            rs =  pst.getGeneratedKeys();
            if(rs.next()){
                int bookId = rs.getInt(1);
                System.out.println("自增值:"+bookId);
            }

        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            BaseDAO.closeAll(con,pst,rs);
        }
    }
}

5.jdbc 事务处理

package com.yzh7.test2;

import com.yzh7.util.BaseDAO;
import org.junit.Test;

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

/**
 * @author: hy
 * @create: 2022-07-22 11:12:32
 */
public class MyTest2 {
    @Test
    public void test1(){
        //jdbc 事务处理
        Connection con = null;
        PreparedStatement pst = null;

        String sql = "update bank " +
                "     set money = money + ? " +
                "     where account = ? ";

        try{
            //获取连接
            con = BaseDAO.getConnection();
            //设置事务处理方式:改为手动提交事务
            con.setAutoCommit(false);

            pst = con.prepareStatement(sql);
            //操作1:张三减去1000
            pst.setObject(1,-1000);
            pst.setObject(2,"张三");
            pst.executeUpdate();

            //System.out.println(1/0);

            //操作2:李四加1000
            pst.setObject(1,1000);
            pst.setObject(2,"李四");
            pst.executeUpdate();

            //没有出错，则提交事务
            System.out.println("没有出错:提交事务");
            con.commit();

        }catch (Exception ex){
            ex.printStackTrace();
            //有错，则回滚事务
            try {
                System.out.println("出错了:回滚事务");
                con.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }finally {
            BaseDAO.closeAll(con,pst,null);
        }
    }
}

7.调用封装BaseDao实现数据库实现数据库的增删改操作

package com.yzh7.test2;

import com.yzh7.util.BaseDAO;
import org.junit.Test;

/**
 * @author: hy
 * @create: 2022-07-22 11:43:42
 */
public class MyTest3 {
    @Test
    public void test1(){
        String sql = "insert into book" +
                "     (bookName,author,price,pubDate)" +
                "     values" +
                "     (?,?,?,?)";
        Object[] params = {"红楼梦","曹雪芹",20,"2002-01-01"};
        //执行增删改操作
        int res = BaseDAO.executeUpdate(sql,params);
        System.out.println("记录数:"+res);
    }

    @Test
    public void test2(){
        String sql = "update book" +
                "     set bookName = ?,author=?,price=?,pubDate = ? " +
                "     where bookId = ? ";
        Object[] params = {"红楼梦2","曹雪芹2",30,"2002-01-02",6};
        //执行增删改操作
        int res = BaseDAO.executeUpdate(sql,params);
        System.out.println("记录数:"+res);
    }

    @Test
    public void test3(){
        String sql = "delete from book where bookId = ? ";
        Object[] params = {6};
        int res = BaseDAO.executeUpdate(sql,params);
        System.out.println("记录数:"+res);
    }
}

8.通用查询（封装）

package com.yzh7.test2;

import com.yzh7.util.BaseDAO;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: hy
 * @create: 2022-07-22 11:43:42
 */
public class MyTest4 {
    @Test
    public void test(){
        //通用查询:
        //1.要用List<Map<String,Object>> 封装查询结果
        //2.要能支持针对不同表，不确定列数，不确定行数的查询结果封装

        //查询图书表
        String sql = "select bookId,bookName,author,price,pubDate from book ";
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        //定义要封装的查询结果集合对象
        List<Map<String,Object>> rows = new ArrayList<>();

        try{
            con = BaseDAO.getConnection();
            pst = con.prepareStatement(sql);
            BaseDAO.setParams(pst,null);
            //执行查询
            rs = pst.executeQuery();

            while (rs.next()){//读取一行数据

                //获取该行的各个列的数据
                Object bookId = rs.getObject(1);
                Object bookName = rs.getObject(2);
                Object author = rs.getObject(3);
                Object price = rs.getObject(4);
                Object pubDate = rs.getObject(5);

                //用Map存储当前行的各个列
                Map<String,Object> map = new HashMap<>();
                map.put("bookId",bookId);
                map.put("bookName",bookName);
                map.put("author",author);
                map.put("price",price);
                map.put("pubDate",pubDate);

                //将当前行的Map存储到rows中
                rows.add(map);
            }

            //遍历rows数据
            for (Map map : rows){
                System.out.println(map);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            BaseDAO.closeAll(con,pst,rs);
        }
   }

    @Test
    public void test2(){
        //通用查询:
        //1.要用List<Map<String,Object>> 封装查询结果
        //2.要能支持针对不同表，不确定列数，不确定行数的查询结果封装

        //查询图书表
        //String sql = "select bookId,bookName,author,price,pubDate from book ";
        String sql = "select * from bank ";
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        //定义要封装的查询结果集合对象
        List<Map<String,Object>> rows = new ArrayList<>();

        try{
            con = BaseDAO.getConnection();
            pst = con.prepareStatement(sql);
            BaseDAO.setParams(pst,null);
            //执行查询
            rs = pst.executeQuery();

            //通过结果集，获取结果集的结构对象(列数,列名,列的类型)
            ResultSetMetaData metaData = rs.getMetaData();
            //获取列数
            int colCount = metaData.getColumnCount();
            //根据列数，循环获取结果集的每个列的信息
            for(int i=1;i<=colCount;i++){
                //获取对应列的列名
                String colName =  metaData.getColumnLabel(i);
                //获取对应列的类型
                String colType = metaData.getColumnTypeName(i);
                System.out.println("第"+i+"列:"+colName+" "+colType);
            }


        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            BaseDAO.closeAll(con,pst,rs);
        }
    }

    @Test
    public void test3(){
        //通用查询:
        //1.要用List<Map<String,Object>> 封装查询结果
        //2.要能支持针对不同表，不确定列数，不确定行数的查询结果封装

        //查询图书表
        //String sql = "select bookId,bookName,author,price,pubDate from book ";
        //String sql = "select bookId,bookName,author from book ";
        String sql = "select * from bank ";
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        //定义要封装的查询结果集合对象
        List<Map<String,Object>> rows = new ArrayList<>();

        try{
            con = BaseDAO.getConnection();
            pst = con.prepareStatement(sql);
            BaseDAO.setParams(pst,null);
            //执行查询
            rs = pst.executeQuery();

            //获取结果集结构对象
            ResultSetMetaData metaData = rs.getMetaData();
            int colCount = metaData.getColumnCount();

            while (rs.next()){//读取一行数据
                Map<String,Object> map = new HashMap<>();
                //循环当前行的各个列，取出列名，和列数据，存储到map中
                for(int i=1;i<=colCount;i++){
                    //获取列名
                    String colName = metaData.getColumnLabel(i);
                    //获取列值
                    Object colVal = rs.getObject(i);

                    map.put(colName,colVal);
                }
                //将当前行的Map存储rows中
                rows.add(map);
            }

            //遍历rows数据
            for (Map map : rows){
                System.out.println(map);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            BaseDAO.closeAll(con,pst,rs);
        }
    }
}

课前测

学生表:学号，姓名，性别，生日。
1.自己手动完成BaseDAO所有内容
2.使用BaseDAO实现增删改查

③.封装BaseDao（已完善）通用查询

1.创建表：

#学生表:学号，姓名，性别，生日。
create table student
(
		sid int primary key auto_increment,
		sname varchar(20),
		ssex varchar(20),
		sbirthday date
);
insert into student
(sname,ssex,sbirthday)
values
('zhangsan','男','1999-09-09'),
('lisi','女','2022-01-01');

select * from student;

2.封装BaseDao

package com.yzh70723.keqiance;

import com.yzh70722.test1yubianyi.BaseDAO;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: XYT
 * @create-date: 2022/7/23 9:01
 */
public class BAO {
    private static String driver="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://localhost:3306/70723_db1?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static String user="root";
    private static String password="123456";

    public static Connection getConnection(){
        Connection con=null;

        try {
            Class.forName(driver);
            con= DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return con;
    }

    public static void closeAll(Connection con, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void setParams(PreparedStatement pst,Object[] params){
        if(params==null){
            return;
        }

        //设置参数
        for (int i = 0; i < params.length; i++) {
            try {
                pst.setObject(i+1,params[i]);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static int executeUpdate(String sql, Object[] params){
        Connection con=null;
        PreparedStatement pst=null;

        int res=-1;

        try {
            con=getConnection();
            pst=con.prepareStatement(sql);
            setParams(pst,params);
            res=pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con,pst,null);
        }
        return res;
    }

    public static List<Map<String, Object>> exeucteQuery(String sql,Object[] params){
        
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        List<Map<String, Object>> rows = new ArrayList<>();

        try {
            con = getConnection();
            pst = con.prepareStatement(sql);
            setParams(pst,params);
            rs = pst.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int colCount = metaData.getColumnCount();

            while (rs.next()){
                Map<String,Object> map = new HashMap<>();
                for(int i=1;i<=colCount;i++){
                    String colName = metaData.getColumnLabel(i);
                    Object colVal = rs.getObject(i);

                    map.put(colName,colVal);
                }
                rows.add(map);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
           closeAll(con,pst,rs);
        }
        return rows;

    }

}

3.使用BaseDAO实现增删改查

package com.yzh70723.keqiance;

import com.yzh70722.test1yubianyi.BaseDAO;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author: XYT
 * @create-date: 2022/7/23 9:10
 */
public class Test1 {
    @Test
    public void TestChaRu(){
        System.out.println("数据库插入操作");

        String sql="insert into student"+
                " (sname,ssex,sbirthday)"+
                " values"+
                " (?,?,?)";
        Object[] params={"wangwu6","女","2006-06-06"};

        int res= BAO.executeUpdate(sql,params);
        System.out.println("jilushu:"+res);

    }
    @Test
    public void TestXiuGai(){
        System.out.println("数据库修改操作");

        String sql="update student"+
                "    set sname=?,ssex=?,sbirthday=?"+
                "    where sid=?";
        Object[] params={"zhaoliu","女","2006-01-01",6};

        int res=BAO.executeUpdate(sql,params);
        System.out.println("jilushu:"+res);
    }
    @Test
    public void TestShanChu(){
        System.out.println("数据库删除操作");

        String sql="delete from student where sid=?";
        Object[] params={6};

        int res=BAO.executeUpdate(sql,params);
        System.out.println("jilushu:"+res);
    }

    @Test
    public void TestQuery(){
        System.out.println("数据库查找操作");
        //String sql = "select sname,sbirthday from student ";
        String sql = "select * from student ";

        //List<Map<String, Object>> rows = new ArrayList<>();

        for (Map map : BAO.exeucteQuery(sql,null)){
            System.out.println(map);
        }

    }
}