如何在JDBCTemplate中处理自定义数据类型？避免使用底层连接可行吗？

Absolutely, this is totally feasible! You don’t need to mess with low-level database connections directly when working with Spring’s JdbcTemplate. Here are clean, Spring-native approaches to handle custom PostgreSQL data types:

1. Use SqlTypeValue for Writing Custom Types to the Database #

When inserting or updating records with custom types, implement SqlTypeValue to wrap your Java object into a PostgreSQL-compatible type (like PGobject) without touching the underlying connection. JdbcTemplate will manage the connection for you.

Example for a custom PostgreSQL type my_custom_type (with id and name fields):

import org.springframework.jdbc.core.SqlTypeValue;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import org.postgresql.util.PGobject;

class CustomTypeValue implements SqlTypeValue {
    private final MyCustomObject customObj;

    public CustomTypeValue(MyCustomObject customObj) {
        this.customObj = customObj;
    }

    @Override
    public Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
        PGobject pgObject = new PGobject();
        pgObject.setType("my_custom_type"); // Match your PostgreSQL custom type name
        // Serialize your object to the format PostgreSQL expects (adjust based on your type definition)
        pgObject.setValue(String.format("(%d,'%s')", customObj.getId(), customObj.getName()));
        return pgObject;
    }
}

Use it with JdbcTemplate:

jdbcTemplate.update(
    "INSERT INTO my_table (custom_column) VALUES (?)",
    new Object[]{new CustomTypeValue(myCustomObjectInstance)},
    new int[]{Types.OTHER}
);

2. Implement RowMapper for Reading Custom Types from Results #

For querying, create a custom RowMapper to convert the PostgreSQL PGobject back to your Java object. Again, no direct connection handling needed.

import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.postgresql.util.PGobject;

class MyCustomRowMapper implements RowMapper<MyCustomObject> {
    @Override
    public MyCustomObject mapRow(ResultSet rs, int rowNum) throws SQLException {
        PGobject pgObject = (PGobject) rs.getObject("custom_column");
        if (pgObject == null) {
            return null;
        }
        // Deserialize the PGobject's value back to your Java object
        String value = pgObject.getValue().replace("(", "").replace(")", "");
        String[] parts = value.split(",");
        return new MyCustomObject(
            Integer.parseInt(parts[0].trim()),
            parts[1].replace("'", "").trim()
        );
    }
}

Use it for queries:

List<MyCustomObject> results = jdbcTemplate.query(
    "SELECT custom_column FROM my_table",
    new MyCustomRowMapper()
);

3. Leverage Spring Data JDBC Converters (For More Automation) #

If you’re using Spring Data JDBC, you can register custom Converter beans to handle type conversion automatically. This eliminates the need to write SqlTypeValue or RowMapper for every query/update.

Example converters:

import org.springframework.core.convert.converter.Converter;
import org.postgresql.util.PGobject;

// Convert PostgreSQL PGobject to your custom Java type
@Component
public class PGobjectToMyCustomConverter implements Converter<PGobject, MyCustomObject> {
    @Override
    public MyCustomObject convert(PGobject source) {
        if (source == null) return null;
        String value = source.getValue().replace("(", "").replace(")", "");
        String[] parts = value.split(",");
        return new MyCustomObject(
            Integer.parseInt(parts[0].trim()),
            parts[1].replace("'", "").trim()
        );
    }
}

// Convert your custom Java type to PostgreSQL PGobject
@Component
public class MyCustomToPGobjectConverter implements Converter<MyCustomObject, PGobject> {
    @Override
    public PGobject convert(MyCustomObject source) {
        PGobject pgObject = new PGobject();
        pgObject.setType("my_custom_type");
        try {
            pgObject.setValue(String.format("(%d,'%s')", source.getId(), source.getName()));
        } catch (SQLException e) {
            throw new RuntimeException("Failed to serialize custom type", e);
        }
        return pgObject;
    }
}

Spring Data JDBC will automatically use these converters when interacting with your custom type.

Key Notes: #

• Ensure your serialization/deserialization logic matches the input/output format of your PostgreSQL custom type (check your CREATE TYPE definition).
• For complex types like JSONB or arrays, you can use PostgreSQL-specific JDBC classes (e.g., PGjson) instead of raw PGobject.

内容的提问来源于stack exchange，提问作者hguser