P17 PreparedStatement针对表的查询操作

针对于Customers表的查询操作

@Test
public void testQuery1()  {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet resultSet = null;
    try {
        conn = JDBCUtil.getConnection();

        String sql="select id,name,email,birth from customers where id=?";
        ps = conn.prepareStatement(sql);

        ps.setObject(1, 1);
        //执行,并返回结果集
        resultSet = ps.executeQuery();
        //处理结果集
        if (resultSet.next()){//next判断结果集的下一条是否有数据，有数据返回true并指针下移，如果返回false，指针不下移
            //获取当前这条数据的各个字段值
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);
            Date birth = resultSet.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 {
        //关闭资源
        JDBCUtil.closeResource(conn, ps,resultSet);
    }

}

针对于Order表的查询操作

@Test
public void testQuery1()  {

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = JDBCUtil.getConnection();
        String sql="select order_id,order_name,order_date from `order` where order_id=?";
        ps = conn.prepareStatement(sql);
        ps.setObject(1, 1);

        rs = ps.executeQuery();

        if (rs.next()){
            int id = (int) rs.getObject(1);
            String name = (String) rs.getObject(2);
            Date date = (Date) rs.getObject(3);

            Order order = new Order(id, name, date);
            System.out.println(order);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, ps,rs);
    }

}

针对于Order表的通用的查询操作

/*
针对于表的字段名与类的属性名不一致的情况：
1.必须声明sql时，使用类的属性名来命名字段的别名
2.使用resultsetmetadata时，需要使用getColumnLabel()来替换getColumnName()，获取别名
    说明：sql中如果没有给字段起别名，getColumnLabel()仍然可以获取到列名
 */
@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);
}

/*
通用的针对于order表的查询操作
 */
public Order orderForQuery(String sql,Object ...args) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = JDBCUtil.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);

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

                //通过反射将对象指定名columnName的属性赋值给指定的值columnValue
                Field field =Order.class.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(order, columnValue);
            }
            return order;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtil.closeResource(conn, ps,rs);
    }

    return null;
}