JSTL SQL Tags


JSTL SQL tags provide SQL support and allows us to interact with relational databases.

Syntax for including JSTL SQL tags library in our jsp page is:

    <%@ taglib uri=”http://java.sun.com/jsp/jstl/sql” prefix=”sql” %>


The JSTL SQL tags are as follows:

1. <sql:setDataSource> tag: It is used to create the data source variable directly from JSP and stored in a scoped variable.

Attribute Description
dataSource Database prepared in advance.
driver Name of JDBC driver class to be registered.
url JDBC URL for the database connection.
user Database username.
password Database password.
var Name of variable to represent the database.
scope Scope of variable to represent the database.

Example: The following example will set up the connection with database server.

    <%@ taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core” %>
    <%@ taglib prefix=”sql” uri=”http://java.sun.com/jsp/jstl/sql” %>
    <html>
    <body>
        <sql:setDataSource var=”db” driver=”com.mysql.jdbc.Driver” url=”jdbc:mysql://localhost/test_sql” user=”uname” password=”1234” />
    </body>
    </html>    


2. <sql:query> tag: It is used for executing the SQL query defined in its sql attribute or the body.

Example: The following example will select all the records from the database.

    <%@ taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core” %>
    <%@ taglib prefix=”sql” uri=”http://java.sun.com/jsp/jstl/sql” %>
    <html>
    <body>
        <sql:setDataSource var=”db” driver=”com.mysql.jdbc.Driver” url=”jdbc:mysql://localhost/test_sql” user=”uname” password=”1234” />
            <sql:query var=”rs” dataSource=”${db}”>
            SELECT * from Tutorials;
        </sql:query>
    </body>
    </html>


3. <sql:update> tag: It is used to execute the SQL query defined in its body or sql attribute. It may be a SQL UPDATE, INSERT or DELETE statement.

Example: The following example will select all the records from the database.

    <%@ taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core” %>
    <%@ taglib prefix=”sql” uri=”http://java.sun.com/jsp/jstl/sql” %>
    <html>
    <body>
        <sql:setDataSource var=”db” driver=”com.mysql.jdbc.Driver” url=”jdbc:mysql://localhost/test_sql” user=”uname” password=”1234” />
        <sql:update var=”rs” dataSource=”${db}”>
            INSERT INTO Tutorials VALUES (1027, ‘Abhi’, ‘2018-09-22’);
        </sql:update>
    </body>
    </html>



4. <sql:param> tag: It is used to set the parameter in an SQL statement to the specified value.

Example: The following example will select all the records from the database.

    <%@ taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core” %>
    <%@ taglib prefix=”sql” uri=”http://java.sun.com/jsp/jstl/sql” %>
    <html>
    <body>
        <sql:setDataSource var=”db” driver=”com.mysql.jdbc.Driver” url=”jdbc:mysql://localhost/test_sql” user=”uname” password=”1234” />
        <sql:update var=”rs” dataSource=”${db}”>
            DELETE FROM Tutorials WHERE Id = ?
            <sql:param value = “$(pageId)” />
        </sql:update>
    </body>
    </html>


5. <sql:dateParam> tag: It is used to provide the date and time value for the SQL query parameter.

Example: The following example will select all the records from the database.

    <%@ taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core” %>
    <%@ taglib prefix=”sql” uri=”http://java.sun.com/jsp/jstl/sql” %>
    <html>
    <body>
        <%  Date DoB = new Date(“2018-05-25”);
            String name=”Tutorialsduniya”;
        %>
        <sql:setDataSource var=”db” driver=”com.mysql.jdbc.Driver” url=”jdbc:mysql://localhost/test_sql” user=”uname” password=”1234” />
        <sql:update var=”rs” dataSource=”${db}”>
            UPDATE Tutorials SET dob=? WHERE name=?
            <sql:dateParam value=”<%=DoB%>” type=”DATE” />
            <sql:param value = “<%=name%>” />
        </sql:update>
    </body>
    </html>


6. <sql:transaction> tag: It is in transaction management to group multiple <sql:update> tags into single transaction.

Example: The following example will select all the records from the database.

    <%@ taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core” %>
    <%@ taglib prefix=”sql” uri=”http://java.sun.com/jsp/jstl/sql” %>
    <html>
    <body>
        <%  Date DoB = new Date(“2018-05-25”);
            String name=”Tutorialsduniya”;
        %>
        <sql:setDataSource var=”db” driver=”com.mysql.jdbc.Driver” url=”jdbc:mysql://localhost/test_sql” user=”uname” password=”1234” />
        <sql:transaction dataSource=”${db}”>
        <sql:update var=”rs” >
            UPDATE Tutorials SET dob=”2018-09-24” WHERE name=”Abhi”
        </sql:update>
        <sql:update var=”rs” >
            INSERT INTO Tutorials VALUES (1028, ‘Ankit’, ‘2018-06-15’);
        </sql:update>
        </sql:transaction>
    </body>
    </html>