项目产品要求运行在不同的数据库上,目前暂定为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; } }); }