欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

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

程序员文章站 2022-04-12 17:50:25
...

一、 项目构建(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 {

    @[email protected](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 {

        @[email protected](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 @ManyToOne @query

上一篇： 从客户端检测到有潜在危险的Request.Form值

下一篇： JPA 中联合查询问题处理

推荐阅读