September 20, 2021

Programmingempire

This example explains Servlet and JDBC Connectivity with a MySQL database.

As can be seen in the output, this example shows a list of users which it fetches from a database table. Further, we use MySQL Workbench 8.0 CE for creating our database.

**Example of Servlet and JDBC Connectivity**

Creating Database Table

To begin with, we create a database called d1. Further, we use the <use d1> command and to make the database in use. After that, we create the table myusers and insert some rows in the table. The following figure shows the database commands.

Database Table - myusers — **Database Table – myusers**

Once, we create the database, we can access it in our dynamic web project. The following section shows how to create a dynamic web project.

Creating a Dynamic Web Project for Servlet and JDBC Connectivity

In order to create a Dynamic Web Project, launch the Eclipse EE IDE. Also, click on the File->New->Dynamic Web Project. As can be seen in the output shown above, we name the project as MyUsersApplication. Further, create a new servlet and name it UsersList. Now, open the servlet file, UsersList.java, and write code in the doGet() method to access the data from the database table.

The following code shows that first, we create a Connection object with the following connection string using the given username and password of the MySQL server.

jdbc:mysql://localhost:3306/d1", "username", "password"

After that, we create a Statement object representing the SQL statement. Further, we create an object of the ResultSet class by executing the method executeQuery() using the Statement object created above. Finally, we can access the records by using the next() method of the ResultSet object.

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.io.*;

@WebServlet("/UsersList")
public class UsersList extends HttpServlet {
	private static final long serialVersionUID = 1L;
  
    public UsersList() {
        super();
    }
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
      response.setContentType("text/html");
      PrintWriter ob=response.getWriter();
      ob.println("<html><head><title>Database Connectivity with JDBC</title></head>");
      ob.println("<body><center><h1>List of Users</h1><h2>");
      ob.println("<table border='2'>");
      try{
    	  Class.forName("com.mysql.jdbc.Driver");
    	  Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/d1", "root", "123456");
    	  Statement mystatement=con.createStatement();
    	  ResultSet myrows=mystatement.executeQuery("select * from myusers");
		  ob.println("<tr>");
		  ob.println("<td>");   
		  ob.println("User ID");
		  ob.println("</td>");
   		  ob.println("<td>");   
		  ob.println("Password");
		  ob.println("</td>");
   		  ob.println("<td>");   
		  ob.println("User Type");
		  ob.println("</td>");
		  ob.println("</tr>");
    	  while(myrows.next())
    	  {
    		  ob.println("<tr>");
    		  ob.println("<td>");   
    		  ob.println(myrows.getString(1));
    		  ob.println("</td>");
       		  ob.println("<td>");   
    		  ob.println(myrows.getString(2));
    		  ob.println("</td>");
       		  ob.println("<td>");   
    		  ob.println(myrows.getString(3));
    		  ob.println("</td>");
    		  ob.println("</tr>");
    	  }
      }catch(Exception ex){
    	  ex.printStackTrace();
      }
      ob.println("</table></h2></center></body></html>");
	}
}

Programmingempire

Creating Database Table

Creating a Dynamic Web Project for Servlet and JDBC Connectivity

You may also like...

Spring Boot Practice Questions and Their Solutions

Example of Using this Keyword in Java

Mocking with JUnit