针对于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;
}