How To Connect Servlet to MySQL Database in Eclipse

In the previous article, we discuss database connectivity in Servlet. Now, let us see how to connect servlet to MySQL database in eclipse? Prerequisite:- Database Connectivity in Servlet.

Step1) Create a new Dynamic Web Project. New => Dynamic Web Project => Enter Project Name.

Here we are choosing the project name as “DBMySQLEclipse” => Next => Next => Select option to generate web.xml deployment descriptor.

Step2) Add MySQL connector jar file to the lib folder of the web application.

This is the most important part of this example. Download the latest suitable JDBC driver of the MySQL database. Open the downloaded zip file and extract it. The mysql-connector-java-<version>.jar is the main Jar file.

To add that jar file, just copy it (select jar file, CTRL + C) and paste it (CTRL + V) to the lib folder of the Eclipse web project. The lib folder is located at DBMySQLEclipse\src\main\webapp\WEB-INF\lib.

Or, you can do the same by configuring the build path of the web application, and deployment assembly. Compared to this approach the copy-paste option is easier. Select web application => Right Click => Build Path => Configure Build Path => Libraries => Select Classpath => Add External Jar Files => Apply => Apply and Close. By default, the jar files added to the classpath won’t come to the lib folder. To do that open properties of the web application => Deployment assembly => Add => Java Build Path Entities => Next => Select Jar file => Finish => Apply => Apply and close. Actually, we won’t see any jar file added to the lib folder, but it will be placed dynamically while the deployment of the web application. 

Step3:- Develop the resources.

Web Application Example for Servlet to MySQL Database in Eclipse

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 file (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) for Servlet to MySQL database,

package com.kp.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

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);
   }
}

Since it was a very basic and simple web application, therefore we are hardcoding the technical input values like JDBC driver class name, URL, database username, and password. But it is always recommended to gather those values from the web.xml file using <init-param> or <context-param> through ServletConfig/ServletContext values.

The deployment descriptor (web.xml) file,

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns="http://xmlns.jcp.org/xml/ns/javaee"
   xsi:schemaLocation=
"http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
   id="WebApp_ID" version="4.0">
   <display-name>DBMySQLEclipse</display-name>
   <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 the source code for this web application (to connect Servlet to MySQL Database in Eclipse) at GitHub.

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