出家如初,成佛有余

Hibernate Criteria使用实例

Posted in Uncategorized by chuanliang on 2008/04/26

在使用jmesa作为组件来实现分页、导入、排序、过滤组件时候,对paging、sort、filter的处理,使用的是Hibernate的Criteria函数,对于单表使用Criteria方法相对容易,但对于多表操作,手册上没有现成的样例可以借鉴。总结一下Criteria的一些用法,以方便在对多表数据复杂操作时候也能够使用jmesa,简化分页、导出等日常操作。

1、目前使用Criteria用于取总数及排序过滤的用法例子

public int getPersonCountWithFilter(final HibernateFilter filter) {

Integer count = (Integer) getHibernateTemplate().execute(new HibernateCallback() {

public Object doInHibernate(Session session)

throws HibernateException, SQLException {

Criteria criteria = session.createCriteria(Person.class);

criteria.add(Expression.eq(“id”,27));

criteria = filter.execute(criteria);

criteria.setProjection(Projections.rowCount()).uniqueResult();

return criteria.uniqueResult();

}

});

return count.intValue();

}

public List<Person> getPersonWithFilterAndSort(final HibernateFilter filter, final HibernateSort sort, final int rowStart, final int rowEnd) {

List applications = (List) getHibernateTemplate().execute(new HibernateCallback() {

public Object doInHibernate(Session session)

throws HibernateException, SQLException {

Criteria criteria = session.createCriteria(Person.class);

criteria = filter.execute(criteria);

criteria = sort.execute(criteria);

criteria.setFirstResult(rowStart);

criteria.setMaxResults(rowEnd – rowStart);

return criteria.list();

}

});

return applications;

}

2、数据库表结构,以sys_user和sys_user_role为例子

CREATE TABLE `sys_user_role` (

`user_id` int(11) NOT NULL,

`role_id` int(11) NOT NULL,

`user_name` varchar(100) default NULL,

`role_name` varchar(100) default NULL,

PRIMARY KEY (`role_id`,`user_id`)

) ;

INSERT INTO `sys_user_role` VALUES (‘1’, ‘1’, ‘liang1’, ‘admin1’);

INSERT INTO `sys_user_role` VALUES (‘2’, ‘2’, ‘liang2’, ‘admin2’);

INSERT INTO `sys_user_role` VALUES (‘3’, ‘3’, ‘liang3’, ‘admin3’);

INSERT INTO `sys_user_role` VALUES (‘4’, ‘4’, ‘liang4’, ‘admin4’);

INSERT INTO `sys_user_role` VALUES (‘5’, ‘5’, ‘liang5’, ‘anonymous’);

INSERT INTO `sys_user_role` VALUES (‘1’, ‘5’, ‘liang1’, ‘admin5’);

CREATE TABLE `sys_user` (

`user_id` int(11) NOT NULL,

`mobile` varchar(15) default NULL,

`imei` varchar(20) default NULL,

`user_name` varchar(100) NOT NULL,

`password` varchar(50) default NULL,

`user_type` varchar(40) default ‘normal’ ,

`login_type` varchar(20) default NULL,

`customer_id` int(11) default NULL,

`customer_name` varchar(200) default NULL,

`root_company_id` int(11) default NULL,

`root_company_name` varchar(255) default NULL,

`compayn_id` int(11) default NULL,

`company_name` varchar(255) default NULL,

`email` varchar(100) default NULL,

`email2` varchar(100) default NULL,

`nickname` varchar(100) default NULL,

`sex` varchar(10) default NULL ,

`status` varchar(50) default NULL,

`credit_amount` decimal(10,2) default NULL,

`credit_rank` varchar(20) default NULL,

`money` decimal(10,2) default NULL,

`integral` decimal(10,2) default ‘0.00’,

`website` varchar(200) default NULL,

`pwd_modify_date` datetime default NULL,

`pwd_duration` varchar(10) default NULL,

`signature` text,

`twitter` varchar(255) default NULL,

`qq` varchar(20) default NULL,

`msn` varchar(50) default NULL,

`icq` varchar(50) default NULL,

`yahoo` varchar(30) default NULL,

`gtalk` varchar(30) default NULL,

`blog` varchar(255) default NULL,

`interest` text,

`safe_question` varchar(100) default NULL,

`safe_answer` varchar(100) default NULL,

`safe_question2` varchar(100) default NULL,

`safe_answer2` varchar(100) default NULL,

`safe_question3` varchar(100) default NULL,

`safe_answer3` varchar(100) default NULL,

`icon` varchar(100) default NULL,

`icon2` varchar(100) default NULL,

`icon3` varchar(100) default NULL,

`is_test` tinyint(1) default NULL ,

`is_admin` tinyint(1) default NULL,

`fax` varchar(20) default NULL,

`home_phone` varchar(50) default NULL,

`office_phone` varchar(20) default NULL,

`birthday` char(19) default NULL,

`vocation` varchar(20) default NULL,

`education` varchar(50) default NULL,

`address` varchar(255) default NULL,

`postcode` varchar(20) default NULL,

`description` text,

`creator` varchar(40) default NULL,

`begin_date` datetime default NULL,

`end_date` datetime default NULL,

`create_date` datetime default NULL,

`modify_user` varchar(40) default NULL,

`modify_date` datetime default NULL,

`last_login_type` varchar(20) default NULL,

`last_login_id` varchar(20) default NULL,

`last_login_date` datetime default NULL,

PRIMARY KEY (`user_id`)

) ;

