Hibernate操作语法

public class DriverInfoDaoImpl extends BaseDaoImpl<DriverInfo> implements DriverInfoDao {

	public DriverInfoDaoImpl() {
		super(DriverInfo.class);
	}

	/**
	 * 新增驾驶员
	 * @param driverInfo
	 * @throws DataAccessException
	 */
	@Override
	public void createDriver(DriverInfo driverInfo) throws DataAccessException {
		super.save(driverInfo);
	}

	/**
	 * 修改驾驶员
	 * @param driverInfo
	 * @throws DataAccessException
	 */
	@Override
	public void editDriver(DriverInfo driverInfo) throws DataAccessException {
		super.update(driverInfo);
	}

	/**
	 * 删除驾驶员
	 * @param driverId
	 * @throws DataAccessException
	 */
	@Override
	public void deleteDriver(String driverId) throws DataAccessException {
		super.remove(driverId);
	}

	/**
	 * 查询驾驶员
	 * @return
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DriverInfo> listDriver(final int curNum,final String location,
			final String dvId,final String dvName) throws DataAccessException {
		return (List<DriverInfo>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(DriverInfo.class);
				criteria.add(Restrictions.ne("state", "2"));
				if (StringUtils.isNotBlank(location)) {
					criteria.add(Restrictions.eq("location", location));
				}
				if (StringUtils.isNotBlank(dvId)) {
					criteria.add(Restrictions.like("dvId", dvId, MatchMode.START));
				}
				if (StringUtils.isNotBlank(dvName)) {
					criteria.add(Restrictions.like("dvName", dvName, MatchMode.START));
				}
				criteria.addOrder(Order.desc("drTime"));
				criteria.addOrder(Order.desc("location"));
				criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				criteria.setMaxResults(PageTag.PAGE_NUM);
				return criteria.list();
			}

		});
	}
	@Override
	public Integer listDriverTotal(final String location,
			final String dvId,final String dvName) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(DriverInfo.class);
				criteria.add(Restrictions.ne("state", "2"));
				if (StringUtils.isNotBlank(location)) {
					criteria.add(Restrictions.eq("location", location));
				}
				if (StringUtils.isNotBlank(dvId)) {
					criteria.add(Restrictions.like("dvId", dvId, MatchMode.START));
				}
				if (StringUtils.isNotBlank(dvName)) {
					criteria.add(Restrictions.like("dvName", dvName, MatchMode.START));
				}
				return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}

		});
	}

	/**
	 * 查询驾驶员
	 * @return
	 * @throws DataAccessException
	 */
	@Override
	public DriverInfo getDriver(String driverId) throws DataAccessException {
		return super.get(driverId);
	}

	/**
	 * 查询驾驶员
	 * @return
	 * @throws DataAccessException
	 */
	@Override
	public boolean getDriverInfo(final String driverId) throws DataAccessException {
		Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("select count(*) from DriverInfo where dvId = :driverId");
				query.setParameter("driverId", driverId);
				Integer num = (Integer)query.uniqueResult();
				logger.info("驾驶员编号：" + driverId + "-->" + num);
				return num;
			}

		});
		if (i==0) {
			return false;
		}
		return true;
	}

}

public class CarInfoDaoImpl extends BaseDaoImpl<CarInfo> implements CarInfoDao {

	public CarInfoDaoImpl() {
		super(CarInfo.class);
	}

	/**
	 * 新增车辆
	 * @param carInfo
	 * @throws DataAccessException
	 */
	@Override
	public void createCar(CarInfo carInfo) throws DataAccessException {
		super.save(carInfo);
	}

	/**
	 * 修改车辆
	 * @param carInfo
	 * @throws DataAccessException
	 */
	@Override
	public void editCar(CarInfo carInfo) throws DataAccessException {
		super.update(carInfo);
	}

	/**
	 * 删除车辆
	 * @param carId
	 * @throws DataAccessException
	 */
	@Override
	public void deleteCar(String carId) throws DataAccessException {
		super.remove(carId);
	}

