数据库分页无非就是根据条件先查出
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