CriteriaQuery to count children of an entity produces malformed numeric constant: . error

Question

I have the following tables in the database. All expressed with Hibernate annotations in my Java code.

|LibraryItem Table             |
|LibraryItemId|LibraryItemTitle|

|ItemListing Table                              |
|ListingId|ChildLibrayItemId|ParentLibraryItemId|

So basically there are library items. And each library item may be a child or a parent of another library item and this relationships are stored in the Itemlisting table.

I am trying to get the count of all the children of a particular library item using the CriteriaBuilder approach. Here's my code:

public int getNumChildren(LibraryItem libItem) {
        CriteriaBuilder builder = sessionFactory.getCriteriaBuilder();
        CriteriaQuery<Long> query = builder.createQuery(Long.class);
        Root<LibraryItem> root = query.from(LibraryItem.class);
        query.select(builder.count(root.get("itemChildren")));
        query.where(builder.equal(root.get("libraryItemId"), libItem.getLibraryItemId()));
        return Math.toIntExact(sessionFactory.getCurrentSession().createQuery(query).uniqueResult());
    }

this produces the following error:

java.sql.SQLSyntaxErrorException: malformed numeric constant: . in statement [select count(.) as col_0_0_ from library_item libraryite0_, ITEM_LISTING itemchildr1_, library_item libraryite2_ where libraryite0_.LIBRARY_ITEM_ID=itemchildr1_.PARENT_LIB_ITEM_ID and itemchildr1_.CHILD_LIB_ITEM_ID=libraryite2_.LIBRARY_ITEM_ID and libraryite0_.LIBRARY_ITEM_ID=4601]

Can someone explain to me what I'm doing wrong here please?

EDIT:

Here's the entity class. I've omitted some of the code that I think is irrelevant:

@Entity
@Table(name = "library_item", uniqueConstraints = {
        @UniqueConstraint(columnNames={"LIBRARY_ITEM_TITLE", "LIBRARY_ID"})

})
public class LibraryItem extends DatabaseObject {

    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "hilo_sequence_generator")
    @GenericGenerator(
            name = "hilo_sequence_generator",
            strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
            parameters = {
                    @org.hibernate.annotations.Parameter(name = "sequence_name", value = "hilo_seqeunce"),
                    @org.hibernate.annotations.Parameter(name = "initial_value", value = "1"),
                    @org.hibernate.annotations.Parameter(name = "increment_size", value = "100"),
                    @org.hibernate.annotations.Parameter(name = "optimizer", value = "hilo")
            })
    @Id
    @Column(name = "LIBRARY_ITEM_ID", unique = true, nullable = false)
    private Long libraryItemId;

    @Column(name = "LIBRARY_ITEM_TITLE", nullable = false)
    private String libraryItemTitle;

    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinTable(name = "ITEM_LISTING",
            joinColumns = {@JoinColumn(name = "PARENT_LIB_ITEM_ID", nullable=false)},
            inverseJoinColumns = {@JoinColumn(name="CHILD_LIB_ITEM_ID", nullable = false)})
    private Set<LibraryItem> itemChildren = new HashSet<>();

    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "itemChildren")
    private Set<LibraryItem> itemParents = new HashSet<>();
}

Answer 1

You are using count on a multi-valued field. This is invalid in JPQL, and hence also in Criteria.

The way to do that would be to make use of the size function (CriteriaBuilder.size), which is explicitly for collection fields.