Dynamic calling store procedure and store function in database
Switch branches/tags
Nothing to show
Clone or download
Latest commit 13503a8 Oct 30, 2017
Permalink
Failed to load latest commit information.
src Initial commit Oct 30, 2017
.gitignore Initial commit Oct 30, 2017
README.md add read me Oct 30, 2017
mvnw Initial commit Oct 30, 2017
mvnw.cmd Initial commit Oct 30, 2017
pom.xml Initial commit Oct 30, 2017

README.md

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

  1. 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 ;
  1. 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 ;
  1. 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); 
  1. 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;