I would like to return a List of Integers from a
javax.persistence.EntityManager.createNativeQuery call
Why is the following incorrect?
entityManager.createNativeQuery("Select P.AppID From P", Integer.class);
specifically why do I get "...Unknown entity: java.lang.Integer"
Would I have to create an entity class that has a single field that is an Integer ?
Thanks
What you do is called a projection. That's when you return only a scalar value that belongs to one entity. You can do this with JPA. See scalar value.
I think in this case, omitting the entity type altogether is possible:
Query query = em.createNativeQuery( "select id from users where username = ?");
query.setParameter(1, "lt");
BigDecimal val = (BigDecimal) query.getSingleResult();
Example taken from here.
Raw use of parameterized class 'Query' warning here.That doesn't work because the second parameter should be a mapped entity and of course Integer is not a persistent class (since it doesn't have the @Entity annotation on it).
for you you should do the following:
Query q = em.createNativeQuery("select id from users where username = :username");
q.setParameter("username", "lt");
List<BigDecimal> values = q.getResultList();
or if you want to use HQL you can do something like this:
Query q = em.createQuery("select new Integer(id) from users where username = :username");
q.setParameter("username", "lt");
List<Integer> values = q.getResultList();
Regards.
List<Integer> values = q.getResultList(); some how without looping and creating new Integers.
Here is a DB2 Stored Procidure that receive a parameter
CREATE PROCEDURE getStateByName (IN StateName VARCHAR(128))
DYNAMIC RESULT SETS 1
P1: BEGIN
-- Declare cursor
DECLARE State_Cursor CURSOR WITH RETURN for
-- #######################################################################
-- # Replace the SQL statement with your statement.
-- # Note: Be sure to end statements with the terminator character (usually ';')
-- #
-- # The example SQL statement SELECT NAME FROM SYSIBM.SYSTABLES
-- # returns all names from SYSIBM.SYSTABLES.
-- ######################################################################
SELECT * FROM COUNTRY.STATE
WHERE PROVINCE_NAME LIKE UPPER(stateName);
-- Cursor left open for client application
OPEN Province_Cursor;
END P1
//Country is a db2 scheme
//Now here is a java Entity bean Method
public List<Province> getStateByName(String stateName) throws Exception {
EntityManager em = this.em;
List<State> states= null;
try {
Query query = em.createNativeQuery("call NGB.getStateByName(?1)", Province.class);
query.setParameter(1, provinceName);
states= (List<Province>) query.getResultList();
} catch (Exception ex) {
throw ex;
}
return states;
}
JPA was designed to provide an automatic mapping between Objects and a relational database. Since Integer is not a persistant entity, why do you need to use JPA ? A simple JDBC request will work fine.
