数据库分页一个比较好的写法

 

 

  数据库分页无非就是根据条件先查出

 

  1.有多少条符合条件的记录

  2.根据起始位置查出需要的记录

 

 

ssh 集成的环境中使用 JdbcDaoSupport 完成分页，以下的一种写法比较优雅 ：

 

 

一个dao的代码片段

 

package cn.com.xinli.tdm.dao.impl.cust;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.annotation.Transactional;

import cn.com.xinli.tdm.core.Page;
import cn.com.xinli.tdm.dao.cust.IXxCustDao;
import cn.com.xinli.tdm.repository.cust.Cust;

@Transactional(rollbackFor = Exception.class)
public class XxCustDao extends JdbcDaoSupport implements IXxCustDao {
	private StringBuffer cond=new StringBuffer();
	private Collection<Object> para=new ArrayList<Object>();
	public List<Cust> list(Page page) {
		String sql ="SELECT * FROM(SELECT A.*,ROWNUM RN FROM("
				+ "select * from xx_cust where 1=1"+cond.toString()+" order by display_order"
				+ ") A WHERE ROWNUM<="+page.getEnd()+")WHERE RN>="+page.getStart();
		System.out.println("sql:"+sql);
		return (List<Cust>)getJdbcTemplate().query(sql,para.toArray(),new CustRowMapper());
	}
	public int listCount(Page page) {
		String sql = "select count(1) from xx_cust where 1=1"+cond.toString();
		return getJdbcTemplate().queryForInt(sql,para.toArray());
	}
	public boolean repeat(Cust cust) {
		String sql = "select count(1) from xx_cust where login_name=?";
		return getJdbcTemplate().queryForInt(sql,new Object[]{cust.getLoginName()})>0;
	}
	public void add(Cust cust) {
		cust.setCustId(null);
		String sql = "insert into xx_cust(cust_name,login_name,cust_type,email,sex,address,office_tel,person_tel,if_linkman,display_order,if_use,idcard,passport_type,passport_nbr,duty,is_legal) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		getJdbcTemplate().update(sql,new CustPstmtSetter(cust));
		Integer id=getJdbcTemplate().queryForInt("select max(cust_id) from xx_cust");
		String sqlRole="insert into xx_cust_to_organrize(organization_id,cust_id) values(?,?)";
		getJdbcTemplate().update(sqlRole,new Object[]{cust.getOrgId(),id});
	}

	public void edit(Cust cust) {
		String sql = "update xx_cust set cust_name=?,login_name=?,cust_type=?,email=?,sex=?,address=?,office_tel=?,person_tel=?,if_linkman=?,display_order=?,if_use=?,idcard=?,passport_type=?,passport_nbr=?,duty=?,is_legal=? where cust_id=?";
		getJdbcTemplate().update(sql,new CustPstmtSetter(cust));
	}

	public void del(Cust cust) {
		String dd="delete from xx_cust_to_organrize where cust_id=?";
		getJdbcTemplate().update(dd,new Object[]{cust.getCustId()});
		String sql = "delete from xx_cust where cust_id=?";
		getJdbcTemplate().update(sql,new Object[]{cust.getCustId()});
	}

