java操作数据库增删改查的小工具2--TxQueryRunner

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

当涉及到多表查询时,如数据库中有两张表分别为t_person和t_address,表结构如下:

其中t_person的外键为t-address的主键aid,

新建两个javaBean类,Person 和 Address;设置属性名与数据库列名一致:

public class Person { private String pid;
private String pname;
private int age;
private String sex;
private Address address;
//把Address作为Person的一个属性引入
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "Person [pid=" + pid + ", pname=" + pname + ", age=" + age
+ ", sex=" + sex + ", address=" + address + "]";
}}

public class Address { private String aid;
private String province;
private String city;
private String district;
private String street;
public String getAid() {
return aid;
}
public void setAid(String aid) {
this.aid = aid;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getDistrict() {
return district;
}
public void setDistrict(String district) {
this.district = district;
}
public String getStreet() {
return street;
}
public void setStreet(String street) {
this.street = street;
}
@Override
public String toString() {
return "Address [aid=" + aid + ", province=" + province + ", city="
+ city + ", district=" + district + ", street=" + street + "]";
}}

测试方法如何下:

/**
* 一行结果集中包含了两张表的列
* 使用MapHandler来处理
* 1. 把结果集封装到map中
* 2. 使用map生成Person对象
* 3. 使用map生成address对象
* 4. 把两个实体对象建立关系
* @throws SQLException
*/
@Test
public void testQuery6() throws SQLException {
String sql = "SELECT * FROM t_person p, t_address a WHERE p.aid=a.aid AND p.pid=?";
QueryRunner qr = new TxQueryRunner();
/*
* 1. 得到Map
*/
Map map = qr.query(sql, new MapHandler(), "aaa");
/*
* 2. 把Map中部分数据封装到Person中
*/
Person p = CommonUtils.toBean(map, Person.class);
/*
* 3. 把Map中部分数据封装到Address中
*/
Address addr = CommonUtils.toBean(map, Address.class);
/*
* 4. 建立两个实体的关系
*/
p.setAddress(addr); System.out.println(p);
}