Making JDBC Connection

Making a connection to a DSN is remarkably simple. Before you see the code, though, you need to think about how often that connection should be opened and closed. There are two approaches to maintaining a database connection:

1. Make the connection in the servlet’s init() method. The connection will stay open throughout the life of the servlet object.
2. Make the connection in the servlet’s doPost() method, and close it in the same method.

To connect to a Data Source Name (DSN) from a Java Servlet using JDBC, you need to choose between managing the connection lifecycle based on performance, resource management, and scalability considerations. Here's how you can implement both approaches:
1. Connection in init() Method:

• Pros: Performance: The connection is opened once and reused across requests, which can reduce the overhead of repeatedly establishing connections. Simpler Code: Less code dealing with connection management in the request handling methods.
• Cons: Resource Management: Holding a connection open for the life of the servlet can lead to resource exhaustion if not managed properly, especially under high load or if the servlet is long-lived.
• Scalability: Might not scale well in environments where connections are a bottleneck.

Implementation:

public class MyServlet extends HttpServlet {
   private Connection conn;

   @Override
   public void init() throws ServletException {
       try {
           // Load the JDBC driver (if not using connection pooling)
           Class.forName("com.mysql.cj.jdbc.Driver");
            
           // Assuming your DSN is configured in the application server or JDBC URL
           String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase?useSSL=false";
           String user = "username";
           String password = "password";
            
           conn = DriverManager.getConnection(jdbcUrl, user, password);
       } catch (ClassNotFoundException | SQLException e) {
           throw new ServletException("Could not initialize database connection", e);
       }
   }

   @Override
   protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       // Use the connection here
       try {
           // Perform database operations
           Statement stmt = conn.createStatement();
           ResultSet rs = stmt.executeQuery("SELECT * FROM some_table");
           // Process result set
           // ...
       } catch (SQLException e) {
           throw new ServletException("Database access error", e);
       }
   }

   @Override
   public void destroy() {
       try {
           if (conn != null && !conn.isClosed()) {
               conn.close();
           }
       } catch (SQLException e) {
           // Log the error
       }
   }
 }

2. Connection in doPost() Method:

• Pros: Resource Management: Each request manages its own connection, which can be closed after use, freeing up resources immediately.
• Cons: Performance Overhead: Establishing a connection for each request can be costly, especially under high load.
• Scalability: Better for scalability as each request can independently manage its connection lifecycle.

Implementation:

public class MyServlet extends HttpServlet {

  @Override
  protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
      Connection conn = null;
      try {
          // Load the JDBC driver (if not using connection pooling)
          Class.forName("com.mysql.cj.jdbc.Driver");
            
          String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase?useSSL=false";
          String user = "username";
          String password = "password";
            
          conn = DriverManager.getConnection(jdbcUrl, user, password);
            
          // Perform database operations
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery("SELECT * FROM some_table");
          // Process result set
          // ...
      } catch (ClassNotFoundException | SQLException e) {
          throw new ServletException("Database access error", e);
      } finally {
          try {
              if (conn != null && !conn.isClosed()) {
                  conn.close();
              }
          } catch (SQLException e) {
              // Log the error
          }
      }
  }
}

Connection Pooling: For production environments, consider using connection pooling. Libraries like Apache DBCP, C3P0, or even JDBC DataSource objects managed by your servlet container can provide significant performance benefits by reusing database connections. This approach combines the benefits of both methods above:

1. Connections are managed by the pool, reducing the cost of creating new connections for each request.
2. Connections are returned to the pool rather than closed, making them available for reuse.

Considerations:

• Load Testing: Test both methods under your expected load to understand performance impacts.
• Environment: In a controlled environment where you can manage connections, opening in init() might suffice, but for scalability, consider connection pooling or opening/closing in doPost().

This decision largely depends on your application's requirements for performance, scalability, and resource management.

In the context of JDBC (Java Database Connectivity) and Java Servlets, a DSN (Data Source Name) is a string that acts like a shortcut to connect to a specific database. Think of it as a nickname for your database that contains all the necessary connection details.
Here's a breakdown:
What a DSN contains:

