mybatis-plus Condition拼接Sql语句各方法
1.setSqlSelect—用于添加查询的列信息
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
public Wrapper<T> setSqlSelect(String... columns) { StringBuilder builder = new StringBuilder(); String[] arr = columns; int len = columns.length; for(int i = 0; i$ < len; ++i$) { String column = arr$[i$]; if (StringUtils.isNotEmpty(column)) { if (builder.length() > 0) { builder.append(","); } builder.append(column); } } this.sqlSelect = builder.toString(); return this; } |
2.eq—拼接等于的where语句 column=params
|
1
2
3
4
5
6
|
public Wrapper<T> eq(boolean condition, String column, Object params) { if (condition) { this.sql.WHERE(this.formatSql(String.format("%s = {0}", column), params)); } return this; } |
3.ne—拼接不等于的where语句 column<>params
|
1
2
3
4
5
6
|
public Wrapper<T> ne(boolean condition, String column, Object params) { if (condition) { this.sql.WHERE(this.formatSql(String.format("%s <> {0}", column), params)); } return this; } |
4.allEq–拼接数据以map封装的等于的where语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
public Wrapper<T> allEq(boolean condition, Map<String, Object> params) { if (condition && MapUtils.isNotEmpty(params)) { Iterator iterator = params.entrySet().iterator(); while(iterator.hasNext()) { Entry<String, Object> entry = (Entry)iterator.next(); Object value = entry.getValue(); if (StringUtils.checkValNotNull(value)) { this.sql.WHERE(this.formatSql(String.format("%s = {0}", entry.getKey()), entry.getValue())); } } } return this; } |
5.gt—-拼接大于号的where语句
|
1
2
3
4
5
6
|
public Wrapper<T> gt(boolean condition, String column, Object params) { if (condition) { this.sql.WHERE(this.formatSql(String.format("%s > {0}", column), params)); } return this; } |
6.ge–拼接大于等于号的where语句 （代码类似5）
7.lt—拼接小于号的where语句
8.le—小于等于
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
/** * AND (user_name=#{ew.paramNameValuePairs.MPGENVAL1} AND id=1) OR (user_status=#{ew.paramNameValuePairs.MPGENVAL2} OR status=1 AND user_nickname NOT LIKE #{ew.paramNameValuePairs.MPGENVAL3}) AND (new=xx AND hhh LIKE #{ew.paramNameValuePairs.MPGENVAL4}) AND (pwd=11 AND n1 IS NOT NULL AND n2 IS NOT NULL AND n3 IS NULL) GROUP BY x1, x2,x3 HAVING (x1=11 AND x3=433) ORDER BY dd, d1,d2 */ ew.where("user_name={0}", "'zhangsan'").and("id=1") .orNew("user_status={0}", "0").or("status=1") .notLike("user_nickname", "notvalue") .andNew("new=xx").like("hhh", "ddd") .andNew("pwd=11").isNotNull("n1,n2").isNull("n3") .groupBy("x1").groupBy("x2,x3") .having("x1=11").having("x3=433") .orderBy("dd").orderBy("d1,d2"); |
9.and — 在没有出现下一个andNew或者orNew之前的所有where条件在一个括号里
10.andNew –新建一组and语句
11.or,orNew–同上
mybatis-plus 自定义sql语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
@Override public Map<String, Object> kccxhj(Term term) throws Exception{ QueryWrapper<EZShangPin> wrapper=new QueryWrapper<>(); if(MyStrUtils.isNotEmpty(term.getQ())){ CWMSTmdrdmx tmdrdmx=new CWMSTmdrdmx(); tmdrdmx.setTm(term.getQ()); tmdrdmx.setSl(1); tmService.recongnizeSptm(tmdrdmx); //log("tmdrdmx",tmdrdmx); if(tmdrdmx.getResult()==0){ wrapper.eq("spdm",tmdrdmx.getSpdm()); }else{ wrapper.like("spdm+'_'+spmc+'_'+isnull(zjf,'')", term.getQ()); } } wrapper.eq(MyStrUtils.isNotEmpty(term.getPpdm()),"fjsx1",term.getPpdm()); wrapper.eq(MyStrUtils.isNotEmpty(term.getDldm()),"fjsx2",term.getDldm()); wrapper.eq(MyStrUtils.isNotEmpty(term.getJjdm()),"fjsx3",term.getJjdm()); wrapper.eq(MyStrUtils.isNotEmpty(term.getNddm()),"fjsx4",term.getNddm()); wrapper.eq("isnull(tzsy,0)", 0); wrapper.eq("ckdm", term.getCurrentsddm()); return repMapper.kccxhj(wrapper); } |
|
1
2
3
4
5
|
@Mapper@Repositorypublic interface RepMapper extends BaseMapper { Map<String, Object> kccxhj(@Param(Constants.WRAPPER) QueryWrapper wrapper); } |
|
1
2
3
4
5
6
7
8
9
|
<select id="kccxhj" resultType="java.util.Map"> SELECT SUM(SL) AS zsl,sum(sl*bzsj) as zje from( select ckdm,ShangPin.spdm,ShangPin.spmc,ShangPin.zjf,shanpin.bzsj,ShangPin.FJSX1,ShangPin.FJSX2,ShangPin.FJSX3,ShangPin.FJSX4,SPKCB.SL from shangpin,SPKCB where shangpin.spdm=spkcb.SPDM ) a ${ew.customSqlSegment} </select> |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/qq_36752632/article/details/79035980
