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 

BEGIN
DECLARE 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);}