英文:
How can I get JPA hibernate to not adding a value NULL, when field is not set (null), when inserting into a db/2 default value field
问题
以下是您要翻译的内容：
我正在使用Java和Hibernate JPA（5.6.15.Final）编写一个应用程序来存储/更改/删除合同和处理数据。
- IBM DB/2数据库中的两个数据库表（简化）：
CREATE TABLE "Schema1"."Contracts" (
"IDX" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
"DATA" varchar(255));
CREATE TABLE "Schema1"."Processes" (
"IDX" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
"CONTRACTIDX" INTEGER NOT NULL DEFAULT 0,
"DATA" varchar(255));
数据库已经存在，而且非常老，其他软件也在使用它（对于其中一个应用程序，我们甚至没有源代码），因此我们不能轻松地对表和字段定义进行更改。
CONTRACTIDX可能默认为0的原因是，在没有合同的情况下也应该能够进行处理（这意味着：当我们在长时间的合同谈判中时，我们也可以进行处理，而当我们已经有合同时也可以进行处理）。因此，CONTRACTIDX的有效值应为现有合同ID或0以表示没有合同。
实体类：
src/main/java/mypackage/ProcessDTO.java
@Entity
@Table(schema = "Schema1", name = "Processes")
public class ProcessDTO
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "IDX")
private Long id = null;
@ManyToOne
@JoinColumn(name = "CONTRACTIDX" /* , nullable=true or nullable=false: I've already tried that... it has no effect on the problem */)
private ContractDTO contract = null;
@Column(name="DATA")
private String data;
// getters and setters
}
src/main/java/mypackage/ContractDTO.java
@Entity
@Table(schema = "SCHEMA1", name = "Contracts")
public class ContractDTO {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "IDX")
private Long id;
@Column(name="DATA")
private String data;
// getters and setters
}
src/main/resources/META-INF/persistence.xml:
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="myPu">
<jta-data-source>jdbc/myDb</jta-data-source>
<class>mypackage.ProcessDTO</class>
<class>mypackage.ContractDTO</class>
<properties>
<property name="hibernate.dialect" value= "org.hibernate.dialect.DB2Dialect" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
</properties>
</persistence-unit>
</persistence>
现在是我的问题：
当运行像这样的代码时：
src/main/java/mypackage/Main.java:
public class Main {
public static void main(String[] args)
{
EntityManagerFactory emf = Persistence.createEntityManagerFactory("myPu");
EntityManager em = emf.createEntityManager();
em.getTransaction()
.begin();
try
{
// case 1: there is no contract
ProcessDTO process = new ProcessDTO();
process.setData("XYZ");
em.merge(process);
// case 2: there is a contract
ContractDTO contract = new ContractDTO();
contract.setData("abc");
contract = em.merge(contract);
ProcessDTO process2 = new ProcessDTO();
process2.setContract(contract);
process2.setData("def");
em.merge(process2);
}
finally
{
em.getTransaction()
.commit();
}
em.getTransaction()
.begin();
try
{
ProcessDTO process1 = new ProcessDTO();
process1.setData("abc");
em.merge(process1);
ProcessDTO process2 = new ProcessDTO();
process2.setData("def");
em.merge(process2);
}
finally
{
em.getTransaction()
.commit();
}
em.getTransaction()
.begin();
try
{
TypedQuery<ProcessDTO> query =
em.createQuery("from ProcessDTO where data='abc'", ProcessDTO.class);
List<ProcessDTO> processes = query.getResultList();
if (!processes.isEmpty())
{
ProcessDTO process = processes.get(0);
process.setData("abcdef");
process = em.merge(process);
}
TypedQuery<ProcessDTO> queryToDelete =
em.createQuery("from ProcessDTO where data='def'", ProcessDTO.class);
List<ProcessDTO> processesToDelete = queryToDelete.getResultList();
if (!processesToDelete.isEmpty())
{
ProcessDTO process = processesToDelete.get(0);
em.remove(process);
}
}
finally
{
em.getTransaction()
.commit();
}
}
}
DB2 SQL错误：SQLCODE=-407，SQLSTATE=23502，SQLERRMC=TBSPACEID=3，TABLEID=24，COLNO=1，DRIVER=4.19.66
这个错误的原因是，JPA尝试执行类似于以下语句：
INSERT INTO Processes(CONTRACTIDX, DATA) values (NULL, 'XYZ');
这是数据库不允许的。
案例1：“没有合同”应该生成类似于以下内容：
INSERT INTO Processes(DATA) values ('XYZ');
案例2：“有合同”应该生成类似于以下内容：
INSERT INTO Contracts(DATA) values ('abc');
INSERT INTO Processes(CONTRACTIDX, DATA) values (<合同的ID值>, 'def');
相关问题：然后，我
英文:
I am using Java and Hibernate JPA (5.6.15.Final) to write an application to store/change/delete contracts and processing data.
- two database tables in a IBM DB/2 database (simplified):
CREATE TABLE "Schema1"."Contracts" (
"IDX" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
"DATA" varchar(255));
CREATE TABLE "Schema1"."Processes" (
"IDX" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
"CONTRACTIDX" INTEGER NOT NULL DEFAULT 0,
"DATA" varchar(255));
The database that is used, is already existing, it is very old and other software is using it also (for one of the apps, we do not even have source code), so we cannot easily make changes to the tables and field definitions.
The reason, why CONTRACTIDX may be 0 by default is, that it should be possible to process before there is a contract here (that means: we could also be processing, when we are in long-running contract negotiations, and we could be processing, when we already have a contract)
So valid values for CONTRACTIDX should be an existing contract id or 0 for no contract.
- the entities:
src/main/java/mypackage/ProcessDTO.java
@Entity
@Table(schema = "Schema1", name = "Processes")
public class ProcessDTO
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "IDX")
private Long id = null;
@ManyToOne
@JoinColumn(name = "CONTRACTIDX" /* , nullable=true or nullable=false: I've already tried that... it has no effect on the problem */)
private ContractDTO contract = null;
@Column(name="DATA")
private String data;
// getters and setters
}
src/main/java/mypackage/ContractDTO.java
@Entity
@Table(schema = "SCHEMA1", name = "Contracts")
public class ContractDTO {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "IDX")
private Long id;
@Column(name="DATA")
private String data;
// getters and setters
}
src/main/resources/META-INF/persistence.xml:
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="myPu">
<jta-data-source>jdbc/myDb</jta-data-source>
<class>mypackage.ProcessDTO</class>
<class>mypackage.ContractDTO</class>
<properties>
<property name="hibernate.dialect" value= "org.hibernate.dialect.DB2Dialect" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
</properties>
</persistence-unit>
</persistence>
Now comes my problem:
When running this with code like this:
src/main/java/mypackage/Main.java:
public class Main {
public static void main(String[] args)
{
EntityManagerFactory emf = Persistence.createEntityManagerFactory("myPu");
EntityManager em = emf.createEntityManager();
em.getTransaction()
.begin();
try
{
// case 1: there is no contract
ProcessDTO process = new ProcessDTO();
process.setData("XYZ");
em.merge(process);
// case 2: there is a contract
ContractDTO contract = new ContractDTO();
contract.setData("abc");
contract = em.merge(contract);
ProcessDTO process2 = new ProcessDTO();
process2.setContract(contract);
process2.setData("def");
em.merge(process2);
}
finally
{
em.getTransaction()
.commit();
}
em.getTransaction()
.begin();
try
{
ProcessDTO process1 = new ProcessDTO();
process1.setData("abc");
em.merge(process1);
ProcessDTO process2 = new ProcessDTO();
process2.setData("def");
em.merge(process2);
}
finally
{
em.getTransaction()
.commit();
}
em.getTransaction()
.begin();
try
{
TypedQuery<ProcessDTO> query =
em.createQuery("from ProcessDTO where data='abc'", ProcessDTO.class);
List<ProcessDTO> processes = query.getResultList();
if (!processes.isEmpty())
{
ProcessDTO process = processes.get(0);
process.setData("abcdef");
process = em.merge(process);
}
TypedQuery<ProcessDTO> queryToDelete =
em.createQuery("from ProcessDTO where data='def'", ProcessDTO.class);
List<ProcessDTO> processesToDelete = queryToDelete.getResultList();
if (!processesToDelete.isEmpty())
{
ProcessDTO process = processesToDelete.get(0);
em.remove(process);
}
}
finally
{
em.getTransaction()
.commit();
}
}
}
DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=3, TABLEID=24, COLNO=1, DRIVER=4.19.66
The reason for this error is, that JPA tries a statement like this:
INSERT INTO Processes(CONTRACTIDX, DATA) values (NULL, 'XYZ');
which the database doesn't allow.
Case 1: 'there is no contract' should generate something like
INSERT INTO Processes(DATA) values ('XYZ');
Case 2: 'there is a contract' should generate something like
INSERT INTO Contracts(DATA) values ('abc');
INSERT INTO Processes(CONTRACTIDX, DATA) values (<the id value of that contract>, 'def');
A related question: I then need a solution for the problem of processes, that have CONTRACTIX==0.
They should be accepted as if they were NULL;
Complete Stacktrace of Case 1:
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:840)
at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:816)
at de.continentale.mvp.jpatest001.Main.main(Main.java:39)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:58)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:43)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3279)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3914)
at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:84)
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645)
at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:329)
at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:286)
at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:192)
at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:122)
at org.hibernate.event.internal.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:273)
at org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:246)
at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:178)
at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:74)
at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107)
at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:829)
... 2 more
Caused by: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=2, TABLEID=265, COLNO=1, DRIVER=4.19.66
at com.ibm.db2.jcc.am.kd.a(kd.java:743)
at com.ibm.db2.jcc.am.kd.a(kd.java:66)
at com.ibm.db2.jcc.am.kd.a(kd.java:135)
at com.ibm.db2.jcc.am.fp.c(fp.java:2788)
at com.ibm.db2.jcc.am.fp.a(fp.java:2236)
at com.ibm.db2.jcc.t4.bb.o(bb.java:908)
at com.ibm.db2.jcc.t4.bb.j(bb.java:267)
at com.ibm.db2.jcc.t4.bb.d(bb.java:55)
at com.ibm.db2.jcc.t4.p.c(p.java:44)
at com.ibm.db2.jcc.t4.vb.j(vb.java:157)
at com.ibm.db2.jcc.am.fp.nb(fp.java:2231)
at com.ibm.db2.jcc.am.gp.b(gp.java:4542)
at com.ibm.db2.jcc.am.gp.mc(gp.java:815)
at com.ibm.db2.jcc.am.gp.executeUpdate(gp.java:789)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
... 21 more
Maven dependencies from pom.xml:
<dependency>
<groupId>jakarta.enterprise</groupId>
<artifactId>jakarta.enterprise.cdi-api</artifactId>
<version>2.0.2</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>jakarta.inject</groupId>
<artifactId>jakarta.inject-api</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>jakarta.persistence</groupId>
<artifactId>jakarta.persistence-api</artifactId>
<version>2.2.3</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.6.15.Final</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>jakarta.transaction</groupId>
<artifactId>jakarta.transaction-api</artifactId>
<version>1.3.3</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.jboss.narayana.jta</groupId>
<artifactId>narayana-jta</artifactId>
<version>5.12.7.Final</version>
</dependency>
Java: openjdk version "1.8.0_222"
答案1
得分: 0
我找到了一个解决方案...但不太喜欢它...有没有更好（更短，更清晰）的方法？
我修改了我的 ProcessDTO：
@Entity
@Table(schema = "Schema1", name = "Processes")
public class ProcessDTO
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "IDX")
private Long id = null;
@ManyToOne
@NotFound(action = NotFoundAction.IGNORE)
@JoinColumn(name = "CONTRACTIDX",
insertable = false,
updatable = false,
foreignKey = @ForeignKey(ConstraintMode.NO_CONSTRAINT))
private ContractDTO contract = null;
@Column(name = "CONTRACTIDX")
private Long contractId = null;
@Column(name = "DATA")
private String data;
// getters and setters
@PrePersist
@PreUpdate
void beforeSave()
{
if (contract != null)
{
contractId = contract.getId();
}
else
{
contractId = 0L;
}
}
@PostLoad
void afterLoad()
{
if (contractId == 0L)
{
contractId = 0L;
contract = null;
}
}
}
还有一个问题是，我需要使用 Hibernate 的原生注解：NotFound 和 NotFoundAction...
我更喜欢使用 JPA 接口的方式...
英文:
I found a solution... but don't like it very much... is there a better (shorter, clearer) one?
I changed my ProcessDTO:
@Entity
@Table(schema = "Schema1", name = "Processes")
public class ProcessDTO
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "IDX")
private Long id = null;
@ManyToOne
@NotFound(action = NotFoundAction.IGNORE)
@JoinColumn(name = "CONTRACTIDX",
insertable = false,
updatable = false,
foreignKey = @ForeignKey(ConstraintMode.NO_CONSTRAINT))
private ContractDTO contract = null;
@Column(name = "CONTRACTIDX")
private Long contractId = null;
@Column(name = "DATA")
private String data;
// getters and setters
@PrePersist
@PreUpdate
void beforeSave()
{
if (contract != null)
{
contractId = contract.getId();
}
else
{
contractId = 0L;
}
}
@PostLoad
void afterLoad()
{
if (contractId == 0L)
{
contractId = 0L;
contract = null;
}
}
}
One more 'bad thing' on it is, that I need hibernate-native annotations: NotFound and NotFoundAction...
I would prefer something from the jpa-interface...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论