JDBC 针对Customers表的通用查询操作

PreparedStatement实现数据库的查询操作

/**
 * @program: HelloWorld
 * @description: 针对Customer表的查询操作
 * @author: fish
 * @create: 2019-12-08 11:38
 **/
public class CustomerForQuery {
    @Test
    public void testQuery1(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,1);
            //执行,并返回结果集
            rs = ps.executeQuery();
            //处理结果集
            if (rs.next()){//判断结果集的下一条是否有数据，如果有数据返回true,并指针下移,如果返回false,指针不会下移

                //获取当前这条数据的各个字段值
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String email = rs.getString(3);
                Date birth = rs.getDate(4);
                //方式一:
                System.out.println("id = "+ id + ",name ="+name+",email ="+email+",birth ="+birth);
                //方式二:
                Object[] data = new Object[]{id,name,email,birth};
                //方式三:将数据封装为一个对象(推荐)
                Customer customer = new Customer(id,name,email,birth);
                System.out.println(customer);
            }
        }catch (Exception e)
        {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtils.closeResource(conn,ps,rs);
        }
    }
}
//输出
id = 1,name =汪峰,email =wf@126.com,birth =2010-02-02
Customer{id=1, name='汪峰', email='wf@126.com', birth=2010-02-02}

针对Customers表的通用查询操作

public class CustomerForQuery {
    @Test
    public  void testQueryForCustomers(){
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = queryForCustomers(sql,11);
        System.out.println(customer);

        sql = "select name,email from customers where id = ?";
        Customer customer1 = queryForCustomers(sql,9);
        System.out.println(customer1);;
    }
        /**
        * @Description: 针对Customer表的通用查询操作
        * @Param: 
        * @return: 
        * @Author: fish
        * @Date: 7:04 PM 2019/12/8
        */ 
    public Customer queryForCustomers(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();
                //处理结果集一行数据中的每一个列
                for (int i = 0;i< columnCount;i++){
                    //获取列值
                    Object columnValue = rs.getObject(i+1);
                    //获取每个列的列名
                    String columnName = rsmd.getColumnName(i+1);
                    //给cust对象指定columnName属性，赋值columnValue，通过反射
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(cust,columnValue);
                }
                return cust;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }
}
//输出
Customer{id=11, name='张学友', email='zhangxy@126.com', birth=1998-12-21 00:00:00.0}
Customer{id=0, name='周杰伦', email='zhoujl@sina.com', birth=null}

针对order表的通用查询操作

public class OrderForQuery {
    @Test
    public void testOrderForQuery(){
        String sql = "select order_id,order_name,order_date from `order` where order_id = ?";
        Order order = orderForQuery(sql,1);
        System.out.println(order);
    }
    /**
    * @Description: 通用的针对于order表的查询操作
    * @Param: 
    * @return: 
    * @Author: fish
    * @Date: 12:14 AM 2019/12/10
    */ 
     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
                     String columnName = rsmd.getColumnName(i+1);
                     //通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
                     Field field = Order.class.getDeclaredField(columnName);
                     field.setAccessible(true);
                     field.set(order,columnValue);
                 }
                 return order;
             }
         }catch (Exception e){
             e.printStackTrace();
         }finally {
             JDBCUtils.closeResource(conn,ps,rs);
         }
         return null;
     }
}
//输出报错
java.lang.NoSuchFieldException: order_id
    at java.lang.Class.getDeclaredField(Class.java:2070)
    at com.cloud.preparedstatement.crud.OrderForQuery.orderForQuery(OrderForQuery.java:57)
    at com.cloud.preparedstatement.crud.OrderForQuery.testOrderForQuery(OrderForQuery.java:23)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
null

报错是因为order类里面没有order_id这个属性。需要给sql语句中查询的元素取别名。

public class OrderForQuery {
    @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);
    }
    /**
    * @Description: 通用的针对于order表的查询操作
    * @Param: 
    * @return: 
    * @Author: fish
    * @Date: 12:14 AM 2019/12/10
    */ 
     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
                     String columnName = rsmd.getColumnName(i+1);
                     //通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
                     Field field = Order.class.getDeclaredField(columnName);
                     field.setAccessible(true);
                     field.set(order,columnValue);
                 }
                 return order;
             }
         }catch (Exception e){
             e.printStackTrace();
         }finally {
             JDBCUtils.closeResource(conn,ps,rs);
         }
         return null;
     }
}
//输出错误
java.lang.NoSuchFieldException: order_id
    at java.lang.Class.getDeclaredField(Class.java:2070)
    at com.cloud.preparedstatement.crud.OrderForQuery.orderForQuery(OrderForQuery.java:57)
    at com.cloud.preparedstatement.crud.OrderForQuery.testOrderForQuery(OrderForQuery.java:23)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
nul

WX20191210-004728@2x.png

还是报相同的错，此时不应再取表中的每个列的列名了。应该取表中的每个列的别名。

public class OrderForQuery {
    @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);
    }
    /**
    * @Description: 通用的针对于order表的查询操作
    * @Param: 
    * @return: 
    * @Author: fish
    * @Date: 12:14 AM 2019/12/10
    */ 
     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
                     //获取列的列名:getColumnName()  --不推荐使用
                     //获取列的别名:getColumnLabel()
//                     String columnName = rsmd.getColumnName(i+1);
                     String columnLabelName = rsmd.getColumnLabel(i+1);
                     //通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
                     Field field = Order.class.getDeclaredField(columnLabelName);
                     field.setAccessible(true);
                     field.set(order,columnValue);
                 }
                 return order;
             }
         }catch (Exception e){
             e.printStackTrace();
         }finally {
             JDBCUtils.closeResource(conn,ps,rs);
         }
         return null;
     }
}
//输出
Order{orderId=1, orderName='AA', orderDate=2010-03-04 00:00:00.0}

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

