createNativeQuery set parameter

Question

I have the following that contains a NativeQuery where i need to set a parameter but somothing is wrong beacause parameter not set so the query is

SELECT movieId, title, genres FROM movies where title like '%%'"

so return all the rows. What is wrong

public List<T> findMovie(String keyword) {
        Query q = getEntityManager().createNativeQuery("SELECT movieId, title, genres FROM movies where title like '%?%'", entityClass);
        q.setParameter(1, keyword); //etc
        return q.getResultList();
    }

"?" is JDBC syntax NOT JPA Native query syntax. If using numbered parameters the syntax is "?1", "?2" etc. Besides which you should use what bc004346 answer says and write this as JPQL hence portable — Neil Stockton, Feb 08 '17 at 08:25

score 11 · Answer 1 · answered Feb 07 '17 at 17:58

public List<T> findMovie(String keyword) {
    Query q = getEntityManager().createQuery("SELECT movieId, title, genres FROM movies where title like :keyword", entityClass);
    q.setParameter("keyword", keyword); //etc
    return q.getResultList();
}

If you want to use positional params, use this syntax:

public List<T> findMovie(String keyword) {
    Query q = getEntityManager().createQuery("SELECT movieId, title, genres FROM movies where title like ?1", entityClass);
    q.setParameter(1, keyword); //etc
    return q.getResultList();
}

Md. Sajedul Karim · Answer 2 · 2019-07-25T06:41:39.767

Suppose your query is "SELECT movieId, title, genres FROM movies where title like = thor".

Here, your query will return a list with movieId, title, genres parameters. In your native query it will return a list of Object[]. Here Object[] has your desired data with specific position.

You can follow steps:

Your Projected Response class will like bellow:

public class MovieObject{
    int movieId;
    String title;
    String genres;

    public MovieObject(Object[] columns) {
        this.movieId = (columns[0] != null)?((BigDecimal)columns[0]).intValue():0;
        this.title = (String) columns[1];
        this.genres = (String) columns[2];
    }

    public int getMovieId() {
        return movieId;
    }

    public void setMovieId(int movieId) {
        this.movieId = movieId;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getGenres() {
        return genres;
    }

    public void setGenres(String genres) {
        this.genres = genres;
    }
}

here MovieObject constructor will map position-wise data of Movie Object[].

public MovieObject(Object[] columns) {
            this.movieId = (columns[0] != null)?((BigDecimal)columns[0]).intValue():0;
        this.title = (String) columns[1];
        this.genres = (String) columns[2];
        }

Your query execution function will like bellow :

public List<MovieObject> getMovieByTitle(EntityManager entityManager,String title) {

    String queryStr = "SELECT movieId, title, genres FROM movies where title like = ?1";
    try {
        Query query = entityManager.createNativeQuery(queryStr);
        query.setParameter(1, title);

        List<Object[]> objectList = query.getResultList();

        List<MovieObject> result = new ArrayList<>();
        for (Object[] row : objectList) {
            result.add(new MovieObject(row));
        }
        return result;
    } catch (Exception e) {
        e.printStackTrace();
        throw e;
    }
}

Here imports are:

import javax.persistence.Query;
import javax.persistence.EntityManager;

Now your main class, you have to call this function. First get EntityManager and call this getMovieByTitle(EntityManager entityManager,String title) function. Calling procedure is given bellow:

Import this

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

Now you have to call the function that will execute the query and return the value. Your execution code is like bellow:

@PersistenceContext
private EntityManager entityManager;

List<MovieObject> movieObjects=getMovieByTitle(entityManager,"thor");

Now, your processed data is in the List movieObjects.

If you want more details then visit this thread

Thanks :)

Niranga Sandaruwan · Answer 3 · 2021-09-16T10:15:29.650

0

public List<T> findMovie(String keyword) {
    Query q = getEntityManager().createQuery("SELECT movieId, title, genres FROM movies where title like CONCAT('%',?,'%')", entityClass);
    q.setParameter(1, keyword); 
    return q.getResultList();
}

edited Sep 16 '21 at 10:15

answered Sep 16 '21 at 09:52

Niranga Sandaruwan

691
2
19
39

createNativeQuery set parameter

3 Answers3

Linked