JpaPagingItemReader not creating entity objects

Question

I'm using spring-batch and the JpaPagingItemReader<T> to read a DB to @Entity. The entity is correctly specified as type argument T.

Problem: The result set that is returned is not of type T, but an array of objects: Object[]. Each object in that array itself is an Object[] array having the following content: [firstname, lastname, date].

So, the database columns are somehow written as String array, and the entities are returned obviously as String[] array.

The reason for this is my query:

SELECT firstname, lastname, min(date) FROM mytable GROUP BY firstname, lastname;

So the reader maps to a string array. How can I have a mapping to my @Entity, maybe only filled with the fields requested in the select?

My reader definition:

JpaPagingItemReader<MyEntity> reader = new JpaPagingItemReader<>();
reader.setEntityManagerFactory(emf);
reader.setQueryString(QUERY);

@Entity
public class MyEntity {
   private String firstname, lastname;
   private Date date;

   public MyEntity(String firstname, String lastname, Date date) {
       this.firstname = firstname;
       this.lastname = lastname;
       this.date = date;
   }
}

Sorry forgot to add my query, which is probably the reason... — membersound, Sep 18 '14 at 08:41
Isn't this using JPQL with JpaPagingItemReader? Added the reader definition above. From the docs: `It executes the JPQL {@link #setQueryString(String)} to retrieve requested * data.` — membersound, Sep 18 '14 at 08:45
I think if you want to keep your query as it is but you want to return an object, you will have to create a POJO whose constructor accepts (String, String, Date) (but it wont be your Entity). — ccheneson, Sep 18 '14 at 08:56
Hm ok that's not what I want. How could I rewrite the query so that it returns the entity? — membersound, Sep 18 '14 at 09:00
@ccheneson It can be an entity as long as the constructor matches it will work. — M. Deinum, Sep 18 '14 at 09:02
I added my entity above, which has a constructor with the params matching the returned columns. Though the automatic mapping does not work. — membersound, Sep 18 '14 at 09:11

ccheneson · Accepted Answer · 2014-09-18T09:14:25.357

1

Try this

SELECT m FROM mytable m GROUP BY m.firstname, m.lastname;

This should return your entity

Here is an example of a declarative way of defining your reader:

<beans:bean id="myreader"
    class="org.springframework.batch.item.database.JpaPagingItemReader">
    <beans:property name="queryString"
        value="SELECT m FROM mytable m GROUP BY m.firstname, m.lastname" />
    <beans:property name="entityManagerFactory" ref="entityManagerFactory" />
</beans:bean>

Edit:

From M.Deinum comment, you should be able to use this:

SELECT new com.myproject.model.MyEntity(firstname, lastname, min(date)) FROM mytable GROUP BY firstname, lastname

edited Sep 18 '14 at 09:14

answered Sep 18 '14 at 08:45

ccheneson

49,072
8
63
68

Error: `Column m.* must occur in GROUP-BY clause or used in an aggregate function` (free translation from the native language output. – membersound Sep 18 '14 at 08:50
Great! The new operator works indeed. Is this the correct way to do things? – membersound Sep 18 '14 at 09:22
If you want to return a subset of your entity yes. But since you just have 3 attributes in your entity it is fine. If you have 20 attributes in your entity and you need to return only a few, you d better go with a new POJO - See http://stackoverflow.com/questions/2355728/jpql-create-new-object-in-select-statement-avoid-or-embrace – ccheneson Sep 18 '14 at 09:25

JpaPagingItemReader not creating entity objects

1 Answers1