How to execute SQL update query in a JSP?

SQLJSPJava 8Object Oriented ProgrammingProgramming

The <sql:update> tag executes an SQL statement that does not return data; for example, SQL INSERT, UPDATE, or DELETE statements.

Attribute

The <sql:update> tag has the following attributes −

AttributeDescriptionRequiredDefault
sqlSQL command to execute (should not return a ResultSet)NoBody
dataSourceDatabase connection to use (overrides the default)NoDefault database
varName of the variable to store the count of affected rowsNoNone
scopeScope of the variable to store the count of affected rowsNoPage

Example

To start with basic concept, let us create a simple table Employees table in the TEST database and create few records in that table as follows −

Step 1

Open a Command Prompt and change to the installation directory as follows −

C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>

Step 2

Login to the database as follows −

C:\Program Files\MySQL\bin>mysql -u root -p
Enter password: ********
mysql>

Step 3

Create the table Employee in the TEST database as follows −

mysql> use TEST;
   mysql> create table Employees (
      id int not null,
      age int not null,
      first varchar (255),
      last varchar (255)
   );
   Query OK, 0 rows affected (0.08 sec)
mysql>

Create Data Records

We will now create few records in the Employee table as follows −

mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)

mysql>

Let us now write a JSP which will make use of the <sql:update> tag to execute an SQL INSERT statement to create one record in the table as follows −

<%@ page import = "java.io.*,java.util.*,java.sql.*"%>
<%@ page import = "javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix = "c"%>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/sql" prefix = "sql"%>
<html>
   <head>
      <title>JSTL sql:update Tag</title>
   </head>
   <body>
      <sql:setDataSource var = "snapshot" driver = "com.mysql.jdbc.Driver"
         url = "jdbc:mysql://localhost/TEST"
         user = "root" password = "pass123"/>
         <sql:update dataSource = "${snapshot}" var = "count">
            INSERT INTO Employees VALUES (104, 2, 'Nuha', 'Ali');
         </sql:update>
        <sql:query dataSource = "${snapshot}" var = "result">
           SELECT * from Employees;
        </sql:query>
       <table border = "1" width = "100%">
          <tr>
             <th>Emp ID</th>
             <th>First Name</th>
             <th>Last Name</th>
             <th>Age</th>
          </tr>
          <c:forEach var = "row" items = "${result.rows}">
             <tr>
                <td> <c:out value = "${row.id}"/></td>
                <td> <c:out value = "${row.first}"/></td>
                <td> <c:out value = "${row.last}"/></td>
                <td> <c:out value = "${row.age}"/></td>
             </tr>
          </c:forEach>
       </table>
   </body>
</html>

Access the above JSP, the following result will be displayed −

+-------------+----------------+-----------------+-----------------+
| Emp ID      | First Name     | Last Name       | Age             |
+-------------+----------------+-----------------+-----------------+
| 100         | Zara           | Ali             | 18              |
| 101         | Mahnaz         | Fatma           | 25              |
| 102         | Zaid           | Khan            | 30              |
| 103         | Sumit          | Mittal          | 28              |
| 104         | Nula           | Ali             | 2               |
+-------------+----------------+-----------------+-----------------+

Similar way, you can try SQL UPDATE and DELETE statements on the same table.

raja
Published on 25-Mar-2019 06:04:02
Advertisements