JSTL SQL sql:update Tag

JSTL sql:update Tag for beginners and professionals with examples on JSTL core tags, function tags, formatting tags, SQL tags, and miscellaneous tags.

JSTL SQL sql:update Tag

JSTL SQL sql:update Tag

The <sql:update> tag is used for executing the SQL DML query defined in its sql attribute or in the tag body. It may be SQL UPDATE, INSERT or DELETE statements.

Example:

  1. <sql:update dataSource="${db}" var="count">  
  2. INSERT INTO Students VALUES (154,'Nasreen', 'jaha', 25);  
  3. </sql:update>  

JSTL SQL <sql:update> Complete Example

Consider the below information about your MySQL database setup:

  • We are using the JDBC MySQL driver
  • We are using the test database on local machine
  • We are using the "root" as username and "1234" as password to access the test database.

To understand the basic concept, let us create a simple table Students in the test database and creates the few records in that table using command prompts as follows:

Step-1: Open the command prompt and change to the installation directory as follows:

  1. C:\Users\hpnmaratt>  
  2. C:\Users\hpnmaratt>cd C:\Program Files\MySQL\MySQL Server 5.7\bin  
  3. C:\Program Files\MySQL\MySQL Server 5.7\bin>  

It will look like this:

 

Step-2: Login to the database using command prompt as shown below:

  1. C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p  
  2. Enter password: ****  
  3. mysql>  

It will look like this:

Step-3: Create the table Students in test database as shown below:

  1. mysql> use test;  
  2. mysql> create table Students  
  3.     (  
  4.      id int not null,  
  5.      First_Name varchar (255),  
  6.      Last_Name varchar (255),  
  7.      Age int not null  
  8.     );  
  9. Query OK, 0 rows affected (0.08 sec)  
  10. mysql>  

It will look like this:

Step 4: In final step you need to create few data records in Students table as shown below:

  1. mysql> INSERT INTO Students VALUES (150, 'Nakul', 'Jain', 22);  
  2. Query OK, 1 row affected (0.05 sec)  
  3.    
  4. mysql>  INSERT INTO Students VALUES (151, 'Ramesh', 'Kumar', 20);  
  5. Query OK, 1 row affected (0.00 sec)  
  6.    
  7. mysql>  INSERT INTO Students VALUES (152, 'Ajeet', 'Singhal', 22);  
  8. Query OK, 1 row affected (0.00 sec)  
  9.    
  10. mysql> INSERT INTO Students VALUES (153, 'Hamza', 'Hussain', 22);  
  11. Query OK, 1 row affected (0.00 sec)  
  12.    
  13. mysql>  

It will look like this:

Let's see the simple JSP example to understand the use of <sql:update> tag is:

  1. <%@ page import="java.io.*,java.util.*,java.sql.*"%>  
  2. <%@ page import="javax.servlet.http.*,javax.servlet.*" %>  
  3. <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>  
  4. <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>  
  5.   
  6. <html>  
  7. <head>  
  8. <title>sql:update Tag</title>  
  9. </head>  
  10. <body>  
  11.    
  12. <sql:setDataSource var="db" driver="com.mysql.jdbc.Driver"  
  13.      url="jdbc:mysql://localhost/test"  
  14.      user="root"  password="1234"/>  
  15. <sql:update dataSource="${db}" var="count">  
  16. INSERT INTO Students VALUES (154,'Nasreen', 'jaha', 25);  
  17. </sql:update>  
  18.   
  19. <sql:query dataSource="${db}" var="rs">  
  20. SELECT * from Students;  
  21. </sql:query>  
  22.    
  23. <table border="2" width="100%">  
  24. <tr>  
  25. <th>Student ID</th>  
  26. <th>First Name</th>  
  27. <th>Last Name</th>  
  28. <th>Age</th>  
  29. </tr>  
  30. <c:forEach var="table" items="${rs.rows}">  
  31. <tr>  
  32. <td><c:out value="${table.id}"/></td>  
  33. <td><c:out value="${table.First_Name}"/></td>  
  34. <td><c:out value="${table.Last_Name}"/></td>  
  35. <td><c:out value="${table.Age}"/></td>  
  36. </tr>  
  37. </c:forEach>  
  38. </table>  
  39.   
  40. </body>  
  41. </html>