INSERT INTO `sys_user` VALUES (‘1’, ‘13911111111’, null, ‘liang1’, ‘liang1’, ‘normal’, null, null, ‘liang1’, null, null, null, null, null, null, null, null, null, null, null, null, ‘0.00’, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);

INSERT INTO `sys_user` VALUES (‘2’, ‘13922222222’, null, ‘liang2’, ‘liang2’, ‘normal’, null, null, ‘liang2’, null, null, null, null, null, null, null, null, null, null, null, null, ‘0.00’, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);

INSERT INTO `sys_user` VALUES (‘3’, ‘13933333333’, null, ‘liang3’, ‘liang3’, ‘normal’, null, null, ‘liang3’, null, null, null, null, null, null, null, null, null, null, null, null, ‘0.00’, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);

INSERT INTO `sys_user` VALUES (‘4’, ‘13944444444’, null, ‘liang4’, ‘liang4’, ‘normal’, null, null, ‘liang4’, null, null, null, ‘li’, null, null, null, null, null, null, null, null, ‘0.00’, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);

INSERT INTO `sys_user` VALUES (‘5’, ‘13955555555’, null, ‘liang5’, ‘liang5’, ‘normal’, null, null, ‘liang5’, null, null, null, null, null, null, null, null, null, null, null, null, ‘0.00’, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);

3、hbm映射文件

注意:

由于在目前的数据模型及程序中并没有使用数据库的外键约束,直接通过程序来控制外键约束关系。因此在所有的hbm中并没有使用hibernate 的one-to-many关联。要使用Criteria实现多表较为复杂的操作,需要加上one-to-many映射。但这与目前的程序实现存在冲突,解决方法如下:

由于目前使用Criteria只用于查询及统计分析部分,可以单独建立一个映射文件及映射类,用于查询及统计分析操作,例如对于SysUser表,可以建立一个SysUser-jmesa.hbm.xml,对此映射文件,将<class name=”com.mobilesoft.esales.model.SysUser” table=”sys_user” catalog=”mysql”>
改为:

<class name=”com.mobilesoft.esales.model.SysUserJmesa” table=”sys_user” >

one-to-many:

<set name=”userRoles” table=”sys_user_role” >

<key column=”role_id” />

<one-to-many class=”com.mobilesoft.esales.model.SysUserRole” />

</set>

由于只是演示,简单起见,直接用的是原有的映射文件及映射类。

3.1、SysUser.hbm.xml

<?xml version=”1.0″ encoding=”utf-8″?>

<!DOCTYPE hibernate-mapping PUBLIC “-//Hibernate/Hibernate Mapping DTD 3.0//EN”

http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd”&gt;

<hibernate-mapping>

<class name=”com.mobilesoft.esales.model.SysUser” table=”sys_user” catalog=”mysql”>

