时间戳

首页 > 解决方案 > 尝试从 Spring Boot 读取 json 数据并将其写入 psotgres 数据库

java - 尝试从 Spring Boot 读取 json 数据并将其写入 psotgres 数据库

问题描述

您好，我正在尝试将 json 文件中的数据添加到我的本地 Postgres 数据库，但没有运气。我不断收到这个错误，我创建了employee.json，但仍然没有让它运行，任何人都可以帮助解决这个问题。我按照https://www.danvega.dev/blog/2017/07/05/read-json-data-spring-boot-write-database/上的教程进行操作*

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "employee" violates foreign key constraint "department_id"
  Detail: Key (department_id)=(1) is not present in table "department".
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) ~[postgresql-42.2.14.jar:42.2.14]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) ~[postgresql-42.2.14.jar:42.2.14]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) ~[postgresql-42.2.14.jar:42.2.14]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) ~[postgresql-42.2.14.jar:42.2.14]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) ~[postgresql-42.2.14.jar:42.2.14]
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153) ~[postgresql-42.2.14.jar:42.2.14]
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:119) ~[postgresql-42.2.14.jar:42.2.14]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:na]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197) ~[hibernate-core-5.4.18.Final.jar:5.4.18.Final]
    ... 78 common frames omitted

这是我的代码

雇员.JAVA

Table
@Entity
public class Employee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "employee_id")
    @NotNull
    private Long id;

    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="department_id")
    @NotNull
    private Long department;

    @Column(name="team_id")
    @NotNull
    private Long team;

    @Column(name="policy_id")
    @NotNull
    private Long policy;

    @Column(name = "first_name")
    @NotNull
    private String firstName;

    @Column(name = "last_name")
    @NotNull
    private String lastName;

    @Column(name = "GMID")
    @NotNull
    private String GMID;

    @Column(name = "GMIN", nullable = false)
    @NotNull
    private Long GMIN;

    @Column(name = "policy_overrides")
    @NotNull
    private String policyOverrides;

    @Column(name = "holiday_details")
    @NotNull
    private String holidayDetails;


    @JsonDeserialize(using = LocalDateDeserializer.class)
    @JsonSerialize(using = LocalDateSerializer.class)
    @JsonFormat(pattern="yyyy-MM-dd")
    @Column(name = "date_started")
    @NotNull
    private LocalDate dateStarted;

    @Column(name = "email_address")
    @NotNull
    private String emailAddress;

    public Employee() {
    }

    public Employee(Long department, Long team, Long policy, String firstName, String lastName, String GMID, Long GMIN, String policyOverrides, String holidayDetails, LocalDate dateStarted, String emailAddress) {
        this.department = department;
        this.team = team;
        this.policy = policy;
        this.firstName = firstName;
        this.lastName = lastName;
        this.GMID = GMID;
        this.GMIN = GMIN;
        this.policyOverrides = policyOverrides;
        this.holidayDetails = holidayDetails;
        this.dateStarted = dateStarted;
        this.emailAddress = emailAddress;
    }


    // For updating existing column in database
    public Employee(Long id, Long department, Long team, Long policy, String firstName, String lastName, String GMID, Long GMIN, String policyOverrides, String holidayDetails, LocalDate dateStarted, String emailAddress){
        this.id = id;
        this.department = department;
        this.team = team;
        this.policy = policy;
        this.firstName = firstName;
        this.lastName = lastName;
        this.GMID = GMID;
        this.GMIN = GMIN;
        this.policyOverrides = policyOverrides;
        this.holidayDetails = holidayDetails;
        this.dateStarted = dateStarted;
        this.emailAddress = emailAddress;
    }

    public Long getId() {
        return id;
    }

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

    public Long getDepartment() {
        return department;
    }

    public void setDepartment(Long department) {
        this.department = department;
    }

    public Long getTeam() {
        return team;
    }

    public void setTeam(Long team) {
        this.team = team;
    }

    public Long getPolicy() {
        return policy;
    }

    public void setPolicy(Long policy) {
        this.policy = policy;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGMID() {
        return GMID;
    }

    public void setGMID(String GMID) {
        this.GMID = GMID;
    }

    public Long getGMIN() {
        return GMIN;
    }

    public void setGMIN(Long GMIN) {
        this.GMIN = GMIN;
    }

    public String getHolidayDetails() {
        return holidayDetails;
    }

    public void setHolidayDetails(String holidayDetails) {
        this.holidayDetails = holidayDetails;
    }

    public LocalDate getDateStarted() {
        return dateStarted;
    }

    public void setDateStarted(LocalDate date_started) {
        this.dateStarted = date_started;
    }

    public String getPolicyOverrides() {
        return policyOverrides;
    }

    public void setPolicyOverrides(String policyOverrides) {
        this.policyOverrides = policyOverrides;
    }

    public String getEmailAddress() {
        return emailAddress;
    }

    public void setEmailAddress(String emailAddress) {
        this.emailAddress = emailAddress;
    }

    @Override
    public String toString() {
        return "{" +
                "\"id\":" + id +
                ", \"department\":" + department +
                ", \"team\":" + team +
                ", \"policy\":" + policy +
                ", \"firstName\":\"" + firstName + "\"" +
                ", \"lastName\":\"" + lastName + "\"" +
                ", \"email\":\"" + emailAddress + "\"" +
                ", \"GMID\":\"" + GMID + "\"" +
                ", \"GMIN\":" + GMIN + "" +
                ", \"policyOverrides\":\"" + policyOverrides + "\"" +
                ", \"holidayDetails\":\"" + holidayDetails + "\"" +
                ", \"dateStarted\":\"" + dateStarted + "\"" +
                '}';
    }

}