• 1.必须声明sql时，使用类的属性名来命名字段的别名
• 2.使用ResultSetMetaData时，需要使用getColumnLabel()来替换getColumnName(),获取列的别名。说明:如果sql中没有给字段取别名，getColumnLabel()获取的就是列名。

使用PreparedStatement实现针对于不同表的通用的查询操作,返回一条记录

public class PreparedStatementQueryTest {
    @Test
    public void testGetInstance(){
       String sql = "select id,name,email from customers where id = ?";
       Customer customer = getInstance(Customer.class,sql,9);
       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: 
    * @return: 
    * @Author: fish
    * @Date: 1:40 AM 2019/12/12
    */
    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()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0;i< columnCount;i++){
                    //获取列值
                    Object columnValue = rs.getObject(i+1);
                    //获取每个列的列名
//                    String columnName = rsmd.getColumnName(i+1);
                    String columnLabelName = rsmd.getColumnLabel(i+1);
                    //给t对象指定columnName属性，赋值columnValue，通过反射
                    Field field = clazz.getDeclaredField(columnLabelName);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }
}
//输出
Customer{id=9, name='周杰伦', email='zhoujl@sina.com', birth=null}
Order{orderId=1, orderName='AA', orderDate=null}

使用PreparedStatement实现针对于不同表的通用的查询操作,返回多条记录

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

        String sql1 = "select order_id orderId,order_name orderName from `order` where order_id < ?";
        List<Order> orderList = getForList(Order.class,sql1,5);
        orderList.forEach(System.out::println);
    }
    /**
     * @Description: 针对于不同的表的通用的查询操作，返回表中的多条记录
     * @Param:
     * @return:
     * @Author: fish
     * @Date: 1:40 AM 2019/12/12
     */
    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> arrayList = new ArrayList<T>();
            while(rs.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0;i< columnCount;i++){
                    //获取列值
                    Object columnValue = rs.getObject(i+1);
                    //获取每个列的列名
//                    String columnName = rsmd.getColumnName(i+1);
                    String columnLabelName = rsmd.getColumnLabel(i+1);
                    //给t对象指定columnName属性，赋值columnValue，通过反射
                    Field field = clazz.getDeclaredField(columnLabelName);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                arrayList.add(t);
            }
            return arrayList;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }
//输出
Customer{id=1, name='汪峰', email='wf@126.com', birth=null}
Customer{id=2, name='王菲', email='wf@126.com', birth=null}
Customer{id=4, name='汤唯', email='tw@126.com', birth=null}
Customer{id=5, name='成龙', email='Jackey@gmail.com', birth=null}
Customer{id=6, name='迪丽热巴', email='reba@163.com', birth=null}
Customer{id=7, name='刘亦菲', email='liuyifei@qq.com', birth=null}
Customer{id=8, name='陈道明', email='bdf@126.com', birth=null}
Customer{id=9, name='周杰伦', email='zhoujl@sina.com', birth=null}
Customer{id=10, name='黎明', email='LiM@126.com', birth=null}
Customer{id=11, name='张学友', email='zhangxy@126.com', birth=null}
Order{orderId=1, orderName='AA', orderDate=null}
Order{orderId=2, orderName='JJ', orderDate=null}
Order{orderId=4, orderName='GG', orderDate=null}

可变形参支持可以不写，sql语句中可以不要占位符。

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

        String sql1 = "select order_id orderId,order_name orderName from `order` where order_id < ?";
        List<Order> orderList = getForList(Order.class,sql1,5);
        orderList.forEach(System.out::println);

        String sql2 = "select order_id orderId,order_name orderName from `order`";
        List<Order> orderList1 = getForList(Order.class,sql2);
        orderList.forEach(System.out::println);
    }
    /**
     * @Description: 针对于不同的表的通用的查询操作，返回表中的多条记录
     * @Param:
     * @return:
     * @Author: fish
     * @Date: 1:40 AM 2019/12/12
     */
    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> arrayList = new ArrayList<T>();
            while(rs.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0;i< columnCount;i++){
                    //获取列值
                    Object columnValue = rs.getObject(i+1);
                    //获取每个列的列名
//                    String columnName = rsmd.getColumnName(i+1);
                    String columnLabelName = rsmd.getColumnLabel(i+1);
                    //给t对象指定columnName属性，赋值columnValue，通过反射
                    Field field = clazz.getDeclaredField(columnLabelName);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                arrayList.add(t);
            }
            return arrayList;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }
//输出
Customer{id=1, name='汪峰', email='wf@126.com', birth=null}
Customer{id=2, name='王菲', email='wf@126.com', birth=null}
Customer{id=4, name='汤唯', email='tw@126.com', birth=null}
Customer{id=5, name='成龙', email='Jackey@gmail.com', birth=null}
Customer{id=6, name='迪丽热巴', email='reba@163.com', birth=null}
Customer{id=7, name='刘亦菲', email='liuyifei@qq.com', birth=null}
Customer{id=8, name='陈道明', email='bdf@126.com', birth=null}
Customer{id=9, name='周杰伦', email='zhoujl@sina.com', birth=null}
Customer{id=10, name='黎明', email='LiM@126.com', birth=null}
Customer{id=11, name='张学友', email='zhangxy@126.com', birth=null}
Order{orderId=1, orderName='AA', orderDate=null}
Order{orderId=2, orderName='JJ', orderDate=null}
Order{orderId=4, orderName='GG', orderDate=null}
Order{orderId=1, orderName='AA', orderDate=null}
Order{orderId=2, orderName='JJ', orderDate=null}
Order{orderId=4, orderName='GG', orderDate=null}