hibernate与Mysql-Dialect问题

hibernate不能完全识别Mysql的函数方法，

扩展MySQL5Dialect并在Dialect里注册自定义函数

public class MySQLLocaDialect extends MySQL5Dialect {      public MySQLLocaDialect(){          super();        //注意isnum最好小写,反正我大写不通过        registerFunction("isnum", new SQLFunctionTemplate(Hibernate.INTEGER, "IsNum(?1,?2)"));          registerFunction("convert_gbk",                  new SQLFunctionTemplate(Hibernate.STRING, "convert(?1 using gbk)") );      }  }  

SQL文

isnum(str varchar(25),startNum int)--将字符串str从startNum开始截断如果截断的字符串全部为数字则返回该数字，不是则返回0

DELIMITER $$DROP FUNCTION IF EXISTS `IsNum` $$CREATE FUNCTION `IsNum` (str VARCHAR(25),startNum int) RETURNS integer BEGINDECLARE strtmp VARCHAR(25); DECLARE iResult INT DEFAULT 0;  IF ISNULL(str) THEN return 0; END IF;-- NULL 字符串 IF str = '' THEN return 0; END IF;-- 空字符串 set strtmp = SUBSTR(str,startNum);SELECT strtmp REGEXP '^[0-9]*$' INTO iResult; IF iResult = 1 THEN  RETURN CAST(strtmp as signed);ELSE RETURN 0; END IF; END $$ DELIMITER ;

最后在hibernate添加配置

hibernate.dialect=${mysql.hibernate.dialect}

方法调用

public int getMaxOrder(String prefixName) {StringBuffer hql = new StringBuffer();Iterator<String> reslut = null; hql.append("select max(isnum(user.username,10)) "); hql.append(" from User user where 1=1 "); hql.append(" and user.username like '"+prefixName+"%'");List users = getHibernateTemplate().find(hql.toString());if (users.isEmpty()) {return 0;}return StringUtil.objectToInteger(users.get(0)).intValue(); }

报错列表

No data type for node: org.hibernate.hql.ast.tree.AggregateNode

--->

No data type for node: org.hibernate.hql.ast.tree.MethodNode(记不清了)

然后按上调错下，就ok了

如果不想注册也可以使用原生SQL去做

public int queryMaxOrder(String prefixName) {StringBuffer hql = new StringBuffer();Iterator<String> reslut = null;  hql.append("select user.username from SYS_USER user where 1=1 "); hql.append(" and user.username regexp '"+"^"+prefixName+"[0-9]*$"+"'  order by user.username desc ");List users= this.getQueryHibernateTemplate().findBySQL(hql.toString(), null, -1, -1);if (users.isEmpty()) {return 0;}else{String username = users.get(0).toString();username=username.substring(prefixName.length());return StringUtil.objectToInteger(username).intValue();}}

<pre name="code" class="java">QueryHibernateTemplate 

import java.lang.reflect.Method;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import org.hibernate.HibernateException;import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.transform.Transformers;import org.springframework.orm.hibernate3.HibernateCallback;import org.springframework.orm.hibernate3.HibernateTemplate;import com.mb.exception.BusinessException;import com.mb.util.StringUtil;/** * hibernate模板类 * @author * */public class QueryHibernateTemplate extends HibernateTemplate implements IQuery{public QueryHibernateTemplate() {}public QueryHibernateTemplate(SessionFactory sessionFactory) { super(sessionFactory);}/** * 原型sql检索一览（分页） * * @author * @param queryModel * 检索条件 * @param firstResult * 开始结果数 * @param maxResults * 最大条数 * @return 返回检索数据 */public List findBySQL(final QueryModel queryModel, final int firstResult, final int maxResults) {if (queryModel==null) {return null;}return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException {// 创建queryorg.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(queryModel.getNativeSQL()); for (EntityModel model : queryModel.getEntityList()) { if(model.isReturn()) { query.addEntity(model.getAliasName(), model.getClazz()); }} Object[] values = queryModel.getParams().toArray();if (values != null) {for (int i = 0; i < values.length; i++) {query.setParameter(i, values[i]);}}prepareQuery(query);if(firstResult >=0) {//定义从第几条开始查询query.setFirstResult(firstResult);} if(maxResults > 0) {//定义返回的记录数 query.setMaxResults(maxResults);} List list = query.list(); return list; } });}/** * hql检索一览（分页） * * @author * @param queryModel * 检索条件 * @param firstResult * 开始结果数 * @param maxResults * 最大条数 * @return 返回检索数据 */public List findByHQL(final HQLQueryModel queryModel, final int firstResult, final int maxResults) {if (queryModel==null) {return null;}return this.executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { StringBuilder tempNativeHQL = new StringBuilder(); for (EntityModel model : queryModel.getEntityList()) { if(model.isReturn()) { if(StringUtil.indexOf(tempNativeHQL.toString(), "select")==-1){ tempNativeHQL.append(" select "+model.getAliasName() + " "); }else{ tempNativeHQL.append(" , "+ model.getAliasName() + " "); } }} tempNativeHQL.append(queryModel.getNativeHQL());Query query = session.createQuery(tempNativeHQL.toString());// 取出参数值 List<Object> values = queryModel.getParams();if (values != null) {for (int i = 0; i < values.size(); i++) {query.setParameter(i, values.get(i));}}prepareQuery(query);if(firstResult >=0) {//定义从第几条开始查询query.setFirstResult(firstResult);} if(maxResults > 0) {//定义返回的记录数 query.setMaxResults(maxResults);} return query.list(); } });}/** * 原型sql返回记录数(总数) * * @param queryModel * @return 总数 */public Object getRecordCount(final QueryModel queryModel) {if (queryModel == null) {return new Integer(0);} List list = getResultCountList(queryModel); if(list.size() > 0 ) { return new Integer(list.get(0).toString()); } else{ return new Integer(0); }}/** * hql返回记录数(总数) * * @param queryModel * @return 总数 */public Object getRecordCount(final HQLQueryModel queryModel) {if (queryModel == null) {return new Integer(0);}return execute(new HibernateCallback<Object>() { public Object doInHibernate(Session session) throws HibernateException, SQLException { String sql = queryModel.getCountHQL();Query query = session.createQuery(sql); List<Object> values = queryModel.getParams();if (values != null) {for (int i = 0; i < values.size(); i++) {query.setParameter(i, values.get(i));}}prepareQuery(query); Object obj = query.uniqueResult(); return obj; } }); }/** * 原型sql返回记录数(总数) * * @param queryModel * @return 总数 */private List getResultCountList(final QueryModel queryModel) {return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException {String sql= queryModel.getCountSQL();org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(sql); Object[] values = queryModel.getParams().toArray();if (values != null) {for (int i = 0; i < values.length; i++) {query.setParameter(i, values[i]);}}prepareQuery(query); List list = query.list(); return list; } }); }/** * 原型sql检索一览（分页） * * @param querySql * sql文 * @param queryModel * 参数 * @param firstResult * 开始结果数 * @param maxResults * 最大条数 * @return 返回检索数据 */public List findBySQL(final String querySql,final QueryModel queryModel, final int firstResult,final int maxResults) {if (StringUtil.isEmpty(querySql)) {return null;}return executeFind(new HibernateCallback<List>() {public List doInHibernate(Session session) throws HibernateException, SQLException {org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql); if(queryModel!=null){ for (EntityModel model : queryModel.getEntityList()) { if(model.isReturn()) { query.addEntity(model.getAliasName(), model.getClazz()); }} Object[] values = queryModel.getParams().toArray();if (values != null) {for (int i = 0; i < values.length; i++) {query.setParameter(i, values[i]);}} }prepareQuery(query);if(firstResult >=0) {//定义从第几条开始查询query.setFirstResult(firstResult);} if(maxResults > 0) {//定义返回的记录数 query.setMaxResults(maxResults);} List list = query.list(); return list; } }); }/** * 视图表检索一览的共通方法。 * @param querySql * @param firstResult * @param maxResults * @param clazz * 返回的class * @return List */public List findBySQL(final String querySql, final int firstResult,final int maxResults,final Class clazz) {if (StringUtil.isEmpty(querySql)) {return null;} return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql); prepareQuery(query); try { Method method= clazz.getMethod("setElementsType",org.hibernate.SQLQuery.class); method.invoke(clazz, query); } catch (Exception e) { e.printStackTrace(); return null; } if(firstResult >=0){ query.setFirstResult(firstResult);//定义从第几条开始查询 } if(maxResults > 0){ query.setMaxResults(maxResults);//定义返回的记录数 } query.setResultTransformer(Transformers.aliasToBean(clazz)); List list = query.list(); return list; } }); }/** * 视图表检索一览的共通方法。 * @param querySql * @param firstResult * @param maxResults * @param clazz * 返回的class * @return List */public List findBySQL(final String querySql,final QueryModel queryModel, final int firstResult,final int maxResults,final Class clazz) {if (StringUtil.isEmpty(querySql)) {return null;}return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException {org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql);prepareQuery(query);try { Method method= clazz.getMethod("setElementsType",org.hibernate.SQLQuery.class); method.invoke(clazz, query); } catch (Exception e) { new BusinessException(""); } if(queryModel!=null){ for (EntityModel model : queryModel.getEntityList()) { if(model.isReturn()) { query.addEntity(model.getAliasName(), model.getClazz()); }} Object[] values = queryModel.getParams().toArray();if (values != null) {for (int i = 0; i < values.length; i++) {query.setParameter(i, values[i]);}} }if(firstResult >=0) { query.setFirstResult(firstResult);//定义从第几条开始查询}if(maxResults > 0) { query.setMaxResults(maxResults);//定义返回的记录数 }query.setResultTransformer(Transformers.aliasToBean(clazz)); List list = query.list(); return list; } }); }/** * 视图表检索一览的共通方法。（根据视图中的方法不同得到值） * @param querySql * @param firstResult * @param maxResults * @param clazz * 返回的class * @return List */public List findBySQL(final String querySql, final int firstResult,final int maxResults,final Class clazz, final String setMethodName) {if (StringUtil.isEmpty(querySql)) {return null;} return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql); prepareQuery(query); try { if(StringUtil.isNullOrEmpty(setMethodName)) { Method method= clazz.getMethod("setElementsType", org.hibernate.SQLQuery.class); method.invoke(clazz, query); } else { Method method= clazz.getMethod(setMethodName, org.hibernate.SQLQuery.class); method.invoke(clazz, query); } } catch (Exception e) { e.printStackTrace(); return null; } if(firstResult >=0){ query.setFirstResult(firstResult);//定义从第几条开始查询 } if(maxResults > 0){ query.setMaxResults(maxResults);//定义返回的记录数 } query.setResultTransformer(Transformers.aliasToBean(clazz)); List list = query.list(); return list; } }); }/** * * 本地SQL查询结果转换成Map集合 * @param querySql sql语句 * @param queryModel 查询参数 * @param firstResult 开始记录数 * @param maxResults 最大记录数 * @return */public List<Map<String, Object>> findMapBySQL(final String querySql,final QueryModel queryModel, final int firstResult,final int maxResults) {if (StringUtil.isEmpty(querySql)) {return null;}return null;}/** * 原型sql返回记录数(总数) * @param querySQL * @param queryModel * @return 总数 */public Integer getRecordMapParamCount(final String querySQL , final Map<String,Object> params) {if (StringUtil.isEmpty(querySQL)) {return null;}// 检索总数List iCount = this.executeFind(new HibernateCallback<List>() {public List doInHibernate(Session session)throws HibernateException, SQLException {String countsql = "select count(*) ";countsql = countsql + querySQL;org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(countsql);// 添加参数if (params != null && !params.isEmpty()) {for (String key : params.keySet()) {query.setParameter(key, params.get(key));}}prepareQuery(query);List list = query.list();return list;}});if(iCount ==null || iCount.isEmpty()) {return new Integer(0); } else {return new Integer(iCount.get(0).toString()); }}/** * 通过检索条件查询数据一览 * @param querySql 检索sql * @param params 参数 * @param firstResult * @param maxResults * @return */public List<Object> findMapParamBySQL(final String querySql,final Map<String,Object> params, final int firstResult,final int maxResults) {if (StringUtil.isEmpty(querySql)) {return null;}return executeFind(new HibernateCallback<List<Map<String, Object>>>() {public List<Map<String, Object>> doInHibernate(Session session) throws HibernateException, SQLException {org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(querySql);// 添加参数if (params != null && !params.isEmpty()) {for (String key : params.keySet()) {query.setParameter(key, params.get(key));}}prepareQuery(query);if(firstResult >=0) { query.setFirstResult(firstResult);//定义从第几条开始查询}if(maxResults > 0) { query.setMaxResults(maxResults);//定义返回的记录数}// 设置返回结果集为MAP形式query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);List<Map<String, Object>> list = query.list();return list; } }); }/** * 原型sql返回记录数(总数) * @param querySQL * @param queryModel * @return 总数 */public Object getRecordCount(final String querySQL , final QueryModel queryModel) {if (StringUtil.isEmpty(querySQL)) {return new Integer(0);}List list = getRecordCountList(querySQL, queryModel); if(list.size() > 0 ) return new Integer(list.get(0).toString()); else return new Integer(0);}/** * 原型sql返回记录数(总数) * @param querySQL * @param queryModel * @return 总数 */private List getRecordCountList(final String querySQL,final QueryModel queryModel) {if (StringUtil.isEmpty(querySQL)) {return null;}return executeFind(new HibernateCallback<List>() { public List doInHibernate(Session session) throws HibernateException, SQLException { String countsql = "select count(*) ";countsql = countsql + querySQL;org.hibernate.SQLQuery query = (org.hibernate.SQLQuery) session.createSQLQuery(countsql);if(queryModel!=null){ Object[] values = queryModel.getParams().toArray();if (values != null) {for (int i = 0; i < values.length; i++) {query.setParameter(i, values[i]);}}}prepareQuery(query); List list = query.list(); return list; } }); }/** * 2级缓存的取得对象的方法 * @param sql * @return List */public List findCacheList(String sql,Object... params){if (StringUtil.isEmpty(sql)) {return null;} this.setCacheQueries(true); List list = null; if(params==null || params.length < 1) { list = this.find(sql); } else { list = this.find(sql, params); } this.setCacheQueries(false); return list;}/** * 2级缓存的取得对象的方法 * @param sql * @return List */public List findCacheListByNamedParam(String queryString, String[] paramNames, Object[] values) {if (StringUtil.isEmpty(queryString)) {return null;}this.setCacheQueries(true);List list = null;if(paramNames == null || paramNames.length < 1) {list = this.find(queryString);} else {list = this.findByNamedParam(queryString, paramNames, values);}this.setCacheQueries(false);return list;}/** * @see 根据纯hql实现翻页效果 * * @param iPaginatedList * 翻页实体 * @param fromHql * from带条件的hql * @param findObject * select的语句 * @param firstResult * 开始点 * @param maxResults * 最大的结果集 * @param params * hql的参数 * @return 返回翻页实体 */public IPaginatedList getRecordsPageByHQL(IPaginatedList iPaginatedList,String fromHql, String findObject,Object... params) {if (StringUtil.isEmpty(fromHql) || StringUtil.isEmpty(findObject)) {return iPaginatedList;}String countHql = "select count(*) ";// 根据纯hql取得一览的总数int count = this.getRecordCountByHQL(countHql + fromHql,params);List result = new ArrayList();if (count > 0) {// 根据纯hql实现翻页效果result = this.findObjectListByHQL(findObject + fromHql, iPaginatedList.getFirstRecordIndex(),iPaginatedList.getPageSize(), params);}iPaginatedList.setTotalNumberOfRows(count);iPaginatedList.setList(result);return iPaginatedList;}/** * @see 根据纯hql实现翻页效果 * @param hql * hql语句 * @param firstResult * 开始点 * @param maxResults * 最大的结果集 * @param params * hql的参数 * @return 返回翻页的结果集 */public List findObjectListByHQL(final String hql, final int firstResult,final int maxResults ,final Object... params){if (StringUtil.isEmpty(hql)) {return null;}List result = executeFind(new HibernateCallback<List>() {@Overridepublic List doInHibernate(Session session) throws HibernateException, SQLException {Query query = session.createQuery(hql);if(params!=null){for (int i = 0; i < params.length; i++) {query.setParameter(i, params[i]);}}prepareQuery(query);if (firstResult >= 0) {// 定义从第几条开始查询query.setFirstResult(firstResult);}if (maxResults > 0) {// 定义返回的记录数query.setMaxResults(maxResults);}List result = query.list();return result;}});return result;}/** * @see 根据纯hql取得一览的总数 * @param hql * hql语句 * @param params * hql的参数 * @return 返回翻页的结果集 */protected int getRecordCountByHQL(String hql ,Object... params){if (StringUtil.isEmpty(hql)) {return 0;}Object oCount= super.iterate(hql,params).next();if (oCount == null) {return 0;} else {return ((Long) oCount).intValue();}}}