DB taglib ver. 3.5


    Custom JSP taglib. Lets you perform SQL requests to your JDBC datasource.

Tags are:

openConnection

opens database connection and saves it in the page scope variable. Parameters are:

1) driver JDBC driver
2) url database url
3) jndiname JNDI name for datasource (you must set this parameter or (driver,url) pair)
4) user Optional parameter: user name for database connection
5) password Optional parameter: password for database connection
6) id variable name you will use later as a reference to your connection

For example:
 


<%@ taglib uri="taglib.tld" prefix="sql" %>
<sql:openConnection driver="your_jdbc_driver" url="your_db_url" user="scott" password="tiger"/>

closeConnection

closes database connection. Parameters are:

1) id connection id

For example:
 


<%@ taglib uri="taglib.tld" prefix="sql" %>
<sql:openConnection driver="your_jdbc_driver" url="your_db_url" user="scott" password="tiger" id="A" />

...

<sql:closeConnection id="A" />

closeStatement

closes CallableStatement (see below how to run stored pocedures). Parameters are:

1) res describes a name for statement

ifError

Executes own body in case of any error in the previous DB request. Parameters are:

1) id connection id

For example:
 


<%@ taglib uri="taglib.tld" prefix="sql" %>
<sql:openConnection driver="your_jdbc_driver" url="your_db_url" user="scott" password="tiger" id="A" />

<sql:ifError id="A">
   <br>Could not connect to database
</sql:ifError>

getError

Prints error message from the previous DB request. Parameters are:

1) id connection id

For example:
 


<%@ taglib uri="taglib.tld" prefix="sql" %>
<sql:openConnection driver="your_jdbc_driver" url="your_db_url" user="scott" password="tiger" id="A" />

<sql:ifError id="A">
   <br>Could not connect to database
   <br>Error is: <sql:getError id="A"/>
</sql:ifError>

setQuery

executes SQL query. Parameters are:

1) id connection id
2) query Optional parameter. Defines a SQL query
3) res Optional parameter: describes a name of page scope variable for result.
4) startRow Optional parameter: describes a starting row for select query. Default value is 1 (first row).
5) maxRows Optional parameter: describes a maximal amount of returned rows for select query. By default all rows will be returned.

For example:
 


<%@ taglib uri="taglib.tld" prefix="sql" %>
<sql:openConnection driver="your_jdbc_driver" url="your_db_url" user="scott" password="tiger" id="A" />

<sql:setQuery id="A" query="delete from T where UserId=12345" />

You can define SQL query as a parameter query or as a body for setSQL tag:
 


<sql:setQuery id="A">
  <sql:setSQL>delete from T where UserId=12345</sql:setSQL>
</sql:setQuery id="A">

As for JDBC you may use ? sign as a mark for parameters within your query. You will set actual values for parameters through setParameter tag.

For example:
 


<sql:setQuery id="A" query="delete from T where UserId=?">
   <sql:setParameter position="1" type="int">12345</sql:setParameter>
</sql:setQuery>

Tag setParameter supports types like blob or clob also. In this case tag's body is a name for data file. E.g.:
 


<sql:setParameter position="2" type="clob">c:/data/memo.txt</sql:setParameter>

An optional parameter res describes a page scope variable with query results. For insert, update, delete this variable (type is Integer) will keep an integer value - how many rows are affected. E.g.:
 


<sql:setQuery id="A" query="update T set Status=0 where UserId>5" res="B"/>
<%=B%> rows have been updated

For select this parameter (res) defines a reference to the object you will use for reading selected data. E.g.:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
Found <sql:getCount res="B"/> records

See a list of tags deals with select results below.

You can execute stored procedures with this tag. For example:
 


<sql:setQuery id="A" query="{call myProc(?)}">
   <sql:setParameter position="1" type="int">6789</sql:setParameter>
</sql:setQuery>

If you need to use some output from your stored procedure you can use setOutputParameter tag. In this case parameter res will describe a CallableStatement you will use for access to output data. Note: in this case you have to directly close this statement after that (directly in Java scriptlet or through closeStatement tag). For example:
 


<sql:setQuery id="A" res="myData" query="{call myProc1(?,?)}">
   <sql:setParameter position="1" type="int">6789</sql:setParameter>
   <sql:setOutputParameter position="2"><%=java.sql.Types.INTEGER%></sql:setOutputParameter>
</sql:setQuery>

<!-- now we can use output data -->
<%=(java.sql.CallableStatement)myData.getInteger(2)%>

<!-- close our statement -->
<sql:closeStatement res="myData"/>

