Jstl SQL label Library

The SQL tag library, as its name implies, provides database operations, including database connection, query, modification, and transactions. You only need to provide the corresponding attribute values to complete database operations, which is easier than using a large number of scriptlet in JSP to operate the database.
Taglib of the SQL tag Library: <% @ taglib uri = "http://java.sun.com/jsp/jstl/ SQL" prefix = "SQL" %>

<SQL: setdatasource/>

All operations on the jstl SQL tag are performed through data source, that is, the database connection is obtained based on the javax. SQL. datasource interface.
You can use the <SQL: setdatasource/> label to obtain the javax. SQL. datasource object.
Syntax structure:
XML/html code
<SQL: setdatasource
{
Datasource = "datasource" | url = "jdbcurl"
[Driver = "driverclassname"]
[User = "username"]
[Password = "password"]
}
[Var = "varname"]
[Scope = "{page | request | session | application}"]
/>

Datasource attribute: defines the data source. It can be a string or javax. SQL. datasource. If it is a string type, it can be the relative path of the jdni resource.
When the dtasource attribute is used, the four attributes of driver, URL, user, and password are ignored, and the resources specified by the datasource attribute (such as the content of the JNDI configuration) are the primary attributes.
For example, if you are using a JNDI resource, open context. XML in the conf directory of Tomcat. You cannot create a new one. Enter the following content:

XML/html code
<XML version = '1. 0' encoding = 'utf-8'>
<Context>
<Watchedresource> WEB-INF/Web. xml </watchedresource>
<Resource Name = "JDBC/MySQL"
Type = "javax. SQL. datasource"
Driverclassname = "com. MySQL. JDBC. Driver"
Maxidle = "5"
Maxwait = "5000"
Username = "root"
Password = "198910"
Url = "JDBC: mysql: // localhost: 3306/product"
Maxactive = "10"
/>
</Context>
The Resource Node defines the datasource attributes. When the server is started, the information is automatically loaded.

Common attribute functions of Resource Nodes:

Driverclassname-name of the loaded database Driver Class. Here I use the MySQL database. If it is an sqlserver database, it is "com. Microsoft. sqlserver. JDBC. sqlserverdriver"
Username-User Name
Password-Password
URL-URL. MySQL database is used here. product is the name of the database to be connected.
Initialsize-Number of connections created during thread pool initialization. The default value is 0.
Maxactive-Maximum number of connections allowed at the same time in the thread pool. The default value is 8.
Minidle-the minimum number of idle connections allowed in the thread pool at the same time. The default value is 0.
Maxidle-Maximum number of idle connections allowed in the thread pool at the same time. The default value is 8.
Maxwait-Maximum number of milliseconds to wait for available connection before the thread pool throws an exception when no connection is available. The default value is-1 (infinitely large ).
For more information about the attributes of the resource node and the configuration of the JNDI resource, see [JNDI resourse how-to] In the Tomcat document ].

After configuration, the JSP page code is:

XML/html code
<% @ Page Language = "Java" contenttype = "text/html; charset = UTF-8" %>
<% @ Taglib uri = "http://java.sun.com/jsp/jstl/ SQL" prefix = "SQL" %>
<SQL: setdatasource datasource = "JDBC/MySQL" Var = "datasource"/>
$ {Datasource}
The datasource attribute value corresponds to the name attribute in the <resource> node in context. XML, and the VaR attribute stores the obtained datasource object. Use the El expression to display this object. The output content is:

Org. Apache. tomcat. DBCP. DBCP. basicdatasource @ 94e858

Datasource in Tomcat is based on the Apache DBCP project by default.

Driver, URL, user, and passowrd attributes: Create a New datasource object.

VaR property: Save the generated datasource object. Reference this object in other SQL labels to obtain the corresponding datasource and connection.

Scope attribute: the scope of var.

The following is an example of using four JDBC parameters:

XML/html code
<SQL: setdatasource Var = "datasource"
Driver = "com. MySQL. JDBC. Driver"
User = "root"
Password = "10000"
Url = "JDBC: mysql: // localhost: 3306/product"
/>
$ {Datasource}
The output object is:

Org. Apache. taglibs. Standard. Tag. Common. SQL. performancewrapper @ f8a000

<SQL: Query/>

XML/html code
Syntax 1: Without tags

<SQL: Query SQL = "sqlquery"

Var = "varname"

[Scope = "{page | request | session | application}"]

[Datasource = "datasource"]

[Maxrows = "maxrows"]

[Startrow = "startrow"]

