JSP page query display common mode Title: JSP page query display common mode Author: evan email: maioto: evan_zhao@hotmail.com background: 1. The database query results need to be displayed in the JSP in a list mode. In a good J2EE mode, database query generally use DAO implementation (Data Access Object), JSP is only used to display data issues: You can get query results (existing in the database buffer) through JDBC ResultSet, but after Statement, Connection is closed ResultSet is not available. 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 =; pst = conn.preparedStatement "select emp_code, real_name from t_employee where organ_id =?" (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 part code <% List Emplist = (list) Request.getaTribute ("Emplist"); if (Emplist == Null) Emplist = Collectes.empty_List;%> ...
Code name <% Employee Emp; for (int i = 0; i
<% = Emp.geTempcode ()%> <% = Emp.getRealName ()%> <%} // end for%>
Workaround 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 Step until the resultSet traversed in the DBUTIL. ResultSettolist (RSET RS) method (all listed names), refer to the use. Sample Code: // query the data portion of the code: ... Connection conn = DBUtil.getConnection (); PreparedStatement pst = null; ResultSet rs = null; try {String sql =; pst = "select emp_code, real_name from t_employee where organ_id =?" 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.GetaTRibute ("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: Using 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, the name is OCRS12.ZIP sample code: // Query data section 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 = neworaclecacheDrowSet (); // Package the data in RowSt in RowSt (RS); Return {DBUTIL.CLOSE (RS, PST, Conn);} // jsp display part code <% javax.sql.rowset EMPRS = (javax.sql.rowset) Request.getaTRibute ("EMPRS");% > ... Code Name <% IF (EMPRS! = Null) While (EMPRS.NEXT ()) {%>
<% = EMPRS.GET ("EMP_CODE")%> <% = EMPRS.GET ("Real_name")%> <%} // end while%>
Applicable occasions: Method One is used in a customized query operation method II for a plurality of query statements or needs to process the query results. Method 3 is suitable for single query statements for rapid development. Related links: If you need a page display, please refer to: JSP Patement Technology Implementation If the query results need to generate Word or Excel, please refer to: Using JSP implementation Word, Excel format report print: 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. If the value is true when the value of True fails, use DriverManager to establish a connection with the database; if False is only looking for a data source to establish a database connection. The default is false.
EnableLocalDebug can be set by system attribute jdbc.enable_local_debug = true, enable local debugging:
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 voidinitdataroup () throws exception {// put connections Properties in to a hashtable. if (ctx == null) {ctx = new initialContext ();} if (ds == null) {ds = (Javax .sql.datasource) CTX.lookup (JDBC_DATA_SOURCE);}} / ** * Find the application server data source to get database connections from the data source.
* If you find the data source fails when debugging and enablelocaldebug == True * is established using java.sql.driverManager according to system properties.
* The system properties configurable during local debugging are as follows:
*
* #JDBC driver name * jdbc.driver = oracle.jdbc.driver.Oracledriver * # database connection string * jdbc.url = jdbc: Oracle: Thin: @ 10.1.1.1: 1521: OCRL * # database username * JDBC. Username = scott * # database user password * 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.1: 1521: ocrl * -Djdbc.username = scott -Djdbc.password = tiger * @return connection * @throws NamingException If the data source lookup fails * @throws SQLException if a database established 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 ());}}} // established a local test connection pastestConn () {string driver = system.getProperty); system.out.println ("JDBC.DRIVER"); system.out.println ("JDBC) (" JDBC.DrInet); system.out.println .driver = " driver; string url = system.getProperty (" jdbc.ur "); system.out.println (" jdbc.ur = " url; string username = system.g EtProperty ("jdbc.username"); system.out.println ("jdbc.username =" username); string password = system.getProperty ("jdbc.password"); system.out.println ("jdbc.password = " password); Class.forName (driver) .newInstance (); return java.sql.DriverManager.getConnection (url, userName, password);} catch (Exception ex) {ex.printStackTrace (); throw new RuntimeException (ex .getMessage ());}} / ** * Pack the query result into a list.
