Struts+jdbc+分页 实例

   根据项目里分页实例，带有注解。

 

  1 package org.tarena.netctoss.dao.impl;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.util.ArrayList;
  7 import java.util.List;
  8 
  9 import org.tarena.netctoss.dao.AccountDAO;
 10 import org.tarena.netctoss.pojo.Account;
 11 import org.tarena.netctoss.util.DBUtil;
 12 
 13 public class AccountDAOImpl implements AccountDAO {
 14 
 15     public List<Account> findByParameters(
 16             int page,int pageSize,
 17             String idCartNo, String realName,
 18             String loginName, String status) throws Exception {
 19         StringBuffer sql = new StringBuffer(
 20                 "select * from ACCOUNT ");
 21         //判断是否为全部
 22         if("-1".equals(status)){
 23             sql.append(" where 1=1 ");
 24         }else{
 25             sql.append(" where STATUS = ? ");
 26         }
 27         // 判断是否追加身份证条件
 28         if (idCartNo != null && !"".equals(idCartNo)) {
 29             sql.append(" and IDCARD_NO like ? ");
 30         }
 31         // 判断是否追加姓名条件
 32         if (realName != null && !"".equals(realName)) {
 33             sql.append(" and REAL_NAME like ? ");
 34         }
 35         // 判断是否追加登录名条件
 36         if (loginName != null && !"".equals(loginName)) {
 37             sql.append(" and LOGIN_NAME like ? ");
 38         }
 39 //        System.out.println(sql);
 40         //追加分页查询的嵌套
 41         StringBuffer pageSQL = new StringBuffer("");
 42         pageSQL.append("select * from ");
 43         pageSQL.append(" ( ");
 44         pageSQL.append(" select a.*,rownum rn ");
 45         pageSQL.append(" from ( ");
 46         pageSQL.append(sql);//sql查询语句
 47         pageSQL.append(" ) a ");
 48         pageSQL.append(" where rownum<=? ");
 49         pageSQL.append(" ) ");
 50         pageSQL.append(" where rn>=? ");
 51         System.out.println(pageSQL);
 52         try {
 53             // 执行sql查询
 54             Connection con = DBUtil.openConnection();
 55             PreparedStatement pst = con.prepareStatement(pageSQL.toString());
 56             // 设置查询参数
 57             List<Object> params = new ArrayList<Object>();
 58             //判断是否追加状态参数
 59             if(!"-1".equals(status)){
 60                 params.add(status);
 61             }
 62 //            判断是否追加身份证参数
 63             if (idCartNo != null && !"".equals(idCartNo)) {
 64                 params.add("%"+idCartNo+"%");
 65             }
 66             // 判断是否追加姓名条件
 67             if (realName != null && !"".equals(realName)) {
 68                 params.add("%"+realName+"%");
 69             }
 70             // 判断是否追加登录名条件
 71             if (loginName != null && !"".equals(loginName)) {
 72                 params.add("%"+loginName+"%");
 73             } 
 74             //追加分页查询参数
 75             int begin = (page-1)*pageSize+1;
 76             int end = page*pageSize;
 77             params.add(end);//结束索引
 78             params.add(begin);//开始索引
 79             System.out.println(params);
 80             // 循环将params集合参数给sql语句设置
 81             for (int i = 0; i < params.size(); i++) {
 82                 pst.setObject(i + 1, params.get(i));
 83             }
 84             // 执行查询
 85             ResultSet rs = pst.executeQuery();
 86             List<Account> list = new ArrayList<Account>();
 87             while (rs.next()) {
 88                 Account account = new Account();
 89                 account.setId(rs.getInt("ID"));
 90                 account.setIdCardNo(rs.getString("IDCARD_NO"));
 91                 account.setRealName(rs.getString("REAL_NAME"));
 92                 account.setLoginName(rs.getString("LOGIN_NAME"));
 93                 account.setStatus(rs.getString("STATUS"));
 94                 list.add(account);
 95             }
 96             return list;
 97         } catch (Exception ex) {
 98             ex.printStackTrace();
 99             throw ex;
100         } finally {
101             DBUtil.closeConnection();
102         }
103     }
104 
105     public static void main(String[] args) throws Exception{
106         AccountDAO dao = new AccountDAOImpl();
107         List<Account> list = dao.findByParameters(
108                 2,3,null, null, null, "1");
109         for(Account account : list){
110             System.out.println(account.getId()+" "+account.getIdCardNo()+" "+account.getRealName());
111         }
112     }
113     
114 }

 

转载于:https://www.cnblogs.com/jiawan/p/3299160.html