Spring boot整合Mybatis實(shí)現(xiàn)級(jí)聯(lián)一對(duì)多CRUD操作的完整步驟

CREATE TABLE user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(20),
 password VARCHAR(20)
)

CREATE TABLE role(
 id INT PRIMARY KEY AUTO_INCREMENT,
 rolename VARCHAR(20)
)

INSERT INTO `role`(`rolename`) VALUES ('后臺(tái)');
INSERT INTO `role`(`rolename`) VALUES ('前端');
INSERT INTO `role`(`rolename`) VALUES ('客戶端');
INSERT INTO `role`(`rolename`) VALUES ('AI');
INSERT INTO `role`(`rolename`) VALUES ('大數(shù)據(jù)');

CREATE TABLE user_role(
 id INT PRIMARY KEY AUTO_INCREMENT,
 userid INT,
 roleid INT
)

   <!-- MyBatis Generator插件 -->
   <plugin>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-maven-plugin</artifactId>
    <version>1.3.7</version>
    <configuration>
     <verbose>true</verbose>
     <overwrite>true</overwrite>
    </configuration>
    <dependencies>
     <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.16</version>
     </dependency>
    </dependencies>
   </plugin>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
  PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
  "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
 <!--配置文件信息-->
 <properties resource="application.properties"/>
 
 <!--defaultModelType="flat" 大數(shù)據(jù)字段，不分表 -->
 <context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
  <property name="autoDelimitKeywords" value="true" />
  <property name="beginningDelimiter" value="`" />
  <property name="endingDelimiter" value="`" />
  <property name="javaFileEncoding" value="utf-8" />
  <plugin type="org.mybatis.generator.plugins.SerializablePlugin" />
 
  <plugin type="org.mybatis.generator.plugins.ToStringPlugin" />
 
  <!-- 注釋 -->
  <commentGenerator >
   <property name="suppressAllComments" value="true"/><!-- 是否取消注釋 -->
   <property name="suppressDate" value="true" /> <!-- 是否生成注釋代時(shí)間戳-->
  </commentGenerator>
 
  <!--數(shù)據(jù)庫(kù)鏈接-->
  <jdbcConnection driverClass="${spring.datasource.driverClassName}"
      connectionURL="${spring.datasource.url}"
      userId="${spring.datasource.username}"
      password="${spring.datasource.password}">
  </jdbcConnection>
 
  <!-- 類型轉(zhuǎn)換 -->
  <javaTypeResolver>
   <!-- 是否使用bigDecimal， false可自動(dòng)轉(zhuǎn)化以下類型（Long, Integer, Short, etc.） -->
   <property name="forceBigDecimals" value="false"/>
  </javaTypeResolver>
 
  <!--生成Model類存放位置-->
  <javaModelGenerator targetPackage="com.shangguan.mybatis1.entity" targetProject="src/main/java">
   <property name="enableSubPackages" value="true"/>
   <property name="trimStrings" value="true"/>
  </javaModelGenerator>
 
  <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources" >
   <property name="enableSubPackages" value="false" />
  </sqlMapGenerator>
 
  <javaClientGenerator targetPackage="com.shangguan.mybatis1.dao" targetProject="src/main/java" type="XMLMAPPER" >
   <property name="enableSubPackages" value="false" />
  </javaClientGenerator>
 
  <!-- 數(shù)據(jù)庫(kù)表的信息 -->
  <table tableName="user" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true">
   <generatedKey column="id" sqlStatement="Mysql" identity="true" />
  </table>
 
  <table tableName="role" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true">
   <generatedKey column="id" sqlStatement="Mysql" identity="true" />
  </table>
 
  <table tableName="user_role" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true">
   <generatedKey column="id" sqlStatement="Mysql" identity="true" />
  </table>
 
 </context>
