How to get the same database connection as JPA used using Java?

Question

I am using Jasper Reports for report generation.

I am using temporary tables to generate reports for which I need the same connection used by JPA while creating temporary table how do I achieve the same.

Solution 1

Probably you are just three steps away.

You can do it using following way

1. use JTA datasource for persistance.xml like as below

   <persistence-unit name="sample">
           <jta-data-source>java:comp/env/jdbc/DBConnectionDS</jta-data-source>
           ....
   </persistence-unit>
   

2. For report generation, retrieve the connection from datasource as shown below

   InitialContext initialContext = new InitialContext();
   DataSource dataSource = (DataSource)initialContext.lookup("java:comp/env/jdbc/DBConnectionDS");
   Connection connection = dataSource.getConnection();
   

3. Use the connection to generate report something like below:

   JasperPrint print = JasperFillManager.fillReport(report, parameters, connection);
   

This should be all I believe. The idea is, using common JNDI connection for both, JPA & JasperReport, and then use them where applicable.

I didn't work with JasperReports, but worked with BIRT Report and solved it this way without any problem.

Solution 2

Within JasperReports you can use either native JDBC queries or EJBQL queries.

When using the latter, your code should look like this (from JRJpaQueryExecuter api):

 Map parameters = new HashMap();
 EntityManager em = emf.createEntityManager();
 parameters.put(JRJpaQueryExecuterFactory.PARAMETER_JPA_ENTITY_MANAGER, em);
 JasperRunManager.runReportToPdfFile(fileName, parameters);

If you really need the underlaying jdbc connection the way to achieve it varies depending on the JPA implementation you are using.

EclipseLink (JPA 2.0):

entityManager.getTransaction().begin();
java.sql.Connection connection = entityManager.unwrap(java.sql.Connection.class);
...
entityManager.getTransaction().commit();

(You won't need to begin and commit transactions for reporting)