Hiberante 3.0 inner join for two different entity with forigen key

1
votes

I am new with this. Please help me.

My controller for listing looks like this: @RequestMapping(value="/possalesList.htm",method=RequestMethod.GET) public String PosSalesList(@ModelAttribute("possales")PSales sales,@RequestParam(value = "salesItemID", required = false)Integer salesItemID,ModelMap model) { List<POSSalesItem> posSalesItemList = posSalesDao.listSalesItem(); model.addAttribute("possalesList", posSalesItemList);
return "possalesList"; }

My hibernateDaoimpl looks like this: @SuppressWarnings("unchecked") @Transactional(readOnly=true) public List<POSSalesItem> listSalesItem() { return (List<POSSalesItem>)getHibernateTemplate().find("select POSSales.posSalesId , POSSalesItem.itemName from POSSales inner join POSSalesItem on POSSales.posSalesId=POSSalesItem.posSalesId"); }

My inner join looks like this: select POSSales.posSalesId , POSSalesItem.itemName from POSSales inner join POSSalesItem on POSSales.posSalesId=POSSalesItem.posSalesId

the foreign key is created successfully in mysql.

what i am doing is i am listing posSalesId from POSSales table and other columns from POSSalesItem .for that purpose i have written this join query but its showing some error.

I want to make the inner join on the column posSalesId, but I get these errors:

org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: on near line 1, column 111 [select POSSales.posSalesId , POSSalesItem.itemName from com.jewellery.entity.POSSales inner join POSSalesItem on POSSales.posSalesId=POSSalesItem.posSalesId]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 111 [select POSSales.posSalesId , POSSalesItem.itemName from com.jewellery.entity.POSSales inner join POSSalesItem on POSSales.posSalesId=POSSalesItem.posSalesId] org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:660) org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411) org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)

1
hibernate
You should add the actual hibernate code to your post if you want help with this, it seems you only posted your generated SQL – Rasmus Franke
Your question is unreadable. Take some time to format it correctly. – JB Nizet
@RasmusFranke can u help me nw...i have posted the controller and hibernate part also. – viquar

1 Answers

0
votes

Your HQL syntax inner join POSSalesItem on POSSales.posSalesId=POSSalesItem.posSalesId is malformed.

The correct syntax is (no select necessary)

from POSSales s inner join s.POSSalesItemList

The join condition you have to implement in the mapping (or annotations) where you tell which class the members of POSSalesItemList are and what the key column (join column) is. For this have a look into the hibernate documentation, one-to-many association with annotations.