联合查询：
表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方法，查询语句中依然不变，结果可执行。 没有意义