spring boot 整合mybatis和jpa遇到的那些坑，附代码

这两天自己练习搭建项目，其中需要用到springSecurity,JWT搞到一半发现数据库还没有配置，然后总结了下，决定用mybatis和jpa，进入正题

一、第一步，引入我们需要的jar包，这里我直接yongidea创建项目时自动生成了一些需要的jar包，暂时先弄这些，以后再加

pom.xml

<?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 http://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.1.5.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>jpa-mybatis</artifactId> <version>0.0.1-SNAPSHOT</version> <name>jpa-mybatis</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-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-security</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.security</groupId> <artifactId>spring-security-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>

二、第二步配置数据库信息，我习惯于使用.yml来配置，后面也附.properties文件

.yml文件配置

spring: #数据库配置 datasource: driver-class-name: com.mysql.cj.jdbc.Driver password: root username: root url: jdbc:mysql://127.0.0.1/oa?characterEncodeing=utf-8&useSSL=false&serverTimezone=GMT+8 # spring模板引擎thymeleaf配置 thymeleaf: cache: false prefix: classpath:/templates/ suffix: .html encoding: UTF-8 mode: HTML5 # jpa配置 jpa: show-sql: true # mybatis配置 mybatis: mapper-locations: classpath:mybatis/*.xml #端口号，这里我使用80端口，80是浏览器访问的默认端口，输入127.0.0.1就直接可以访问网站了 server: port: 80

.properties文件配置

#数据库配置 spring.datasource.url=jdbc:mysql://127.0.0.1/oa?characterEncodeing=utf-8&useSSL=false&serverTimezone=GMT+8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver # spring模板引擎thymeleaf配置 spring.thymeleaf.mode= HTML5 spring.thymeleaf.cache=false spring.thymeleaf.prefix = classpath:/templates/ spring.thymeleaf.suffix=.html spring.thymeleaf.encoding=UTF-8 # jpa配置 spring.jpa.show-sql=true # mybatis配置 mybatis.mapper-locations=classpath:mybatis/*.xml #端口号，这里我使用80端口，80是浏览器访问的默认端口，输入127.0.0.1就直接可以访问网站了 server.port=80

三、第三步创建包和类，我的项目结构下边显示，简单的弄了下，这里需要注意：mybatis的map和jpa的接口不能写到一起，dao文件夹下的，我分成两个文件夹来分别存放，否则会抛异常，这块纠结了好久

四、创建mybatis的mapper.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"> <!-- namespace必须指向Dao接口 --> <mapper namespace="com.example.jpamybatis.dao.mapper.UserMapperDao"> <select id="getUser" parameterType="map" resultType="map"> select * from user </select> </mapper>

五、更改springboot的启动类,这里注意：mybatis需要指定mapper的具体位置，或者在mapper接口里添加@Mapper注解

package com.example.jpamybatis; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @MapperScan({"com.example.jpamybatis.dao.mapper"})//指定mapper所在目录，或者在mapper上添加@Mapper注解 @SpringBootApplication public class JpaMybatisApplication { public static void main(String[] args) { SpringApplication.run(JpaMybatisApplication.class, args); } }

六、新建mapper文件

package com.example.jpamybatis.dao.mapper; import org.apache.ibatis.annotations.Mapper; import java.util.List; import java.util.Map; //@Mapper //因为在启动类上添加了@MapperScan({"com.example.jpamybatis.dao.mapper"})注解，这里可以注掉 public interface UserMapperDao { List<Map<String,Object>> getUser();//此方法对应xml文件里的id }

七、新建测试方法，idea快捷键可以快速创建,点击test直接ok就好了

