zwwcn

Just another WordPress.com site

JPA namedquery and mysql

JPA : query specified join fetching, but the owner of the fetched association was not present in the select list

JPA don’t support  right join, we have to use left join and the returned object must be the object after the first from clause.

correct : SELECT a FROM Customer a LEFT JOIN a.address ad…

wrong : SELECT count(ad) FROM Customer a LEFT JOIN a.address ad…

——————————-

native query:

String sqlString = “select count(*) as total from OP_CUSTOMER customer left outer join OP_CUSTOMER_ADDRESS_XREF custAddressType on customer.OBJECT_ID=custAddressType.CUSTOMER_ID left outer join OP_ADDRESSTYPE addressType  on custAddressType.ADDRESS_TYPE_ID=addressType.OBJECT_ID where customer.REC_STATUS=’A’ and addressType.rec_status=’A’ and customer.AGENCY_ID=:agencyId and (addressType.type=’ShipAddress’ or addressType.type=’BillAddress’)”;
Session session = (Session) em.getDelegate();
Query query = (Query)session.createSQLQuery(sqlString)
.setParameter(“agencyId”, currentAgency.getObjID());

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: