一个数据库查询方法（可以动态设置查询参数，设置查询条件），很巧妙的组合sql语句

最新推荐文章于 2024-07-21 04:28:15 发布

gaoge19861207

最新推荐文章于 2024-07-21 04:28:15 发布

阅读量6.2k

点赞数

分类专栏：源代码文章标签： sql 数据库 string query object null

本文链接：https://blog.csdn.net/gaoge19861207/article/details/4601842

版权

源代码专栏收录该内容

20 篇文章 0 订阅

订阅专栏

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;
}