## Mybatis Plus中使用自定义的sql语句
Mybatis Plus中在xml文件中手写sql语句,这个如同在Mybatis中写sql语句一样。但是由于Mybatis Plus进行了封装所以使用起来的配置稍微有所不同,一不小心容易踩坑:
* 首先确定Mybatis Plus的依赖:
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
XML文件采用Mybatis Plus 推荐的放置位置,放在mapper包映射类下面的xml包下面,
接着需要在pom.xml文件中配置一下映射路径否则会报加载不到sql语句的异常:
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
<resources>
<!--不添加这句话就无法使用 xml编写sql语句-->
<resource>
<directory>src/main/resources</directory>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
xml映射文件所在路径如图所示:
启动类配置包扫描:
@MapperScan("com.gec.hawaste.mapper")
@SpringBootApplication
public class HawasteApplication {
public static void main(String[] args) {
SpringApplication.run(HawasteApplication.class, args);
}
}
接下来在yaml文件中配置mapper.xml文件的所在路径:
#此为默认值 表示删除的状态 配置可有可无
mybatis-plus:
global-config:
db-config:
logic-not-delete-value: 0
# Mybatis plus中使用sql语句 需要添加pom文件 和这里加上xml文件的路径声明
mapper-locations: classpath*:/com/gec/hawaste/mapper/xml/*.xml
编写mapper接口:
IPage<QualificationDo> selectByCondition(IPage<QualificationDo> iPage,QualificationDo qualificationDo);
在xml映射文件中编写sql语句:
<select id="selectByCondition" parameterType="com.gec.hawaste.domain.QualificationDo" resultType="com.gec.hawaste.domain.QualificationDo">
SELECT
qu.*,
su.NAME user_name,
cu.NAME check_user_name
FROM
qualification qu
LEFT JOIN sys_user su ON qu.upload_user_id = su.id
LEFT JOIN sys_user cu ON qu.check_user_id = cu.id
WHERE
qu.del_flag = 0
AND qu.type = #{qualificationDo.type}
AND qu.CHECK = #{qualificationDo.check}
<![CDATA[ AND DATE(qu.create_date) >= #{qualificationDo.startDate}
AND DATE(qu.create_date) <= #{qualificationDo.endDate} ]]>
</select>
<![CDATA[ AND DATE(qu.create_date) >= #{qualificationDo.startDate}
AND DATE(qu.create_date) <= #{qualificationDo.endDate} ]]>
是为了解决xml文件中有无法直接使用 > <的问题
在单元测试类中使用测试:
运行测试结果:
PageInfo(navigatePages=0, navigatepageNums=null, navigateFirstPage=0, navigateLastPage=0, prePage=0, nextPage=0, isFirstPage=false, isLastPage=false, hasPreviousPage=false, hasNextPage=false)
QualificationDo(super=Qualification(id=1, uploadUserId=2, type=3, address=/uploads/56/0B0DF1F02F804F048A623804EFB88741.png, check=0, description=null, checkUserId=null, createDate=2019-10-10T14:29:12, updateDate=2019-10-10T14:29:12, delFlag=0, createBy=2,超级管理员), userName=超级管理员, checkUserName=null, startDate=null, endDate=null)