EntityManager Native Query Syntax?

Question

The following method uses the createNativeQuery() method of the Java entity manager:

   public List<Dog> findDogById(String id) {

        List<Dog> resultList = new ArrayList<>();
        try {
            resultList = persistence.entityManager().createNativeQuery(" SELECT * FROM DOG WHERE ID = '" + id+ "' ", DogEntity.class).getResultList();
        } catch (Exception e) {
        }
        return resultList;
    }

The issue that I am having is that this method is not returning results when I expect it to do so. I.e. when I run the queries directly through SQL Developer I get results, but the method does not return the same.

Is my Syntax correct? I am unsure about this:

" SELECT * FROM DOG WHERE ID = '" + id+ "' "

i.e. do I need both the ' and the " ?

score 6 · Accepted Answer · edited Jul 13 '21 at 06:19

Your syntax is correct but you have other problems in your code.

You are silently ignoring the exception. You are probably getting an exception, ignoring it and then returning the empty list:

    try {
        resultList = persistence.entityManager().createNativeQuery(" SELECT * FROM DOG WHERE ID = '" + id+ "' ", DogEntity.class).getResultList();
    } catch (Exception e) {
        // If an exception is thrown in this try block, you are ignoring it.
    }

If you use the query without parameter binding, you could have issues with SQL injection. To make an example, if someone sends 0' OR 1=1-- as the id in your function, the user get a full list of Dogs.

Use parameters and avoid this kind of problems, the queries are also more readable and less error prone:

.createNativeQuery(" SELECT * FROM DOG WHERE ID = ?1", DogEntity.class)
.setParameter(1, id)
.getResultList();

Thanks I have added the e.printStackTrace() which has shown that an error is thrown when the query attempts to execute. This would explain the empty lists being returned? — java123999, Sep 21 '16 at 12:46
Accepted answer as it helped solve my issue. My code was throwing an error that I was ignoring. The error was that my DB update script was not ran so Hibernate was looking for a Column that wasn't there yet. — java123999, Sep 21 '16 at 13:03

score 0 · Answer 2 · answered Sep 21 '16 at 11:03

0

To avoid problems you should use params:

Query query = em.createNativeQuery("SELECT * FROM DOG WHERE ID =  ?");  
query.setParameter(1, id);

answered Sep 21 '16 at 11:03

Marco A. Hernandez

791
4
15

EntityManager Native Query Syntax?

2 Answers2