JPA2 (Hibernate) how to query 2 tables (joined) but only need certains properties from 2nd table

Question

I am using JPA2 with hibernate as provider in java ee6 environment.

I have a one to many relationship and when getting all the rows for the one side to display in a JSF page, I want to display some of the many side's properties, ** but not all ** and even avoid loading those properties them when populating the many side entities and I am not sure how to do that.

here is a snippet

I want to get all rows for table A and display them in jsf table, when displaying rows for table A, I want to also display some properties from table B in same jsf table, and ideally not even retrieve/store the unused properties from table B

TBL_A
________________
int     ID
varchar FIRSTNAME
varchar LASTNAME

and the second is

TBL_B
___________
int      ID
varchar  VERSION  // display on jsf page
varchar  XML_DATA //large, don't want to load when getting results for A/B join
int      A_ID

and my JPA2 associated class's are like so

@Entity
@Table(name = "TBL_A")
@NamedQueries // not sure what sql to create to accomplish this?
({ @NamedQuery(name = "A_and_B.findAll", query = "SELECT a FROM EntityA a") })

public class EntityA implements Serializable 
{    
   @Id
   @GeneratedValue(strategy=GenerationType.IDENTITY)
   @NotNull
   @Column(name = "ID", nullable = false)
   private Integer id;
.

@Column(name = "FIRSTNAME")
private String firstname;
.
@Column(name = "LASTNAME")
private String lastname;
.
@OneToMany(mappedBy = "entityA", fetch = FetchType.EAGER)
private List<EntityB> entityBList;

and my TBL_B associated entity is

@Entity
@Table(name = "TBL_B")  
public class EntityB implements Serializable 
{ 
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@NotNull
@Column(name = "ID", nullable = false)
private Integer id;
.        
@Column(name = "VERSION")
private String version;
.
@Column(name = "XML_DATA")
private String xmlData;
.
@JoinColumn(name = "A_ID", referencedColumnName = "ID")
@ManyToOne
private EntityA entityA;

I have a JSF page that displays a datatable/list of TBL_A entries and also the VERSION column from TBL_B, when getting all the rows for TBL_A.

I had to use the fetch type of EAGER to avoid hibernate lazy instatiation error to do that. hope thats the correct way to do it.

when loading all the TBL_B entities I guess I don't mind eagerly loading all TBL_B rows in entityBList, but I don't want the list of EntityB beans to have thier xmlData property loaded cause it is significantly large.

can someone explain how I can do this?

how can i make this happen with my named query

public List<EntityA> findAll_A_Entities() 
{
   return em.createNamedQuery("A_and_B.findAll", EntityA.class).getResultList();
}

score 0 · Accepted Answer · answered Dec 01 '11 at 03:53

0

One solution is to make the property lazy - but that requires additional build configuration - refer to the doc.

The other option is to move the xmlData Field to a derived class and use hibernate inheritance support.

If you were querying EntityB directly you can use projection query to selected only the required fields. When it is in a collection association that is not possible.

answered Dec 01 '11 at 03:53

gkamal

20,777
4
60
57

gkamal, I was hoping I could do a query like this: em.createQuery("SELECT a, b.version FROM EntityA a, EntityB b", EntityA.class).getResultList(); I was thinking this would populate a List of EntityA objects, each EntityA object has all its properties polilated. AND each of the EntityA objects has a collection of EntityB objects, but only thier version property is populated because query only pulls that property from EntityB via b.version ? – user825402 Dec 01 '11 at 17:23
You can use a projection query - but in that case hibernate will not be able to aggregate the list of b into a. You will get a flat list of a attrs + b attrs - the a rows will be repeated one for each associated b. Like what you would get by using a SQL join. The options I outlined are the best you can do without going too far from how other hibernate query operations work. If you need to do this kind of queries in lot of places you should re look at the choice of hibernate for persistence. – gkamal Dec 02 '11 at 06:41
thanks gkamal, I did try use `@Basic(fetch=FetchType.LAZY) @Column(name = "XML_DATA") String xmlData;` but i guess the JPA provider (hibernate) didn't honor the request because i found that in debugging the property was populated. In Pro JPA book: _the directive to lazily fetch an attribute is meant only to be a hint to the persistence provider to help the application achieve better performance. The provider is not required to respect the request because the behavior of the entity is not compromised if the provider goes ahead and loads the attribute._ :-( maybe i'll try ur 2nd suggestion – user825402 Dec 02 '11 at 16:04
For lazy fetching of property to work with hibernate you need to enable byte code instrumentation - the doc link (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html#performance-fetching-lazyproperties) has instructions on how to enable it with ant task - there might be ways of doing it for IDE builds / maven etc. – gkamal Dec 03 '11 at 06:53

JPA2 (Hibernate) how to query 2 tables (joined) but only need certains properties from 2nd table

1 Answers1