JDBC  «Prev  Next »

Lesson 3 How to use JDBC
Objective Describe how to use JDBC.

JDBC Servlets and Databases

Here's how you can use JDBC with Java Servlets in the context of Jakarta Servlet 6.0:
Step-by-Step Guide:
  1. Setup JDBC Driver
    • Download: Obtain the JDBC driver for your database. For example, if you're using MySQL, you would download the MySQL Connector/J.
    • Add to Project: Include the JDBC driver in your project's classpath. In a servlet environment, this typically means placing the JAR file in the WEB-INF/lib directory of your web application.
  2. Configure DataSource in Web Application
    • JNDI Lookup: For production environments, it's recommended to use JNDI to manage database connections. Define your DataSource in your application server (like Tomcat, WildFly, etc.):
      1. Edit the server's context file (context.xml for Tomcat) or equivalent: 
        <Context>
  <Resource name="jdbc/myDB" 
           auth="Container" 
           type="javax.sql.DataSource" 
           driverClassName="com.mysql.cj.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/mydb"
           username="youruser" 
           password="yourpassword" 
           maxActive="20" 
           maxIdle="10"
           maxWait="-1"/>
</Context>
    • Reference in web.xml: If needed, you might reference this in your web.xml: 
      <resource-ref>
  <description>DB Connection</description>
  <res-ref-name>jdbc/myDB</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>
  3. Create Servlet
    • Get Connection: Inside your servlet, look up the DataSource: 
      import jakarta.servlet.*;
import jakarta.servlet.http.*;
import java.io.IOException;
import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;

public class MyServlet extends HttpServlet {
  protected void doGet(HttpServletRequest request, HttpServletResponse response) 
          throws ServletException, IOException {
    Connection conn = null;
    try {
      // JNDI lookup
      Context initContext = new InitialContext();
      Context envContext  = (Context)initContext.lookup("java:/comp/env");
      DataSource ds = (DataSource)envContext.lookup("jdbc/myDB");
      
      // Get connection from DataSource
      conn = ds.getConnection();
      
      // Use the connection for database operations
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM your_table");
      while(rs.next()){
        // Process each row
      }
      
    } catch (NamingException | SQLException e) {
      throw new ServletException(e);
    } finally {
      try { if (conn != null) conn.close(); } catch (SQLException e) { /* ignore */ }
    }
  }
}
  4. Error Handling and Resource Management
    • Close Resources: Always close your database resources (ResultSet, Statement, Connection) in a finally block or use try-with-resources if available in your Java version for automatic resource management.
    • Exception Handling: Wrap SQLExceptions in ServletExceptions to propagate them up to the servlet container's error handling.
  5. Deploy and Test
    • Deploy your application on your servlet container and test the servlet by making requests to it, ensuring that database operations are performed correctly.

This integration leverages Jakarta EE's capabilities for resource management, making it easier to manage database connections in a servlet environment. Remember, the exact steps might vary slightly depending on your specific servlet container and database setup.

To use JDBC, you follow a number of fairly simple steps:
  1. First you create a database, using any of the many databases supported by ODBC.
    You can also use a non-ODBC database if the database vendor provides a JDBC driver, but most vendors provide ODBC drivers.
  2. You must ensure that the appropriate driver is installed on your computer.
    It is installed at the same time as the database program.
  3. Once the data source and drivers are in place, you can write JDBC code.
    The first thing JDBC code does is to make a database connection.
    With the connection in place, you build SQL statements. These statements are commands to the database, and work with the data you get back from the data source however you wish. In most cases, you will include it in the HTML your servlet is generating.

The last step will be explained in detail in the lessons that follow.
The next lesson is an introduction to the basic SQL commands you will be using in this module.

SEMrush Software