ssh和ssm基础代码总结

ssh

spring,struts2,hibernate

1.分页

action

	public String getCustomByPage() {
		if (custom == null) {
			custom = new VipCustom();
		}
		
		if (custom.getRegionId() == null) {
			// 1为控制单元
			custom.setRegionId(1);
		}
		if(custom.getSex() == null) {
			// 0 代表全部
			custom.setSex(0);
		}
		pageBean.setPageNo(startPage);
		pageBean.setPageSize(limitPage);
		try {
			pageBean = customService.getCustomByPage(custom, otherParams, pageBean);
		} catch (Exception e) {
			logger.error("分页查询VIP客户信息异常", e);
			if(e.getMessage().startsWith("msg:")) {
				msg = e.getMessage().split(":")[1];
			} else {
				msg = "数据库异常";
			}
		}
		this.operPage = "/modules/vip/user/custom-grid.jsp";
		return DISPATCHER;
	}

service

使用的原生sql,比较灵活,查好后,需要一个一个拼装

	public PageBean getCustomByPage(VipCustom custom, OtherParams otherParams, PageBean pageBean) throws Exception {
		PageBean result = customDao.getCustomByPage(custom, otherParams, pageBean);
		List<?> customList = result.getResult();
		List<VipCustomWrapper> wrapList = new ArrayList<VipCustomWrapper>();
		if (customList != null && customList.size() > 0) {
			for (int i = 0; i < customList.size(); i++) {
				Object[] data = (Object[]) customList.get(i);
				VipCustomWrapper wrap = new VipCustomWrapper();
				wrap.setId(data[0] != null ? Integer.parseInt(String.valueOf(data[0])) : 0);
				wrap.setStrName(data[1] != null ? String.valueOf(data[1]) : "");
				wrap.setIdCardNo(data[2] != null ? String.valueOf(data[2]) : "");
				wrap.setSex(data[3] != null ? Integer.parseInt(String.valueOf(data[3])) : 0);
				wrap.setBirthday(data[4] != null ? String.valueOf(data[4]) : "");
				wrap.setPhone(data[5] != null ? String.valueOf(data[5]) : "");
				wrap.setRegionId(data[6] != null ? Integer.parseInt(String.valueOf(data[6])) : 0);
				wrap.setRegionName(data[7] != null ? String.valueOf(data[7]) : "");
				wrap.setManagerId(data[8] != null ? Integer.parseInt(String.valueOf(data[8])) : 0);
				wrap.setManagerName(data[9] != null ? String.valueOf(data[9]) : "");
				wrap.setLevel(data[10] != null ? Integer.parseInt(String.valueOf(data[10])) : 0);
				wrap.setImageUrl1(data[11] != null ? String.valueOf(data[11]) : null);
				wrap.setImageUrl2(data[13] != null ? String.valueOf(data[13]) : null);
				wrap.setPersonType(data[15] != null ? Integer.parseInt(String.valueOf(data[15])) : 0);
				wrap.setDeptName(data[16] != null ? String.valueOf(data[16]) : null);
				wrapList.add(wrap);
			}
		}
		result.setResult(wrapList);
		return result;
	}

dao

