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
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:
- <sql:update dataSource="${db}" var="count">
- INSERT INTO Students VALUES (154,'Nasreen', 'jaha', 25);
- </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:
- C:\Users\hpnmaratt>
- C:\Users\hpnmaratt>cd C:\Program Files\MySQL\MySQL Server 5.7\bin
- 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:
- C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p
- Enter password: ****
- mysql>
It will look like this:
Step-3: Create the table Students in test database as shown below:
- mysql> use test;
- mysql> create table Students
- (
- id int not null,
- First_Name varchar (255),
- Last_Name varchar (255),
- Age int not null
- );
- Query OK, 0 rows affected (0.08 sec)
- mysql>
It will look like this:
Step 4: In final step you need to create few data records in Students table as shown below:
- mysql> INSERT INTO Students VALUES (150, 'Nakul', 'Jain', 22);
- Query OK, 1 row affected (0.05 sec)
- mysql> INSERT INTO Students VALUES (151, 'Ramesh', 'Kumar', 20);
- Query OK, 1 row affected (0.00 sec)
- mysql> INSERT INTO Students VALUES (152, 'Ajeet', 'Singhal', 22);
- Query OK, 1 row affected (0.00 sec)
- mysql> INSERT INTO Students VALUES (153, 'Hamza', 'Hussain', 22);
- Query OK, 1 row affected (0.00 sec)
- mysql>
It will look like this:
Let's see the simple JSP example to understand the use of <sql:update> tag is:
- <%@ 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>sql:update Tag</title>
- </head>
- <body>
- <sql:setDataSource var="db" driver="com.mysql.jdbc.Driver"
- url="jdbc:mysql://localhost/test"
- user="root" password="1234"/>
- <sql:update dataSource="${db}" var="count">
- INSERT INTO Students VALUES (154,'Nasreen', 'jaha', 25);
- </sql:update>
- <sql:query dataSource="${db}" var="rs">
- SELECT * from Students;
- </sql:query>
- <table border="2" width="100%">
- <tr>
- <th>Student ID</th>
- <th>First Name</th>
- <th>Last Name</th>
- <th>Age</th>
- </tr>
- <c:forEach var="table" items="${rs.rows}">
- <tr>
- <td><c:out value="${table.id}"/></td>
- <td><c:out value="${table.First_Name}"/></td>
- <td><c:out value="${table.Last_Name}"/></td>
- <td><c:out value="${table.Age}"/></td>
- </tr>
- </c:forEach>
- </table>
- </body>
- </html>