JPA中关于外键设计的测试和思考

偏执的太偏执、 2021-12-23 14:53 284阅读 0赞

## 一、 项目构建(Maven工程） ##

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>
    
        <groupId>com.tonels</groupId>
        <artifactId>classicModel</artifactId>
        <version>1.0-SNAPSHOT</version>
    
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.0.6.RELEASE</version>
            <relativePath /> 
        </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>org.projectlombok</groupId>
            <artifactId>lombok</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</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
    
        
        <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.18</version>
    </dependency>
    
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    
        
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.5.10</version>
        </dependency>
    
        
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>28.0-jre</version>
        </dependency>
    
    
        
        
            
            
        
    
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
        </dependency>
        
        <dependency>
            <groupId>com.ctrip.framework.apollo</groupId>
            <artifactId>apollo-client</artifactId>
            <version>1.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.ctrip.framework.apollo</groupId>
            <artifactId>apollo-core</artifactId>
            <version>1.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
            <version>2.0.3.RELEASE</version>
        </dependency>
    
    
    
    
    
    </dependencies>
    
    </project>

这是application.yml

spring:
      datasource:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://localhostassicmodels?autoReconnect=true&useSSL=false
        username: root
        password: root
        hikari:
          data-source-properties:
            cachePrepStmts: true
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            useServerPrepStmts: true
      jpa:
        database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
        database: MYSQL
        show-sql: true
        properties:
          hibernate.id.new_generator_mappings: true
          hibernate.cache.use_second_level_cache: false
          hibernate.cache.use_query_cache: false
          hibernate.generate_statistics: false
          hibernate.hbm2ddl.auto: none # 自动生成建表语句
        hibernate:
          naming:
            physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl # 取消驼峰式命名
    
    server:
      port: 1210

### 1.1、model和表的创建（基于自动建表），这里只提供model模型 ###

这是One的一方

@Data
    @Entity
    @Accessors(chain = true)
    @Table(name = "com_11")
    @JsonIgnoreProperties({"hibernateLazyInitializer","handler"}) // 后来加的注解，能解决报错，但对取消级联查询也没什么用
    public class Com_11 {
    
        @Id@GeneratedValue(strategy=GenerationType.IDENTITY)
        @Column(name="teamId")				// 主键
        private Integer teamId;
    
        @Column(name="teamName",unique=true,columnDefinition="varchar(50) not null ")
        private String teamName;				// 名称
    
    }

这是Many的一方

@Data
    @Entity
    @Accessors(chain = true)
    @Table(name = "com_12")
    //@JsonIgnoreProperties({"hibernateLazyInitializer","handler"})
    public class Com_12 {
    
            @Id@GeneratedValue(strategy=GenerationType.IDENTITY)
            @Column(name="partnerId")
            private Integer partnerId;                  //主键
    
            @Column(name="partnerName")
            private String partnerName;					//姓名
    
            @JoinColumn(name = "teamId",referencedColumnName = "teamId")
            @ManyToOne(cascade=CascadeType.PERSIST/*,fetch = FetchType.LAZY,targetEntity = Com_11.class*/)
            private Com_11 com_11;
    }

### 1.2、Controller、Service和Repository ###

Controller层

package tonels.controller.combine;
    
    import org.springframework.web.bind.annotation.*;
    import tonels.common.ResultBean;
    import tonels.model.combine.Com_11;
    import tonels.service.comb.Comb11_Service;
    
    import javax.annotation.Resource;
    import java.util.Set;
    
    @RestController
    @RequestMapping("/comb11")
    public class Comb11_Controller {
    
        @Resource
        private Comb11_Service comb11_service;
    
    
        @GetMapping("/findAll")
        public ResultBean g1(){
            return ResultBean.ok(comb11_service.findAll());
        }
    
        @PostMapping("/add")
        public ResultBean add(@RequestBody Com_11 vo){
            return ResultBean.ok(comb11_service.doCreate(vo));
        }
    
        @PutMapping("/update")
        public ResultBean update(@RequestBody Com_11 vo){
            return ResultBean.ok(comb11_service.doUpdate(vo));
        }
    
        @DeleteMapping("/del")
        public ResultBean del(@RequestBody Set<Integer> ids){
            return ResultBean.ok(comb11_service.doRemove(ids));
        }
    
        @GetMapping("/findById")
        public ResultBean findById(Integer id){
            return ResultBean.ok(comb11_service.findById(id));
        }
    
    }

