JAVA调用MySQL递归查询相关的存储过程引发的思考

最新推荐文章于 2021-07-15 19:12:35 发布

Funnee

最新推荐文章于 2021-07-15 19:12:35 发布

阅读量203

点赞数

分类专栏： JAVA MySQL

本文链接：https://blog.csdn.net/u010344410/article/details/102937507

版权

JAVA 同时被 2 个专栏收录

44 篇文章 1 订阅

订阅专栏

MySQL

4 篇文章 0 订阅

订阅专栏

一、前言

在实际开发中，偶遇一个需求，需要用MySQL查询树状结构数据，由于MySQL没有特定语法，因此决定采用存储过程或者函数实现。

方案变更

起初决定用函数进行递归查询，但是MySQL的函数无法执行动态SQL，后改为用存储过程实现。

二、代码实现

(2.1)起始方案：通过查询出所求的id串的集合串，在用这些id串查询出需要的结果集

过程名： get_child_nodes
入参： 表名，id字段名，父id字段名，父id值，所需查询层级数
出参： 所有id用 “,” 所拼的串

CREATE PROCEDURE `get_child_nodes`
  (IN `tableName` VARCHAR(100),
    IN `idKey` VARCHAR(50),
    IN `pIdKey` VARCHAR(50),
    IN `pId` VARCHAR(100),
    IN 'queryLevel' INT,
    OUT `result` VARCHAR(10000))

BEGIN
  SET result = '';
  SET nowLevel = 0;
  SET @sTempChd = pId;
  #将子id用逗号间隔拼成串写入变量@sTempChd
  SET @sqlcmd = CONCAT('SELECT group_concat(',idKey,') INTO @sTempChd FROM ',tableName,' where FIND_IN_SET(',pIdKey,',@sTempChd)>0;');

  WHILE @sTempChd is not null AND nowLevel < queryLevel DO
    SET nowLevel = nowLevel + 1;
    IF result != '' THEN
      SET result = concat(result,',',@sTempChd);
    ELSE
      SET result = @sTempChd;
    END IF;
    PREPARE stmt FROM @sqlcmd;
    EXECUTE stmt;
  END WHILE;
  select result;
END

JAVA端：采用EntityManager的调用存储过程查询的方式
优点：规范了出参，入参分别是什么
缺点：调用繁琐

@Service
public class OrganizationServiceImpl{
	@PersistenceContext
	private EntityManager em;
	public List<Organization> getChildrenOrgs(String id, int level){
		StoredProcedureQuery query = em.createStoredProcedureQuery ("get_child_nodes")
	                .registerStoredProcedureParameter ("tableName",String.class,ParameterMode.IN)
	                .registerStoredProcedureParameter ("idKey",String.class,ParameterMode.IN)
	                .registerStoredProcedureParameter ("pIdKey",String.class,ParameterMode.IN)
	                .registerStoredProcedureParameter ("pId",String.class,ParameterMode.IN)
	                .registerStoredProcedureParameter ("queryLevel",Integer.class,ParameterMode.IN)
	                .registerStoredProcedureParameter("result",String.class, ParameterMode.OUT)
	                .setParameter("tableName", "t_organization")
	                .setParameter("idKey", "id")
	                .setParameter("pIdKey", "parent_id")
	                .setParameter("pId", id)
	                .setParameter("queryLevel", level);
		query.execute ();
		String a = (String)query.getOutputParameterValue ("result");
		//省略通过a查询出所需的organizations的过程...
		return organizations;
	}
}

(2.2)优化方案：由于之前的方案需要和数据库交互两次，本方案在过程中就直接查出结果集

过程名： get_child_nodes
入参： 表名，id字段名，父id字段名，父id值，所需查询层级数
出参： 无

CREATE DEFINER=`root`@`%` PROCEDURE `get_child_nodes`(IN `tableName` VARCHAR(100),
    IN `idKey` VARCHAR(50),
    IN `pIdKey` VARCHAR(50),
    IN `pId` VARCHAR(100),
    IN `queryLevel` INT)
BEGIN
  SET @result = '';
  SET @nowLevel = 0;
  SET @sTempChd = pId;
  #将子id用逗号间隔拼成串写入变量@sTempChd
  SET @sqlcmd = CONCAT('SELECT group_concat(',idKey,') INTO @sTempChd FROM ',tableName,' where FIND_IN_SET(',pIdKey,',@sTempChd)>0;');
  SET @tableData = CONCAT('SELECT * FROM ',tableName,' where FIND_IN_SET(id,@result)>0;');
  
  WHILE @sTempChd is not null AND @nowLevel <= queryLevel DO
    SET @nowLevel = @nowLevel + 1;
    IF @result != '' THEN
    	#这步的意义在于将第一步中存放于@result的顶级父id清空
		IF @nowLevel = 2 THEN
			SET @result = '';
		END IF;
      SET @result = concat(@result,',',@sTempChd,',');
    ELSE
      SET @result = @sTempChd;
    END IF;
    PREPARE stmt FROM @sqlcmd;
    EXECUTE stmt;
  END WHILE;
  #@nowLevel > 1 意为第一步查询成功，说明成功找到子节点
  IF @nowLevel > 1 THEN
	PREPARE stmt FROM @tableData;
	EXECUTE stmt;
  END IF;
END

JAVA端：
①用JdbcTemplate创建存储过程(在应用启动时调用),这样可以不用维护创建存储过程的数据库脚本。
②新建一个StoredProceduresHelper存储过程辅助类，利用泛型调用存储过程相关方法。
优点：抽象出具体逻辑，便于维护。调用简洁易懂，便于扩展。
缺点：创建脚本在服务每次启动时都会调用一次，若同名会抛出异常。因为不想数据库单独设立一张表来存放是否含有该存储过程的信息，因此采用该方式。

