Java 调用自定义函数SQL

最新推荐文章于 2025-05-25 16:57:29 发布

原创最新推荐文章于 2025-05-25 16:57:29 发布 · 3.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#call #function #SQL #Java #spring

Java 专栏收录该内容

38 篇文章

订阅专栏

本文介绍了如何使用MySQL的存储过程`getTreeChildList`来获取指定ID的子节点列表，通过调用存储过程并传入参数，实现高效的数据查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL function:

DELIMITER $$

USE `test`$$

DROP FUNCTION IF EXISTS `getTreeChildList`$$

CREATE DEFINER=`test`@`%` FUNCTION `getTreeChildList`(rootId VARCHAR(40)) RETURNS VARCHAR(5000) 
BEGIN
       DECLARE sTemp VARCHAR(5000);
       DECLARE sTempChd VARCHAR(5000);

       SET sTemp = '';
       SET sTempChd = rootId;

     WHILE sTempChd IS NOT NULL DO
         SET sTemp = CONCAT(sTemp,',',sTempChd);
         SELECT GROUP_CONCAT(CODE) INTO sTempChd FROM P_EVALUATE_STRUCTURE WHERE   FIND_IN_SET(parent,sTempChd)>0;
       END WHILE;
       RETURN sTemp;
    END$$

DELIMITER ;

Java

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<JSONObject> getCallDefinedFunctionSQL(String sysCode){
        final String callFunctionSql = "{?= call getTreeChildList(?)}";
        final String inputStr = sysCode;
        List<SqlParameter> params = new ArrayList<SqlParameter>();
        params.add(new SqlOutParameter("result", Types.LONGVARCHAR));
        params.add(new SqlParameter("str", Types.VARCHAR));
        Map<String, Object> outValues = jdbcTemplate.call(
              new CallableStatementCreator() {
              @Override
             public CallableStatement createCallableStatement(Connection conn) throws SQLException {
                        CallableStatement callableStatement = conn.prepareCall(callFunctionSql);
                        callableStatement.registerOutParameter(1, Types.LONGVARCHAR);
                        callableStatement.setString(2, inputStr);
                        return callableStatement;
         }}, params);
        System.out.println(outValues.get("result"));