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 关于引入外键的思考，有待学习，现在还停留在

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

相关推荐

11-20 17:40
西安邮电大学 有线传输工程师
点赞 评论 收藏
分享
11-22 00:49
湖南大学 iOS开发
点赞 评论 收藏
分享
10-02 13:31
长沙学院 嵌入式软件开发
大华我也谢谢你！
投递大华股份等公司10个岗位
点赞 评论 收藏
分享
11-19 13:40
西安电子科技大学 Java
马斯克招人策略曝光：9 轮面试
作者：量子位事情是这样的。Business&nbsp;Insider&nbsp;最近获得了特斯拉内部薪酬数据库（截至&nbsp;2021&nbsp;年&nbsp;12&nbsp;月）的访问权限，里面有&nbsp;10&nbsp;万名员工的薪酬数据。然后他们发现了有关特斯拉薪酬的一系列猛料：面试&nbsp;9&nbsp;轮只为招聘特斯拉铁粉；采用低底薪&nbsp;+&nbsp;股票奖励策略，打出&nbsp;“高风险、高回报”&nbsp;口号；特斯拉底薪低于同行，不及苹果、谷歌、英伟达、Meta、福特等科技公司和传统汽车制造商；工程师更有可能获得股票奖励；仅有&nbsp;4%&nbsp;的员工通过激励股票期权（ISOs）&nbsp;获得股票，且通常授予高管；……（顺手推几个技术大厂的机会，前、后端or测试，感兴趣可以试试&nbsp;）更多爆料细节如下——马斯克招人策略：低底...
投递谷歌等公司10个岗位 >
点赞 评论 收藏
分享
点赞 收藏 评论
分享

全站热榜

正在热议
# 25届秋招总结 #
302983次浏览 2690人参与
# 如果不工作真的会快乐吗 #
59457次浏览 519人参与
# 阿里云管培生offer #
17803次浏览 297人参与
# 地方国企笔面经互助 #
3885次浏览 11人参与
# 美团求职进展汇总 #
1328123次浏览 12453人参与
# 选完offer后，你后悔学本专业吗 #
20156次浏览 144人参与
# 百度开奖 #
163861次浏览 981人参与
# 正在实习的你，几点下班 #
52108次浏览 391人参与
# 国央企薪资爆料 #
8616次浏览 69人参与
# 如何一边实习一边秋招 #
992491次浏览 12640人参与
# 提前批简历挂麻了怎么办 #
146565次浏览 1948人参与
# 学历or实习经历，哪个更重要 #
51284次浏览 402人参与
# 海康威视求职进展汇总 #
399042次浏览 3406人参与
# 米哈游求职进展汇总 #
176082次浏览 1458人参与
# 求职遇到的搞笑事件 #
70898次浏览 577人参与
# 投递实习岗位前的准备 #
1179718次浏览 18397人参与
# 面试体验感最好的是哪家？ #
85152次浏览 846人参与
# 实习生应该准时下班吗 #
167483次浏览 1159人参与
# 得物求职进展汇总 #
66360次浏览 682人参与
# 网申一定要掌握的小技巧 #
5349次浏览 53人参与
# 招聘要求与实际实习内容不符怎么办 #
10314次浏览 273人参与
# 0offer是寒冬太冷还是我太菜 #
898884次浏览 8015人参与
牛客网
牛客企业服务