根据Hibernate的方言决定要执行的本地sql

项目产品要求运行在不同的数据库上,目前暂定为sqlserver和mysql,要求通过更改配置文件能够在不同的数据库之间切换：

CommonDao代码：

@Resource(name="hibernateTemplate")
	protected HibernateTemplate hibernateTemplate;
	
	/**
	 * 是否mysql数据库
	 * @return
	 */
	public Boolean isMysql(){
		return getDialect() instanceof MySQLDialect;
	}
	
	/**
	 * 是否sqlserver数据库
	 * @return
	 */
	public Boolean isSqlserver(){
		return getDialect() instanceof SQLServerDialect;
	}
	
	/**
	 * 获得方言
	 * @return
	 */
	public Dialect getDialect(){
		if(hibernateTemplate.getSessionFactory() instanceof SessionFactoryImplementor){
			SessionFactoryImplementor sf = (SessionFactoryImplementor)hibernateTemplate.getSessionFactory();			
			return sf.getDialect();	
		}
		return new MySQL5InnoDBDialect();
	}

下面是业务层的使用案例：

/**
	 * 根据部门和操作员查询客户邮箱列表
	 * 
	 * @param pi
	 *            分页信息对象
	 * @param deptId
	 *            部门编号
	 * @param empID
	 *            操作员编号
	 */
	@Override
	public void getCustomerEmailsList(final PageInfo<Object[]> pi,
			final Integer deptId, final Integer empId) {
		
		String sql = "";
		if (super.commonDao.isSqlserver()){
			//sqlserver版本
			sql = "SELECT c.customerid,c.name, "
					+ "		[linkm] = REPLACE((SELECT name AS [data()] "
					+ "			from linkman l where c.customerid = l.customerid "
					+ "			FOR XML PATH('')), ' ', ','), "
					+ "		[email] = REPLACE((SELECT email AS [data()] "
					+ "			from linkman l where c.customerid = l.customerid "
					+ "			FOR XML PATH('')), ' ', ',') "
					+ "		FROM customer AS c left join emp as e on e.empid = c.empid "
					+ "         inner join Dept d on d.DeptID=e.DeptID "
					+ "			where 1=1 {condition} order by c.customerid desc ";	
		}
		if (super.commonDao.isMysql()){
			//mysql版本
			sql  = "select c.customerid, "
				+"		c.name, "
				+"		(select group_concat(name) from linkman as l "
				+"		where c.customerId=l.customerId) as linkm, "
				+"		(select group_concat(email) from linkman as l where c.customerId = l.customerId) as email "
				+"	from customer as c left join emp as e on e.empid=c.empid "
				+"	inner join dept as d on d.deptid=e.deptid "
				+"	where 1=1  {condition}  order by c.customerId desc";	
		}

		String count = "select count(1) from customer as c left join emp as e on e.empid = c.empid inner join Dept d on d.DeptID=e.DeptID where 1=1 {condition} ";

		// 获取当前登录的用户
		Emp emp = (Emp) UserUtil.getLoginedUser();
		Role r = emp.getRole();
		
		// 根据当前登录用户的角色，拼接不同的查询语句
		StringBuilder conditionBuilder = new StringBuilder();
		if (r.getRoleId().intValue() == 1) {
			conditionBuilder.append(" and d.disunderling=1 ");
			if (deptId != null && deptId.intValue() > 0 && empId == null) {
				conditionBuilder.append(" and e.deptid=" + deptId);
			} else if (empId != null && empId.intValue() > 0) {
				conditionBuilder.append(" and e.empid=" + empId);
			}
		} else if (r.getRoleId().intValue() == 2) {
			conditionBuilder.append(" and e.deptid="
					+ emp.getDept().getDeptId());
			if (empId != null && empId.intValue() > 0) {
				conditionBuilder.append(" and e.empid=" + empId);
			}
		} else if (r.getRoleId().intValue() == 3) {
			conditionBuilder.append(" and e.empid=" + emp.getEmpId());
		} else {
			throw new RuntimeException("对不起，您的权限不足！");
		}

		if (conditionBuilder.length() > 0) {
			count = count.replace("{condition}", conditionBuilder.toString());
			sql = sql.replace("{condition}", conditionBuilder.toString());
		} else {
			count = count.replace("{condition}", " ");
			sql = sql.replace("{condition}", " ");
		}

		final String _count = count;
		final String _sql = sql;

		super.commonDao.execute(new IHibernateCallback() {

			private static final long serialVersionUID = 7582287328942457212L;

			@SuppressWarnings("unchecked")
			@Override
			public Object doInHibernate(Session session) {
				
				Object cnt = session.createSQLQuery(_count).uniqueResult();
				
				if (cnt == null)
					return null;

				List<Object[]> list = session.createSQLQuery(_sql)
						.setFirstResult(
								(pi.getPageIndex() - 1) * pi.getPageSize())
						.setMaxResults(pi.getPageSize()).list();
				pi.setRecordCount(Integer.valueOf(cnt.toString()));
				pi.setPageCount((int) Math.ceil((pi.getRecordCount() + 0.0)
						/ pi.getPageSize()));
				pi.setResult(list);
				return null;
			}
		});
	}