	public Cust view(Cust cust) {
		String sql = "select * from xx_cust where cust_id=?";
		return (Cust) getJdbcTemplate().queryForObject(sql,
				new Object[] {cust.getCustId()}, 
				new CustRowMapper());
	}
	public void make(Page page){
		Cust cust=(Cust)page.getConditions();
		cond.delete(0,cond.length());
		para.clear();
		if(cust.getCustType()!=null && !"".equals(cust.getCustType())){
			cond.append(" and cust_type=?");
			para.add(cust.getCustType());
		}
		if(cust.getOrgId()!=null && cust.getOrgId()>0){
			cond.append("  and cust_id in(select cust_id from xx_cust_to_organrize where organization_id=?)");
			para.add(cust.getOrgId());
		}
		if(cust.getLoginName()!=null && !"".equals(cust.getLoginName())){
			cond.append(" and login_name=?");
			para.add(cust.getLoginName());
		}
		if(cust.getIdcard()!=null && !"".equals(cust.getIdcard())){
			cond.append(" and idcard=?");
			para.add(cust.getIdcard());
		}
		if(cust.getCustName()!=null && !"".equals(cust.getCustName())){
			cond.append(" and cust_name like ?");
			para.add("%"+cust.getCustName()+"%");
		}
		if(cust.getPersonTel()!=null && !"".equals(cust.getPersonTel())){
			cond.append(" and person_tel=?");
			para.add(cust.getPersonTel());
		}
		if(cust.getOfficeTel()!=null && !"".equals(cust.getOfficeTel())){
			cond.append(" and OFFICE_TEL=?");
			para.add(cust.getOfficeTel());
		}
		if(cust.getPassPortNumber()!=null && !"".equals(cust.getPassPortNumber())){
		cond.append(" and PASSPORT_NBR=?");
		para.add(cust.getPassPortNumber());
		}
	}

	private class CustRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			Cust cust = new Cust();
			cust.setCustName(rs.getString("cust_name"));
			cust.setLoginName(rs.getString("login_name"));
			cust.setCustType(rs.getString("cust_type"));
			cust.setEmail(rs.getString("email"));
			cust.setSex(rs.getString("sex"));
			cust.setAddress(rs.getString("address"));
			cust.setOfficeTel(rs.getString("office_tel"));
			cust.setPersonTel(rs.getString("person_tel"));
			cust.setIfLinkMan(rs.getString("if_linkman"));
			cust.setPwAvail(rs.getDate("pw_avail"));
			cust.setDisplayOrder(rs.getInt("display_order"));
			cust.setIfUse(rs.getString("if_use"));
			cust.setPassPortType(rs.getString("passport_type"));
			cust.setPassPortNumber(rs.getString("passport_nbr"));
			cust.setDuty(rs.getString("duty"));
			cust.setIsLegal(rs.getString("is_legal"));
			cust.setIdcard(rs.getString("idcard"));
			cust.setCustId(rs.getLong("cust_id"));
			return cust;
		}
	}
	private class CustPstmtSetter implements PreparedStatementSetter {
		Cust cust;
		public CustPstmtSetter(Cust cust) {
			this.cust=cust;
		}
		public void setValues(PreparedStatement ps) throws SQLException {
		ps.setString(1,cust.getCustName());
		ps.setString(2,cust.getLoginName());
		ps.setString(3,cust.getCustType());
		ps.setString(4,cust.getEmail());
		ps.setString(5,cust.getSex());
		ps.setString(6,cust.getAddress());
		ps.setString(7,cust.getOfficeTel());
		ps.setString(8,cust.getPersonTel());
		ps.setString(9,cust.getIfLinkMan());
		ps.setInt(10,cust.getDisplayOrder());
		ps.setString(11,cust.getIfUse());
		ps.setString(12,cust.getIdcard());
		ps.setString(13,cust.getPassPortType());
		ps.setString(14,cust.getPassPortNumber());
		ps.setString(15,cust.getDuty());
		ps.setString(16,cust.getIsLegal());
		if(cust.getCustId()!=null)
			ps.setLong(17,cust.getCustId());
		}
	}
}

  

 

备注：

 

 1.在拼sql 的时候使用了 where 1=1 这个永远成立子查询，这样是为了让后面的 条件有规律和蒸汽

 2.使用了 ArrayList 的toArray 方法 把一个list转化为一个 数组

 

import java.util.ArrayList;
import java.util.Collection;


public class TestToArray
{
	public static void main(String[] args) 
	{
		Collection<Object> list=new ArrayList<Object>();
		list.add("aa");
		list.add("bb");
		list.add("cc");
		Object [] arr=list.toArray();
		for(Object str:arr)
		{
			System.out.println("str:"+str);
		}
	}
}

 

 打印：

str:aa
str:bb
str:cc