SQL Tag Library in Java Server Page

Ashish Bhatnagar
2w
160
0
4

Article

Introduction

JSTL SQL tags are used to access databases and are designed for low-volume Web-based applications. SQL tag library provides tags that allow direct database access within a JSP page. Tags in SQL library have a prefix <sql>.
The JSTL SQL tag provides the following functionalities:

Passing Database queries

Provides users with the SQL Tag Library to query the database. The tag library allows executing queries such as SELECT statement. This can be executed using the <sql:query>tag.

Accessing query results

Allows the users to access results of queries.

Database modifications

Provides the SQL tag library. Which helps in modifying database using the <sql:update> tag.

The setDataSource Tag

The <sql: setdatasource>tag is used to set a data source for database. This is an empty tag and allows the user to set data source information for a database.

Syntax

<sql:setDataSource DataSource= “datasource”  url =”jdbcurl”driver=”driverclassdriver” user =”username”password = “userpwd” var = “varname” scope = “page/request/session/application”/>

Where

Datasource: Can either be the path to Java Naming and Directory Interface (JNDI) resource or a JDBC parameter string.
URL: Is the URL associated with the database.
Driver: Is a JBDC parameter and takes the driver class name.
User: Takes the user of the database.
Password: Takes the user password.
Var: Is the name of exported scoped variable for the data source specified.
Scope: Specifies the scope.

Source Code

<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>

<html>
<head>
    <title>Using SQL Tag Library</title>
</head>
<body>
    <h1>Using SQL Tag Library</h1>
    <c:set var="valprice" value="1000" />
    <sql:setDataSource var="conn" 
        driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
        url="jdbc:sqlserver://10.1.3.27:1433;databaseName=pubs;"
        user="sa" password="playware" />
    <sql:query dataSource="${conn}" var="result">
        SELECT * FROM books WHERE price > ${valprice}
    </sql:query>
    <table border="1">
        <tr>
            <th>Book ID</th>
            <th>Title</th>
            <th>Price</th>
        </tr>
        <c:forEach var="row" items="${result.rows}">
            <tr>
                <td>${row.book_id}</td>
                <td>${row.title}</td>
                <td>${row.price}</td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>

This Code is used to set the data source for the database by using <sql:setdataSource>tag.

Output

Output

The query Tag

The <sql:query>tag searches the database and returns a result set containing rows of data .The tag can either be an empty tag or a container tag.

Syntax for empty query tag

<sql:query sql = “sqlQuery” var =”varName” scope =”{page|request| session|application}” dataSource=”dataSource”  maxRows = startRow =”startRow”/>

Where

Sql: Specifies the SQL query statement.
Var: Specifies the name of the exported scope variable for the query result.
Scope: Specifies the scope of the variable.
dataSource: Specifies the data source associated with the database to query.
maxRows: Specifies the maximum number of rows to be included in the result.
startRow: Specifies the row starting at the specified index.

The syntax for the container query tag

<sql:query var = “varName” datasource =”datasource” scope =”{page|request|session|application}”maxrows =startRow =”startRow”>
           SQL Statement
          <sql: param/>
</sql:query>

Where

Param: Takes the parameter for the query.

Source Code

<sql:query var=”products” dataSource=${ conn}’’>
         select * from Products
</sql:query>

This code, <sql:query> tag searches the database and returns the result set containing rows of data.

The update Tag

The <sql:update>tag is used to insert or delete rows from a table in a database .it is also used for updating information stored in a table.This tag executes an INSERT, UPDATE, or DELETE statement and returns 0 if no rows are affected by the query statement.

Syntax for empty update tag

<sql:update sql = “sqlUpdate”dataSource= “dataSource”var= “varName” scope =”{page|request|session|application}”/>

Where

sql: Specifies the update,insert or delete statement.
datasource: Is the data source associated with the database to update.
var: Specifies the name of the exported scope variable for the result of the database update.
Scope: Specifies the scope of variable.

Syntax for the container update tag

<sql:update datasource =”datasource” var = “varName” scope =”{page|request|session|application}”>
           SQL Statement
          <sql: paramvalue =”value”/>
<sql : update>

Where

Update: Is the SQL UPDATE statement.
Param : Takes the parameter for the query.

Source Code

<sql:update var="newTable">
    CREATE TABLE Products
    (
        ProductId int IDENTITY(1000,1) NOT NULL PRIMARY KEY,
        ProductName varchar(20) NOT NULL,
        ProductType varchar(15) NOT NULL,
        Price varchar(5) NOT NULL,
        Brand varchar(25) NOT NULL,
        Description varchar(50) NOT NULL
    )
</sql:update>

