Common JSP page query display mode (with source code)

Common JSP page query display mode title: Common JSP page query Display Mode

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 a Java class needs to be defined for each query and a lot of additional code is required to encapsulate record data into a Java object.
Sample Code:

1. 
   
2. // Query the data code
3. Connection conn = dbutil. getconnection ();
4. Preparedstatement PST =Null;
5. Resultset rs =Null;
6. Try{
7. StringSQL = "select emp_code, real_name from t_employee where organ_id = ?";
8. Pst = conn. preparedstatement (SQL );
9. Pst. setstring (1, 101 ");
10. Resultset rs = pst.exe cutequery ();
11. List list =NewArraylist ();
12. Employee EMP;
13. While(Rs. Next ()){
14. EMP =NewEmployee ();
15. EMP. setreakname (Rs. getstring ("real_name "));
16. EMP. setempcode (Rs. getstring ("emp_code "));
17. ...
18. List. Add (EMP );
19. }
20. ReturnList;
21. }Finally{
22. Dbutil. Close (RS, Pst, Conn );
23. }
24. 
    
25. 
    
26. // Display part of the JSP code
27. <%
28. List emplist = (list) request. getattribute ("emplist ");
29. If(Emplist =Null) Emplist = collections. empty_list;
30. %>
31. ...
32. <Table cellspacing = "0" width = "90%">
33. <Tr> <TD> Code </TD> <TD> name </TD> </tr>
34. <%
35. Employee EMP;
36. For(IntI = 0; I <emplist. Size (); I ++ ){
37. EMP = (employee) emplist. Get (I );
38. %>
39. <Tr>
40. <TD> <% = EMP. getempcode () %> </TD>
41. <TD> <% = EMP. getrealname () %> </TD>
42. </Tr>
43. <%
44. }// End
45. %>
46. </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:

1. 
   
2. // Code for querying data:
3. ...
4. Connection conn = dbutil. getconnection ();
5. Preparedstatement PST =Null;
6. Resultset rs =Null;
7. Try{
8. StringSQL = "select emp_code, real_name from t_employee where organ_id = ?";
9. Pst = conn. preparedstatement (SQL );
10. Pst. setstring (1, 101 ");
11. Rs = pst.exe cutequery ();
12. List list = dbutil. resultsettolist (resultset RS );
13. ReturnList;
14. }Finally{
15. Dbutil. Close (RS, Pst, Conn );
16. }
17. 
    
18. 
    
19. 
    
20. // Display part of the JSP code
21. <%
22. List emplist = (list) request. getattribute ("emplist ");
23. If(Emplist =Null) Emplist = collections. empty_list;
24. %>
25. ...
26. <Table cellspacing = "0" width = "90%">
27. <Tr> <TD> Code </TD> <TD> name </TD> </tr>
28. <%
29. Map colmap;
30. For(IntI = 0; I <emplist. Size (); I ++ ){
31. Colmap = (MAP) emplist. Get (I );
32. %>
33. <Tr>
34. <TD> <% = colmap. Get ("emp_code") %> </TD>
35. <TD> <% = colmap. Get ("real_name") %> </TD>
36. </Tr>
37. <%
38. }// End
39. %>
40. </Table>

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:

1. // Code for querying data:
2. ImportJavax. SQL.Rowset;
3. ImportOracle. JDBC. rowset. oraclecachedrowset;
4. ...
5. Connection conn = dbutil. getconnection ();
6. Preparedstatement PST =Null;
7. Resultset rs =Null;
8. Try{......
9. StringSQL = "select emp_code, real_name from t_employee where organ_id = ?";
10. Pst = conn. preparedstatement (SQL );
11. Pst. setstring (1, 101 ");
12. Rs = pst.exe cutequery ();
13. Oraclecachedrowset ors = neworaclecachedrowset ();
14. // Encapsulate data in the resultset into the rowset
15. ORS. populate (RS );
16. ReturnORS;
17. }Finally{
18. Dbutil. Close (RS, Pst, Conn );
19. }
20. 
    
21. 
    
22. // Display part of the JSP code
23. <%
24. Javax. SQL.RowsetEmprs = (javax. SQL.Rowset) Request. getattribute ("emprs ");
25. %>
26. ...
27. <Table cellspacing = "0" width = "90%">
28. <Tr> <TD> Code </TD> <TD> name </TD> </tr>
29. <%
30. If(Emprs! =Null)While(Emprs. Next ()){
31. %>
32. <Tr>
33. <TD> <% = emprs. Get ("emp_code") %> </TD>
34. <TD> <% = emprs. Get ("real_name") %> </TD>
35. </Tr>
36. <%
37. }// End while
38. %>
39. </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:

1. ImportJava. util.List;
2. ImportJava. util.Arraylist;
3. ImportJava. util.Map;
4. ImportJava. util.Hashmap;
5. ImportJava. util.Properties;
6. ImportJava. util.Collections;
7. 
   
8. ImportJava. SQL.Connection;
9. ImportJava. SQL.Sqlexception;
10. ImportJava. SQL.Resultset;
11. ImportJava. SQL.Resultsetmetadata;
12. ImportJava. SQL.Statement;
13. ImportJava. SQL.Preparedstatement;
14. 
    
15. ImportJavax. naming.Context;
16. ImportJavax. naming.Initialcontext;
17. ImportJavax. naming.Namingexception;
18. 
    
19. ImportJavax. SQL.Datasource;
20. 
    
21. Public ClassDbutil {
22. 
    
23. Private Static Final StringJdbc_data_source = "Java: COMP/ENV/jdbc/datasource ";
24. 
    
25. /**
26. Enablelocaldebug: whether to debug locally. <Br>
27. If the value is true, if the data source fails to be searched, use drivermanager to establish a connection with the database;
28. If the value is false, only the data source is searched for to establish a database connection.
29. The default value is false. <Br>
30. Enablelocaldebug can be set to true through the system attribute JDBC. enable_local_debug = true to enable local debugging: <br>
31. Added JVM parameter:-djdbc. enable_local_debug = true.
32. */
33. Private Static BooleanEnablelocaldebug =False;
34. 
    
35. Static{
36. Enablelocaldebug =Boolean. Getboolean ("JDBC. enable_local_debug ");
37. }
38. 
    
39. 
    
40. Private Static ContextCTX =Null;
41. Private StaticJavax. SQL.DatasourceDS =Null;
42. 
    
43. 
    
44. Private Static VoidInitdatasource ()Throws Exception{
45. // Put Connection Properties in to a hashtable.
46. 
    
47. 
    
48. If(CTX =Null){
49. CTX =New Initialcontext();
50. }
51. If(DS =Null){
52. DS = (javax. SQL.Datasource) CTX. Lookup (jdbc_data_source );
53. }
54. }
55. 
    
56. /**
57. * Search for the application server data source and obtain the database connection from the data source. <Br>
58. * If the data source fails to be searched during local debugging and enablelocaldebug = true
59. * Use Java. SQL. drivermanager to establish a connection based on system properties. <Br>
60. * The following system attributes can be configured during local debugging: <br>
61. * <P>
62. * # JDBC driver name <br>
63. * JDBC. Driver = <I> oracle. JDBC. Driver. oracledriver </I> <br>
64. * # Database connection string <br>
65. * JDBC. url = <I> JDBC: oracle: thin: @ 10.1.1.1: 1521: ocrl </I> <br>
66. * # Database username <br>
67. * JDBC. Username = <I> Scott </I> <br>
68. * # Database User Password <br>
69. * JDBC. Password = <I> tiger </I> <br>
70. * </P>
71. * The preceding system attributes can be set using JVM parameters: <br>
72. *-Djdbc. Driver = oracle. JDBC. Driver. oracledriver
73. *-Djdbc. url = JDBC: oracle: thin: @ 10.1.1.1: 1521: ocrl
74. *-Djdbc. Username = Scott-djdbc. Password = Tiger
75. * @ Return connection
76. * @ Throws namingexception if data source search fails
77. * @ Throws sqlexception if the database connection fails
78. */
79. Public Static ConnectionGetconnection ()ThrowsSqlexception{
80. Try{
81. Initdatasource ();
82. ReturnDS. getconnection ();
83. }Catch(SqlexceptionSqle ){
84. ThrowSqle;
85. }Catch(ExceptionNe ){
86. If(Enablelocaldebug ){
87. ReturnGettestconn ();
88. }Else{
89. Throw New Runtimeexception(Ne. tostring ());
90. }
91. }
92. }
93. 
    
94. 
    
95. // Establish a local test connection through drivermanager
96. Private Static ConnectionGettestconn (){
97. Try{
98. StringDriver =System. Getproperty ("JDBC. Driver ");
99. System. Out. println ("JDBC. Driver =" + driver );
100. 
     
101. StringUrl =System. Getproperty ("JDBC. url ");
102. System. Out. println ("JDBC. url =" + URL );
103. 
     
104. StringUsername =System. Getproperty ("JDBC. username ");
105. System. Out. println ("JDBC. Username =" + username );
106. 
     
107. StringPassword =System. Getproperty ("JDBC. Password ");
108. System. Out. println ("JDBC. Password =" + password );
109. 
     
110. Class. Forname (driver). newinstance ();
111. ReturnJava. SQL.Drivermanager. Getconnection (URL, username, password );
112. }
113. Catch(ExceptionEx ){
114. Ex. printstacktrace ();
115. Throw New Runtimeexception(EX. getmessage ());
116. }
117. }
118. 
     
119. /**
120. * Encapsulate the query result into a list. <Br>
121. * 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.
122. * @ Param Rs resultset
123. * @ Return list
124. * @ Throws java. SQL. sqlexception
125. */
126. Public Static ListResultsettolist (ResultsetRS)ThrowsJava. SQL.Sqlexception{
127. If(Rs =Null)Return Collections. Empty_list;
128. 
     
129. ResultsetmetadataMD = Rs. getmetadata ();
130. IntColumncount = md. getcolumncount ();
131. 
     
132. ListList =New Arraylist();
133. MapRowdata;
134. While(Rs. Next ()){
135. Rowdata =New Hashmap(Columncount );
136. For(IntI = 1; I <= columncount; I ++ ){
137. Rowdata. Put (Md. getcolumnname (I), RS. GetObject (I ));
138. }
139. List. Add (rowdata );
140. }
141. ReturnList;
142. }
143. 
     
144. /**
145. * Disable resultset, statement, and connection.
146. * @ Param Rs resultset to be closed
147. * @ Param stmt statement or preparedstatement to be closed
148. * @ Param conn connection to be closed
149. */
150. Public Static VoidClose (ResultsetRS,StatementStmt,ConnectionConn ){
151. If(RS! =Null)Try{
152. Rs. Close ();
153. }Catch(Java. SQL.SqlexceptionEx ){
154. Ex. printstacktrace ();
155. }
156. If(Stmt! =Null)Try{
157. Stmt. Close ();
158. }Catch(Java. SQL.SqlexceptionEx ){
159. Ex. printstacktrace ();
160. }
161. If(Conn! =Null)Try{
162. Conn. Close ();
163. }Catch(Java. SQL.SqlexceptionEx ){
164. Ex. printstacktrace ();
165. }
166. }
167. 
     
168. }// End of dbui