package tonels.controller.combine;
    
    import org.springframework.web.bind.annotation.*;
    import tonels.common.ResultBean;
    import tonels.model.combine.Com_11;
    import tonels.model.combine.Com_12;
    import tonels.repository.comb.Comb12_Repo;
    import tonels.service.comb.Comb11_Service;
    import tonels.service.comb.Comb12_Service;
    
    import javax.annotation.Resource;
    import java.util.Map;
    import java.util.Set;
    
    @RestController
    @RequestMapping("/comb12")
    public class Comb12_Controller {
    
    
        @Resource
        private Comb12_Service comb12_service;
    
        @Resource
        private Comb11_Service comb11_service;
    
        @GetMapping("/findAll")
        public ResultBean g1(){
            return ResultBean.ok(comb12_service.findAll());
        }
    
        @PostMapping("/add")
        public ResultBean add(@RequestBody Map<String,Object> map){
            String partnerName = (String)map.get("partnerName");
            Integer teamId1 = (Integer) map.get("teamId");
    
            Com_12 com_12 = new Com_12();
            Com_11 byId = comb11_service.findById(teamId1);
    
            Com_12 com_121 = com_12.setPartnerName(partnerName).setCom_11(byId);
            return ResultBean.ok(comb12_service.doCreate(com_121));
        }
    
        @PostMapping("/add2")
        public ResultBean add2(@RequestBody Com_12 Com_12){
            return ResultBean.ok(comb12_service.doCreate(Com_12));
        }
    
        @PutMapping("/update")
        public ResultBean update(@RequestBody Com_12 vo){
            return ResultBean.ok(comb12_service.doUpdate(vo));
        }
    
        @DeleteMapping("/del")
        public ResultBean del(@RequestBody Set<Integer> ids){
            return ResultBean.ok(comb12_service.doRemove(ids));
        }
    
        @GetMapping("/findById")
        public ResultBean findById(Integer id){
            return ResultBean.ok(comb12_service.findById(id));
        }
    
    }

Service层

package tonels.service;
    
    import org.springframework.data.domain.Page;
    
    import java.util.List;
    import java.util.Set;
    
    // V是对象，K是主键
    public interface BaseService<T,K> {
    
        boolean doCreate(T vo);
    
        boolean doUpdate(T vo);
    
        boolean doRemove(Set<K> ids);
    
        T findById(K id);
    
        List<T> findAll();
    
        Page<T> findPages();
    
        List<T> findAllSplit(String column, String keyword, Integer page, Integer rows);
    
        Integer getCount(String column, String keyword);
    
    
    
    }

package tonels.service.comb;
    
    import tonels.model.combine.Com_11;
    import tonels.service.BaseService;
    import java.util.Set;
    
    public interface Comb11_Service extends BaseService<Com_11,Integer> {
    
    }

package tonels.service.comb;
    
    import tonels.model.combine.Com_12;
    import tonels.service.BaseService;
    
    public interface Comb12_Service extends BaseService<Com_12,Integer> {
    
    
    }

实现类

package tonels.service.comb.impl;
    
    import cn.hutool.json.JSONUtil;
    import org.springframework.data.domain.Page;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    import tonels.model.combine.Com_11;
    import tonels.model.combine.Com_12;
    import tonels.repository.comb.Comb11_Repo;
    import tonels.repository.comb.Comb12_Repo;
    import tonels.service.comb.Comb11_Service;
    import tonels.service.comb.Comb12_Service;
    
    import javax.annotation.Resource;
    import java.util.List;
    import java.util.Map;
    import java.util.Set;
    import java.util.stream.Collectors;
    
    
    @Service
    @Transactional
    public class Comb11_Impl implements Comb11_Service {
    
        @Resource
        private Comb11_Repo comb11_repo;
        @Resource
        private Comb12_Repo comb12_repo;
    
    
        @Override
        public boolean doCreate(Com_11 vo) {
            comb11_repo.save(vo);
            return true;
        }
    
        @Override
        public boolean doUpdate(Com_11 vo) {
            comb11_repo.save(vo);
            return true;
        }
    
        @Override
        public boolean doRemove(Set<Integer> ids) {
            comb12_repo.deleteByids(ids);
            comb11_repo.deleteByIdIn(ids);
            return true;
        }
    
    
        @Override
        public Com_11 findById(Integer id) {
            return comb11_repo.findById(id).orElse(null);
        }
    
        @Override
        public List<Com_11> findAll() {
            return comb11_repo.findAll();
        }
    
        @Override
        public Page<Com_11> findPages() {
            return null;
        }
    
        @Override
        public List<Com_11> findAllSplit(String column, String keyword, Integer page, Integer rows) {
            return null;
        }
    
        @Override
        public Integer getCount(String column, String keyword) {
            return null;
        }
    }

