很多时候我们会根据动态条件去查找数据,这个时候如果自己去动态构建HQL语句,语句就会比较复杂,这个时候Criteria Query可以帮助我们从面向对象的角度来构建SQL,下面举例说明一下,按照动态条件的分页查询如何实现,对用户进行动态条件查询,查询条件包含角色信息
UserQuery:
public class Query {
private Integer startRow;
private String sortBy;
private String sortType;
private Integer querySize;
public Query(){
}
public Query(Query query){
this.startRow = query.startRow;
this.sortBy = query.sortBy;
this.sortType = query.sortType;
this.querySize= query.querySize;
}
public Integer getStartRow() {
return startRow;
}
public void setStartRow(Integer startRow) {
this.startRow = startRow;
}
public String getSortBy() {
return sortBy;
}
public void setSortBy(String sortBy) {
this.sortBy = sortBy;
}
public String getSortType() {
return sortType;
}
public void setSortType(String sortType) {
this.sortType = sortType;
}
/**
* @return the querySize
*/
public Integer getQuerySize() {
return querySize;
}
/**
* @param querySize
* the querySize to set
*/
public void setQuerySize(Integer querySize) {
this.querySize = querySize;
}
@Override
public String toString() {
return MoreObjects.toStringHelper(this.getClass())
.add("startRow", startRow).add("querySize", querySize)
.add("sortBy", sortBy).add("sortType", sortType)
.toString();
}
public class UserQuery extends Query {
private String username;
private String rolename;
public UserQuery(){
}
public UserQuery(UserQuery query){
super(query);
this.username = query.username;
this.rolename = query.rolename;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
/**
* @return the rolename
*/
public String getRolename() {
return rolename;
}
/**
* @param rolename the rolename to set
*/
public void setRolename(String rolename) {
this.rolename = rolename;
}
@Override
public String toString() {
return MoreObjects.toStringHelper(this.getClass())
.add("startRow", getStartRow()).add("querySize", getQuerySize())
.add("sortBy", getSortBy()).add("sortType", getSortType())
.add("username", username).add("rolename", rolename)
.toString();
}
}
按照条件查询用户总数和按照条件查询分页用户实现
public int getTotalCount(UserQuery query) {
if(null == query){
throw new IllegalArgumentException("query should not be null");
}
if(LOG.isDebugEnabled()){
LOG.debug("create criteria with query: " + query);
}
Criteria c = createCriteria(query);
return ((Long)c.setProjection(Projections.rowCount()).uniqueResult()).intValue();
}
private Criteria createCriteria(UserQuery query) {
if(LOG.isDebugEnabled()){
LOG.debug("create criteria with query: " + query);
}
Criteria c = getSession().createCriteria(User.class);
if(EnumUtils.isValidEnum(RoleNameEnum.class, query.getRolename())){
c.createAlias("roles", "role");
c.add(Restrictions.eq("role.name", query.getRolename()));
}
return c;
}
/**
* @param Query should not be null
* @return Result list (UserInfo), list is empty if no result found
*/
@SuppressWarnings("unchecked")
public List<User> findUserByQuery(UserQuery query) {
if(null == query){
throw new IllegalArgumentException("query should not be null");
}
Criteria c = createCriteria(query);
if (query.getStartRow() != null){
c.setFirstResult(query.getStartRow());
}
if (query.getQuerySize() != null){
c.setMaxResults(query.getQuerySize());
}
return c.list();
}