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

案例 ：mysql数据同步到oracle

注意： 不要使用navicat，navicat有自己的编码，使用plsql客户端查看数据，plsql客户端与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){
    	// 查询时数据转码
    	// IOUtils.toString(rs.getAsciiStream(label),"GBK");
    	// 插入时数据转码
       return new String(string.getBytes("GBK"),StandardCharsets.ISO_8859_1);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

整合封装

AsciiUtils

package cn.shunnengnet.icoupon.common.utils;

import lombok.SneakyThrows;
import org.apache.commons.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");
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

TranscodingFunction

@FunctionalInterface
public interface TranscodingFunction<T1,T2,R> {

    R apply(T1 t1,T2 t2);

}
1
2
3
4
5
6

QueryUtils

package cn.shunnengnet.icoupon.common.utils;

import cn.shunnengnet.icoupon.common.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();
              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;
        });
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

最后使用方式

 @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);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

二、第二种

直接转码

 new String(source.getBytes("GBK"), StandardCharsets.ISO_8859_1)
1