springdata-jpa (二)多表操作

  • Post author:
  • Post category:其他




springdata-jpa(二) 多表操作

Spring Data JPA中除了单表操作外,还可以支持多表级联操作,但是这需要表和表之间有关联关系,同时还需要在实体类中把这种关联关系给配置映射出来(Object-Relationl Mapping)。



一、1对1关系

假设一个学生对应一个地址,一个地址也对应一个学生,那么学生和地址之间就是1 对1关系。

1对1关系中,数据库中表的外键列在哪一方都可以


Address

package com.hzx.springdatajpa.bean;
import javax.persistence.*;

@Entity
@Table(name = "t_address")
public class Address {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String city;

    public Address() {
    }

    public Address(Long id, String city) {
        this.id = id;
        this.city = city;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }
    
    @Override
    public String toString() {
        return "Address{" +
                "id=" + id +
                ", city='" + city + '\'' +
                '}';
    }
}


Student

package com.hzx.springdatajpa.bean;

import javax.persistence.*;

@Entity
@Table(name = "t_student")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;
    /**
     * @OneToOne 表示当前studnet实体类和Address实现类之间是1对1关系
     * cascade=CascadeType.ALL 表示student和address之间的级联类型为ALL
     *
     * @JoinColumn 该注解专门用来指定外键列的名字
     * 在这里表示:将来student表中将会有一个外键列为 address_id,
     * 该外键列默认引用Address表中的主键列(id)的值
     */
    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "address_id")
    private Address address;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Address getAddress() {
        return address;
    }

    public void setAddress(Address address) {
        this.address = address;
    }
    
    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", address=" + address +
                '}';
    }

}
@Repository
public interface StudentRepository extends JpaRepository<Student,Long> {
}
@Test
public void test_one2one(){
    Student student = new Student();
    Address address = new Address();
    address.setCity("上海");
    student.setName("zhangsan");
    student.setAddress(address);
    studentRepository.save(student);
}

控制台中输出的建表语句:

在这里插入图片描述

级联操作生成的sql语句为:

在这里插入图片描述


级联查询

@Test
public void test_one2one_select(){
    Student student = studentRepository.findById(1L).orElse(null);
    System.out.println(student);
    System.out.println(student.getAddress());
}

生成的sql语句

在这里插入图片描述

运行结果:

在这里插入图片描述


级联删除

@Test
public void test_one2one_delete(){
    Student student = studentRepository.findById(1L).orElse(null);
    studentRepository.delete(student);
}

生成的sql语句:

Hibernate: 
    select
        student0_.id as id1_1_0_,
        student0_.address_id as address_3_1_0_,
        student0_.name as name2_1_0_,
        address1_.id as id1_0_1_,
        address1_.city as city2_0_1_ 
    from
        t_student student0_ 
    left outer join
        t_address address1_ 
            on student0_.address_id=address1_.id 
    where
        student0_.id=?
Hibernate: 
    select
        student0_.id as id1_1_0_,
        student0_.address_id as address_3_1_0_,
        student0_.name as name2_1_0_,
        address1_.id as id1_0_1_,
        address1_.city as city2_0_1_ 
    from
        t_student student0_ 
    left outer join
        t_address address1_ 
            on student0_.address_id=address1_.id 
    where
        student0_.id=?
Hibernate: 
    delete 
    from
        t_student 
    where
        id=?
Hibernate: 
    delete 
    from
        t_address 
    where
        id=?



二、1对N关系

假设一个老师有多辆车,一辆车只属于一个老师,那么老师和汽车的关系就是1对N

1对N关系中,数据库表中的外键列需要设置在N的一方,否则会有数据冗余。


Teacher

package com.hzx.springdatajpa.bean;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "t_teacher")
public class Teacher {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private Double salary;

    /**
     * @OneToMany 表示teacher和car之间是一对多关系
     * 
     * mappedBy = "teacher"
     * 表示teacher和car之间一对多关系的外键列
     * 是由car的teacher属性类映射维护的
     */
    @OneToMany(mappedBy = "teacher",cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    private List<Car> cars = new ArrayList<>();
    
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public List<Car> getCars() {
        return cars;
    }

    public void setCars(List<Car> cars) {
        this.cars = cars;
    }
    
    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", cars=" + cars +
                '}';
    }
}


Car

package com.hzx.springdatajpa.bean;

import javax.persistence.*;

@Entity
@Table(name = "t_car")
public class Car {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String type;

