How to group queries using transaction in a JSP?

The tag is used to group the and tags into transactions. You can put as many and tags as statements inside the tag to create a single transaction.

It ensures that the database modifications performed by the nested actions are either committed or rolled back if an exception is thrown by any nested action.

Attribute

The tag has the following attributes −

Attribute	Description	Required	Default
dataSource	Database connection to use (overrides the default)	No	Default database
isolation	Transaction isolation (READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, or SERIALIZABLE)	No	Database’s default

Example

To start with the basic concept, let us create a Students table in the TEST database and create a 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 Employee table in the TEST database as follows −

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

Create Data Records

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

mysql> INSERT INTO Students
VALUES (100, 'Zara', 'Ali', '2002/05/16');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Students
VALUES (101, 'Mahnaz', 'Fatma', '1978/11/28');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Students
VALUES (102, 'Zaid', 'Khan', '1980/10/10');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Students
VALUES (103, 'Sumit', 'Mittal', '1971/05/08');
Query OK, 1 row affected (0.00 sec)

mysql>

Let us now write a JSP which will make use of the tag along with  tag to execute an SQL UPDATE statement. Here the code inside tag either will be executed completely or not at all −

   
      
   
   
      
      
      
         
            UPDATE Students SET last = 'Ali' WHERE Id = 102
         
         
            UPDATE Students SET last = 'Shah' WHERE Id = 103
         
         
            INSERT INTO Students
            VALUES (104,'Nuha', 'Ali', '2010/05/26');
         
      
      
         SELECT * from Students;
      
      

                                                                                                                                                                                     

Emp ID	First Name	Last Name	DoB

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

+-------------+----------------+-----------------+-----------------+
| Emp ID      | First Name     | Last Name       | DoB             |
+-------------+----------------+-----------------+-----------------+
| 100         | Zara           | Ali             | 2001-12-16      |
| 101         | Mahnaz         | Fatma           | 1978-11-28      |
| 102         | Zaid           | Ali             | 1980-10-10      |
| 103         | Sumit          | Mittal          | 1971-05-08      |
| 104         | Nuha           | Ali             | 2010-05-26      |
+-------------+----------------+-----------------+-----------------+