java调用mysql方法
1.mysql方法
查询部门的上级公司
CREATE FUNCTION `queryCompanyId` (areaId VARCHAR(50)) RETURNS varchar(50) BEGIN DECLARE sTemp VARCHAR(50); DECLARE sTempChd VARCHAR(50); DECLARE deptType INT; SET sTemp = areaId; SET deptType = 0; WHILE deptType!=1 DO SET sTemp = CONCAT(sTemp,',',sTempChd); SELECT pid,type,id INTO areaId,deptType,sTemp FROM sys_department where id=areaId; END WHILE; RETURN sTemp; END
java 代码
public String findCompanyId(String deptId) throws Exception { String companyId = ""; Query query = this.getSession().createSQLQuery("select queryCompanyId(?)"); query.setParameter(0, deptId); List<String> list = query.list(); if(list != null && list.size()>0) { companyId = list.get(0); } return companyId; }