public PageBean getCustomByPage(VipCustom custom, OtherParams otherParams, PageBean pageBean) {
		String countSql = " from vip_custom where type=0";
		String sql = " from vip_custom c inner join region r on c.regionId=r.id left join vip_manager m on c.managerId=m.id where c.type=0";
		// 用于传参
		List<Object> params = new ArrayList<Object>();
		if (custom != null) {
			if (custom.getRegionId() != null && custom.getRegionId() > 1) {
				// 排查regionid为0 和1的情况 1为控制单元
				sql += " and c.regionId=?";
				countSql += " and regionId=?";
				params.add(custom.getRegionId());
			}
			if (StringUtils.isNotBlank(custom.getStrName())) {
				sql += " and c.strName like '%" + custom.getStrName() + "%'";
				countSql += " and strName like '%" + custom.getStrName() + "%'";
			}
			if (StringUtils.isNotBlank(custom.getIdCardNo())) {
				sql += " and c.idCardNo like '%" + custom.getIdCardNo() + "%'";
				countSql += " and idCardNo like '%" + custom.getIdCardNo() + "%'";
			}
			if (custom.getPersonType() != null && custom.getPersonType() != 0) {
				sql += " and c.person_type=?";
				countSql += " and person_type=?";
				params.add(custom.getPersonType());
			}
			if (StringUtils.isNotBlank(custom.getDeptName())) {
				sql += " and c.dept_name like '%" + custom.getDeptName() + "%'";
				countSql += " and dept_name like '%" + custom.getDeptName() + "%'";
			}
			if (StringUtils.isNotBlank(custom.getPhone())) {
				sql += " and c.phone like '%" + custom.getPhone() + "%'";
				countSql += " and phone like '%" + custom.getPhone() + "%'";
			}
			// 0代表全部
			if (custom.getSex() != null && custom.getSex() != 0) {
				sql += " and c.sex=?";
				countSql += " and sex=?";
				params.add(custom.getSex());
			}
			if (custom.getManagerId() != null && custom.getManagerId() != 0) {
				sql += " and c.managerId=?";
				countSql += " and managerId=?";
				params.add(custom.getManagerId());
			}
			if (custom.getLevel() != null && custom.getLevel() != 0) {
				sql += " and c.level=?";
				countSql += " and level=?";
				params.add(custom.getLevel());
			}
		}
		if (otherParams != null) {
			if (StringUtils.isNotBlank(otherParams.getStartTime())) {
				sql += " and c.birthday >= '" + otherParams.getStartTime() + "'";
				countSql += " and birthday >= '" + otherParams.getStartTime() + "'";
			}
			if (StringUtils.isNotBlank(otherParams.getEndTime())) {
				sql += " and c.birthday <= '" + otherParams.getEndTime() + "'";
				countSql += " and birthday <= '" + otherParams.getEndTime() + "'";
			}
		}
		String querySql = "select c.id,c.strName,c.idCardNo,c.sex,date_format(c.birthday,'%Y年%m月%d日'),c.phone,c.regionId,ifnull(r.strName, '') as regionName,"
				+ "c.managerId, ifnull(m.strName, '') as managerName,c.level,c.imageUrl1,c.imageSource1,c.imageUrl2,c.imageSource2,c.person_type,c.dept_name" + sql;
		querySql += " order by c.id desc";
		PageBean result = null;
		if (params.size() > 0) {
			result = this.getPageBySql(countSql, querySql, pageBean, params);
		} else {
			result = this.getPageBySql(countSql, querySql, pageBean);
		}
		return result;
	}

 这里,先查出总数count,然后根据当前页数和一页条数来setFirstResult(设置开始位置)和setMaxResults(页条数)

	public PageBean getPageBySql(String countString, String queryString, PageBean pageBean, List<Object> params) throws DataAccessException {
		countString = "select * " + countString;
		// 查询获取总记录数
		long totalCount = countSQLQueryResult(countString, params);
		if (totalCount < 1 || pageBean == null) {// 返回空页对象
			return new PageBean();
		} else {
			int pageTotal = (int)Math.ceil((double)totalCount / pageBean.getPageSize());
			if (pageBean.getPageNo() > pageTotal - 1) {
				pageBean.setPageNo(pageTotal - 1);
			}
			Query query = createSQLQuery(queryString, params);
			query.setFirstResult(pageBean.getPageNo() * pageBean.getPageSize());// 设置开始位置
			query.setMaxResults(pageBean.getPageSize());
			List<?> list = query.list();
			pageBean.setTotal(totalCount);
			pageBean.setResult(list);;
		}
		return pageBean;
	}

	public long countSQLQueryResult(String sql, List<Object> values) {
		Object countNum = createSQLQuery(buidSqlPage(sql), values).uniqueResult();
		if (countNum == null) {
			return 0L;
		}
		long totalCount = ((Number)countNum).longValue();
		if (totalCount < 1) {
			return 0L;
		}
		return totalCount;
	}

计算count比较麻烦,可以优化 

	private String buidSqlPage(String sql) {
		Matcher matcher = IS_REMOVE_SELECT_PATTERN.matcher(sql);
		if (matcher.matches()) {
			return " select count(*) as count from (" + removeOrders(sql) + ") c";
		}
		return " select count(*) as count " + removeSelect(removeOrders(sql));
	}

优化后的纯原生sql实现分页