package tonels.service.comb.impl;
    
    import org.springframework.data.domain.Page;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    import tonels.model.combine.Com_11;
    import tonels.model.combine.Com_12;
    import tonels.repository.comb.Comb12_Repo;
    import tonels.service.comb.Comb11_Service;
    import tonels.service.comb.Comb12_Service;
    
    import javax.annotation.Resource;
    import java.util.List;
    import java.util.Set;
    
    @Service
    @Transactional
    public class Comb12_Impl implements Comb12_Service {
    
        @Resource
        private Comb12_Repo comb12_repo;
        @Resource
        private Comb11_Service comb11_service;
    
        @Override
        public boolean doCreate(Com_12 vo) {
            Com_11 byId = comb11_service.findById(vo.getCom_11().getTeamId());
            vo.setCom_11(byId);
            comb12_repo.save(vo);
            return true;
        }
    
        @Override
        public boolean doUpdate(Com_12 vo) {
            Com_11 byId = comb11_service.findById(vo.getCom_11().getTeamId());
            vo.setCom_11(byId);
            comb12_repo.save(vo);
            return true;
        }
    
        @Override
        public boolean doRemove(Set<Integer> ids) {
            comb12_repo.deleteByIdIn(ids);
            return true;
        }
    
        @Override
        public Com_12 findById(Integer id) {
            return comb12_repo.findById(id).orElse(null);
        }
    
        @Override
        public List<Com_12> findAll() {
            return comb12_repo.findAll();
        }
    
        @Override
        public Page<Com_12> findPages() {
            return null;
        }
    
        @Override
        public List<Com_12> findAllSplit(String column, String keyword, Integer page, Integer rows) {
            return null;
        }
    
        @Override
        public Integer getCount(String column, String keyword) {
            return null;
        }
    }

Repository层

package tonels.repository;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.repository.NoRepositoryBean;
    
    @NoRepositoryBean
    public interface BaseRepository<T,K> extends JpaRepository<T, K>,JpaSpecificationExecutor<T>{
    }

package tonels.repository.comb;
    
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    import tonels.model.combine.Com_11;
    import tonels.repository.BaseRepository;
    
    import java.util.List;
    import java.util.Map;
    import java.util.Set;
    
    public interface Comb11_Repo extends BaseRepository<Com_11,Integer> {
    
        @Modifying
        @Query("delete from Com_11 c11 where c11.teamId in (?1)")
        void deleteByIdIn(Set<Integer> ids); // true
    
    //    void deleteByIdIn(List<Long> ids); // false,这个命名规则会加载失败，No property id found for type Com_11!
    
    
        @Query(nativeQuery = true,value = "SELECT c12.* from com_11 as c11 LEFT JOIN com_12 as c12 ON c11.team_id =c12.team_id where c11.team_id in ?1")
        List<Map<String,Object>> findCom12ByIdIn(Set<Integer> ids); // false,编译没错，但运行出错，无法转换Obiect[]到对象
    
    
    }

package tonels.repository.comb;
    
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    import tonels.model.combine.Com_11;
    import tonels.model.combine.Com_12;
    import tonels.repository.BaseRepository;
    
    import java.util.List;
    import java.util.Set;
    
    public interface Comb12_Repo extends BaseRepository<Com_12,Integer> {
    
        @Modifying
        @Query("delete from Com_12 c12 where c12.partnerId in (?1)")
        void deleteByIdIn(Set<Integer> ids);
    
        @Modifying
        @Query(nativeQuery = true,value = "delete from com_12 where com_12.team_id in (?1)")
        void deleteByids(Set<Integer> teamId);
    
    }

## 二、启动项目的自动生成的建表语句，会有外键生成，虽然百度上各种论坛，普遍都不认可的外键，真的像他们所说的那么差吗？（研究中） ##

