How to fetch data from joined tables straight to a class?

Question

Is it possible to retrieve data from a query straight into the EmployeeForm?

Query as stored procedure empdata

SELECT a.name,b.username,b.password FROM Tbemployee left join Tbuser

Code

   List<EmployeeForm> form = new ArrayList<EmployeeForm>();
    EmpDB service = (EmpDB) RuntimeAccess.getInstance().getServiceBean(
    service.begin();
    Session session = service.getDataServiceManager().getSession();
    SQLQuery query = session.createSQLQuery("EXEC empdata");
    List list = query.list();
    formList = list;

This gives me an error:

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.emp.form.EmployeeForm

Use [JPA](http://docs.oracle.com/javaee/6/tutorial/doc/bnbpz.html) for object/relational mapping. — Prabhat, Jul 26 '17 at 11:18
Are you saying I can make entities that is not a table in the database? — Ar-jay Alaon, Jul 26 '17 at 11:20
check [this](https://stackoverflow.com/questions/37420401/mapping-hibernate-query-results-to-custom-class) and [this](https://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/transform/AliasToBeanResultTransformer.html) for reference. — Prabhat, Jul 26 '17 at 11:31

score 1 · Accepted Answer · answered Jul 26 '17 at 11:23

1

You need to use a ResultTransformer

The other option is to cast to List<Object[]> which contains rows with columns from the query result, and then iterate and extract data(more work).

The transformer could be something like:

query.setResultTransformer(Transformers.aliasToBean(EmployeeForm.class));

answered Jul 26 '17 at 11:23

isah

5,221
3
26
36

This works but when I add a date column It gives me an error "org.hibernate.property.BasicPropertyAccessor - expected type: java.util.Date, actual value: java.lang.String" does this resulttransformer returns string for all values? – Ar-jay Alaon Jul 26 '17 at 11:32
No, it works with dates as well, if your column on DB side is date. – isah Jul 26 '17 at 11:41
The error is gone now. My date is datetime so I used cast to convert it into date which I think caused the error? Does it also accept null values ? – Ar-jay Alaon Jul 26 '17 at 11:54
I'ved tried it on all common datatypes only exception was on boolean. It gives me an error if boolean is null. But case when null is applicable in this scenario. Thank you sir. – Ar-jay Alaon Jul 26 '17 at 12:06
Use `Boolean` wrapper, not `boolean` primitive. – isah Jul 26 '17 at 12:24

How to fetch data from joined tables straight to a class?

1 Answers1