Reading:

Introduction to Servlet technology

Chapters

Working with a Database

If you are unfamiliar of how JDBC works we recommend to read our tutorial on JDBC first. This page posts an example on how to utilize JDBC to connect to a mySQL database.

Let's start by creating a database called BooksDB

To create the book_tbl table in our BooksDB database:

mysql> use BooksDB;
mysql> create table book_tbl
    (
     id int not null,
     book_age int not null,
     book_title varchar (255),
     book_author varchar (255)
    );
Query OK, 0 rows affected (0.01 sec)
mysql>

Lets fill our test table with some demo data as shown below

mysql> INSERT INTO book_tbl VALUES (1, 'Gone with the wind', 'Margaret mitchell');
Query OK, 1 row affected (0.01 sec)
 
mysql> INSERT INTO book_tbl VALUES (2, 'Games of thrones', 'George R. R. Martin');
Query OK, 1 row affected (0.00 sec)
 
mysql>

Here is an example that shows connection and working with our above data

import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class DatabaseExample extends HttpServlet{
    
  public void doGet(HttpServletRequest request,
                    HttpServletResponse response)
            throws ServletException, IOException
  {
      // JDBC driver name and database URL
       String JDBC_DRIVER= "com.mysql.jdbc.Driver";  
       String DB_URL="jdbc:mysql://localhost/my_db_test";
       Connection conn = null;
      //  Database credentials
       String USER = "root";
       String PASS = "admin";

      // 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 book_id, book_title, book_author FROM book_tbl";
         ResultSet rs = stmt.executeQuery(sql);

         // Extract data from result set
         while(rs.next()){
            //Retrieve by column name
            int id  = rs.getInt("book_id");
            String bk_title = rs.getString("book_title");
            String bk_author = rs.getString("book_author");
          
            //Display values
            out.println("ID: " + id + "<br>");
            out.println(", Title: " + bk_title + "<br>");
            out.println(", Author: " + bk_author + "<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
   }
}

As usual we'll be creating entries to web.xml file so that server would locate this file, here is the entry for DatabaseExample class

<servlet>
     <servlet-name>DatabaseExample</servlet-name>
     <servlet-class>DatabaseExample</servlet-class>
 </servlet>
 
 <servlet-mapping>
     <servlet-name>DatabaseExample</servlet-name>
     <url-pattern>/DatabaseExample</url-pattern>
 </servlet-mapping>

When you browse to this file, considering that you successfully connect to the database you will see following output

ID: 1
Title: Gone with the wind
Author: Margaret mitchell'
ID:2
Title:Games of thrones
Author: George R. R. Martin

In next part of this guide we will check out how packaging works.

Description

This guide introduces Servlet technology and we will cover below topics

What is a Servlet?
Initial Setup
Life Cycle
Various Examples
Client Request
Server Response
Http Status Codes
Writing Filters
Exception Handling
Cookies Handling
Session Tracking
Database Access
Packaging
Internationalization

This is to the point introduction to the topic to get you started

Prerequisites

Working knowledge of Core Java is essential

Audience

Beginner to Java Servlet technology or students looking to brush up their skills quickly

Learning Objectives

Learn Java Servlets

Author: Subject Coach
Added on: 2nd May 2015

You must be logged in as Student to ask a Question.

None just yet!