Dynamic Field Selection

Hibernate ORM
1

Hi,

Is there any way to select dynamic fields

like for selection I will declare a method in my DAO :

public List findBySearchcriteria(EmployeeSearchCriteria sc);

Now My EployeeSearchCriteria extends EmployeeBean (for availability of bean’s getter & setters)
also my EmployeeSearchCriteria do have an array field like below including getter & setters:

String[] includeMask;

this array must have only those fields name which I want to select from my Employee entity.
Sine if my table doe have 72 columns out of that I hardly need 15-20 columns for reporting purpose that too will be dynamic like

  1. report A needs 10 columns
  2. report B needs 12 columns information
    …
    …
    N. report n needs x set of columns from Employee entity

without writing N number of different methods to fetch such type of report using includeMask
how can I select dynamically using search criteria

can some one helpme if you didn’t get I will add sample code.

we were on similar line of orm which used to generate end sql by these patterns so I used that way to explain my query.

My issue is I am not getting any reference that how to dynamically select fields programmatically using Hibernate

if you don’t get my issue explanation you should check this link https://stackoverflow.com/questions/49016296/hibernate-dynamic-field-selection/54786248#54786248

2

You can use Criteria API for that.

3

But you guys deprecated criteria API of hibernate, I was Intent to use JPA criteria API which do not have this capability,

Actually We are migrating our existing system to Spring & Hibernate,
Criteria API of hibernate does have this capability in 5.1.16 version (since we are yet to move on JDK8 which will take another 1 year) now in that case if I use criteria API insted of JPA criteria API I will be end creating similar blockade for my code base where moving out of depricated API will create Lot of pain

On Other side JPA criteria API does lack in this regard

I have searched all these references but each and every of them only have predefined set of selection for they are not showing how to select fields dynamically

https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#criteria

4

Also my point is in each and every example out there including Hibernate JPA based Criteria Reference also says same that If my Entity have A, B, C, D, E, F fields (mapping to table is understood)
then if I need in one of my service only field A & B then i have to write

public List<Object> getFewColumns(MySearchCriteria sc) {
Path<String> A= root.get( MyEntity_.A );
Path<String> B = root.get( MyEntity_.B);

criteria.multiselect( A, B );
criteria.where( builder conditions );

List<Object[]> aAndBList= entityManager.createQuery( criteria ).getResultList();
return aAndBList;
}

// for better understanding 
/*MySearchCriteria  is a class extending our Entity AKA Bean AKA POJO, which have additional fields like */

private String[] includeMask; /* columns which needs to be selected only those fields name must be set as array */

private String[] excludeMask;/* If i have 70 columns out of that I want to select 65 then I can set rest 5 excluding columns and ORM should exclude only those columns */

private String[] valueAIn; /* for where clause in which I can set a specific to A fiels value upto 1000 entries to use dynamically create in criteria */

private String[] orderBy[]; /* for dynamically setting columns in which i want ordering
its and Search criteria class which can help me to build where clause and select clause dynamically and pass it to my DAO which will prepare SQL/JPQL/HQL according to this clause field values,it helps to keep intact Entity as well helps to prepare dynamic query without creating boiler plate methods */

now if I want to get A,B & F columns only from same method getFewColumns I can’t i have to amend it or need to write another method for specifically selection of A, B & F

this leads boiler plate code also if I want to use NamedQueries that will not help me since that also leads to boiler plate code.

One more drawback I am not getting object of my Entity, instedof that I am getting Object Array List which I need to further cast back to my Entity object, as have to initialize entity and set each selected fields eg

List<MyEntity> entityList = new ArrayList(aAndBList.size);
for(Object[] obj: aAndBList){

MyEntity entity = new MyEntity ();
entity .setA(obj[0]);
entity .setB(obj[0]);

entityList .add(entity);
}

Above code is absolutely unnecessary if orm can’t give me this task done automatically.

hope you got my point, if you need I can share my old framework working which has this capability and guide me how can I achieve same in JPA criteria Query of Hibernate,

we r moving out of old ORM because its proprietary to us only, no proper documentation & reference and there is no scalability along with features like cashing & transaction management

5

You can use the JPA Criteria API to select columns dynamically. I don’t see any limitation. You can use a Tuple to hold the result set instead of using the Object[].

So, what exactly did Hibernate Criteria ordered that you cannot do with JPA Criteria API?

Add some code examples showings thise limitations as it is not clear what your problem is.

6

Apology I added my code snippet and example just before your reply

7

Other the excludedIn, you could achieve the same goal with JPA Criteria API.

