| Title: Common display modes for JSP page queries Author: Evan Email: evan_zhao@hotmail.com
Background:
1. List the database query results in JSP
2. In a good J2EE mode, database queries are generally implemented using Dao (Data Access Object). jsp is only used to display data. Problem:
You can use JDBC resultset to obtain the query results (stored in the database buffer). However, after statement and connection are disabled, resultset is unavailable. Therefore, you need to retrieve all the query results and pass them to the JSP page. Solution 1:
Use value object. Encapsulate each record into a JavaBean object and load these objects into a collection file and send them to JSP for display. The disadvantage of this method is that each query needs to define a Java class, and it also requires a lot of additionalCode.
Sample Code: // Query the data code
Connection conn = dbutil. getconnection ();
Preparedstatement PST = NULL;
Resultset rs = NULL;
Try {
String SQL = "select emp_code, real_name from t_employee where organ_id = ?";
Pst = conn. preparedstatement (SQL );
Pst. setstring (1, 101 ");
Resultset rs = pst.exe cutequery ();
List list = New Arraylist ();
Employee EMP;
While (Rs. Next ()){
EMP = New Employee ();
EMP. setreakname (Rs. getstring ("real_name "));
EMP. setempcode (Rs. getstring ("emp_code "));
...
List. Add (EMP );
}
Return List;
} Finally {
Dbutil. Close (RS, Pst, Conn );
} // Display part of the JSP code
<%
List emplist = (list) request. getattribute ("emplist ");
If (Emplist = NULL) emplist = collections. empty_list;
%>
...
<Table cellspacing = "0" width = "90%">
<Tr> <TD> Code </TD> <TD> name </TD> </tr>
<%
Employee EMP;
For ( Int I = 0; I <emplist. Size (); I ++ ){
EMP = (employee) emplist. Get (I );
%>
<Tr>
<TD> <% = EMP. getempcode () %> </TD>
<TD> <% = EMP. getrealname () %> </TD>
</Tr>
<%
} // End
%>
</Table> Solution 2:
Traverse the resultset to retrieve all data and encapsulate it into the collection.
Specific Practices:
1. Generate a list object (list = new arraylist ()).
2. Generate a map object (MAP map = new hashmap ()). Use Map to encapsulate a row of data. The key is the name of each field and the value is the corresponding value. (Map. Put ("user_name"), RS. getstring ("user_name "))
3. Load the map object generated in step 1 into the list object (list. Add (MAP) in step 2 )).
4. Repeat steps 2 and 3 until the resultset traversal is complete.
The preceding procedure is implemented in the dbutil. resultsettolist (resultset RS) method (uppercase is used for all column names. Sample Code: // code used to query data:
...
connection conn = dbutil. getconnection ();
preparedstatement PST = NULL;
resultset rs = NULL;
try {
string SQL = "select emp_code, real_name from t_employee where organ_id =? ";
PST = Conn. preparedstatement (SQL);
PST. setstring (1, "101");
rs = pst.exe cutequery ();
List list = dbutil. resultsettolist (resultset RS);
return list;
}< B> finally {< br> dbutil. close (RS, Pst, Conn);
} // JSP display part of the Code
<%
List emplist = (list) request. getattribute ("emplist");
If (emplist = NULL) emplist = collections. empty_list;
%>
...
for int colmap = (MAP) emplist. get (I);
%>
| Code |
name | / tr> <% map colmap; ( I = 0; I
| <% = colmap. get ("emp_code") %> |
<% = colmap. get ("real_name ") %> |
<% }< I> // end for %>
Solution 3:
Use rowset.
Rowset is an interface provided in jdbc2.0. Oracle implements this interface accordingly. It is useful for Oracle. JDBC. rowset. oraclecachedrowset. Oraclecachedrowset implements all the methods in resultset. However, unlike resultset, data in oraclecachedrowset remains valid after connection is disabled. The Oracle rowset is implemented in the jdbcdownload of http://otn.oracle.com/software/content.html. it is named ocrs12.zip. Sample Code: // Code for querying data:
Import Javax. SQL. rowset;
Import Oracle. JDBC. rowset. oraclecachedrowset;
...
Connection conn = dbutil. getconnection ();
Preparedstatement PST = NULL;
Resultset rs = NULL;
Try {......
String SQL = "select emp_code, real_name from t_employee where organ_id = ?";
Pst = conn. preparedstatement (SQL );
Pst. setstring (1, 101 ");
Rs = pst.exe cutequery ();
Oraclecachedrowset ors = neworaclecachedrowset ();
// Encapsulate data in the resultset into the rowset
ORS. populate (RS );
Return ORS;
} Finally {
Dbutil. Close (RS, Pst, Conn );
} // Display part of the JSP code
<%
Javax. SQL. rowset emprs = (javax. SQL. rowset) request. getattribute ("emprs ");
%>
...
<Table cellspacing = "0" width = "90%">
<Tr> <TD> Code </TD> <TD> name </TD> </tr>
<%
If(Emprs! = NULL)While(Emprs. Next ()){
%>
<Tr>
<TD> <% = emprs. Get ("emp_code") %> </TD>
<TD> <% = emprs. Get ("real_name") %> </TD>
</Tr>
<%
}// End while
%>
</Table> Applicable scenarios:
Method 1: use custom query operations
Method 2 is applicable when multiple query statements or query results need to be processed.
Method 3 is suitable for a single query statement and is suitable for rapid development. Related Links:
For paging display, see JSP paging technology implementation.
If you need to generate word or excel in the query result, see: use JSP to print reports in word or Excel format. Appendix:Dbutil code:
ImportJava. util. List;
ImportJava. util. arraylist;
ImportJava. util. Map;
ImportJava. util. hashmap;
ImportJava. util. properties;
ImportJava. util. collections; ImportJava. SQL. connection;
ImportJava. SQL. sqlexception;
ImportJava. SQL. resultset;
ImportJava. SQL. resultsetmetadata;
ImportJava. SQL. statement;
ImportJava. SQL. preparedstatement; ImportJavax. Naming. context;
ImportJavax. Naming. initialcontext;
ImportJavax. Naming. namingexception; ImportJavax. SQL. datasource; Public ClassDbutil { Private Static FinalString jdbc_data_source = "Java: COMP/ENV/jdbc/datasource "; /**
Enablelocaldebug: whether to debug locally. <Br>
If the value is true, if the data source fails to be searched, use drivermanager to establish a connection with the database;
If the value is false, only the data source is searched for to establish a database connection.
The default value is false. <Br>
Enablelocaldebug can be set to true through the system attribute JDBC. enable_local_debug = true to enable local debugging: <br>
Added JVM parameter:-djdbc. enable_local_debug = true.
*/
Private Static BooleanEnablelocaldebug =False; Static{
Enablelocaldebug = Boolean. getboolean ("JDBC. enable_local_debug ");
} Private StaticContext CTX = NULL;
Private StaticJavax. SQL. datasource DS = NULL; Private Static VoidInitdatasource ()ThrowsException {
// Put Connection Properties in to a hashtable. If(CTX = NULL ){
CTX =NewInitialcontext ();
}
If(DS = NULL ){
DS = (javax. SQL. datasource) CTX. Lookup (jdbc_data_source );
}
} /**
* Search for the application server data source and obtain the database connection from the data source. <Br>
* If the data source fails to be searched during local debugging and enablelocaldebug = true
* Use Java. SQL. drivermanager to establish a connection based on system properties. <Br>
* The following system attributes can be configured during local debugging: <br>
* <P>
* # JDBC driver Program Name <br>
* JDBC. Driver = <I> oracle. JDBC. Driver. oracledriver </I> <br>
* # Database connection string <br>
* JDBC. url = <I> JDBC: oracle: thin: @ 10.1.1.1: 1521: ocrl </I> <br>
* # Database username <br>
* JDBC. Username = <I> Scott </I> <br>
* # Database User Password <br>
* JDBC. Password = <I> tiger </I> <br>
* </P>
* The preceding system attributes can be set using JVM parameters: <br>
*-Djdbc. Driver = oracle. JDBC. Driver. oracledriver
*-Djdbc. url = JDBC: oracle: thin: @ 10.1.1.1: 1521: ocrl
*-Djdbc. Username = Scott-djdbc. Password = Tiger
* @ Return connection
* @ Throws namingexception if data source search fails
* @ Throws sqlexception if the database connection fails
*/
Public Static Connection getconnection () Throws Sqlexception {
Try {
Initdatasource ();
Return DS. getconnection ();
} Catch (Sqlexception sqle ){
Throw Sqle;
} Catch (Exception ne ){
If (Enablelocaldebug ){
Return Gettestconn ();
} Else {
Throw New Runtimeexception (ne. tostring ());
}
}
} // Establish a local test connection through drivermanager
Private StaticConnection gettestconn (){
Try{
String driver = system. getproperty ("JDBC. Driver ");
System. Out. println ("JDBC. Driver =" + driver ); String url = system. getproperty ("JDBC. url ");
System. Out. println ("JDBC. url =" + URL ); String username = system. getproperty ("JDBC. username ");
System. Out. println ("JDBC. Username =" + username ); String Password = system. getproperty ("JDBC. Password ");
System. Out. println ("JDBC. Password =" + password ); Class. forname (driver). newinstance ();
ReturnJava. SQL. drivermanager. getconnection (URL, username, password );
}
Catch(Exception ex ){
Ex. printstacktrace ();
Throw NewRuntimeexception (ex. getmessage ());
}
} /**
* Encapsulate the query result into a list. <Br>
* The element type in the list is the map that encapsulates a row of data. The map key is the field name (uppercase), and the value is the corresponding field value.
* @ Param Rs resultset
* @ Return list
* @ Throws java. SQL. sqlexception
*/
Public StaticList resultsettolist (resultset RS)ThrowsJava. SQL. sqlexception {
If(Rs = NULL)ReturnCollections. empty_list; Resultsetmetadata MD = Rs. getmetadata ();
IntColumncount = md. getcolumncount (); List list = New Arraylist ();
Map rowdata;
While (Rs. Next ()){
Rowdata = New Hashmap (columncount );
For ( Int I = 1; I <= columncount; I ++ ){
Rowdata. Put (Md. getcolumnname (I), RS. GetObject (I ));
}
List. Add (rowdata );
}
Return List;
} /**
* Disable resultset, statement, and connection.
* @ Param Rs resultset to be closed
* @ Param stmt statement or preparedstatement to be closed
* @ Param conn connection to be closed
*/
Public Static Void Close (resultset RS, statement stmt, connection conn ){
If (RS! = NULL) Try {
Rs. Close ();
} Catch (Java. SQL. sqlexception ex ){
Ex. printstacktrace ();
}
If (Stmt! = NULL) Try {
Stmt. Close ();
} Catch (Java. SQL. sqlexception ex ){
Ex. printstacktrace ();
}
If (Conn! = NULL) Try {
Conn. Close ();
} Catch (Java. SQL. sqlexception ex ){
Ex. printstacktrace ();
}
} }// End of dbutil |