setBlobQuery

executes SQL query that requests BLOB (CBLOB, LONGVAR) column and outputs data. Parameters are:

1) id connection id
2) query Optional parameter. Describes a SQL query
3) contentType content type for output
4) res Optional parameter. Describes a name for the page scope variable with results. Type of this variable depends on the parameter contentType. If contentType is a text based (e.g. text/html, text/xml etc.) than it is java.lang.String, otherwise it is java.lang.Object that could be casted to byte[].

For example:
 


<%@ taglib uri="taglib.tld" prefix="sql" %>
<sql:openConnection driver="your_jdbc_driver" url="your_db_url" user="scott" password="tiger" id="A" />

<sql:setBlobQuery id="A" query="select Picture from T where UserId=12345" contentType="image/jpeg" />

As for setQuery tag you may use ? sign as a mark for parameters within your query. You will set actual values for parameters through setParameter tag.

For example:
 


<sql:setBlobQuery id="A" query="select XML_DATA from T where UserId=?" contentType="text/xml">
   <sql:setParameter position="1" type="int">12345</sql:setParameter>
</sql:setBlobQuery>

With parameter res tag saves the output in the page scope variable. For example:
 


<sql:setBlobQuery id="A" query="select XML_DATA from T where UserId=?" contentType="text/xml" res="myXML">
   <sql:setParameter position="1" type="int">12345</sql:setParameter>
</sql:setBlobQuery>

<%=myXML%>

setSQL

Body tag defines a SQL string (statement). Parameters are: none

setParameter

Tag's body defines one parameter for SQL statement. Parameters are:

1) position Optional parameter. Describes an index (the first parameter is 1, the second is 2 etc.) Default value is 1.
2) type Optional parameter. Describes a type for this parameter. Possible values are: boolean, byte, date, double, float, int, long, null, string, time, timestamp, blob, clob. Default value is string. For types blob and clob tag's body will be used as a name for data file.

setOutputParameter

Describes output parameter for stored procedure. Tag's body defines a type (integer value). Parameters are:

1) position Optional parameter. Describes an index (the first parameter is 1, the second is 2 etc.) Default value is 1.
2) type Optional parameter. Describes a type (integer value) for this parameter. Default value is java.sql.Types.VARCHAR.

transaction

Marks a transaction. Parameters are:

1) id connection id

For example:
 


<%@ taglib uri="taglib.tld" prefix="sql" %>
<sql:openConnection driver="your_jdbc_driver" url="your_db_url" user="scott" password="tiger" id="A" />

<sql:transaction id="A"/>
   <sql:setQuery id="A" query="delete from T where UserId=12345" />
   <sql:setQuery id="A" query="update T1 set Status=0 where UserId=12345" />
</sql:transaction>

Data reading

getCount

Reads the number of rows retrieved from the database. Parameters are:

1) res id for results (see setQuery)
2) id Optional parameter: describes a page scope variable for data (type is Integer). Without this parameter tag just prints data. For example:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
Found <sql:getCount res="B"/> records

or

<sql:setQuery id="A" query="select A,B from T" res="B"/>
<sql:getCount res="B" id="C"/>
Found <%=C%> records

ifFound

Body tag. Executes own body if query's result is not empty. Parameters are:

1) res id for results (see setQuery). For example:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
<sql:ifFound res="B">
    ...
</sql:ifFound>

ifNotFound

Body tag. Executes own body if query's result is empty. Parameters are:

1) res id for results (see setQuery). For example:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
<sql:ifNotFound res="B">
    ...
</sql:ifNotFound>

sortRows

Tag lets you sort rows. Parameters are:

1) res id for results (see setQuery)
2) position index for column. Default value is 1
3) direction Optional parameter. Describes how to order data. Possible values asc and desc. Default value is asc.

getColumn

Reads the given column from the current row. Parameters are:

1) res id for results (see setQuery)
2) position Optional attribute. Describes an index for column. Default value is 1
3) name Optional attribute. Describes a name for column. You can set either name or position.
4) id Optional attribute. Describes a page scope variable for data. Type is java.lang.Object. Without this parameter tag just prints data. For example:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
A=<sql:getColumn position="1" res="B"/>
B=<sql:getColumn position="2" res="B"/>

or

<sql:setQuery id="A" query="select A,B from T" res="B"/>
<sql:getColumn res="B" position="1" id="C"/>
A=<%=C%>

getColumnName

Lets you read a name for the given column from the current row. Parameters are:

1) res id for results (see setQuery)
2) position index for column. Default value is 1
3) id Optional parameter: describes a page scope variable for data. Without this parameter tag just prints data.

