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 " ?

Answer 1

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 these 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();