java - PSQL异常 : ERROR: insert or update on table "*" violates foreign key constraint

Question

我尝试将所有数据从一个数据库插入到另一个数据库。这两个数据库具有相同的结构(它们使用相同的实体)。我使用 PostgreSQL 9.1、Glassfish 4.0、EclipseLink (JPA 2.1) 和 Java EE 7 Web。

这些是实体:

@Entity
public class Store extends BaseEntity {

    @NotNull
    private String name;

   /*
    * required
    */
    @OneToMany(cascade = CascadeType.PERSIST)
    private List<Price> listPrices;

   /*
    * required
    */
    @OneToMany(cascade = CascadeType.PERSIST)
    private List<BusinessHours> listBusinessHours;

    @OneToOne(cascade = CascadeType.PERSIST, optional = false)
    private PointCoordinates pointCoordinates;
    ...
}

例如@OneToMany 注释实体之一:

@Entity
public class BusinessHours extends BaseEntity {

    private Boolean holiday;

    ...
}

BaseEntity 包含带有 GenerationType.SEQUENCE 的 Id 和 serialVersionUID。

持久性.xml:

<persistence-unit name="restDBFromRawData" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/RestDBFromRawData</jta-data-source>

    <class>info.mycompany.entities.BusinessHours</class>
    <class>info.mycompany.entities.Store</class>
    <class>info.mycompany.entities.PointCoordinates</class>
    <class>info.mycompany.entities.Price</class>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
    </properties>
  </persistence-unit>

<persistence-unit name="restClientDB" transaction-type="JTA">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/RestClientDB</jta-data-source>

    <class>info.mycompany.entities.BusinessHours</class>
    <class>info.mycompany.entities.Store</class>
    <class>info.mycompany.entities.PointCoordinates</class>
    <class>info.mycompany.entities.Price</class>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
      <property name="eclipselink.ddl-generation.output-mode" value="both"/>
    </properties>
   </persistence-unit>

如果我尝试从一个数据库获取所有数据并尝试将它们插入另一个数据库 (addStoresToRestClientDB):

@Stateless
public class StoreOutputService {

    @EJB
    StoreOutputDAO storeOutputDAO;

    @EJB
    StoreOutputRestClientDAO storeOutputRCDAO;

    public List<Store> getAllStores() {
        return storeOutputDAO.findAll();
    }

    public void addStoresToRestDBFromRawData() {
        //this works fine
        ...
        for (Store store : listStore) {
            storeOutputDAO.edit(store);
        }
    }

    public void addStoresToRestClientDB() {

        List<Store> listStore = getAllStores();
        //size of list is right, the same like I insert in "addStoresToRestDBFromRawData"

        for (Store store : listStore) {
            storeOutputRCDAO.edit(store);
        }
    }
}

我收到以下错误:

WARNING:   Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: insert or update on table "store_businesshours" violates foreign key constraint "fk_store_businesshours_listbusinesshours_id"
  Detail: Key (listbusinesshours_id)=(55) is not present in table "businesshours".
Error Code: 0
Call: INSERT INTO STORE_BUSINESSHOURS (listBusinessHours_ID, Store_ID) VALUES (?, ?)
    bind => [2 parameters bound]
Query: DataModifyQuery(name="listBusinessHours" sql="INSERT INTO STORE_BUSINESSHOURS (listBusinessHours_ID, Store_ID) VALUES (?, ?)")
...
Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "store_businesshours" violates foreign key constraint "fk_store_businesshours_listbusinesshours_id"
  Detail: Key (listbusinesshours_id)=(55) is not present in table "businesshours".
...
WARNING:   StandardWrapperValve[info.mycompany.web.ApplicationConfig]: Servlet.service() for servlet info.mycompany.web.ApplicationConfig threw exception
org.postgresql.util.PSQLException: ERROR: insert or update on table "store_businesshours" violates foreign key constraint "fk_store_businesshours_listbusinesshours_id"
  Detail: Key (listbusinesshours_id)=(55) is not present in table "businesshours".

DAO 应该没问题。 StoreOutputDAO 使用持久化单元 restDBFromRawData 而 StoreOutputRestClientDAO 使用持久化单元 restClientDB。

JDBC jdbc/RestDBFromRawData 使用连接池与 javax.sql.ConnectionPoolDataSource ressourcetype (classname: org.postgresql.ds.PGConnectionPoolDataSource) 和其他 JDBC "jdbc/RestClientDB”使用“javax.sql.XADataSource”资源类型(类名:org.postgresql.xa.PGXADataSource)。如果我在“jdbc/RestClientDB”中采用与在“jdbc/RestDBFromRawData”中相同的配置，我会收到以下错误消息:“本地事务已经有 1 个非 XA 资源”。

$GLASSFISH_HOME/glassfish/lib 和 $GLASSFISH_HOME/domains/domain1/lib 包含 PostgreSQL 驱动程序“postgresql-9.1-901.jdbc4.jar”。

如果我在数据库中使用 pgAdmin 查看，一切看起来都很好。它们具有相同的结构，其他一切正常:将数据插入另一个数据库并使用 StoreOutputDAO 插入数据，但 StoreOutputRestClientDAO 除外。

更新基础实体:

@MappedSuperclass
public abstract class BaseEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private int id;

    public int getId() {
        return id;
    }

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

Answer 1

错误发生是因为其他数据库中不存在营业时间。确保插入它。

你在上面调用persist，它的Id是怎么定义的？将其迁移到其他数据库时，您可能需要清空其 Id 和版本。