SQL动态查询“万法归1”解决方案

这是原创文章，转载请注明javaeye的picksun所作，谢谢~

?

Hibernate使用的criteria语法过于复杂，使用的很多类及常量需要特别引入，

因此本人设计一个小型的条件查询，满足基本的查询需要，可以通过map实现多表查询、“group by”等

类似于PCI的插拔式结构，条件一般都是可以随意插拔的。

当然你可以并联，就想历史上大名鼎鼎的"巫毒"显卡一样，

把两个条件并联起来("or" 关联或者"between"条件的时候使用，可以使用括号)

实现一个语法，一般是通过拼接，完成对单追加条件（可以在后面追加条件），

where条件可以在conList不断添加，完全不加也可以，这样就实现了sql查询的”万法归一“：

既不用写一堆重载的findByProperties,也不用自己拼接SQL（或者HQL）

?

List<SqlConditionGroup> conList = new ArrayList<SqlConditionGroup>();

PageControl pageControl??? = PageControl.getQueryOnlyInstance();
//PageControl pageControl?? ?= PageControl.getPageEnableInstance(start, limit);

pageControl .setOrder("order by school desc, dataContent asc");

//pageControl .groupBy("group by xxx");

?

?

conList.add(new SqlConditionGroup("school","1","=","and"));

conList.add(new SqlConditionGroup("dataContent","%"+bean.getDataContent()+"%","like","and"));

conList.add(new SqlConditionGroup("school",Arrays.asList(new String[]{"1","2"}),"in","and"));

conList.add(new SqlConditionGroup("(dataValue",new Double(1.0),"between",""));

conList.add(new SqlConditionGroup("and",new Double(2.0),"",")and"));

conList.add(new SqlConditionGroup("schoolId",null,"in (select schoolId from tableA)","and"));

?

?

?

conList.add(new SqlConditionGroup("((fDate", beg_date,"=","and"));

conList.add(new SqlConditionGroup("fTime", end_time,"<=",")or("));
conList.add(new SqlConditionGroup("fDate", end_date,"=","and"));
conList.add(new SqlConditionGroup("fTime", end_time,"<=","))"));

?

?

//使用SQL
List<PDayData> day_list = curService.findByNativeCondition(pageControl, conList, "select * from PDayData ", PDayData.class);//PDayData.class,Map.class都可以

?

//使用HQL
List<PDayData> day_list = curService.findByCondition(pageControl, conList);

?

?

简单说明:SqlConditionGroup的结构,sign 默认为"=",type默认为"and"

/**
??? ?* Sql参数查询条件
??? ?*
??? ?* @param name? 属性名
??? ?* @param value 值，可以是Array,List或者单值
??? ?* @param sign? 符号,如>,<,=,like,<>,in,isnull等等
??? ?* @param type? 连接类型,and或or，视情况可加括号
??? ?*/
??? public SqlConditionGroup(String name, Object value, String sign, String type) {
??? ??? super();
??? ??? this.name = name;
??? ??? this.value = value;
??? ??? this.sign = sign;
??? ??? this.type = type;
??? }