</generatorConfiguration>

 @RequestMapping("/addUser")
 @Transactional(rollbackFor={RuntimeException.class, Exception.class})
 public Result saveUser(@RequestParam(value = "username") String username, 
   @RequestParam(value = "password") String password, 
   @RequestParam(value = "roleids") List<Integer> roleids) {
  User user = new User();
  user.setUsername(username);
  user.setPassword(password);
  userService.addUser(user);
  for(int i=0;i<roleids.size();i++) {
   UserRole userRole = new UserRole();
   userRole.setUserid(user.getId());
   userRole.setRoleid(roleids.get(i));
   userRoleService.addUserRole(userRole);
  }
  return ResultUtils.result(States.errorCode.SUCCESS, "添加成功", null);
 }

  @RequestMapping("/deleteUserById")
  @Transactional(rollbackFor={RuntimeException.class, Exception.class})
  public Result deleteUserById(Integer id) {
    userService.deleteUserById(id);
    List<UserRole> list = userRoleService.selectByUserId(id);
    for(int i=0;i<list.size();i++) {
      userRoleService.deleteByPrimaryKey(list.get(i).getId());
    }
    return ResultUtils.result(States.errorCode.SUCCESS, "刪除成功", null);
  }

  @RequestMapping("/updateUser")
  @Transactional(rollbackFor={RuntimeException.class, Exception.class})
  public Result updateUser(@RequestParam(value = "id")Integer id, 
      @RequestParam(value = "username") String username, 
      @RequestParam(value = "password") String password, 
      @RequestParam(value = "roleids") List<Integer> roleids) {
    userService.updateUser(id, username, password);
    //查找user_role然后按照id進(jìn)行刪除
    List<UserRole> list = userRoleService.selectByUserId(id);
    for(int i=0;i<list.size();i++) {
      userRoleService.deleteByPrimaryKey(list.get(i).getId());
    }
    //插入新的roleids
    for(int i=0;i<roleids.size();i++) {
      UserRole record = new UserRole();
      record.setUserid(id);
      record.setRoleid(roleids.get(i));
      userRoleService.addUserRole(record);
    }
    return ResultUtils.result(States.errorCode.SUCCESS, "更新成功", null);
  }

void updateByPrimaryKey(@Param("id")Integer id, 
@Param("username")String username, @Param("password")String 
password);

public class UserDetail {
  private Integer userid;
  private String username;
  private String password; 
  private List<Integer> roleids; 
  private List<String> rolenames;
  //省略getter和setter
}

public List<UserDetail> selectAll(){
  List<User> userList = new ArrayList<>();
  List<UserDetail> details = new ArrayList<>();
 
  try{
    userList = userMapper.selectAll();
    details = getUserDetails(userList);
  }catch(Exception e){
    e.printStackTrace();
    return details;
  }
  return details;
}
 
public List<UserDetail> getUserDetails(List<User> lists){
  List<UserDetail> details = new ArrayList<>();
  if(lists == null || lists.size() < 1){
    return details;
  }
  Map<Integer, String> nameMap = roleService.getNameMap();
  for(int i=0; i< lists.size();i++){
    User user = lists.get(i);
    UserDetail detail = new UserDetail();
 
    detail.setUserid(user.getId());
    detail.setUsername(user.getUsername());
    detail.setPassword(user.getPassword());
    List<Integer> roleids = new ArrayList<>();
    List<String> rolenames = new ArrayList<>();
    List<UserRole> userroles = userRoleMapper.selectByUserId(user.getId());
    for(int j=0;j<userroles.size();j++) {
      roleids.add(userroles.get(j).getRoleid());
      rolenames.add(nameMap.get(userroles.get(j).getRoleid()));
    }
    detail.setRoleids(roleids);
    detail.setRolenames(rolenames);
    details.add(detail);
  }
  return details;
}

@RequestMapping("/getAllUser")
public Result getAllUser() {
  List<UserDetail> list = userService.selectAll();
  return ResultUtils.result(States.errorCode.SUCCESS, "查詢成功", list);
}