# Spring Boot Using Hikari Connection Pool Connection Pool In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a […]
SpringBoot Archive
Dynamic Call Store Procedure and Function Database
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); } } } } |
[…]
Rest Documentation with Swagger And Spring boot
Simple Spring-boot Swagger Documentation Learning Swagger documentation for rest. To Add Swagger to your application add the depedencies
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger2</artifactId> <version>2.7.0</version> <scope>compile</scope> </dependency> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger-ui</artifactId> <version>2.7.0</version> <scope>compile</scope> </dependency> |
create SwaggerConfiguration class
|
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 |
@EnableSwagger2 @Configuration public class SwaggerConfig { @Bean public Docket productAPI(){ return new Docket(DocumentationType.SWAGGER_2) .select() .apis(RequestHandlerSelectors.basePackage("com.putracode")) .paths(PathSelectors.regex("/api.*")) .build() .apiInfo(metaInfo()); } private ApiInfo metaInfo(){ return new ApiInfo( "Spring boot Swagger", "Learning Swagger For Project", "1.0", "Tearm Of Service", new Contact("putracode", "www.putracode.com", "mr.krisna.putra@gmail.com"), "Apache License Version 2.0", "https://www.apache.org/license.html", new ArrayList<>() ); } } |
Add annotation swagger @ApiOperation on the Method endpoint. Example
|
1 2 3 4 5 6 7 8 |
@ApiOperation(value = "Get All Data Product", responseContainer = "List", produces = "application/json", response = Product.class) @GetMapping("/") public Iterable<Product> getProduct(){ return products; } |
@ApiModel and @ApiModelProperty this is used for tell consumer application about information about the property (response/ request) example […]
Implementation Dynamic Specification Using RSQL-Parser Example
Introducing Changes Is Enemy, But We Must Prepared . – I have been code with spring boot and thymeleaf, When the *User Interface * add a new search criteria the backend code (my repository, service and controller) must changed too. Honestly I don’t like that. to much changing :(. I try searching library to handle […]
Build Spring Boot in Eclipse Luna
What is SpringBooot Spring Boot makes it easy to create stand-alone, production-grade Spring based Applications that you can “just run”. We take an opinionated view of the Spring platform and third-party libraries so you can get started with minimum fuss. Most Spring Boot applications need very little Spring configuration. Requierements in this example 1. Java […]