8

Can help me by sharing some comprehensive example since even tutorial of hibernate is not comprehensive as its explanation do not cover requirements like i am looking for, sorry m asking but did you get properly what I am asking and idea of my code working in
my post above?

9

If you think the default User Guide is not comprehensive, then add Jira issues where you describe what exactly is missing and I’ll investigate it.

10 
CriteriaBuilder builder = entityManager.getCriteriaBuilder();

CriteriaQuery<Object[]> criteria = builder.createQuery( Object[].class );
Root<Person> root = criteria.from( Person.class );

Path<Long> idPath = root.get( Person_.id );
Path<String> nickNamePath = root.get( Person_.nickName);

criteria.select( builder.array( idPath, nickNamePath ) );
criteria.where( builder.equal( root.get( Person_.name ), "John Doe" ) );

List<Object[]> idAndNickNames = entityManager.createQuery( criteria ).getResultList();

for example above(taken from https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#criteria) part as you were mentioning How can I re-write so it give me below result

CriteriaBuilder builder = entityManager.getCriteriaBuilder();

CriteriaQuery<Object[]> criteria = builder.createQuery( Object[].class );
Root<Person> root = criteria.from( Person.class );

Path<Long> idPath = root.get( Person_.id );
Path<String> nickNamePath = root.get( Person_.nickName);

criteria.select( builder.array( idPath, nickNamePath ) );
criteria.where( builder.equal( root.get( Person_.name ), "John Doe" ) );


List<Person> idAndNickNames = entityManager.createQuery( criteria ).getResultList();

here only ID & NICK_NAME fields will be initialized by data from data base so list will be containing Person class

this is the result I wanted because explicitly initializing Person class for result obtained from getResultList() is not scalable

also this is only one part since

criteria.select( builder.array( idPath, nickNamePath ) );

in this I can not pass array like below
criteria.select((Selection) builder.array(new Selection[]{ idPath, nickNamePath} ) );

because if i do pass it throws exception

Selection items in a multi-select cannot contain compound array-valued elements

11

All the code is on GitHub in the documentation folder, so you can just run it and see that everything woks just fine.

In your case, maybe you should use multiselect. Anyway, this discussion goes nowhere, so you are better off studying the Criteria API documentation and see that it will allow you to achieve your goal.

12

Exactly my point is it only fetches ID, NICK_NAME only if I need to make it ID, NICK_NAME, LAST_NAME for eg then I have to amend the code of base method Let me prepare reference I will share you in while and you will help me to figure out how can I achieve this in JPA Hibernate

13

You can make it dynamic by creating the Path based on the provided list of columns you want to fetch. Hence, no issue.

14

But there Path is created one by one even If I want to create list of path which method I need to invoke since

criteria.select( builder.array( idPath, nickNamePath ) );

is even taking elipses there i can pass Path[] but when I pass it, it throws

Selection items in a multi-select cannot contain compound array-valued elements exception along with IllegalArgumentException

15 
//My Bean
@Entity
@Table(name= "ATUL")
public class AtulBean implements Serializable
{

	//ID, NAME, TELEPHONE
	@Id
	@Column(name="ID")
	private String id;
	
	@Column(name="NAME")
	private String name;
	
	@Column(name="TELEPHONE")
	private String telephone;
	
	@Column(name="ACCOUNT_STATUS") // Can Have values like : pending, confirmed, receieved
	private String accountStatus;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getTelephone() {
		return telephone;
	}
	public void setTelephone(String telephone) {
		this.telephone = telephone;
	}
	
	public String getAccountStatus() {
		return accountStatus;
	}
	public void setAccountStatus(String accountStatus) {
		this.accountStatus = accountStatus;
	}


}

//My Search Criteria
public class AtulSearchCriteria extends AtulBean
{
	private boolean AND = true;
	private String[] orderBy; 
	private boolean[] desc; 
	private String[] groupBy;
	private String[] includeMask;
	
	private boolean isLikeName;
		
	public boolean isAND() {return AND;}
	public void setAND(boolean AND) {this.AND=AND;}
	
	public String[] getOrderBy() {return orderBy;}
	public void setOrderBy(String[] orderBy) { this.orderBy = orderBy;}
	
	public String[] getGroupBy() {return groupBy;}
	public void setGroupBy(String[] groupBy) {this.groupBy=groupBy; }
	
	public boolean[] isDesc() {return desc;}
	public void setDesc(boolean[] desc) { this.desc=desc; }
	
	public boolean isLikeName() {return isLikeName;}
	public void setIsLikeName(boolean isLikeName) {this.isLikeName=isLikeName;}
	
	
	public String[] getIncludeMask() {return includeMask;}
	public void setIncludeMask(String[] includeMask) { this.includeMask=includeMask;}
}

// My Dao
@Component
public class AtulDao 
{
	private final String ID = "id";
	private final NAME = "name";
	private final TELEPHONE = "telephone";
	private final ACCOUNT_STATUS = "accountStatus";
	
	EntityManagerFactory entityManagerFactory;
	
	private EntityManager entityManager;

    private CriteriaBuilder criteriaBuilder;

    private CriteriaQuery<AtulBean > criteriaQuery;

    private CriteriaUpdate<AtulBean > criteriaUpdate;

    private CriteriaDelete<AtulBean > criteriaDelete;

	
	public EntityManager getEntityManager()
    {
        if(this.entityManager==null)
            entityManager= entityManagerFactory.createEntityManager();

        return entityManager;
    }

    // Generic
    public CriteriaBuilder getCriteriaBuilder()
    {
        if(this.criteriaBuilder==null)
            criteriaBuilder= getEntityManager().getCriteriaBuilder();
        return criteriaBuilder;
    }

    // Generic
    @SuppressWarnings({"rawtypes","unchecked"})
    public CriteriaQuery<AtulBean > getCriteriaQuery(Class<AtulBean > bean)
    {
        if(this.criteriaQuery==null)
            criteriaQuery= getCriteriaBuilder().createQuery(bean);
        return criteriaQuery;
    }

    // Generic
    @SuppressWarnings({"rawtypes","unchecked"})
    public CriteriaUpdate getCriteriaUpdate(Class<AtulBean > bean)
    {
        if(this.criteriaUpdate==null)
            criteriaUpdate= getCriteriaBuilder().createCriteriaUpdate(bean);
        return criteriaUpdate;
    }

    // Generic
    @SuppressWarnings({"rawtypes","unchecked"})
    public CriteriaDelete<EmployeeBean> getCriteriaDelete(Class<EmployeeBean> bean)
    {
        if(this.criteriaDelete==null)
            criteriaDelete= getCriteriaBuilder().createCriteriaDelete(bean);
        return criteriaDelete;
    }
	
	//Finding On Basis Of Criteria	
	public List<AtulBean> findAtulBean(AtulSearchCriteria sc) throws Exception
	{
		List<Predicate> predicateList= SQLExpression(sc);
	

		 if(sc.isAND())
            predicate= criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
        else
            predicate= criteriaBuilder.or(predicateList.toArray(new Predicate[predicateList.size()]));

        if(sc.isDistinct())
            criteriaQuery.distinct(sc.isDistinct());
		
// How To Apply Include Mask Here
		
		TypedQuery<EmployeeBean> typedQuery= entityManager.createQuery(criteriaQuery);
        return typedQuery.getResultList();
	}
	
	
	public List<AtulBean> deleteAtulBean(AtulSearchCriteria sc) throws Exception
	{
		List<Predicate> predicateList= SQLExpression(sc);
		
		// This I know as below will work
		criteriaDelete.where(predicateList);
	}

	public void updateAtulBeanBulk(AtulBean bean, AtulSearchCriteria sc) throws Exception
	{
		CriteriaUpdate<AtulBean > criteriaUpdate= getCriteriaUpdate((Class<AtulBean>) bean.getClass());
        Root<AtulBean > root= criteriaUpdate.from((Class<AtulBean >) bean.getClass());

		
		//getValueToAssign(bean) I have not written this but it 
		//will invoke getter of field from bean using reflection
		if(sc.getIncludeMask()!=null)
			for(String fields : sc.getIncludeMask())
				criteriaUpdate.set(fields, getValueToAssign(bean, field));
				
		// This could be achieved by automatically since there must be mechanism by which above for loop 
		// can be avoided and mapping must be auto detected by ORM.
        
		List<Predicate> predicateList= SQLExpression(sc);
		
		// This I know as below will work
		criteriaUpdate.createQuery(predicateList).executeUpdate();
	}
	
	
	public List<Predicate> SQLExpression(AtulSearchCriteria sc)
    {
		List<Predicate> predicateLists = new ArrayList<Predicate>();
		if(!StringUtils.isBlank(sc.getId()))
			predicateLists.add(criteriaBuilder.equal(mappingBean.get(ID), sc.getId()));

		if(!StringUtils.isBlank(sc.getName()))
			predicateLists.add(criteriaBuilder.equal(mappingBean.get(NAME), sc.getName()));
		
		if(!StringUtils.isBlank(sc.getTelephone()))
			predicateLists.add(criteriaBuilder.equal(mappingBean.get(TELEPHONE), sc.getTelephone()));
		
		if(!StringUtils.isBlank(sc.getAccountStatus()) && sc.isLikeName)
			predicateLists.add(criteriaBuilder.like(mappingBean.<String>get(ACCOUNT_STATUS), "%"+sc.getAccountStatus()+"%"));
		
		return predicateLists;
	}
	
}

// This Class Is just for demonstration
public class Action 
{
	@Inject
	AtulDao dao;

	// Differrent methods arerepresentation of different business model scenarios where different data needed from
	// from same table
	// As of now I am considering simple selection
	
	private void searchAtulDetails1()
	{
		AtulSearchCriteria aSC =new  AtulSearchCriteria();
		aSC.setIncludeMask(new String[]{"name","status","id"});
		aSC.setName("At");
		aSC.setIsLikeName(true);
		
		 List<AtulBean> atulList = dao.findAtulBean(AtulSearchCriteria sc) 
	}
	
	private void searchAtulDetails2()
	{
		AtulSearchCriteria aSC =new  AtulSearchCriteria();
		aSC.setIncludeMask(new String[]{"name","id"});
		aSC.setName("At");
		aSC.setIsLikeName(true);
		
		 List<AtulBean> atulList = dao.findAtulBean(AtulSearchCriteria sc) 
	}

	private void searchAtulDetails3()
	{
		AtulSearchCriteria aSC =new  AtulSearchCriteria();
		aSC.setIncludeMask(new String[]{"telephone","id"});
		aSC.setName("At");
		aSC.setIsLikeName(true);
		aSC.setDesc(true);		
		List<AtulBean> atulList = dao.findAtulBean(AtulSearchCriteria sc) 
	}
	
}

Please ignore if any syntactical mistake

Above code is sample what I was talking about

here above codes if you check inside Action you will get what I need also

Hibernate & JPA talks about well designed db schema where every table maintains proper relation with its corresponding table like for eg Employee & Dept both have many to one scenario but what about where data base is not defined so properly structured and non of the table follow proper schema relations in that scenario we know how data relates but to wire them in proper relations is mammoth task for which organization don’t allow in such conditions how join solutions can help from hibernate no where its written

my point is real work challenges are missing in tutorials

16

If you think there is a use case that’s not covered by the User Guide, then you should create a Jira issue for it. And, try to provide a Pull Request too since that’s how open source software is supposed to work.

The JPA spec is open for enhancement. Add an issue if the spec is missing a certain feauture. However, you nred to read the JPA spec to make sure the feature you want to use is nit already there.

17

Apology I bother you, but do you get Dynamic Field Selection and can help me to find solution as I am new to hibernate & JPA.

I repeat I need help since I have already read n no of time the hibernate tutorial and tried all possible combination but couldn’t succeed. if you can help it will be great as I am running out of time for delivery and I am very new to Hibernate.

// This Class Is just for demonstration
public class Action 
{
	@Inject
	AtulDao dao;

	// Differrent methods arerepresentation of different business model scenarios where different data needed from
	// from same table
	// As of now I am considering simple selection
	
	private void searchAtulDetails1()
	{
		AtulSearchCriteria aSC =new  AtulSearchCriteria();
		aSC.setIncludeMask(new String[]{"name","status","id"});
		aSC.setName("At");
		aSC.setIsLikeName(true);
		
		 List<AtulBean> atulList = dao.findAtulBean(AtulSearchCriteria sc) 
	}
	
	private void searchAtulDetails2()
	{
		AtulSearchCriteria aSC =new  AtulSearchCriteria();
		aSC.setIncludeMask(new String[]{"name","id"});
		aSC.setName("At");
		aSC.setIsLikeName(true);
		
		 List<AtulBean> atulList = dao.findAtulBean(AtulSearchCriteria sc) 
	}

	private void searchAtulDetails3()
	{
		AtulSearchCriteria aSC =new  AtulSearchCriteria();
		aSC.setIncludeMask(new String[]{"telephone","id"});
		aSC.setName("At");
		aSC.setIsLikeName(true);
		aSC.setDesc(true);		
		List<AtulBean> atulList = dao.findAtulBean(AtulSearchCriteria sc) 
	}
	
}
18

I already provided you the solution. Use multiselect anf it should work. For more details, check out the JPA specification too.

Anyway, if you have a solution based in the Hibernate Criteria, use that for now. It was not decided when it will be removed, so you can still use it for a while.

20

nred : Insulting people is easy, I don’t think so I am since, its easy for you guys to read 577 pages of JPA specifications, because you r the team who creates Hibernate and maintain it to align with default Java specification, its your task, I am as of now a user, just looking help from creators of API.

Also in process to create my desired output I reached where this exception is threw by hibernate
when I used source code instead of hibernate jar


 org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [com.journaldev.spring.persistence.EmployeeBean]. Expected arguments are: java.lang.String, java.lang.String [select new com.journaldev.spring.persistence.EmployeeBean(generatedAlias0.empId, generatedAlias0.empName) from com.journaldev.spring.persistence.EmployeeBeanas generatedAlias0 where generatedAlias0.rntContact=:param0]
	at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:92) ~[main/:?]
	at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:110) ~[main/:?]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:288) ~[main/:?]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:200) ~[main/:?]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142) ~[main/:?]
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:127) ~[main/:?]
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:81) ~[main/:?]
	at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:151) ~[main/:?]
	at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:276) ~[main/:?]
	at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:214) ~[main/:?]
	at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1913) ~[main/:?]
	at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:483) [main/:?]
	at org.hibernate.jpa.criteria.CriteriaQueryImpl$1.buildCompiledQuery(CriteriaQueryImpl.java:335) [main/:?]

