Database Connectivity in Servlet

Database connectivity in servlet. There are different approaches to communicating with databases through servlet components. We need to perform the servlet to database communication due to the following main reasons:-

• To save the inputs coming from the end-user through forms to database software. Example:- Email Id registration.
• To save the result generated by servlet Component in database software. Example:- Bill generation
• To get inputs from the database software to the Servlet component. Example:- Balance inquiry, account statement generation

For servlet to database software communication, we need to place JDBC code/Hibernate code/Spring JDBC/Spring ORM/Spring Data code in Servlet Component. Since JDBC is the most basic one therefore here we will discuss servlet to database communication through JDBC code.

Different Approaches for Database Connectivity in Servlet

There are 4 approaches for Database Connectivity in Servlet.

Approach-1:- Writing logics in multiple methods.

• Create JDBC Connection in the init()
• Use the JDBC Connection in the service(-,-)/doXxx(-,-)
• Close JDBC Connection in the destroy method.

Here JDBC Connection (con) must be taken as an instance variable of the Servlet Component class.

The disadvantage of this approach:- Since JDBC Connection is an instance variable, it is not thread-safe by default. So, we should use the synchronization concept.

Advantage:- All the requests coming to Servlet Component will use a single JDBC Connection to interact with database software, due to this the performance will be improved.

Note:- This approach is outdated and nowadays no one is using this approach (except the maintenance side).

Approach-2:- Write all the logics in service(-,-) or doXxx(-,-) method.

• Create JDBC Connection in service(-,-)/doXxx(-,-) method.
• Use JDBC Connection in service(-,-)/doXxx(-,-) method.
• Close JDBC Connection in service(-,-)/doXxx(-,-) method.

Note:- Here JDBC Connection (con) will be a local variable to service(-,-)/doXxx(-,-) method.

Disadvantage:- The JDBC Connection (con) is a local variable so, every request which is given to the servlet will create one JDBC Connection object with database software, hence the performance will be poor.

Advantage:- Since JDBC Connection is a local variable, it is thread-safe by default. Hence there is no need to work with the synchronization concept.

Approach-3:- Use server-managed JDBC connection from the connection pool.

• Get JDBC Connection object from JDBC Connection pool being from service(-,-)/doXxx(-,-)
• Use JDBC Connection object in service(-,-)/doXxx(-,-)
• Return JDBC connection back to JDBC connection pool being from service(-,-)/doXxx(-,-)

Advantage:- Here JDBC connection is local to service(-,-)/doXxx(-,-) method. So, it becomes thread-safe.

We can get all the advantages of the JDBC connection pool. The main advantages of the JDBC connection pool are,

• Reusability of JDBC connection objects.
• With minimum JDBC connection objects, we can make max clients/requests talking with database software.
• Programmer is free from creating connection objects, managing connection objects, and destroying connection objects.

Web servers are also supplying existing created JDBC connections. Therefore programmers should not worry about how to create JDBC connections, manage them and destroy them. These tasks will be done by the web server itself. The programmer will just fetch those JDBC connections and use them in the servlet component. Moreover, one JDBC connection can be used for multiple requests, therefore performance will be good compared to approach2.

Approach4:- Use DAO class for the persistence operation.

What is DAO:- The Java class/component that separates persistence logic from other logics of the application and makes that logic as flexible logic to modify and reusable logic is called DAO (Data access object).

In this approach, we can use either approach-2 or approach-3 to get the JDBC connection. The main task in this approach, 

• Write JDBC code (persistence logic) in DAO class either by using direct connection object or server-managed pooled connection object.
• Create a DAO class object in Servlet Component and use its persistence logic in Servlet Component.

Conclusion

We should use which approach for database connectivity in Servlet? Java is popular for developing medium and large web applications so approach-4 is recommended to use. But before going to approach4, we must know approach-2 and approach-3 then only you can develop logic for approach4. In this post, we will discuss approach2. 

Adding Jar File

If a web server is using any third-party API which isn’t part of servlet-api, jsp-api, and JDK API then it must be added to the lib folder of the web application. The file structure of the web application taking to database software:-

DBApp
   |==> input.html
   |==> WEB-INF
      |==> web.xml
      |=> lib
         |==> mysql-connector.jar 
      |==> classes
         |==> DBServlet.java

Note that this is the standard file structure of the web application, not for the Eclipse IDE. For Eclipse IDE we will discuss it separately here:- how to connect servlet to the database in eclipse.

For database connectivity in Servlet, the jar file should be added in the <web-app>\WEB-INF\lib\ folder.

Servlet Container varifies in the following places and order to get the third party API,

• In the classes and packages of the WEB-INF\classes folder.
• In the jar files added to the WEB-INF\lib folder of the current web application.
• In the jar files added to the server library folder.
• If not found in all the above places then exceptions will come like ClassNotFoundException or the NoClassDefException.

Example of Database connectivity in Servlet

Create the above file structure and add the mysql-connector.jar file in the lib folder. 

Web Application Description:- In the MySQL database, we have a “student” table. In a web application, there will be an HTML form that gets the student number (sno) as input and passes it to the servlet component. Based on the passed sno fetch the sname, sadd and avg details of the student and display them as result. Get more about Java to database connectivity here:- Java to MySQL database connection.

