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