JDBC06-PerparedStatement针对不同表的通用查询操作

JDBC06-PerparedStatement针对不同表的通用查询操作

一、针对于不同的表的通用的查询操作，返回表中的一条记录

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 columValue = rs.getObject(i + 1);

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

            // 给t对象指定的columnName属性，赋值为columValue：通过反射
            Field field = clazz.getDeclaredField(columnLabel);
            field.setAccessible(true);
            field.set(t, columValue);
         }
         return t;
      }
   } catch (Exception e) {
      e.printStackTrace();
   } finally {
      JDBCUtils.closeResource(conn, ps, rs);

   }

   return null;
}

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

二、针对于不同的表的通用的查询操作，返回表中的多条记录

@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`";
   List<Order> orderList = getForList(Order.class, sql1);
   orderList.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()) {
         T t = clazz.newInstance();
         // 处理结果集一行数据中的每一个列:给t对象指定的属性赋值
         for (int i = 0; i < columnCount; i++) {
            // 获取列值
            Object columValue = rs.getObject(i + 1);

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

            // 给t对象指定的columnName属性，赋值为columValue：通过反射
            Field field = clazz.getDeclaredField(columnLabel);
            field.setAccessible(true);
            field.set(t, columValue);
         }
         list.add(t);
      }
      
      return list;
   } catch (Exception e) {
      e.printStackTrace();
   } finally {
      JDBCUtils.closeResource(conn, ps, rs);

   }

   return null;
}
 e) {
      e.printStackTrace();
   } finally {
      JDBCUtils.closeResource(conn, ps, rs);

   }

   return null;
}