<id name=”userId” type=”java.lang.Integer”>

<column name=”user_id” />

<generator class=”native” />

</id>

<property name=”mobile” type=”java.lang.String”>

<column name=”mobile” length=”15″ />

</property>

<!—

省略掉其他内容

–>

<set name=”userRoles” table=”sys_user_role” >

<key column=”user_id” />

<one-to-many class=”com.mobilesoft.esales.model.SysUserRole” />

</set>

</class>

</hibernate-mapping>

3.2、SysUserRole.hbm.xml

<?xml version=”1.0″ encoding=”utf-8″?>

<!DOCTYPE hibernate-mapping PUBLIC “-//Hibernate/Hibernate Mapping DTD 3.0//EN”

http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd”&gt;

<!–

Mapping file autogenerated by MyEclipse Persistence Tools

–>

<hibernate-mapping>

<class name=”com.mobilesoft.esales.model.SysUserRole” table=”sys_user_role” catalog=”mysql”>

<composite-id name=”id” class=”com.mobilesoft.esales.model.SysUserRoleId”>

<key-property name=”roleId” type=”java.lang.Integer”>

<column name=”role_id” />

</key-property>

<key-property name=”userId” type=”java.lang.Integer”>

<column name=”user_id” />

</key-property>

</composite-id>

<property name=”userName” type=”java.lang.String”>

<column name=”user_name” length=”100″ />

</property>

<property name=”roleName” type=”java.lang.String”>

<column name=”role_name” length=”100″ />

</property>

</class>

</hibernate-mapping>

4、测试用例

import java.util.Iterator;

import java.util.List;

import junit.framework.TestCase;

import org.apache.log4j.Logger;

import org.hibernate.FetchMode;

import org.hibernate.criterion.Projections;

import org.hibernate.criterion.Restrictions;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.mobilesoft.esales.dao.hibernate.SysUserDAO;

import com.mobilesoft.esales.model.SysUser;

import com.mobilesoft.esales.model.SysUserRole;

/**

* Hibernate Criteria用法测试用例

*

* @author liangchuan@mobile-soft.cn

*

*/