<sql:update var="newrow" dataSource="${conn}">
    INSERT INTO Products (ProductName, ProductType, Price, Brand, Description)
    VALUES ('Jeans', 'Clothes', '1000', 'Lee', 'Good Quality Jeans')
</sql:update>

In this Code ,the <sql :update> tag is used to add the product detail in the Products table.

The Transaction Tag

Batch processing is a system of processing data in batches , whereas, transaction is a sequence of operations performed as a single logical unit of work .in SQL, a batch can also be series of standalone task or transaction where failure of one process can cause roll back of the complete batch.

In SQL , the SQLTransact( ) method commits or roll back the current transaction. All changes to the database that have been made on being connected since connect time or the previous call to SQLTransact( ), whichever is recent, are committed or rolled back. if a transaction is active on a connection, the application must call SQLTransact( ) before it can be disconnected from the database. Thus, the <sql: transaction>tag is used to establish a transaction context for <sql:query> and <sql:update>tags.

Syntax

<sql :transaction datasource =”datasource” isolation =isolationLevel>
    <sql :update> or < sql:query > statements
 </sql :transaction>

Where

datasource: Sets the SQL data source.This can be a string or a DataSource object.The Datasource objects are used to access relational database for storing, organizing and retrieving data.
Isolation: Sets the transaction isolation level.Isolation level can be read_committed,read_uncommitted,repeatable_read or serializable

Source Code

<sql:update var="newTable">
    CREATE TABLE Products
    (
        ProductId int IDENTITY (1000, 1) NOT NULL PRIMARY KEY,
        ProductName varchar(20) NOT NULL,
        ProductType varchar(15) NOT NULL,
        Price varchar(5) NOT NULL,
        Brand varchar(25) NOT NULL,
        Description varchar(50) NOT NULL
    )
</sql:update>

In this code, the <sql : transaction > tag includes <sql : update> that creates a new table as Products.

The param Tag

The <sql:param> tag sets values for the parameter in SQL statements .it acts as a sub tag for <sql :query> and <sql : update>.

Syntax

<sql:param value  = “value”/>

Where

Value: Sets the value for the parameter.

Source Code

<sql:update dataSource="${conn}" var="result">
    INSERT INTO AddressList (name, street, city, country, telephone) 
    VALUES (?, ?, ?, ?, ?)
    <sql:param value="${param['name']}" />
    <sql:param value="${param['street']}" />
    <sql:param value="${param['city']}" />
    <sql:param value="${param['country']}" />
    <sql:param value="${param['tel']}" />
</sql:update>
<c:if test="${result >= 1}">
    <p>Record successfully inserted into AddressList!</p>
</c:if>
<c:if test="${result == 0}">
    <p>Insertion failed. Please try again.</p>
</c:if>

The Source Code uses <sql:param>to set the value of the various parameters.

Full JSP Code with Output

<sql:update var="newTable">
    CREATE TABLE Products
    (
        ProductId int IDENTITY(1000,1) NOT NULL PRIMARY KEY,
        ProductName varchar(20) NOT NULL,
        ProductType varchar(15) NOT NULL,
        Price varchar(5) NOT NULL,
        Brand varchar(25) NOT NULL,
        Description varchar(50) NOT NULL
    )
</sql:update>
<c:if test="${newTable >= 0}">
    <p>Table 'Products' created successfully!</p>
</c:if>
<c:if test="${newTable == 0}">
    <p>Table creation failed or the table already exists.</p>
</c:if>
<sql:update var="newrow" dataSource="${conn}">
    INSERT INTO Products (ProductName, ProductType, Price, Brand, Description)
    VALUES ('Jeans', 'Clothes', '1000', 'Lee', 'Good Quality Jeans')
</sql:update>
<c:if test="${newrow >= 1}">
    <p>New product 'Jeans' inserted successfully!</p>
</c:if>
<c:if test="${newrow == 0}">
    <p>Insertion of new product failed.</p>
</c:if>

Output

Output

Summary

The JSTL SQL Tag Library simplifies database interactions in JSP by allowing developers to execute queries, update records, and manage transactions using built-in tags. It provides an easy-to-use approach for handling database operations without requiring explicit JDBC code. However, JSTL SQL tags are best suited for low-volume web applications and should be avoided in enterprise-level applications where MVC frameworks like Spring and Hibernate offer better database management solutions.

By using tags like `<sql:setDataSource>`, `<sql:query>`, `<sql:update>`, and `<sql:transaction>`, developers can efficiently integrate database operations into JSP pages while maintaining clean and manageable code. Despite its ease of use, it is advisable to separate database logic from the presentation layer to ensure better scalability and maintainability.

Up Next

Ebook Download

View all

Printing in C# Made Easy

Read by 22.5k people

Download Now!

Learn

View all