Intermediary result set in ItemReader

Question

Fairly new to the Spring Batch mechanism–I managed to get it up and running but my business logic does not seem to fit as a Spring Batch job. Allow me to explain:

The job is responsible of sending an email to the User as follows:

1. Fetch the set of News items which satisfy a certain criteria (Set<News>)
2. Fetch the set of users who haven't received one of the News items yet (Set<User>)
3. For each user, send an e-mail for each News the user hasn't received.
4. 'Tag' that user that the News has/have been received.

The entity bean definition are as follows:

    @Entity
    public class News {
        ...
        // ID with a sequence generator (more recent news have a higher ID value)
        private Integer id;
        ...
    }

    @Entity
    public class User {
        ...
        // last received news;
        private Integer lastReceivedNewsId;
        ...
    }

There is no one-to-many relationship in between, so in order to retrieve the unreceived news one could retrieve as follows:

SELECT n FROM db.news n 
JOIN User u on n.id > u.lastReceivedNewsId; 

I decided to use Spring Batch since my user base is currently at 28,000 and counting, settings steps 1 and 2 as the ItemReader and steps 3 and 4 as the ItemWriter.

The problem lies in the firs two steps: I decided to use JpaPagingItemReader<T> as my reader but unfortunately it is incredibly rigid such that I can only specify a query with no change of mapping the result set to something else. In my example, I would first need to fetch the set of News, then query the database to fetch the set of Users based on the received News (Tuple<User, Set<News>>).

What can I do to allow my reader to return a tuple of User and his/her unreceived set of News? What am I missing?

Much appreciated. Do let me know if you need any more information.

Answer 1

If you can use Hibernate, I'd suggesting looking into the HibernateCursorItemReader. I'm going to make some assumptions about your data model, so hopefully this is close enough to be useful...

USER: USER_ID
NEWS: NEWS_ID, CONDITION_COL
USER_NEWS: USER_ID, NEWS_ID, SENT_FLG

Assuming you have relationships between your persistence objects, you could write the HQL to look something like this:

select un from UserNews un
inner join fetch un.news news
inner join fetch un.user user
where un.sentFlag = false
and news.condition = :injectCondition

This also assumes that when you create a new news item, you pre-populate the USER_NEWS table with SENT_FLG = 'N'.

If that's not true, you can do so with this:

INSERT INTO USER_NEWS (USER_ID, NEWS_ID, SENT_FLG)
SELECT U.USER_ID, N.NEWS_ID, 'N' SENT_FLG
FROM USER U, NEWS N
WHERE NOT EXISTS (
  SELECT 1 
  FROM USER_NEWS UN
  WHERE     UN.USER_ID = U.USER_ID
        AND UN.NEWS_ID = N.NEWS_ID
)

EDIT: It's too bad you don't have a many-to-many join table in your data model, but Hibernate still might be able to help if you add a vague one-to-many relationship:

@OneToMany
@JoinColumnsOrFormulas({
    @JoinColumnOrFormula(formula=@JoinFormula(value="(SELECT n.id FROM News n)", referencedColumnName="id"))
})
private Set<News> newsItems;

Then your HQL query would look something like this:

from User u
left join fetch u.newsItems n
where n.id > u.lastReceivedNewsId

If that doesn't work, there's a less-pretty option where you can write a driving query like this:

select u, n from User u, News n where n.id > u.lastReceivedNewsId
order by u.id, n.id

The cursor above won't give you the exact Tuple you're looking for, but you will get Object[2] where the first object is a User and the Second is an unsent news item. Presumably, you could then write an aggregator in your reader to combine several results into the Tuple you want.