这篇以Java+Oracle为基础，讲解代码层的实现。
就如平时我们很在分页中看到的，分页的时候返回的不仅包括查询的结果集(List)，而且还包括总的页数(pageNum)、当前第几页(pageNo)等等信息，所以我们封装一个查询结果PageModel类，代码如下：| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 | package kane; import java.util.List; public class PageModel<E> { private List<E> list; private int pageNo; private int pageSize; private int totalNum; private int totalPage; public List<E> getList() { return list; } public void setList(List<E> list) { this .list = list; } public int getPageNo() { return pageNo; } public void setPageNo( int pageNo) { this .pageNo = pageNo; } public int getPageSize() { return pageSize; } public void setPageSize( int pageSize) { this .pageSize = pageSize; } public int getTotalNum() { return totalNum; } public void setTotalNum( int totalNum) { this .totalNum = totalNum; setTotalPage((getTotalNum() % pageSize) == 0 ? (getTotalNum() / pageSize) : (getTotalNum() / pageSize + 1 )); } public int getTotalPage() { return totalPage; } public void setTotalPage( int totalPage) { this .totalPage = totalPage; } // 获取第一页 public int getFirstPage() { return 1 ; } // 获取最后页 public int getLastPage() { return totalPage; } // 获取前页 public int getPrePage() { if (pageNo > 1 ) return pageNo - 1 ; return 1 ; } // 获取后页 public int getBackPage() { if (pageNo < totalPage) return pageNo + 1 ; return totalPage; } // 判断'首页'及‘前页'是否可用 public String isPreable() { if (pageNo == 1 ) return "disabled" ; return "" ; } // 判断'尾页'及‘下页'是否可用 public String isBackable() { if (pageNo == totalPage) return "disabled" ; return "" ; } } |
其中使用泛型是为了能使的该分页类能进行重用，比如在查询用户时可以封装User对象、在查询财务中的流向单时可以封装流向单FlowCard类。
我们以查询用户为例，用户选择查询条件，首先调用Servlet获取查询参数，然后请求业务逻辑层取得分页封装结果类。业务逻辑调用Dao层取得结果集、取得中记录数封装成分页类。最后Servlet将结果设置到jsp页面显示。首先来讲解Servlet，代码如下：| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | package kane; import java.io.*; import java.util.*; import javax.servlet.ServletConfig; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import kane.UserInfo; import kane.UserInfoManage; import kane.PageModel; public class UserBasicSearchServlet extends HttpServlet { private static final long serialVersionUID = 1L; private int pageSize = 0 ; @Override public void init(ServletConfig config) throws ServletException { pageSize = Integer.parseInt(config.getInitParameter( "pageSize" )); } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { doPost(req, resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // 1.取得页面参数并构造参数对象 int pageNo = Integer.parseInt(req.getParameter( "pageNo" )); String sex = req.getParameter( "gender" ); String home = req.getParameter( "newlocation" ); String colleage = req.getParameter( "colleage" ); String comingyear = req.getParameter( "ComingYear" ); UserInfo u = new UserInfo(); u.setSex(sex); u.setHome(home); u.setColleage(colleage); u.setCy(comingyear); // 2.调用业务逻辑取得结果集 UserInfoManage userInfoManage = new UserInfoManage(); PageModel<UserInfo> pagination = userInfoManage.userBasicSearch(u, pageNo, pageSize); List<UserInfo> userList = pagination.getList(); // 3.封装返回结果 StringBuffer resultXML = new StringBuffer(); try { resultXML.append( "<?xml version='1.0' encoding='gb18030'?>/n" ); resultXML.append( "<root>/n" ); for (Iterator<UserInfo> iterator = userList.iterator(); iterator .hasNext();) { UserInfo userInfo = iterator.next(); resultXML.append( "<data>/n" ); resultXML.append( "/t<id>" + userInfo.getId() + "</id>/n" ); resultXML.append( "/t<truename>" + userInfo.getTruename() + "</ truename >/n" ); resultXML.append( "/t<sex>" + userInfo.getSex() + "</sex>/n" ); resultXML.append( "/t<home>" + userInfo.getHome() + "</home>/n" ); resultXML.append( "</data>/n" ); } resultXML.append( "<pagination>/n" ); resultXML.append( "/t<total>" + pagination.getTotalPage() + "</total>/n" ); resultXML.append( "/t<start>" + pagination.getFirstPage() + "</start>/n" ); resultXML.append( "/t<end>" + pagination.getLastPage() + "</end>/n" ); resultXML.append( "/t<pageno>" + pagination.getPageNo() + "</pageno>/n" ); resultXML.append( "</pagination>/n" ); resultXML.append( "</root>/n" ); } catch (Exception e) { e.printStackTrace(); } writeResponse(req, resp, resultXML.toString()); } public void writeResponse(HttpServletRequest request, HttpServletResponse response, String result) throws IOException { response.setContentType( "text/xml" ); response.setHeader( "Cache-Control" , "no-cache" ); response.setHeader( "Content-Type" , "text/xml; charset=gb18030" ); PrintWriter pw = response.getWriter(); pw.write(result); pw.close(); } } |
其中User对象代码如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | package kane; import java.util.Date; public class UserInfo { private int id; private String username; private String password; private String truename; private String sex; private Date birthday; private String home; private String colleage; private String comingYear; public int getId() { return id; } public void setId( int id) { this .id = id; } public String getUsername() { return username; } public void setUsername(String username) { this .username = username; } public String getPassword() { return password; } public void setPassword(String password) { this .password = password; } public String getTruename() { return truename; } public void setTruename(String truename) { this .truename = truename; } public String getSex() { return sex; } public void setSex(String sex) { this .sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this .birthday = birthday; } public String getHome() { return home; } public void setHome(String home) { this .home = home; } public String getColleage() { return colleage; } public void setColleage(String colleage) { this .colleage = colleage; } public String getCy() { return comingYear; } public void setCy(String cy) { this . comingYear= cy; } } |
接着是业务逻辑层代码，代码如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | package kane; import java.sql.Connection; import kane.DBUtility; import kane.PageModel; public class UserInfoManage { private UserInfoDao userInfoDao = null ; public UserInfoManage () { userInfoDao = new UserInfoDao(); } public PageModel<UserInfo> userBasicSearch(UserInfo u, int pageNo, int pageSize) throws Exception { Connection connection = null ; PageModel<UserInfo> pagination = new PageModel<UserInfo>(); try { connection = DBUtility.getConnection(); DBUtility.setAutoCommit(connection, false ); pagination.setList(userInfoDao.getUserList(u, pageNo, pageSize)); pagination.setPageNo(pageNo); pagination.setPageSize(pageSize); pagination.setTotalNum(userInfoDao.getTotalNum(u)); DBUtility.commit(connection); } catch (Exception e) { DBUtility.rollBack(connection); e.printStackTrace(); throw new Exception(); } finally { DBUtility.closeConnection(); } return pagination; } } |
其中DBUtility为数据库的连接封装类。
最后是Dao层代码实现，代码如下：| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | package kane; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import kane.UserInfo; import kane.DBUtility; public class UserInfoDao { public List<UserInfo> getUserList(UserInfo userInfo, int pageNo, int pageSize) throws Exception { PreparedStatement pstmt = null ; ResultSet rs = null ; List<UserInfo> userList = null ; try { String sql = "select * from(select rownum num,u.* from(select * from user_info where sex = ? and home like '" + userInfo.getHome() + "%" + "' and colleage like '" + userInfo.getColleage() + "%" + "' and comingyear like '" + userInfo.getCy() + "%" + "' order by id) u where rownum<=?) where num>=?" ; userList = new ArrayList<UserInfo>(); Connection conn = DBUtility.getConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString( 1 , userInfo.getSex()); pstmt.setInt( 2 , pageNo * pageSize); pstmt.setInt( 3 , (pageNo - 1 ) * pageSize + 1 ); rs = pstmt.executeQuery(); while (rs.next()) { UserInfo user = new UserInfo(); user.setId(rs.getInt( "id" )); user.setTruename(rs.getString( "truename" )); user.setSex(rs.getString( "sex" )); user.setHome(rs.getString( "home" )); userList.add(user); } } catch (SQLException e) { e.printStackTrace(); throw new Exception(e); } finally { DBUtility.closeResultSet(rs); DBUtility.closePreparedStatement(pstmt); } return userList; } public int getTotalNum(UserInfo userInfo) throws Exception { PreparedStatement pstmt = null ; ResultSet rs = null ; int count = 0 ; try { String sql = "select count(*) from user_info where sex=? and home like '" + userInfo.getHome() + "%" + "' and colleage like '" + userInfo.getColleage() + "%" + "' and comingyear like '" + userInfo.getCy()+ "%" + "'" ; Connection conn = DBUtility.getConnection(); pstmt = conn.prepareStatement(sql); pstmt.setString( 1 , userInfo.getSex()); rs = pstmt.executeQuery(); if (rs.next()) { count = rs.getInt( 1 ); } } catch (SQLException e) { e.printStackTrace(); throw new Exception(e); } finally { DBUtility.closeResultSet(rs); DBUtility.closePreparedStatement(pstmt); } return count; } } |
最后就是servlet将得到的结果返回给jsp页面显示出来。
注：其中DBUtility代码是封装数据库连接操作的代码，如下：| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | package kane; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DBUtility { private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>(); public static Connection getConnection() { Connection conn = null ; conn = threadLocal.get(); if (conn == null ) { try { Class.forName( "oracle.jdbc.driver.OracleDriver" ); conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:oracle" , "admin" , "admin" ); threadLocal.set(conn); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } return conn; } // 封装设置Connection自动提交 public static void setAutoCommit(Connection conn, Boolean flag) { try { conn.setAutoCommit(flag); } catch (SQLException e) { e.printStackTrace(); } } // 设置事务提交 public static void commit(Connection conn) { try { conn.commit(); } catch (SQLException e) { e.printStackTrace(); } } // 封装设置Connection回滚 public static void rollBack(Connection conn) { try { conn.rollback(); } catch (SQLException e) { e.printStackTrace(); } } // 封装关闭Connection、PreparedStatement、ResultSet的函数 public static void closeConnection() { Connection conn = threadLocal.get(); try { if (conn != null ) { conn.close(); conn = null ; threadLocal.remove(); } } catch (SQLException e) { e.printStackTrace(); } } public static void closePreparedStatement(PreparedStatement pstmt) { try { if (pstmt != null ) { pstmt.close(); pstmt = null ; } } catch (SQLException e) { e.printStackTrace(); } } public static void closeResultSet(ResultSet rs) { try { if (rs != null ) { rs.close(); rs = null ; } } catch (SQLException e) { e.printStackTrace(); } } } |
使用ThreadLocal是为了保证事务的一致，使得同一个线程的所有数据库操作使用同一个Connection。
到此一个简单的代码实现就完成了。