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.

m.title + b.isbn + b.author does NOT an instatiation of Media make. — esej, May 29 '12 at 22:24
I can't see what you want, do you want Media's or Book's or something else that is not an entity? — esej, May 29 '12 at 22:41
actually, I do the same way as I do in the SQL. I want to select title, isbn and authors from the parameters which it could be isbn or title or authors. — user1412944, May 29 '12 at 22:43
You are going about this all wrong. If Book extends Media then you should model it that way in your entities. Please post the relevant entity code for both Book and Media. Posting the DDL for the tables would be nice too. — Perception, May 29 '12 at 23:42

score 61 · Answer 1 · edited Jan 25 '17 at 10:06

61

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.

edited Jan 25 '17 at 10:06

Hitham S. AlQadheeb

13,613
3
47
74

answered May 30 '12 at 08:14

Nayan Wadekar

10,772
4
41
69

Actually a better approch is to use `JOIN FETCH` : http://stackoverflow.com/a/39465150/1304830 – Fr4nz Feb 21 '17 at 09:08
This works but what if I've a list inside Media.class representing OneToMany mapping and I want to fetch for example only IDs of members of list? – Ismail Yavuz Dec 20 '19 at 09:32

score 43 · Accepted Answer · edited Jan 07 '13 at 17:51

43

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 ...

edited Jan 07 '13 at 17:51

OnaBai

39,919
5
91
122

answered May 30 '12 at 07:21

JB Nizet

633,450
80
1,108
1,174

I am still getting the error. My SQL looks like this: `SELECT NEW com.company.ui.EntityIDKey(c.companyId, c.name) FROM Company c WHERE c.companyId is not null and c.name is not null and length(trim(c.name)) > 0 order by c.name asc`. I am using a TypedQuery as follows: `List companies = getEntityManager().createQuery(sql, EntityIDKey.class).getResultList();` – Web User Nov 05 '14 at 19:39
@WebUser don't hijack a question from 2?5 years ago. Ask your own question, and give all the details. – JB Nizet Nov 05 '14 at 20:04
1

I wasn't trying to hijack the question. On the contrary, this thread was very relevant which is why I ended up trying your solution. I felt that if I was missing something and you could help find it, then it should help others. – Web User Nov 05 '14 at 20:57

David DeMar · Answer 3 · 2020-02-26T15:00:05.243

4

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

edited Feb 26 '20 at 15:00

answered Feb 10 '18 at 08:48

David DeMar

1,803
2
23
38

could you clarify a bit more how you would cast this? I take it that Object[] is a list of object fields. Can i just cast Object[] to the desired type? Or do i have to create a new instance of the desired type first and then set each field with the corresponding element in the Object[] array? Thank you – Maurice Sep 18 '18 at 11:11
1

You can't cast an `Object[]` to a Hibernate entity. This particular result also returns values from two different tables (Media and Book) so there's not a single object those field values map to anyway. If you wanted to store the results in an object, you would have to loop through the results, cast each field value, then store the values in your new object. I updated my solution with an example of how you could do that. – David DeMar Sep 18 '18 at 17:54

score 0 · Answer 4 · answered Jan 28 '14 at 16:29

0

if your are using Hibernate version < 4, you can meet this bug.

I go same problem with v3.5. Finally i had to use simple Query and cast each parameter manually

see other comments here : https://groups.google.com/forum/#!topic/axonframework/eUd1d4rotMY

answered Jan 28 '14 at 16:29

Benjamin Fuentes

567
6
20

score 0 · Answer 5 · edited Mar 18 '17 at 19:12

0

@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.

edited Mar 18 '17 at 19:12

Tomaž Bratanič

3,300
2
12
26

answered Mar 18 '17 at 16:23

dubem uzuegbu

131
4

score 0 · Answer 6 · edited Jan 09 '18 at 12:20

0

I... remove

Media.class

of

createQuery

because you return more Entities in this source "SELECT m.title, b.isbn, b.authors"

Ex.:

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");

edited Jan 09 '18 at 12:20

Climbatize

1,025
17
29

answered Aug 01 '17 at 14:18

acacio.martins

101
3

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

6 Answers6

Linked