How to import data from one table to another – SpringBoot JPA /MySQL

I recently created a vehicle management system The system is derived from MySQL database and server side in spring I want to create another table (automatically at runtime) that will display only 2 of the columns of the existing table.

And the question is what am I doing wrong? Final goal – when adding / deleting / editing a vehicle, both tables will work in sync and without collisions I would be happy for your help

Below is the “Car” class

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;
    }
}
 

And CarType class which need only to create another MySQL table with the related columns (car_id and car_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;
    }
}

Answer

• Instead of checking and managing flow on the server-side. Just create Replicated Audit Table and making 3 Triggers on the original table like “After Insert, After Update, and After Delete Trigger at MySQL level.

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

read more about trigger Visit https://www.mysqltutorial.org/mysql-triggers.aspx

• as @scaisEdge said also create a view from the table and implement that on your spring project.

car_detailed_view.sql [View]

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 [View Class]

@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
}