使用SQL联合查询来构建临时vo对象的应用

联合查询：

表1： team球队表　　　　表2：schedule 赛程表

需要数据：

球队名称、主队ID、主队名称、客队ID、客队名称、胜负情况

方法1. Object数组取出列和数值

import java.util.List;

import junit.framework.TestCase;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.junit.Test;

import com.nubb.db.HibernateUtil;
import com.nubb.db.career.CareerDaoFactory;
import com.nubb.po.User;
import com.nubb.po.career.CareerTeam;
import com.nubb.vo.career.VoCareerTodaySchedule;

/** 
 * 
 * @description
 * 
 * @classname     THHCareerDAO 
 * @author         maoyun<maoyun@nubb.com>
 * @date         2013-9-6 上午09:41:32 
 * @version     1.0
 */
public class THHCareerDAO extends TestCase{
    
    private Session _session = null;
    
    private Session _getSession(){
        _session = HibernateUtil.currentSession();
        return _session;
    }
    
    private void _close(){
        HibernateUtil.closeSession();
    }
   
    @Test
    public void testCombinePojo(){
        this._getSession();
        SQLQuery q = _session.createSQLQuery("SELECT * FROM user WHERE uid = '000000000009'");
//　　　 Object obj = _session.get(User.class, "000000000009");
//　　　 Object obj = q.uniqueResult();
　　　　 Object obj
        this._close();
        
        //POJO实体对象可以将get函数所得的Object对象强转(其他方式查询到的单条记录强转会报错)
        User user = (User) obj;
        System.out.println(new String(user.getTeamName()));
        System.out.println(user.getCity());
        
        
        //SQL查询得出唯一结果为： Object数组 , 字段名被忽略,用数组的下标依次表示！  如： id name ...  在数组中对应的就是 obj[0] obj[1]
        Object[] obj1 = (Object[]) obj;
        byte[] b = ((byte[])obj1[1]);
        //
        String str = new String(b);
        System.out.println(str);
        String sss = new String();
        for (int i = 0; i < b.length; i++) {
            sss+=b[i];
        }
        byte[] c = ((byte[])obj1[2]);
        String stri = new String(c);
        System.out.println(stri);
        String ccc = new String();
        for (int i = 0; i < c.length; i++) {
            ccc+=c[i];
        }
        System.out.println(obj1[0]+"..."+ sss +"..." + ccc +"..." + obj1[3]+"..." + obj1[4]);
    }

}

方法2（推荐）. 创建临时对象(vo),利用

session.createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(VoCareerTodaySchedule.class))方法来将属性值set进临时对象中(VoCareerTodaySchedule对象中加入相应的set和get方法即可)

3. 如在联合查询中需要辅表同一字段(这种情况常见,比如：在赛程表中,现需要获取未来几天的比赛赛程,其中就肯定有两个字段来记录主、客队,二者两字段都是来自于同一表)
首先都会想到用as关键字来区分两字段,在数据库里直接执行是没错的，但是用Hibernate中Session的createSQLQuery方法就会报错Column 'name' not found.
后来试了一下前一个字段不用,后一个字段用了就能查出来，但是后一个字段是null

最后才知道应该另加上addScalar(column_name)方法来区分
sql：

Query q = session.createSQLQuery(sql)
.addScalar("h_name").addScalar("v_name")
.setResultTransformer(Transformers.aliasToBean(VoCareerTodaySchedule.class));

查询：

/** 
     * 当前赛程<br/>
     * 赛程表+球队表<br/>
     * @description
     * @param time 10位数开始时间
     * @title  getCurrentDayList
     */
    public List<VoCareerTodaySchedule> getCurrentDayList(long time) {
        try {
            this._VerifyParam(time);
        } catch (HHParamVerifyException e) {
            return null;
        }
        String sql = "SELECT cps.id , cps.home_cid , ct1.name as h_name , cps.visit_cid , ct2.name as v_name, cps.pk_date , cps.win "
                        + "FROM career_pk_schedule cps , career_team ct1 , career_team ct2 "
                        + "WHERE cps.home_cid = ct1.id AND cps.visit_cid = ct2.id "
                        + "AND cps.`status` = 0 AND cps.pk_date = " + time ;
        Session session = HibernateUtil.currentSession();
        Query q = session.createSQLQuery(sql)
                .addScalar("h_name").addScalar("v_name")
                .setResultTransformer(Transformers.aliasToBean(VoCareerTodaySchedule.class));
        List<VoCareerTodaySchedule> list = q.list();
        HibernateUtil.closeSession();
        return list.isEmpty() ? null : list;
    }

vo对象：

/** 
 * 
 * @description
 * 
 * @classname     CareerTeamPkSchedule 
 * @author         maoyun<maoyun@nubb.com>
 * @date         2013-9-24 下午03:09:40 
 * @version     1.0
 */
public class VoCareerTodaySchedule {
    
    private int id;
    private int home_cid;
    private String h_name;
    private int visit_cid;
    private String v_name;
    private int pk_date;
    private int win;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getHome_cid() {
        return home_cid;
    }
    public void setHome_cid(int home_cid) {
        this.home_cid = home_cid;
    }
    public int getVisit_cid() {
        return visit_cid;
    }
    public void setVisit_cid(int visit_cid) {
        this.visit_cid = visit_cid;
    }
    public int getPk_date() {
        return pk_date;
    }
    public void setPk_date(int pk_date) {
        this.pk_date = pk_date;
    }
    public int getWin() {
        return win;
    }
    public void setWin(int win) {
        this.win = win;
    }
    public String getV_name() {
        return v_name;
    }
    
    public String getH_name() {
        return h_name;
    }
    public void setH_name(String h_name) {
        this.h_name = h_name;
    }
    public void setV_name(String v_name) {
        this.v_name = v_name;
    }
//调试用
    @Override
    public String toString() {
        return "VoCareerTodaySchedule [id=" + id + ", home_cid=" + home_cid
                + ", h_name=" + h_name + ", visit_cid=" + visit_cid
                + ", v_name=" + v_name + ", pk_date=" + pk_date
                + ", win=" + win + "]";
    }

因为其原理是根据Select后的column name 对vo临时对象进行set值，所以可以颠倒vo对象中字段的顺序，也可以添加多余的字段，还可以去除现有字段....

比如：现对VoCareerTodaySchedule对象添加一个属性，但是查询的字段并没有它，结果可执行，但值为null

同理对VoCareerTodaySchedule对象去掉id属性和set/get方法，查询语句中依然不变，结果可执行。没有意义

使用SQL联合查询来构建临时vo对象的应用

推荐阅读