Use Hibernate Query Language to Join Tables and Map Column Values to Java Objects

The following example creates a HQL statement with named parameters, executes the query then maps the query result back to Java objects. The two named parameters in the example are accountNumber and trackingId. The three selected columns are unitPrice, qty and itemName from the Transactions table. The ALIAS_TO_ENTITY_MAP is used as the result transformer. Then the unitPrice column is mapped to Java float, qty is mapped to int and itemName is mapped to String type.

private void hqlTest(){
	SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
	Session session = sessionFactory.getCurrentSession();

	String hql =
		"select  oi.unitPrice, oi.qty, oi.itemName from OrderItems as oi"
		+ " join Orders as o on o.id=oi.orderId"
		+ " where o.accountNumber= :accountnumber o.certTrackingId=:trackingid";

	Query query = session.createSQLQuery(hql).setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);;
	query.setParameter("accountnumber", 12345);
	query.setParameter("trackingid", "abc123");

	@SuppressWarnings("rawtypes")
	List sqlResults = query.list();
	if (!sqlResults.isEmpty()) {
		for (Object result : sqlResults) {
			@SuppressWarnings("unchecked")
			Map<String, Object> aliasToValue = (Map<String, Object>) result;
			float unitPrice = (float)aliasToValue.get("unitPrice");
			int quantity = (int) aliasToValue.get("qty");
			String productName = (String) aliasToValue.get("itemName");
		}
	}
}
Advertisements
This entry was posted in Database, Java and tagged , . Bookmark the permalink.

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