数据库函数:
DELIMITER $$
CREATE DEFINER=`user`@`%` FUNCTION `queryChildrenType`(ppid varchar(32)) RETURNS text CHARSET utf8
BEGIN
DECLARE sTemp text;
DECLARE sTempChd text;
SET sTemp = "";
SET sTempChd = cast(ppid as char);
WHILE sTempChd is not NULL DO
if ISNULL(sTemp) || LENGTH(trim(sTemp))<1 THEN
SET sTemp = sTempChd;
ELSE
SET sTemp = CONCAT(sTemp,',',sTempChd);
END IF;
SELECT group_concat(id) INTO sTempChd FROM qs_airdata_materialtype where FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
return sTemp;
END
dao层函数(result即为所有的id):
public String queryChildren(String id) {
Session session = getHibernateTemplate().getSessionFactory()
.openSession();
try {
String hql = "select queryChildrenType(?)";
String result = null;
SQLQuery query = session.createSQLQuery(hql);
query.setParameter(0, id);
result = (String) query.uniqueResult();
return result;
} finally {
session.close();
}
}