getColumnType

Lets you read a type for the given column from the current row. Parameters are:

1) res id for results (see setQuery)
2) position index for column. Default value is 1
3) id Optional parameter: describes a page scope variable for data. Without this parameter tag just prints data.

ifNullColumn

Body tag. Executes own body if column's value from the current row is null. Parameters are:

1) res id for results (see setQuery)
2) position index for column. Default value is 1. For example:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
<sql:ifNullColumn position="1" res="B">
    ...
</sql:ifNullColumn>

ifNotNullColumn

Body tag. Executes own body if column's value from the current row is not null. Parameters are:

1) res id for results (see setQuery)
2) position index for column. Default value is 1. For example:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
<sql:ifNotNullColumn position="1" res="B">
    ...
</sql:ifNotNullColumn>

moveFirst

Changes current row to the first row. Parameters are:

1) res id for results (see setQuery)

moveLast

Changes current row to the last row. Parameters are:

1) res id for results (see setQuery)

moveNext

Changes current row to the next row. Parameters are:

1) res id for results (see setQuery)
2) step Optional parameter. Describes how many records will be passed. Default value is 1 (move to next record).

movePrev

Changes current row to the previous row. Parameters are:

1) res id for results (see setQuery)
2) step Optional parameter. Describes how many records will be passed. Default value is 1 (move to previous record).

forEachRow

Body tag lets you iterate over rows. Tag executes own body for the each selected row. Parameters are:

1) res id for results (see setQuery)
2) from Optional parameter. Describes the first record. Default value is 1 (first record).
3) to Optional parameter. Describes the last record. By default is the last record in recordset.
4) currentRow Optional parameter. Describes a nested variable you can use a reference to the current position (row number).

For example:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
<sql:forEachRow res="B">
   A=<sql:getColumn position="1" res="B"/>
   B=<sql:getColumn position="2" res="B"/>
</sql:forEachRow>

within forEachRow tag you may use getColumn, ifNullColumn, ifNotNullColumn etc. without result's id:
 


<sql:setQuery id="A" query="select A,B from T" res="B"/>
<sql:forEachRow res="B">
   A=<sql:getColumn position="1"/>
   B=<sql:getColumn position="2"/>
</sql:forEachRow>

forEachColumn

Body tag lets you iterate over columns. Tag executes own body for the each column. Parameters are:

1) res id for results (see setQuery). You can omit this parameter if you are using this tag as a part of forEachRow body.
2) from Optional parameter. Describes the first column. Default value is 1 (first column).
3) to Optional parameter. Describes the last column. By default is the last column in the current record.
4) currentColumn Optional parameter. Describes a nested variable you can use a reference to the current position.

within forEachColumn tag you may use getColumn, ifNullColumn, ifNotNullColumn etc. without position parameter. For example:
 


<sql:setQuery id="A" query="select * from T" res="B"/>
<sql:forEachRow res="B">
   <p>
   <sql:forEachColumn>
    <sql:getColumnName/>=<sql:getColumn/>
   </sql:forEachColumn>
</sql:forEachRow>

escape

Parses own body or data parameter and replaces each single quote with a pair of quotes. Parameters are:

1) data Optional parameter. You may pass your data through this parameter.

Cache

You may setup cache for select queries. Selected data will be cached according to your settings. So any another select query uses the same cache may actually read data from memory rather than request database. Tags are:

setCache

Describes cache policy for your request. You may use this tag in the body of setQuery tag. Parameters are:

1) key unique ID (identification) for this cache
2) scope cache scope. Possible values are session or application. Default value is session.
3) refresh cache refresh time (in seconds). Default value is 600. For example:
 


<sql:setQuery id="A" query="select A,B from T" res="B">
   <sql:setCache key="12345" scope="application" refresh="900"/>
</sql:setQuery>

clearCache

Clears cache. Parameters are:

1) key unique ID (identification) for this cache
2) scope cache scope. Possible values are session or application. Default value is session.

ifCached

Body tag. Executes own body if cache exists and data are valid. Parameters are:

1) key unique ID (identification) for this cache
2) scope cache scope. Possible values are session or application. Default value is session.

ifNotCached

Body tag. Executes own body if cache does not exist or data are not valid. Parameters are:

1) key unique ID (identification) for this cache
2) scope cache scope. Possible values are session or application. Default value is session.

for downloading:

Library: dbtag.jar    Description: taglib.tld

© Coldbeans Software      Comments?

See also Coldtags suite - the largest collection of custom JSP tags.

Also in Coldtags: