本地SQL查询来完善HQL不能涵盖所有的查询特性
下面通过例子来理解本地SQL
例子查询用户和租房的信息
配置文件
hibernatecfgxml
<?xml version= encoding=utf?>
<!DOCTYPE hibernateconfiguration PUBLIC
//Hibernate/Hibernate Configuration DTD //EN
hiber/hibernateconfigurationdtd>
<hibernateconfiguration>
<sessionfactory>
<! Database connection settings >
<property name=connectiondriver_class>oraclejdbcdriverOracleDriver</property>
<property name=connectionurl>jdbc:oracle:thin:@OWEYOJDUAAHZZ::ORCL</property>
<property name=connectionusername>jbit</property>
<property name=connectionpassword>bdqn</property>
<! JDBC connection pool (use the builtin) >
<property name=connectionpool_size></property>
<! SQL dialect >
<property name=dialect>orghibernatedialectOracleDialect</property>
<! Enable Hibernates automatic session context management >
<property name=current_session_context_class>thread</property>
<! Disable the secondlevel cache >
<! <property name=cacheprovider_class>orghibernatecacheNoCacheProvider</property> >
<! Echo all executed SQL to stdout >
<property name=show_sql>true</property>
<! Drop and recreate the database schema on startup >
<property name=hbmddlauto>update</property>
<mapping resource=cn/jbit/hibernate/entity/Userhbmxml />
<mapping resource=cn/jbit/hibernate/entity/Househbmxml />
<mapping resource=cn/jbit/hibernate/entity/Streethbmxml />
<mapping resource=cn/jbit/hibernate/entity/Typehbmxml />
<mapping resource=cn/jbit/hibernate/entity/Districthbmxml />
</sessionfactory>
</hibernateconfiguration>
hibernate工具类
HibernateUtiljava
package cnjbithibernateutil;
import orghibernateHibernateException;
import orghibernateSession;
import orghibernateSessionFactory;
import orghibernatecfgConfiguration;
/*
* hibernate工具类
*/
public class HibernateUtil {
private static Configuration configuration;
private static final SessionFactory sessionFactory;
static{
try {
configuration=new Configuration();
nfigure();
sessionFactory=configurationbuildSessionFactory();
}
catch (Throwable ex) {
// Make sure you log the exception as it might be swallowed
Systemerrprintln(Initial SessionFactory creation failed + ex);
throw new ExceptionInInitializerError(ex);
}
}
public static SessionFactory getSessionFactory() {
return sessionFactory;
}
public Session getSession() throws HibernateException{
return getSessionFactory()getCurrentSession();
}
}
实体类
Userjava
package cnjbithibernateentity;
import javautilSet;
public class User implements javaioSerializable {
private static final long serialVersionUID = L;
private Integer id;
private String name;
private String password;
private String telephone;
private String username;
private String isadmin;
private Set<House> house;
//get&set方法
public Integer getId() {
return id;
}
public void setId(Integer id) {
thisid = id;
}
public String getName() {
return name;
}
public void setName(String name) {
thisname = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
thispassword = password;
}
public String getTelephone() {
return telephone;
}
public void setTelephone(String telephone) {
thistelephone = telephone;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
thisusername = username;
}
public String getIsadmin() {
return isadmin;
}
public void setIsadmin(String isadmin) {
thisisadmin = isadmin;
}
public Set<House> getHouse() {
return house;
}
public void setHouse(Set<House> house) {
thishouse = house;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
}
实体类
Housejava
package cnjbithibernateentity;
import javautilDate;
public class House {
private Integer id;
private Integer type_id;
private Integer user_id;
private Integer street_id;
private String description;
private Date date;
private Integer price;
private String contact;
private Integer floorage;
private String title;
public Integer getId() {
return id;
}
public void setId(Integer id) {
thisid = id;
}
public Integer getType_id() {
return type_id;
}
public void setType_id(Integer type_id) {
thistype_id = type_id;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
thisuser_id = user_id;
}
public Integer getStreet_id() {
return street_id;
}
public void setStreet_id(Integer street_id) {
thisstreet_id = street_id;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
thisdescription = description;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
thisdate = date;
}
public Integer getPrice() {
return price;
}
public void setPrice(Integer price) {
thisprice = price;
}
public String getContact() {
return contact;
}
public void setContact(String contact) {
ntact = contact;
}
public Integer getFloorage() {
return floorage;
}
public void setFloorage(Integer floorage) {
thisfloorage = floorage;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
thistitle = title;
}
}
映射文件
Househbmxml
<?xml version=?>
<!DOCTYPE hibernatemapping PUBLIC
//Hibernate/Hibernate Mapping DTD //EN
hiber/hibernatemappingdtd>
<hibernatemapping>
<class name=cnjbithibernateentityHouse table=House>
<id name=id type=javalangInteger>
<column name=id />
<generator class=native>
</generator>
</id>
<property name=type_id type=javalangInteger>
<column name=type_id length= />
</property>
<property name=user_id type=javalangInteger>
<column name=user_id length= />
</property>
<property name=street_id type=javalangInteger>
<column name=street_id length= />
</property>
<property name=description type=javalangString>
<column name=description length= />
</property>
<property name=date type=javautilDate>
<column name=pubdate length= />
</property>
<property name=price type=javalangInteger>
<column name=price length=/>
</property>
<property name=contact type=javalangString>
<column name=contact length=/>
</property>
<property name=floorage type=javalangInteger>
<column name=floorage length=/>
</property>
<property name=title type=javalangString>
<column name=title length=/>
</property>
</class>
</hibernatemapping>
映射文件
Userhbmxml
<?xml version=?>
<!DOCTYPE hibernatemapping PUBLIC
//Hibernate/Hibernate Mapping DTD //EN
hiber/hibernatemappingdtd>
<hibernatemapping>
<class name=cnjbithibernateentityUser table=users>
<id name=id type=javalangInteger>
<column name=id />
<generator class=sequence>
<param name=sequence>SEQ_ID</param>
</generator>
</id>
<property name=name type=javalangString>
<column name=name length= />
</property>
<property name=password type=javalangString>
<column name=password length= />
</property>
<property name=telephone type=javalangString>
<column name=telephone length= />
</property>
<property name=username type=javalangString>
<column name=username length= />
</property>
<property name=isadmin type=javalangString>
<column name=isadmin length=/>
</property>
<set name=House table=house>
<key>
<column name=user_id></column>
</key>
<onetomany class=cnjbithibernateentityHouse/>
</set>
</class>
<!
使用<sqlquery>元素定义本地sql查询语句和<class>并列与命名查询类似使用<sqlquery>元素的子元素<return>指定别名与实体类联系其中alias属性用于
指定别名class属性用于指定实体类在程序中通过Session对象的getNameQuery()方法获取该查询语句
>
<sqlquery name=findUserHouse>
<![CDATA[
select {u*}{h*} from users uhouse h where uid=huser_id
]]>
<return alias=u class=cnjbithibernateentityUser/>
<return alias=h class=cnjbithibernateentityHouse/>
</sqlquery>
</hibernatemapping>
测试类
Testjava
import javautilIterator;
import javautilList;
import orghibernateHibernateException;
import orghibernateQuery;
import orghibernateSession;
import orghibernateSessionFactory;
import orghibernateTransaction;
import cnjbithibernateentityHouse;
import cnjbithibernateentityQueryProperties;
import cnjbithibernateentityUser;
import cnjbithibernateutilHibernateUtil;
public class Test {
public static void main(String[] args) {
HibernateUtil u= new HibernateUtil();
SessionFactory sf = null;
Session session =null;
Transaction tx=null;
try{
session=ugetSession();
tx=sessionbeginTransaction();
//本地SQL查询
Query query=sessiongetNamedQuery(findUserHouse);//获取本地查询语句
List result=querylist();
Iterator it=erator();
while(ithasNext()){
Object[] results=(Object[])itnext();
User user =(User)results[];
House house=(House)results[];
Systemoutprintln(用户名:+usergetName()+ 房屋信息:+housegetTitle()+usergetTelephone());
}
}catch(HibernateException e){
eprintStackTrace();
}
}
}
如果不在Userhbmxml配置文件中添加本地sql查询语句也可以在直接在测试类中添加
执行本地sql查询将不是使用Query接口了而是通过SQLQuery接口使用Session的createSQLQuery(String sql)方法利用传入的sql参数获得SQLQuery实例
在使用这个方法时还需要传入查询实体类因此需要SQLQuery的addEntity(String aliasClass entityClass)方法addEntity()方法是将别名与实体类联系在一起
例如
Testjava
import javautilIterator;
import javautilList;
import orghibernateHibernateException;
import orghibernateQuery;
import orghibernateSession;
import orghibernateSessionFactory;
import orghibernateTransaction;
import cnjbithibernateentityHouse;
import cnjbithibernateentityQueryProperties;
import cnjbithibernateentityUser;
import cnjbithibernateutilHibernateUtil;
public class Test {
public static void main(String[] args) {
HibernateUtil u= new HibernateUtil();
SessionFactory sf = null;
Session session =null;
Transaction tx=null;
try{
session=ugetSession();
tx=sessionbeginTransaction();
//本地SQL查询
//sql语句中的u是sql中表Users表名也是指定实体对象的别名{}表示引用实体类的属性
String sql=select {u*}{h*} from users as uhouse as h where uid=huser_id;
SQLQuery query=sessioncreateSQLQuery(sql)addEntity(uUserclass)addEntity(hHouseclass);
List result=querylist();
Iterator it=erator();
while(ithasNext()){
Object[] results=(Object[])itnext();
User user =(User)results[];
House house=(House)results[];
Systemoutprintln(用户名:+usergetName()+ 房屋信息:+housegetTitle()+usergetTelephone());
}
}catch(HibernateException e){
eprintStackTrace();
}
}
