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
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
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
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.
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.
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)
<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"/>
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)
getColumn Reads the given column from the current row. Parameters are: 1) res id for results (see setQuery)
<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"/>
getColumnName Lets you read a name for the given column from the current row. Parameters are: 1) res id for results (see setQuery)
getColumnType Lets you read a type for the given column from the current row. Parameters are: 1) res id for results (see setQuery)
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)
<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)
<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)
movePrev Changes current row to the previous row. Parameters are: 1) res id for results (see setQuery)
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)
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.
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
<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
ifCached Body tag. Executes own body if cache exists and data are valid. Parameters are: 1) key unique ID (identification) for this cache
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
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:
|