    private Double price;
    /**
     * @ManyToOne 表示我(car)和对方(teacher)之间的关系是多对一
     * @JoinColumn 表示当前属性为外键列属性,并且指定外键列的名字
     */
    @ManyToOne
    @JoinColumn(name = "teacher_id")
    private Teacher teacher;

    public Car() {
    }

    public Car(Long id, String type, Double price, Teacher teacher) {
        this.id = id;
        this.type = type;
        this.price = price;
        this.teacher = teacher;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
    
    @Override
    public String toString() {
        return "Car{" +
                "id=" + id +
                ", type='" + type + '\'' +
                ", price=" + price +
                '}';
    }
}
package com.hzx.springdatajpa.repository;

import com.hzx.springdatajpa.bean.Car;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface CarRepository extends JpaRepository<Car,Long> {
    
}
package com.hzx.springdatajpa.repository;

import com.hzx.springdatajpa.bean.Teacher;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface TeacherRepository extends JpaRepository<Teacher,Long> {
}

测试类:


级联保存

@Test
public void one2many_insert(){
    Teacher teacher = new Teacher();
    teacher.setName("wangwu");
    teacher.setSalary(12000D);

    Car car = new Car();
    car.setType("yueye");
    car.setPrice(400000D);

    Car car2 = new Car();
    car2.setType("jiayong");
    car2.setPrice(500000D);

    Car car3 = new Car();
    car3.setType("dianli");
    car3.setPrice(600000D);

    car.setTeacher(teacher);
    car2.setTeacher(teacher);
    car3.setTeacher(teacher);

    ArrayList<Car> list = new ArrayList<>();
    list.add(car);
    list.add(car2);
    list.add(car3);
    teacher.setCars(list);
    teacherRepository.save(teacher);
}

控制台输出的建表语句:

在这里插入图片描述

级联保存生成的sql语句:

Hibernate: 
    insert 
    into
        t_teacher
        (name, salary) 
    values
        (?, ?)
Hibernate: 
    insert 
    into
        t_car
        (price, teacher_id, type) 
    values
        (?, ?, ?)
Hibernate: 
    insert 
    into
        t_car
        (price, teacher_id, type) 
    values
        (?, ?, ?)
Hibernate: 
    insert 
    into
        t_car
        (price, teacher_id, type) 
    values
        (?, ?, ?)


级联查询

@Test
public void one2many_select(){
    Teacher teacher = teacherRepository.findById(1L).orElse(null);
    System.out.println(teacher);
    System.out.println(teacher.getCars());
}

生成的sql语句

在这里插入图片描述

运行结果:

在这里插入图片描述

@OneToMany(mappedBy = “teacher”,cascade = CascadeType.ALL,fetch = FetchType.EAGER)该配置中**fetch = FetchType.EAGER)**表示当前关联关系为实时加载

@OneToMany(mappedBy = “teacher”,cascade = CascadeType.ALL,fetch = FetchType.LAZY)该配置中**fetch = FetchType.LAZY)**表示当前关联关系为懒加载

注意,当使用延迟加载的时候,我们默认获取到的是一个代理对象,在真正使用这个对象的时候,才会发出sql查询数据库中的数据。可以使用 getClass 方法来看到这个代理对象真正的运行时类型是什么。

例如:修改fetch = FetchType.LAZY,然后再来执行对应的测试方法,这时候会报错:


Teacher

@OneToMany(mappedBy = "teacher",cascade = CascadeType.ALL,fetch = FetchType.LAZY)
private List<Car> cars = new ArrayList<>();

测试类:

@Test
public void test_one2many_select(){
    Teacher teacher = teacherRepository.findById(1L).orElse(null);
    System.out.println(teacher);
    System.out.println(teacher.getCars());
}

执行结果报错:

org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: com.hzx.springdatajpa.bean.Teacher.cars, could not initialize proxy – no Session

原因:由于是设置了懒加载,那么查询Teacher时所关联出的Car对象是一个代理对象(proxy),而这个代理对象在我们使用的时候(上面代码的第6行,也就说注释掉第6行后代码是不报错的),需要发出sql语句,去查询真正的数据,但是在JPA中执行sql时候,一定需要当前有session的支持,但是这时候,session已经关闭了,默认情况下当前我们执行完JPA的操作(findById方法)时候就关闭了。

**修改方法:**在这里其实只要在测试方法上加上 @Transactional 注解,就可以保证该测试方法在执行完之前都是有事务管理的,这时候session就不会执行完 findById 而自动关闭了。

