1.针对customers表通用的查询操作
CustomerForQuery
package com.aff.PreparedStatement; import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.junit.Test; import com.aff.bean.Customer;
import com.aff.utils.JDBCUtils; //对于Customers表的查询操作
public class CustomerForQuery {
@Test
public void testqueryForCustomers() {
String sql = "select id,name,email from customers where id =?";
Customer customer = queryForCustomers(sql, 12);
System.out.println(customer); } // 针对customers表通用的查询操作
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 columvalue = rs.getObject(i + 1);// 获取列值
String columName = rsmd.getColumnName(i + 1);// 获取列名 // 给cust对象指定的columName属性，赋值为columvalue,通过反射完成
// 把名为columName的属性拿到，因为表中列名和属性是对应的
Field field = Customer.class.getDeclaredField(columName);// 和列名相同的属性
field.setAccessible(true);// 可能是私有的，这样设置使的能够访问 // 把这个属性名的值设置给当前的cust，赋值为columvalue
field.set(cust, columvalue);
}
return cust;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
} @Test
public void tesQuery() {
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();
}
// 关闭资源
JDBCUtils.closeResource(conn, ps, rs);
}
}
2.针对Order表的通用查询
OrderForQuery
package com.aff.PreparedStatement; import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.junit.Test; import com.aff.bean.Order;
import com.aff.utils.JDBCUtils; //针对Order表的通用查询
public class OrderForQuery {
/*
* 针对表的字段名与类的属性名不相同的情况
* 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 = 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++) {
// 获取每个列的列值
Object columnValue = rs.getObject(i + 1); // 获取列的列名,列数 列名为元数据用来修饰ResultSet(结果集)的，
// String columnName = rsmd.getColumnName(i + 1);-- 不推荐使用
// 改为获取列的别名
String ColumnLabel = rsmd.getColumnLabel(i+1); // 通过反射将对象指定名columnName的属性赋给指定的值columnValue
// 先拿到class
Field field = Order.class.getDeclaredField(ColumnLabel);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
} @Test
public void testQuery1() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select order_id, order_name,order_date from `order` where order_id = ?";
ps = conn.prepareStatement(sql);
// 查询id = 1数据
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 {
JDBCUtils.closeResource(conn, ps, rs);
}
}
}
Customer
package com.aff.bean; import java.sql.Date; //ORM编程思想 Object relational mapping
//一个数据表对应一个java类
//表中的一条记录对应java类的一个对象
//表中的一个字段对应java类的一个属性
//java.sql.Date -------------- Date sql类型
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
super();
// TODO Auto-generated constructor stub
}
public Customer(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
} }
Order
package com.aff.bean;
import java.sql.Date;
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
super();
}
public Order(int orderId, String orderName, Date orderDate) {
super();
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]";
}
}