Connecting to the Jersey Web Application Database

Question

I am currently developing a REST api from Jersey to Tomcat 8. Since I am currently implementing a database connection pool, I realized that my approach of open and closed connections is far from good.

I divided my project into 3 packages:

• Resource (example: UserResource.java)
• Model (example: User.java)
• Data (example: UserDAO.java)

In UserResource, I create a UserDAO object and call, for example,

@GET
public List<User> getUsers(){
    return userDAO.getAll()
}

in the UserDAO class, I create a new connection in each function, for example:

public List<User> getAll(){
    List<User> users;
    Connection conn = ConnectionManager.getConnection();
    PreparedStatement stmt = conn.prepareStatement("my example query");
    ResultSet rs = stmt.executeQuery();
    .
    .
    .
    return users;
}

ConnectionManager is only used to get a new connection from the connection pool.

Therefore, every time I call from a resource in the DAO, I have to get a connection. I considered connecting in the resource and passing it dao in the constructor, but this does not seem like a good solution.

, .