Query annotation count of list for many to many mapping

Abhijit Kumbhar
@Entity
@Table(name="DMS_Document_Class")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class,property = "docClassindexID")
public class DMSDocumentClass {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="DocumentClass_Index_ID")
    private long docClassindexID;


    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "DOCCLASS_GROUP", 
    joinColumns = {@JoinColumn(referencedColumnName ="DocumentClass_Index_ID")}, inverseJoinColumns = {@JoinColumn(referencedColumnName="groupID")})
    @JsonManagedReference
    private List<DMSGroupBean> groups;


    @ManyToMany(mappedBy = "documentClass", fetch = FetchType.LAZY)
    private List<DMSDocument> documents;

DMS Document Class

public class DMSDocument {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="Doc_Index_ID")
    private long docID;

    @OneToOne
    @JoinTable(name = "DOCCLASS_DOCS", 
    joinColumns = {@JoinColumn(referencedColumnName ="Doc_Index_ID")}, 
    inverseJoinColumns = {@JoinColumn(referencedColumnName="DocumentClass_Index_ID")})
    @JsonBackReference
    @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
    private DMSDocumentClass documentClass;

And Below is the repository class for which I tried Query Annotation

    @Query(value = "SELECT count(D.documents) from DMSDocumentClass D where D.docClassindexID = :classID")
    public Long isDocClassHasDocument(@Param("classID") long classID);

While I am trying to execute the code snippet

docClassRepo.isDocClassHasDocument(docClassID);

I am getting output as below

select
        count(.) as col_0_0_ 
    from
        dms_document_class dmsdocumen0_ cross 
    join
        docclass_docs documents1_, dms_document dmsdocumen2_ 
    left outer join
        docclass_docs dmsdocumen2_1_ 
            on dmsdocumen2_.doc_index_id=dmsdocumen2_1_.doc_index_id 
    where
        dmsdocumen0_.document_class_index_id=documents1_.document_class_document_class_index_id 
        and documents1_.doc_index_id=dmsdocumen2_.doc_index_id 
        and dmsdocumen0_.document_class_index_id=?

Here it is giving count(.) which is giving the SQL syntax error. I want to get the count of documents for particular document class but it is writing . in the query. What should I pass to get the count of documents associated with the document class.

Eklavya - UpvoteDon'tSayThanks

You can use size()

@Query(value = "SELECT size(D.documents) from DMSDocumentClass D where D.docClassindexID = :classID")
public int isDocClassHasDocument(@Param("classID") long classID);

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related