but my doubt is that, what is causing Hibernate to seek for constructor based initialization because I wanted to initialize the object by getter & setter methods of its fields.

If you can help me out so I can build direct initialization of bean instead of returning Object array while using javax.persistence.criteria.Selection in CriteriaQuery.multiselect method.

Because possibly you guys and Java also suggests that for any specific requirement there must be specific DTO which should only have those specific fields but I deffer that openion since if I have already class(Pojo or bean) I will defiantly use that for reusability for example:

I have a table EMPLOYEE which have emp_id, manager_id, emp_name, emp_dept, emp_address columns

so its equivalent bean will have Employee entity or pojo or bean now in that case if I want only in my selection emp_id and manager_id then I will be forced to create another DTO class EmployeeDTO which will have constructor EmployeeDTO(empId, managerId)

this actually against re-usability since I have already Employee class and for each new requirements I have to create new DTO class it creates unnecessary boilerplate code and barred developer to achieve reusability of bean as data carrier

possibly you are referring below code snippet as your solution but its not helping since I have to create a DTO class with parameterized constructor

      public List dbFind3(EmployeeSearchCriteria sc) throws Exception
    {
     * CriteriaQuery<Object[]> query= getCriteriaBuilder().createQuery(Object[].class);// query object
       Root<EmployeeBean>  mappingBean= query.from(EmployeeBean.class);

        List<Predicate> predicateList= SQLExpression(sc);  //returns Predicate List

        Predicate predicate= null;
        if(sc.isAND())
            predicate= criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
        else
            predicate= criteriaBuilder.or(predicateList.toArray(new Predicate[predicateList.size()]));

        query= query.where(predicate);

        query.multiselect(new Selection[]{mappingBean.get("empId"),mappingBean.get("empName")});
    **    List<Object[]> list= entityManager.createQuery(query).getResultList();

        return list;
    }

