一、简介
Mybatis-Plus（以下简称MBP）的初衷是为了简化开发，而不建议开发者自己写SQL语句的；但是有时客户需求比较复杂，仅使用MBP提供的Service，Mapper与Wrapper进行组合，难以实现可以需求； 这时我们就要用到自定义的SQL了。
MBP实现自定义的方式有以下两种：
1.使用Wrapper中提供的自定义SQL函数；（推荐）
2.Annotation注解方式；（推荐）
3.XML配置文件方式；（不推荐）
二、使用方法
用Wrapper
1.使用Wrapper基类，自定义Where后面的SQL语句；
-
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)
-
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)
例：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)
例：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.使用QueryWrapper
-
select | select(String... sqlSelect)
例select("id", "name", "age")
3.使用UpdateWrapper
-
set | set(boolean condition, String column, Object val)
例：set("name", "老李头")；
-
setSql | setSql("name = '老李头'")
setSql("name = '老李头'")
Annotation注解方式
我们可以在Mapper的继承类中，使用完整的自定义SQL；
1.查询
@Select("select * from t_station where no = #{paramNo}" and type = #{paramType}")
List<TStation> getStationByNo(String paramNo ,String paramType);
2.更新
@Update("update t_station set no = #{paramNo} where id = #{paramId}")
int updateStationByid(@Param("paramNo" no,@Param("ParamId") id)
3.插入
@Insert("insert into t_station (no,name) values (#{no},#{name})");
Int insert(TStation station)
4.删除
@Delete("Delete from t_station where no = #{no}")
int deleteByNo(String no)
XML配置文件方式
1.在Mapper的继承类中定义函数：
public interface StationMapper extends BaseMapper<User> {
public User getStationById(Long id);
}
2.在application.yml文件中指定mapper文件的位置
mybatis-plus:
mapper-locations: classpath:/mapper/**.xml
注：如果是放在resource目录 classpath:/mapper/**.xml
3.在mapper下创建文件UserDao.xml
<?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.example.demo.dao.StationMapper">
<select id="getStationById" parameterType="Long" resultType="com.example.demo.entity.TStation">
SELECT * FROM TBL_USER WHERE id = #{id}
</select>
</mapper>
