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);
    }

输出结果为:

Person [pid=002, pname=李四, age=22, sex=null, address=Address [aid=222, province=湖北, city=武汉, district=施恩, street=苗族自治区]]