[Java] [Spring] [JDBC] [Oracle] How to call stored procedure - example 1

Assume I have a stored procedure as bellows:

create or replace PACKAGE PACKAGE1 AS 
     PROCEDURE TEST_HELLO 
    (
      I_NAME IN VARCHAR2,
      outParam1 OUT VARCHAR2,
      o_return_code OUT INT
    );
END PACKAGE1;

create or replace PACKAGE BODY PACKAGE1
AS
   PROCEDURE TEST_HELLO 
    (
      I_NAME IN VARCHAR2,
      outParam1 OUT VARCHAR2,
      o_return_code OUT INT
    ) AS 
    BEGIN
      outParam1 := 'Hello World! ' || I_NAME;
      DBMS_OUTPUT.put_line (outParam1);
      o_return_code := 0;
    END TEST_HELLO;
END PACKAGE1;

Step1. Create a custom repository interface:

package com.example.jpa.repository.custom;

import java.util.Map;

public interface EmployeeRepositoryCustom {
    Map<String, Object> sayHello(String name);
}

Step2. Create a custom implementation class:

package com.example.jpa.repository.impl;

import com.example.jpa.entity.Employee;
import com.example.jpa.repository.custom.EmployeeRepositoryCustom;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import java.util.Map;

@Slf4j
public class EmployeeRepositoryImpl implements EmployeeRepositoryCustom {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    
    @Override
    public Map<String, Object> sayHello(String name) {
        SimpleJdbcCall jdbcCall
                = new SimpleJdbcCall(jdbcTemplate.getJdbcTemplate().getDataSource())
                .withCatalogName("PACKAGE1")
                .withProcedureName("TEST_HELLO");

        Map<String, Object> output = jdbcCall.execute(name);
        output.entrySet().forEach(entry -> log.debug("key = {}, value = {}", entry.getKey(), entry.getValue()));

        return output;
    }
}

Step3. Create an repository interface

package com.example.jpa.repository;

import com.example.jpa.entity.Employee;
import com.example.jpa.repository.custom.EmployeeRepositoryCustom;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface EmployeeRepository extends CrudRepository<Employee, String>, EmployeeRepositoryCustom {
}

Step4. Create a test case:

package com.example.jpa;

import com.example.jpa.entity.Employee;
import com.example.jpa.repository.EmployeeRepository;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.junit.Assert;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@SpringBootTest
@Slf4j
public class TestEmployeeRepository {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Test
    public void testSayHello() {
        String name = "Albert Kuo";
        Map<String, Object> output = employeeRepository.sayHello(name);

        String message = (String) output.get("OUTPARAM1");
        int returnCode = ((BigDecimal) output.get("O_RETURN_CODE")).intValue();
        log.debug("message = {}, returnCode = {}", message, returnCode);

        Assert.assertEquals(0, returnCode);
        Assert.assertEquals("Hello World! " + name, message);
    }

}

Step5. Check result:

[           main] c.e.j.r.impl.EmployeeRepositoryImpl      : key = OUTPARAM1, value = Hello World! Albert Kuo
[           main] c.e.j.r.impl.EmployeeRepositoryImpl      : key = O_RETURN_CODE, value = 0
[           main] com.example.jpa.TestEmployeeRepository   : message = Hello World! Albert Kuo, returnCode = 0

[Spring] How to invoke a spring component dynamically

Requirement
Assume I have 2 job classes, I will use command line to execute with job name, and program invoke specific Job class to do something.

Command line looks like:

>java -jar batch-0.0.1-SNAPSHOT.jar -name Job1

The two Job class are as bellows:

package com.test.batch.job;

import com.test.batch.service.Service1;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Slf4j
@Component ("Job1")
public class Job1 implements Job{

    @Autowired
    private Service1 service1;

    public void execute() {
        log.debug("execute job1");
        service1.sayHello();
    }

}

package com.test.batch.job;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

@Component("Job2")
@Slf4j
public class Job2 implements Job {

    @Override
    public void execute() {
        log.debug("execute job2");
    }

}

Invoke specific Job class based on the argument which provide by user:

package com.test.batch.service;

import com.test.batch.job.Job;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Service;

@Slf4j
@Service
public class JobExecutor {

    @Autowired
    private ApplicationContext ctx;

    public void execute(String argument) {
        try {
            // get bean by name
            Job job = (Job) ctx.getBean(argument);
            job.execute();
        } catch (Exception e) {
            String error = "Fail to execute batch job, you provide job name is " + argument;
            log.error(error, e);
        }
    }

}

Command line runner class:

package com.test.batch;

import com.beust.jcommander.JCommander;
import com.test.batch.parameter.BatchJobArgument;
import com.test.batch.service.JobExecutor;
import com.google.common.base.Strings;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

@Slf4j
@Component
public class BatchCommandLineRunner implements CommandLineRunner {

    @Autowired
    private JobExecutor jobExecutor;

    @Override
    public void run(String... args) throws Exception {
        BatchJobArgument arguments = new BatchJobArgument();

        JCommander jCommander = JCommander.newBuilder().build();
        jCommander.addObject(arguments);
        jCommander.parse(args);

        if(arguments.isHelp()){
            jCommander.usage();
            return;
        }

        log.debug("arguments = {}", arguments);

        if(Strings.isNullOrEmpty(arguments.getName())) {
            log.debug("Please assign job name (ex. -name Job1)");
        }else{
            jobExecutor.execute(arguments.getName());
        }
    }

}

[Spring] How to pass command line argument to Spring Boot application via junit ?

Here has sample code:

package com.test.batch;

import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
class BatchApplicationTests {

 @Autowired
 private ApplicationContext ctx;

