Spring boot 3 call stored procedure and function from PostgreSQL
12 min readMay 31, 2024
Spring Boot supports various databases, such as Oracle, MySQL, MongoDB, and PostgreSQL. It provides a call-stored procedure and function via a JDBC template or JPA(Java Persistence API).
In real life, it usually uses stored procedures and functions for batch processes or specific business logic, such as managing logs or monthly reports.
The developer can read more about PostgreSQL.
Create stored procedures and functions with a PostgreSQL link
Create a PostgreSQL database server with a docker link
Add dependencies in pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>Add configuration connection database PostgreSQL in the application.properties file.
# ===============================
# = DATA SOURCE
# ===============================
spring.datasource.url=jdbc:postgresql://localhost:5432/demo
spring.datasource.username=admin
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.DriverCreate RESTful web services
import jakarta.persistence.Column;
import jakarta.persistence.Id;
import lombok.Getter;
import lombok.Setter;
import java.time.LocalDateTime;
import java.util.Date;
@Getter
@Setter
public class UserProfilesBean {
@Id
@Column(name = "id")
String id;
@Column(name = "first_name")
String firstName;
@Column(name = "last_name")
String lastName;
@Column(name = "email")
String email;
@Column(name = "birth_date")
Date birthDate;
@Column(name = "sex")
long sex;
@Column(name = "create_by")
String createBy;
@Column(name = "create_date")
LocalDateTime createDate;
@Column(name = "update_by")
String updateBy;
@Column(name = "update_date")
LocalDateTime updateDate;
}import com.example.demo.bean.UserProfilesBean;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.ArrayList;
import java.util.List;
@RestController
public class UserProfilesController {
@Autowired
UserProfileServiceInf userProfileServiceInf;
@GetMapping(path = "/user-profiles/{userid}", produces = "application/json")
public ResponseEntity<Object> getUserprofile(@PathVariable String userid) {
//call service
List<UserProfilesBean> list = userProfileServiceInf.inqUserProfile(userid);
return new ResponseEntity<>(list, HttpStatus.OK);
}
}Example call stored procedures using JDBC template
Create a service layer to get user profiles from the database using JdbcTemplate.
CREATE OR REPLACE PROCEDURE get_user_profiles(in user_id CHARACTER VARYING, out ref refcursor)
LANGUAGE plpgsql
AS $$
BEGIN
-- Open a cursor and select all rows from user_profiles
OPEN ref FOR
SELECT * FROM user_profiles
where user_profiles.id::text = user_id;
END;
$$;import com.example.demo.bean.UserProfilesBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@Service
public class UserProfileServiceImpl implements UserProfileServiceInf {
private final JdbcTemplate jdbcTemplate;
@Autowired
public UserProfileServiceImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public List<UserProfilesBean> inqUserProfile(String userId) {
List<UserProfilesBean> list = new ArrayList<>();
Connection connection = null;
try {
connection = jdbcTemplate.getDataSource().getConnection();
connection.setAutoCommit(false);
CallableStatement call = connection.prepareCall("call get_user_profiles(?, ?)");
call.setString(1, userId);
call.registerOutParameter(2, Types.OTHER);
call.execute();
ResultSet rs = (ResultSet) call.getObject(2);
while (rs.next()) {
UserProfilesBean up = new UserProfilesBean();
System.out.println("userId=" + rs.getObject("id"));
up.setId(rs.getString("id"));
up.setFirstName(rs.getString("first_name"));
up.setLastName(rs.getString("last_name"));
up.setEmail(rs.getString("email"));
up.setBirthDate(rs.getDate("birth_date"));
up.setSex(rs.getLong("sex"));
up.setCreateBy(rs.getString("create_by"));
up.setCreateDate(rs.getTimestamp("create_date") != null ? rs.getTimestamp("create_date").toLocalDateTime() : null);
up.setUpdateBy(rs.getString("update_by"));
up.setUpdateDate(rs.getTimestamp("update_date") != null ? rs.getTimestamp("update_date").toLocalDateTime() : null);
list.add(up);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
}Send GET request URL with user id path parameter.
http://localhost:8080/user-profiles/18555d30-eba0-4077-a080-ce4eabe15bfd
[
{
"id": "18555d30-eba0-4077-a080-ce4eabe15bfd",
"firstName": "John ",
"lastName": "Doh ",
"email": "demo@example.com ",
"birthDate": "1989-01-06",
"sex": 1,
"createBy": "f6fd6894-52ed-49b4-8a30-e6641d8408d3",
"createDate": "2024-05-15T08:50:16.747656",
"updateBy": null,
"updateDate": null
}
]Example dynamic mapping column from the resultset.
Create custom column annotation in package com.example.demo.utils.
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {
String name();
Class<?> type() default String.class; // Default to String type
}Create a resultset mapper class.
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.UUID;
import com.example.demo.utils;
public class ResultSetMapper {
public static <T> T mapResultSetToObject(ResultSet rs, Class<T> outputClass) throws SQLException {
T obj = null;
try {
obj = outputClass.getDeclaredConstructor().newInstance();
Field[] fields = outputClass.getDeclaredFields();
String columnName = null;
for (Field field : fields) {
if (field.isAnnotationPresent(Column.class)) {
Column column = field.getAnnotation(Column.class);
columnName = column.name();
Class<?> columnType = column.type();
Object value = getValueByType(rs, columnName, columnType);
if (null == value) continue;
field.setAccessible(true);
field.set(obj, value);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return obj;
}
private static Object getValueByType(ResultSet rs, String columnName, Class<?> columnType) throws SQLException {
try {
if (columnType == String.class) {
String value = rs.getString(columnName);
return null == value || value.isEmpty() ? null : value;
} else if (columnType == UUID.class) {
UUID value = (UUID) rs.getObject(columnName);
return null == value ? null : String.valueOf(value);
} else if (columnType == int.class || columnType == Integer.class) {
return rs.getInt(columnName);
} else if (columnType == long.class || columnType == Long.class) {
return rs.getLong(columnName);
} else if (columnType == double.class || columnType == Double.class) {
return rs.getDouble(columnName);
} else if (columnType == float.class || columnType == Float.class) {
return rs.getFloat(columnName);
} else if (columnType == boolean.class || columnType == Boolean.class) {
return rs.getBoolean(columnName);
} else if (columnType == Date.class) {
return rs.getDate(columnName);
} else if (columnType == Timestamp.class) {
return rs.getTimestamp(columnName) != null ? rs.getTimestamp(columnName).toLocalDateTime() : null;
} else {
return rs.getObject(columnName);
}
} catch (Exception ex) {
//skip column not found from stored procedure
System.out.println("error message = " + ex.getMessage());
System.out.println("columnName not found = " + columnName);
return null;
}
}
}Modify bean java class using custom column annotation.
import com.example.demo.utils.Column;
@Getter
@Setter
public class UserProfilesBean {
@Column(name = "id", type = UUID.class)
String id;
@Column(name = "first_name", type = String.class)
String firstName;
@Column(name = "last_name", type = String.class)
String lastName;
@Column(name = "email", type = String.class)
String email;
@Column(name = "birth_date", type = Date.class)
Date birthDate;
@Column(name = "sex", type = Long.class)
long sex;
@Column(name = "create_by", type = UUID.class)
String createBy;
@Column(name = "create_date", type = Timestamp.class)
LocalDateTime createDate;
@Column(name = "update_by", type = UUID.class)
String updateBy;
@Column(name = "update_date", type = Timestamp.class)
LocalDateTime updateDate;
}Modify the method in the UserProfileServiceImpl class.
public List<UserProfilesBean> inqUserProfile(String userId) {
List<UserProfilesBean> list = new ArrayList<>();
Connection connection = null;
try {
connection = jdbcTemplate.getDataSource().getConnection();
connection.setAutoCommit(false);
CallableStatement call = connection.prepareCall("call get_user_profiles(?, ?)");
call.setString(1, userId);
call.registerOutParameter(2, Types.OTHER);
call.execute();
ResultSet rs = (ResultSet) call.getObject(2);
while (rs.next()) {
UserProfilesBean obj = ResultSetMapper.mapResultSetToObject(rs, UserProfilesBean.class);
list.add(obj);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}Send GET request URL with user id path parameter.
http://localhost:8080/user-profiles/18555d30-eba0-4077-a080-ce4eabe15bfd
[
{
"id": "18555d30-eba0-4077-a080-ce4eabe15bfd",
"firstName": "John",
"lastName": "Doh",
"email": "demo@example.com",
"birthDate": "1989-01-06",
"sex": 1,
"createBy": "f6fd6894-52ed-49b4-8a30-e6641d8408d3",
"createDate": "2024-05-15T08:50:16.747656",
"updateBy": null,
"updateDate": null
}
]Alternatively, The developer can send a parameter class to the resultset, but the class from the stored procedure must match the Java class.
such as the difference between java.util.Date and java.sql.Date
Resultset returns a timestamp, but Java bean using LocalDateTime causes an error. The developer should decide which way is suitable for the project.
private static Object getValueByType(ResultSet rs, String columnName, Class<?> columnType){
return rs.getObject(columnName, columnType);
}Example call function using JDBC template
Add calculate age method to user profile service.
//UserProfileServiceImpl.java
public int calculateAge(
Date birthDate, Date dateTo) {
String sql = "SELECT calculate_age(?,?)";
Object[] params = {birthDate, dateTo};
int[] types = {Types.DATE, Types.DATE};
return jdbcTemplate.queryForObject(sql, params, types, Integer.class);
}Add calculate age method to user profile controller.
@GetMapping(path = "/cal-age/{birthdate}", produces = "application/json")
public ResponseEntity<Object> calculateAge(@PathVariable String birthdate) {
String format = "yyyy-MM-dd";
int age = 0;
if (birthdate == null) return null;
SimpleDateFormat sdf = new SimpleDateFormat(format);
try {
Date birthdateInput = sdf.parse(birthdate);
age = userProfileServiceInf.calculateAge(birthdateInput , new Date());
} catch (ParseException e) {
throw new RuntimeException(e);
}
return new ResponseEntity<>(age, HttpStatus.OK);
}Send GET request URL with birthdate path parameter.
http://localhost:8080/cal-age/1986–06–01
36Example call stored procedures using EntityManager
Create a repository or service layer using EntityManager for the call stored procedure.
@PersistenceContext
private EntityManager entityManager;
@Transactional
public List<UserProfilesBean> inqUserProfile(String userId) {
List<UserProfilesBean> list = new ArrayList<>();
// Create stored procedure query
StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("get_user_profiles");
// Set input parameters
storedProcedure.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
storedProcedure.setParameter(1, userId);
// Register the output parameter
storedProcedure.registerStoredProcedureParameter(2, void.class, ParameterMode.REF_CURSOR);
// Execute the stored procedure
storedProcedure.execute();
// Get the output parameter result
List<Object[]> results = storedProcedure.getResultList();
list = results.stream()
.map(row -> {
UserProfilesBean up = new UserProfilesBean();
up.setId(String.valueOf(row[0]));
up.setFirstName((String) row[1]);
up.setLastName((String) row[2]);
up.setEmail((String) row[3]);
up.setBirthDate((java.sql.Date) row[4]);
up.setSex(new BigDecimal(row[5].toString()).longValue());
up.setCreateBy(String.valueOf(row[6]));
up.setCreateDate(row[7] != null ? ((java.sql.Timestamp) row[7]).toLocalDateTime() : null);
up.setUpdateBy(String.valueOf(row[8]));
up.setUpdateDate(row[9] != null ? ((java.sql.Timestamp) row[9]).toLocalDateTime() : null);
return up;
})
.collect(Collectors.toList());
return list;
}Send GET request URL with user id path parameter.
http://localhost:8080/user-profiles/18555d30-eba0-4077-a080-ce4eabe15bfd
[
{
"id": "18555d30-eba0-4077-a080-ce4eabe15bfd",
"firstName": "John ",
"lastName": "Doh ",
"email": "demo@example.com ",
"birthDate": "1989-01-06",
"sex": 1,
"createBy": "f6fd6894-52ed-49b4-8a30-e6641d8408d3",
"createDate": "2024-05-15T08:50:16.747656",
"updateBy": "null",
"updateDate": null
}
]Example dynamic mapping column from the list of object arrays.
Create custom column annotation.
package com.example.demo.utils;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface ColumnByIndex {
int index();
Class<?> type() default String.class; // Default to String type
}Edit Java bean.
public class UserProfilesBean {
@ColumnByIndex(index = 0, type = UUID.class)
String id;
@ColumnByIndex(index = 1, type = String.class)
String firstName;
@ColumnByIndex(index = 2, type = String.class)
String lastName;
@ColumnByIndex(index = 3, type = String.class)
String email;
@ColumnByIndex(index = 4, type = Date.class)
Date birthDate;
@ColumnByIndex(index = 5, type = Long.class)
long sex;
@ColumnByIndex(index = 6, type = UUID.class)
String createBy;
@ColumnByIndex(index = 7, type = Timestamp.class)
LocalDateTime createDate;
@ColumnByIndex(index = 8, type = UUID.class)
String updateBy;
@ColumnByIndex(index = 9, type = Timestamp.class)
LocalDateTime updateDate;
}Create a row mapper.
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.UUID;
import com.example.demo.utils.ColumnByIndex;
public class RowMapperByIndex {
public static <T> T mapRowToObject(Object[] row, Class<T> outputClass) {
T obj = null;
try {
obj = outputClass.getDeclaredConstructor().newInstance();
Field[] fields = outputClass.getDeclaredFields();
int columnIndex = 0;
for (Field field : fields) {
if (field.isAnnotationPresent(ColumnByIndex.class)) {
ColumnByIndex column = field.getAnnotation(ColumnByIndex.class);
columnIndex = column.index();
Class<?> columnType = column.type();
Object value = getValueByType(row, columnIndex, columnType);
if (null == value) continue;
field.setAccessible(true);
field.set(obj, value);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return obj;
}
private static Object getValueByType(Object[] obj, int columnIndex, Class<?> columnType) throws SQLException {
try {
if (columnType == String.class) {
String value = obj[columnIndex].toString();
return null == value || value.isEmpty() ? null : value;
} else if (columnType == UUID.class) {
UUID value = (UUID) obj[columnIndex];
return null == value ? null : String.valueOf(value);
} else if (columnType == int.class || columnType == Integer.class) {
return obj[columnIndex] != null ? Integer.parseInt(obj[columnIndex].toString()) : 0;
} else if (columnType == long.class || columnType == Long.class) {
return Long.valueOf(obj[columnIndex].toString());
} else if (columnType == double.class || columnType == Double.class) {
return Double.valueOf(obj[columnIndex].toString());
} else if (columnType == float.class || columnType == Float.class) {
return Float.valueOf(obj[columnIndex].toString());
} else if (columnType == boolean.class || columnType == Boolean.class) {
return (boolean) obj[columnIndex];
} else if (columnType == Date.class) {
return (Date) obj[columnIndex];
} else if (columnType == Timestamp.class) {
return obj[columnIndex] != null ? ((Timestamp) obj[columnIndex]).toLocalDateTime() : null;
} else {
return obj[columnIndex];
}
} catch (Exception ex) {
//skip column not found from stored procedure or index of bound.
System.out.println("error message = " + ex.getMessage());
System.out.println("columnIndex not found = " + columnIndex);
return null;
}
}
}Edit service.
@Transactional
public List<UserProfilesBean> inqUserProfile(String userId) {
List<UserProfilesBean> list = new ArrayList<>();
// Create stored procedure query
StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("get_user_profiles");
// Set input parameters
storedProcedure.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
storedProcedure.setParameter(1, userId);
// Register the output parameter
storedProcedure.registerStoredProcedureParameter(2, void.class, ParameterMode.REF_CURSOR);
// Execute the stored procedure
storedProcedure.execute();
// Get the output parameter result
List<Object[]> results = storedProcedure.getResultList();
list = results.stream()
.map(row -> {
return RowMapperByIndex.mapRowToObject(row, UserProfilesBean.class);
})
.collect(Collectors.toList());
return list;
}Send GET request URL with user id path parameter.
http://localhost:8080/user-profiles/18555d30-eba0-4077-a080-ce4eabe15bfd
[
{
"id": "18555d30-eba0-4077-a080-ce4eabe15bfd",
"firstName": "John",
"lastName": "Doh",
"email": "demo@example.com",
"birthDate": "1989-01-06",
"sex": 1,
"createBy": "f6fd6894-52ed-49b4-8a30-e6641d8408d3",
"createDate": "2024-05-15T08:50:16.747656",
"updateBy": null,
"updateDate": null
}
]Conclusion: The developer can modify the mapping class to suitable project business requirements.
Example call function using EntityManager
Modify the calculate age method in the user profile service.
@PersistenceContext
private EntityManager entityManager;
public int calculateAge(Date birthDate, Date dateTo) {
Query query = entityManager.createNativeQuery("SELECT calculate_age(?1, ?2)");
query.setParameter(1, birthDate);
query.setParameter(2, dateTo);
return (Integer) query.getSingleResult();
}Send GET request URL with birthdate path parameter.
http://localhost:8080/cal-age/1994–06–01
29Example call stored procedures using JPA repository
Create a repository using the JPA repository for the call stored procedure.
//UserProfileRepository.java
import com.example.demo.bean.UserProfile;
import com.example.demo.bean.UserProfilesBean;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface UserProfileRepository extends JpaRepository<UserProfilesBean, Long> {
@Procedure(name = "get_user_profiles" ,procedureName = "get_user_profiles",refCursor = true)
List<UserProfilesBean> getUserProfiles(@Param("userId") String userId);
}stored procedure must return all columns in UserProfilesBean; otherwise, cause an error column not found.
Modify UserProfilesBean by adding Entity annotation.
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import lombok.Getter;
import lombok.Setter;
import java.time.LocalDateTime;
import java.util.Date;
@Getter
@Setter
@Entity
public class UserProfilesBean {
@Id
@Column(name = "id")
String id;
@Column(name = "first_name")
String firstName;
@Column(name = "last_name")
String lastName;
@Column(name = "email")
String email;
@Column(name = "birth_date")
Date birthDate;
@Column(name = "sex")
long sex;
@Column(name = "create_by")
String createBy;
@Column(name = "create_date")
LocalDateTime createDate;
@Column(name = "update_by")
String updateBy;
@Column(name = "update_date")
LocalDateTime updateDate;
}Send GET request URL with user id path parameter.
http://localhost:8080/user-profiles/18555d30-eba0-4077-a080-ce4eabe15bfd
[
{
"id": "18555d30-eba0-4077-a080-ce4eabe15bfd",
"firstName": "John",
"lastName": "Doh",
"email": "demo@example.com",
"birthDate": "1989-01-05T17:00:00.000+00:00",
"sex": 1,
"createBy": "f6fd6894-52ed-49b4-8a30-e6641d8408d3",
"createDate": "2024-05-15T08:50:16.747656",
"updateBy": null,
"updateDate": null
}
]For example, call stored procedures using the JPA repository output more than one parameter implemented with NamedStoredProcedureQuery annotation.
CREATE OR REPLACE procedure get_user_details(
IN user_id CHARACTER VARYING,
OUT out_first_name CHARACTER VARYING,
OUT out_last_name CHARACTER VARYING
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT first_name, last_name INTO out_first_name, out_last_name
FROM user_profiles
WHERE id::text = user_id;
RETURN;
END;
$$;Modify UserProfilesBean by adding NamedStoredProcedureQuery annotation.
//UserProfilesBean.java
@NamedStoredProcedureQuery(name = "get_user_details",
procedureName = "get_user_details", parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "user_id", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "out_first_name", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "out_last_name", type = String.class)})
public class UserProfilesBean {...}Modify UserProfileRepository by adding a method.
//UserProfileRepository.java
@Procedure(name = "get_user_details")
Map<String, Object> getUserProfilesName(@Param("user_id") String userId);Add a new web service method.
//UserProfilesController.java
@GetMapping(path = "/user-details/{userid}", produces = "application/json")
public ResponseEntity<Object> getUserDetail(@PathVariable String userid) throws JsonProcessingException {
//call service
String result = userProfileServiceInf.getUserDetail(userid);
return new ResponseEntity<>(result, HttpStatus.OK);
}Send GET request URL with user ID path parameter.
http://localhost:8080/user-profiles/18555d30-eba0-4077-a080-ce4eabe15bfd
{
"out_last_name": "Doh",
"out_first_name": "John"
}Example call function using JPA repository
Modify the repository by adding the calculate age method.
//UserProfileRepository.java
@Query(value = "SELECT calculate_age(:birthDate, :dateTo)", nativeQuery = true)
Integer calculateAge(@Param("birthDate") Date birthDate, @Param("dateTo") Date dateTo);Modify the service by adding the calculate age method.
public int calculateAge(
Date birthDate, Date dateTo) {
return userProfileRepository.calculateAge(birthDate, dateTo);
}Send GET request URL with birthdate and current date path parameter.
http://localhost:8080/cal-age/1994-06-01
29Call stored procedure using StoredProcedure class from spring framework
Spring boot provides StoredProcedure class support for calling stored procedures from the database.
Create ObjectMapper class for mapping results from ref cursor to Java bean.
import org.springframework.jdbc.core.RowMapper;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.UUID;
public record ObjectMapper(Class theClass) implements RowMapper {
private static Object getValueByType(ResultSet rs, String columnName, Class<?> columnType) throws SQLException {
if (columnType == Date.class) {
columnType = java.sql.Date.class;
}
return rs.getObject(columnName, columnType);
}
@Override
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Object obj = null;
try {
obj = theClass.newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
String columnName = null;
for (Field field : fields) {
if (field.isAnnotationPresent(Column.class)) {
Column column = field.getAnnotation(Column.class);
columnName = column.name();
Class<?> columnType = column.type();
Object value = getValueByType(rs, columnName, columnType);
if (null == value) continue;
if (value instanceof UUID) {
value = String.valueOf(value);
} else if (value instanceof Timestamp) {
value = ((Timestamp) value).toLocalDateTime();
}
field.setAccessible(true);
field.set(obj, value);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return obj;
}
}Create Java bean for mapping results from ref cursor.
import com.example.demo.utils.Column;
import com.example.demo.utils.ColumnByIndex;
import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;
import java.sql.ResultSet;
import java.sql.Struct;
import java.sql.Timestamp;
import java.sql.Types;
import java.time.LocalDateTime;
import java.util.*;
@Getter
@Setter
public class UserProfilesBean {
@Column(name = "id", type = UUID.class)
String id;
@Column(name = "first_name", type = String.class)
String firstName;
@Column(name = "last_name", type = String.class)
String lastName;
@Column(name = "email", type = String.class)
String email;
@Column(name = "birth_date", type = Date.class)
Date birthDate;
@Column(name = "sex", type = Integer.class)
int sex;
@Column(name = "create_by", type = UUID.class)
String createBy;
@Column(name = "create_date", type = Timestamp.class)
LocalDateTime createDate;
@Column(name = "update_by", type = UUID.class)
String updateBy;
@Column(name = "update_date", type = Timestamp.class)
LocalDateTime updateDate;
}Create a method in the service class for calling the stored procedure.
//UserProfileServiceImpl.java
@Transactional
public List<UserProfilesBean> inqUserProfile(String userId) {
ObjectStoredProcedure proc = new ObjectStoredProcedure(jdbcTemplate, UserProfilesBean.class);
Map<String, Object> result = proc.execute(userId);
return (List<UserProfilesBean>) result.get("result");
}
private static class ObjectStoredProcedure<T> extends StoredProcedure {
private static final String SP_NAME = "call get_user_profiles(?, ?)";
public ObjectStoredProcedure(JdbcTemplate jdbcTemplate, Class<T> classz) {
//the developer can modify for dynamic stored procedure and parameter.
super(jdbcTemplate, SP_NAME);
setFunction(false);
setSqlReadyForUse(true);
setSql(SP_NAME);
declareParameter(new SqlParameter("p_userid", Types.VARCHAR));
declareParameter(new SqlOutParameter("result", Types.REF_CURSOR, new ObjectMapper(classz)));
compile();
}
public Map<String, Object> execute(int userId) {
HashMap input = new HashMap();
input.put("p_userid", userId);
return super.execute(input);
}
}In ObjectStoredProcedure method.
setSqlReadyForUse(true) and setSql(“call get_user_profiles(?, ?)”)
because in the PostgreSQL database bracket, “{}” that auto-generating from the StoredProcedure class causes an error “badsqlgrammarexception”.
but it works perfectly in the Oracle database.
Generate from StoredProcedure class "{call get_user_profiles(?, ?)}"
must change to "call get_user_profiles(?, ?)"Recommend: the developer should modify the ObjectStoredProcedure class for dynamic stored procedures and parameters that make an application
easy to maintain and reduce redundant code.
Send GET request URL with user ID path parameter.
http://localhost:8080/user-profiles/18555d30-eba0-4077-a080-ce4eabe15bfd
[
{
"id": "18555d30-eba0-4077-a080-ce4eabe15bfd",
"firstName": "John",
"lastName": "Doh",
"email": "demo@example.com",
"birthDate": "1989-01-06",
"sex": 1,
"createBy": "f6fd6894-52ed-49b4-8a30-e6641d8408d3",
"createDate": "2024-05-15T08:50:16.747656",
"updateBy": null,
"updateDate": null
}
]Finally, Spring Boot provides various features for calling stored procedures and functions. The developer should learn to limit of each feature in Spring Boot to prevent problems.
Thank you for reading.