Servlet With JDBC

Servlets are Java programs that run on a web server and are used to handle client requests, process data, and generate dynamic responses for web applications. They act as a bridge between client requests and server-side processing.

• Servlets execute on the server-side and manage user requests efficiently.
• They are used to build dynamic and interactive web applications.
• Servlets can handle complex request processing and generate customized responses.

Prerequisites

• Download JDK
• Download Eclipse IDE.
• Install Apache Tomcat – 9.x or 10.x (configured in Eclipse)
• Downloaded and installed PostgreSQL
• JDBC Driver – postgresql-42.2.18.jar (placed in WEB-INF/lib)

Example to show Student Database Management system

In this example, we will be creating a simple "Student Database Management system" in which, we can insert, update, delete and view the student details.

Step by Step Implementation

Step 1: Create Table in PostgreSQL

• Create a Table studentdetails to store details of students.
• Create a table named studentdetails with the following columns:

Below image describe the structure of table:

Step 2: Create a Dynamic web project in Eclipse

• In Eclipse IDE, create a Dynamic web project as the below project structure.
• To connect with PostgreSQL DB, add "postgresql-42.2.18.jar" in the project under "WEB-INF/lib" folder.

Step 3: Create required JSP(Home.jsp) to get the details

This is the welcome page of the project. On this page, we will be displaying the different types of operations such as Insert, Delete and Select data on the Student details.

Based on the student selection, the respective JSP page will be displayed.

Step: 3.1 Create Insert.jsp

This page takes the required values from the student - Student Id, Student Name, Email Id, and Phone number and submits the page to Insert servlet to store the values in DB.

Based on the action and method specified, it will map the respective servlet and go to the doPost method() in that servlet.

Step: 3.2 Create Delete.jsp

To delete the student information from DB, we need the ID of the student. This page takes the id from the student and submits the value to the Delete servlet.

Step: 3.3 Create Success.jsp

To display the success message after insert, update and delete operations.

After successful insertion or deletion of the values, this page will display the success message to the UI. 

Step: 3.4 Create Select.jsp

To populate the student information from DB, we need the ID of the student. This page takes the id provided and submits it to the Select servlet to fetch the details.

Step: 3.5 Create Result.jsp

This page is to populate the student information in the UI. To display the result set of the student details based on the given id.

Step: 3.6 CreateUpdate.jsp

Once the information is populated, if the student wants to update their details, this page takes the updated information from the student and submits it to the Update servlet.

We are making the ID value as read-only so that it won't be changed and the information will be updated with respect to the ID value.

Step 4: Create DBUtil.java

To establish a JDBC connection with PostgreSQL, we need to specify the driver, URL, username, and password objects of the PostgreSQL. In order to reuse these objects in all the servlets to make a connection with the DB, we can provide these values in separate class like below.

Instead of using a java class, we can specify these values in the properties file also.

Step 5: Create respective servlet classes

Servlet classes and JSP pages can be mapped through the web.xml(deployment descriptor) or by using annotations. In this example, we are using @WebServlet annotation to map the jsp pages to their respective servlets. To work with servlets, we need to extend the java class from HttpServlet.

Step: 5.1 InsertDetails.java

When the student enters the data through Insert.jsp and submits the page to insert the details into DB, based on the URL "/InsertDetails" in JSP page, InsertDetails.java servlet class will be mapped and executed.

Explanation:

• We need to import all the required packages for the servlet and JDBC connection.
• To insert the details, we need to use the doPost method of the servlet class.
• First, establish the JDBC connection inside the try/catch block to handle the exception if arises.
• Using Class.forName(), load the PostgreSQL driver.
• Then using Connection and DriverManager object, get the connection to the PostgreSQL server.
• Prepare statement object with the insert query and set the student information into respective parameters and execute the query.
• Once the query is executed, close the statement and connection object to avoid resource leak.
• Then redirect the page to Success.jsp page with a successful message to display on the screen.

Step: 5.2 DeleteDetails.java

Same as the insert operation delete servlet also will be mapped based on the URL specified. When a student enters the ID value to delete the student information, DeleteDetails.java servlet class will be mapped and executed.

Explanation:

• As specified in the insert servlet, the same way delete the servlet also will work.
• First, establish JDBC connection and prepare to delete statement which takes an ID as an input parameter.
• Once the delete operation is completed, display the success message in the UI.

Step: 5.3 SelectDetails.java

To populate the student information based on the ID submitted.

Explanation:

• Here, we are fetching the details, so we need to use the doGet() method of the servlet class.
• Establish a JDBC connection and prepare a select statement with ID as input parameter and get all the details.
• In order to display this information, send these values to the Result.jsp page.

Step: 5.4 UpdateDetails.java

If the student wants to update any information, then we need to get the respective ID and perform the operation.

Explanation:

• We are updating the student information, hence the doPost() method is called.
• Establish JDBC connection and prepare update statement passing ID as the input parameter.
• Execute the query and display the success message in the UI.

Step 6: Run the Application

Now, run the application using Run As -> Run on Server. The application will be deployed on the Tomcat server. Run the URL,

http://localhost:8081/ServletsPostgre/Home.jsp

in the browser to get the home page of the application.

• To insert the details, click on Insert data. On clicking, redirects to Insert.jsp page and displays below UI.

• Enter all the details and click on Insert Data.
• After successful insertion, a success message will be displayed on the screen.

• Return to the home page.
• Check if the data is inserted successfully in PostgreSQL DB.

• To fetch the values from the DB, click on Select Data on the Home page.

• Enter the ID which you want to fetch the details and click View data.
• The result will be displayed on the Result.jsp page.

• If the data needs to be updated, click on Update Data, or if don't just return to the home page.
• If we click on update data, redirect to the Update.jsp page.

• Here, the Id will be displayed on the load of the page and will be read-only.
• Update the data and click on update data. After updating success message will be displayed.
• Check if the data is updated in PostgreSQL DB.

• Finally, to delete the data from DB, click on Delete data on the home page.

• Enter the ID which has to be deleted and click on Delete Data.
• The information will be deleted from the DB and a success message will be displayed as below.

• Check in the DB if the information is deleted.

In this way, we can perform all the insert, update, delete and select operations using Servlets with JDBC.