 @Test
 public void testCommandline() throws Exception {
  CommandLineRunner runner = ctx.getBean(CommandLineRunner.class);
  runner.run("-name", "Job2");
 }

}

[Spring Boot] How to implement Spring Boot Console Application

Maven Dependency

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

Then using Spring's @SpringBootApplication annotation on our main class to enable auto-configuration.

This class also implements Spring's CommandLineRunner interface. CommandLineRunner is a simple Spring Boot interface with a run method. Spring Boot will automatically call the run method of all beans implementing this interface after the application context has been loaded.

package com.test.batch;

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.Banner;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@Slf4j
@SpringBootApplication
public class BatchApplication implements CommandLineRunner {

    private final JobExecutor jobExecutor;

    public BatchApplication(JobExecutor jobExecutor) {
        this.jobExecutor = jobExecutor;
    }

    public static void main(String[] args) {
        SpringApplication app = new SpringApplication(BatchApplication.class);
        app.setBannerMode(Banner.Mode.OFF);
        app.run(args);
    }

    @Override
    public void run(String... args) {
        // receive arguments in run method
        if (args != null && args.length > 0) {
            String argument = args[0];
            log.debug("argument = {}", argument);
            jobExecutor.execute(argument);
        } else {
            log.debug("no argument found");
        }
    }

}

[Spring Boot] How to disable Spring logo banner

Problem
When starting my Spring Boot project, I always have Spring logo banner as bellows:

How-To
You need to modify spring main class.
As-is

package com.esb.batch;

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.Banner;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@Slf4j
@SpringBootApplication
public class BatchApplication implements CommandLineRunner {

    public static void main(String[] args) {
         SpringApplication.run(BatchApplication.class, args);
    }

}

To-be (set banner mode to off):

package com.esb.batch;

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@Slf4j
@SpringBootApplication
public class BatchApplication implements CommandLineRunner {

    public static void main(String[] args) {
        SpringApplication app = new SpringApplication(BatchApplication.class);
        app.setBannerMode(Banner.Mode.OFF);
        app.run(args);
    }
    
}

Check the result:

[Spring Boot] Enabling Swagger2

How To
1. Add dependency in your pom.xml

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

2. Add @EnableSwagger2 annotation

package tw.com.xxx.analysis;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@SpringBootApplication
@EnableSwagger2
public class AnalysisApplication {

    public static void main(String[] args) {
        SpringApplication.run(AnalysisApplication.class, args);
    }

}

3. Startup your spring boot project on the Tomcat port 8080

4. Check result: http://localhost:8080/swagger-ui.html
Swagger2 provides a user interface to access our RESTful web services via the web browser.

[Spring Boot] How to create custom repository

Process

1.0 Create Repository  interface

package tw.com.abc.analysis.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import tw.com.abc.analysis.entity.Goal;
import tw.com.abc.analysis.entity.GoalId;
import tw.com.abc.analysis.repository.custom.GoalRepositoryCustom;

import java.util.List;

@Repository
public interface GoalRepository extends CrudRepository<Goal, GoalId> {
}

1.1 Create Repository Custom interface

package tw.com.abc.analysis.repository.custom;

public interface GoalRepositoryCustom {
}

1.2 Create Repository Custom Imple. class

package tw.com.abc.analysis.repository.impl;

public class GoalRepositoryImpl implements GoalRepositoryCustom {
}

1.3 Repository class extends Repository Custom interface

package tw.com.abc.analysis.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import tw.com.abc.analysis.entity.Goal;
import tw.com.abc.analysis.entity.GoalId;
import tw.com.abc.analysis.repository.custom.GoalRepositoryCustom;

import java.util.List;

@Repository
public interface GoalRepository extends CrudRepository<Goal, GoalId>, GoalRepositoryCustom {
}

1.4 Write SQL statement and save in SQL file

1.5 Create Mapper Class

package tw.com.abc.analysis.vo.rowmapper;

import org.springframework.jdbc.core.RowMapper;
import tw.com.abc.analysis.vo.GoalVo;

import java.sql.ResultSet;
import java.sql.SQLException;

public class GoalVoRowMapper implements RowMapper<GoalVo> {

    @Override
    public GoalVo mapRow(ResultSet rs, int rowNum) throws SQLException {
        GoalVo vo = new GoalVo();
        vo.setItemId(rs.getString("itemId"));
        vo.setName(rs.getString("name"));
        vo.setYear(rs.getString("year"));
        vo.setMonth(rs.getString("month"));
        vo.setGoal(rs.getBigDecimal("goal"));
        return vo;
    }
}

1.6 Declare custom method in custom interface

package tw.com.abc.analysis.repository.custom;

import tw.com.abc.analysis.vo.GoalVo;

import java.io.IOException;
import java.util.List;

public interface GoalRepositoryCustom {

    List<GoalVo> findByYear(String year) throws IOException;

}

1.7 Implement custom class method

package tw.com.abc.analysis.repository.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import tw.com.abc.analysis.repository.custom.GoalRepositoryCustom;
import tw.com.abc.analysis.util.SqlFileLoader;
import tw.com.abc.analysis.vo.GoalVo;
import tw.com.abc.analysis.vo.rowmapper.GoalVoRowMapper;

import java.io.IOException;
import java.util.List;

public class GoalRepositoryImpl implements GoalRepositoryCustom {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    private SqlFileLoader sqlFileLoader;

    @Override
    public List<GoalVo> findByYear(String year) throws IOException {
        String sql = sqlFileLoader.getSqlFile("find_by_year.sql");

        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("year", year);

       return jdbcTemplate.query(sql, parameters, new GoalVoRowMapper());
    }
}