SpringBoot整合mybatis实现mapper.xml方式访问数据库
一、项目架构
二、代码
## pom文件
<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.yuyou</groupId>
<artifactId>springboot-mybatis-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.1.RELEASE</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.11</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
</project>
##mapper层
---------------------mapper接口-----------------------
package com.yuyou.demo.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.yuyou.demo.po.User;
public interface UserMapper {
List<User> getUserList(User user);
}
---------------------mapper接口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.yuyou.demo.mapper.UserMapper" >
<select id="getUserList" resultType="User">
select *
from t_user
<where>
<if test="type != null">
and type = #{type}
</if>
<if test ="age != null">
and age > #{age}
</if>
</where>
</select>
</mapper
##service层
---------------------service接口-----------
import java.util.List;
import com.yuyou.demo.po.User;
public interface UserService {
List<User> selectUser();
}
---------------------service接口实现-----------
package com.yuyou.demo.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.yuyou.demo.mapper.UserMapper;
import com.yuyou.demo.po.User;
import com.yuyou.demo.service.UserService;
@Service
public class UserServiceImpl implements UserService{
@Autowired
private UserMapper userMapper;
@Override
public List<User> selectUser() {
User user = new User();
user.setType(1);
user.setAge(16);
return userMapper.getUserList(user);
}
}
##controller层
package com.yuyou.demo.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import com.yuyou.demo.po.User;
import com.yuyou.demo.service.UserService;
@RestController
public class IndexController {
@Autowired
private UserService userService;
@GetMapping("/getUser")
public List<User> getUser(){
return userService.selectUser();
}
}
## 配置文件application.yml
server:
port: 8080
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://ip_address:port/db_name?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true
username: username
password: password
mybatis:
mapper-locations: classpath:mapper/**/*.xml
type-aliases-package: com.yuyou.demo.po
##启动类
package com.yuyou.demo;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.yuyou.demo.mapper")
public class StartUp {
public static void main(String[] args) {
SpringApplication.run(StartUp.class, args);
}
}
三、注意事项
一、可以在每个mapper接口上添加@Mapper注解,这样启动类就不需要@MapperScan
二、mapper.xml文件中写sql语句注意点
1、where 1==1 and col = '#' 和 <where><if test="col != null"> and col = '#'</if></where>
2、再进行数值比较时,>可以直接使用,<必须转义<,否者编译异常
三、异常org.apache.ibatis.binding.BindingException: Invalid bound statement (not found):
1、配置文件mybatis.mapper-locations配置有误
2、检查xml文件所在package名称是否和Mapper interface所在的包名一一对应
3、检查xml的namespace是否和xml文件的package名称一一对应
4、检查方法名称是否对应
5、去除xml文件中的中文注释