Erreur lors de l'obtention de la list à partir de la vue SQL via la requête JPA Creteria

J'ai créé une vue SQL dans la database Mysql. J'écris le service Web pour montrer la list en utilisant JPA basé sur l'input de search d'user. La requête Hibernate s'exécute correctement dans la console, mais la list n'apparaît pas lorsqu'elle est cochée dans le client de repos avancé. Veuillez vérifier ci-dessous le code requirejs:

Classe d'entité:

@Entity @Table(name = "tbi_datadiscovery_detail_view"/*, catalog = "timebound"*/) public class TbiDDDetailView implements java.io.Serializable { // Fields @Id @Column(name = "dd_mesortingc_id") @GeneratedValue(generator = "gen") @GenericGenerator(name = "gen", strategy = "foreign", parameters = @Parameter(name = "property", value = "ddConfigId")) private Integer ddConfigId; @Column(name="dd_mesortingc_name") private Ssortingng mesortingcName; @Column(name="dd_type") private Ssortingng ddType; @Column(name="dd_name") private Ssortingng ddName; @Column(name="discovery_id") private Integer discoveryId; @Column(name="key_words") private Ssortingng keywords; @OneToOne @PrimaryKeyJoinColumn private TbiDDConfigMaster tbiDDConfigMaster; //setters & getters }

Référentiel JPA:

 public interface TbiDDDetailViewDao extends CrudRepository<TbiDDDetailView, Integer>, JpaRepository<TbiDDDetailView, Integer> { @Query("select v from TbiDDDetailView v,TbiDDConfigMaster t where v.ddConfigId = t.ddConfigId and t.domainId=?1 and v.mesortingcName LIKE %?2% or t.keywords LIKE %?2%") List<TbiDDDetailView> findByDomainIdAndMesortingcNameLike(Integer domainId,Ssortingng mesortingcName); }

Mise en œuvre du service:

 public TbiDDDetailViewListResponse getViewMasortingcs(List sortProperties, List sortTypes, List operator, List value, List property, int page, int limit, Integer domainId, Ssortingng searchSsortingng) { TbiDDDetailViewListResponse res = new TbiDDDetailViewListResponse(); List<TbiDDDetailView> tbiDDDetailViews = tbiDDDetailViewDao.findByDomainIdAndMesortingcNameLike(domainId, searchSsortingng); CriteriaBuilder cb=em.getCriteriaBuilder(); CriteriaQuery<TbiDDDetailView> qry =cb.createQuery(TbiDDDetailView.class); Root root = qry.from(TbiDDDetailView.class); Root config = qry.from(TbiDDConfigMaster.class); List creteriaList = new ArrayList<>(); Predicate predicate1 = cb.equal(root.get("tbiDDConfigMaster").get("ddConfigId"), config.get("ddConfigId")); creteriaList.add(predicate1); Predicate predicate2 = cb.equal(root.get("tbiDDConfigMaster").get("ddConfigId"), domainId); creteriaList.add(predicate2); Predicate predicate3 = cb.like(cb.upper(root.get("mesortingcName")),searchSsortingng); creteriaList.add(predicate3); CriteriaQuery<TbiDDDetailView> criteriaQuery = qry.select(cb.construct(TbiDDDetailView.class, root.get("ddConfigId"),root.get("mesortingcName"),root.get("ddType"), root.get("ddName"),root.get("discoveryId"))); List<Order> orderList = new ArrayList<>(); orderList = getSort(cb,root,sortProperties, sortTypes, null); qry.where(cb.and((Predicate[]) creteriaList.toArray(new Predicate[0]))); int start=0; if(limit != 0) start=(page-1)*limit; TypedQuery<TbiDDDetailView> tq = em.createQuery(qry); tbiDDDetailViews = tq.setFirstResult(start).setMaxResults(limit).getResultList(); TypedQuery<TbiDDDetailView> queryTotal = em.createQuery(criteriaQuery); long totalRecords = (long) queryTotal.getResultList().size(); List<DDDetailViewResponse> details = new ArrayList<>(); if(tbiDDDetailViews!=null) { for(TbiDDDetailView t : tbiDDDetailViews){ DDDetailViewResponse detailView = new DDDetailViewResponse(); detailView.setDdMesortingcId(t.getDdConfigId()); detailView.setDdMesortingcName(t.getMesortingcName()); detailView.setDdType(t.getDdType()); detailView.setDdName(t.getDdName()); detailView.setDiscoveryId(t.getDiscoveryId()); details.add(detailView); System.out.println("name-->"+t.getDdName()+"------type-------"+t.getDdType()+"-------------id--------------"+t.getDdConfigId()+"---------Mesortingcname-----------"+t.getMesortingcName()); } } res.setRecords(details); res.setPageNumber(page); if(limit != 0) { int Rem = (totalRecords%limit)>0?new Integer(1):0; int total = (int) (totalRecords/limit + Rem); res.setTotalPages(total); log.info("TotalRecords :"+totalRecords + "Total Pages:" +total); } return res; }

Quand j'ai vérifié avec le mode de debugging, l'exécution n'est pas à l'intérieur pour la boucle seulement, sa sortie directe de la boucle.

Hibernate l'exécution de la requête dans la console:

 Hibernate: select tbidddetai0_.dd_mesortingc_id as dd_mesorting1_34_, tbidddetai0_.dd_name as dd_name2_34_, tbidddetai0_.dd_type as dd_type3_34_, tbidddetai0_.discovery_id as discover4_34_, tbidddetai0_.key_words as key_word5_34_, tbidddetai0_.dd_mesortingc_name as dd_mesorting6_34_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbiddconfi1_.domain_id=? and (tbidddetai0_.dd_mesortingc_name like ?) or tbiddconfi1_.keywords like ? Hibernate: select tbiddconfi0_.dd_mesortingc_config_id as dd_mesorting1_35_0_, tbiddconfi0_.created_by as created_2_35_0_, tbiddconfi0_.created_date as created_3_35_0_, tbiddconfi0_.domain_id as domain_i4_35_0_, tbiddconfi0_.is_active as is_activ5_35_0_, tbiddconfi0_.keywords as keywords6_35_0_, tbiddconfi0_.mesortingc_name as mesortingc_n7_35_0_, tbiddconfi0_.modified_by as modified8_35_0_, tbiddconfi0_.modified_date as modified9_35_0_ from tbi_dd_config_master tbiddconfi0_ where tbiddconfi0_.dd_mesortingc_config_id=? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) limit ? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) 95953 [http-bio-9090-exec-3] INFO com.acinfotech.timebound.jpa.service.ReportJobsPersistenceServiceImpl - TotalRecords :0Total Pages:0 en Hibernate: select tbidddetai0_.dd_mesortingc_id as dd_mesorting1_34_, tbidddetai0_.dd_name as dd_name2_34_, tbidddetai0_.dd_type as dd_type3_34_, tbidddetai0_.discovery_id as discover4_34_, tbidddetai0_.key_words as key_word5_34_, tbidddetai0_.dd_mesortingc_name as dd_mesorting6_34_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbiddconfi1_.domain_id=? and (tbidddetai0_.dd_mesortingc_name like ?) or tbiddconfi1_.keywords like ? Hibernate: select tbiddconfi0_.dd_mesortingc_config_id as dd_mesorting1_35_0_, tbiddconfi0_.created_by as created_2_35_0_, tbiddconfi0_.created_date as created_3_35_0_, tbiddconfi0_.domain_id as domain_i4_35_0_, tbiddconfi0_.is_active as is_activ5_35_0_, tbiddconfi0_.keywords as keywords6_35_0_, tbiddconfi0_.mesortingc_name as mesortingc_n7_35_0_, tbiddconfi0_.modified_by as modified8_35_0_, tbiddconfi0_.modified_date as modified9_35_0_ from tbi_dd_config_master tbiddconfi0_ where tbiddconfi0_.dd_mesortingc_config_id=? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) limit ? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) 95953 [http-bio-9090-exec-3] INFO com.acinfotech.timebound.jpa.service.ReportJobsPersistenceServiceImpl - TotalRecords :0Total Pages:0 en Hibernate: select tbidddetai0_.dd_mesortingc_id as dd_mesorting1_34_, tbidddetai0_.dd_name as dd_name2_34_, tbidddetai0_.dd_type as dd_type3_34_, tbidddetai0_.discovery_id as discover4_34_, tbidddetai0_.key_words as key_word5_34_, tbidddetai0_.dd_mesortingc_name as dd_mesorting6_34_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbiddconfi1_.domain_id=? and (tbidddetai0_.dd_mesortingc_name like ?) or tbiddconfi1_.keywords like ? Hibernate: select tbiddconfi0_.dd_mesortingc_config_id as dd_mesorting1_35_0_, tbiddconfi0_.created_by as created_2_35_0_, tbiddconfi0_.created_date as created_3_35_0_, tbiddconfi0_.domain_id as domain_i4_35_0_, tbiddconfi0_.is_active as is_activ5_35_0_, tbiddconfi0_.keywords as keywords6_35_0_, tbiddconfi0_.mesortingc_name as mesortingc_n7_35_0_, tbiddconfi0_.modified_by as modified8_35_0_, tbiddconfi0_.modified_date as modified9_35_0_ from tbi_dd_config_master tbiddconfi0_ where tbiddconfi0_.dd_mesortingc_config_id=? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) limit ? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) 95953 [http-bio-9090-exec-3] INFO com.acinfotech.timebound.jpa.service.ReportJobsPersistenceServiceImpl - TotalRecords :0Total Pages:0 en Hibernate: select tbidddetai0_.dd_mesortingc_id as dd_mesorting1_34_, tbidddetai0_.dd_name as dd_name2_34_, tbidddetai0_.dd_type as dd_type3_34_, tbidddetai0_.discovery_id as discover4_34_, tbidddetai0_.key_words as key_word5_34_, tbidddetai0_.dd_mesortingc_name as dd_mesorting6_34_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbiddconfi1_.domain_id=? and (tbidddetai0_.dd_mesortingc_name like ?) or tbiddconfi1_.keywords like ? Hibernate: select tbiddconfi0_.dd_mesortingc_config_id as dd_mesorting1_35_0_, tbiddconfi0_.created_by as created_2_35_0_, tbiddconfi0_.created_date as created_3_35_0_, tbiddconfi0_.domain_id as domain_i4_35_0_, tbiddconfi0_.is_active as is_activ5_35_0_, tbiddconfi0_.keywords as keywords6_35_0_, tbiddconfi0_.mesortingc_name as mesortingc_n7_35_0_, tbiddconfi0_.modified_by as modified8_35_0_, tbiddconfi0_.modified_date as modified9_35_0_ from tbi_dd_config_master tbiddconfi0_ where tbiddconfi0_.dd_mesortingc_config_id=? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) limit ? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) 95953 [http-bio-9090-exec-3] INFO com.acinfotech.timebound.jpa.service.ReportJobsPersistenceServiceImpl - TotalRecords :0Total Pages:0 en Hibernate: select tbidddetai0_.dd_mesortingc_id as dd_mesorting1_34_, tbidddetai0_.dd_name as dd_name2_34_, tbidddetai0_.dd_type as dd_type3_34_, tbidddetai0_.discovery_id as discover4_34_, tbidddetai0_.key_words as key_word5_34_, tbidddetai0_.dd_mesortingc_name as dd_mesorting6_34_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbiddconfi1_.domain_id=? and (tbidddetai0_.dd_mesortingc_name like ?) or tbiddconfi1_.keywords like ? Hibernate: select tbiddconfi0_.dd_mesortingc_config_id as dd_mesorting1_35_0_, tbiddconfi0_.created_by as created_2_35_0_, tbiddconfi0_.created_date as created_3_35_0_, tbiddconfi0_.domain_id as domain_i4_35_0_, tbiddconfi0_.is_active as is_activ5_35_0_, tbiddconfi0_.keywords as keywords6_35_0_, tbiddconfi0_.mesortingc_name as mesortingc_n7_35_0_, tbiddconfi0_.modified_by as modified8_35_0_, tbiddconfi0_.modified_date as modified9_35_0_ from tbi_dd_config_master tbiddconfi0_ where tbiddconfi0_.dd_mesortingc_config_id=? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) limit ? Hibernate: select tbidddetai0_.dd_mesortingc_id as col_0_0_, tbidddetai0_.dd_mesortingc_name as col_1_0_, tbidddetai0_.dd_type as col_2_0_, tbidddetai0_.dd_name as col_3_0_, tbidddetai0_.discovery_id as col_4_0_ from tbi_datadiscovery_detail_view tbidddetai0_ cross join tbi_dd_config_master tbiddconfi1_ where tbidddetai0_.dd_mesortingc_id=tbiddconfi1_.dd_mesortingc_config_id and tbidddetai0_.dd_mesortingc_id=4926 and (upper(tbidddetai0_.dd_mesortingc_name) like ?) 95953 [http-bio-9090-exec-3] INFO com.acinfotech.timebound.jpa.service.ReportJobsPersistenceServiceImpl - TotalRecords :0Total Pages:0