/>

Syntax 2: With label bodies, Param sub-tags

<SQL: Query SQL = "sqlquery"

Var = "varname"

[Scope = "{page | request | session | application}"]

[Datasource = "datasource"]

[Maxrows = "maxrows"]

[Startrow = "startrow"]>

<SQL: param> actions

</SQL: Query>

Syntax 3: defines the query statement in the TAG body. Optional Param labels

<SQL: Query Var = "varname"

[Scope = "{page | request | session | application}"]

[Datasource = "datasource"]

[Maxrows = "maxrows"]

[Startrow = "startrow"]>

Query

Optional <SQL: param> actions

</SQL: Query>

 

 

SQL attribute: SQL statement to be executed for syntax1 and 2. Syntax3 can be written in the TAG body.

The returned results are kept in the VaR attribute. The VaR attribute is required and its type is javax. servlet. jsp. jstl. SQL. Result interface instance. The following objects are available in the result interface object:

 

Attribute description
Rows sortedmap objects in a row. Each object corresponds to a column name and a single row in the structure set.
Rowsbyindex array in a row, each corresponding to a single row in the result set
Columnnames uses the same sequence as the rowsbyindex attribute for the column names in the result set in a row.
Rowcount
Limitedbymaxrows: If the query is restricted by maxrows, the attribute is true.

 

XML/html code
<% @ Page Language = "Java" contenttype = "text/html; charset = UTF-8" %>
<% @ Taglib uri = "http://java.sun.com/jsp/jstl/ SQL" prefix = "SQL" %>
<% @ Taglib uri = "http://java.sun.com/jsp/jstl/core" prefix = "C" %>
<SQL: setdatasource Var = "datasource"
Driver = "com. MySQL. JDBC. Driver"
User = "root"
Password = "10000"
Url = "JDBC: mysql: // localhost: 3306/product"
/>
<SQL: Query Var = "Rs" datasource = "$ {datasource}">
Select * From usertable
</SQL: Query>
<Table>
<Tr class = "title">
<TD> id </TD>
<TD> name </TD>
<TD> password </TD>
</Tr>
<C: foreach Var = "row" items = "$ {Rs. Rows}">
<Tr>
<TD >$ {row. ID} </TD>
<TD >$ {row. uname} </TD>
<TD >$ {row. upassword} </TD>
</Tr>
</C: foreach>
</Table>
Use <SQL: setdatasource/> to obtain the datasource object, save it in the datasource object, and run <SQL: Query/> to save the result to the RE object, and then loop through the content of each row, output the user ID, name, and password.

This is just a simple query operation without parameters. If there is a where condition after the SELECT statement, you can use the placeholder: Question mark (?) in the appropriate position (?), Declare the <SQL: Param/> sub-tag in the TAG body, corresponding to the value of each placeholder.

In the preceding example, the <SQL: Query/> label is changed:

 

XML/html code
<SQL: Query Var = "Rs" datasource = "$ {datasource}">
Select * From usertable where id =?
<SQL: Param value = "1"/>
</SQL: Query>
If there are multiple placeholders, the values of the param sub-tag are assigned to each placeholder in order of their appearance in the TAG body.

Note: if there are n placeholders, there are n Param sub-tags. If the number of non-matched tags is exceeded, an exception is thrown.

If the query result is empty, an empty result interface object is returned.

Datasource attribute: The datasource required for the query operation. It can be a datasource object, that is, the VaR object declared in <SQL: setdatasource/>, or the relative path string of A jdni resource.

If the datasource property value is null, A jspexception is thrown.

The datasource object generated using <SQL: setdatasource/> can be found in the preceding example.

Use the relative path of the jdni resource as the datasource property value. First configure the JNDI resource, and then set the datasource property value to the path of the JNDI resource, such as "JDBC/MySQL". For more information, see the preceding section.

Maxrows attribute: Maximum number of rows in the query result. If it is undefined or set to-1, no limit is imposed on the number of rows. The number of rows in the result set is determined.

It is equivalent to the top (n) function in MSSQLServer, and the limit clause in MySQL, which is used to limit the number of returned rows.

Maxrows must be> =-1.

Startrows attribute: the data contained in the returned result object starts from the row. The index of the first row is 0. If the startrows attribute value is 1, The result set starts from the second row. If this attribute is not defined, the index starts from 0 by default, that is, the first row of the result set.

VaR attribute: Keep the query result. It is an instance of the javax. servlet. jsp. jstl. SQL. Result interface. For details, see the SQL attribute.

