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 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 (Rs.getstring ("real_name");
Emp.seTempcode (Rs.getstring ("EMP_CODE"));
...
List.Add (EMP);
}
Return List;
}
Finally {
DBUTIL.CLOSE (RS, PST, CONN);
}
// jsp display some code
<%
List Emplist = (list) Request.GetaTribute ("Emplist");
IF (EMPLIST == NULL) Emplist = Collectes.empty_List;
%>
...
"0" width = "90%">
Code
Name
<%
Employee EMP;
FOR
INT i = 0; I
EMP = (EMPLOYEE) Emplist.get (i);
%>
<% = Emp.getempcode ()%>
<% = Emp.getRealName ()%>
<%
}
// end for
%>
Solution 2:
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 data part of the 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");
RS = pst.executeQuery ();
List list = dbutil. ResultSettolist (ResultSet RS);
Return List;
}
Finally {
DBUTIL.CLOSE (RS, PST, CONN);
}
// jsp display some code
<%
List Emplist = (list) Request.GetaTribute ("Emplist");
IF (EMPLIST == NULL) Emplist = Collectes.empty_List;
%>
...
"0" width = "90%">
Code
Name
<%
Map colmap;
FOR
INT i = 0; I
Colmap = (map) Emplist.get (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 part 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 =?";
/ ** 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. EnableLocalDebug can be set by system attribute jdbc.enable_local_debug = true, enable local debugging: Add JVM parameter: -djdbc.enable_local_debug = true * /
/ ** * Find the application server data source to get a database connection from the data 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: *
* # user password database * jdbc.password = tiger * * You can set the above system properties by JVM parameters: * -djdbc.driver = oracle.jdbc.driver.OracleDriver * -djdbc.url = jdbc: Oracle: Thin: @ 10.1.1: 1521: Ocrl * -djdbc .username = scott -djdbc.password = tiger * @Return connection * @throws namingexception If data source lookup failed * @throws SQLException If the database connection failed * / 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 via DriverManager
/ ** * Pack the query result into a list. * List The element type is the MAP of the package, the map key is the field name (uppercase), value is the corresponding field value * @param @ @ keturn list * @Throws java.sql.sqlexception * /
/ ** * Close 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) {
