JSTL SQL sql:transaction Tag

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

JSTL SQL sql:transaction Tag

JSTL SQL sql:transaction Tag

The <sql:transaction> tag is used for transaction management. It is used to group multiple <sql:update> into common transaction. If you group multiple SQL queries in a single transaction, a database is hit only once.

It is used for ensuring that the database modifications are performed by the nested actions which can be either rolled back or committed.

Example:

  1. <%  
  2. Date DoB = new Date("2000/10/16");  
  3. int studentId = 151;  
  4. %>  
  5. <sql:transaction dataSource="${db}">  
  6.    <sql:update var="count">  
  7.       UPDATE Student SET First_Name = 'Suraj' WHERE Id = 150  
  8.    </sql:update>  
  9.    <sql:update var="count">  
  10.       UPDATE Student SET Last_Name'Saifi' WHERE Id = 153  
  11.    </sql:update>  
  12.    <sql:update var="count">  
  13.      INSERT INTO Student   
  14.      VALUES (154,'Supriya', 'Jaiswal', '1995/10/6');  
  15.    </sql:update>  
  16. </sql:transaction>  

JSTL SQL <sql:transaction> 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 Student 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>  
  4. C:\Users\hpnmaratt>  
  5. C:\Users\hpnmaratt>cd C:\Program Files\MySQL\MySQL Server 5.7\bin  
  6. C:\Program Files\MySQL\MySQL Server 5.7\bin> 

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> 

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

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

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

  1. mysql> INSERT INTO student VALUES (150, 'Nakul', 'Jain', '1994/07/19' );  
  2. Query OK, 1 row affected (0.05 sec)  
  3.    
  4. mysql>  INSERT INTO Student VALUES (151, 'Simran', 'Kumar', '1992/03/9');  
  5. Query OK, 1 row affected (0.00 sec)  
  6.    
  7. mysql>  INSERT INTO Student VALUES (152, 'Ajeet', 'Singhal', '1992/01/23' );  
  8. Query OK, 1 row affected (0.00 sec)  
  9.    
  10. mysql> INSERT INTO Student VALUES (153, 'Hamza', 'Hussain', '1992/01/9');  
  11. Query OK, 1 row affected (0.00 sec)  
  12.    
  13. mysql> 

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

  1. <%@ page import="java.io.*,java.util.*,java.sql.*"%>  
  2. <%@ page import="javax.servlet.http.*,javax.servlet.*" %>  
  3. <%@ page import="java.util.Date,java.text.*" %>  
  4. <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>  
  5. <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>  
  6.    
  7. <html>  
  8. <head>  
  9. <title>sql:transaction Tag</title>  
  10. </head>  
  11. <body>  
  12.    
  13. <sql:setDataSource var="db" driver="com.mysql.jdbc.Driver"  
  14.      url="jdbc:mysql://localhost/test"  
  15.      user="root"  password="1234"/>  
  16.   
  17. <%  
  18. Date DoB = new Date("2000/10/16");  
  19. int studentId = 151;  
  20. %>  
  21.    
  22. <sql:transaction dataSource="${db}">  
  23.    <sql:update var="count">  
  24.       UPDATE Student SET First_Name = 'Suraj' WHERE Id = 150  
  25.    </sql:update>  
  26.    <sql:update var="count">  
  27.       UPDATE Student SET Last_Name= 'Saifi' WHERE Id = 153  
  28.    </sql:update>  
  29.    <sql:update var="count">  
  30.      INSERT INTO Student   
  31.      VALUES (154,'Supriya''Jaiswal''1995/10/6');  
  32.    </sql:update>  
  33. </sql:transaction>  
  34.    
  35. <sql:query dataSource="${db}" var="rs">  
  36.    SELECT * from Student;  
  37. </sql:query>  
  38.    
  39. <table border="2" width="100%">  
  40. <tr>  
  41.    <th>Emp ID</th>  
  42.    <th>First Name</th>  
  43.    <th>Last Name</th>  
  44.    <th>DoB</th>  
  45. </tr>  
  46. <c:forEach var="table" items="${rs.rows}">  
  47. <tr>  
  48.    <td><c:out value="${table.id}"/></td>  
  49.    <td><c:out value="${table.First_Name}"/></td>  
  50.    <td><c:out value="${table.Last_Name}"/></td>  
  51.    <td><c:out value="${table.dob}"/></td>  
  52. </tr>  
  53. </c:forEach>  
  54. </table>  
  55.    
  56. </body>  
  57. </html>