How to Perform Database Operations Using JSP?

The following article describes How to Perform Database Operations Using JSP.

Here is an example code for a JSP program that displays, inserts, updates, and deletes data from a database table.

1. Connect to the database.

<%@ page import="java.sql.*" %>

<%
   Connection connection = null;
   try {
      Class.forName("com.mysql.jdbc.Driver");
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "password");
   } catch (Exception e) {
      out.println("Error connecting to database");
   }
%>

2. Display data from the database table.

<%
   Statement statement = connection.createStatement();
   ResultSet resultSet = statement.executeQuery("SELECT * FROM users");
   while (resultSet.next()) {
      out.println("ID: " + resultSet.getInt("id"));
      out.println("Name: " + resultSet.getString("name"));
      out.println("Email: " + resultSet.getString("email"));
      out.println("<br>");
   }
   resultSet.close();
   statement.close();
%>

3. Insert data into the database table.

<%
   String name = request.getParameter("name");
   String email = request.getParameter("email");
   PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO users (name, email) VALUES (?, ?)");
   preparedStatement.setString(1, name);
   preparedStatement.setString(2, email);
   preparedStatement.executeUpdate();
   preparedStatement.close();
%>

4. Update data in the database table.

<%
   int id = Integer.parseInt(request.getParameter("id"));
   String name = request.getParameter("name");
   String email = request.getParameter("email");
   PreparedStatement preparedStatement = connection.prepareStatement("UPDATE users SET name=?, email=? WHERE id=?");
   preparedStatement.setString(1, name);
   preparedStatement.setString(2, email);
   preparedStatement.setInt(3, id);
   preparedStatement.executeUpdate();
   preparedStatement.close();
%>

5. Delete data from the database table.

<%
   int id = Integer.parseInt(request.getParameter("id"));
   PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM users WHERE id=?");
   preparedStatement.setInt(1, id);
   preparedStatement.executeUpdate();
   preparedStatement.close();
%>

In this code, the JDBC API is used to connect to a MySQL database, execute SQL statements, and manipulate data in a users table. The DriverManager.getConnection() method is used to establish a database connection. The Statement and PreparedStatement classes are used to execute SQL statements. The ResultSet class is used to retrieve the results of a query. The executeQuery() and executeUpdate() methods are used to execute SELECT and INSERT/UPDATE/DELETE statements, respectively.

Further Reading

Understanding Enterprise Java Beans

Java Practice Exercise

• Android
• Angular
• ASP.NET
• AWS
• Bootstrap
• C
• C#
• C++
• Cloud Computing
• Competitions
• Courses
• CSS
• Deep Learning
• Django
• Dot Net Framework
• HTML
• IoT
• Java
• JavaScript
• Kotlin
• Machine Learning
• MEAN Stack
• MERN Stack
• MongoDB
• NodeJS
• PHP
• Power Bi
• Projects
• Python
• Scratch 3.0
• Solidity
• SQL
• TypeScript
• VB.NET
• Web Designing
• XML

Princites