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

Assume I have a stored procedure as bellows:

create or replace PACKAGE PACKAGE1 AS 
     PROCEDURE TEST_EMPLOYEE 
    (
      I_GENDER IN VARCHAR2 DEFAULT 'M',
      O_RESULT OUT SYS_REFCURSOR,
      o_return_code OUT INT
    );
END PACKAGE1;

create or replace PACKAGE BODY PACKAGE1
AS
   PROCEDURE TEST_EMPLOYEE 
    (
      I_GENDER IN VARCHAR2 DEFAULT 'M',
      O_RESULT OUT SYS_REFCURSOR,
      o_return_code OUT INT 
    ) AS 
    BEGIN
      OPEN O_RESULT FOR 
      SELECT ID, NAME, PHONE, ADDRESS, GENDER
      FROM EMPLOYEE 
      WHERE GENDER = I_GENDER;
      o_return_code := 0;
    END TEST_EMPLOYEE;
END PACKAGE1;

Step1. Create a custom repository interface:

package com.example.jpa.repository.custom;

import java.util.Map;

public interface EmployeeRepositoryCustom {

    Map<String, Object> findEmployee(String gender);

}

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> findEmployee(String gender) {
        SimpleJdbcCall jdbcCall
                = new SimpleJdbcCall(jdbcTemplate.getJdbcTemplate().getDataSource())
                .withCatalogName("PACKAGE1")
                .withProcedureName("TEST_EMPLOYEE")
                .returningResultSet("O_RESULT", 
                                    BeanPropertyRowMapper.newInstance(Employee.class));

        SqlParameterSource input = new MapSqlParameterSource().addValue("I_GENDER", gender);

        Map<String, Object> output = jdbcCall.execute(input);
        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 testFindEmployee() {
        Map<String, Object> output = employeeRepository.findEmployee("F");

        List<Employee> employees = (List<Employee>) output.get("O_RESULT");
        for (int i = 0; i < employees.size(); i++) {
            log.debug("employee = {}", ToStringBuilder.reflectionToString(employees.get(i)));
        }

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

}

Step5. Check result:

[           main] c.e.j.r.impl.EmployeeRepositoryImpl      : key = O_RESULT, value = [com.example.jpa.entity.Employee@2adbd899, com.example.jpa.entity.Employee@d6831df]
[           main] c.e.j.r.impl.EmployeeRepositoryImpl      : key = O_RETURN_CODE, value = 0
[           main] com.example.jpa.TestEmployeeRepository   : employee = com.example.jpa.entity.Employee@77ccded4[id=2,name=Mandy,phone=0911111111,address=Taipei,gender=F]
[           main] com.example.jpa.TestEmployeeRepository   : employee = com.example.jpa.entity.Employee@2bb717d7[id=3,name=Gina,phone=0911111111,address=ChiaYi,gender=F]
[           main] com.example.jpa.TestEmployeeRepository   : returnCode = 0

[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

[Java] [IntelliJ] [JPA] Cannot resolve table 'xxxx'

Problem
As I create entity class in my JPA project in IntelliJ, it complained cannot resolve table 'Product' as following:

How-To
Step1. Configure database connection

Step2. Assign Datasource

Step3. Check result

[Java] [IntelliJ] Generate Entity Class from Database

1. Created test project using Spring Boot

2. Right clicked the project and “Add Framework Support” and select “JavaEE Persistence(2.0)” and chose some settings and clicked "OK"

3. View  => Tool Windows => Database, and connected to my local database (i.e. Oracle). 

4. View  => Tool Windows => Persistence, right clicked project and go to “Generated Persistence Mapping” and go to click the “By Database Schema”.

5. Select table and generate entity class(es).

[Java] [Freemarker] How to ignore certain white-space

Problem
Here is my original ftl file:

<#if employees??>
<#list employees as e>
    ${e_index + 1}. ${e.name?right_pad(10)} - 
    <#switch e.gender>
        <#case "M">男<#break>
        <#case "F">女<#break>
        <#default>No matching gender found.
    </#switch>
</#list>
<#else>
No data found.
</#if>

But it had unexpected outcome:

    1. Albert     -
男    2. Mandy      -
女    3. Mia        -
女    4. Eric       -
男

How-To
Making use of #rt to ignore all railing white-space and add one enter at the end of switch. The updated ftl file looks like:

<#if employees??>
<#list employees as e>
    <#-- using <#rt> (for right trim) to ignore all 
         trailing white-space in this line. -->
    ${e_index + 1}. ${e.name?right_pad(10)} - <#rt>
    <#switch e.gender>
        <#case "M">男<#break>
        <#case "F">女<#break>
        <#default>No matching gender found.
    </#switch>
    <#-- 多按一個 enter 才會換行 -->

</#list>
<#else>
No data found.
</#if>

Check the result:

    1. Albert     - 男
    2. Mandy      - 女
    3. Mia        - 女
    4. Eric       - 男

Reference
[1] https://freemarker.apache.org/docs/ref_directive_t.html

[Java] [Freemarker] Using macro to reuse common template fragments

When you find yourself copy-pasting common parts between templates a lot, you should probably use macros.

Assume I create a utils.ftl in /resources/ftl/common as bellows:

<#macro myPage>
    <html>
    <head>
        <title>${title}</title>
    </head>
    <body>
    <h1>${title}</h1>

    <#-- This processes the enclosed content:  -->
    <#nested>

    </body>
    </html>
</#macro>

<#macro otherExample p1 p2>
    <p>The parameters were: ${p1}, ${p2}</p>
</#macro>

helloWorld.ftl will looks like:

<#-- import common template fragments -->
<#import "common/utils.ftl" as u>

<@u.myPage>
    <p>${example.name} by ${example.developer}</p>
    <#-- Just another example of using a macro: -->
    <@u.otherExample p1="Albert" p2="Mandy" />

    <#if systems??>
    <ul>
        <#list systems as system>
            <li>${system_index + 1}. ${system.name} from ${system.developer}</li>
        </#list>
    </ul>
    <#else>
    No data found!
    </#if>

</@u.myPage>

Test case:

package com.esb.batch;

import com.esb.batch.exception.FtlException;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import lombok.Builder;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Writer;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class FtlTest {

    private Configuration cfg;

    @Before
    public void init() {
        cfg = new Configuration();
        // 設定到 classpath 讀取 ftl file
        cfg.setClassForTemplateLoading(this.getClass(), "/");
    }

    @Test
    public void testHelloWorld() {
        try (Writer file = new FileWriter(new File("C:/ftl_helloWorld.html"));) {
            Template template = cfg.getTemplate("ftl/helloWorld.ftl");

            Map<String, Object> data = new HashMap<>();
            data.put("title", "Freemarker Example");
            data.put("example", ValueExampleObject.builder().name("Java Object").developer("Albert").build());

            List<ValueExampleObject> systems = new ArrayList<>();
            systems.add(ValueExampleObject.builder().name("Android").developer("Google").build());
            systems.add(ValueExampleObject.builder().name("iOS").developer("Apple").build());
            systems.add(ValueExampleObject.builder().name("Ubuntu").developer("Canonical").build());
            systems.add(ValueExampleObject.builder().name("Windows").developer("Microsoft").build());
            data.put("systems", systems);

            template.process(data, file);
        } catch (IOException | TemplateException e) {
            throw new FtlException("fail to generate file from ftl file : " + e.getMessage(), e);
        }
    }

    @Getter
    @Builder
    public static class ValueExampleObject {
        private String name;
        private String developer;
    }

}

Check the result:

<html>

<head>
    <title>Freemarker Example</title>
</head>

<body>
    <h1>Freemarker Example</h1>

    <p>Java Object by Albert</p>
    <p>The parameters were: Albert, Mandy</p>

    <ul>
        <li>1. Android from Google</li>
        <li>2. iOS from Apple</li>
        <li>3. Ubuntu from Canonical</li>
        <li>4. Windows from Microsoft</li>
    </ul>

</body>

</html>

[Oracle] How to export package and package body into sql file?

Step1. Make sure you select both (package and package body), and click export

Step2. Assign export destination and click next

Step3. Click Complete

Step4. Check result