JSTL and SQL-TAGS

May 22, 2008

The Struts
community has ordained that JSP should bestrictly a 'view-technology', in the
Model-View-Controller Architecture. According to Struts philosophy, JSP should
not deal with Data-Accesss and such data accessshould be done
by 'Model' components only.( read 'beans'). JSTL ,
however, provides for sql tags, inspired by ColdFusion! ( please see a very
short tutorial on DB-Operations using ColdFusion' available
in this issue as a separate lesson. and compare JSTL code and CF code!).And , a
few months back, the editor of 'Java Lobby' magazine was all admiration for the
absolutely nice features of these sql tags, whatever,
'struts-fans' may say! Just as EJB may be 'overkill', except for really big Enterprise
applications, Struts also may bean unnecessary complication for small
and medium level projects. In such cases, it is much more direct to provide for
data access by the JSP itself, but using JSTL 'sql' tags.We take up these 'sql' tags
inthis part of the tutorial.

———————————————–

Let us
begin with 'sql.htm'. It just provides a simple form with just a textarea & submit button. Normally, queries by MIS
department will be very complex and so we have provided a textarea
for the 'select' query.After filling up the query, it
is submitted and the corresponding query.jsp is
invoked.

———————————————–

//
query.htm

============

<html>

<body>

<formmethod=postaction="query.jsp">

<textareaname='area1'

rows=10cols=30>

</textarea>

<inputtype=submit>

</form>

</body>

</html>

————————————

query.jsp
is given below. In the standard jdbccode,we begin by asking for the
availability of the driver.

"jdbc.odbc.JdbcOdbcDriver". And then,
we specify the URL of the database as

'jdbc:odbc:telephone'.

——————

Similarly,
in JSTL also, we begin with

<sql:setDataSourcetag.

It has attributes for 'driver' and 'url'.

We will refer to the database as 'db'.

The next
step is to collect the query typed in area1 by the user.

<c:setvar="s"value="${param.area1}"/>

is used for
this purpose.We also check up whether the query typed
by the user has indeed been correctly received.

<c utvalue="${s}"/>

<br>

Next, the '<sql:query'tag, takes

three
attributes., such as,

symbolic
name:var="query1"

datasource="${db}

sql="${s}

The query result
is then displayed in table form.It should be possible
to follow the code now.In our example, we are having
an Access db,

with table1,
having two fields, (name, place).

registered with
ODBC.

——————————————-

//
query.jsp

============

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

<%@taglibprefix="sql"
%>

uri="http://java.sun.com/jstl/sql"

<html>

<body>

<sql:setDataSourcevar="db"

driver="sun.jdbc.odbc.JdbcOdbcDriver"

url="jdbc:odbc:dbdemo"/>

<c:setvar='s'value="${param.area1}"/>

<c utvalue="${s}"/><br>

<sql:queryvar="query1"

dataSource="${db}"

sql="${s}"/>

</sql:query>

<c:forEachvar="row"items="${query1.rows}" >

<tr>

</tr>

</c:forEach>

</table>

</body>

</html>

===============================================

In the
second example,(dbeditor.htm & dbeditor.jsp)
we provide a combo, with options such as:

add, modify,
remove and verify.

In JSTL , we have a separate sql tag
known as

'<sql:update'for 'add', 'modify' and 'remove'
operations.

When we
want to verify, we must use' <sql:query'tag, because, resultset will be returned.

——————

dbeditor.htm

<html>

<input
type=text name='text2'>number<br>

<input
type=text name='text3'> criterion<br>

<option value="add">add

<option
value="delete">delete

<option
value="modify">modify

<option value="verify">find

</select>

<br>

</body>

</html>

————————————————-

dbeditor.jsp

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

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

<html>

<body>

<sql:setDataSourcevar="db"

driver="sun.jdbc.odbc.JdbcOdbcDriver"

url="jdbc:odbc:dbdemo"/>

<c:setvar='a'value='${param.text1}'/>

<c:setvar='b'value='${param.text2}' />

<c:setvar='c'value='${param.text3}' />

<c:setvar='d'value='${param.combo1}' />

———————————————-

<c:iftest="${d == 'add'}"
>

<sql:updatevar="query1"dataSource="${db}"

sql="insert
into table1 values${a}','${b}')" >

</sql:update>

</c:if>

———————————————

<c:iftest="${d == 'delete'}"
>

<sql:updatevar="query1"dataSource="${db}"

sql="delete
from table1 where name='${a}'" >

</sql:update>

</c:if>

——————————————-

<c:iftest="${d == 'modify'}" >

<sql:updatevar="query1"dataSource="${db}"

sql="update
table1 set table1.name='${a}',

table1.place='${b}' where

table1.name='${c}'" >

<–sqlshould be typed in a single line–>

</sql:update>

</c:if>

——————————————

<c:iftest="${d == 'verify'}" >

<sql:queryvar="query1"dataSource="${db}"

sql="select
* from table1 where name='${a}'" >

</sql:query>

<c:forEachvar="row"
tems="${query1.rows}" >

<c:setvar="n" value="OK" />

<tr>

</tr>

</c:forEach>

<c:iftest="${n != 'OK'}" >

</c:if>

</table>

</c:if>

</html>

<body>

====================

These are ,afterall, the essentials. All else, are only, ornamental.
Thus, it will be seen that the JCP has done a great job in creating really fine
standard tags, for simplifying routine tasks. But, Java coders,

have to take note that , their job
as JSP coders, will be slowly erodedby
page-authors

using JSTL!

====

Suggested
Reference Books

=========================

1) ProJSP

SIMON BROWN & OTHERS

(A-Press)

(Excellent material)

2) JavaServer Pages

Hans Bergsten ( Third Edition)

(O'Reilly Press/SPD)