JSTL XML Tags

In this fourth & last part of the tutorial on JSTL,the author deals with the 'sql' tags in JSTL and shows how they greatly simplify simple database operations like 'select' queries. In another demo, common database operations like 'add','modify' , 'dele

JSTL XML Tags

JSTL XML Tags

     

PART-4 - JSTL  &  SQL-TAGS  

JSP Tutorials HomeTutorial Home | Part 1 | Part 2Part 3 | Part 4

In this fourth & last part of the tutorial on JSTL,the author deals with the 'sql' tags in JSTL  and shows how they greatly simplify simple database operations like 'select' queries.  In another demo, common database operations like 'add','modify' , 'delete' & 'verify'also are dealt with.

The Struts community has ordained that JSP should be  strictly a 'view-technology', in the Model-View-Controller Architecture. According to Struts philosophy, JSP should not deal with Data-Accesss and such data access  should 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' may say! Just as EJB may be 'overkill', except for really big Enterprise applications, Struts also may be  an 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 in  this 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>  
<form  method=post  action="query.jsp">  
<textarea  name='area1'    rows=10  cols=30>  
</textarea>  
<input  type=submit>  
</form>  
</body>  
</html>

------------------------------------
query.jsp is given below. In the standard jdbc code,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:set  var="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:out   value="${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

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

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

<html>  
<body>  

<sql:setDataSource   var="db" 
  
 driver="sun.jdbc.odbc.JdbcOdbcDriver" 
 
url="jdbc:odbc:dbdemo"  />  

<c:set
   var='s'   value="${param.area1}"   />  
<c:out
   value="${s}"  />  <br>  
<sql:query 
 var="query1"    dataSource="${db}"  sql="${s}"   /> 
</sql:query>  

<table border="1">  
  <c:forEach   var="row"  items="${query1.rows}" >  
 
<tr> 
 
<td> <c:out value="${row.name}" /></td> 
 
<td> <c:out value="${row.place}" /></td>  
 
</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>  
<body bgcolor=lightgreen>  
<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

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

<sql:setDataSource  
var="db" 
 
driver="sun.jdbc.odbc.JdbcOdbcDriver" 
 
url="jdbc:odbc:dbdemo"  />

<c:set   var='a'   value='${param.text1}'  />  
<c:set   var='b'  value='${param.text2}' />  
<c:set   var='c'  value='${param.text3}' />  
<c:set   var='d'   value='${param.combo1}' />  
----------------------------------------------

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

<sql:update var="query1"  dataSource="${db}"  
 
sql="insert into table1 values${a}','${b}')" > 
</sql:update>  
<c:out  value="record added"/>  
</c:if>  
---------------------------------------------

<c:if   test="${d == 'delete'}" >  
 
<sql:update  var="query1"  dataSource="${db}" 
 
sql="delete from table1 where name='${a}'" > 
 
</sql:update> 
 
<c:out  value="record deleted"/>  
</c:if>
-------------------------------------------

<c:if  test="${d == 'modify'}" >
<
sql:update var="query1"  dataSource="${db}"  
   sql="update table1 set table1.name='${a}', 
  
table1.place='${b}' where 
  
table1.name='${c}'" > 

<--sql  should be typed in a single line  -->

</sql:update>
<c:out value="record modified"/>
</c:if>
------------------------------------------
<c:if  test="${d == 'verify'}" >  
 
<sql:query   var="query1"  dataSource="${db}"  
 
sql="select * from table1 where name='${a}'" > 
 
</sql:query>  
<table border="1">  
 
<c:forEach   var="row" tems="${query1.rows}" >  

 
<c:set   var="n" value="OK" />  
 
<tr> 
 
<td> <c:out value="${row.name}" /></td> 
 
<td> <c:out value="${row.place}" /></td>  
 </tr>

 </c:forEach>  
 
<c:if  test="${n != 'OK'}" >  
 
<c:out value="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 eroded  by page-authors using JSTL!

Suggested Reference Books

  1) Pro  JSP  by 
  
SIMON BROWN & OTHERS 
  
(A-Press) 
  
(Excellent material)

    2) JavaServer Pages   by
  Hans Bergsten ( Third Edition)
     (O'Reilly Press/SPD)

  3) EARLY ADOPTER JSP Standard Tag Library    by
   JAYSON FALKNER & OTHERS
   Wrox Press

  4) JSTL in Action   by
   SHAWN BAYERN
   (Manning/ DreamTech).

JSP Tutorials HomeTutorial Home | Part 1 | Part 2Part 3 | Part 4