Copyright © tutorialspoint.com

# Servlets - Database Access

This tutorial assumes you have understanding on how JDBC application works. Before starting with database access through a servlet, make sure you have proper JDBC environment setup along with a database.

For more detail on how to access database using JDBC and its environment setup you can go through our JDBC Tutorial.

To start with basic concept, let us create a simple table and create few records in that table as follows:

# Create Table

To create the Employees table in TEST database, use the following steps:

## Step 1:

Open a Command Prompt and change to the installation directory as follows:

    C:\>
    C:\>cd Program Files\MySQL\bin
    C:\Program Files\MySQL\bin>

## Step 2:

Login to database as follows

    C:\Program Files\MySQL\bin>mysql -u root -p
    Enter password: ********
    mysql>

## Step 3:

Create the table Employee in TEST database as follows:

    mysql> use TEST;
    mysql> create table Employees
        (
         id int not null,
         age int not null,
         first varchar (255),
         last varchar (255)
        );
    Query OK, 0 rows affected (0.08 sec)
    mysql>

# Create Data Records

Finally you create few records in Employee table as follows:

    mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
    Query OK, 1 row affected (0.05 sec)

    mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
    Query OK, 1 row affected (0.00 sec)

    mysql>

# Accessing a Database:

Here is an example which shows how to access TEST database using Servlet.

    // Loading required libraries
    import java.io.*;
    import java.util.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import java.sql.*;

    public class DatabaseAccess extends HttpServlet{

      public void doGet(HttpServletRequest request,
                        HttpServletResponse response)
                throws ServletException, IOException
      {
          // JDBC driver name and database URL
          static final String JDBC_DRIVER="com.mysql.jdbc.Driver";
          static final String DB_URL="jdbc:mysql://localhost/TEST";

          //  Database credentials
          static final String USER = "root";
          static final String PASS = "password";

          // Set response content type
          response.setContentType("text/html");
          PrintWriter out = response.getWriter();
          String title = "Database Result";
          String docType =
            "<!doctype html public \"-//w3c//dtd html 4.0 " +
             "transitional//en\">\n";
             out.println(docType +
             "<html>\n" +
             "<head><title>" + title + "</title></head>\n" +
             "<body bgcolor=\"#f0f0f0\">\n" +
             "<h1 align=\"center\">" + title + "</h1>\n");
          try{
             // Register JDBC driver
             Class.forName("com.mysql.jdbc.Driver");

             // Open a connection
             conn = DriverManager.getConnection(DB_URL,USER,PASS);

             // Execute SQL query
             stmt = conn.createStatement();
             String sql;
             sql = "SELECT id, first, last, age FROM Employees";
             ResultSet rs = stmt.executeQuery(sql);

             // Extract data from result set
             while(rs.next()){
                //Retrieve by column name
                int id  = rs.getInt("id");
                int age = rs.getInt("age");
                String first = rs.getString("first");
                String last = rs.getString("last");

                //Display values
                out.println("ID: " + id + "<br>");
                out.println(", Age: " + age + "<br>");
                out.println(", First: " + first + "<br>");
                out.println(", Last: " + last + "<br>");
             }
             out.println("</body></html>");

             // Clean-up environment
             rs.close();
             stmt.close();
             conn.close();
          }catch(SQLException se){
             //Handle errors for JDBC
             se.printStackTrace();
          }catch(Exception e){
             //Handle errors for Class.forName
             e.printStackTrace();
          }finally{
             //finally block used to close resources
             try{
                if(stmt!=null)
                   stmt.close();
             }catch(SQLException se2){
             }// nothing we can do
             try{
                if(conn!=null)
                conn.close();
             }catch(SQLException se){
                se.printStackTrace();
             }//end finally try
          } //end try
       }
    }

Now let us compile above servlet and create following entries in web.xml

    ....
     <servlet>
         <servlet-name>DatabaseAccess</servlet-name>
         <servlet-class>DatabaseAccess</servlet-class>
     </servlet>

     <servlet-mapping>
         <servlet-name>DatabaseAccess</servlet-name>
         <url-pattern>/DatabaseAccess</url-pattern>
     </servlet-mapping>
    ....

Now call this servlet using URL http://localhost:8080/DatabaseAccess which would display following response:

#

    Database Result
    ID: 100, Age: 18, First: Zara, Last: Ali
    ID: 101, Age: 25, First: Mahnaz, Last: Fatma
    ID: 102, Age: 30, First: Zaid, Last: Khan
    ID: 103, Age: 28, First: Sumit, Last: Mittal

Copyright © tutorialspoint.com