	/**
	 * 查询车辆
	 * @return
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<CarInfo> listCar(final int curNum, final String location, 
			final String car) throws DataAccessException {
		return (List<CarInfo>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(CarInfo.class);
				criteria.add(Restrictions.ne("state", "2"));
				if (StringUtils.isNotBlank(location)) {
					criteria.add(Restrictions.eq("location", location));
				}
				if (StringUtils.isNotBlank(car)) {
					criteria.add(Restrictions.like("car", car, MatchMode.START));
				}
				criteria.addOrder(Order.desc("cTime"));
				criteria.addOrder(Order.desc("location")).addOrder(Order.asc("car"));
				criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				criteria.setMaxResults(PageTag.PAGE_NUM);
				return criteria.list();
			}

		});
	}

	/**
	 * 查询车辆
	 * @return
	 * @throws DataAccessException
	 */
	@Override
	public Integer listCarTotal(final int curNum, final String location, 
			final String car) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(CarInfo.class);
				criteria.add(Restrictions.ne("state", "2"));
				if (StringUtils.isNotBlank(location)) {
					criteria.add(Restrictions.eq("location", location));
				}
				if (StringUtils.isNotBlank(car)) {
					criteria.add(Restrictions.like("car", car, MatchMode.END));
				}
				return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}

		});
	}

	/**
	 * 查询车辆
	 * @return
	 * @throws DataAccessException
	 */
	@Override
	public CarInfo getCar(String carId) throws DataAccessException {
		return super.get(carId);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> listCarByLoc(final String location) throws DataAccessException {
		return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT * FROM (");
				sql.append("SELECT TR.F_CAR, F_SEATS, TR.F_STATE, F_R_T_O ");
				sql.append("FROM (SELECT * FROM (SELECT F_CAR,F_STATE, ");
				sql.append("TO_CHAR(F_R_T_O, 'yyyy-mm-dd hh24:mi:ss') F_R_T_O, ");
				sql.append("ROW_NUMBER() OVER(PARTITION BY F_CAR ORDER BY F_TR_T DESC) RN ");
				sql.append("FROM T_RECORD) WHERE RN = 1) TR,T_CAR_INFO TC ");
				sql.append("WHERE TC.F_CAR = TR.F_CAR AND TC.F_LOCATION = :location ");
				sql.append("AND TC.F_STATE = '1' UNION ");
				sql.append("SELECT TC.F_CAR, TC.F_SEATS, '1', '' ");
				sql.append("FROM T_CAR_INFO TC ");
				sql.append("WHERE TC.F_LOCATION = :location ");
				sql.append("AND TC.F_STATE = '1' AND NOT EXISTS ");
				sql.append("(SELECT 1 FROM T_RECORD TR WHERE TR.F_CAR = TC.F_CAR) ");
				sql.append(") ORDER BY F_STATE,F_R_T_O ");
				logger.info("车辆详细列表--->打印参数:"+location);
				return session.createSQLQuery(sql.toString()).setParameter("location", location).list();
			}
		});
	}

	/**
	 * 查询车辆对应的驾驶员
	 * @return
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<String> listDriversByLoc(final String location) throws DataAccessException {
		return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("select dvName from DriverInfo where location=:location");
				query.setParameter("location", location);
				return query.list();
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getAllLocation() throws DataAccessException {
		return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createSQLQuery("SELECT COMPANY_ID, NAME FROM SPFS.COMPANYS ORDER BY COMPANY_ID");
				return query.list();
			}
		});
	}

	@Override
	public boolean getCarInfo(final String car) throws DataAccessException {
		Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("select count(*) from CarInfo where car = :car");
				query.setParameter("car", car);
				Integer num = (Integer)query.uniqueResult();
				logger.info("车牌号：" + car + "-->" + num);
				return num;
			}

		});
		if (i==0) {
			return false;
		}
		return true;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllCarByLoc(final String location)
			throws DataAccessException {
		return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("select car from CarInfo WHERE location = :location");
				query.setParameter("location", location);
				return query.list();
			}
		});
	}

	@Override
	public boolean getAccoutnInfo(final String atId) throws DataAccessException {
		Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("select count(*) from Account where atId = :atId");
				query.setParameter("atId", atId);
				Integer num = (Integer)query.uniqueResult();
				logger.info("编号：" + atId + "-->" + num);
				return num;
			}

		});
		if (i==0) {
			return false;
		}
		return true;
	}

	/**
	 * 获取费用记录
	 * @return
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getReportData() throws DataAccessException {
		return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT B.F_CARD_GAS, B.F_CARD_PASS,A.* FROM (SELECT F_CAR,F_MONTH,SUM(F_FEE_GAS) TOTAL_GAS, ");
				sql.append("SUM(F_FEE_CARD) TOTAL_CARD,SUM(F_FEE_MAINTAIN) TOTAL_MAINTAIN FROM (SELECT F_CAR,TO_CHAR( ");
				sql.append("F_COST_DATE, 'MM') F_MONTH,F_FEE_GAS,F_FEE_CARD,F_FEE_MAINTAIN FROM T_COST WHERE TRUNC(SYSDATE,");
				sql.append("'YYYY')=TRUNC(F_COST_DATE,'YYYY')) GROUP BY F_CAR,F_MONTH) A,T_CAR_INFO B WHERE A.F_CAR = B.F_CAR ORDER BY A.F_CAR, F_MONTH ");
				Query query = session.createSQLQuery(sql.toString());
				return query.list();
			}
		});

	}

}

public class ApplyDaoImpl extends BaseDaoImpl<Apply> implements ApplyDao {

	public ApplyDaoImpl() {
		super(Apply.class);
	}

	/**
	 * 新增申请条目
	 * @param applyInfo
	 * @throws DataAccessException
	 */
	@Override
	public void createApply(Apply applyInfo) throws DataAccessException {
		super.save(applyInfo);
	}

	/**
	 * 修改申请条目
	 * @param applyInfo
	 * @throws DataAccessException
	 */
	@Override
	public void editApply(Apply applyInfo) throws DataAccessException {
		super.update(applyInfo);
	}

	/**
	 * 删除申请条目
	 * @param applyId
	 * @throws DataAccessException
	 */
	@Override
	public void deleteApply(String applyId) throws DataAccessException {
		super.remove(applyId);
	}

	/**
	 * 查询申请条目记录
	 * @return
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Apply> listApply(final String atId, final String location, 
			final String aim, final String uTime, final int curNum) 
					throws DataAccessException {
		return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Apply.class);
				criteria.add(Restrictions.eq("location", location));
				criteria.add(Restrictions.eq("apId", atId));
				if (StringUtils.isNotBlank(aim)) {
					criteria.add(Restrictions.like("aim", aim, MatchMode.START));
				}
				if (StringUtils.isNotBlank(uTime)) {
					try {
						SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						criteria.add(Restrictions.between("useBegin", format.parse(
								uTime+" 00:00:00"),format.parse(uTime+" 23:59:59")));
					} catch (Exception e) {
					}
				}
				criteria.addOrder(Order.desc("cTime")).addOrder(Order.desc("useBegin"));
				criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				criteria.setMaxResults(PageTag.PAGE_NUM);
				return criteria.list();
			}

		});
	}

	/**
	 * 查询申请条目数
	 * @return
	 * @throws DataAccessException
	 */
	@Override
	public Integer listApplyTotal(final String atId, final String location,
			final String aim, final String uTime) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Apply.class);
				criteria.add(Restrictions.eq("location", location));
				criteria.add(Restrictions.eq("apId", atId));
				if (StringUtils.isNotBlank(aim)) {
					criteria.add(Restrictions.like("aim", aim, MatchMode.START));
				}
				if (StringUtils.isNotBlank(uTime)) {
					try {
						SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						criteria.add(Restrictions.between("useBegin", format.parse(
								uTime+" 00:00:00"),format.parse(uTime+" 23:59:59")));
					} catch (Exception e) {
					}
				}
				return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}

		});
	}

	/**
	 * 查询申请条目
	 * @return
	 * @throws DataAccessException
	 */
	@Override
	public Apply getApply(String applyId) throws DataAccessException {
		return super.get(applyId);
	}

	/**
	 * 查询申请条目
	 * @return
	 * @throws DataAccessException
	 */
	@Override
	public Object[] getApplyDetail(final String applyId) throws DataAccessException {
		return (Object[])getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT TA.F_AP_NAME,TA.F_AP_NUM,TA.F_AP_DEPT,TA.F_AIM,TA.F_CAUSE, ");
				sql.append("TA.F_USE_SPEC,TA.F_USE_BEGIN,TA.F_USE_END,TA.F_TYPE_OUT,TA.F_TYPE_IN, ");
				sql.append("(SELECT TP.F_AV_NAME FROM T_APPROVE TP WHERE TP.F_APPLY_ID = TA.F_APPLY_ID "); 
				sql.append("AND TP.F_LEVEL = '10' AND ROWNUM = 1) LEVEL_ONE, ");
				sql.append("(SELECT TP.F_AV_NAME FROM T_APPROVE TP WHERE TP.F_APPLY_ID = TA.F_APPLY_ID "); 
				sql.append("AND TP.F_LEVEL <> '10' AND ROWNUM = 1) LEVEL_TWO, ");
				sql.append("(SELECT TD.F_WAY_LINE FROM T_DISTRIBUTE TD WHERE TD.F_APPLY_ID = TA.F_APPLY_ID ");
				sql.append("AND TD.F_STATE = '1' AND ROWNUM = 1) F_WAY_LINE ");
				sql.append("FROM T_APPLY TA WHERE TA.F_APPLY_ID = :applyId AND ROWNUM = 1 ");
				logger.info("基本信息列表--->打印参数:"+applyId);
				return session.createSQLQuery(sql.toString()).setParameter("applyId", applyId).uniqueResult();
			}
		});
	}

	/**
	 * 门卫登记列表
	 * @return
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getGuardInfo(final String applyId) throws DataAccessException {
		return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT TR.F_TYPE,TR.F_CAR,TR.F_NAME,F_R_T_O,F_R_P_O, ");
				sql.append("F_R_P_N_O,F_R_T_I,F_R_P_I,F_R_P_N_I FROM T_RECORD TR, "); 
				sql.append("T_DISTRIBUTE TD, t_apply ta WHERE TR.F_DISTRIBUTE_ID= ");
				sql.append("TD.F_DISTRIBUTE_ID AND TD.F_APPLY_ID = TA.F_APPLY_ID AND ");
				sql.append("TA.F_APPLY_ID=:applyId AND TD.F_STATE='1' ORDER BY TR.F_TYPE");
				logger.info("门卫登记列表：");
				return session.createSQLQuery(sql.toString()).setParameter("applyId", applyId).list();
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Apply> listApprove(final int curNum, final String atId,final String apId,
			final String apName,final String cTime,final String aim) throws DataAccessException {
		return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT * FROM T_APPLY TA WHERE TA.F_STATE IN ('2','3','4') ");
				if (StringUtils.isNotBlank(apId)) {
					sql.append("AND F_AP_ID LIKE '").append(apId).append("%'");
				}
				if (StringUtils.isNotBlank(apName)) {
					sql.append("AND F_AP_NAME LIKE '").append(apName).append("%'");
				}
				if (StringUtils.isNotBlank(aim)) {
					sql.append("AND F_AIM LIKE '").append(aim).append("%'");
				}
				if (StringUtils.isNotBlank(cTime)) {
					sql.append("AND TO_CHAR(F_C_T,'YYYY-MM-DD')='").append(cTime).append("'");
				}
				sql.append("AND EXISTS (SELECT 1 FROM (SELECT EMP_ID, UD.REAL_NAME ");
				sql.append("FROM SPFS.USER_DETAILS UD WHERE UD.REAL_NAME <> :atId START WITH ");
				sql.append("UD.REAL_NAME = :atId CONNECT BY PRIOR UD.EMP_ID = ");
				sql.append("UD.MANAGER_ID) WUD WHERE WUD.REAL_NAME = TA.F_AP_ID) ");
				sql.append("ORDER BY TA.F_C_T DESC, TA.F_USE_BEGIN DESC ");
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.addEntity("ta", Apply.class);
				query.setParameter("atId", atId);
				query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				query.setMaxResults(PageTag.PAGE_NUM);
				return query.list();
			}

		});
	}

	@Override
	public Integer listApproveTotal(final String atId,final String apId,
			final String apName,final String cTime,final String aim) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT count(1) FROM T_APPLY TA WHERE TA.F_STATE IN ('2','3','4') ");
				if (StringUtils.isNotBlank(apId)) {
					sql.append("AND F_AP_ID LIKE '").append(apId).append("%'");
				}
				if (StringUtils.isNotBlank(apName)) {
					sql.append("AND F_AP_NAME LIKE '").append(apName).append("%'");
				}
				if (StringUtils.isNotBlank(aim)) {
					sql.append("AND F_AIM LIKE '").append(aim).append("%'");
				}
				if (StringUtils.isNotBlank(cTime)) {
					sql.append("AND TO_CHAR(F_C_T,'YYYY-MM-DD')='").append(cTime).append("'");
				}
				sql.append("AND EXISTS (SELECT 1 FROM (SELECT EMP_ID, UD.REAL_NAME ");
				sql.append("FROM SPFS.USER_DETAILS UD WHERE UD.REAL_NAME <> :atId START WITH ");
				sql.append("UD.REAL_NAME = :atId CONNECT BY PRIOR UD.EMP_ID = ");
				sql.append("UD.MANAGER_ID) WUD WHERE WUD.REAL_NAME = TA.F_AP_ID) ");
				sql.append("ORDER BY TA.F_C_T DESC, TA.F_USE_BEGIN DESC ");
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setParameter("atId", atId);
				return ((BigDecimal)query.uniqueResult()).intValue();
			}

		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Apply> listUnApprove(final int curNum, final String atId, final String level) throws DataAccessException {
		return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT * FROM T_APPLY TA WHERE TA.F_STATE IN ('1',:level) ");
				sql.append("AND EXISTS (SELECT 1 FROM (SELECT EMP_ID, UD.REAL_NAME ");
				sql.append("FROM SPFS.USER_DETAILS UD WHERE UD.REAL_NAME <> :atId START WITH ");
				sql.append("UD.REAL_NAME = :atId CONNECT BY PRIOR UD.EMP_ID = ");
				sql.append("UD.MANAGER_ID) WUD WHERE WUD.REAL_NAME = TA.F_AP_ID) ");
				sql.append("ORDER BY TA.F_C_T DESC, TA.F_USE_BEGIN DESC ");
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.addEntity("ta", Apply.class);
				query.setParameter("level", level);
				query.setParameter("atId", atId);
				query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				query.setMaxResults(PageTag.PAGE_NUM);
				return query.list();
			}

		});
	}

	@Override
	public Integer listUnApproveTotal(final int curNum, final String atId, final String level) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT count(1) FROM T_APPLY TA WHERE TA.F_STATE IN ('1',:level) ");
				sql.append("AND EXISTS (SELECT 1 FROM (SELECT EMP_ID, UD.REAL_NAME ");
				sql.append("FROM SPFS.USER_DETAILS UD WHERE UD.REAL_NAME <> :atId START WITH ");
				sql.append("UD.REAL_NAME = :atId CONNECT BY PRIOR UD.EMP_ID = ");
				sql.append("UD.MANAGER_ID) WUD WHERE WUD.REAL_NAME = TA.F_AP_ID) ");
				sql.append("ORDER BY TA.F_C_T DESC, TA.F_USE_BEGIN DESC ");
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setParameter("level", level);
				query.setParameter("atId", atId);
				return ((BigDecimal)query.uniqueResult()).intValue();
			}

		});
	}

	@Override
	public void updateApplysState(final String[] applyIds, final String state) throws DataAccessException {

		getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String hql = "update Apply set state = :state where applyId in (:applyIds)";
				Query query = session.createQuery(hql);
				query.setParameter("state", state);
				query.setParameterList("applyIds", applyIds);
				query.executeUpdate();
				return null;
			}

		});
	}

	@Override
	public void saveApproves(Approve[] approves) throws DataAccessException {
		for (Approve approve : approves) {
			getHibernateTemplate().save(approve);
			getHibernateTemplate().flush();
			getHibernateTemplate().clear();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Record> listOutGuard(final int curNum, final String location) throws DataAccessException {
		return (List<Record>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT F_CAR,F_NAME,MAX(F_TR_T) F_TR_T FROM T_RECORD "); 
				sql.append("WHERE F_R_T_O IS NULL AND F_LOCATION=:location AND TRUNC(F_TR_T) >= TRUNC(SYSDATE) ");
				sql.append("GROUP BY F_CAR,F_NAME ORDER BY F_TR_T DESC ");
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setParameter("location", location);
				query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				query.setMaxResults(PageTag.PAGE_NUM);
				List<Object[]> objects = (List<Object[]>)query.list();
				List<Record> records = new ArrayList<Record>();
				Record record = null;
				if (objects!=null&&!objects.isEmpty()) {
					for (Object[] object : objects) {
						record = new Record();
						record.setCar((String)object[0]);
						record.setrName((String)object[1]);
						record.settTime((Date)object[2]);
						records.add(record);
					}
				}
				return records;
			}

		});
	}

	@Override
	public Integer listOutGuardTotal(int curNum, final String location) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT COUNT(DISTINCT F_CAR) FROM T_RECORD WHERE F_R_T_O IS NULL AND "); 
				sql.append("F_LOCATION=:location AND TRUNC(F_TR_T) >= TRUNC(SYSDATE) ");
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setParameter("location", location);
				return ((BigDecimal)query.uniqueResult()).intValue();
			}

		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Record> listInGuard(final int curNum, final String location) throws DataAccessException {
		return (List<Record>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT F_CAR,F_NAME,F_R_T_O,MAX(F_TR_T) F_TR_T FROM T_RECORD WHERE F_R_T_O IS NOT NULL AND "); 
				sql.append("F_R_T_I IS NULL AND F_LOCATION=:location AND TRUNC(F_TR_T) >= TRUNC(SYSDATE) ");
				sql.append("GROUP BY F_R_T_O,F_NAME,F_CAR ORDER BY F_TR_T DESC ");
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setParameter("location", location);
				query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				query.setMaxResults(PageTag.PAGE_NUM);
				List<Object[]> objects = (List<Object[]>)query.list();
				List<Record> records = new ArrayList<Record>();
				Record record = null;
				if (objects!=null&&!objects.isEmpty()) {
					for (Object[] object : objects) {
						record = new Record();
						record.setCar((String)object[0]);
						record.setrName((String)object[1]);
						record.setRto((Date)object[2]);
						record.settTime((Date)object[3]);
						records.add(record);
					}
				}
				return records;
			}

		});
	}

	@Override
	public Integer listInGuardTotal(int curNum, final String location) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT COUNT(DISTINCT F_CAR) FROM T_RECORD WHERE F_R_T_I IS NULL AND F_R_T_O "); 
				sql.append("IS NOT NULL AND F_LOCATION=:location AND TRUNC(F_TR_T) >= TRUNC(SYSDATE) ");
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setParameter("location", location);
				return ((BigDecimal)query.uniqueResult()).intValue();
			}

		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Apply> listDistribute(final int curNum,final String location,final String apNum,
			final String apId,final String aim) throws DataAccessException {
		return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Apply.class);
				criteria.add(Restrictions.ne("state", "1"));
				criteria.add(Restrictions.ne("state", "2"));
				criteria.add(Restrictions.ne("state", "3"));
				criteria.add(Restrictions.ne("state", "4"));
				if (StringUtils.isNotBlank(location)) {
					criteria.add(Restrictions.eq("location", location));
				}
				if (StringUtils.isNotBlank(apNum)) {
					criteria.add(Restrictions.eq("apNum", apNum));
				}
				if (StringUtils.isNotBlank(apId)) {
					criteria.add(Restrictions.like("apId", apId, MatchMode.START));
				}
				if (StringUtils.isNotBlank(aim)) {
					criteria.add(Restrictions.like("aim", aim, MatchMode.START));
				}
				criteria.addOrder(Order.desc("cTime")).addOrder(Order.desc("useBegin"));
				criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				criteria.setMaxResults(PageTag.PAGE_NUM);
				return criteria.list();
			}

		});
	}

	@Override
	public Integer listDistributeTotal(final String location,final String apNum,
			final String apId,final String aim) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Apply.class);
				criteria.add(Restrictions.ne("state", "1"));
				criteria.add(Restrictions.ne("state", "2"));
				criteria.add(Restrictions.ne("state", "3"));
				criteria.add(Restrictions.ne("state", "4"));
				if (StringUtils.isNotBlank(location)) {
					criteria.add(Restrictions.eq("location", location));
				}
				if (StringUtils.isNotBlank(apNum)) {
					criteria.add(Restrictions.eq("apNum", apNum));
				}
				if (StringUtils.isNotBlank(apId)) {
					criteria.add(Restrictions.like("apId", apId, MatchMode.START));
				}
				if (StringUtils.isNotBlank(aim)) {
					criteria.add(Restrictions.like("aim", aim, MatchMode.START));
				}
				return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}

		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Apply> listUnDistribute(final int curNum) throws DataAccessException {
		return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Apply.class);
				criteria.add(Restrictions.eq("state", "3"));
				criteria.add(Restrictions.ge("useBegin", DateUtil.getCurDate()));
				criteria.addOrder(Order.desc("cTime")).addOrder(Order.asc("useBegin"));
				criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				criteria.setMaxResults(PageTag.PAGE_NUM);
				return criteria.list();
			}

		});
	}

	@Override
	public Integer listUnDistributeTotal(int curNum) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Apply.class);
				criteria.add(Restrictions.eq("state", "3"));
				criteria.add(Restrictions.ge("useBegin", DateUtil.getCurDate()));
				return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}

		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Apply> listSurvey(final int curNum, final String atId) throws DataAccessException {
		return (List<Apply>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Apply.class);
				criteria.add(Restrictions.eq("state", "7"));
				criteria.add(Restrictions.eq("apId", atId));
				criteria.addOrder(Order.desc("cTime"));
				criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				criteria.setMaxResults(PageTag.PAGE_NUM);
				return criteria.list();
			}

		});
	}

	@Override
	public Integer listSurveyTotal(int curNum, final String atId) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Apply.class);
				criteria.add(Restrictions.eq("state", "7"));
				criteria.add(Restrictions.eq("apId", atId));
				return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}

		});
	}

	@Override
	public String saveRecord(Record record) throws DataAccessException {
		return (String)getHibernateTemplate().save(record);
	}

	@Override
	public void saveDistributes(Distribute[] distributes)
			throws DataAccessException {
		for (Distribute distribute : distributes) {
			getHibernateTemplate().save(distribute);
			getHibernateTemplate().flush();
			getHibernateTemplate().clear();
		}
	}

	@Override
	public String saveDistribute(Distribute distribute)
			throws DataAccessException {
		return (String)getHibernateTemplate().save(distribute);
	}

	@Override
	public void saveRecords(List<Record> records) throws DataAccessException {
		for (Record record : records) {
			getHibernateTemplate().save(record);
			getHibernateTemplate().flush();
			getHibernateTemplate().clear();
		}
	}

	@Override
	public Record getItemGuard(String recordId, String flag) throws DataAccessException {
		Date rto=(Date)getHibernateTemplate().find("SELECT rto FROM Record WHERE car ='" +
				recordId + "' AND state = '" +
				flag + "' AND ROWNUM=1").get(0);
		Record record = new Record();
		record.setRto(rto);
		return record;
	}

	@Override
	public void updateItemGuard(final String recordId, final String atId, final String atName, 
			final String flag) throws DataAccessException {
		getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder hql = new StringBuilder();
				if (flag.equals("2")) {
					hql.append("UPDATE Record SET rto=sysdate,rpo =:atId,rpno=:atName,state=:state ");
					hql.append("WHERE rto IS NULL AND car=:car");
				} else {
					hql.append("UPDATE Record SET rti=sysdate,rpi =:atId,rpni=:atName,state=:state ");
					hql.append("WHERE rto IS NOT NULL AND rti IS NULL AND car=:car");
				}
				Query query = session.createQuery(hql.toString());
				query.setParameter("atId", atId);
				query.setParameter("atName", atName);
				query.setParameter("state", flag);
				query.setParameter("car", recordId);
				return query.executeUpdate();
			}
		});
	}

	@Override
	public void updateAccount(final String atid, final String psd1, 
			final String role) throws DataAccessException {
		getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String hql = null;
				Query query = null;
				if (psd1 != null) {
					hql = "UPDATE Account a SET atPsd = :psd1 WHERE atId = :atid";
					query = session.createQuery(hql);
					query.setParameter("atid", atid);
					query.setParameter("psd1", psd1);
				} else {
					hql = "UPDATE Account a SET role = :role WHERE atId = :atid";
					query = session.createQuery(hql);
					query.setParameter("atid", atid);
					query.setParameter("role", role);
				}
				return query.executeUpdate();
			}
		});
	}

	@Override
	public void updateApplyStateByRId(final String recordId, final String state)
			throws DataAccessException {
		getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder hql = new StringBuilder();
				hql.append("UPDATE Apply ta SET ta.state = :state WHERE ta.applyId IN (select ");
				hql.append("td.applyId FROM Distribute td, Record tr WHERE td.distributeId ");
				hql.append("= tr.distributeId AND tr.car = :recordId) ");
				Query query = session.createQuery(hql.toString());
				query.setParameter("state", state);
				query.setParameter("recordId", recordId);
				query.executeUpdate();
				return null;
			}

		});
	}

	@Override
	public void saveSurvey(Survey survey) throws DataAccessException {
		getHibernateTemplate().save(survey);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getApplyIds(final String recordId) throws DataAccessException {
		return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("SELECT TA.applyId FROM Apply TA, " +
						"Distribute TD, Record TR WHERE TA.applyId = TD.applyId AND " +
				"TD.distributeId=TR.distributeId AND TR.car=:recordId AND TR.rti IS NULL");
				query.setParameter("recordId", recordId);
				return query.list();
			}
		});
	}

	@Override
	public void saveCost(Cost cost) throws DataAccessException {
		getHibernateTemplate().save(cost);
	}

	@Override
	public boolean checkCarNoState(final String carNo) throws DataAccessException {
		Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("SELECT COUNT(*) FROM Record WHERE car = :car" +
						" AND rto IS NOT NULL AND state='2'");
				query.setParameter("car", carNo);
				Integer num = (Integer)query.uniqueResult();
				return num;
			}

		});
		if (i==0) {
			return false;
		}
		return true;
	}

	@Override
	public boolean checkExistsCarNo(final String distributeId, final String carNo)
			throws DataAccessException {
		Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("SELECT COUNT(*) FROM Record WHERE car = :car" +
						" AND rto IS NULL AND state='3' AND distributeId = :distributeId");
				query.setParameter("distributeId", distributeId);
				query.setParameter("car", carNo);
				Integer num = (Integer)query.uniqueResult();
				return num;
			}

		});
		if (i==0) {
			return false;
		}
		return true;
	}

	@Override
	public Object[] getSurveyInfo(final String applyId) throws DataAccessException {
		return (Object[])getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT TS.F_IMPROVE, TS.F_GRADE, TS.F_ADVICE, TS.F_SV_T FROM ");
				sql.append("T_SURVEY TS WHERE TS.F_APPLY_ID = :applyId");
				logger.info("满意度登记列表：");
				return session.createSQLQuery(sql.toString()).setParameter("applyId", applyId).uniqueResult();
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Cost> listCost(final int curNum,final String car,final String costDate) throws DataAccessException {
		return (List<Cost>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Cost.class);
				if (StringUtils.isNotBlank(costDate)) {
					try {
						criteria.add(Restrictions.eq("costDate", new SimpleDateFormat("yyyy-MM-dd").parse(costDate)));
					} catch (Exception e) {
						criteria.add(Restrictions.eq("cp","TEST"));
					}
				}
				if (StringUtils.isNotBlank(car)) {
					criteria.add(Restrictions.like("car", car, MatchMode.START));
				}
				criteria.addOrder(Order.desc("ct"));
				criteria.addOrder(Order.asc("car"));
				criteria.addOrder(Order.desc("costDate"));
				criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				criteria.setMaxResults(PageTag.PAGE_NUM);
				return criteria.list();
			}

		});
	}

	@Override
	public Integer listCostTotal(final String car,final String costDate) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Cost.class);
				if (StringUtils.isNotBlank(costDate)) {
					try {
						criteria.add(Restrictions.eq("costDate", new SimpleDateFormat("yyyy-MM-dd").parse(costDate)));
					} catch (Exception e) {
						criteria.add(Restrictions.eq("cp","TEST"));
					}
				}
				if (StringUtils.isNotBlank(car)) {
					criteria.add(Restrictions.like("car", car, MatchMode.START));
				}
				return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}

		});
	}

	@Override
	public void delCost(String costId) throws DataAccessException {
		Cost cost = (Cost)getHibernateTemplate().load(Cost.class, costId);
		getHibernateTemplate().delete(cost);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> listRecord(final int curNum, final String location)
			throws DataAccessException {
		return (List<Object[]>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery("select distinct car,rName,rto,rpno,rti,rpni from Record r where location=? order by rto desc,rti desc");
				query.setString(0, location);
				query.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				query.setMaxResults(PageTag.PAGE_NUM);
				return query.list();
			}
		});
	}

	@Override
	public Integer listRecordTotal(final String location) throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createSQLQuery("SELECT COUNT(*) FROM (SELECT DISTINCT F_CAR,F_NAME,F_R_T_O,F_R_P_N_O,F_R_T_I,F_R_P_N_I FROM T_RECORD R WHERE F_LOCATION=?)");
				query.setString(0, location);
				return ((BigDecimal)query.uniqueResult()).intValue();
			}

		});
	}

	@SuppressWarnings("unchecked")
	public List<String> getFleetAtId() throws DataAccessException {
		return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT U.USER_EMAIL FROM SPFS.USER_DETAILS UD,SC.USERS U, ");
				sql.append("T_ACCOUNT TA WHERE UD.USER_ID=U.USER_ID AND UD.REAL_NAME ");
				sql.append("= TA.F_AT_ID AND UD.IS_NEW = 'N' AND TA.F_ROLE = '4' ");
				Query query = session.createSQLQuery(sql.toString());
				return query.list();
			}
		});
	}

	/**
	 * 申请人获取到上级邮箱
	 * @param atId
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getMailById(final String atId) throws DataAccessException {
		return (List<String>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT USER_EMAIL FROM (SELECT U.USER_EMAIL FROM (SELECT USER_ID,JOB_LEVEL,IS_NEW FROM ");
				sql.append("SPFS.USER_DETAILS UD WHERE REAL_NAME<>:atId START WITH REAL_NAME=:atId CONNECT BY ");  
				sql.append("PRIOR UD.MANAGER_ID=UD.EMP_ID) TU,SC.USERS U WHERE TU.USER_ID=U.USER_ID AND IS_NEW "); 
				sql.append("= 'N' AND (JOB_LEVEL<='50' OR JOB_LEVEL IS NULL) ORDER BY JOB_LEVEL) WHERE ROWNUM <=2");
				Query query = session.createSQLQuery(sql.toString());
				query.setParameter("atId", atId);
				return query.list();
			}
		});
	}

}

public class LoginDaoImpl extends HibernateDaoSupport implements LoginDao  {

	@Override
	public Map<String, String> getLocations() throws DataAccessException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Account getAccount(final String atId, final String atPsd)
			throws DataAccessException {
		return (Account)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Account.class);
				criteria.add(Restrictions.eq("atId", atId));
				if (atPsd != null) {
					criteria.add(Restrictions.eq("atPsd", atPsd));
				}
				criteria.add(Restrictions.eq("state", "1"));
				return criteria.uniqueResult();
			}
		});
	}

	@Override
	public boolean checkAccount(final String atId) throws DataAccessException {
		Integer i = (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				SQLQuery query =session.createSQLQuery("SELECT COUNT(1) FROM SPFS.USER_DETAILS WHERE IS_NEW='N' AND REAL_NAME = :real_name");
				query.setParameter("real_name", atId);
				Integer num = ((BigDecimal)query.uniqueResult()).intValue();
				logger.info("登录名：" + atId + "-->" + num);
				return num;
			}

		});
		if (i==0) {
			return false;
		}
		return true;
	}

	@Override
	public void saveAccount(Account account) throws DataAccessException {
		getHibernateTemplate().save(account);
	}

	@Override
	public Object[] getLocById(final String atId) throws DataAccessException {
		return (Object[])getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				StringBuilder sql = new StringBuilder();
				sql.append("SELECT UD.REAL_NAME,D.NAME,C.COMPANY_ID,C.NAME,UD.ALTERNATE_NAME,UD.JOB_LEVEL,UD.EMP_ID FROM ");
				sql.append("SPFS.USER_DETAILS UD ,SPFS.DEPTS D, SPFS.COMPANYS C WHERE UD.COMPANY_ID = C.COMPANY_ID AND UD.DEPT_ID ");
				sql.append("= D.DEPT_ID AND IS_NEW='N' AND REAL_NAME = :real_name ");
				SQLQuery query =session.createSQLQuery(sql.toString());
				query.setParameter("real_name", atId);
				return query.uniqueResult();
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Account> listAccount(final int curNum, final String role,final String actId)
			throws DataAccessException {
		return (List<Account>)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Account.class);
				if ("4".equals(role)) {
					criteria.add(Restrictions.eq("state", "1"));
					criteria.add(Restrictions.eq("role", "3"));
				}
				if (StringUtils.isNotBlank(actId)) {
					criteria.add(Restrictions.like("atId", actId, MatchMode.START));
				}
				criteria.addOrder(Order.desc("cTime"));
				criteria.addOrder(Order.desc("role"));
				criteria.addOrder(Order.asc("location"));
				criteria.setFirstResult((curNum - 1) * PageTag.PAGE_NUM);
				criteria.setMaxResults(PageTag.PAGE_NUM);
				return criteria.list();
			}

		});
	}

	@Override
	public Integer listAccountTotal(final String role,final String actId)
			throws DataAccessException {
		return (Integer)getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Criteria criteria = session.createCriteria(Account.class);
				if ("4".equals(role)) {
					criteria.add(Restrictions.eq("state", "1"));
					criteria.add(Restrictions.eq("role", "3"));
				}
				if (StringUtils.isNotBlank(actId)) {
					criteria.add(Restrictions.like("atId", actId, MatchMode.START));
				}
				return (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();
			}

		});
	}

	@Override
	public void delAccount(String accountId) throws DataAccessException {
		Account account = (Account)getHibernateTemplate().load(Account.class, accountId);
		getHibernateTemplate().delete(account);
	}

	@Override
	public Role getRole(String role) throws DataAccessException {
		return (Role)getHibernateTemplate().get(Role.class, role);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Role> getAllRole() throws DataAccessException {
		return (List<Role>)getHibernateTemplate().find("from Role order by roleName");
	}

	@Override
	public void updateRole(Role role) throws DataAccessException {
		getHibernateTemplate().flush();
		getHibernateTemplate().clear();
		getHibernateTemplate().update(role);
	}

}