Error: Cannot create TypedQuery for query with more than one return

Question

I try to do the function searchBook with java and jpa. I have 2 classes which are Media and Book. Book extends Media. And I keep the data in the different table. I try to select the data from the query below:

TypedQuery<Media> query = em.createQuery(
                "SELECT m.title, b.isbn, b.authors"
                        + " FROM Book b, Media m" + " WHERE b.isbn = :isbn"
                        + " OR lower(m.title) LIKE :title"
                        + " OR b.authors LIKE :authors", Media.class);
        query.setParameter("isbn", book.getisbn());
        query.setParameter("title", "%" + book.getTitle().toLowerCase()
                + "%");
        query.setParameter("authors", "%" + book.getAuthors() + "%");
        bookList = query.getResultList();

But I got the error:

java.lang.IllegalArgumentException: Cannot create TypedQuery for query with more than one return

This is the first time I use JPA. I can't find the the mistake.

Answer 1

As a workaround, to get entity composed by other entity attributes, you can create it within query, providing constructor for it.

Query :

TypedQuery<Media> query = em.createQuery("SELECT NEW package_name.Media(m.title, b.isbn, b.authors)"
+ " FROM Book b, Media m" 
+ " WHERE b.isbn = :isbn"                         
+ " OR lower(m.title) LIKE :title"                         
+ " OR b.authors LIKE :authors", Media.class); 

Entity :

public Media(String title, int isbn, String author){

    //-- Setting appropriate values
}

I have provided sample, change the datatypes of the constructor accordingly.

Answer 2

Without goind into details about how Media and Book should be modeled, I will at least explain why you get this exception.

You're doing:

em.createQuery(someJPQL, Media.class);

This means: create a query using someJPQL, and this query will return instances of the Media entity.

But your JPQL is:

SELECT m.title, b.isbn, b.authors ...

So the query does not return entities of type Media. It returns three fields, from two different entities. There is no way your JPA engine could magically create instances of Media from these 3 columns. A query would return instances of Media if it looked like this:

select m from Media m ...

Answer 3

If you still want to use TypedQuery you can change the result type to Object[].

List<Object[]> results = entityManager
    .createQuery("SELECT m.title, b.isbn, b.authors ...", Object[].class)
    .getResultList();

Each Object[] in the List represents a row of data. It contains the selected values for that row in the order in which they were selected in the query. Element 0 is the title, element 1 is the ISBN, and element 2 is the authors. You'll likely need to cast those values if you want to use them in a meaningful way. Since the field values come from two different tables, you could store them in some kind of container object.

List<MediaContainer> mediaList = new ArrayList<>();

for (Object[] row : results) {
    MediaContainer container = new MediaContainer();
    container.setTitle((String) row[0]);
    container.setIsbn((int) row[1]);
    container.setAuthors((String) row[2]);

    mediaList.add(container);
}

Answer 4

@WebUser instead of doing

List<EntityIDKey> companies = 
getEntityManager().createQuery(sql, EntityIDKey.class).getResultList();

Try this :

List<EntityIDKey> companies =
(List<EntityIDKey>)getEntityManager().createQuery(sql).getResultList();

works for me.