1. Need to display the database query results in the JSP in list mode
2. In a good J2EE mode, database queries are generally used with DAO implementation (Data Access Object), JSP is only used to display data
problem:
The query result (existing in the database buffer) is available through JDBC ResultSet, but RESULTSET is not available after Statement, the Connection is closed. Therefore, there is a need to take out all query results and pass to the JSP page.
Solution 1:
Use value object. Each record is encapsulated into a JavaBean object, and these objects are loaded into the Collection to the JSP display. The disadvantage of this method is that each query needs to define a Java Class and a lot of additional code is also required when the recorded data is encapsulated into a Java object.
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.executeQuery (); list list = new arraylist (); Employee Emp; While (rs.next ()) {EMP = New Employee (); EMP. SetReakName ("REAL_NAME"); Emp.seTempcode (rs.getstring ("EMP_CODE")); ... list.add (EMP);} return list;} finally {dbutil.close (RS, PST, Conn } // JSP display part code <% List Emplist = (list) Request.getaTribute ("Emplist"); if (Emplist == NULL) Emplist = collections.empty_list;%> ...
Traversing ResultSet Removes all data packages into the collection.
specific methods:
1. Generate a List object (List list = new arraylist ()). 2. Generate an MAP object (Map Map = New HashMap ()). Using the MAP packaged line data, Key is the value of each field name, Value is the corresponding value. (Map.Put ("User_Name"), RS.GetString ("User_name"))
3. Load the MAP object generated in step 2 into the List object of step 1 (List.Add (Map)).
4. Repeat 2, 3 steps until the resultset traverses
The above process is implemented in the DBUTIL. ResultSettolist (RSET RS) method (all column names are capitalized), which can be referred to.
Sample code:
// query the data portion of the code: "? Select emp_code, real_name from t_employee where organ_id =" ... Connection conn = DBUtil.getConnection (); PreparedStatement pst = null;; ResultSet rs = null try {String sql =; pst = conn.preparedStatement (SQL); Pst.setString (1, "101"); RS = pst.executeQuery (); list = dbutil. ResultSettolist (ResultSet RS); Return List;} finally {dbutil.close (RS, PST, CONN) } // JSP display part code <% List Emplist = (list) Request.getattribute ("Emplist"); if (Emplist == NULL) Emplist = collections.empty_list;%> ...
code
Name <% map colmap; for (int i = 0; i
<% = colmap.get ("EMP_CODE")%>
<% = colmap.get ("Real_name ")%> <%} // end for%>
Solution 3:
Use RowSet.
RowSet is the interface provided in JDBC2.0, and Oracle has a corresponding implementation of the interface, which is useful for oracle.jdbc.rowset.OracleCachedRowSet. OracleCachedRowSet implements all methods in the ResultSet, but with the resultSet is that the data in OracleCachedRowSet is still valid after the Connection is shut down.
Oracle's RowSet is implemented in http://otn.oracle.com/software/content.html, name is Ocrs12.zip
Sample code: // query data portion of the code: 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.executeQuery (); OracleCachedRowSet ors = newOracleCachedRowSet () / / Package the data in the ROWSET in the ROWSET (RS); return {dbutil.close (RS, PST, CONN);} // jsp Display Some code <% javax.sql.rowset EMPRS = (javax.sql.rowset) Request.GetaTribute ("EMPRS");%> ...
<% = EMPRS.GET ("Real_name")%> <%} // end while%>
Applications:
Method 1 User uses to operate
Method II is suitable for a plurality of query statements or a case where the query results are required.
Method 3 is suitable for single query statements for rapid development.
Related Links:
Please refer to if you need a page display:
JSP page technology implementation
If the result of the query needs to generate Word or Excel, please refer to:
Implement Word, Excel format report printing with JSP
Attachment:
DBUTIL code:
import java.util.List; import java.util.ArrayList; import java.util.Map; import java.util.HashMap; import java.util.Properties; import java.util.Collections; import java.sql.Connection; import java.sql.SQLException; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; import java.sql.PreparedStatement; import javax.naming.Context; import javax.naming.InitialContext; import javax .naming.namingexception; import javax.sql.datasource; public class dbutil {private static final string jdbc_data_source = "java: comp / env / jdbc / datasource"; / ** enablelocaldebug: Whether it is commissioned locally. Value When true is true If the DRIVERMANAGER is used to establish a connection using DRIVERMANAGER, using DRIVERMANAGER to establish a database connection if False is found. The default is false. may be provided by the system property = true jdbc.enable_local_debug enableLocalDebug is true, the local debug enable: increase JVM parameter: -Djdbc.enable_local_debug = true * / private static boolean enableLocalDebug = false; static {enableLocalDebug = Boolean.getBoolean ( "jdbc.enable_local_debug");} private static Context ctx = null; private static javax.sql.DataSource ds = null; private static void initDataSource () throws Exception {// Put connection properties in to a hashtable if (ctx =. = null) {CTX = new initialContext ();} if (ds == null) {ds = (javax.sql.datasource) ctx.lookup (jdbc_data_source);}} / ** * Find Application Server Data Source, from Data A database connection is obtained in the source.
* If you look at the local debugging data source fails and enableLocalDebug == true * connection is established using java.sql.DriverManager The system attributes.
* The system properties configurable during local debugging are as follows: *
