spring-boot-call-store-procedure Dynamic calling store procedure and store function in database Dynamic calling store procedure and function 1. Via Callable Statement 2. Via SimpleJdbcCall spring-boot-starter-jdbc See Code Snippets Store Procedure
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
/** * Created by krisna putra on 10/30/2017. */ @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<String,Object> parameters){ simpleJdbcCall.withProcedureName(procedureName); MapSqlParameterSource inParams = new MapSqlParameterSource(); if(null!=parameters) { for (Map.Entry<String, Object> parameter : parameters.entrySet()) { inParams.addValue(parameter.getKey(), parameter.getValue()); } } simpleJdbcCall.execute(inParams); logger.info("PROCEDURE {} IS CALLED",procedureName); } public Object callStoredFunction(String functionName, Map<String,Object> parameters, Class<?> classreturn){ simpleJdbcCall.withFunctionName(functionName); simpleJdbcCall.withReturnValue(); MapSqlParameterSource inParams = new MapSqlParameterSource(); if(null!=parameters) { for (Map.Entry<String, Object> 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
|
1 2 3 4 5 6 |
Map<String,Object> 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
|
1 |
Long count=(Long) jdbcUtils.callStoredFunction( "count_product",null,Long.class); |
Implementation using CallableStatement
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
package com.putracode.sp.utils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.stereotype.Component; import javax.sql.DataSource; import java.sql.CallableStatement; import java.sql.SQLException; /** * 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); } } } } |
[…]
