使用JdbcTemplate存储二进制数据时的压缩存储问题

问题原因解析 #

• 指定Types.VARBINARY时报错：JDBC驱动会将传入的String类型参数当作十六进制字符串解析（因为VARBINARY对应的Java类型是byte[]），普通文本不符合十六进制格式规则，因此抛出String is not in a valid hex format错误。
• 指定Types.VARCHAR时查询乱码：插入时COMPRESS函数处理的是字符串转默认编码的二进制数据，查询时DECOMPRESS返回的二进制数据未按原编码转换回字符串，导致乱码。

解决方案 #

方案1：将字符串转为字节数组传入（推荐） #

直接将Java字符串转换为指定编码的字节数组，匹配VARBINARY类型的参数要求，避免类型解析错误，同时保证编码一致性。

插入代码示例：

import java.nio.charset.StandardCharsets;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import java.sql.Types;

String sql = "insert into test(payload) values (compress(:payload))";
MapSqlParameterSource msps = new MapSqlParameterSource();
// 将字符串转为UTF-8字节数组（可根据实际需求调整编码）
byte[] payloadBytes = "some text".getBytes(StandardCharsets.UTF_8);
// 显式指定参数类型为VARBINARY，或省略（JDBC会自动映射byte[]到VARBINARY）
msps.addValue("payload", payloadBytes, Types.VARBINARY);

NamedParameterJdbcTemplate npjt = // 初始化你的NamedParameterJdbcTemplate实例
npjt.update(sql, msps);

查询代码示例：

方式1：SQL层面转换为字符串

SELECT CAST(decompress(payload) AS VARCHAR(MAX)) FROM test;

方式2：Java层面转换为字符串

String querySql = "select decompress(payload) from test";
byte[] resultBytes = npjt.queryForObject(querySql, byte[].class);
// 用相同编码转换回字符串
String result = new String(resultBytes, StandardCharsets.UTF_8);

方案2：保持字符串参数，统一编码处理 #

若需直接传入字符串参数，需确保插入和查询时的编码一致，避免乱码。

插入代码示例：

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import java.sql.Types;

String sql = "insert into test(payload) values (compress(:payload))";
MapSqlParameterSource msps = new MapSqlParameterSource();
// 指定参数类型为VARCHAR，驱动会按默认编码转换为SQL VARCHAR
msps.addValue("payload", "some text", Types.VARCHAR);

NamedParameterJdbcTemplate npjt = // 初始化实例
npjt.update(sql, msps);

查询代码示例：

在SQL中显式指定编码COLLATE，匹配插入时的编码：

SELECT CAST(decompress(payload) AS VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS) FROM test;

（注：SQL_Latin1_General_CP1_CI_AS为MSSQL默认编码，若插入时使用UTF-8，需确保数据库支持对应编码，如COLLATE UTF8_GENERAL_CI）

关键注意点 #

• VARBINARY类型在Java中对应的是byte[]，而非String，直接传String会触发十六进制解析逻辑。
• 压缩和解压缩过程必须保持编码一致，否则必然出现乱码。
• 若使用自定义编码，需确保MSSQL数据库支持该编码格式。

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