CREATE TABLE `com_11` (
      `team_id` int(11) NOT NULL AUTO_INCREMENT,
      `team_name` varchar(50) NOT NULL,
      PRIMARY KEY (`team_id`),
      UNIQUE KEY `UK_di7cg25ubign6rud63bh5fvxf` (`team_name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `com_12` (
      `partner_id` int(11) NOT NULL AUTO_INCREMENT,
      `partner_name` varchar(255) DEFAULT NULL,
      `team_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`partner_id`),
      KEY `FK11najlfeiyagys1i5u12u0t7c` (`team_id`),
      CONSTRAINT `FK11najlfeiyagys1i5u12u0t7c` FOREIGN KEY (`team_id`) REFERENCES `com_11` (`team_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4;

## 三、接口测试，发现的问题 ##

### 3.1 添加和修改时，One表不会受到Many表的和影响；但Many表添加时，会有很多问题发现，入参方式，两种Map和对象（嵌套对象方式的入参方式） ###

// Many表的对象接收时的入参方式
    {
      "partnerName": "esse ipsum",
      "com_11": {
        "teamId": -51517669.8947001
      }
    }

### 因为在Many的Model中并没有外键的属性字段，只是一个对象，关于外键的保存是基于对象的，如果传入的teamId在One表中不存在会在Many表中存的是Null（也是可以的) ###

### 3.2 删除问题，删除One表数据时，会对Many表的进行外键检查，需要手动先删除Many表的对应数据，再去删除One表的数据，好像是可以在 ###

@ManyToOne(cascade=CascadeType.PERSIST）// 这里是可以配置级联级别的

### 还有在写Many表的删除时，只能用原生SQL，Many中没有显式显示外键字段，deleteByOneIn(List ones)是不行的，原生SQl表是不能用别名的。 ###

### 3.3 查询问题，One表的是正常的查询还有返回，Many查询时会级联查到One表的数据，打印的SQl为 ###

select com_12x0_.partner_id as partner_1_9_, com_12x0_.team_id as team_id3_9_, com_12x0_.partner_name as partner_2_9_ from com_12 com_12x0_
            select com_11x0_.team_id as team_id1_8_0_, com_11x0_.team_name as team_nam2_8_0_ from com_11 com_11x0_ where com_11x0_.team_id=?
            select com_11x0_.team_id as team_id1_8_0_, com_11x0_.team_name as team_nam2_8_0_ from com_11 com_11x0_ where com_11x0_.team_id=?
            select com_11x0_.team_id as team_id1_8_0_, com_11x0_.team_name as team_nam2_8_0_ from com_11 com_11x0_ where com_11x0_.team_id=?
            select com_11x0_.team_id as team_id1_8_0_, com_11x0_.team_name as team_nam2_8_0_ from com_11 com_11x0_ where com_11x0_.team_id=?
            select com_11x0_.team_id as team_id1_8_0_, com_11x0_.team_name as team_nam2_8_0_ from com_11 com_11x0_ where com_11x0_.team_id=?
            select com_11x0_.team_id as team_id1_8_0_, com_11x0_.team_name as team_nam2_8_0_ from com_11 com_11x0_ where com_11x0_.team_id=?
            select com_11x0_.team_id as team_id1_8_0_, com_11x0_.team_name as team_nam2_8_0_ from com_11 com_11x0_ where com_11x0_.team_id=?
            select com_11x0_.team_id as team_id1_8_0_, com_11x0_.team_name as team_nam2_8_0_ from com_11 com_11x0_ where com_11x0_.team_id=?

{
      "code": "200",
      "msg": "成功",
      "result": [
        {
          "partnerId": 1,
          "partnerName": "sss",
          "com_11": {
            "teamId": 4,
            "teamName": "B1组"
          }
        },
        {
          "partnerId": 3,
          "partnerName": "张七",
          "com_11": {
            "teamId": 7,
            "teamName": "B4组"
          }
        },
        {
          "partnerId": 5,
          "partnerName": "张七",
          "com_11": {
            "teamId": 7,
            "teamName": "B4组"
          }
        },
        {
          "partnerId": 62,
          "partnerName": "张六",
          "com_11": null
        }
      ]
    }

### 对于findAll()方法，没有找到不让它自动级联的方法，只能自己手动写查询语句，HQL或者SQL都行 ###

### 3.4 关于引入外键的思考，有待学习，现在还停留在 ###

可以做数据保护，肯定的
    百度上所谓大牛的客观评价？怀疑的