springboot整合Mybatisplus且用xml自定义sql

项目结构

依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.1</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.xc</groupId>
<artifactId>boot-mybatis-plus</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>boot-mybatis-plus</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<!-- <dependency>-->
<!-- <groupId>org.mybatis.spring.boot</groupId>-->
<!-- <artifactId>mybatis-spring-boot-starter</artifactId>-->
<!-- <version>2.1.4</version>-->
<!-- </dependency>-->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>


application.yaml

spring:
# rest风格的请求
mvc:
hiddenmethod:
filter:
enabled: true
datasource:
url: jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
# 3秒没查出来超时
jdbc:
template:
query-timeout: 3


mybatis-plus 实例

1、表的实体类

@Data
public class myUser {
private Long id;
private String name;
private Integer age;
private String email;
}


2、mapper包

@Mapper
public interface UserMapper extends BaseMapper<User> {
}


3、service

public interface UserService {
}


@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}


ctrl + F12 显示封装的sql方法

controller

package com.xc.bootmybatisplus.controller;
import com.xc.bootmybatisplus.bean.Account;
import com.xc.bootmybatisplus.bean.User;
import com.xc.bootmybatisplus.service.imp.UserServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
public class Controller {
@Autowired
UserServiceImpl userService;
// mybatis-puls ===========================
@ResponseBody
@GetMapping("/getAll")
public List<User> getAll(){
List<User> list = userService.list();
return list;
}
@ResponseBody
@GetMapping("/getById/id={id}")
public Object getById(@PathVariable("id") Integer id){
System.out.println("id = " + id);
Object o = userService.getById(id);
return o ;
}
}


主程序类


@SpringBootApplication
@MapperScan("com.xc.bootmybatisplus.mapper")
public class BootMybatisPlusApplication {
public static void main(String[] args) {
SpringApplication.run(BootMybatisPlusApplication.class, args);
}
}


=====================自定义sql ===========================

controller 中注入service
service 中注入 mapper
mapper 和数据表 和xml文件有关

MP+XML

实体类、yaml 依赖不变

实体类

@Data
public class myUser {
private Long id;
private String name;
private Integer age;
private String email;
}


mapper

@Mapper
public interface UserMapper extends BaseMapper<User> {
// 自定义方法
public List<User> seAll();
}


service

public interface UserService {
}


@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
// 注入自定义的方法
@Autowired
UserMapper userMapper;
// 查询所有
public List<User> seAll(){
return userMapper.seAll();
}
}


controller

package com.xc.bootmybatisplus.controller;
import com.xc.bootmybatisplus.bean.Account;
import com.xc.bootmybatisplus.bean.User;
import com.xc.bootmybatisplus.service.imp.UserServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
public class Controller {
@Autowired
UserServiceImpl userService;
// xml文件自定义sql
@ResponseBody
@GetMapping("/seAll")
public List<User> seAll(){
return userService.seAll();
}
}


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.xc.bootmybatisplus.mapper.UserMapper">
<!--public Account getAcct(Long id);-->
<select id="seAll" resultType="com.xc.bootmybatisplus.bean.User">
select * from user
</select>
</mapper>


========================

自定义结果集

resultMap

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
private String name;
private int age;
}


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.xc.bootmybatisplus.mapper.UserMapper">
<resultMap id="seAllMap" type="com.xc.bootmybatisplus.resultMap.User">
<result property="name" column="name"/>
<result property="age" column="age"/>
</resultMap>
<select id="seAll" resultMap="seAllMap">
select * from user where
</select>
</mapper>


查询表的字段名和类型

controller

@ResponseBody
@RequestMapping("/getC")
public List<UserColumns> getColumns(){
return userService.getColumns();
}


service

// 查询字段和类型
public List<UserColumns> getColumns(){
return userMapper.getColumns();
}


mapper

List<UserColumns> getColumns();


xml

<select id="getColumns" resultType="com.xc.mybatisplus3boot.pojo.UserColumns">
select
COLUMN_NAME columns,
DATA_TYPE type
from information_schema.COLUMNS where table_name = 'user' and table_schema = 'school';
</select>


返回参数类

@Data
public class UserColumns {
private String columns;
private String type;
}


注意：使用自定义返回的参数时，xml中查询的字段 要和 定义的参数字段一致，起个别名

查找数据库所有表名

controller

/**
* 获取school数据库的所有表名
*/
@ResponseBody
@RequestMapping("/getTableName")
public List<String> getTableName(){
return userService.getTableName();
}


service

// 获取数据库额表名
public List<String> getTableName(){
return userMapper.getTableName();
}


mapper

// 获取school数据库的的所有表
List<String> getTableName();


xml

<select id="getTableName" resultType="string">
show tables from school
</select>


注意： resultType=“string” 代表的是在List 中存放string 数据