hibernate复合查询

hibernate复合查询

1.    问题场景

考试系统开发中，类似查询考题等，查询条件不固定，有时需要按照题目类型、题目难度、科目进行查询，有时可能只输入其中一个条件进行查询，因此查询接口需要具有很强的扩展性。

2.    实现方法

查询基本类AndQueryCreator.java

import java.util.List;   /**  * 查询and基本类  * @author huaqing  *  */ public class AndQueryCreator {            protected String[] condition ;                   protected List<ConBean> conditionKeys ;                   protected Object obj;                   private static final String PREFIX = "get";                   protected void init()          {                             }                   public AndQueryCreator(String... condition)          {                    this.condition = condition;                    this.init();          }                   public AndQueryCreator(Object obj)          {                    this.obj = obj;                    this.init();          }                   public String createStatement()          {                    StringBuffer content = new StringBuffer("");                    if(condition == null || condition.length == 0 )                             return "";                    for(int i=0; i< condition.length; i++)                    {                             String value = condition[i];                             if(value == null || value.isEmpty() || value.trim().isEmpty() || value.equals("-1") )                                      continue;                             else                             {                                      if(i != 0 && content.length() > 0)                                                content.append(" and ");                                      content.append(conditionKeys.get(i).toString());                                      if(conditionKeys.get(i).getOperator().equals("like"))                                      {                                                content.append("'%");                                                content.append(value);                                                content.append("%'");                                      }                                      else if(conditionKeys.get(i).getOperator().equals("=="))                                      {                                                content.append(Integer.parseInt(value));                                      }                                      else if(conditionKeys.get(i).getOperator().equals("="))                                      {                                                content.append("'" + value + "'");                                      }                                      else                                      {                                                content.append(value);                                      }                             }                    }                    return content.toString();          }                   public String appendMethodName(String key)          {                                       StringBuffer buffer = new StringBuffer();                    buffer.append(PREFIX);                    key = key.substring(0, 1).toUpperCase() + key.substring(1);                    buffer.append(key);                    return buffer.toString();          } }

查询条件封装对象ConBean.java:

其中key是要查询的字段名，operator是查询操作符（=，like等）

public class ConBean {            private String key;                   private String operator;                   public ConBean(String key, String operator)          {                    this.key = key;                   this.operator = operator;          }                            public String getKey() {                    return key;          }              public void setKey(String key) {                    this.key = key;          }              public String getOperator() {                    return operator;          }              public void setOperator(String operator) {                    this.operator = operator;          }              /**           * 暂时只支持 = 和 like两种查询， between等暂时先不实现           */          public String toString()          {                    StringBuffer buf = new StringBuffer(key);                    if(operator.equals("=="))                    {                             buf.append(" ");                             buf.append("=");                             buf.append(" ");                    }                    else                    {                             buf.append(" ");                             buf.append(operator);                             buf.append(" ");                    }                    return buf.toString();          } }

复合查询实现类QuestionAndQueryCreator.java

import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.List;   import com.geariot.platform.exam.entities.Question;     public class QuestionAndQueryCreator extends AndQueryCreator {          protected void init() {                    conditionKeys = new ArrayList<ConBean>();                    ConBean bean1 = new ConBean("typeName", "=");                    ConBean bean2 = new ConBean("level", "==");                    ConBean bean3 = new ConBean("subject", "like");                      conditionKeys.add(bean1);                    conditionKeys.add(bean2);                    conditionKeys.add(bean3);                      List<String> conditionValueLs = new ArrayList<String>();                    Question q = (Question) obj;                    if (q != null) {                             Class<?> demo = null;                             try {                                      demo = Class.forName("com.geariot.platform.exam.entities.Question");                             } catch (ClassNotFoundException e) {                                      // TODO Auto-generated catch block                                      e.printStackTrace();                             }                             for (ConBean bean : conditionKeys) {                                      String methodName = appendMethodName(bean.getKey());                                      Method method = null;                                      String conditionValue = null;                                      try {                                                method = demo.getMethod(methodName);                                                Object valueObj = method.invoke(q);                                                if (valueObj instanceof Integer)                                                         conditionValue = String.valueOf(valueObj);                                                else                                                         conditionValue = (String) valueObj;                                      } catch (NoSuchMethodException e) {                                                // TODO Auto-generated catch block                                                e.printStackTrace();                                      } catch (SecurityException e) {                                               // TODO Auto-generated catch block                                                e.printStackTrace();                                      } catch (IllegalAccessException e) {                                                // TODO Auto-generated catch block                                                e.printStackTrace();                                      } catch (IllegalArgumentException e) {                                                // TODO Auto-generated catch block                                               e.printStackTrace();                                      } catch (InvocationTargetException e) {                                                // TODO Auto-generated catch block                                                e.printStackTrace();                                      }                                      conditionValueLs.add(conditionValue);                             }                             condition = (String[]) conditionValueLs                                                .toArray(new String[conditionValueLs.size()]);                    }          }            public QuestionAndQueryCreator(String... con) {                    // TODO Auto-generated constructor stub                    super(con);          }            public QuestionAndQueryCreator(Question q) {                    // TODO Auto-generated constructor stub                    super(q);          } }

Query生成辅助类QueryUtils.java

import org.apache.log4j.Logger; import org.hibernate.Query; import org.hibernate.Session;   import com.geariot.platform.exam.model.ORDER_CON;   /**  * query生成辅助  * @author huaqing  *  */ public class QueryUtils {            private static final Logger logger = Logger.getLogger(QueryUtils.class);            public static Query createLocalQuery(Session session, StringBuffer basicQueryStr, String creatorStr, ORDER_CON orderByTime)          {                    Query query;                    String prefix = " and ";                    if(basicQueryStr.indexOf("where") == -1)                             prefix = " where ";                    if(creatorStr != null && !creatorStr.isEmpty())                    {                             basicQueryStr.append(prefix);                             basicQueryStr.append(creatorStr);                    }                    switch(orderByTime)                    {                             case DESC_ORDER:                                      basicQueryStr.append(" order by createTime desc");                                      break;                             case ASC_ORDER:                                      basicQueryStr.append(" order by createTime asc");                                      break;                             default:                                      break;                                                         }                    logger.debug("复合查询sql:" + basicQueryStr.toString());                    query = session.createQuery(basicQueryStr.toString()).setCacheable(true);                    return query;          } }

 

Dao层调用实现复合查询

public List<Question> getQuestions(Question q, int start, int number) {                    StringBuffer basicQueryStr ;                    basicQueryStr =  new StringBuffer("select new Question(id, level, type, typeName, abs, subject) from Question");                    QuestionAndQueryCreator creator = new QuestionAndQueryCreator(q);                    String creatorStr = creator.createStatement();                    Query query = QueryUtils.createLocalQuery(getSession(),basicQueryStr, creatorStr,ORDER_CON.DESC_ORDER);                    query.setCacheable(true);                    query.setFirstResult(start);//设置起始行                    query.setMaxResults(number);//每页条数                                         return query.list();          }