public class TestCriteria extends TestCase {

private static final Logger logger = Logger.getLogger(TestCriteria.class);

private static ClassPathXmlApplicationContext context = null;

private static SysUserDAO dao;

static {

context = new ClassPathXmlApplicationContext(new String[] {

“applicationContext.xml”, “applicationContext-resources.xml”,

“applicationContext-dao.xml”, “applicationContext-service.xml” });

}

protected void setUp() throws Exception {

}

/**

* 演示使用Criteria实现:select userId from SysUser as user

*/

public void testSelectId() {

dao = (SysUserDAO) context.getBean(“SysUserDAO”);

List mylist=dao.getHibernateTemplate().getSessionFactory().openSession().createCriteria(SysUser.class)

.setProjection(

Projections.projectionList().add(

Projections.property(“userId”)

)

).list();

Iterator iterator=mylist.iterator();

while(iterator.hasNext()){

logger.fatal(“id is :”+iterator.next());

}

}

/**

* 演示使用Criteria实现:select user.*,userRole.* from SysUser as user ,SysUserRole userRole where user.userId=userRole.id

*/

public void testJoin1() {

dao = (SysUserDAO) context.getBean(“SysUserDAO”);

List mylist=dao.getHibernateTemplate().getSessionFactory().openSession().createCriteria(SysUser.class)

.setFetchMode(“userRoles”,FetchMode.JOIN).list();

Iterator iterator=mylist.iterator();

while(iterator.hasNext()){

SysUser user=(SysUser)iterator.next();

logger.fatal(“testJoin1:userid is :”+user.getUserId()+” userName is “+user.getUserName());

}

}

/**

* 演示使用Criteria实现:select user.* ,userRole.* from SysUser as user ,SysUserRole userRole

* where user.userId=userRole.id and user.userId=1

*/

public void testJoin2() {

dao = (SysUserDAO) context.getBean(“SysUserDAO”);

List mylist=dao.getHibernateTemplate().getSessionFactory().openSession().createCriteria(SysUser.class)

.setFetchMode(“userRoles”,FetchMode.JOIN)

.add(Restrictions.eq(“userId”,1))

.list();

Iterator iterator=mylist.iterator();

while(iterator.hasNext()){

SysUser user=(SysUser)iterator.next();

logger.fatal(“testJoin2:userid is :”+user.getUserId()+” userName is “+user.getUserName());

}

}

/**

* 演示使用Criteria实现:select user.* ,userRole.* from SysUser as user ,SysUserRole userRole

* where user.userId=userRole.id and userRole.id.roleId=2

* 演示createAlias的使用

*/

public void testJoin3() {

dao = (SysUserDAO) context.getBean(“SysUserDAO”);

List mylist=dao.getHibernateTemplate().getSessionFactory().openSession().createCriteria(SysUser.class)

.setFetchMode(“userRoles”,FetchMode.JOIN)

.createAlias(“userRoles”, “b”)

.add(Restrictions.eq(“b.id.roleId”,2))

.list();

Iterator iterator=mylist.iterator();

while(iterator.hasNext()){

SysUser user=(SysUser)iterator.next();

logger.fatal(“testJoin3:userid is :”+user.getUserId()+” userName is “+user.getUserName());

}

}

/**

* 演示使用Criteria实现:select count(userId) from SysUser as user ,SysUserRole userRole

* where user.userId=userRole.id

* 同时演示createAlias的使用

*/

public void testJoin4() {

dao = (SysUserDAO) context.getBean(“SysUserDAO”);

List mylist=dao.getHibernateTemplate().getSessionFactory().openSession().createCriteria(SysUser.class)

.setFetchMode(“userRoles”,FetchMode.JOIN)

.setProjection( Projections.projectionList().add( Projections.count(“userId”) ))

.list();

Iterator iterator=mylist.iterator();

while(iterator.hasNext()){

logger.fatal(“testJoin4:count(userId) is :”+iterator.next());

}

}

/**

* 演示使用Criteria实现:select count(id.roleId) from SysUserRole userRole

* 同时演示createAlias的使用

*/

public void testJoin5() {

dao = (SysUserDAO) context.getBean(“SysUserDAO”);

List mylist=dao.getHibernateTemplate().getSessionFactory().openSession().createCriteria(SysUserRole.class)

.setProjection( Projections.projectionList().add( Projections.count(“id.roleId”) ))

.list();

Iterator iterator=mylist.iterator();

while(iterator.hasNext()){

logger.fatal(“testJoin5:count(roleId) is :”+iterator.next());

}

}

}

5、Model

5.1、SysUser.java

没有什么特别的,直接用myeclipse生成,然后在SysUser中添加上:

private java.util.Set userRoles = new HashSet();

public java.util.Set getUserRoles() {

return userRoles;

}

public void setUserRoles(java.util.Set userRoles) {

this.userRoles = userRoles;

}

5.2、SysUserRole.java

package com.mobilesoft.esales.model;

/**

* SysUserRole entity.

*

* @author MyEclipse Persistence Tools

*/

public class SysUserRole implements java.io.Serializable {

// Fields

private SysUserRoleId id;

private String userName;

private String roleName;

// Constructors

/** default constructor */

public SysUserRole() {

}

/** minimal constructor */

public SysUserRole(SysUserRoleId id) {

this.id = id;

}

/** full constructor */

public SysUserRole(SysUserRoleId id, String userName, String roleName) {

this.id = id;

this.userName = userName;

this.roleName = roleName;

}

// Property accessors

public SysUserRoleId getId() {

return this.id;

}

public void setId(SysUserRoleId id) {

this.id = id;

}

public String getUserName() {

return this.userName;

}

public void setUserName(String userName) {

this.userName = userName;

}

public String getRoleName() {

return this.roleName;

}

public void setRoleName(String roleName) {

this.roleName = roleName;

}

}

6、参考文档

http://www.devarticles.com/c/a/Java/Hibernate-Criteria-Queries-in-Depth/

http://www.devx.com/Java/Article/28754/1954

 

Technorati 标签: ,,,
Tagged with: , , ,

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s

%d 博主赞过: