A list,

Mybatis-Plus (hereinafter referred to as MBP) is intended to simplify development, and does not recommend developers to write their own SQL statements; However, sometimes the customer needs are complicated, and it is difficult to realize the requirements by combining MBP Service, Mapper and Wrapper only. This is where custom SQL comes in.

MBP can be customized in the following two ways:

1. Use custom SQL functions provided in Wrapper; (recommended)

Annotation method; (recommended)

3.XML configuration file. (Not recommended)

Two, use method

With the Wrapper

1. Use the Wrapper base class to customize the SQL statement behind Where;

  • inSql | inSql(boolean condition, R column, String inValue)

    InSql (“id”, “select ID from table where ID < 3”) –> id in (select ID from table where ID < 3) –> id in (select id from table where ID < 3)

  • notInSql | notInSql(boolean condition, R column, String inValue)

    NotInSql (“id”, “select id from table where ID < 3”) –> id in (select id from table where ID < 3)

  • having | having(boolean condition, String sqlHaving, Object… params)

    Having (“sum(age) > 10”) –> having sum(age) > 10

  • apply | apply(boolean condition, String applySql, Object… params)

    Example: apply (” date_format (dateColumn, ‘Y – m – % d % %) = {0} “, “2008-08-08″) — – > date_format (dateColumn,’ Y – m – % d % %) = ‘2008-08-08′”

  • last | last(boolean condition, String lastSql)

    Example: last (” limit 1 “)

  • exists | exists(boolean condition, String existsSql)

    Exists (“select id from table where age = 1”) –> exists(select id from table where age = 1)

  • notExists | notExists(boolean condition, String notExistsSql)

    NotExists (“select id from table where age = 1”) –> not exists (select id from table where age = 1)

2. Use QueryWrapper

  • select | select(String… sqlSelect)

    The select (” id “, “name”, “age”)

3. Use UpdateWrapper

  • set | set(boolean condition, String column, Object val)

    Example: set(“name”, “Lao Li tou “);

  • SetSql | setSql (” name = ‘Lao li head “)

    SetSql (“name = ‘name’)

Annotation Method

We can use full custom SQL in Mapper’s inherited classes;

1. Query

@Select("select * from t_station where no = #{paramNo}" and type = #{paramType}")

List<TStation> getStationByNo(String paramNo ,String paramType);
Copy the code

Update 2.

@Update("update t_station set no = #{paramNo} where id = #{paramId}")

int updateStationByid(@Param("paramNo" no,@Param("ParamId") id)
Copy the code

3. Insert the

@Insert("insert into t_station (no,name) values (#{no},#{name})");

Int insert(TStation station)
Copy the code

4. Remove

@Delete("Delete from t_station where no = #{no}")

int deleteByNo(String no)
Copy the code
XML configuration file

1. Define functions in Mapper’s inherited classes:

public interface StationMapper extends BaseMapper<User> {
	public User getStationById(Long id);
}
Copy the code

2. Specify the location of the mapper file in application.yml

mybatis-plus:
  mapper-locations: classpath:/mapper/**.xml
Copy the code

Classpath :/mapper/**.xml

3. Create the userdao.xml file in mapper


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.StationMapper">
    <select id="getStationById" parameterType="Long" resultType="com.example.demo.entity.TStation">
        SELECT * FROM TBL_USER WHERE id = #{id}
    </select>
</mapper>
Copy the code