CODEWITHSUNDEEP
javasqljpa
Grégoire C
Grégoire C

Reputation: 1381

Returning the entities with no 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);

    ...
}

The superclass AbstractEntity is used to define the @Id field:

@MappedSuperclass
public abstract class AbstractEntity implements Serializable {
    ...
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    ...
}

I have the following request, that should return the entities for which the "parents" collection is empty.

@Query("SELECT pn FROM PedagogicalNeed pn WHERE pn.parents IS EMPTY")
Iterable<PedagogicalNeedEntity> findRoots();

My problem is that, when this request 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

Question : how I can return the entities that have no parents?

UPDATE : the generated schema is the following:

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 a single table "PEDAGOGICALNEED_PEDAGOGICALNEED" with three fields? The links between a PedagogicalNeedEntity and its parents has nothing to do with the links between the same entity and its children. So there should be two tables and not only one, each table having only two columns :

Upvotes: 3

Views: 108

Answers (3)

Gr&#233;goire C
Gr&#233;goire C

Reputation: 1381

UPDATE : this doesn't work, see the best answer to learn how to get a single join table.

Problem solved! I just needed to specify the name of the "join tables" 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 helping out!

Upvotes: 1

slartidan
slartidan

Reputation: 21598

Your database table PEDAGOGICALNEED_PEDAGOGICALNEED for the many to many between PedagogicalNeedEntitys has a column PEDAGOGICALNEED_ID that is never filled.

Provide a column definition in PedagogicalNeedEntity for an ID column like that (pseudo-code):

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

You can either provide id's by yourself (before persisting the entities) or use a generated id value.

Important: to avoid redundancy you should not use @JoinTable on both relationships. Use @JoinTable on one relationship and mappedBy on the other one.

A simple example, showing how to create such a "net 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:

  • Table Node
    • Column ID (int)
    • Column NODENAME (varchar(255) nullable)
  • Table RELATIONSHIPS
    • Column PARENTS_ID (int)
    • Column CHILDREN_ID (int)

Upvotes: 2

sfrutig
sfrutig

Reputation: 287

Thats not the exception which is caused by the query. It must be an exception from a persist operation which was triggered before the query.

The persistence provider must flush all dml-statements before the query can be executed.

The column PEDAGOGICALNEED_ID can't be null on th DB, but you didn't provide a value for that column.

The query looks fine.

Upvotes: 1

Related Questions