ORM Bee如何实现复杂查询,实现复杂条件

对于复杂SQL查询,ORM Bee可以使用Condition condition构造复杂的条件.

对于复杂SQL查询,ORM Bee可以使用Condition condition构造复杂的条件.

/*
 * Copyright 2016-2019 the original author.All rights reserved.
 * Kingstar(honeysoft@126.com)
 * The license,see the LICENSE file.
 */

package org.teasoft.exam.bee.osql;

import java.math.BigDecimal;
import java.util.List;

import org.teasoft.bee.osql.BeeException;
import org.teasoft.bee.osql.FunctionType;
import org.teasoft.bee.osql.Op;
import org.teasoft.bee.osql.OrderType;
import org.teasoft.bee.osql.api.Condition;
import org.teasoft.bee.osql.api.Suid;
import org.teasoft.bee.osql.api.SuidRich;
import org.teasoft.exam.bee.osql.entity.Orders;
import org.teasoft.exam.bee.osql.entity.TestUser;
import org.teasoft.exam.comm.Printer;
import org.teasoft.honey.osql.core.BeeFactory;
import org.teasoft.honey.osql.core.BeeFactoryHelper;
import org.teasoft.honey.osql.core.ConditionImpl;
import org.teasoft.honey.osql.core.HoneyUtil;
import org.teasoft.honey.osql.core.Logger;

/**
 * @author Kingstar
 * @since  1.6
 */
public class ConditionExam {
	
	private static SuidRich suidRich=BeeFactoryHelper.getSuidRich();
	
	public static SuidRich getSuidRich() {
		return suidRich;
	}
	
	public static void setSuidRich(SuidRich suidRich) {
		ConditionExam.suidRich=suidRich;
	}
	
	public static void main(String[] args) {
		test();
	}
	public static void test() {
       try{
		Suid suid = BeeFactory.getHoneyFactory().getSuid();
		Orders orders = new Orders();
		
//		SimpleDateFormat defaultFormat= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
//		orders.setName("Bee(ORM Framework)"); //等于的条件,会默认转换
//		orders.setTotal(new BigDecimal("100"));  //不会再处理.因为between已有用
		
		 Condition condition=new ConditionImpl();
		 condition
//		 .op("1=1 -- userid", Op.like, "bee%") // test invalid field
//		 .lParentheses()    //(  左括号
		 .op("userid", Op.like, "bee%") //模糊查询
//		 .rParentheses()    //) 右括号
//		 .op("userid", Op.like, "%") //模糊查询   测试非法模糊查询
//		 .set("userid", 2)
//		 .between("total", 90, 100)     //范围查询
//		 .op("name", Op.nq, null)     //is not null
//		 .between("createtime","2020-03-01","2020-03-03")
//		 .between("createtime",DateUtil.currentDate(),DateUtil.currentDate())
//		 .between("createtime",new Date(),new Date())
		 .orderBy("userid",OrderType.ASC) //排序
		 .start(0).size(100)              //分页
		 ;
		 
//       测试顺序异常
//		 condition
//		 .orderBy("name")
//		 .groupBy("userid") 
//		 ;
		 
//		long start=System.currentTimeMillis();
//		for (int k = 0; k < 10000; k++) {
		List<Orders> list2 = suid.select(orders, condition);
		for (int i = 0; i < list2.size(); i++) {
			Logger.info(list2.get(i).toString());
		}
//		}
//		long end=System.currentTimeMillis();
//		Logger.info("--------------------------------");
//		Logger.info(end-start);
		
		condition.selectField("userid,total,createtime");//只查询部分字段
//		
		List<Orders> list3 = suid.select(orders, condition);
		for (int i = 0; i < list3.size(); i++) {
			Logger.info(list3.get(i).toString());
		}
		
		} catch (BeeException e) {
			Logger.error("In ConditionExam (BeeException):"+e.getMessage());
			e.printStackTrace();
		}catch (Exception e) {
			Logger.error("In ConditionExam (Exception):"+e.getMessage());
			e.printStackTrace();
		}
	}
}

生成的SQL(默认是可以防SQL注入的, like也是可以的,但mybatis的,却是提了两种方式,like不绑定参数,还不能防注入):

[INFO] [Bee] select SQL: select userid, total, createtime from orders where userid like ? order by userid asc limit ?,?   [values]: bee%(String),0(Integer),100(Integer)
[INFO] [Bee] select SQL:  ( ExecutableSql )
select userid, total, createtime from orders where userid like 'bee%' order by userid asc limit 0,100 ;

以上代码,可以应对复杂的查询条件.通过Condition可以构造各种where条件, 且不用提前声明.

它还支持In语法,使用List类型.

package org.teasoft.exam.bee.osql;

import java.util.ArrayList;
import java.util.List;

import org.teasoft.bee.osql.Op;
import org.teasoft.bee.osql.api.Condition;
import org.teasoft.bee.osql.api.Suid;
import org.teasoft.exam.bee.osql.entity.Orders;
import org.teasoft.honey.osql.shortcut.BF;

/**
 * @author Kingstar
 * @since  2.2
 */
public class ConditionInListExam {
	
	public static void main(String[] args) {
		
//		org.teasoft.bee.osql.BeeSQLException: No value specified for parameter 1
		Suid suid=BF.getSuid();
		Condition condition=BF.getCondition();
		List<String> list=new ArrayList<>();
		list.add("Bee");
		list.add("mybatis");
		condition.op("name", Op.in,list); //作为in的 list为空时,会报错; 2.1.8 bug
		
//		suid.select(orders, condition);
		suid.select(new Orders(), condition);
	}
}

[INFO] [Bee] ========= get the dbName via url is: MySQL
[INFO] [Bee] select SQL: select id,userid,name,total,orderid,createtime,remark,sequence,abc,updatetime from orders where name in (?,?)   [values]: Bee(String),mybatis(String)
[INFO] [Bee] select SQL:  ( ExecutableSql )
select id,userid,name,total,orderid,createtime,remark,sequence,abc,updatetime from orders where name in ('Bee','mybatis') ;
[WARN] Do not set the database info: bee.db.driverName do not config; 
[DEBUG] Use OriginalConn!
[INFO] [Bee]  | <--  select rows: 8

Condition构造的条件,可以应用于Select, Update, Delete。