一个数据库查询方法(可以动态设置查询参数,设置查询条件),很巧妙的组合sql语句...

public <T> QueryResult<T> getScrollData(Class<T> entityClass,String wherejpql,Object[] queryParams,
int firstindex, int maxResult, LinkedHashMap<String, String> orderby) {
//for(int i = 0;i<queryParams.length;i++){
//System.out.println("=================queryParams is " + i +queryParams[i]);
//}
String entityName = getEntityName(entityClass);
QueryResult qr = new QueryResult<T>();
String sql = "select o from "+entityName+" o " + (wherejpql==null?"":"where " + wherejpql) + buildOrderby(orderby);
Query query = em.createQuery(sql);
setQueryParams(query,queryParams);
System.out.println("-------------------in DaoSupport :sql is:" + sql);
/**
* 1:查询的时候第一步查询到的都是数据表里所有的记录
* 2:再用query.setFirstResult(firstindex).setMaxResults(maxResult)
* 方法来设置要显示的首记录(即从哪一条开始显示)和要显示的记录条数
* 3:然后保存相关数据,方便前台页面显示
*/
if(firstindex != -1 && maxResult != -1){
query.setFirstResult(firstindex).setMaxResults(maxResult);
}


qr.setResultList(query.getResultList());
/**
* 这里的记录数不是要显示的记录有多少条,而是数据表里一共有多少条记录
*/
query = em.createQuery("select count(o) from "+entityName+" o " + (wherejpql==null?"":"where " + wherejpql));

setQueryParams(query,queryParams);

qr.setTotalrecord((Long)query.getSingleResult());
return qr;
}

/**
* 构建order by 语句
* orderby 语句格式:order by key1 asc,key2 asc
* @param orderby
* @return
*/
protected String buildOrderby(LinkedHashMap<String, String> orderby){
StringBuffer orderbySql = new StringBuffer("");
if(orderby !=null && orderby.size()>0){
orderbySql.append(" order by ");
//order by key1 desc,key2 asc
for(String key:orderby.keySet()){
orderbySql.append(key).append(" ").append(orderby.get(key)).append(",");
}
orderbySql.deleteCharAt(orderbySql.length() - 1);
}
return orderbySql.toString();
}

/**
* 给where语句设置参数值
* wherejpql 语句格式:where o.property=?1,o.property=?2。
* @param query
* @param params
*/
protected void setQueryParams(Query query,Object[] params){
if(params != null && params.length > 0){
for(int i=0;i<params.length;i++){
//System.out.println("i+1 = " + (i+1) + " params[i] " + params[i] );
query.setParameter(i+1, params[i]);
}
}
}

QueryResult.java:

package com.itcast.util;

import java.util.List;

public class QueryResult<T>{
private List<T> resultList;
private long totalrecord;
public List<T> getResultList() {
return resultList;
}
public void setResultList(List<T> resultList) {
this.resultList = resultList;
}
public long getTotalrecord() {
return totalrecord;
}
public void setTotalrecord(long totalrecord) {
this.totalrecord = totalrecord;
}

}

展开阅读全文

没有更多推荐了,返回首页