部门.JAVA

package com.gm.absencetracker.models;

import com.fasterxml.jackson.annotation.JsonProperty;

import javax.persistence.*;
import javax.validation.constraints.NotNull;
import java.io.Serializable;

@Table
@Entity
public class Department implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "department_id")
    @NotNull
    @JsonProperty("id")
    private Long id;


    @Column(name = "department_name")
    @NotNull
    @JsonProperty("departmentName")
    private String departmentName;


    @Column(name = "first_manager")
    @NotNull
    @JsonProperty("firstManager")
    private String firstManager;

    
    @Column(name = "alternative_manager")
    @NotNull
    @JsonProperty("alternativeManager")
    private String alternativeManager;

    public Department() {
    }

    public Department(String departmentName, String firstManager, String alternativeManager ) {
        this.departmentName = departmentName;
        this.firstManager = firstManager;
        this.alternativeManager = alternativeManager;
    }

    // For updating existing column in database
    public Department(Long id, String departmentName, String firstManager, String alternativeManager ) {
        this.id = id;
        this.departmentName = departmentName;
        this.firstManager = firstManager;
        this.alternativeManager = alternativeManager;
    }

    public Long getId() {
        return id;
    }

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

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public String getFirstManager() {
        return firstManager;
    }

    public void setFirstManager(String firstManager) {
        this.firstManager = firstManager;
    }

    public String getAlternativeManager() {
        return alternativeManager;
    }

    public void setAlternativeManager(String alternativeManager) {
        this.alternativeManager = alternativeManager;
    }

    @Override
    public String toString() {
        return "{" +
                "\"id\":" + id +
                ", \"departmentName\":\"" + departmentName + "\"" +
                ", \"firstManager\":\"" + firstManager + "\"" +
                ", \"alternativeManager\":\"" + alternativeManager + "\"" +
                '}';
    }
}

员工.json

