Spring Boot supports 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).
It typically uses stored procedures and functions for batch processes or to implement specific business logic, such as managing logs or generating monthly reports.
Getting Started | Accessing Data with JPA
Learn how to work with JPA data persistence using Spring Data JPA.
Step-by-step guide for calling stored procedures and functions from PostgreSQL
1. 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>2. Add the PostgreSQL configuration connection to 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.Driver
3. Create 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
1. 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;
}
}2. Send the GET request using the URL with the 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 result set.
1. Create custom column annotation in package com.example.demo.utils.
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUN.TIME)
public @interface Column {
String name();
Class<?> type() default String.class; // Default to String type
}2. 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;
}
}
}3. Modify the bean Java class using a 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;
}4. 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;
}5. Send the GET request using the URL with the 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 result set, but the class from the stored procedure must match the Java class.
Such as the difference between "java.util.Date" and “java.sql.Date"
6. The result set returns a timestamp, but using a Java bean with 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
1. Add a calculateAge method to the 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);
}2. Add the calculate age method to the 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 Run.timeException(e);
}
return new ResponseEntity<>(age, HttpStatus.OK);
}3. Send a GET request to the URL with the birthdate path parameter.
http://localhost:8080/cal-age/1986–06–01
36
Example call stored procedures using EntityManager.
1. Create a repository or service layer using EntityManager to call the 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;
}2. Send the GET request using the URL with the 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.
1. Create a custom column annotation.
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUN.TIME)
public @interface Column {
String name();
Class<?> type() default String.class; // Default to String type
}2. Edit Java bean.
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;
}
}
}3. Create a row mapper.
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;
}4. Edit service.
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;
}5. Send the GET request using the URL with the 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 suit the project’s business requirements.
Example call function using EntityManager
1. Modify the age calculation 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();
}2. Send a GET request to the URL with the birthdate path parameter.
http://localhost:8080/cal-age/1994–06–01
29
Example call stored procedures using JPA repository
1. Create a repository using the JPA repository for the stored procedure call.
//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.
2. Modify UserProfilesBean by adding the 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;
}3. Send the GET request using the URL with the 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 the 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;
$$;1. 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 {...}2. Modify UserProfileRepository by adding a method.
//UserProfileRepository.java
@Procedure(name = "get_user_details")
Map<String, Object> getUserProfilesName(@Param("user_id") String userId);3. 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);
}4. Send the GET request URL using the 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
1. Modify the repository by adding a ‘calculate age’ method.
//UserProfileRepository.java
@Query(value = "SELECT calculate_age(:birthDate, :dateTo)", nativeQuery = true)
Integer calculateAge(@Param("birthDate") Date birthDate, @Param("dateTo") Date dateTo);2. Modify the service by adding a calculateAge method.
public int calculateAge(
Date birthDate, Date dateTo) {
return userProfileRepository.calculateAge(birthDate, dateTo);
}3. Send the GET request URL with the birthdate and current date path parameters.
http://localhost:8080/cal-age/1994-06-01
29
Call a stored procedure using the StoredProcedure class from the Spring Framework
Spring Boot provides support for the Stored Procedure class, allowing you to call stored procedures from the database.
1. Create an ObjectMapper class to map results from the ref cursor to the 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;
}
}2. Create a Java bean to map results from the 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;
}3. 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);
}
}4. In ObjectStoredProcedure method.
setSqlReadyForUse(true) and setSql(“call get_user_profiles(?, ?)”)
because the bracket “{}” in the PostgreSQL database, which is auto-generated 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.
5. Send the GET request using the URL with the 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 each feature in Spring Boot to prevent problems.