My requirement was like below

Check * :   CriteriaQuery<EmployeeBean> query= getCriteriaBuilder().createQuery(EmployeeBean.class);// query object


And at **: List<EmployeeBean> list= entityManager.createQuery(query).getResultList();

Here inside EmployeeBean there is no constructor defined and its exact mapping class having fields more than what I am trying to select in above code snippet.

Now I have no wonder what solution you are talking about, even I will read JPA specs it will not help because I don’t know your API internal working, for which I am requesting your help

Now I request help me instead of of insulting me.Hope you are able to connect what I am talking above.

21

Hey @vlad I found something weird or possibly a heck in Hibernate

I think its flaw from my point of view(m not sure what is your rationale)

If i have script like below:

select name, email from customer;

but at the same time if I do like below:

  CriteriaQuery<CustTestBean> query= getCriteriaBuilder().createQuery(CustTestBean.class);
Root<RntBean> mappingBean =  query.from(CustTestBean.class);

query.multiselect(new Selection[]{mappingBean.get("email"),mappingBean.get("name")});

 TypedQuery<CustTestBean> typedQuery= entityManager.createQuery(query);

List<CustTestBean> list= typedQuery.getResultList();

then result I get is like below:

[name=jondeo@abc.com, email=Smith Jones]

which is again kind of blind mapping,what is the solution there,