use of entityManager.createNativeQuery(query,foo.class)

Question

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

score 60 · Accepted Answer · answered Jan 21 '10 at 16:24

60

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.

answered Jan 21 '10 at 16:24

ewernli

38,045
5
92
123

Yes, My oversight I didn't need the 2nd parameter. Still puzzled as to why it didn't work. I have to work that out. Thanks – Jim Ward Jan 21 '10 at 19:07
2

It is better to used named parameters instead of "?" – Omar Al Kababji Apr 11 '13 at 12:54
It works but I get `Raw use of parameterized class 'Query'` warning here. – kio21 Feb 15 '22 at 15:28

score 38 · Answer 2 · answered Jan 21 '10 at 21:04

38

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.

answered Jan 21 '10 at 21:04

Omar Al Kababji

1,768
4
16
31

For JPA, is there a way, when using a native query, to specify how you would like the scalar value cast? I'd like to use `List values = q.getResultList();` some how without looping and creating new Integers. – Snekse Sep 14 '12 at 15:59
In Hibernate you can define your own dialect for mapping SQL types to Java types take a look at the org.hibernate.dialect interface, but this is not standard JPA. – Omar Al Kababji Sep 17 '12 at 08:29
FYI - ":username" doesn't work with Oracle connections (http://docs.oracle.com/cd/E18283_01/java.112/e16548/apxref.htm#BABEJDBH) – cs94njw Apr 09 '13 at 08:55
@cs94njw what you commented is related to the oracle JDBC driver it has nothing to do with named parameters. – Omar Al Kababji Apr 11 '13 at 12:53
2

It's important that someone reading this issue, knows that the Oracle driver doesn't support named parameters. I'd appreciate knowing that if I was looking for answers. – cs94njw Apr 12 '13 at 08:25
Yes but the question is related to JPA/Hibernate in that case named parameters are supported, even when using Oracle. – Omar Al Kababji Apr 12 '13 at 08:31
3

"The use of named parameters is not defined for native queries." http://stackoverflow.com/questions/3144235/jpa-hibernate-native-queries-do-not-recognize-parameters – emeraldhieu Nov 20 '14 at 08:52
2

Named parameters do not work for native queries (tried with Postgresql), you should use positional parameters instead. – P.Péter Apr 21 '15 at 07:54

score 6 · Answer 3 · edited Jun 20 '20 at 09:12

Here is a DB2 Stored Procidure that receive a parameter

SQL

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

Java

//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;
}

score 0 · Answer 4 · answered Aug 14 '17 at 22:30

0

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.

answered Aug 14 '17 at 22:30

jmb

129
11

use of entityManager.createNativeQuery(query,foo.class)

4 Answers4

SQL

Java

Linked