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;%> ...
code
Name <% Employee Emp; for (int i = 0; i
<% = Emp.GeteMpcode ()%>
<% = Emp.getRealName ()%> < / t D> <%} // end for%>
Solution 2:
Traversing ResultSet Removes all data packages into the collection.
Details: 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 RESULTSET in the ROWSET (RS); return {dbutil.close (RS, PST, CONN);} // jsp Display partial 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.
