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