一,MyBatis框架介绍
MyBatis是一款支持动态sql的半自动化sql的ORM框架,在企业中应用的很多,相比较于Hibernate,JdbcTemplate这样的框架,它有着自己独特的好处所以这篇还是自己去整理一下关于MyBatis框架在支持多表关联操作的编写。
二,项目依赖的jar包信息
<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.2</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>
以上jar包依赖主要是mysql连接,支持mybatis操作以及简化get/set方法的lombok包。
三,连接数据库配置文件的信息
server: port: 8080spring: application: name: mybatis-assoaction datasource: url: jdbc:mysql://localhost:3306/assoaction?serverTimezone=UTC username: root password: root driver-class-name: com.mysql.cj.jdbc.Drivermybatis: mapper-locations: classpath:/mapper/**.xml
四,便于用postman工具测试的UserController类相关的代码如下,这里进行了分层逻辑的拆分。
package com.wpw.mybatisassoaction.controller;
import com.wpw.mybatisassoaction.entity.User;import com.wpw.mybatisassoaction.service.UserService;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.RestController;
/** * @author wpw */@RestControllerpublic class UserController { private final UserService userService;
public UserController(UserService userService) { this.userService = userService; }
@GetMapping(value = "/get") public User getUserByUserId(@RequestParam(value = "userId") Integer userId) { return userService.getUserByUserId(userId); }
@GetMapping(value = "/getUserWithCompany") public User getUserWithCompany(@RequestParam(value = "userId") Integer userId) { return userService.getUserWithCompany(userId); }
@GetMapping(value = "/getUserWithAccountList") public User getUserWithAccountList(@RequestParam(value = "userId") Integer userId) { return userService.getUserWithAccountList(userId); }
@GetMapping(value = "/optionUser") public User optionUser(@RequestParam(value = "userId") Integer userId) { return userService.optionUser(userId); }
@GetMapping(value = "/selectOne") public User selectOne(@RequestParam(value = "userId") Integer userId) { return userService.selectOne(userId); }}
五,业务逻辑处理类相关的代码如下,这里都针对方法名做了见名知意的处理,所以本篇文章就不会很多去介绍是如何实现的。
package com.wpw.mybatisassoaction.service;
import com.wpw.mybatisassoaction.entity.User;
/** * @author pc */public interface UserService { /** * 根据用户id查询用户信息 * * @param userId 用户id * @return 用户信息 */ User getUserByUserId(Integer userId);
/** * 根据用户id获取用户信息 * * @param userId 用户id * @return 用户信息,不含有用户账户信息 */ User getUserWithCompany(Integer userId);
/** * 根据用户id获取用户信息 * * @param userId 用户id * @return 用户信息,不含有公司信息 */ User getUserWithAccountList(Integer userId);
/** * 根据用户id获取用户信息 * @param userId 用户id * @return 用户信息 */ User optionUser(Integer userId);
/** * 根据用户id获取用户信息 * @param userId 用户id * @return 用户信息 */ User selectOne(Integer userId);}
实现类的逻辑代码如下
package com.wpw.mybatisassoaction.service.impl;
import com.wpw.mybatisassoaction.entity.User;import com.wpw.mybatisassoaction.mapper.UserMapper;import com.wpw.mybatisassoaction.service.UserService;import org.springframework.stereotype.Service;
/** * @author wpw */@Servicepublic class UserServiceImpl implements UserService { private final UserMapper userMapper;
public UserServiceImpl(UserMapper userMapper) { this.userMapper = userMapper; }
@Override public User getUserByUserId(Integer userId) { return userMapper.getUserByUserId(userId); }
@Override public User getUserWithCompany(Integer userId) { return userMapper.getUserWithCompany(userId); }
@Override public User getUserWithAccountList(Integer userId) { return userMapper.getUserWithAccountList(userId); }
@Override public User optionUser(Integer userId) { return userMapper.optionUser(userId); }
@Override public User selectOne(Integer userId) { return userMapper.selectOne(userId); }}
六,这里主要是和数据库进行操作的UserMapper接口和UserMapper.xml文件代码的编写。
package com.wpw.mybatisassoaction.mapper;
import com.wpw.mybatisassoaction.entity.Account;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;
import java.util.List;
/** * @author wpw */@Mapper@Repositorypublic interface AccountMapper {
/** * 根据用户id查找当前用户下的账户信息 * * @param userId 用户id * @return 账户列表信息 */ @Select(value = "select * from account where user_id=#{userId}") @Results({ @Result(property = "id", column = "id"), @Result(property = "accountName", column = "name") }) List<Account> listAccountByUserId(Integer userId);}
package com.wpw.mybatisassoaction.mapper;
import com.wpw.mybatisassoaction.entity.Company;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;
/** * @author wpw */@Mapper@Repositorypublic interface CompanyMapper { /** * 根据公司id查询公司信息 * * @param companyId 公司id * @return 公司信息 */ @Select(value = "select * from company where id=#{companyId}") @Results({ @Result(property = "id", column = "id"), @Result(property = "companyName", column = "name") }) Company getByCompanyId(Integer companyId);}
package com.wpw.mybatisassoaction.mapper;
import com.wpw.mybatisassoaction.entity.User;import org.apache.ibatis.annotations.*;import org.springframework.stereotype.Repository;
/** * @author wpw */@Mapper@Repositorypublic interface UserMapper { /** * 根据用户id获取用户信息 * * @param userId 用户id * @return 用户信息 */ @Select(value = "select * from user where id=#{userId}") @Results({ @Result(property = "id", column = "id"), @Result(property = "name", column = "name"), @Result(property = "company", column = "company_id", one = @One(select = "com.wpw.mybatisassoaction.mapper.CompanyMapper.getByCompanyId")), @Result(property = "accountList", column = "id", many = @Many(select = "com.wpw.mybatisassoaction.mapper.AccountMapper.listAccountByUserId")) }) User getUserByUserId(Integer userId);
/** * 根据用户id获取用户信息 * * @param userId 用户id * @return 用户信息,不含有账户信息 */ @Select(value = "select * from user where id=#{userId}") @Results({ @Result(property = "id", column = "id"), @Result(property = "name", column = "name"), @Result(property = "company", column = "company_id", one = @One(select = "com.wpw.mybatisassoaction.mapper.CompanyMapper.getByCompanyId")) }) User getUserWithCompany(Integer userId);
/** * 根据用户id获取用户信息 * * @param userId 用户id * @return 用户信息,不含有公司信息 */ @Select(value = "select * from user where id=#{userId}") @Results({ @Result(property = "id", column = "id"), @Result(property = "name", column = "name"), @Result(property = "accountList", column = "id", many = @Many(select = "com.wpw.mybatisassoaction.mapper.AccountMapper.listAccountByUserId")) }) User getUserWithAccountList(Integer userId);
/** * 根据用户id获取用户信息 * * @param userId 用户id * @return 用户信息 */ User optionUser(Integer userId);
/** * 根据用户id获取用户信息 * * @param userId 用户id * @return 用户信息 */ User selectOne(Integer userId);}
UserMapper.xml文件代码是这篇文章主要的内容,相比较于注解的方式,它还是比较好用的,注解也挺好,不过自己慢慢也喜欢了用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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的 --><mapper namespace="com.wpw.mybatisassoaction.mapper.UserMapper"> <resultMap id="map" type="com.wpw.mybatisassoaction.entity.User"> <id property="id" column="id"/> <result property="name" column="name"/> <association property="company" column="company_id" select="getCompany"> </association> <collection property="accountList" column="id" select="listAccountByUserId"/> </resultMap> <select id="listAccountByUserId" parameterType="int" resultMap="accountMap"> select * from account where user_id = #{id} </select> <resultMap id="accountMap" type="com.wpw.mybatisassoaction.entity.Account"> <id property="id" column="id"/> <result property="accountName" column="name"/> </resultMap> <select id="optionUser" parameterType="int" resultMap="map"> select * from user where id = #{id} </select> <resultMap id="companyMap" type="com.wpw.mybatisassoaction.entity.Company"> <id property="id" column="id"/> <result property="companyName" column="name"/> </resultMap> <select id="getCompany" resultMap="companyMap"> select * from company where id = #{id} </select> <resultMap id="userMap" type="com.wpw.mybatisassoaction.entity.User"> <id property="id" column="id"/> <result property="name" column="name"/> <association property="company" column="company_id" javaType="com.wpw.mybatisassoaction.entity.Company"> <id property="id" column="companyId"/> <result property="companyName" column="companyName"/> </association> <collection property="accountList" column="id" ofType="com.wpw.mybatisassoaction.entity.Account"> <id property="id" column="accountId"/> <result property="accountName" column="accountName"/> </collection> </resultMap> <select id="selectOne" parameterType="int" resultMap="userMap"> select u.id, u.name, c.id companyId, c.name companyName, a.id accountId, a.name accountName from user u left join company c on u.company_id = c.id left join account a on u.id = a.user_id where u.id = #{id} </select></mapper>
七,项目需要的测试基础类
package com.wpw.mybatisassoaction.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;
/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class Account implements Serializable { private Integer id; private String accountName;}
package com.wpw.mybatisassoaction.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class Company implements Serializable { private Integer id; private String companyName;}
package com.wpw.mybatisassoaction.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;import java.util.List;
/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class User implements Serializable { private Integer id; private String name; private Company company; private List<Account> accountList;}
八,项目依赖的sql文件信息
create table if not exists `account`( id int(11) not null auto_increment comment '主键', name varchar(200) not null comment '账户名称', user_id int(11) not null comment '用户id', primary key (`id`)) engine = InnoDB DEFAULT CHARSET = utf8;
create table if not exists `company`( id int(11) not null auto_increment comment '主键', name varchar(200) not null comment '公司名称', primary key (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8;
create table if not exists `user`( id int(11) not null auto_increment comment '主键', name varchar(50) not null comment '姓名', company_id int(11) not null comment '公司id', primary key (`id`)) engine = InnoDB DEFAULT CHARSET = utf8;
insert into user(name,company_id)values("zhangsan",1);insert into user(name,company_id)values("lisi",2);insert into company(name) values("中国移动");insert into company(name) values("中国联通");insert into account(name,user_id) values("中国农业银行",1);insert into account(name,user_id) values("中国工商银行",1);insert into account(name,user_id) values("中国邮政储蓄银行",2);insert into account(name,user_id) values("中国银行",2);
整个项目的搭建和测试流程就结束了,下面是项目的结构图以及项目代码的结构图
gitHub地址:https://github.com/myownmyway/mybatis-assoaction.git