自定义动态SQL标签(易用,效率高)

1.List, Set转SQL in

定义的sql:

select * from orders where userid in #{userid @in}

Java代码:

			Map<String, Object> map = new HashMap<>();
			
			List<String> list=new ArrayList<>();
			list.add("bee");
			list.add("orders3");
			list.add("orders4");
			map.put("userid", list);

            select(sqlIn, map);


	private static void select(String sql,Map<String,Object> map) {
		List<Orders> list3 = preparedSql.select(sql, new Orders(), map,1,3);//map
		for (int i = 0; i < list3.size(); i++) {
			Logger.info(list3.get(i).toString());
		}
	}

生成的sql:

[INFO] [Bee] ========= get the dbName from the Connection is :MySQL
[INFO] [Bee] PreparedSql select SQL: select * from orders where userid in  (?,?,?)   [values]: bee(String),orders3(String),orders4(String)
[INFO] [Bee] PreparedSql select SQL:  ( ExecutableSql )
select * from orders where userid in  ('bee','orders3','orders4')
[INFO] [Bee]  | <--  select rows: 5

Bee V1.11 对in操作,支持List, Set两种类型

2. 批量插入操作

无需像foreach之类的标签, Bee框架会自动为我们处理.

			String sql9="INSERT INTO orders(id,name,userid) VALUES (#{id},#{name},#{userid});";
			final String USERID="userid";
			final String NAME="name";
			final String ID="id";
			Map<String,Object> insertMap1=new HashMap<>();
			insertMap1.put(USERID, "1001");
			insertMap1.put(NAME, "bee");
			insertMap1.put(ID, 121L);
			
			Map<String,Object> insertMap2=new HashMap<>();
			insertMap2.put(USERID, "1001");
			insertMap2.put(NAME, "bee");
			insertMap2.put(ID, 122L);
			
			Map<String,Object> insertMap3=new HashMap<>();
			insertMap3.put(USERID, "1001");
			insertMap3.put(NAME, "bee");
			insertMap3.put(ID, 123L);
			
			List<Map<String, Object>> parameterMapList=new ArrayList<>();
			parameterMapList.add(insertMap1);
			parameterMapList.add(insertMap2);
			parameterMapList.add(insertMap3);
			

	//		preparedSql.insertBatch(sql9, parameterMapList,2);

			preparedSql.insertBatch(sql9, parameterMapList);

生成的sql:

INSERT INTO orders(id,name,userid) VALUES (?,?,?), (?,?,?), (?,?,?)

可以看到,使用Mysql时,Bee框架会为我们智能的添加多组占位符.

3. <if isNotNull>,<if isNotBlank>

select * from orders where <if isNotNull>userid in #{userid@in}</if>

当#{userid@in}里声明的参数userid的值不为null时,还解析<if isNotNull> </if>之前的代码.

<if isNotBlank>类似,表示不为null且不为空字符时,才解析.

select * from orders where <if isNotBlank>name like #{%name}</if>

当不解析if时,

sql变为:select * from orders where,

Bee为自动将where删除. 变成正确的: select * from orders

4.@toIsNULL1,@toIsNULL2

我们看下这个SQL自定义语句.

select * from orders where userid=#{userid}

当userid为null时,会转成
 

select * from orders where userid=null

这是不对的.应该是:

select * from orders where userid is null

使用@toIsNULL1和@toIsNULL1就可以解决这个问题.当是null时, 就转成is null.
 

select * from orders where userid=#{userid @toIsNULL1}

select * from orders where id>=#{id@toIsNULL2}

@toIsNULL1,@toIsNULL2后面的数字1和2是告诉Bee框架,要将标签前的一个或两个字符删除.

一般是"="或">="之类的符号.

源码:

https://gitee.com/automvc/bee-exam/blob/master/src/main/java/org/teasoft/exam/bee/osql/PreparedSqlCustomAutoSqlTokenExam.java