JSTL and SQL-TAGS

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.

also read:

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, itis 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:setDataSource tag. 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:outvalue="${s}"/>

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"
  Consolas, Monaco, monospace;">%>
 Consolas, Monaco, monospace;">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:outvalue="${s}"/><br>
<sql:queryvar="query1"
dataSource="${db}"
sql="${s}"/>
 Consolas, Monaco, monospace;"></sql:query>
<table border="1">
<c:forEachvar="row"items="${query1.rows}" >
<tr>
 Consolas, Monaco, monospace;"><td> <c:out value="${row.name}"
  Consolas, Monaco, monospace;">/></td>
<td> <c:out value="${row.place}"
  Consolas, Monaco, monospace;">/></td>
</tr>
 Consolas, Monaco, monospace;"></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>
<body>
<form>method=post action="dbeditor.jsp">
<input>type=text name='text1'>name<br>
<input>type=text name='text2'>number<br>
<input>type=text name='text3'> criterion<br>
<select>name=combo1>
<option value="add">add
<option>value="delete">delete
<option>value="modify">modify
<option value="verify">find
</select>
<br>
<input>type=submit>
</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="insertinto table1 values${a}','${b}')" > 
</sql:update>
<c:outvalue="record added"/>
</c:if>
---------------------------------------------
 <c:iftest="${d == 'delete'}">
<sql:updatevar="query1"dataSource="${db}"
sql="deletefrom table1 where name= '${a}' " >
</sql:update>
<c:outvalue="record deleted"/>
</c:if>
-------------------------------------------
 <c:iftest="${d == 'modify'}" >
<sql:updatevar="query1"dataSource="${db}"
sql="updatetable1 set table1.name='${a}',
table1.place='${b}' where
table1.name='${c}'" >
<--sqlshould be typed in a single line-->
</sql:update>
<c:outvalue="record modified"/>
</c:if>
------------------------------------------
 <c:iftest="${d == 'verify'}" >
<sql:queryvar="query1"dataSource="${db}"
sql="select* from table1 where name='${a}'" >
</sql:query>
<tableborder="1">
<c:forEachvar="row"tems="${query1.rows}" >
<c:setvar="n" value="OK" />
<tr>
 <td> <c:out value="${row.name}"/></td>
 <td> <c:out value="${row.place}"/></td>
</tr>
</c:forEach>
<c:iftest="${n != 'OK'}" >
<c:outvalue="No such Records" />
</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

  • ProJSP by SIMON BROWN & OTHERS (A-Press) (Excellent material)
  • JavaServer Pages by Hans Bergsten ( Third Edition) (O’Reilly Press/SPD)
  • EARLY ADOPTER JSP Standard Tag Library by JAYSON FALKNER & OTHERS
    Wrox Press
  • JSTL in Action by SHAWN BAYERN (Manning/ DreamTech).

Comments

comments

About Krishna Srinivasan

He is Founder and Chief Editor of JavaBeat. He has more than 8+ years of experience on developing Web applications. He writes about Spring, DOJO, JSF, Hibernate and many other emerging technologies in this blog.

Speak Your Mind

*