搜索到
15
篇与
的结果
-
mybatis获取自增主键 我们在进行数据库设计时，经常使用自增主键的设计（(⊙o⊙)…，其实，我本人不太喜欢这种设计，我更喜欢使用UUID的方式）。对于自增主键，我们在进行插入时，一般不会指定具体的主键，而是让数据库自己生成一个主键。问题分析这样就会出现一个问题，比如，我们有一个学生表（student），有一个课程表（course），其中在课程表里面有一个外键（student_id），用户关联学生表。如果我们在插入学生表的同时，希望同时插入课程表，那么就会产生一个问题，在插入初始化学生实体时，我们没有设置主键值，导致插入课程表时，获取不到当前学生实体的主键。mybatis问题解决预制表我们先创建两张表。学生表（student)CREATE TABLE `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;课程表（course）CREATE TABLE `course` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `course_name` varchar(255) DEFAULT NULL, `student_id` bigint(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `course_ibfk_1` (`student_id`), CONSTRAINT `course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;预制实体Student.java/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ @Data public class Student { private Long id; private String name; }Course.java/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ @Data public class Course { private Long id; private String courseName; private Long studentId; }mapperStudentMapper.java/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ public interface StudentMapper { int deleteByPrimaryKey(Long id); int insert(Student record); int insertSelective(Student record); Student selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Student record); int updateByPrimaryKey(Student record); }CourseMapper.java/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ public interface CourseMapper { int deleteByPrimaryKey(Long id); int insert(Course record); int insertSelective(Course record); Course selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Course record); int updateByPrimaryKey(Course record); }/** * @author laughing * @date 2020/10/26 * @site https://www.xiangcaowuyu.net */ public interface CourseMapper { int deleteByPrimaryKey(Long id); int insert(Course record); int insertSelective(Course record); Course selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Course record); int updateByPrimaryKey(Course record); }xmlStudentMapper.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="net.xiangcaowuyu.mybatis.mapper.StudentMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.Student"> <!--@mbg.generated--> <!--@Table student--> <id column="id" jdbcType="BIGINT" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <sql id="Base_Column_List"> <!--@mbg.generated--> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> <!--@mbg.generated--> select <include refid="Base_Column_List" /> from student where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> <!--@mbg.generated--> delete from student where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.mybatis.entity.Student"> <!--@mbg.generated--> update student <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.mybatis.entity.Student"> <!--@mbg.generated--> update student set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>CourseMapper.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="net.xiangcaowuyu.mybatis.mapper.CourseMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.Course"> <!--@mbg.generated--> <!--@Table course--> <id column="id" jdbcType="BIGINT" property="id" /> <result column="course_name" jdbcType="VARCHAR" property="courseName" /> <result column="student_id" jdbcType="BIGINT" property="studentId" /> </resultMap> <sql id="Base_Column_List"> <!--@mbg.generated--> id, course_name, student_id </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> <!--@mbg.generated--> select <include refid="Base_Column_List" /> from course where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> <!--@mbg.generated--> delete from course where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Course" useGeneratedKeys="true"> <!--@mbg.generated--> insert into course (course_name, student_id) values (#{courseName,jdbcType=VARCHAR}, #{studentId,jdbcType=BIGINT}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Course" useGeneratedKeys="true"> <!--@mbg.generated--> insert into course <trim prefix="(" suffix=")" suffixOverrides=","> <if test="courseName != null"> course_name, </if> <if test="studentId != null"> student_id, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="courseName != null"> #{courseName,jdbcType=VARCHAR}, </if> <if test="studentId != null"> #{studentId,jdbcType=BIGINT}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.mybatis.entity.Course"> <!--@mbg.generated--> update course <set> <if test="courseName != null"> course_name = #{courseName,jdbcType=VARCHAR}, </if> <if test="studentId != null"> student_id = #{studentId,jdbcType=BIGINT}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.mybatis.entity.Course"> <!--@mbg.generated--> update course set course_name = #{courseName,jdbcType=VARCHAR}, student_id = #{studentId,jdbcType=BIGINT} where id = #{id,jdbcType=BIGINT} </update> </mapper>我们看一下插入语句<insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student (`name`) values (#{name,jdbcType=VARCHAR}) </insert>能够获取主键的关键在于keyColumn="id" keyProperty="id" useGeneratedKeys="true"其中keyColumn用于指定主键对应数据库的列，keyProperty对应实体的主键字段，useGeneratedKeys代表使用生成的主键。服务层我们看一下服务层的关键代码@Resource private StudentMapper studentMapper; @Resource private CourseMapper courseMapper; @Override public int insert(Student student) { studentMapper.insert(student); Course course = new Course(); course.setStudentId(student.getId()); course.setCourseName("测试"); courseMapper.insert(course); return 1; }调用服务层@Resource StudentService studentService; @RequestMapping("/student/insert") public String insertStudent() { Student student = new Student(); student.setName("张三"); studentService.insert(student); return "success"; }我们插入student时，没有指定student的id字段，在调用insert方法后，mybatis会自动将生成的主键，赋值给id字段，所以我们在插入course表时，就能获取到student的主键。 -
Spring Boot Mybatis一对一、一对多查询 数据库查询时，我们用的比较多的一般是一对一查询还有一对多查询。所谓一对一查询，就是查询对象关联的对象是唯一的，比如图书与作者，一般情况下，一个图书只有一个作者，这就是一对一查询。一对多查询，是指一个查询对象，关联多个对象，比如权限系统中，用户与角色的对应，一个用户可能属于一个角色，也可能属于多个角色，这就是一对多。下面我们针对介绍一下在mybatis中如何实现一对一及一对多的查询。创建项目并添加依赖这块没什么好说的，主要添加依赖如下： <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.1.3</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>配置控制台输出sql为了查询sql输出信息，我们配置一下输出日志信息，让sql语句在控制台输出spring: datasource: url: jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: root server: port: 8888 compression: enabled: true mybatis: type-aliases-package: net.xiangcaowuyu.mybatis.entity mapper-locations: classpath:mapper/*.xml configuration: cache-enabled: true logging: level: net.xiangcaowuyu.mybatis.mapper: DEBUG1.一对一查询这里，我们以图书与作者的对应关系为例，约定一个图书只能有一个作者。1.1 数据库表图书对应数据库表为book，作者对应数据库表为author，创建表及预制表数据sql如下：1.1.1 book表/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 01:32:16 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for book -- ---------------------------- DROP TABLE IF EXISTS `book`; CREATE TABLE `book` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `aid` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of book -- ---------------------------- INSERT INTO `book` VALUES (1, '《射雕英雄传》', 1); SET FOREIGN_KEY_CHECKS = 1; 1.1.2 author表/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 01:32:38 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for author -- ---------------------------- DROP TABLE IF EXISTS `author`; CREATE TABLE `author` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of author -- ---------------------------- INSERT INTO `author` VALUES (1, '张三', 35); SET FOREIGN_KEY_CHECKS = 1;1.2 创建实体1.2.1 Author.java/** * author * @author laughing */ @Data public class Author implements Serializable { private Integer id; private String name; private Integer age; private static final long serialVersionUID = 1L; }1.2.2 Book.java因为是图书（book）关联作者（author），所以我们需要在图书（book）实体类中，加入作者（author）属性，用于sql查询映射。/** * book * @author laughing */ @Data public class Book implements Serializable { private Integer id; private String name; private Author author; private static final long serialVersionUID = 1L; }1.3 创建mapper1.3.1 BookMapper.javapublic interface BookMapper { /** * 查询book * * @param id 主键 * @return book */ Book selectByPrimaryKey(Integer id); }1.4 创建xml一对一关联查询的关键在于association关键字，这个节点中的内容，和 resultMap 一样，也是 id，result 等，在这个节点中，我们还可以继续描述一对一。由于在实际项目中，每次返回的数据类型可能都会有差异，这就需要定义多个 resultMap，而这多个 resultMap 中，又有一部份属性是相同的，所以，我们可以将相同的部分抽出来，做成一个公共的模板，然后通过extends关键字，继承公共的模板。<?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="net.xiangcaowuyu.mybatis.mapper.BookMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.Book"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <resultMap id="BookWithAuthor" type="net.xiangcaowuyu.mybatis.entity.Book" extends="BaseResultMap"> <association property="author" javaType="net.xiangcaowuyu.mybatis.entity.Author"> <id column="id" property="id" jdbcType="INTEGER" javaType="java.lang.Integer"/> <id column="name" property="name"/> <id column="age" property="age"/> </association> </resultMap> <sql id="Base_Column_List"> id, `name`, aid </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BookWithAuthor"> select <include refid="Base_Column_List" /> from book where id = #{id,jdbcType=INTEGER} </select> </mapper>1.5 创建服务层为了节省篇幅，我们这里就省略掉接口代码，具体服务层代码如下：/** * @author laughing * @date 2020-10-16 */ @Service public class BookServiceImpl implements BookService { @Resource BookMapper bookMapper; /** * 查询book * * @param id 主键 * @return book */ @Override public Book selectByPrimaryKey(Integer id) { return bookMapper.selectByPrimaryKey(id); } }1.6 创建controller创建一个rest接口，测试我们的查询/** * @author laughing * @date 2020-10-16 */ @RestController @RequestMapping("book") public class BookController { private final BookService bookService; public BookController(BookService bookService){ this.bookService=bookService; } /** * 查询book * * @param id 主键 * @return book */ @GetMapping("selectByPrimaryKey/{id}") public Book selectByPrimaryKey(@PathVariable("id") Integer id) { return bookService.selectByPrimaryKey(id); } }2.一对多查询一对多查询与一对一查询类似，区别主要在于实体的关联关系及xml文件的配置。我们以权限系统常见的用户、角色对应关系为例。约定一个用户可以关联多个角色。2.1 数据库表我们数据库有三张表，user用户存储用户信息，role用于存储角色信息，user_role用于存储用户与角色的关联关系，相关表结构及数据如下/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 02:02:01 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for role -- ---------------------------- DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `nameZh` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of role -- ---------------------------- INSERT INTO `role` VALUES (1, 'dba', '数据库管理员'); INSERT INTO `role` VALUES (2, 'admin', '系统管理员'); INSERT INTO `role` VALUES (3, 'user', '用户'); SET FOREIGN_KEY_CHECKS = 1; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `enabled` tinyint(1) NULL DEFAULT NULL, `locked` tinyint(1) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (1, 'root', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); INSERT INTO `user` VALUES (2, 'admin', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); INSERT INTO `user` VALUES (3, 'sang', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); SET FOREIGN_KEY_CHECKS = 1; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user_role -- ---------------------------- DROP TABLE IF EXISTS `user_role`; CREATE TABLE `user_role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NULL DEFAULT NULL, `rid` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user_role -- ---------------------------- INSERT INTO `user_role` VALUES (1, 1, 1); INSERT INTO `user_role` VALUES (2, 1, 2); INSERT INTO `user_role` VALUES (3, 2, 2); INSERT INTO `user_role` VALUES (4, 3, 3); SET FOREIGN_KEY_CHECKS = 1;1.2 创建实体1.2.1 User.java/** * user * * @author laughing */ @Data @JsonIgnoreProperties(value = { "handler"}) public class User implements Serializable { private Integer id; private String username; private String password; private Boolean enabled; private Boolean locked; private List<Role> roleList; private static final long serialVersionUID = 1L; }1.2.2 Role.java/** * role * @author laughing */ @Data @JsonIgnoreProperties(value = { "handler"}) public class Role implements Serializable { private Integer id; private String name; private String namezh; private static final long serialVersionUID = 1L; }1.3 创建mapperpublic interface UserMapper { /** * 根据Id查找 * @param id * @return */ User selectByPrimaryKey(Integer id); /** * 根据用户查找角色列表 * @param uid * @return */ List<Role> getRolesByUid(@Param("uid") Integer uid); }1.4 创建xml一对一的查询通过association，一对多的查询与此类似，只是通过collection关键字，替换association关键字。1.4.1 方式1<?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="net.xiangcaowuyu.mybatis.mapper.UserMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.User"> <id column="id" jdbcType="INTEGER" property="id"/> <result column="username" jdbcType="VARCHAR" property="username"/> <result column="password" jdbcType="VARCHAR" property="password"/> <result column="enabled" jdbcType="BOOLEAN" property="enabled"/> <result column="locked" jdbcType="BOOLEAN" property="locked"/> <collection property="roleList" ofType="net.xiangcaowuyu.mybatis.entity.Role"> <result column="rid" property="id" javaType="java.lang.Integer"/> <result column="rname" property="name" javaType="java.lang.String"/> <result column="rnameZh" property="namezh" javaType="java.lang.String"/> </collection> </resultMap> <sql id="Base_Column_List"> id, username, `password`, enabled, locked </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select user.*,role.id as rid,role.name as rname,role.nameZh as rnameZh from user,role,user_role where user.id = user_role.uid and role.id = user_role.rid and user.id = #{id,jdbcType=INTEGER} </select> </mapper>1.4.2 懒加载方式通过fetchType="lazy"实现懒加载<?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="net.xiangcaowuyu.mybatis.mapper.UserMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.mybatis.entity.User"> <id column="id" jdbcType="INTEGER" property="id"/> <result column="username" jdbcType="VARCHAR" property="username"/> <result column="password" jdbcType="VARCHAR" property="password"/> <result column="enabled" jdbcType="BOOLEAN" property="enabled"/> <result column="locked" jdbcType="BOOLEAN" property="locked"/> <collection property="roleList" ofType="net.xiangcaowuyu.mybatis.entity.Role" column="id" select="getRolesByUid" fetchType="lazy"> </collection> </resultMap> <sql id="Base_Column_List"> id, username, `password`, enabled, locked </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from user where user.id = #{id,jdbcType=INTEGER} </select> <select id="getRolesByUid" resultType="net.xiangcaowuyu.mybatis.entity.Role"> SELECT r.* FROM role r,user_role ur WHERE r.`id`=ur.`rid` AND ur.`uid`=#{uid} </select> </mapper>1.5 创建服务层服务层我们依然省略掉接口代码/** * @author laughing * @date 2020-10-17 */ @Service public class UserServiceImpl implements UserService { @Resource UserMapper userMapper; /** * 根据Id查找 * * @param id * @return */ @Override public User selectByPrimaryKey(Integer id) { return userMapper.selectByPrimaryKey(id); } }1.6 创建controller创建rest接口，测试代码/** * @author laughing * @date 2020-10-17 */ @RestController @RequestMapping("user") public class UserController { private final UserService userService; public UserController(UserService userService){ this.userService=userService; } @RequestMapping("selectByPrimaryKey/{id}") public User selectByPrimaryKey(@PathVariable("id") Integer id) { return userService.selectByPrimaryKey(id); } }
-
Spring boot整合mybatis多数据源简单使用 日常开发中，我们很少会在一个应用程序中同时使用多个数据源。但是，如果涉及一些数据迁移等应用，可能会涉及将数据从一个库迁移到另外一个库，甚至是不同类型的数据库，比如MySQL到Oracle。这篇博文，我们不介绍mybatis的基本使用，只介绍基于mybatis配置多数据源的方法。数据源准备我这里用了本地MySQL两个库，分别是mybatisone和mybatistwo,mybatisone库中有一张userone表，mybatistwo库中有一张usertwo表，建表sql如下：userone/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : mybatisone Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 01/10/2020 00:34:29 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for userone -- ---------------------------- DROP TABLE IF EXISTS `userone`; CREATE TABLE `userone` ( `id` bigint(0) NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of userone -- ---------------------------- INSERT INTO `userone` VALUES (1, '姓名1'); SET FOREIGN_KEY_CHECKS = 1; usertwo/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : mybatistwo Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 01/10/2020 00:34:35 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for usertwo -- ---------------------------- DROP TABLE IF EXISTS `usertwo`; CREATE TABLE `usertwo` ( `id` bigint(0) NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of usertwo -- ---------------------------- INSERT INTO `usertwo` VALUES (1, '姓名2'); SET FOREIGN_KEY_CHECKS = 1; 添加依赖主要依赖如下 <!--mybatis依赖--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!--MySQL驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!--druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.14</version> </dependency>application.yaml配置文件yaml文件配置数据源信息以及mybatis的配置信息spring: datasource: mybatisone: url: jdbc:mysql://localhost:3306/mybatisone?chartset=utf8mb4&serverTimezone=UTC&usessl=false username: root password: root type: com.alibaba.druid.pool.DruidDataSource mybatistwo: url: jdbc:mysql://localhost:3306/mybatistwo?chartset=utf8mb4&serverTimezone=UTC&usessl=false username: root password: root type: com.alibaba.druid.pool.DruidDataSource mybatis: mapper-locations: classpath*:mapper/*.xml type-aliases-package: net.xiangcaowuyu.simplemybatis.entity配置druid根据yaml文件提供的DataSource，分别配置对应的两个数据源。DataSourceConfig.java/** * datasource配置文件 * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @Configuration public class DataSourceConfig { /** * * @return 第一个数据源 */ @Bean @ConfigurationProperties(prefix = "spring.datasource.mybatisone") DataSource dataSourceOne(){ return DruidDataSourceBuilder.create().build(); } /** * * @return 第二个数据源 */ @Bean @ConfigurationProperties(prefix = "spring.datasource.mybatistwo") DataSource dataSourceTwo(){ return DruidDataSourceBuilder.create().build(); } }配置mybatis数据源MyBatisConfigOne.java/** * mybatis第一个配置文件 * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @Configuration @MapperScan(basePackages = {"net.xiangcaowuyu.simplemybatis.mapper.one"},sqlSessionFactoryRef = "sqlSessionFactoryOne",sqlSessionTemplateRef = "sqlSessionTemplateOne") public class MyBatisConfigOne { private final Logger logger = LoggerFactory.getLogger(MyBatisConfigOne.class); @Resource(name = "dataSourceOne") DataSource dataSourceOne; @Bean SqlSessionFactory sqlSessionFactoryOne() { SqlSessionFactory sqlSessionFactory = null; try { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml")); sqlSessionFactoryBean.setDataSource(dataSourceOne); sqlSessionFactory = sqlSessionFactoryBean.getObject(); }catch (Exception ex){ logger.error(ex.getMessage()); } return sqlSessionFactory; } @Bean SqlSessionTemplate sqlSessionTemplateOne(){ return new SqlSessionTemplate(sqlSessionFactoryOne()); } }MyBatisConfigTwo.java/** * mybatis第二个配置文件 * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @Configuration @MapperScan(basePackages = {"net.xiangcaowuyu.simplemybatis.mapper.two"},sqlSessionFactoryRef = "sqlSessionFactoryTwo",sqlSessionTemplateRef = "sqlSessionTemplateTwo") public class MyBatisConfigTwo { private final Logger logger = LoggerFactory.getLogger(MyBatisConfigTwo.class); @Resource(name = "dataSourceTwo") DataSource dataSourceTwo; @Bean SqlSessionFactory sqlSessionFactoryTwo() { SqlSessionFactory sqlSessionFactory = null; try { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/two/*.xml")); sqlSessionFactoryBean.setDataSource(dataSourceTwo); sqlSessionFactory = sqlSessionFactoryBean.getObject(); }catch (Exception ex){ logger.error(ex.getMessage()); } return sqlSessionFactory; } @Bean SqlSessionTemplate sqlSessionTemplateTwo(){ return new SqlSessionTemplate(sqlSessionFactoryTwo()); } }生成mybatis信息mybatis不是我们要讲解的重点，我这里的信息都是通过Free MyBatis插件自动生成的。不做过多介绍。增加实体Userone.java/** * userone * @author */ @Data public class Userone implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }Usertwo.java/** * usertwo * @author */ @Data public class Usertwo implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }增加mapperUseroneMapper.java@Mapper public interface UseroneMapper { int deleteByPrimaryKey(Long id); int insert(Userone record); int insertSelective(Userone record); Userone selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Userone record); int updateByPrimaryKey(Userone record); }Usertwo.java/** * usertwo * @author */ @Data public class Usertwo implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }增加xmlUseroneMapper.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="net.xiangcaowuyu.simplemybatis.mapper.one.UseroneMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.simplemybatis.entity.Userone"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="name" jdbcType="VARCHAR" property="name"/> </resultMap> <sql id="Base_Column_List"> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from userone where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from userone where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone"> update userone <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone"> update userone set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>UsertwoMapper.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="net.xiangcaowuyu.simplemybatis.mapper.two.UsertwoMapper"> <resultMap id="BaseResultMap" type="net.xiangcaowuyu.simplemybatis.entity.Usertwo"> <id column="id" jdbcType="BIGINT" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <sql id="Base_Column_List"> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from usertwo where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from usertwo where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo"> update usertwo <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo"> update usertwo set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>配置测试Api/** * @author laughing * @date 2020/9/30 * @site https://www.xiangcaowuyu.net */ @RestController public class MyBatisController { @Resource UseroneMapper useroneMapper; @Resource UsertwoMapper usertwoMapper; @RequestMapping("/one") public Userone userone(){ return useroneMapper.selectByPrimaryKey(1L); } @RequestMapping("/two") public Usertwo usertwo(){ return usertwoMapper.selectByPrimaryKey(1L); } }增加两个Api，分别用于获取数据源1和数据源2的数据。整体代码结构里面封装了全局异常，如果不了解的，可以参考 SpringBoot 之 @ControllerAdvice使用场景代码测试我们在分别打开两个请求，查看获取的数据注意事项在配置数据源时，注意设置mapper的位置，即如下代码：sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml"));如果没有设置，可能会提示Invalid bound statement (not found)
