Java分页操作2 SQL_JAVA_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > JAVA > Java分页操作2 SQL

Java分页操作2 SQL

 2015/3/30 9:23:07  sophyly  程序员俱乐部  我要评论(0)
  • 摘要:SQL语句:/***<p>Method:queryMzjInfo*<p>Description:民政局信息查询**@parammzjInfo*@parampageNum*@parampageSize*@return*/@SuppressWarnings("unchecked")publicList<FrkMzjMbstjbxx>queryMzjInfo(finalMzjQueryConditionPoJomzjInfo,finalintpageNum
  • 标签:Java SQL 操作
SQL语句:
class="java" name="code">/**
	 * <p>Method :queryMzjInfo
	 * <p>Description : 民政局信息查询
	 *
	 * @param mzjInfo
	 * @param pageNum
	 * @param pageSize
	 * @return 
	 */
	@SuppressWarnings("unchecked")
	public  List<FrkMzjMbstjbxx> queryMzjInfo(final MzjQueryConditionPoJo mzjInfo, final int 

pageNum, final int pageSize) {
		return (List<FrkMzjMbstjbxx>)this.getHibernateTemplate().executeFind(new 

HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException, 

SQLException {
				StringBuffer queryHQL = new StringBuffer();
				queryHQL.append("SELECT " +
						"T.MZFRJBQKID, T.DWBH, T.ZZJGDM, T.DJSJ, T.DJBM, 

T.BMDJZH, T.BMJHM, T.DWMC, T.FDDBRXM, " +
						"(SELECT MAX(CODE_NAME) FROM C##ZCK_HXK.UP_CODELIST 

WHERE CODE_VALUE = T.ZJLX AND CODETYPE = 'CB16') AS ZZLXNAME, " +
						"T.ZJHM, T.DZ, T.YB, T.DH, T.HYFL, T.MZFL, T.FZJG, 

T.MJZZFZRS, T.CYRS, T.ZLSHGZSRS, T.SHGZSRS, T.ZXKJZD, T.GDZCYJ, T.BNSRHJ, T.BNFYHJ, T.YWZGBM, 

T.KBZJ, " +
						"(SELECT MAX(CODE_NAME) FROM C##ZCK_HXK.UP_CODELIST 

WHERE CODE_VALUE = T.SFJLDZZ AND CODETYPE = 'CEX01') AS SFJLDZZNAME, " +
						"T.NJSJ,T.ZJLX, T.SFJLDZZ,T.DNSFNJ, " +
						"(SELECT MAX(CODE_NAME) FROM C##ZCK_HXK.UP_CODELIST 

WHERE CODE_VALUE = T.DNSFNJ AND CODETYPE = 'CEX01') AS DNSFNJNAME " +
						"FROM FRK_MZJ_MBSTJBXX T,FRK_JCXX T1 WHERE 

(T1.ZZJGDM = T.ZZJGDM OR T1.JGMC = T.DWMC) ");
				List<Object> params = new ArrayList<Object>();
				if(mzjInfo != null)
				{
					//组织机构代码
					if(StringUtils.isNotBlank(mzjInfo.getZzjgdm())){
						queryHQL.append(" AND T.ZZJGDM = ? ");
						params.add(mzjInfo.getZzjgdm());
					}
					//单位名称
					if(StringUtils.isNotBlank(mzjInfo.getDwmc())){
						queryHQL.append(" AND T.DWMC LIKE ? ");
						params.add("%" + mzjInfo.getDwmc() + "%");
					}
					//登记时间自
					if(mzjInfo.getDjsjfrom()!=null){
						queryHQL.append(" AND T.DJSJ >= ? ");
						params.add(mzjInfo.getDjsjfrom());
					}
					//登记时间至
					if(mzjInfo.getDjsjto()!=null){
						queryHQL.append(" AND T.DJSJ <= ? ");
						params.add(mzjInfo.getDjsjto());
					}
					//登记部门
					if(StringUtils.isNotBlank(mzjInfo.getDjbm())){
						queryHQL.append(" AND T.DJBM LIKE ? ");
						params.add("%" + mzjInfo.getDjbm() + "%");
					}
					//部门交换码
					if(StringUtils.isNotBlank(mzjInfo.getBmjhm())){
						queryHQL.append(" AND T.BMJHM = ? ");
						params.add(mzjInfo.getBmjhm());
					}
				}
				queryHQL.append(" ORDER BY T.DJSJ DESC,T.MZFRJBQKID DESC");  
				Query query = ((SQLQuery) session.createSQLQuery(queryHQL.toString

())).
						addScalar("mzfrjbqkid", Hibernate.STRING).
						addScalar("dwbh", Hibernate.STRING).
						addScalar("zzjgdm", Hibernate.STRING).
						addScalar("djsj", Hibernate.DATE).
						addScalar("djbm", Hibernate.STRING).
						addScalar("bmdjzh", Hibernate.STRING).
						addScalar("bmjhm", Hibernate.STRING).
						addScalar("dwmc", Hibernate.STRING).
						addScalar("fddbrxm", Hibernate.STRING).
						addScalar("zjlx", Hibernate.STRING).
						addScalar("zjhm", Hibernate.STRING).
						addScalar("dz", Hibernate.STRING).
						addScalar("yb", Hibernate.STRING).
						addScalar("dh", Hibernate.STRING).
						addScalar("hyfl", Hibernate.STRING).
						addScalar("mzfl", Hibernate.STRING).
						addScalar("fzjg", Hibernate.LONG).
						addScalar("mjzzfzrs", Hibernate.INTEGER).
						addScalar("cyrs", Hibernate.INTEGER).
						addScalar("zlshgzsrs", Hibernate.INTEGER).
						addScalar("shgzsrs", Hibernate.INTEGER).
						addScalar("zxkjzd", Hibernate.STRING).
						addScalar("gdzcyj", Hibernate.BIG_DECIMAL).
						addScalar("bnsrhj", Hibernate.BIG_DECIMAL).
						addScalar("bnfyhj", Hibernate.BIG_DECIMAL).
						addScalar("ywzgbm", Hibernate.STRING).
						addScalar("kbzj", Hibernate.BIG_DECIMAL).
						addScalar("sfjldzz", Hibernate.STRING).
						addScalar("njsj", Hibernate.DATE).
						addScalar("dnsfnj", Hibernate.STRING).
						addScalar("zzlxname", Hibernate.STRING).
						addScalar("sfjldzzname", Hibernate.STRING).
						addScalar("dnsfnjname", Hibernate.STRING).
						setResultTransformer(Transformers.aliasToBean

(FrkMzjMbstjbxx.class));
				 
				if(params!=null){
                    for(int i=0;i<params.size();i++){
                       query.setParameter(i, params.get(i));
                    }
               }
               
               if(pageSize>0){
                   query.setMaxResults(pageSize);
                   query.setFirstResult((pageNum-1)*pageSize);
               }
               
               return query.list();
			}
		});	
	}

