JPA双库同步异常：主键不可更新+字段值为空求助

维护老旧JPA项目时，原Derby数据库可正常推送LocalCertificate实体，新增PostgreSQL同步后遇到两个问题：

1. 更新操作报错：The attribute [id] of class 'LocalCertificate' is mapped to a primary key column in the database. Updates are not allowed
2. PostgreSQL中d_id和certificate字段显示为NULL

相关代码 #

实体类LocalCertificate #

@Table(name = "certs")
public class LocalCertificate implements Serializable {
    private static final long serialVersionUID = -5003848691574858779L;

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

    @Expose
    @Column(name = "d_id")
    private String d_id;
    
    @Expose
    @Column(name = "certificate", columnDefinition="clob")
    @Convert()
    @Lob
    private X509CertificateHolder certificate;
    
    @Expose
    @Column(name = "revoked")
    public boolean revoked = false;
    
    public byte[] getCertDER() throws IOException {
        return certificate.getEncoded();
    }
    
    
    public long getId() {
        return id.longValue();
    }
    
    public String getDId() {
        return d_id;
    }
    
    public void store() {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("LDB");
        EntityManager locEm = emf.createEntityManager();

        EntityTransaction ta = locEm.getTransaction();
        ta.begin();
        locEm.persist(this);
        ta.commit();
        
        // for Postgres
        EntityManagerFactory eemf = Persistence.createEntityManagerFactory("PU_CSA");
        EntityManager llocEm = eemf.createEntityManager();

        EntityTransaction tta = llocEm.getTransaction();
        tta.begin();
        llocEm.persist(this);
        tta.commit();
    }
    
    public void update() {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("LDB");
        EntityManager locEm = emf.createEntityManager();

        EntityTransaction ta = locEm.getTransaction();
        
        ta.begin();
        locEm.merge(this);
        ta.commit();
        
        // for postgres
        EntityManagerFactory eemf = Persistence.createEntityManagerFactory("PU_CSA");
        EntityManager llocEm = eemf.createEntityManager();

        EntityTransaction tta = llocEm.getTransaction();
        
        tta.begin();
        llocEm.merge(this);
        ta.commit();
    }
    
    public void setD_id(String dId) {
        // TODO Auto-generated method stub
        this.d_id =  dId;
    }


    public void setCertificate(X509CertificateHolder cert) {
        // TODO Auto-generated method stub
        this.certificate = cert;        
    }
}

persistence.xml配置 #

<persistence-unit name="LDB">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <non-jta-data-source>java:comp/env/jdbc/LDB</non-jta-data-source>
        <class>org.backend.dao.LocalCertificate</class>
        <class>org.backend.certserver.jpa.LocalCertificateAdapter</class>
        
        <properties>
            <!-- EclipseLink should create the database schema automatically -->
            <property name="eclipselink.ddl-generation" value="create-or-extend-tables" />
            <property name="eclipselink.ddl-generation.output-mode" value="database" />
        </properties>
    </persistence-unit>

    <persistence-unit name="PU_CSA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <non-jta-data-source>java:comp/env/jdbc/CSA</non-jta-data-source>
        <class>org.backend.dao.Device</class>
        <class>org.backend.dao.LocalCertificate</class>
        <class>org.backend.certserver.jpa.LocalCertificateAdapter</class>
        <properties>
            <!-- EclipseLink should create the database schema automatically -->
            <property name="eclipselink.ddl-generation" value="create-or-extend-tables" />
            <property name="eclipselink.ddl-generation.output-mode" value="database" /> 
        </properties>
    </persistence-unit>

出错的Servlet代码片段 #

