java 程序插入oracle数据库 clob Nclob大字段数据 代码

java 程序插入oracle数据库 clob Nclob大字段数据 代码

懒得说直接贴出来

package cn.com.oceansoft.osc.ms.service.impl;

import cn.com.oceansoft.osc.ms.config.YmlConfig;
import cn.com.oceansoft.osc.ms.domain.SwapTempData;
import cn.com.oceansoft.osc.ms.persistence.datasource.IDataMachineMapper;
import cn.com.oceansoft.osc.ms.persistence.datasource2.IDataServerMapper;
import cn.com.oceansoft.osc.ms.service.IDataService;
import oracle.sql.CLOB;
import oracle.sql.NCLOB;
import org.springframework.stereotype.Service;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.sql.*;

import javax.annotation.Resource;
import java.util.*;
import oracle.sql.TIMESTAMP;
import java.text.SimpleDateFormat;
import java.util.Date;

@Service
public class DataServiceImpl implements IDataService{

    private static final Logger logger = LoggerFactory.getLogger(DataServiceImpl.class);

    @Resource
    private IDataMachineMapper iDataMachineMapper;

    @Resource
    private IDataServerMapper iDataServerMapper;

    @Resource
    private YmlConfig ymlConfig;

    @Override
    public void synchroData() {

        // 获取待同步表信息
        List<SwapTempData> swapTempDataList = iDataMachineMapper.getSwapTempData();

        StringBuilder stringSql = new StringBuilder();
        for(SwapTempData swapTempData : swapTempDataList) {
            // 查询数据sql 拼接
            stringSql.append("select * from ").append(swapTempData.getTablename())
                    .append(" where ").append("guid = '").append(swapTempData.getRefguid()).append("'");
            //执行查询
            Map<String, Object> map = iDataMachineMapper.selectSql(stringSql);

            // 清空stringSql
            stringSql.delete(0, stringSql.length());
            int res = 0;

            if ("INSERT".equals(swapTempData.getAction().toUpperCase()) && map != null) {
                // 拼接sql
                try {
                    res = splitSqlInsert(swapTempData.getTablename(), map);
                } catch (Exception e) {
                    e.printStackTrace();
                }

            } else if ("DELETE".equals(swapTempData.getAction().toUpperCase()) && map == null) {
                // 拼接sql
                stringSql = splitSqlDelete(swapTempData.getTablename(), swapTempData.getRefguid());
                logger.info("springSql delete :  {}",stringSql);
                // 执行
                res = iDataServerMapper.deleteSql(stringSql);

            } else if ("UPDATE".equals(swapTempData.getAction().toUpperCase()) && map != null) {
                // 拼接sql
                try {
                    res = splitSqlUpdate(swapTempData.getTablename(), map, swapTempData.getRefguid());
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            stringSql.delete(0, stringSql.length());

            // 成功后更新临时数据表数据状态
            if (res > 0) {
                this.iDataMachineMapper.updateStatus(swapTempData.getGuid());
            }
        }

    }

    /**
     * 数据插入
     * @param tablename 表名
     * @param map 待插入表信息
     * @return
     */
    public int splitSqlInsert(String tablename, Map<String, Object> map) throws SQLException, IOException {
        // 获取当前表字段类型
        Map<String,String> columnTypes = getColumnType(tablename);
        // Nclob、clob参数map
        Map<String, String> mapNclob = new HashMap<>();
        // 时间格式
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 插入语句key
        StringBuilder stringBuilderSql = new StringBuilder();
        // 插入语句value
        StringBuilder stringSqlValue = new StringBuilder();
        Set set = map.keySet();
        Iterator iter = set.iterator();

        String key = "";
        String str = "";
        StringReader reader = null;
        int res = 1 ;
        stringBuilderSql.append("insert into ").append(tablename).append(" (");

            Connection conn = DriverManager.getConnection(ymlConfig.getUrl(), ymlConfig.getUsername(), ymlConfig.getPassword());
logger.info("getUsername :{}",ymlConfig.getUsername());
            conn.setAutoCommit(false);
            // 拼sql
            while (iter.hasNext()) {
                key = (String) iter.next();
                stringBuilderSql.append(key);

                // 判断是否为时间类型
                if("DATE".equals(columnTypes.get(key).toUpperCase()) || columnTypes.get(key).toUpperCase().indexOf("TIMESTAMP")!=-1) {
                    try {
                        TIMESTAMP timestamp = (TIMESTAMP) map.get(key);
                        str = timestamp.toString().substring(0, timestamp.toString().indexOf("."));
                    } catch (Exception e) {
                        Date date = (Date) map.get(key);
                        str = sdf.format(date);
                    }

                    stringSqlValue.append("TO_DATE('").append(str).append("', 'SYYYY-MM-DD HH24:MI:SS')");
                } else if( "NCLOB".equals(columnTypes.get(key).toUpperCase())){

                    NCLOB nclob = (NCLOB) map.get(key);
                    long longLen = nclob.length();
                    str = nclob.getSubString(1L, (int) longLen);
                    mapNclob.put(res+"",str);
                    stringSqlValue.append("?");
                    res++;
                } else if( "CLOB".equals(columnTypes.get(key).toUpperCase())){

                    CLOB clob = (CLOB) map.get(key);
                    long longLen = clob.length();
                    str = clob.getSubString(1L, (int) longLen);
                    mapNclob.put(res+"",str);
                    stringSqlValue.append("?");
                    res++;
                } else {
                    stringSqlValue.append("'").append(map.get(key)).append("'");
                }

                if (iter.hasNext()) {
                    stringBuilderSql.append(", ");
                    stringSqlValue.append(", ");
                }
            }
            stringBuilderSql.append(") values (").append(stringSqlValue).append(")");
            // 1.这种方法写入CLOB字段可以。
            logger.info("stringBuilderSql insert:   {}", stringBuilderSql);
            PreparedStatement stat = conn.prepareStatement(stringBuilderSql+"");

            Set setClob = mapNclob.keySet();
            Iterator iterNclob = setClob.iterator();
            while (iterNclob.hasNext()) {
                key = (String) iterNclob.next();
                str = mapNclob.get(key);
                reader = new StringReader(str);
                stat.setCharacterStream(Integer.parseInt(key), reader, str.length());
            }

            stat.executeUpdate();
            conn.commit();
            stat.close();
            reader.close();
            conn.close();
        res = 1 ;
        return res;
    }

    /**
     * 拼接删除sql
     * @param tablename 待删除表名
     * @param guid 待删除表主键
     * @return
     */
    public StringBuilder splitSqlDelete(String tablename, String guid) {
        StringBuilder stringSql = new StringBuilder();
        stringSql.append("delete from ").append(tablename).append(" where guid = '").append(guid).append("'");
        return stringSql;
    }

    /**
     * 数据更新 先删除再插入
     * @param tablename 待更新表名
     * @param map 待更新数据
     * @param guid 待更新表主键
     * @return
     */
    public int splitSqlUpdate(String tablename, Map<String, Object> map, String guid) throws SQLException, IOException {
        // 先删除 再插入
        StringBuilder stringSql = splitSqlDelete(tablename, guid);
        iDataServerMapper.deleteSql(stringSql);
        logger.info("-------------------------  更新先删除再插入   -----------------------------");
        splitSqlInsert(tablename, map);
        int res = 1;
        return res;
    }

    /**
     * 处理查询结果
     * @param tablename
     * @return
     */
    public Map<String, String> getColumnType (String tablename) {
        // 获取当前表字段类型
        List<Map<String,String>> columnTypeList = iDataMachineMapper.selectTableType(tablename);

        Map<String, String> columnTypes = new HashMap<>();
        for(Map<String, String> columnType : columnTypeList){
            columnTypes.put(columnType.get("key"),columnType.get("value"));
        }
        return columnTypes;
    }

}