/**
	 * <p>Method :queryMzjInfoCount
	 * <p>Description : 民政局信息查询记录数
	 *
	 * @param mzjInfo
	 * @return 
	 */
	public Integer queryMzjInfoCount(final MzjQueryConditionPoJo mzjInfo){
		return (Integer)this.getHibernateTemplate().execute(new HibernateCallback() {
            public Integer doInHibernate(Session session)throws HibernateException, SQLException {
            	StringBuffer queryHQL = new StringBuffer();
				queryHQL.append("SELECT COUNT(*) FROM FRK_MZJ_MBSTJBXX T,FRK_JCXX 

T1 WHERE (T1.ZZJGDM = T.ZZJGDM OR T1.JGMC = T.DWMC) ");
				List<Object> params = new ArrayList<Object>();
				if(mzjInfo != null)
				{
					//组织机构代码
					if(StringUtils.isNotBlank(mzjInfo.getZzjgdm())){
						queryHQL.append(" AND T.ZZJGDM = ? ");
						params.add(mzjInfo.getZzjgdm());
					}
					//单位名称
					if(StringUtils.isNotBlank(mzjInfo.getDwmc())){
						queryHQL.append(" AND T.DWMC LIKE ? ");
						params.add("%" + mzjInfo.getDwmc() + "%");
					}
					//登记时间自
					if(mzjInfo.getDjsjfrom()!=null){
						queryHQL.append(" AND T.DJSJ >= ? ");
						params.add(mzjInfo.getDjsjfrom());
					}
					//登记时间至
					if(mzjInfo.getDjsjto()!=null){
						queryHQL.append(" AND T.DJSJ <= ? ");
						params.add(mzjInfo.getDjsjto());
					}
					//登记部门
					if(StringUtils.isNotBlank(mzjInfo.getDjbm())){
						queryHQL.append(" AND T.DJBM LIKE ? ");
						params.add("%" + mzjInfo.getDjbm() + "%");
					}
					//部门交换码
					if(StringUtils.isNotBlank(mzjInfo.getBmjhm())){
						queryHQL.append(" AND T.BMJHM  = ? ");
						params.add(mzjInfo.getBmjhm());
					}
				}
				Query query = (SQLQuery) session.createSQLQuery(queryHQL.toString

());
                if(params!=null){
                    for(int i=0;i<params.size();i++){
                       query.setParameter(i, params.get(i));
                    }
               }
                Object[] result = query.list().toArray();
                return  ((BigDecimal)result[0]).intValue();
            }
        });
	}
上一篇: Java分页操作 BO 下一篇: Java返回json数据
发表评论
用户名: 匿名