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

一、项目构建(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 /> <!-- lookup parent from repository -->
    </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>

    <!--Lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>

    <!-- 引入web依赖 -->
    <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>

    <!-- 国内开源工具类Hutool和Google的Guava,Java工具集 -->
    <dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-all</artifactId>
        <version>4.5.10</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/com.google.guava/guava -->
    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
        <version>28.0-jre</version>
    </dependency>


    <!--&lt;!&ndash;log相关&ndash;&gt;-->
    <!--<dependency>-->
        <!--<groupId>org.slf4j</groupId>-->
        <!--<artifactId>slf4j-log4j12</artifactId>-->
    <!--</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>
    <!--spring2.0集成redis所需common-pool2 -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-pool2</artifactId>
    </dependency>
    <!--apollo -->
    <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 关于引入外键的思考，有待学习，现在还停留在

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

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

一、 项目构建(Maven工程）

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

1.2、Controller、Service和Repository

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

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

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

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

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

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

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

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

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

一、项目构建(Maven工程）