oracle乱码（编码为AMERICAN_AMERICA.US7ASCII）问题解决

案例 ：mysql数据同步到oracle service类

本地用的多数据源 com.baomidou dynamic-datasource-spring-boot-starter

private final JdbcTemplate jdbcTemplate; private static final List<String> lableList = new ArrayList(); static { lableList.add("doctor_id"); lableList.add("dept_id"); lableList.add("dept_name"); lableList.add("doctor_name"); lableList.add("doctor_title"); } @SneakyThrows @Override public List<SchSyncSchedule> syncHisToOracleAscii(String date){ // 示例 String sql = "SELECT *,CASE schedule_noon_code WHEN '0' THEN '上午' WHEN '1' THEN '下午' WHEN '2' THEN '全天'ELSE '' END as 'schedule_noon_code_str'" + "FROM `sch_sync_schedule` WHERE DATE_FORMAT( schedule_date, '%Y-%m-%d' ) >= DATE_FORMAT( '"+ date +"', '%Y-%m-%d' )"; return jdbcTemplate.query(sql, rs -> { List<SchSyncSchedule> list = new ArrayList<>(); // 获取ResultSet对象的列的数量、类型和属性。 ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); if(rs.next()){ Map<String, Object> rowData = new HashMap<>(); while (rs.next()) { for (int i = 1; i <= columnCount; i++) { String columnLabel = md.getColumnLabel(i); if(lableList.contains(columnLabel)){ String asciiString = getAsciiString(rs, columnLabel); rowData.put(columnLabel, asciiString); }else if(columnLabel.equals("schedule_noon_code_str")){ String asciiString = getAsciiString(rs, columnLabel); rowData.put(columnLabel, asciiString); }else { rowData.put(md.getColumnLabel(i), rs.getObject(i)); } } // 序列化成对象实体 String jsonStr = JSONObject.toJSONString(rowData); SchSyncSchedule userBean = JSONObject.parseObject(jsonStr, SchSyncSchedule.class); log.info("转换为实体类对象:{}",userBean); list.add(userBean); } } return list; }); } @SneakyThrows public String getAsciiString(ResultSet rs,String label){ return IOUtils.toString(rs.getAsciiStream(label),"GBK"); } 整合封装 AsciiUtils package cn.shunnengnet.icoupon mon.utils; import lombok.SneakyThrows; import org.apache mons.io.IOUtils; import java.sql.ResultSet; /*** * * @author qb * @date 2023/9/27 14:25 * @version 1.0 */ public class AsciiUtils { @SneakyThrows public static String getAsciiString(ResultSet rs, String label){ return IOUtils.toString(rs.getAsciiStream(label),"GBK"); } } TranscodingFunction @FunctionalInterface public interface TranscodingFunction<T1,T2,R> { R apply(T1 t1,T2 t2); } QueryUtils package cn.shunnengnet.icoupon mon.utils; import cn.shunnengnet.icoupon mon.utils.function.TranscodingFunction; import com.alibaba.fastjson.JSONObject; import org.apache.http.util.Asserts; import org.springframework.jdbc.core.JdbcTemplate; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.function.Consumer; import java.util.function.Predicate; /*** * * @author qb * @date 2023/9/27 15:05 * @version 1.0 */ public class QueryUtils{ private TranscodingFunction<ResultSet,String,String> transcoding; private Consumer<String> consumer; private Predicate<String> isTranscoding; public static QueryUtils builder(){ return new QueryUtils(); } public QueryUtils setTranscoding(TranscodingFunction<ResultSet,String,String> transcoding){ this.transcoding = transcoding; return this; } public QueryUtils extend(Consumer<String> consumer){ this.consumer = consumer; return this; } public QueryUtils isTranscoding(Predicate<String> isTranscoding){ this.isTranscoding = isTranscoding; return this; } public <T> List<T> query(JdbcTemplate jdbcTemplate, String sql, Class<T> clas){ return jdbcTemplate.query(sql, rs -> { List<T> list = new ArrayList<>(); // 获取ResultSet对象的列的数量、类型和属性。 ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); if(rs.next()){ Map<String, Object> rowData = new HashMap<>(); while (rs.next()) { for (int i = 1; i <= columnCount; i++) { String columnLabel = md.getColumnLabel(i); // 自定义字段扩展 if(null != consumer) consumer.accept(columnLabel); // 如果为true，则需要转码 if(null != isTranscoding && isTranscoding.test(columnLabel)){ Asserts.check(transcoding != null , "转码函数为空"); String asciiString = transcoding.apply(rs,columnLabel); rowData.put(columnLabel, asciiString); } else { rowData.put(md.getColumnLabel(i), rs.getObject(i)); } } String jsonStr = JSONObject.toJSONString(rowData); T times = JSONObject.parseObject(jsonStr, clas); list.add(times); } } return list; }); } } 最后使用方式 @SneakyThrows @Override public List<HpSyncScheduleTimesDhCurr> syncTimeToHisAscii(Set<String> codes) { StringBuilder sql = new StringBuilder("SELECT * FROM `hp_sync_schedule_times_dh_curr` WHERE schedule_item_code in ("); Iterator<String> iterator = codes.iterator(); while (iterator.hasNext()) { sql.append("'").append(iterator.next()).append("'"); if(iterator.hasNext()){ sql.append(","); } } sql.append(")"); return QueryUtils.builder() .setTranscoding(AsciiUtils::getAsciiString) .isTranscoding(lableList::contains) .query(jdbcTemplate, sql.toString(), HpSyncScheduleTimesDhCurr.class); }