package com.example.jpamybatis.dao.mapper; import com.example.jpamybatis.dao.jpa.UserJpaDao; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import static org.junit.Assert.*; @SpringBootTest @RunWith(SpringRunner.class) public class UserMapperDaoTest { @Autowired //注入mapper，如果下面抛红线在mapper里添加@Repository注解 private UserMapperDao userMapperDao; @Autowired private UserJpaDao userJpaDao; @Test public void test() { System.out.println(userMapperDao.getUser()); } }

测试通过

接下来搞jpa的配置，jpa就简单多了

创建一个与数据库对应的bean属性

package com.example.jpamybatis.bean; import lombok.Data; import org.hibernate.annotations.DynamicUpdate; import org.springframework.beans.factory.annotation.Autowired; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; @Entity//jpa的注解，想具体的百度一下 @DynamicUpdate @Data//lombok生成get和set方法的注解 public class User { //用户id @Id //ID更新策略 @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer userId; //用户姓名 private String userName; //用户密码 private String password; //用户电话 private String phone; //用户头像 private String icon; //用户权限1为管理员0为普通用户 private Integer admin; //经纪人姓名 private String name; }

dao层

package com.example.jpamybatis.dao.jpa; import com.example.jpamybatis.bean.User; import org.springframework.data.jpa.repository.JpaRepository; //使用jpa就先在dao、继承JpaRepository，User为实体类，Integer为id主键的类型 public interface UserJpaDao extends JpaRepository<User,Integer> { }

然后，直接测试，哈哈

package com.example.jpamybatis.dao.mapper; import com.example.jpamybatis.dao.jpa.UserJpaDao; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import static org.junit.Assert.*; @SpringBootTest @RunWith(SpringRunner.class) public class UserMapperDaoTest { @Autowired //注入mapper，如果下面抛红线在mapper里添加@Repository注解 private UserMapperDao userMapperDao; @Autowired private UserJpaDao userJpaDao; @Test public void test() { System.out.println(userMapperDao.getUser()); } @Test public void test2(){ System.out.println(userJpaDao.findAll()); } }

有对jpa还不了解的同学，可以先看看如何使用jpa，后附.sql文件

INSERT INTO `user` VALUES (1, 'lukai', 'e10adc3949ba59abbe56e057f20f883e', '111111', NULL, '1', '路凯'); INSERT INTO `user` VALUES (2, 'admin', 'e10adc3949ba59abbe56e057f20f883e', '111111', NULL, '0', '管理员'); INSERT INTO `user` VALUES (3, 'ltx', 'd8e2b4b7b5846e2b5a28f37f03ea3ca7', '111111', NULL, '1', '李天祥'); INSERT INTO `user` VALUES (4, 'ysm', '25f9e794323b453885f5181f1b624d0b', '111111', NULL, '1', '燕世民'); INSERT INTO `user` VALUES (5, 'kzh', '202cb962ac59075b964b07152d234b70', '111111', NULL, '0', '康志豪'); INSERT INTO `user` VALUES (6, 'cxc', 'e10adc3949ba59abbe56e057f20f883e', '111111', NULL, '0', '成燮城'); INSERT INTO `user` VALUES (7, 'ly', 'a4d2f0d23dcc84ce983ff9157f8b7f88', '111111', NULL, '0', '刘阳'); INSERT INTO `user` VALUES (8, 'ysl', '202cb962ac59075b964b07152d234b70', '111111', NULL, '0', '燕世亮'); INSERT INTO `user` VALUES (9, 'lyj', '6403900e50926ad7ee1c2302a1c84285', '111111', NULL, '0', '刘永杰'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (1, 'lukai', 'e10adc3949ba59abbe56e057f20f883e', '111111', NULL, '1', '1'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (2, 'admin', 'e10adc3949ba59abbe56e057f20f883e', '111111', NULL, '0', '管理员'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (3, 'ltx', 'd8e2b4b7b5846e2b5a28f37f03ea3ca7', '111111', NULL, '1', '2'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (4, 'ysm', '25f9e794323b453885f5181f1b624d0b', '111111', NULL, '1', '3'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (5, 'kzh', '202cb962ac59075b964b07152d234b70', '111111', NULL, '0', '康..'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (6, 'cxc', 'e10adc3949ba59abbe56e057f20f883e', '111111', NULL, '0', '成'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (7, 'ly', 'a4d2f0d23dcc84ce983ff9157f8b7f88', '111111', NULL, '0', '刘'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (8, 'ysl', '202cb962ac59075b964b07152d234b70', '111111', NULL, '0', '燕'); INSERT INTO `user`(`user_id`, `user_name`, `password`, `phone`, `icon`, `admin`, `name`) VALUES (9, 'lyj', '6403900e50926ad7ee1c2302a1c84285', '111111', NULL, '0', '刘永杰');