private void processDevCertRequest(HttpServletResponse response, String devId) throws IOException {

        LocalCertificate newCert = new LocalCertificate();
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("LDB");
        EntityManager locEm = emf.createEntityManager();

        EntityTransaction ta = locEm.getTransaction();
        ta.begin();
        locEm.persist(newCert);
        ta.commit();
        
        // postgres
        EntityManagerFactory posemf = Persistence.createEntityManagerFactory("PU_CSA");
        EntityManager posEm = posemf.createEntityManager();
        logger.info(newCert.toString());
        EntityTransaction posta = posEm.getTransaction();
        posta.begin();
        posEm.persist(newCert);
        posta.commit();
        
        BigInteger certID = BigInteger.valueOf(newCert.getId());

        CertificateAuthorithyManager cam = CertificateAuthorithyManager.getInstance();
        KeyPairCert kpc = cam.generateSignedKeyPairCert(devId, certID);

        String pemcert = CAServletHelper.generatePEMBlock(kpc.cert);
        String pemkey = CAServletHelper.generatePEMBlock(kpc.keyPair.getPrivate());
        

        ByteArrayOutputStream byteOutput = new ByteArrayOutputStream();
        ZipOutputStream zos = new ZipOutputStream(byteOutput);
        zos.putNextEntry(new ZipEntry(devId + "_cert.pem"));
        zos.write(pemcert.getBytes(), 0, pemcert.length());
        zos.closeEntry();
        zos.putNextEntry(new ZipEntry(devId + "_key.pem"));
        zos.write(pemkey.getBytes(), 0, pemkey.length());
        zos.closeEntry();
        zos.close();

        newCert.setDev_id(devId);
        newCert.setCertificate(kpc.cert);
        ta.begin();
        locEm.merge(newCert);
        ta.commit();            <==== 此处执行失败
        
        // postgress
        newCert.setDev_id(devId);
        newCert.setCertificate(kpc.cert);
        posta.begin();
        posEm.merge(newCert);
        posta.commit();

        OutputStream resos = response.getOutputStream();
        response.setContentType("application/zip");
        response.setStatus(HttpServletResponse.SC_CREATED);
        response.setHeader("Content-Disposition", "attachment;filename=\"" + devId + ".zip\"");
        resos.write(byteOutput.toByteArray());
        resos.flush();
        byteOutput.close();
    }

问题1：主键更新报错处理 #

报错核心原因：同一个LocalCertificate实例被先后持久化到Derby和PostgreSQL，Derby生成的主键ID被带入PostgreSQL的merge操作，但PostgreSQL的主键是IDENTITY自增类型，不允许手动修改主键值。

修正步骤： #

1. 拆分跨库实体实例：向PostgreSQL持久化时，创建全新的LocalCertificate实例，仅复制原实例的非ID字段，避免将Derby生成的ID带入：

   // postgres持久化代码修改
   EntityManagerFactory posemf = Persistence.createEntityManagerFactory("PU_CSA");
   EntityManager posEm = posemf.createEntityManager();
   EntityTransaction posta = posEm.getTransaction();
   posta.begin();
   LocalCertificate pgCert = new LocalCertificate();
   pgCert.setD_id(newCert.getDId());
   pgCert.setCertificate(newCert.certificate);
   pgCert.revoked = newCert.revoked;
   posEm.persist(pgCert);
   posta.commit();
   

2. 修正update方法的跨库逻辑：针对PostgreSQL的更新，先从其EntityManager中查询出对应实体，再更新字段（而非直接merge原实例）：

   public void update() {
       // Derby部分保持不变
       EntityManagerFactory emf = Persistence.createEntityManagerFactory("LDB");
       EntityManager locEm = emf.createEntityManager();
       EntityTransaction ta = locEm.getTransaction();
       ta.begin();
       locEm.merge(this);
       ta.commit();
       locEm.close();
       emf.close();
       
       // PostgreSQL部分修改
       EntityManagerFactory eemf = Persistence.createEntityManagerFactory("PU_CSA");
       EntityManager llocEm = eemf.createEntityManager();
       EntityTransaction tta = llocEm.getTransaction();
       tta.begin();
       // 通过d_id查询PostgreSQL中的对应实体
       LocalCertificate pgCert = llocEm.createQuery("SELECT c FROM LocalCertificate c WHERE c.d_id = :dId", LocalCertificate.class)
                                       .setParameter("dId", this.d_id)
                                       .getSingleResult();
       pgCert.setCertificate(this.certificate);
       pgCert.revoked = this.revoked;
       // 托管状态实体无需merge，事务提交自动更新
       tta.commit();
       llocEm.close();
       eemf.close();
   }
   

