Dynamic calling store procedure and store function in database
Dynamic calling store procedure and function
- Via Callable Statement
- Via SimpleJdbcCall spring-boot-starter-jdbc
See Code Snippets
- Store Procedure
DELIMITER $$ USE `training_sp`$$ DROP PROCEDURE IF EXISTS `create_product`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `create_product`(id VARCHAR(255), p_code VARCHAR(255),p_name VARCHAR(255),weight BIGINT) BEGIN INSERT INTO product(id, CODE,NAME,weight) VALUES(id,p_code,p_name,weight); END$$ DELIMITER ;
- Function
DELIMITER $$ USE `training_sp`$$ DROP FUNCTION IF EXISTS `count_product`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `count_product`() RETURNS BIGINT(20) BEGIN DECLARE v_count BIGINT DEFAULT 0; SELECT COUNT(1) INTO v_count FROM product; RETURN v_count; END$$ DELIMITER ;
- Implementation Using SimpleJdbcCall
@Component public class JdbcTemplateUtils { Logger logger = LoggerFactory.getLogger(JdbcTemplateUtils.class); private SimpleJdbcCall simpleJdbcCall; @Autowired @Qualifier("data_mysql") public void setDatasource(DataSource datasource){ this.simpleJdbcCall =new SimpleJdbcCall(datasource); } public void callStoreProcedure(String procedureName, Map parameters){ simpleJdbcCall.withProcedureName(procedureName); MapSqlParameterSource inParams = new MapSqlParameterSource(); if(null!=parameters) { for (Map.Entry parameter : parameters.entrySet()) { inParams.addValue(parameter.getKey(), parameter.getValue()); } } simpleJdbcCall.execute(inParams); logger.info("PROCEDURE {} IS CALLED",procedureName); } public Object callStoredFunction(String functionName, Map parameters, Class classreturn){ simpleJdbcCall.withFunctionName(functionName); simpleJdbcCall.withReturnValue(); MapSqlParameterSource inParams = new MapSqlParameterSource(); if(null!=parameters) { for (Map.Entry parameter : parameters.entrySet()) { inParams.addValue(parameter.getKey(), parameter.getValue()); } } logger.info("FUNCTION {} IS CALLED",functionName); return simpleJdbcCall.executeFunction(classreturn,inParams); } }
3.1 Calling the store procedure
Map params=new HashMap<>(); params.put("id",UUID.randomUUID().toString()); params.put("p_code",product.getCode()); params.put("p_name",product.getName()); params.put("weight",product.getWeight()); jdbcUtils.callStoreProcedure("create_product",params);
3.2 Calling the Stored function
Long count=(Long) jdbcUtils.callStoredFunction( "count_product",null,Long.class);
- Implementation using CallableStatement
/** * Created by krisna putra on 10/28/2017. */ @Component public class DatabaseUtils { Logger log= LoggerFactory.getLogger(DatabaseUtils.class); private final String callFunction = "{ ? = call #statement}"; private final String callProcedure = "{ call #statement}"; CallableStatement callableStatement; private DataSource dataSource; @Autowired @Qualifier("data_mysql") public void setDataSource(DataSource dataSource){ this.dataSource=dataSource; } public Object callStoredFunction(int sqlReturnType, String functionName, Object[] params){ try { callableStatement= dataSource.getConnection() .prepareCall( callFunction.replace("#statement",functionName) ); callableStatement.registerOutParameter(1,sqlReturnType); if(params!=null) { for (int i = 0; i < params.length; i++) { callableStatement.setObject((i+2),params[i]); } } callableStatement.execute(); log.info("FUNCTION {} is CALLED",functionName); return callableStatement.getObject(1); } catch (SQLException e) { log.error("Error Call Function {} ",functionName,e); }finally { try { if(callableStatement!=null){ callableStatement.close(); } }catch (Exception e2){ log.error("Error Closed Connection ",e2); } } return null; } public void callStoredProcedure( String procedureName, Object[] params){ try { callableStatement= dataSource.getConnection() .prepareCall( callProcedure.replace("#statement",procedureName) ); if(params!=null) { for (int i = 0; i < params.length; i++) { callableStatement.setObject((i+1),params[i]); } } callableStatement.execute(); log.info("PROCEDURE {} is CALLED",procedureName); } catch (SQLException e) { log.error("Error Call Procedure {} ",procedureName,e); }finally { try { if(callableStatement!=null){ callableStatement.close(); } }catch (Exception e2){ log.error("Error Closed Connection ",e2); } } } }
4.1 Call the Stored Procedure
Object[] params=new Object[]{ UUID.randomUUID().toString(), product.getCode(), product.getName(), product.getWeight() }; utils.callStoredProcedure("create_product(?,?,?,?)",params);
4.2 Call the Stored Function
Long count=(Long) utils.callStoredFunction(Types.BIGINT, "count_product()",null);
Done. Happy Coding;