辅助类：

@Conditional({GeneralServiceImplConditional.class, MysqlConditional.class})
@Component
public class StoredProceduresHelper {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @PersistenceContext
    private EntityManager em;

    private Logger logger = LoggerFactory.getLogger (StoredProceduresHelper.class);

    /**
     * 创建查询树状结构的存储过程
     */
    @Transactional
    public void createViewTreeDataProcedure(){
        String sql = "CREATE DEFINER=`root`@`%` PROCEDURE `get_child_nodes`(IN `tableName` VARCHAR(100),\n" +
                "    IN `idKey` VARCHAR(50),\n" +
                "    IN `pIdKey` VARCHAR(50),\n" +
                "    IN `pId` VARCHAR(100),\n" +
                "    IN `queryLevel` INT)\n" +
                "BEGIN\n" +
                "  SET @result = '';\n" +
                "  SET @nowLevel = 0;\n" +
                "  SET @sTempChd = pId;\n" +
                "  SET @sqlcmd = CONCAT('SELECT group_concat(',idKey,') INTO @sTempChd FROM ',tableName,' where FIND_IN_SET(',pIdKey,',@sTempChd)>0;');\n" +
                "  SET @tableData = CONCAT('SELECT * FROM ',tableName,' where FIND_IN_SET(id,@result)>0;');\n" +
                "  \n" +
                "  WHILE @sTempChd is not null AND @nowLevel <= queryLevel DO\n" +
                "    SET @nowLevel = @nowLevel + 1;\n" +
                "    IF @result != '' THEN\n" +
                "    IF @nowLevel = 2 THEN\n" +
                "      SET @result = '';\n" +
                "    END IF;\n" +
                "      SET @result = concat(@result,',',@sTempChd,',');\n" +
                "    ELSE\n" +
                "      SET @result = @sTempChd;\n" +
                "    END IF;\n" +
                "    PREPARE stmt FROM @sqlcmd;\n" +
                "    EXECUTE stmt;\n" +
                "  END WHILE;\n" +
                "  IF @nowLevel > 1 THEN\n" +
                "    PREPARE stmt FROM @tableData;\n" +
                "    EXECUTE stmt;\n" +
                "  END IF;\n" +
                "END";
        try{
            jdbcTemplate.execute (sql);
            logger.info ("get_child_nodes存储过程创建成功！");
        }catch (Exception e){
            logger.info (e.getMessage ());
        }
    }
	//通过泛型，传入类型参数的方式进行抽象
    public <T> List<T> queryViewTreeDataProcedure(String tableName, String idKey, String pIdKey, String pId, int level, Class clazz){
        List<T> result;
        try{
        	//这种方式就能获得存储过程中select...from...的结果集了
            result = em.createNativeQuery ("{call get_child_nodes(?,?,?,?,?)}", clazz)
                    .setParameter(1, tableName)
                    .setParameter(2, idKey)
                    .setParameter(3, pIdKey)
                    .setParameter(4, pId)
                    .setParameter (5, level)
                    .getResultList ();
        }catch(Exception e){
            logger.warn (e.getMessage ());
            result = Collections.EMPTY_LIST;
        }
        return result;
    }
}

调用处：精简了相当一部分代码

@Service
public class OrganizationServiceImpl{
	@Autowired
	private StoredProceduresHelper storedProceduresHelper;
	public List<Organization> getChildrenOrgs(String id, int level){
		List<Organization> organizations = storedProceduresHelper
			.queryViewTreeDataProcedure("t_organization","id","parent_id",id,level);
		return organizations;
	}
}

三、开发过程所遇到的问题

参考存储过程辅助类的相关代码：

@Conditional({GeneralServiceImplConditional.class, MysqlConditional.class})
@Component
public class StoredProceduresHelper {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    //...
    /**
     * 创建查询树状结构的存储过程
     */
    @Transactional
    public void createViewTreeDataProcedure(){//...}

    public <T> List<T> queryViewTreeDataProcedure(String tableName, String idKey, String pIdKey, String pId, int level, Class clazz){//...}
}

(3.1)使用静态方法，语句块

起初想要使用静态语句块(static{...})来实现创建存储过程的相关逻辑。结果注入进来的jdbcTemplate为null，静态语句块是同类一块加载的，在成员变量注入之前，因此为null。
解决方法： @Autowired 用在构造方法上,这样jdbcTemplate会同类一同加载，静态方法可以调用到,静态语句块仍然取不到对象。

@Conditional({GeneralServiceImplConditional.class, MysqlConditional.class})
@Component
public class StoredProceduresHelper {
    private static JdbcTemplate jdbcTemplate;
	@Autowired
	public StoredProceduresHelper (JdbcTemplate jdbcTemplate){
		StoredProceduresHelper .jdbcTemplate = jdbcTemplate;
	}
	public static void test(){
        System.out.println (jdbcTemplate);//调用时对象已被注入
    }
	public static void test(){
		System.out.println (jdbcTemplate);//null
	}
}

(3.2)使用new对StoredProceduresHelper进行实例化

测试时，new StoredProceduresHelper(),对其方法进行测试，发现成员变量均为null，未注入。

解决方法： 通过@Autowired注入StoredProceduresHelper

结论： new 的对象无法将spring容器对象注入自身(@Value,@Component,@Controller…)

Funnee

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
JAVA调用MySQL递归查询相关的存储过程引发的思考

CREATE PROCEDURE `get_child_nodes` (IN `tableName` VARCHAR(100), IN `idKey` VARCHAR(50), IN `pIdKey` VARCHAR(50), IN `pId` VARCHAR(100), IN 'queryLevel' INT, OUT `result` VARCHAR...
复制链接

扫一扫