sno	sname	sadd	avg
10	Sophia	Manchester	72
11	William	Washington	80
12	Alex	Boise	95
13	Ameila	London	85

The HTML form (input.html),

<h1 style="color:blue">Find Student details</h1>
<form action="student-details" method="post">
   Student No: <input type="text" name="sid"/><br>
   <input type="submit" value="Get Student Details">
   <input type="reset" value="Cancel">
</form>

The Servlet component (GetStudentServlet.java),

package com.kp.servlet;

import java.io.*;
import java.sql.*;

import jakarta.servlet.*;
import jakarta.servlet.http.*;

public class GetStudentServlet extends HttpServlet {
   // SQL query
   private static final String SELECT_STUDENT_QUERY = 
         "SELECT SNAME, SADD, AVG FROM STUDENT WHERE SNO = ?";
   
   @Override
   public void doGet(HttpServletRequest req, HttpServletResponse res) 
         throws ServletException, IOException {
      
      // variables
      PrintWriter pw = null;
      int sno = 0;
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      boolean flag = false;
      
      // set content type
      res.setContentType("text/html");
      // get Writer
      pw = res.getWriter();

      // get form data
      sno = Integer.parseInt(req.getParameter("sid"));
      
      try {
         // register JDBC driver
         Class.forName("com.mysql.cj.jdbc.Driver");
         // create JDBC connection
         con = DriverManager.getConnection(
         	"jdbc:mysql:///knowprogram", "root", "Know9@Program");
         // compile SQL query and store it in
         // PreparedStatement object
         if (con != null)
            ps = con.prepareStatement(SELECT_STUDENT_QUERY);
         // set input value to query parameter
         if (ps != null)
            ps.setInt(1, sno);
         // execute the query
         if (ps != null)
            rs = ps.executeQuery();

         // process the result
         if (rs != null) {
            while(rs.next()) {
               // display result
               flag = true;
               pw.println("<h1>Student Details, </h1>"
                     + "Name: " + rs.getString("SNAME") + "<br>"
                     + "Address: " + rs.getString("SADD") + "<br>"
                     + "Average: " + rs.getDouble("AVG") + "<br>");
            }
         }
         
         // Student not found
         if(!flag) {
            pw.println("<h1>Student Not Found.</h1>");
         }

      } catch (SQLException se) {
         se.printStackTrace();
         pw.println("Error Occured");
      } catch (Exception e) {
         e.printStackTrace();
         pw.println("Unknown Exception Occured");
      } finally {
         // close JDBC connection
         try {
            if (rs != null)
               rs.close();
         } catch (SQLException se) {
            se.printStackTrace();
         }
         try {
            if (ps != null)
               ps.close();
         } catch (SQLException se) {
            se.printStackTrace();
         }
         try {
            if (con != null)
               con.close();
         } catch (SQLException se) {
            se.printStackTrace();
         }

         // Link to home
         pw.println("<h3><a href='input.html'>Home</a></h3>");
         // close stream
         pw.close();
      }
   }

   @Override
   public void doPost(HttpServletRequest req, HttpServletResponse res) 
         throws ServletException, IOException {
      doGet(req, res);
   }

}

The deployment descriptor (web.xml) file,

<web-app>
   <welcome-file-list>
      <welcome-file>input.html</welcome-file>
   </welcome-file-list>
   <servlet>
      <servlet-name>student</servlet-name>
      <servlet-class>com.kp.servlet.GetStudentServlet</servlet-class>
   </servlet>
   <servlet-mapping>
      <servlet-name>student</servlet-name>
      <url-pattern>/student-details</url-pattern>
   </servlet-mapping>
</web-app>

See this database connectivity in servlet application code at GitHub.

In this (database connectivity in servlet) web application, we hardcoded the technical input values like driver class name, URL, database username, and password. 

All these are changeable technical input values. For security reasons, the username and passwords are changed frequently. Similarly, when an application is hosted from one computer to another computer, the URL pattern may change because it depends on the <host_name> and <port_number>. Whenever we want to change the database software then the driver class name will change. 

The standard principle of the software industry is don’t hard code any values in our application that are changeable in the future. It is recommended to pass such values to the application form outside the application. 

We can pass them as <init-parm> or <context-param> values from the web.xml file. The <init-param> value is accessed through ServletConfig object, and <context-param> values are accessed through ServletContext object.

FAQ

Q1) Why are we not using properties files in Servlet Component to supply JDBC properties?
We have two ways to supply the JDBC properties dynamically to the servlet component either by using the “properties” file or by using the “web.xml” file. We can take properties files having JDBC properties but not recommended because already we have web.xml for regular servlet configurations. So instead of creating a separate file (i.e. properties files) to supply JDBC properties, it is better to use the existing web.xml file to supply JDBC properties.

Q2) Can we place zero param constructor in a servlet component/class?
Yes, we can place zero param constructor in a servlet component/class.

Question3) What is the difference between placing initialization logic in the servlet comp’s constructor and placing it in the init() method of servlet comp? Or, When the constructor is already then why do we use the init() method to place the initialization logic? (for approach-1)

Answer:- Constructor can’t work with servlet init, context params because it is not having access to ServletConfig and ServletContext objects whereas init() method can work with them. Constructors can’t access data outside of the servlet.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search