Servlets Database Access


This instructional exercise expect you have understanding on how JDBC application functions. Before beginning with database access through a servlet, ensure you have legitimate JDBC condition setup alongside a database.

For more detail on the most proficient method to get to database utilizing JDBC and its condition setup you can experience our JDBC Tutorial.

To begin with fundamental idea, let us make a straightforward table and make few records in that table as pursues −

Create Table

To make the Employees table in TEST database, utilize the accompanying advances −

Step 1

Open a Command Prompt and change to the establishment index as pursues −

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 pursues −

mysql> use TEST; 

mysql> make table Employees ( 

id int not null, 

age int not null, 

first varchar (255), 

last varchar (255) 

); 

Query OK, 0 columns influenced (0.08 sec) 

mysql>

Create Data Records

Finally you make few records in Employee table as pursues −

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
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

         // Execute SQL query
         Statement 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 order above servlet and make following sections 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 utilizing URL http://localhost:8080/DatabaseAccess which would show following reaction −

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






© Javacodegeeks 2018 -
All Right Reserved and you agree to have read and accepted our term and condition.

Python 3