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 @Repository public 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
查看更多关于mybatis-plus 使用Condition拼接Sql语句各方法的用法的详细内容...