hibernate 原生sql  不支持 limit ?,?   所以采用 limit 1,8 这样直接拼接sql的方式

    @Test
    public void testPage() {
        Page page = new Page();
        page.setPageNo(1);
        page.setPageSize(8);
        VipCustomQo vipCustomQo = new VipCustomQo();
        vipCustomQo.setIdCardNo("00");
        try {
            page = customService.getCustomPageByvipCustom(vipCustomQo, page);
            System.out.println(page);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

	@Override
	public Page getCustomPageByvipCustom(VipCustomQo vipCustomQo, Page page) {
		Page result = customDao.getCustomPage(vipCustomQo, page);
		return result;
	}

	@Override
	public Page getCustomPage(VipCustomQo vipCustomQo, Page page) {
		String fromSql = " from vip_custom v where 1= 1 ";
		String selectSql = " select v.id,v.strName,v.idCardNo,v.regionId,v.dept_name ";
		String countSql = " select count(v.id) ";
		// 用于传参
		List<Object> params = new ArrayList<Object>();
		if (vipCustomQo != null) {
			if (StringUtils.isNotBlank(vipCustomQo.getStrName())) {
				fromSql += " and v.strName = ?";
				params.add(vipCustomQo.getStrName());
			}
			if (StringUtils.isNotBlank(vipCustomQo.getIdCardNo())) {
				fromSql += " and v.idCardNo like '%" + vipCustomQo.getIdCardNo() + "%'";
			}
		}
		countSql += fromSql;
		selectSql+=fromSql;
		selectSql+=" order by id desc " ;

		Integer total = getTotal(countSql, params);
		if(total == 0){
			return new Page();
		}
		page.setTotal(total);
		page.setPageTotal(total);

		if(page.getPageNo()>page.getPageTotal()){
			throw new RuntimeException("页数超过");
		}

		page.setList(getPageList(page, selectSql));
		page.setList(fillList(page));
		return page;
	}


	/**
	 * 获取查询总数
	 * @param countSql
	 * @param params
	 * @return
	 */
	private Integer getTotal(String countSql, List<Object> params) {
		Integer total = 0;
		List<?> bySql = null;
		if(params.size()>0){
			bySql = findBySql(countSql, params);
		}else{
			bySql = findBySql(countSql);
		}
		if(CollectionUtils.isEmpty(bySql)){
			total = 0;
		}else{
			BigInteger bigInteger = (BigInteger) bySql.get(0);
			total = bigInteger.intValue();
		}
		return total;
	}

	private List<VipCustomVo> fillList(Page page) {
		List<VipCustomVo> list = new ArrayList<>();
		for (Object o : page.getList()) {
			Object[] objArray = (Object[]) o;
			VipCustomVo vipCustomVo = new VipCustomVo();
			vipCustomVo.setId((Integer) objArray[0]);
			vipCustomVo.setStrName((String) objArray[1]);
			vipCustomVo.setIdCardNo((String) objArray[2]);
			vipCustomVo.setRegionId((Integer) objArray[3]);
			vipCustomVo.setDeptName((String) objArray[4]);
			list.add(vipCustomVo);
		}
		return list;
	}

	private List<?> getPageList(Page page, String selectSql) {
		int beginIndex = (page.getPageNo() - 1) * page.getPageSize();
		selectSql += " limit " + beginIndex + "," + page.getPageSize();
		return findBySql(selectSql);
	}

3个pojo类

public class Page {
    private int pageNo = 1;//当前页码
    private int pageTotal;//总页码
    private int total;//总条数
    private int pageSize = 8;//每页显示条数
    private List<?> list;//返回的数据集合

    @Override
    public String toString() {
        return "Page{" +
                "pageNo=" + pageNo +
                ", pageTotal=" + pageTotal +
                ", total=" + total +
                ", pageSize=" + pageSize +
                ", list=" + list +
                '}';
    }

    public int getPageNo() {
        return pageNo;
    }

    public Page setPageNo(int pageNo) {
        this.pageNo = pageNo;
        return this;
    }

    public int getPageTotal() {
        return pageTotal;
    }

    public Page setPageTotal(int total) {
        this.total = total;
        pageTotal = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
        return this;
    }

    public int getTotal() {
        return total;
    }

    public Page setTotal(int total) {
        this.total = total;
        return this;
    }

    public int getPageSize() {
        return pageSize;
    }

    public Page setPageSize(int pageSize) {
        this.pageSize = pageSize;
        return this;
    }

    public List<?> getList() {
        return list;
    }

    public Page setList(List<?> list) {
        this.list = list;
        return this;
    }
}



public class VipCustomQo {
    private String strName;
    private String idCardNo;

    @Override
    public String toString() {
        return "VipCustomQo{" +
                "strName='" + strName + '\'' +
                ", idCardNo='" + idCardNo + '\'' +
                '}';
    }

    public String getStrName() {
        return strName;
    }

    public VipCustomQo setStrName(String strName) {
        this.strName = strName;
        return this;
    }

    public String getIdCardNo() {
        return idCardNo;
    }

    public VipCustomQo setIdCardNo(String idCardNo) {
        this.idCardNo = idCardNo;
        return this;
    }
}

public class VipCustomVo {
    private Integer id;
    private Integer regionId;
    private String strName;
    private String idCardNo;
    private String deptName;

    @Override
    public String toString() {
        return "VipCustomVo{" +
                "id=" + id +
                ", regionId=" + regionId +
                ", strName='" + strName + '\'' +
                ", idCardNo='" + idCardNo + '\'' +
                ", deptName='" + deptName + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public VipCustomVo setId(Integer id) {
        this.id = id;
        return this;
    }

    public Integer getRegionId() {
        return regionId;
    }

    public VipCustomVo setRegionId(Integer regionId) {
        this.regionId = regionId;
        return this;
    }

    public String getStrName() {
        return strName;
    }

    public VipCustomVo setStrName(String strName) {
        this.strName = strName;
        return this;
    }

    public String getIdCardNo() {
        return idCardNo;
    }

    public VipCustomVo setIdCardNo(String idCardNo) {
        this.idCardNo = idCardNo;
        return this;
    }

    public String getDeptName() {
        return deptName;
    }

    public VipCustomVo setDeptName(String deptName) {
        this.deptName = deptName;
        return this;
    }
}