如何将数据从一个表导入到另一个表-springboot jpa/mysql

bnlyeluc  于 2021-07-23  发布在  Java
关注(0)|答案(1)|浏览(413)

我最近创建了一个车辆管理系统,这个系统是从mysql数据库和服务器端派生的,在spring中我想创建另一个表(在运行时自动),它只显示现有表中的两列。
问题是我做错了什么?最终目标-当添加/删除/编辑车辆时,两个表将同步工作,并且不会发生碰撞我将很高兴得到您的帮助
下面是“汽车”类

import javax.persistence.*;
import java.time.LocalDate;

@Entity
@Table(name = "car")
public class Car {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long carId;

    private String licensePlate;

    private int carType;

    private boolean suv;

    private int engineCapacity;

    private int year;

    private String note;

    private int status;

    private LocalDate careDate;

    private LocalDate editDate;

    public Car() {
    }

    public Car(long carId) {
        this.carId = carId;
    }

    public long getCarId() {
        return carId;
    }

    public void setCarId(long carId) {
        this.carId = carId;
    }

    public String getLicensePlate() {
        return licensePlate;
    }

    public void setLicensePlate(String licensePlate) {
        this.licensePlate = licensePlate;
    }

    public int getCarType() {
        return carType;
    }

    public void setCarType(int carType) {
        this.carType = carType;
    }

    public boolean isSuv() {
        return suv;
    }

    public void setSuv(boolean SUV) {
        this.suv = SUV;
    }

    public int getEngineCapacity() {
        return engineCapacity;
    }

    public void setEngineCapacity(int engineCapacity) {
        this.engineCapacity = engineCapacity;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public LocalDate getCareDate() {
        return careDate;
    }

    public void setCareDate(LocalDate careDate) {
        this.careDate = careDate;
    }

    public LocalDate getEditDate() {
        return editDate;
    }

    public void setEditDate(LocalDate editDate) {
        this.editDate = editDate;
    }
}

cartype类只需要创建另一个mysql表和相关列(car\u id和car\u type)

package com.example.CarSystemMatanElbaz.model;

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

@Entity
public class CarType {

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

    @OneToOne(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name= "car_id")
    private Car carId;

    @OneToOne(cascade = CascadeType.ALL,fetch = FetchType.LAZY,orphanRemoval = true)
    @JoinColumn(name= "car_type")
    private Car carType;

    public CarType() {

    }

    public CarType(long id, Car carId, Car carType) {
        this.id = id;
        this.carId = carId;
        this.carType = carType;
    }

    public long getId() {
        return id;
    }

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

    public Car getCarId() {
        return carId;
    }

    public void setCarId(Car carId) {
        this.carId = carId;
    }

    public Car getCarType() {
        return carType;
    }

    public void setCarType(Car carType) {
        this.carType = carType;
    }
}
mdfafbf1

mdfafbf11#

而不是在服务器端检查和管理流。只需创建复制的审计表,并在原始表上生成3个触发器,如mysql级别的“after insert、after update和after delete触发器”。

CREATE  TRIGGER `db`.`car_AFTER_INSERT` AFTER INSERT ON `trn_student_misc_fees_req_status` FOR EACH ROW
BEGIN
  # INSERT Query of Another table using 'NEW' Keyword with car table fields. 
END
CREATE  TRIGGER `db`.`car_AFTER_UPDATE` AFTER UPDATE ON `trn_student_misc_fees_req_status` FOR EACH ROW
BEGIN
  # UPDATE Query of Another table using 'NEW' Keyword with car table fields. 
END
CREATE  TRIGGER `db`.`car_AFTER_DELETE` AFTER DELETE ON `trn_student_misc_fees_req_status` FOR EACH ROW
BEGIN
  # DELETE Query of Another table using 'OLD' Keyword with car table fields. 
END

阅读更多关于触发访问的信息https://www.mysqltutorial.org/mysql-triggers.aspx
正如@scaisedge所说,还可以从表中创建一个视图,并在spring项目中实现它。
汽车详细视图.sql[视图]

CREATE 
    ALGORITHM = UNDEFINED  
    SQL SECURITY DEFINER
VIEW `car_detailed_view` AS
    SELECT 
    car.carId,car.licensePlate,car.carType,car.suv,car.engineCapacity,car.year,car.note,car.status,car.careDate,car.editDate;
    FROM
        (`car`
        INNER JOIN `CarType`  ON ((`car`.`car_id` = `CarType`.`car_id`)))

cardetailedview.java[视图类]

@Immutable
@Entity
@Table(name = "car_detailed_view")
public class CarDetailedView{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long carId;

    private String licensePlate;

    private int carType;

    private boolean suv;

    private int engineCapacity;

    private int year;

    private String note;

    private int status;

    private LocalDate careDate;

    private LocalDate editDate;

   //getter,setter and constructor
}

相关问题