Spring-data-JPA - Executing complex multi join queries

Question

I have a requirement for which I need to execute a bunch of random complex queries with multiple joins for reporting purposes. So I am planning to use entitymanager native query feature directly. I just tried and it seems to work.

@Service
public class SampleService {

    @Autowired
    private EntityManager entityManager;

    public List<Object[]> execute(String sql){
        Query query = entityManager.createNativeQuery(sql);
        return query.getResultList();
    }

}

This code is invoked once in every 30 seconds. Single threaded - scheduled process.

Question:

• Should I be using entity manager or entity manager factory?
• Should I close the connection here? or is it managed automatically?
• How to reduce the DB connection pool - as it is not multi threaded app or Should I not be worried about that?
• Any other suggestions!?

Answer 1

Should I be using entity manager or entity manager factory? Injecting EntityManager Vs. EntityManagerFactory

EntityManager looks fine in this instance.

Should I close the connection here? or is it managed automatically? No I dont think you need to as the manager handles this.

How to reduce the DB connection pool - as it is not multi threaded app or Should I not be worried about that?

I doubt you need concern yourself with the connection pools unless you are expecting large volumes and your application is running slowly under load. Try doing some bench marking you may have much more capacity than you need and be prematurely optimising your app. It more likely you would you increase it number of connections rather than decrease. To increase the number of connections you do that in the application.properties (or application.yml)

Any other suggestions!?

Rather than a generic method I would consider having a separate repository class outside of the service and have that repository method do something specific. Make a method return a specific result or thing rather than pass in any sql.

As a rough outline of two seperate classes (files) something like this

@Service
public class SampleService {

  @Autowired
  private MyAuthorNativeRepository myAuthorNaviveRepository;

  public List<Author> getAuthors(){
    return myAuthorRepository.getAuthors();
  }

}


@Service
public class MyAuthorNativeRepository {

  @Autowired
  private EntityManager entityManager;

  public List<Author> getAuthors(){

    Query q = entityManager.createNativeQuery("SELECT blah blah FROM Author");

    List<Author> authors = new ArrayList();

    for (Object[] row : q.getResultList()) {
       Author author = new Author();
       author.setName(row[0]);


        authors.add(author);
    }
    return authors;
  }
}