I'm creating a website (with servlets & jsp) and for now I don't want to use an ORM tool like JPA or Hibernate but do it with JDBC.
I'm confused about how this works; I also want to follow the MVC pattern.
Where should I put all the code that accesses the DB?
Basically I'm doing it like this:
1) User sends a request for a page
2) The request goes to the controller
3) I query the DB for the content of the page requested
4) Set an attribute with the results from the DB
5) Forward the request and display the results to the end user
Should I make a java bean that represents a page (title, content), instantiate one and pass the object to the controller (basically the model)? Also, since I'm creating a JDBC resource and a connection pool in glassfish, I need to close the connection every time and connect to the DB again when another request comes in? How can I use connection pooling to avoid this overhead of opening/closing connections?
Servlet - get request and manipulate with input parameter
Service - apply your business logic
DAO - works with DB
Model - usually represent DB's table
FormBean - represent page
I open connection and commit transaction at service layer. Service layer will call DAO which is where you work with your DB (INSERT, UPDATE etc..).
I open connection and commit at service layer because some transaction might use more than one DAO.