@Transactional
@Test
public void test_one2many_select(){
    Teacher teacher = teacherRepository.findById(1L).orElse(null);
    System.out.println(teacher);
    System.out.println(teacher.getCars());
}

生成对应的sql语句和执行结果:

在这里插入图片描述


级联删除:

@Test
public void one2many_delete(){
    Teacher teacher = teacherRepository.findById(1L).orElse(null);
    teacherRepository.delete(teacher);
}

生成的sql语句:

Hibernate: 
    select
        teacher0_.id as id1_3_0_,
        teacher0_.name as name2_3_0_,
        teacher0_.salary as salary3_3_0_ 
    from
        t_teacher teacher0_ 
    where
        teacher0_.id=?
Hibernate: 
    select
        teacher0_.id as id1_3_0_,
        teacher0_.name as name2_3_0_,
        teacher0_.salary as salary3_3_0_ 
    from
        t_teacher teacher0_ 
    where
        teacher0_.id=?
Hibernate: 
    select
        cars0_.teacher_id as teacher_4_1_0_,
        cars0_.id as id1_1_0_,
        cars0_.id as id1_1_1_,
        cars0_.price as price2_1_1_,
        cars0_.teacher_id as teacher_4_1_1_,
        cars0_.type as type3_1_1_ 
    from
        t_car cars0_ 
    where
        cars0_.teacher_id=?
Hibernate: 
    delete 
    from
        t_car 
    where
        id=?
Hibernate: 
    delete 
    from
        t_car 
    where
        id=?
Hibernate: 
    delete 
    from
        t_car 
    where
        id=?
Hibernate: 
    delete 
    from
        t_teacher 
    where
        id=?

级联删除时,在删除老师的数据的同时也级联把老师关联的汽车数据也删除了



三、N对N关系

假设一个玩家可以玩啊多个游戏,一个游戏也可以有多个玩家,那么玩家和游戏的关系就是N对N

N对N关系中,数据库中需要有第三张

桥表

来关联另外俩张表。


Player

package com.hzx.springdatajpa.bean;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "t_player")
public class Player {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    /**
     * 多对多,一般不设置任何级联操作
     * @ManyToMany 表示当前类Player和对方Game之间是多对多关系
     * @JoinTable 表示多对多关系的设计中,第三张桥表的相关信息
     *          name = "player_game" 表示桥表的名字为player_game
     *          joinColumns = @JoinColumn(name = "player_id")
     *              表示桥表中的player_id为外键列,并引用自己Player的主键值
     *          inverseJoinColumns = @JoinColumn(name = "game_id")
     *              表示桥表中的game_id为外键列,并引用对方Game的主键值
     */
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "player_game",
            joinColumns = @JoinColumn(name = "player_id"),
            inverseJoinColumns = @JoinColumn(name = "game_id")
    )
    private List<Game> gameList = new ArrayList<>();

    public Player() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Game> getGameList() {
        return gameList;
    }

    public void setGameList(List<Game> gameList) {
        this.gameList = gameList;
    }

}


Game

package com.hzx.springdatajpa.bean;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "t_game")
public class Game {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    /**
     * mappedBy属性,在双向关联中必定会出现
     * mappedBy 表示 外键的配置/维护 由对方来处理
     * 如果不配置mappedBy,则默认表示外键的配置/维护 由我方来处理
     *
     * mappedBy = "gameList" 表示我方Game和对方Player之间关系的维护
     * 由对方Player的属性gameList来进行配置/维护
     */
    @ManyToMany(mappedBy = "gameList")
    private List<Player> playerList = new ArrayList<>();

    public Game() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Player> getPlayerList() {
        return playerList;
    }

    public void setPlayerList(List<Player> playerList) {
        this.playerList = playerList;
    }

}


PlayerRepository

package com.hzx.springdatajpa.repository;

import com.hzx.springdatajpa.bean.Player;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface PlayerRepository extends JpaRepository<Player,Long> {
}


GameRepository

package com.hzx.springdatajpa.repository;

import com.hzx.springdatajpa.bean.Game;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface GameRepository extends JpaRepository<Game,Long> {
}


测试类

@Autowired
private PlayerRepository playerRepository;

@Autowired
private GameRepository gameRepository;

