Hibernate使用MySQL自定义函数

Hibernate使用MySQL自定义函数

构建函数

1. 新建表

CREATE TABLE `sys_user` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `name` varchar(50) NOT NULL COMMENT '登录名',
  `parent_id` varchar(32) DEFAULT NULL COMMENT '父id',
  PRIMARY KEY (`id`),
  KEY `FKl6y9o1vwawvu2w5o3pgioj3yd` (`parent_id`),
  CONSTRAINT `FKl6y9o1vwawvu2w5o3pgioj3yd` FOREIGN KEY (`parent_id`) REFERENCES `sys_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 测试数据

INSERT INTO `sys_user` VALUES ('0', '0', null);
INSERT INTO `sys_user` VALUES ('10', '10', '0');
INSERT INTO `sys_user` VALUES ('1001', '1001', '10');
INSERT INTO `sys_user` VALUES ('100101', '100101', '1001');
INSERT INTO `sys_user` VALUES ('10010101', '10010101', '100101');
INSERT INTO `sys_user` VALUES ('1001010101', '1001010101', '10010101');
INSERT INTO `sys_user` VALUES ('10010102', '10010102', '100101');
INSERT INTO `sys_user` VALUES ('100102', '100102', '1001');
INSERT INTO `sys_user` VALUES ('1002', '1002', '10');
INSERT INTO `sys_user` VALUES ('20', '20', '0');
INSERT INTO `sys_user` VALUES ('2001', '2001', '20');
INSERT INTO `sys_user` VALUES ('200101', '200101', '2001');
INSERT INTO `sys_user` VALUES ('20010101', '20010101', '200101');
INSERT INTO `sys_user` VALUES ('20010102', '20010102', '200101');
INSERT INTO `sys_user` VALUES ('200102', '200102', '2001');
INSERT INTO `sys_user` VALUES ('2002', '2002', '20');
INSERT INTO `sys_user` VALUES ('2003', '2003', '20');
INSERT INTO `sys_user` VALUES ('30', '30', '0');
INSERT INTO `sys_user` VALUES ('3001', '3001', '30');
INSERT INTO `sys_user` VALUES ('3002', '3002', '30');
INSERT INTO `sys_user` VALUES ('300201', '300201', '3002');
INSERT INTO `sys_user` VALUES ('300202', '300202', '3002');
INSERT INTO `sys_user` VALUES ('300203', '300203', '3002');
INSERT INTO `sys_user` VALUES ('30020301', '30020301', '300203');

3. 执行函数生成语句

DROP FUNCTION IF EXISTS `getChildList`;
-- 不写这一句有时候会报错
set global log_bin_trust_function_creators=TRUE;
-- 创建函数
CREATE FUNCTION `getChildList`(rootId VARCHAR(100)) RETURNS VARCHAR(10000) CHARSET utf8
BEGIN
   -- 声明变量
   DECLARE sChildList VARCHAR(10000);
   DECLARE sChildTemp VARCHAR(1000);
   -- 赋值
   SET sChildTemp =rootId;
   -- 具体实现逻辑
   WHILE sChildTemp IS NOT NULL DO
	 IF (sChildList IS NOT NULL) THEN 
		SET sChildList = CONCAT(sChildTemp,',',sChildList); 
	 ELSE 
		SET sChildList = CONCAT(sChildTemp); 
	 END IF; 
     -- 结果处理
     SELECT GROUP_CONCAT(id) INTO sChildTemp FROM sys_user WHERE FIND_IN_SET(parent_id, sChildTemp)>0;
   END WHILE;
   RETURN sChildList;
 END

4. 测试一下

SELECT * FROM sys_user WHERE FIND_IN_SET(id,getChildList('10'))>0

5. 结果
   

使用MySQL方言将自定义函数引入到Hibernate中

public class MyLocalDialect extends MySQL5Dialect {

    public MyLocalDialect() {
        super();
        // 启动项目时可以看到打说明成功
        System.out.println("MyLocalDialect");     
        registerFunction("getChildList", new SQLFunctionTemplate(StandardBasicTypes.STRING,"getChildList(?1)"));
    }
}

使用自定义的MySQL方言

 <prop key="hibernate.dialect">com.demo.util.MyLocalDialect</prop>

Hql

select u from User u where 1 = 1 and FIND_IN_SET(u.id,getChildList(:userId))>0