Scope attribute: the scope of var.

<SQL: update/>

 

Execute insert, update, and delete operations.

Syntax structure:

XML/html code
Syntax 1: Without tags

<SQL: Update SQL = "sqlupdate"

[Datasource = "datasource"]

[Var = "varname"]

[Scope = "{page | request | session | application}"]

/>

Syntax 2: contains the <SQL: param> sub-tag in the TAG body.

<SQL: Update SQL = "sqlupdate"

[Datasource = "datasource"]

[Var = "varname"]

[Scope = "{page | request | session | application}"]>

<SQL: param> sub-tag

</SQL: Update>

Syntax 3: Contains execution statements and <SQL: param> subtags in the label body.

<SQL: update [datasource = "datasource"]

[Var = "varname"]

[Scope = "{page | request | session | application}"]>

SQL statement to be executed update statement

Optional <SQL: param> sub-labels

</SQL: Update>

SQL attribute: the SQL statement to be executed. Syntax3 can be directly written in the TAG body without declaring this attribute. If the execution statement requires parameters, use the <SQL: Param/> sub-label in the label body to declare the placeholder value in the execution statement. The usage is similar to <SQL: Query/>.

Datasource attribute: datasource required to run an SQL statement. You can use the datasource object returned by the <SQL: setdatasource/> label or directly use the relative path of the JNDI data source. If the attribute value is null, an exception is thrown. For more information, see the preceding section.

VaR attribute: the number of affected rows after the SQL statement is executed. The type is Java. Lang. integer.

Scope attribute: the scope of var. If the scope attribute is set, the VaR attribute must also be declared.

<SQL: Transaction/>

Implement transaction management for the <SQL: Query/> and <SQL: update/> labels.

Syntax structure:

XML/html code
<SQL: transaction [datasource = "datasource"]

[Isolation = isolationlevel]>

<SQL: Query> and <SQL: Update> statements

</SQL: Transaction>

Isolationlevel: = "read_committed"

| "Read_uncommitted"

| "Repeatable_read"

| "Serializable"

 

 

Datasource property: the relative path of the JNDI data source or the datasource object returned in the <SQL: setdatasource/> label. The <SQL: Query/> and <SQL: update/> values in the TAG body cannot use the datasource attribute. Otherwise, an exception is thrown. If the datasource attribute is null, an exception is thrown.

Isolation attribute: transaction type. Optional types are as follows (see java. SQL. Connection class in Java se6 ):

XML/html code
Read_commited: prevents transactions from reading rows with uncommitted changes.

Read_uncommited. This level allows the row changed by a transaction to be read ("Dirty read") by another transaction before all changes to the row have been committed "). If all changes are rolled back, the second transaction gets invalid rows.

Repatable_read: This level prohibits the transaction from reading rows with uncommitted changes. It also prohibits this situation: one transaction reads a row while the other transaction changes the row and the first transaction reads the row again, and get different values for the second read ("repeated read is not allowed ").

Serializable. This level includes the items that are prohibited in transaction_repeatable_read. This is also prohibited: A transaction reads all rows that meet the where condition, and another transaction inserts a row that meets the where condition, the first transaction re-reads the rows that meet the same conditions and obtains additional "virtual" rows during the second read.

The <SQL: Query/> and <SQL: update/> labels in the TAG body. If one operation has an exception, all other operations will not take effect.

Similar to the connection. Commit () method and the connection. rollback () method, commit is used if all operations are successful. Otherwise, rollback is used.

<SQL: Param/>

 

Set the placeholder part (?) in an SQL statement (?) Value, common types such as string, Int, used in the <SQL: Query/> or <SQL: update/> label body.

Syntax structure:

XML/html code
Syntax 1: declare a value using the Value Attribute

<SQL: Param value = "value"/>

Syntax 2: declare a value in the TAG body

<SQL: param>

Parameter Value

</SQL: param>

There is only one value attribute and the value to be set. If the value is null, The placeholder value of the SQL statement is also null.

<SQL: dataparam/>

 

<SQL: Param/> you can set basic types such as string and Int. <sq: dataparam/> you can set data of the date type.

Syntax structure:

<SQL: dateparam value = "value" [type = "{timestamp | time | date}"]/>

 

Value Attribute: Java. util. date type. You can use usebean to declare a date object.

XML/html code
<JSP: usebean id = "date" class = "Java. util. Date"/>
<SQL: Query Var = "Rs">
Select * From usertable where date =?
<SQL: dataparam value = "$ {date}"/>
</SQL: Query>