@Test
public void test_many2many_insert(){
    Player player2 = new Player();
    player2.setName("xiaoming");

    Player player1 = new Player();
    player1.setName("xiaowang");

    Game game3 = new Game();
    game3.setName("lanqiu");

    Game game1 = new Game();
    game1.setName("zuqiu");

    Game game2 = new Game();
    game2.setName("paiqiu");

    List<Game> gameList1 = new ArrayList<>();
    gameList1.add(game3);
    gameList1.add(game1);
    gameList1.add(game2);

    List<Game> gameList2 = new ArrayList<>();
    gameList2.add(game3);
    gameList2.add(game1);

    player1.setGameList(gameList1);
    player2.setGameList(gameList2);
    //先保存Game再保存Player,因为关联关系由Player维护
    gameRepository.save(game1);
    gameRepository.save(game2);
    gameRepository.save(game3);
    
    playerRepository.save(player1);
    playerRepository.save(player2);
}

在这个操作中,要注意先保存game,然后再保存player,因为是让player关联的game

控制台输出的建表语句为:

Hibernate: 
    
    create table player_game (
       player_id bigint not null,
        game_id bigint not null
    ) engine=InnoDB
Hibernate: 
    
    create table t_game (
       id bigint not null auto_increment,
        name varchar(255),
        primary key (id)
    ) engine=InnoDB
Hibernate: 
    
    create table t_player (
       id bigint not null auto_increment,
        name varchar(255),
        primary key (id)
    ) engine=InnoDB
Hibernate: 
    
    alter table player_game 
       add constraint FKijjwgqv8y2mdiwxhk2i56e4jy 
       foreign key (game_id) 
       references t_game (id)
Hibernate: 
    
    alter table player_game 
       add constraint FKpc16kqwtroqa8jcwu91f5204p 
       foreign key (player_id) 
       references t_player (id)

插入数据操作对应的SQL语句为:

Hibernate: 
    insert 
    into
        t_game
        (name) 
    values
        (?)
Hibernate: 
    insert 
    into
        t_game
        (name) 
    values
        (?)
Hibernate: 
    insert 
    into
        t_game
        (name) 
    values
        (?)
Hibernate: 
    insert 
    into
        t_player
        (name) 
    values
        (?)
Hibernate: 
    insert 
    into
        player_game
        (player_id, game_id) 
    values
        (?, ?)
Hibernate: 
    insert 
    into
        player_game
        (player_id, game_id) 
    values
        (?, ?)
Hibernate: 
    insert 
    into
        player_game
        (player_id, game_id) 
    values
        (?, ?)
Hibernate: 
    insert 
    into
        t_player
        (name) 
    values
        (?)
Hibernate: 
    insert 
    into
        player_game
        (player_id, game_id) 
    values
        (?, ?)
Hibernate: 
    insert 
    into
        player_game
        (player_id, game_id) 
    values
        (?, ?)

最后数据库表中的执行结果:

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述


级联查询

@Test
public void test_many2many_select(){
    Player player = playerRepository.findById(1L).orElse(null);
    System.out.println(player);
    System.out.println(player.getGameList());
}

生成的sql语句以及运行结果:

在这里插入图片描述


删除操作

@Test
public void test_many2many_delete(){
    Player player = playerRepository.findById(2L).orElse(null);
    playerRepository.delete(player);
}

生成对应的sql语句:

Hibernate: 
    select
        player0_.id as id1_4_0_,
        player0_.name as name2_4_0_,
        gamelist1_.player_id as player_i1_0_1_,
        game2_.id as game_id2_0_1_,
        game2_.id as id1_3_2_,
        game2_.name as name2_3_2_ 
    from
        t_player player0_ 
    left outer join
        player_game gamelist1_ 
            on player0_.id=gamelist1_.player_id 
    left outer join
        t_game game2_ 
            on gamelist1_.game_id=game2_.id 
    where
        player0_.id=?
Hibernate: 
    select
        player0_.id as id1_4_0_,
        player0_.name as name2_4_0_,
        gamelist1_.player_id as player_i1_0_1_,
        game2_.id as game_id2_0_1_,
        game2_.id as id1_3_2_,
        game2_.name as name2_3_2_ 
    from
        t_player player0_ 
    left outer join
        player_game gamelist1_ 
            on player0_.id=gamelist1_.player_id 
    left outer join
        t_game game2_ 
            on gamelist1_.game_id=game2_.id 
    where
        player0_.id=?
Hibernate: 
    delete 
    from
        player_game 
    where
        player_id=?
Hibernate: 
    delete 
    from
        t_player 
    where
        id=?

运行后数据库表中的结果为:

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

以上代码在https://gitee.com/huangzhixiong2021/spring-data-jpa.git 中,有不足或者错误的地方欢迎指正。



版权声明:本文为weixin_43521932原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。