Je l'ai eu en travaillant en changeant la Creteria query avec le Predicate

Vérifiez ci-dessous le code qui a fonctionné pour moi:

 CriteriaBuilder cb=em.getCriteriaBuilder(); CriteriaQuery<TbiDDDetailView> qry =cb.createQuery(TbiDDDetailView.class); Root root = qry.from(TbiDDDetailView.class); Root config = qry.from(TbiDDConfigMaster.class); List creteriaList = new ArrayList<>(); Predicate predicate1 = cb.equal(root.get("tbiDDConfigMaster").get("ddConfigId"), config.get("ddConfigId")); creteriaList.add(predicate1); Predicate predicate2 = cb.equal(root.get("tbiDDConfigMaster").get("domainId"), domainId); creteriaList.add(predicate2); Predicate predicate3 = cb.or(cb.like(cb.upper(root.get("tbiDDConfigMaster").get("keywords")), "%"+searchSsortingng+"%"), cb.like(cb.upper(root.get("mesortingcName")),"%"+searchSsortingng+"%")); CriteriaQuery<TbiDDDetailView> criteriaQuery = qry.select(cb.construct(TbiDDDetailView.class, root.get("ddConfigId"),root.get("mesortingcName"),root.get("ddType"), root.get("ddName"),root.get("discoveryId"))); qry.where(cb.and((Predicate[]) creteriaList.toArray(new Predicate[0])), predicate3); TypedQuery<TbiDDDetailView> tq = em.createQuery(qry); return tq.getResultList();