[
    {
      "id": 1,
      "department": 1,
      "team": 1,
      "policy": 1,
      "firstName": "Laxmivaraprasad",
      "lastName": "Ale",
      "gmid": "HZDT3V",
      "gmin": "475609900",
      "policyOverrides": "",
      "holidayDetails":"",
      "dateStarted": "2020-07-13",
      "emailAddress": "laxmivaraprasad.1.ale@gm.com"
    },
    {
      "id": 1,
      "department":1,
      "team": 1,
      "policy": 1,
      "firstName": "Michael",
      "lastName": "Aroyehun",
      "gmid": "KZV0FP",
      "gmin": "246873348",
      "policyOverrides": "",
      "holidayDetails":"",
      "dateStarted": "2020-07-13",
      "emailAddress": "michael.o.aroyehun@gm.com"
    }
]

MainApplication.java

package com.gm.absencetracker;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import com.gm.absencetracker.models.Employee;
import com.gm.absencetracker.repository.EmployeeRepository;
import com.gm.absencetracker.service.EmployeeService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.annotation.Bean;
import org.springframework.context.event.EventListener;

import javax.annotation.PostConstruct;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

@SpringBootApplication
public class AbsenceTrackerServiceApplication {

    private final static Logger LOGGER = LoggerFactory.getLogger(AbsenceTrackerServiceApplication.class);

    @Autowired
    private FakeDataFactory fakeDataFactory;

    @Autowired
    private EmployeeService employeeService;

    @Autowired
    private EmployeeRepository employeeRepository;
    @Autowired
    private ObjectMapper objectMapper;

    public static void main(String[] args) {
        LOGGER.info("Application is running");
        SpringApplication.run(AbsenceTrackerServiceApplication.class, args);
    }

    @PostConstruct
    public void setUp() {

        objectMapper.registerModule(new JavaTimeModule());
    }

    @EventListener(ApplicationReadyEvent.class)
    public void addData() {
        fakeDataFactory.addPolicys();
        fakeDataFactory.addDepartments();
        fakeDataFactory.addTeams();
        //fakeDataFactory.addEmployees();
        fakeDataFactory.addHolidays();
    }

    @Bean
    CommandLineRunner runner(EmployeeService employeeService) {
        return args -> {
            // read json and write to db
            ObjectMapper mapper = new ObjectMapper();
            TypeReference<List<Employee>> typeReference = new TypeReference<>() {
            };
            InputStream inputStream = TypeReference.class.getResourceAsStream("/json/employee.json");
            try {
                List<Employee> users = mapper.readValue(inputStream, typeReference);

                employeeService.saveAll(users);

                System.out.println("Users Saved!");
            } catch (IOException e) {
                System.out.println("Unable to save users: " + e.getMessage());
            }
        };

    }
}

标签： javapostgresqlspring-boot

解决方案

TL;DR： addData()在CommandLineRunner. 将事件更改为ApplicationStartedEvent。

如果您查看 的源代码SpringApplication，您将看到事件的顺序。

事件的顺序是：

  1. 事件： ApplicationStartingEvent
  2. 记录“正在启动...应用程序...”
  3. 事件： ContextRefreshedEvent
  4. 记录“已开始...应用程序...秒...”
  5. 事件： ApplicationStartedEvent
  6. 执行CommandLineRunnerbean
  7. 事件： ApplicationReadyEvent
  8. 退货，可能main()

这意味着您addData()在.CommandLineRunner

解决方案 1：更改为@EventListener(ApplicationStartedEvent.class)使其在CommandLineRunner.

解决方案 2：将 中的代码移动CommandLineRunner到main()方法中。

以下是 的缩写源代码SpringApplication.run()：

public ConfigurableApplicationContext run(String... args) {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    // ...
    listeners.starting(); // #1
    try {
        // ...
        Banner printedBanner = printBanner(environment); // #2
        context = createApplicationContext(); // #3
        // ...
        stopWatch.stop();
        if (this.logStartupInfo) { // #4
            new StartupInfoLogger(this.mainApplicationClass).logStarted(getApplicationLog(), stopWatch);
        }
        listeners.started(context); // #5
        callRunners(context, applicationArguments); // #6
    }
    catch (Throwable ex) {
        // ...
    }

    try {
        listeners.running(context); // #7
    }
    catch (Throwable ex) {
        // ...
    }
    return context; // #8
}

推荐阅读