自定义动态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: ( ExecutableSql )
select * from orders where userid in ('bee','orders3','orders4')
[INFO] [Bee] | <-- select rows: 5

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

1. 批量插入操作

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

    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. ,

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

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

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

select * from orders where name like #{%name}

当不解析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框架,要将标签前的一个或两个字符删除.

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

​