下面我将核心类源码贴出来源码：
package com.ncs.util;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import com.ncs.pojo.Student;
import com.sun.org.apache.bcel.internal.generic.ACONST_NULL;
/**
* 动态创建hql通用查询语句的类
* @author yuanli
*
*/
public class CreateQueryHQL {
private static final String alias = "t_";
private static final String logicCalChar = "and";
private static final String from = "from";
private static final String select = "select";
private static final String dot = ".";
private static final String space = " ";
private static final String star = "*";
private static final String comma = ",";
private static final String orderby = "order by";
private static final String where = "where";
private static final String having = "having";
private static final String groupby = "group by";
private static final String update = "update";
private static final String delete = "delete";
private static final String insert = "insert";
/**
* 创建没有条件的完整表的查询语句
* @param c
* @return
*/
public static StringBuffer createCommonQuery(Class c){
StringBuffer sb = new StringBuffer(from);
sb.append(space).append(c.getSimpleName()).append(space);
sb.append(alias).append(c.getSimpleName()).append(space);
return sb;
}
/**
* 创建根据某个字段排序的hql查询语句
* @param hql
* @param map 排序类型、排序字段 键值对集合
* @param c
* @return
*/
public static StringBuffer createOrderByQuery(String hql,Map<String,OrderByType> map,Class c){
if(hql == null || (hql != null && hql.length()==0)){
throw new NullPointerException("基本hql不能为空");
}
if(map == null||(map != null && map.size() == 0)){
throw new NullPointerException("必须指定排序字段，以及排序类型");
}
StringBuffer shql = new StringBuffer(hql);
shql.append(orderby).append(space);
Iterator<String> iterator = map.keySet().iterator();
while(iterator.hasNext()){
String column = iterator.next();
shql.append(alias).append(c.getSimpleName()).append(dot)
.append(column).append(space).append(map.get(column))
.append(space).append(comma).append(space);
}
//返回最后一个逗号的位置
int last = shql.lastIndexOf(",");
String result = "";
if(last>0){
shql.delete(last, shql.length());
}
return shql;
}
/**
* 创建分组查询的hql语句
* @param hql
* @param groupByColumn
* @param c
* @return
*/
public static StringBuffer createGroupByQuery(String hql,String[] groupByColumn,Class c){
if(hql == null || (hql != null && hql.length()==0)){
throw new NullPointerException("基本hql不能为空");
}
if(groupByColumn == null||(groupByColumn != null && groupByColumn.length == 0)){
throw new NullPointerException("必须指定分组字段");
}
StringBuffer shql = new StringBuffer(hql);
shql.append(groupby).append(space);
for(int i=0;i<groupByColumn.length-1;i++){
shql.append(alias).append(c.getSimpleName()).append(dot).append(groupByColumn[i]).append(comma);
}
shql.append(alias).append(c.getSimpleName()).append(dot).append(groupByColumn[groupByColumn.length-1]).append(space);
return shql;
}
/**
* 创建包含条件分组查询
* @param hql
* @param condition
* @param c
* @return
*/
public static StringBuffer createGroupByQueryWithHaving(String hql,List<Condition> condition,Class c){
StringBuffer base = new StringBuffer(hql);
base.append(createHaving(condition, c));
return base;
}
/**
* 创建查询某个字段的hql语句,无条件
* @param c
* @param column
* @return
*/
public static StringBuffer createOneColumnQuery(Class c,String column){
if(column == null ||(column != null && column.length() == 0)){
throw new NullPointerException("必须指定一个要查询的字段");
}
StringBuffer hql = new StringBuffer(select);
hql.append(space).append(column).append(space);
hql.append(from).append(space).append(c.getSimpleName()).append(space);
hql.append(alias).append(c.getSimpleName()).append(space);
return hql;
}
/**
* 创建查询某个字段的hql语句,有条件
* @param c
* @param column
* @param condition
* @return
*/
public static StringBuffer createOneColumnQueryWithCondition(Class c,String column,List<Condition> condition){
StringBuffer sb = new StringBuffer(createOneColumnQuery(c, column));
sb.append(createWhere(condition,c));
return sb;
}
/**
* 创建查询部分字段的hql语句,非条件
* @param c
* @param columns
* @return
*/
public static StringBuffer createSomeColumnQuery(Class c,List<String> columns){
if(columns == null ||(columns != null && columns.size() == 0)){
throw new NullPointerException("要查询的字段名集合不能为空");
}
StringBuffer sb = new StringBuffer(select).append(space);
for(int i=0;i<columns.size()-1;i++){
sb.append(alias).append(c.getSimpleName()).append(dot).append(columns.get(i)).append(space);
sb.append(comma).append(space);
}
sb.append(alias).append(c.getSimpleName()).append(dot).append(columns.get(columns.size()-1)).append(space);
sb.append(from).append(space);
sb.append(c.getSimpleName()).append(space).append(alias).append(c.getSimpleName()).append(space);
return sb;
}
/**
* 创建查询部分字段的hql语句,有条件
* @param c
* @param columns
* @param condition
* @return
*/
public static StringBuffer createSomeColumnQueryWithCondition(Class c,List<String> columns,List<Condition> condition){
StringBuffer base = new StringBuffer(createSomeColumnQuery(c, columns));
base.append(createWhere(condition,c));
return base;
}
/**
* 创建聚合查询的hql语句,无条件
* @param c
* @param columnName 列名
* @param type 聚合类型
* @return
* @throws FormatException
*/
public static StringBuffer createAggregationQuery(Class c,String columnName,AggregationType type) throws FormatException{
StringBuffer sb = new StringBuffer(select);
sb.append(space).append(type)
.append("(");
if(type.equals(AggregationType.COUNT)&&(columnName == null ||(columnName != null && (columnName == star ||columnName.length()==0)))){
sb.append(star);
}else if(columnName != null && columnName.length() != 0 && columnName != star){
sb.append(alias).append(c.getSimpleName()).append(dot).append(columnName);
}else{
throw new FormatException("非COUNT(*)聚合统计,必须指定明确的字段！");
}
sb.append(")")
.append(space).append(from).append(space)
.append(c.getSimpleName()).append(space).append(alias).append(c.getSimpleName())
.append(space);
return sb;
}
/**
* 创建聚合查询的hql语句,有条件
* @param c
* @param columnName 列名
* @param type 聚合类型
* @param condition 条件集合
* @return
*/
public static StringBuffer createAggregationQueryWithCondition(Class c,String columnName,AggregationType type,List<Condition> condition){
StringBuffer base = null;
try {
base = new StringBuffer(createAggregationQuery(c,columnName,type));
} catch (FormatException e) {
e.printStackTrace();
}
base.append(createWhere(condition,c));
return base;
}
/**
* 创建包含条件的完整表的查询语句
* @param c
* @param condition
* @return
*/
public static StringBuffer createQueryWithCondition(Class c,List<Condition> condition){
StringBuffer base = new StringBuffer(createCommonQuery(c));
base.append(createWhere(condition,c));
return base;
}
/**
* 根据条件对象集合，返回条件语句
* @param condition
* @return
*/
private static String createWhere(List<Condition> condition,Class c){
if(condition == null || condition != null && condition.size() == 0){
throw new NullPointerException("条件集合不能为空");
}
StringBuffer conStr = new StringBuffer(where);
return createConditionStr(conStr, condition, c);
}
/**
* 创建分组条件语句
* @param condition
* @param c
* @return
*/
public static String createHaving(List<Condition> condition,Class c){
if(condition == null || condition != null && condition.size() == 0){
throw new NullPointerException("条件集合不能为空");
}
StringBuffer conStr = new StringBuffer(having);
return createConditionStr(conStr, condition, c);
}
/**
* 创建公用的条件语句
* @param conStr
* @param condition
* @param c
* @return
*/
private static String createConditionStr(StringBuffer conStr,List<Condition> condition,Class c){
conStr.append(space);
for(int i=0;i<condition.size()-1;i++){
Condition con = condition.get(i);
conStr.append(alias).append(c.getSimpleName()).append(dot).append(con.getKey())
.append(space)
.append(transOperator(con.getOperator()))
.append(space)
.append(getPlaceholder(con))
.append(space)
.append(logicCalChar)
.append(space);
}
//追加最后一个条件
Condition con = condition.get(condition.size()-1);
conStr.append(alias).append(c.getSimpleName()).append(dot).append(con.getKey())
.append(space)
.append(transOperator(con.getOperator()))
.append(space)
.append(getPlaceholder(con)).append(space);
return conStr.toString();
}
/**
* 返回占位符
* @param key
* @return
*/
public static String getPlaceholder(Condition con){
String key = con.getKey();
Operator op = con.getOperator();
String zhanWeiChar = ":";
if(key.indexOf(dot)>0){
key = key.replace('.', '_');
}
if(op.equals(Operator.GT)){
key += "_gt";
}else if(op.equals(Operator.LT)){
key += "_lt";
}
else if(op.equals(Operator.GE)){
key += "_ge";
}else if(op.equals(Operator.LE)){
key += "_le";
}
con.setKey(key);//重新设置键
return zhanWeiChar + key;
}
/**
* 转换比较运算符
* @param op
* @return
*/
private static String transOperator(Operator op){
String operator = "";
if(op.equals(Operator.EQ)){
operator = "=";
}else if(op.equals(Operator.NE)){
operator = "!=";
}else if(op.equals(Operator.GT)){
operator = ">";
}else if(op.equals(Operator.LT)){
operator = "<";
}
else if(op.equals(Operator.GE)){
operator = ">=";
}else if(op.equals(Operator.LE)){
operator = "<=";
}else if(op.equals(Operator.LIKE)){
operator = "like";
}else if(op.equals(Operator.ISNOTNULL)){
operator = "is not";
}else{
operator = "is";
}
return operator;
}
public static void main(String[] args) {
Class c = Student.class;
List<Condition> condition = new ArrayList<Condition>();
Condition name = new Condition("stuname",Operator.LIKE,"立");
Condition sex = new Condition("stusex",Operator.EQ,"男");
Condition agemin = new Condition("stuage",Operator.GE,"18");
Condition agemax = new Condition("stuage",Operator.LE,"25");
Condition inclass = new Condition("inclass",Operator.ISNULL,null);
condition.add(name);
condition.add(sex);
condition.add(agemin);
condition.add(agemax);
condition.add(inclass);
String s = createAggregationQueryWithCondition(Student.class, null, AggregationType.COUNT,condition).toString();
// System.out.println(s);
List<String> columns = new ArrayList<String>();
columns.add("stuname");
columns.add("stusex");
columns.add("stuage");
// String ss = createSomeColumnQueryWithCondition(c, columns,condition);
Map map = new HashMap<String,OrderByType>();
map.put("stuage",OrderByType.ASC);
// map.put("stuname",OrderByType.DESC);
// String orderbyhql = createOrderByQuery(ss, map,c);
// System.out.println(orderbyhql);
String hql = createSomeColumnQuery(c, columns).toString();
String[] groupByColumn = new String[]{"stuname","stuage"};
String groupBy = createGroupByQuery(hql, groupByColumn, c).toString();
groupBy = createGroupByQueryWithHaving(groupBy, condition, c).toString();
groupBy = createOrderByQuery(groupBy, map, c).toString();
System.out.println(groupBy);
}
}
/**
* 聚合类型枚举
* @author yuanli
*
*/
public enum AggregationType {
COUNT,
MAX,
MIN,
AVG,
SUM
}
/**
* 排序类型枚举
* @author yuanli
*
*/
public enum OrderByType {
DESC,
ASC
}
/**
* 比较运算符枚举
* @author yuanli
*
*/
public enum Operator {
EQ,//等于
LE,//小于等于
GE,//大于等于
LT,//小于
GT,//大于
NE,//不等于
LIKE,//模糊查询
ISNULL,//空
ISNOTNULL//非空
}
/**
* 查询条件实体类
* @author yuanli
*
*/
public class Condition {
private String key;
private Operator operator;
private String value;
public Condition() {
super();
}
public Condition(String key, Operator operator, String value) {
super();
this.key = key;
this.operator = operator;
this.value = value;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
public Operator getOperator() {
return operator;
}
public void setOperator(Operator operator) {
this.operator = operator;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}