Working with PostgreSQL stored procedures in MyBatis applications is a common requirement for enterprise Java applications. This comprehensive MyBatis PostgreSQL stored procedure tutorial will guide you through everything you need to know to effectively call stored procedures and functions using MyBatis, from basic setup to advanced techniques.
PostgreSQL stored procedures and functions provide powerful server-side processing capabilities, and when combined with MyBatis, they offer an excellent way to encapsulate complex business logic while maintaining clean separation between your Java application and database operations.
Understanding PostgreSQL Functions vs Stored Procedures
Before diving into MyBatis integration, it’s important to understand the difference between PostgreSQL functions and stored procedures. PostgreSQL 11 introduced true stored procedures, while functions have been available since earlier versions.
PostgreSQL Functions
Functions are the traditional way to encapsulate logic in PostgreSQL and must return a value:
CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER)
RETURNS TABLE(id INTEGER, username VARCHAR, email VARCHAR) AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.username, u.email
FROM users u
WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;PostgreSQL Stored Procedures
Stored procedures don’t return values but can have OUT parameters:
CREATE OR REPLACE PROCEDURE update_user_status(
IN p_user_id INTEGER,
IN p_status VARCHAR,
OUT p_result INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE users SET status = p_status WHERE id = p_user_id;
GET DIAGNOSTICS p_result = ROW_COUNT;
END;
$$;MyBatis Configuration for PostgreSQL
First, ensure your MyBatis configuration is properly set up for PostgreSQL. Here’s the essential configuration:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="jdbcTypeForNull" value="OTHER"/>
<setting name="callSettersOnNulls" value="true"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="org.postgresql.Driver"/>
<property name="url" value="jdbc:postgresql://localhost:5432/mydb"/>
<property name="username" value="username"/>
<property name="password" value="password"/>
</dataSource>
</environment>
</environments>
</configuration>Calling PostgreSQL Functions with MyBatis
Let’s start with calling PostgreSQL functions, which is the most common scenario in MyBatis PostgreSQL stored procedure implementations.
Creating the Mapper Interface
package com.example.mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
public interface UserMapper {
// Calling function that returns a table
List<User> getUserById(@Param("userId") Integer userId);
// Calling function with multiple parameters
List<User> searchUsers(
@Param("username") String username,
@Param("email") String email
);
// Calling function that returns a single value
Integer getUserCount(@Param("status") String status);
}XML Mapper Configuration for Functions
Here’s how to configure your XML mapper to call PostgreSQL functions:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserMapper">
<resultMap id="userResultMap" type="com.example.model.User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="email" column="email"/>
<result property="status" column="status"/>
</resultMap>
<!-- Calling function that returns a table -->
<select id="getUserById" resultMap="userResultMap">
SELECT * FROM get_user_by_id(#{userId})
</select>
<!-- Calling function with multiple parameters -->
<select id="searchUsers" resultMap="userResultMap">
SELECT * FROM search_users(#{username}, #{email})
</select>
<!-- Calling function that returns a single value -->
<select id="getUserCount" resultType="java.lang.Integer">
SELECT get_user_count(#{status})
</select>
</mapper>Calling PostgreSQL Stored Procedures with MyBatis
Calling stored procedures requires a different approach since they don’t return values directly but use OUT parameters.
Using CallableStatement for Stored Procedures
package com.example.mapper;
import java.util.Map;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.mapping.StatementType;
public interface UserProcedureMapper {
@Options(statementType = StatementType.CALLABLE)
void updateUserStatus(Map<String, Object> parameters);
@Options(statementType = StatementType.CALLABLE)
void createUser(
@Param("username") String username,
@Param("email") String email,
@Param("result") Integer result
);
}XML Configuration for Stored Procedures
<!-- Calling stored procedure with OUT parameter -->
<select id="updateUserStatus" statementType="CALLABLE">
{call update_user_status(
#{userId, mode=IN, jdbcType=INTEGER},
#{status, mode=IN, jdbcType=VARCHAR},
#{result, mode=OUT, jdbcType=INTEGER}
)}
</select>
<!-- More complex stored procedure call -->
<select id="createUser" statementType="CALLABLE">
{call create_user(
#{username, mode=IN, jdbcType=VARCHAR},
#{email, mode=IN, jdbcType=VARCHAR},
#{userId, mode=OUT, jdbcType=INTEGER},
#{success, mode=OUT, jdbcType=BOOLEAN}
)}
</select>Handling Complex Data Types
PostgreSQL supports complex data types like arrays and custom types. Here’s how to handle them in MyBatis:
Working with Arrays
-- PostgreSQL function returning array
CRETE OR REPLACE FUNCTION get_user_roles(user_id INTEGER)
RETURNS TEXT[] AS $$
DECLARE
roles TEXT[];
BEGIN
SELECT ARRAY_AGG(r.role_name) INTO roles
FROM user_roles ur
JOIN roles r ON ur.role_id = r.id
WHERE ur.user_id = get_user_roles.user_id;
RETURN roles;
END;
$$ LANGUAGE plpgsql;MyBatis mapper for array handling:
<select id="getUserRoles" resultType="java.lang.String[]">
SELECT get_user_roles(#{userId})
</select>Custom Type Handlers
For complex PostgreSQL types, you might need custom type handlers:
package com.example.typehandler;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
public class PostgreSQLArrayTypeHandler extends BaseTypeHandler<String[]> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i,
String[] parameter, JdbcType jdbcType) throws SQLException {
ps.setArray(i, ps.getConnection().createArrayOf("text", parameter));
}
@Override
public String[] getNullableResult(ResultSet rs, String columnName)
throws SQLException {
Array array = rs.getArray(columnName);
return array != null ? (String[]) array.getArray() : null;
}
@Override
public String[] getNullableResult(ResultSet rs, int columnIndex)
throws SQLException {
Array array = rs.getArray(columnIndex);
return array != null ? (String[]) array.getArray() : null;
}
@Override
public String[] getNullableResult(CallableStatement cs, int columnIndex)
throws SQLException {
Array array = cs.getArray(columnIndex);
return array != null ? (String[]) array.getArray() : null;
}
}Transaction Management
When working with stored procedures, proper transaction management is crucial. Here’s how to handle transactions in MyBatis:
package com.example.service;
import java.util.HashMap;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
public class UserService {
private SqlSessionFactory sqlSessionFactory;
public boolean updateUserWithTransaction(Integer userId, String status) {
try (SqlSession session = sqlSessionFactory.openSession()) {
try {
UserProcedureMapper mapper =
session.getMapper(UserProcedureMapper.class);
Map<String, Object> params = new HashMap<>();
params.put("userId", userId);
params.put("status", status);
mapper.updateUserStatus(params);
Integer result = (Integer) params.get("result");
if (result > 0) {
session.commit();
return true;
} else {
session.rollback();
return false;
}
} catch (Exception e) {
session.rollback();
throw new RuntimeException("Failed to update user", e);
}
}
}
}Best Practices and Tips
When working with MyBatis and PostgreSQL stored procedures, follow these best practices:
Parameter Handling
- Always specify parameter modes (IN, OUT, INOUT) explicitly
- Use appropriate JDBC types for better compatibility
- Handle null values properly with jdbcTypeForNull setting
Error Handling
<select id="safeUserOperation" statementType="CALLABLE">
{call safe_user_operation(
#{userId, mode=IN, jdbcType=INTEGER},
#{operation, mode=IN, jdbcType=VARCHAR},
#{success, mode=OUT, jdbcType=BOOLEAN},
#{errorMessage, mode=OUT, jdbcType=VARCHAR}
)}
</select>Performance Considerations
- Use connection pooling for better performance
- Consider using batch operations for multiple procedure calls
- Monitor and optimize stored procedure performance
Common Issues and Solutions
Issue: “No suitable driver found” Error
Ensure the PostgreSQL JDBC driver is in your classpath:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.6.0</version>
</dependency>Issue: Parameter Type Mismatch
Always match Java parameter types with PostgreSQL parameter types and use explicit JDBC type mapping when necessary.
Issue: OUT Parameter Not Retrieved
Ensure you’re using CallableStatement (statementType=”CALLABLE”) and properly defining OUT parameters with mode=OUT.
Conclusion
This MyBatis PostgreSQL stored procedure tutorial has covered the essential techniques for integrating PostgreSQL stored procedures and functions with MyBatis. From basic function calls to complex stored procedures with OUT parameters, you now have the knowledge to effectively leverage server-side processing in your Java applications.
Remember to follow best practices for parameter handling, transaction management, and error handling to build robust applications. The combination of MyBatis and PostgreSQL stored procedures provides a powerful foundation for enterprise applications that require both flexibility and performance.
As you implement these techniques in your projects, focus on creating maintainable code with proper error handling and transaction management. The examples provided in this tutorial serve as a solid foundation for more complex scenarios you may encounter in production applications.