JPA entitymanager run native sql query to get everything out from a table

Question

What I need to do is to run a piece of native sql query to select everything and get data out from a specific table.

My app is a spring hibernate based web app. Here is my code:

DAOserviceImpl:

@Service public class ItemServiceImpl implements ItemService { @PersistenceContext EntityManager em; @Transactional public void addItem(Item item) { em.persist(item); } @Transactional public List<Item> iosADVsearchResults(String itemCode) { //run native query with jpa List<Item> itemList = (List<Item>)em.createQuery("SELECT * FROM item itemcode='" + itemCode + "'") .getResultList(); return itemList; } }

but what I eventually get is this error:

HTTP Status 500 - Request processing failed; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: = near line 1, column 35 [SELECT itemcode FROM item itemcode='ll3369']

I was following this tutorial: http://www.oracle.com/technetwork/articles/vasiliev-jpql-087123.html

Please help, any code example would be helpful.

Gemtastic · Accepted Answer · 2016-04-17 18:15:46Z

If You are using JPA your query to get all records should be like this:

List<Item> itemList = (List<Item>) em.createQuery("SELECT i FROM item where itemcode=?1) .setParameter(1, itemcode) .getResultList();

this will return all the record which matches the parameter itemcode ..

Predrag Maric · Accepted Answer · 2014-12-23 15:41:50Z

em.createQuery() expects a query written in JPQL, not SQL (see this for some info). For SQL query, use em.createNativeQuery()

List<Item> itemList = (List<Item>)em.createNativeQuery("SELECT * FROM item WHERE itemcode='" + itemCode + "'", Item.class).getResultList();

Collectives™ on Stack Overflow

JPA entitymanager run native sql query to get everything out from a table

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related