Could not open JPA EntityManager for transaction; EntityManager is closed

Question

I searched the internet up and down for this the last few days and tried all I could think of. I consider myself out of options now.

In a java 8 spring boot rest application I have a

JPA repository:

@Transactional
public interface PageRepository extends JpaRepository<ListPage, Long> {

    static final String CLAUSE_AND_PAGE_NR = " and lp.pageNumber = :pageNumber";
    static final String BY_LIST_ID = "from ListPage lp inner join lp.listInstance li where li.objectId = :listId";
    static final String FIND_CONTENT = "select lp.content ";

    @Query(value = FIND_CONTENT + BY_LIST_ID + CLAUSE_AND_PAGE_NR)
    public String findContentByListIdAndPageNumber(@Param("listId") final Long listId, @Param("pageNumber") final Integer pageNumber);    
}

The controller:

@RestController
@RequestMapping("/download")
public class DownloadController {

    private final Logger logger = LoggerFactory.getLogger(DownloadController.class);

    @Resource
    ListRepository listRepository;

    @Resource
    PageRepository pageRepository;

    @RequestMapping(value = "/list/{id}/browse-txt", method = RequestMethod.GET, produces= MediaType.TEXT_PLAIN_VALUE)
    public ResponseEntity<StreamingResponseBody> getListAsSingleFileBrowseTxt(@PathVariable(value="id") Long listId,
                                                                            HttpServletResponse response,
                                                                            HttpServletRequest request) {


        ListInstance list = listRepository.findOne(listId);
        response.setHeader("Content-Disposition", "inline; filename=" + renameFileExtension(list.getFileName(),".TXT"));
        response.setHeader("content-type", "text/plain");


        return getResponse(auth.getCorporateKey(), list);
    }

    private ResponseEntity<StreamingResponseBody> getResponse (String corporateKey, ListInstance list) {

        Long listId = list.getObjectId();

        StreamingResponseBody responseBody = out -> {

            Integer pageCount = pageRepository.countByListId(listId);

            long bytesBuffered = 0;
            ByteArrayOutputStream buff = new ByteArrayOutputStream();
            String pageString;
            byte[] pageBytes;
            for (Integer i = 1; i <= pageCount; i++) {

                try {
                    pageString = pageRepository.findContentByListIdAndPageNumber(listId, i);

                    buff.write(pageBytes);
                    bytesBuffered += buff.size();
                    if (bytesBuffered > 1024) { //flush after 1KB
                        bytesBuffered = 0;
                        out.write(buff.toByteArray());
                        out.flush();
                        buff.reset();
                    }
                }
                catch (Exception e) {
                    logger.error("error writing lazy buffer: page " + i + " of list " + listId, e);
                }
            }
            out.write(buff.toByteArray());
            out.flush();
        };

        return new ResponseEntity(responseBody, HttpStatus.OK);
    }
}

When I call the controller everything works as expected. I see in the browser the pages coming in as chunks of 1k. That's what I wanted,-lazy load of large content.

But out of a sudden (and not always at the same page) it crashes with: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is java.lang.IllegalStateException: EntityManager is closed

I understand, that this happens because the JPA repository operates in a different thread, where the thread the repository was created in, has finished (I tried that reading loop in a single thread and that worked).

Question for me is: How can I prevent the JPA connection (whatever I should call it) from vanishing in the different thread?

Someone please give me another hint.

greetings

S.

score 0 · Answer 1 · answered Nov 07 '17 at 09:36

The exception is clearly saying EntityManager is closed.

There are following things you can do

move getResponse (...) method to some service class and annotate the method with @transactional. Call this method from your controller. Remove @Transactional from PageRepository interface. Its really doesn't matter but it is recommended. To understand more please find the link
Important : pageString = pageRepository.findContentByListIdAndPageNumber(listId, i); Here you are hitting DB for each pageNumber, so there that many transactions will happen. Modify your code just to do one in query by passing list of page numbers. That way there will be only one transaction to the DB.

Just try once these changes and please let us know if it works.

score 0 · Answer 2 · answered Nov 09 '17 at 17:55

This is how I could solve it:

Add this function to the JPA repository

@Query(value = FIND_CONTENT + BY_LIST_ID + CLAUSE_ORDER_BY_PAGE_NR)
@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "0"))
public Stream<String> streamAllPageContentByListID(@Param("listId") final Long listId);

And this will be the Controller

@RestController
@RequestMapping("/download")
public class DownloadController {

    private final Logger logger = LoggerFactory.getLogger(DownloadController.class);

    @Resource
    ListRepository listRepository;

    @Resource
    PageRepository pageRepository;

    @Transactional(readOnly = true)
    @RequestMapping(value = "/list/{id}/browse-txt", method = RequestMethod.GET, produces= MediaType.TEXT_PLAIN_VALUE)
    public ResponseEntity<StreamingResponseBody> getListAsSingleFileBrowseTxt(@PathVariable(value="id") Long listId,
                                                                            HttpServletResponse response,
                                                                            HttpServletRequest request) {


        ListInstance list = listRepository.findOne(listId);
        response.setHeader("Content-Disposition", "inline; filename=" + renameFileExtension(list.getFileName(),".TXT"));
        response.setHeader("content-type", "text/plain");


        return getResponse(response, auth.getCorporateKey(), list);
    }

    protected ResponseEntity getResponse (HttpServletResponse response, String corporateKey, ListInstance list) {

        Long listId = list.getObjectId();

        try {
            try (PrintWriter out = response.getWriter()) {                

                try (Stream<String> pageStream = pageRepository.streamAllPageContentByListID(listId)) {

                    pageStream.forEach(page -> {
                        out.write(page);
                    });
                    out.flush();
                }
            }
        } catch (Exception e) {
            logger.error("error writing lazy buffer of list " + listId, e);
            return new ResponseEntity(HttpStatus.BAD_REQUEST);
        }

        return new ResponseEntity(HttpStatus.OK);
    }
}

A JPA repository will never work in a different thread. A final hint came from this side http://knes1.github.io/blog/2015/2015-10-19-streaming-mysql-results-using-java8-streams-and-spring-data.html

Solution works perfect so far. The speed of the lazy load is stunning.

Could not open JPA EntityManager for transaction; EntityManager is closed

2 Answers2