Analysis of Common Query and Display Methods on jsp Page

This paper describes the common query and display methods of jsp pages with examples. Share it for your reference, as follows:

Background:

1. The database query results need to be displayed as a list in JSP
2. In a good J2EE mode, database query 1 is generally implemented with DAO (Data Access Object), and JSP is only used to display data

Method 1:

Build a class, encapsulate the query results into this class, and then add this class object to List. This is also the method I used at the beginning, which is not universal and too troublesome.

Method 2:

When introducing method 2, let's first look at how to convert ResultSet into List. The code is as follows:

private static List resultSetToList(ResultSet rs) throws SQLException {
 List list = new ArrayList();
 ResultSetMetaData md = rs.getMetaData();
 int columnCount = md.getColumnCount();
 while (rs.next()) {
  Map rowData = new HashMap();
  for (int i = 1; i <= columnCount; i++) {
  rowData.put(md.getColumnName(i), rs.getObject(i));
  }
  list.add(rowData);
 }
 return list;
}

Traversing ResultSet takes out all data and encapsulates it into Collection.

Specific practices:

1. Generate an List object (List list = new ArrayList ()).
2. Generate an Map object (Map map = new HashMap ()). Encapsulate 1 row of data with Map, key for each field name and value for the corresponding value. (map. put ("USER_NAME"), rs. getString ("USER_NAME"))
3. Load the Map object generated in step 2 into the list object in step 1 (list. add (map)).
4. Repeat steps 2 and 3 until ResultSet is traversed

The above procedure is implemented in the DBUtil. resultSetToList (ResultSet rs) method (all column names are capitalized) and can be used for reference.

Sample code:

// Query data part 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 Show part of the code 
<%
List empList = (List)request.getAttribute("empList");
if (empList == null) empList = Collections.EMPTY_LIST;
%>
 … 
<table cellspacing="0" width="90%">
<tr> <td> Code </td> <td> Name </td> </tr>
<%
Map colMap;
for (int i=0; i< empList.size(); i++){
colMap = (Map) empList.get(i);
%>
<tr>
<td><%=colMap.get("EMP_CODE")%></td>
<td><%=colMap.get("REAL_NAME")%></td>
</tr>
<%
}// end for
%>
</table>

Solution 3:

Use RowSet.

RowSet is an interface provided in JDBC 2.0, which is implemented by Oracle, of which oracle. jdbc. rowset. OracleCachedRowSet is very useful. OracleCachedRowSet implements all the methods in ResultSet, but unlike ResultSet, the data in OracleCachedRowSet is still valid after Connection is turned off.

The rowset implementation of oracle is available in the jdbc download of http://otn.oracle.com/software/content. html under the name ocrs12. zip

Sample code:

// Query data part 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();

I hope this article is helpful to everyone's jsp programming.