java实现mysql表的通用查询操作

原理：
要实现通用的方法，先分析问题：
mysq中的select 语句是有返回值，所以与 增删改不同的是有了返回结果集合，
所以在连接好数据库和对sql语言的预编译处理后，要用resultSet = xxxx.executeQuery()来获取对象的每个属性值，但是对某个类来说：需要用构造器赋值，我们通过resultSet得到的值，我们是不确定具体的值，所以用动态反射的方法赋值。

注意：只看Query的实现过程

public class CustomerForQuery {

    public ArrayList<Customer> Query(String sql, Object...args) {

        PreparedStatement ps=null;
        ResultSet resultSet=null;
        Customer customer=null;

        //获取数据库链接
        Connection connection = JDBCUtils.getConnection();

        //存储customer集合
        ArrayList<Customer> customers = null;

        try {

            //sql 预编译 Pre实例化
            ps = connection.prepareStatement(sql);

            //填充占位符
            for(int i=0;i<args.length;++i){
                ps.setObject(i+1,args[i]);
            }
            customers = new ArrayList<>();

            //获取Pre的查询结果集
            resultSet = ps.executeQuery();

            //获取结果集的元数据，找到列个数
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            //遍历查询结果集
            while(resultSet.next()){
                customer = new Customer();
                for(int i=0;i<count;++i){
                    //获取当前列对应的值
                    Object ColumnValue = resultSet.getObject(i + 1);
                    //获取当前列的名字
                    String columnName = metaData.getColumnName(i + 1);
                   //给当前对象的指定的列赋上对应的值，通过反射
                    Field field = customer.getClass().getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(customer,ColumnValue);

                }
                customers.add(customer);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                if(ps!=null)
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(resultSet!=null)
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return customers;
        }

    }
    @Test
    public void testQuery2() throws Exception {
        String sql ="select email,name from customers";
        ArrayList<Customer> query = Query(sql);
        for(Customer c: query){
            System.out.println(c);
        }
    }
    //没有反射方法，提前知道赋值顺序和属性
    @Test
    public void testQuery1() throws Exception {
        Connection con = JDBCUtils.getConnection();
        String sql ="select id,name,email,birth from customers";
        PreparedStatement ps = con.prepareStatement(sql);

        ResultSet resultSet = ps.executeQuery();
        ArrayList<Customer> customers =new ArrayList<>();
        while(resultSet.next()){
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);
            Date date =resultSet.getDate(4);

            customers.add(new Customer(id,name,email,date));
        }
        for(Customer c : customers){
            System.out.println(c);
        }
    }
    @Test
    public void testQuery() throws SQLException {

        Connection con = JDBCUtils.getConnection();
        String sql ="select id,name,email,birth from customers where id =?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setObject(1,1);
        
        ResultSet resultSet = ps.executeQuery();
        if(resultSet.next()){
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);

            Date birth = resultSet.getDate(4);
            java.util.Date date = new java.util.Date(birth.getTime());
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
            String s = sd.format(date);

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

        }


        JDBCUtils.CloseConnect_Statement(con,ps);
    }
}

//获取数据库连接和增删改通用方法

public class JDBCUtils {

    public static void modify(){
        //1.获取数据库连接
        Connection connection = JDBCUtils.getConnection();

        //second.预编译sql 返回PreparedStatement实例
        String  sql="update Customers set name = ? where id = ? ";
        PreparedStatement pr = null;

        try {
            //third填充占位符
            pr = connection.prepareStatement(sql);
            pr.setString(1,"莫扎特");
            pr.setInt(2,18);
            //执行
            pr.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            //resource close
            System.out.println("修改成功");

            JDBCUtils.CloseConnect_Statement(connection,pr);
        }

    }

    public static void CloseConnect_Statement(Connection connection,PreparedStatement ps){
        try {
            if(connection!=null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void addCustomerMessage(){

        Connection connection = getConnection();
        String sql="insert into Customers(name,email,birth) values(?,?,?)";
        PreparedStatement ps =null;
        try {
            ps = connection.prepareStatement(sql);
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
            //add
            ps.setString(1,"特朗普");
            ps.setString(2,"961208477@qq.com");
            Date parse = sd.parse("1994-10-1");
            ps.setDate(3,new java.sql.Date(parse.getTime()));
            //ok
            ps.execute();
        } catch (Exception e) {

        } finally {
            CloseConnect_Statement(connection,ps);

        }
    }

    public static Connection getConnection(){
        Connection connection=null;
        //获取配置信息和获取驱动连接
        try {
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties pro = new Properties();
            pro.load(is);

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

            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            return connection;
        }
    }

}