3. Servlet更新逻辑修复：原代码复用了已提交的事务ta，需要重新获取事务；同时PostgreSQL的更新也要用查询后修改的方式：

   // Derby更新修正
   newCert.setD_id(devId);
   newCert.setCertificate(kpc.cert);
   EntityTransaction newTa = locEm.getTransaction();
   newTa.begin();
   locEm.merge(newCert);
   newTa.commit();
   
   // PostgreSQL更新修正
   EntityTransaction newPosta = posEm.getTransaction();
   newPosta.begin();
   LocalCertificate pgCert = posEm.createQuery("SELECT c FROM LocalCertificate c WHERE c.d_id = :dId", LocalCertificate.class)
                                   .setParameter("dId", devId)
                                   .getSingleResult();
   pgCert.setCertificate(kpc.cert);
   newPosta.commit();
   

问题2：d_id和certificate字段为NULL处理 #

核心原因：

• Servlet中存在方法名拼写错误：实体类只有setD_id方法，但代码中调用了setDev_id，导致d_id字段从未被正确赋值；
• 先持久化空实例再更新的逻辑存在事务复用问题，导致PostgreSQL更新未生效；
• X509CertificateHolder的@Convert注解未指定转换器类，无法正确映射到CLOB字段。

修正步骤： #

1. 修复方法名拼写错误：将Servlet中的newCert.setDev_id(devId);改为newCert.setD_id(devId);。
2. 调整持久化顺序：优先设置必要字段再持久化，避免空实例同步：

   // 先创建实例并设置d_id
   LocalCertificate newCert = new LocalCertificate();
   newCert.setD_id(devId);
   
   // 持久化到Derby
   EntityManagerFactory emf = Persistence.createEntityManagerFactory("LDB");
   EntityManager locEm = emf.createEntityManager();
   EntityTransaction ta = locEm.getTransaction();
   ta.begin();
   locEm.persist(newCert);
   ta.commit();
   
   // 生成证书后更新Derby
   BigInteger certID = BigInteger.valueOf(newCert.getId());
   CertificateAuthorithyManager cam = CertificateAuthorithyManager.getInstance();
   KeyPairCert kpc = cam.generateSignedKeyPairCert(devId, certID);
   newCert.setCertificate(kpc.cert);
   
   ta = locEm.getTransaction();
   ta.begin();
   locEm.merge(newCert);
   ta.commit();
   
   // 同步到PostgreSQL：直接创建完整实例持久化
   EntityManagerFactory posemf = Persistence.createEntityManagerFactory("PU_CSA");
   EntityManager posEm = posemf.createEntityManager();
   EntityTransaction posta = posEm.getTransaction();
   posta.begin();
   LocalCertificate pgCert = new LocalCertificate();
   pgCert.setD_id(devId);
   pgCert.setCertificate(kpc.cert);
   posEm.persist(pgCert);
   posta.commit();
   

3. 配置字段转换器：为X509CertificateHolder实现AttributeConverter，将其转换为数据库可存储的类型（如字符串或字节数组），并在@Convert注解中指定转换器类：

   // 示例转换器
   public class X509CertificateHolderConverter implements AttributeConverter<X509CertificateHolder, String> {
       @Override
       public String convertToDatabaseColumn(X509CertificateHolder attribute) {
           try {
               return Base64.getEncoder().encodeToString(attribute.getEncoded());
           } catch (IOException e) {
               throw new RuntimeException(e);
           }
       }
   
       @Override
       public X509CertificateHolder convertToEntityAttribute(String dbData) {
           try {
               return new X509CertificateHolder(Base64.getDecoder().decode(dbData));
           } catch (IOException e) {
               throw new RuntimeException(e);
           }
       }
   }
   

   然后修改实体类注解：

   @Convert(converter = X509CertificateHolderConverter.class)
   @Lob
   private X509CertificateHolder certificate;
   

内容的提问来源于stack exchange，提问作者NoobCoder