• Database type: (e.g., MySQL, Oracle, PostgreSQL)
• Database server address: (e.g., hostname or IP address)
• Database name: (e.g., mydatabase)
• Port number: (e.g., 3306 for MySQL)
• Authentication details: (username and password)

How DSNs are used in JDBC:

1. Configuration: You typically configure a DSN through your operating system or application server. This involves using a tool (like the ODBC Data Source Administrator on Windows) to create and store the DSN.
2. Lookup: In your Java code (including Servlets), you use DriverManager.getConnection() to establish a database connection. Instead of providing all the individual connection details, you simply provide the DSN.
3. Connection: JDBC uses the DSN to look up the associated connection information and establishes a connection to the database.

Benefits of using DSNs:

• Simplified code: Your Java code becomes cleaner and easier to read since you don't have to hardcode connection details.
• Centralized configuration: You can manage database connection settings in one place (the DSN) instead of scattering them throughout your code.
• Improved security: You can potentially store sensitive information (like passwords) within the DSN configuration, rather than directly in your code.
• Portability: If you need to change database servers or settings, you can often just modify the DSN without changing your application code.

DSNs and Java Servlets: In Java Servlets, DSNs are particularly useful because they allow you to easily connect to databases from your web applications. You can configure a DSN in your Servlet container (e.g., Tomcat) and then access it from your Servlet code to perform database operations.
Example:

String dsn = "myDatabaseDSN"; // The name of your configured DSN
Connection conn = DriverManager.getConnection("jdbc:odbc:" + dsn); 
// ... use the connection to interact with the database ...

Alternatives to DSNs: While DSNs are convenient, there are alternatives:

• JDBC URL: You can provide the full JDBC connection URL directly in your code, including all the connection details.
• JNDI: Java Naming and Directory Interface (JNDI) allows you to look up data sources that are registered with your application server. This is often preferred in enterprise environments.

Keep in mind that the specific way you use DSNs might vary slightly depending on your operating system, database, and application server.

Remember, unlike CGI programs, servlets are very long-lived and use threads to handle multiple requests.
The consequences of this are that any resource, such as a database connection, that belongs to the servlet will be used by multiple requests, perhaps multiple requests at the same time.

• JDBC Resource drain
  If you are worried about the resource drain involved in each servlet hanging onto a database connection at all times, you might prefer to get those connections just before you need them, and let go immediately afterwards.
• Instance pooling Instance pooling allows a single component instance to service multiple clients. The component lifecycle contains activation and deactivation steps: Activation binds an instance to an individual client; deactivation indicates that the instance is unbound. Instance pooling eliminates resource drain from repeated allocation of component instances.
• Supporting instance pooling in your component Instance pooling eliminates resource drain caused by repeated allocation of new component instances. For Java components, you can implement a lifecycle-control interface to control whether the component instances are pooled. These interfaces also provide activate and deactivate methods that are called to indicate state transitions in a component instance’s lifetime. For more information on these interfaces, see the following sections:

Java components can implement the interface

jaguar.beans.enterprise.ServerBean.

To support instance pooling, code that responds to activation events must restore the component to its initial state (that is, as if it were newly created). The Java interface has methods that allow an instance to selectively refuse pooling: canReuse in Java. When the component Pooling option is set in EJB Server, the Java canReuse method is not called, even if the component implements the ServerBean Java interface.
Connection pooling is a technique used for reusing and sharing Connection objects among requesting clients.

Perhaps you would like your servlet to connect to a different database to handle different kinds of requests, or to handle requests from users with different levels of authority, or maybe most of the work your servlet does has no need of a database. In that case, you should open those connections just before you deal with the request, and close them immediately afterwards. Are you concerned about the resource drain involved in each servlet hanging onto a database connection at all times?
You might prefer to get those connections just before you need them, and let go immediately afterwards.
For most work, the performance costs associated with opening a connection outweigh other considerations.
By opening the connection once, in the init() method, you pay this cost only once and other servlet requests can just use the open connection. In the next lesson, I will show you how to do this in init(), but remember you could move this code to doPost() if you prefer.
In the next lesson, you will see the code to connect to a data source.