preface:

Mybatis is a very popular database framework at present. Mybatis plus is an enhanced version of mybatis (only enhanced, not changed). You can study it if you are interested.

Method 1:

Configure xml file, which is a general method and suitable for any sql statement (especially complex sql).

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.gtja.ibcenter.wechat.moudle.mapper.WeChatPushRecordMapper">

    <select id="getPushRecord"  resultType="com.gtja.ibcenter.wechat.dto.WeChatPushRecordDto">
        select job_id jobId,pusher,type,app_key appKey,app_name appName,content,cancel,pr.create_time createTime,pr.update_time updateTime
        from wechat_push_record pr join wechat_app_info ai on pr.create_app=ai.app_key
        where job_id is not null

        <if test="pusher != null and pusher != ''">
            and pusher=#{pusher}
        </if>
        <if test="type != null and type != ''">
            and type=#{type}
        </if>
        <if test="createApp != null and createApp != ''">
            and create_app=#{createApp}
        </if>
        <if test="content != null and content != ''">
            and content like concat("%",#{content},"%")
        </if>
        <if test="cancel != null and cancel != ''">
            and cancel=#{cancel}
        </if>
        <if test="startTime != null and startTime != ''">
            and pr.create_time &gt;= #{startTime}
        </if>
        <if test="endTime != null and endTime != ''">
            and pr.create_time &lt;= #{endTime}
        </if>

        order by pr.create_time desc
    </select>

</mapper>

Note: the writing method of greater than sign and less than sign:

Original sql statement symbol

Escape symbol

>

>

>=

>=

<

<

<=

<=

Mode 2:

Using the @Select annotation, this method is suitable for relatively simple sql statements and is relatively simple to use.

    @Select("select dept_code,dept_name from dept_info where source = #{source}")
    List<DeptPo> getDeptBySource(@Param("source") Integer source);

Method 3:

SqlSession executes sql, which is slightly complex. It is not recommended to use it as a last resort. Mybatis plus humanizes the process of adding, deleting, modifying and checking. This method is suitable for people who do not want to do any configuration.

[available for reference Integrate mybatis plus and paging query functions into springboot_-CSDN blog Integrate mybatis plus].

Various wrappers are used to construct conditions:

Wrapper

explain

Wrapper

Conditional construction abstract class, topmost parent class

AbstractWrapper

Used to encapsulate query conditions and generate sql where conditions

QueryWrapper

Query condition encapsulation, not lambda syntax

UpdateWrapper

Update condition encapsulation, used for object update operations

AbstractLambdaWrapper

Lambda syntax uses Wrapper to handle parsing uniformly

LambdaQueryWrapper

Query Wrapper used by Lambda syntax

LambdaUpdateWrapper

Lambda update Wrapper

Conditional statement:

Query method

explain

setSqlSelect

Set the SELECT query field

where

WHERE statement, splicing +WHERE condition

and

AND statement, splicing +AND field = value

andNew

AND statement, splicing +AND (field = value)

or

OR statement, splicing +OR field = value

orNew

OR statement, splicing +OR (field = value)

eq

Equal to=

allEq

map based content equals=

ne

Not equal to < >

gt

Greater than >

ge

Greater than or equal to >=

lt

Less than<

le

Less than or equal to<=

like

Fuzzy query LIKE

notLike

Fuzzy query NOT LIKE

in

IN query

notIn

NOT IN query

isNull

NULL value query

isNotNull

IS NOT NULL

groupBy

GROUP BY

having

HAVING keyword

orderBy

Sort ORDER BY

orderAsc

ASC sort ORDER BY

orderDesc

DESC sort ORDER BY

exists

EXISTS conditional statement

notExists

NOT EXISTS conditional statement

between

BETWEEN conditional statement

notBetween

NOT BETWEEN conditional statement

addFilter

Free splicing SQL

last

Splice at the end, for example: last("LIMIT 1")

Example (there are all methods in BaseMapper):

int result = userMapper.insert(userPo);    // increase

QueryWrapper<UserPo> queryWrapper= new QueryWrapper<>();
queryWrapper.eq("uid", uid);
int result = userMapper.delete(queryWrapper);    // Delete

UpdateWrapper<UserPo> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("uid", uid);
int result = userMapper.update(userPo, updateWrapper);    //change

QueryWrapper<UserPo> queryWrapper= new QueryWrapper<>();
queryWrapper.eq("uid", uid);
List<UserPo> list = userMapper.selectList(queryWrapper);    //check

First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials