The attribute [id] of class 'LocalCertificate' is mapped to a primary key column in the database. Updates are not allowed

Question

I am facing this issue since long now and I am not aware of this problem in java side. I am working on an old JPA project without prior knowledge in java and Postgres SQL.

I have an entity class where I can able to push into derby db. But now I want to make a replica of data into Postgres as well .

Entity class:

@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);
        tta.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 file:

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

For d_id and certificate column I can see NULL value. How to solve this issue ?

EDIT:

Adding the servlet file where it is failing

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();            <==== here it is failing
        
        // 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();
    }

Answer 1

For this to work, you'll need to create a copy of the instance you are persist/merging for use in the second persistence context - something more like:

Static final EntityManagerFactory emf = Persistence.createEntityManagerFactory("LDB");
// for postgres
Static final EntityManagerFactory eemf = Persistence.createEntityManagerFactory("PU_CSA");

public void update(LocalCertificate cert) {
    EntityManager locEm = emf.createEntityManager()
    try {
      EntityTransaction ta = locEm.getTransaction();
    
      ta.begin();
      locEm.merge(cert);
      ta.commit();
    } finally { locEm.close();}

    CopyGroup group = new CopyGroup();
    LocalCertificate copy = (LocalCertificate)em.unwrap( JpaEntityManager.class ).copy( cert, group );
    
    EntityManager llocEm = eemf.createEntityManager();
    try {
      EntityTransaction tta = llocEm.getTransaction();
    
      tta.begin();
      llocEm.merge(copy);
      tta.commit();
    } finally { llocEm.close();}
}

For persisting a new entity, you will have to do more as your model won't work as it is - You likely cannot use identity management in both derby and Postgres or your entities will be given different primary key values. To get around that, the simplest solution is to assign values yourself, something like:

@Table(name = "certs")
public class LocalCertificate implements Serializable {

  @Id
  private String id;
  ..
  
  public void assignId() {
    if (StringUtil.isBlank(id)) {
      id = UUID.randomUUID().toString();
    }
  }
 }

You then can use the same concept for persisting and just call assignId on the first object passed in, and use that assigned value in the copy passed into the second persistence unit as well.