Returning entities without parents?
I have the following class that is used in a graph, and as such can have parents and children:
@Entity(name = "PedagogicalNeed")
@Table(name = "PedagogicalNeed")
public class PedagogicalNeedEntity extends AbstractEntity implements PedagogicalNeed {
private String name;
private String description;
@ManyToMany(targetEntity = PedagogicalNeedEntity.class, fetch = FetchType.LAZY)
private Set<PedagogicalNeed> parents = new HashSet<PedagogicalNeed>(0);
@ManyToMany(targetEntity = PedagogicalNeedEntity.class, fetch = FetchType.LAZY)
private Set<PedagogicalNeed> children = new HashSet<PedagogicalNeed>(0);
...
}
Superclass is AbstractEntity
used to define the field @Id
:
@MappedSuperclass
public abstract class AbstractEntity implements Serializable {
...
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
...
}
I have the following query that should return objects for which the parent collection is empty.
@Query("SELECT pn FROM PedagogicalNeed pn WHERE pn.parents IS EMPTY")
Iterable<PedagogicalNeedEntity> findRoots();
My problem is that when this query is executed I get the following NOT NULL constraint violation:
Caused by: org.hsqldb.HsqlException: integrity constraint violation: NOT NULL check constraint; SYS_CT_10102 table: PEDAGOGICALNEED_PEDAGOGICALNEED column: PARENTS_ID
The question is : how can I return objects that have no parents?
UPDATE : The generated schema looks like this:
CREATE MEMORY TABLE PUBLIC.PEDAGOGICALNEED(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,DESCRIPTION VARCHAR(255),NAME VARCHAR(255))
ALTER TABLE PUBLIC.PEDAGOGICALNEED ALTER COLUMN ID RESTART WITH 1
CREATE MEMORY TABLE PUBLIC.PEDAGOGICALNEED_PEDAGOGICALNEED(
PEDAGOGICALNEED_ID BIGINT NOT NULL,
PARENTS_ID BIGINT NOT NULL,
CHILDREN_ID BIGINT NOT NULL,
PRIMARY KEY(PEDAGOGICALNEED_ID,CHILDREN_ID),
CONSTRAINT FK_GSX50JH17YMGUMDQ7ADD0Y0Y5 FOREIGN KEY(PARENTS_ID) REFERENCES PUBLIC.PEDAGOGICALNEED(ID),
CONSTRAINT FK_L6QY3L7AMTRVX1O62WVS6C9X8 FOREIGN KEY(PEDAGOGICALNEED_ID) REFERENCES PUBLIC.PEDAGOGICALNEED(ID),
CONSTRAINT FK_EHV0QET4GVHBX733YJV6Q9KQD FOREIGN KEY(CHILDREN_ID) REFERENCES PUBLIC.PEDAGOGICALNEED(ID))
I don't understand this mapping, why is there one table "PEDAGOGICALNEED_PEDAGOGICALNEED" with three fields? The connections between PedagogicalNeedEntity and its parents have nothing to do with the connections between the same entity and its children. So there should be two tables and not just one, each table only has two columns:
- First table must have (PEDAGOGICALNEED_ID, CHILDREN_ID) as key
- Second table must have (PEDAGOGICALNEED_ID, PARENTS_ID) as key
source to share
Your database table PEDAGOGICALNEED_PEDAGOGICALNEED
for many, many PedagogicalNeedEntity
has a column PEDAGOGICALNEED_ID
that is never populated.
Provide a column definition in PedagogicalNeedEntity for an ID column like this (pseudocode):
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
You can either provide the id yourself (before storing the entities) or use the generated id value.
Important . To avoid redundancy, you shouldn't use @JoinTable
for both relationships. Use @JoinTable
for one relationship and mappedBy
on another.
A simple example showing how to create such a "network of nodes" (tested with Hibernate 4.3.8 and Derby 10.9.1.0):
@Entity
public class Node {
@Id
public int id;
public String nodeName;
@ManyToMany
@JoinTable(name="relationships")
public List<Node> parents;
@ManyToMany(mappedBy="parents")
public List<Node> children;
public static void main(String[] args) {
EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("persistenceUnit");
EntityManager entityManager = entityManagerFactory.createEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
entityManager.persist(new Node());
transaction.commit();
entityManager.close();
}
}
This will create two tables:
- Node table
- Column id (int)
- Column NODENAME (varchar (255) with null)
- RELATIONSHIP TABLE
- Column PARENTS_ID (int)
- Column CHILDREN_ID (int)
source to share
This is not an exception thrown by the request. This should be an exception from the persist operation that was called before the request.
The persistence provider must clear all dml statements before the request is executed.
The PEDAGOGICALNEED_ID column cannot be null on the DB, but you did not provide a value for this column.
The request looks ok.
source to share
UPDATE : this doesn't work, see the best answer for how to get a single join table.
Problem solved! I just needed to specify the name of the "join table" with:
@ManyToMany(targetEntity = PedagogicalNeedEntity.class, fetch = FetchType.LAZY)
@JoinTable(name="PedagogicalNeed_parents")
private Set<PedagogicalNeed> parents = new HashSet<PedagogicalNeed>(0);
@ManyToMany(targetEntity = PedagogicalNeedEntity.class, fetch = FetchType.LAZY)
@JoinTable(name="PedagogicalNeed_children")
private Set<PedagogicalNeed> children = new HashSet<PedagogicalNeed>(0);
Thanks for the help!
source to share