Database Operations with HTTP Servlet

The following article demonstrates Database Operations with HTTP Servlet using an example.

In order to create an HTTP servlet to perform various database operations, you can proceed as follows.

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class DatabaseServlet extends HttpServlet {
  private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
  private static final String DB_USER = "root";
  private static final String DB_PASSWORD = "password";

  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      // Load the JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      // Connect to the database
      conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

      // Execute a SELECT statement to get all the records from the database
      stmt = conn.prepareStatement("SELECT * FROM mytable");
      rs = stmt.executeQuery();

      // Process the results
      while (rs.next()) {
        // Get the values of the columns for each record
        int id = rs.getInt("id");
        String name = rs.getString("name");
        double price = rs.getDouble("price");

        // Do something with the values
        System.out.println(id + " " + name + " " + price);
      }

      // Execute an INSERT statement to add a new record to the database
      stmt = conn.prepareStatement("INSERT INTO mytable (name, price) VALUES (?, ?)");
      stmt.setString(1, "New Item");
      stmt.setDouble(2, 9.99);
      int rowsInserted = stmt.executeUpdate();

      // Print how many rows actually inserted
      System.out.println(rowsInserted + " row(s) inserted.");

      // Execute an UPDATE statement to update an existing record in the database
      stmt = conn.prepareStatement("UPDATE mytable SET name = ? WHERE id = ?");
      stmt.setString(1, "Updated Item");
      stmt.setInt(2, 1);
      int rowsUpdated = stmt.executeUpdate();

      // Print the number of rows updated
      System.out.println(rowsUpdated + " row(s) updated.");

      // Execute a DELETE statement to delete a record from the database
      stmt = conn.prepareStatement("DELETE FROM mytable WHERE id = ?");
      stmt.setInt(1, 2);
      int rowsDeleted = stmt.executeUpdate();

      // Print the number of rows deleted
      System.out.println(rowsDeleted + " row(s) deleted.");

    } catch (ClassNotFoundException | SQLException e) {
      e.printStackTrace();
    } finally {
      // Close the database resources
      try {
        if (rs != null) {
          rs.close();
        }
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

In this example, we have created a servlet class named DatabaseServlet that extends HttpServlet. Further, in the doGet method, we load the JDBC driver, and connect to the database using the DriverManager.getConnection method, and execute various SQL statements to perform database operations.

Then, we execute a SELECT statement to get all the records from the database and process the results using a while loop. After that, we execute an INSERT statement to add a new record to the database, an UPDATE statement to update an existing record in the database, and a DELETE statement to delete a record from the database.

Also, we use prepared statements to avoid SQL injection attacks.

Another example is here.

https://www.programmingempire.com/insert-and-display-records-using-http-servlet/

Further Reading