java插入内容进入oracle的clob字段

1. 背景

自己在拷贝数据表内容，进入另外一个数据库时。

2. 说明

java给oracle的clob字段插入内容。

3. 代码片段

获取clob字段内容：

public static ArrayList doExport1(){

        CLOB clob = null; 

        try
        {           
            PreparedStatement pState = cFactory.createConnection1().prepareStatement("select * from PROPOSAL_CONTENT");
            ResultSet rs = pState.executeQuery();

            //HashMap map = new HashMap();
            ArrayList list = new ArrayList();

            while(rs.next()){
               String ID = rs.getString("ID");
               String CREATE_TIME = rs.getString("CREATE_TIME"); 
               String CONTENT_CATEGORY_ID = rs.getString("CONTENT_CATEGORY_ID"); 
               String PROPOSAL_ID = rs.getString("PROPOSAL_ID"); 
               String ENABLED = rs.getString("ENABLED"); 
               clob = (oracle.sql.CLOB) rs.getClob("CONTENT");                      // 获得CLOB字段str 
               String CONTENT = ClobToString(clob); 

               HashMap map = new HashMap();
               map.put("ID", ID);
               map.put("CREATE_TIME", CREATE_TIME);
               map.put("CONTENT_CATEGORY_ID", CONTENT_CATEGORY_ID);
               map.put("PROPOSAL_ID", PROPOSAL_ID);
               map.put("ENABLED", ENABLED);
               map.put("CONTENT", CONTENT);

               list.add(map);

               System.out.println("ID="+ID+";CREATE_TIME="+CREATE_TIME+";CONTENT_CATEGORY_ID="+CONTENT_CATEGORY_ID
                       +";PROPOSAL_ID="+PROPOSAL_ID+";ENABLED="+ENABLED+";CONTENT="+CONTENT);
            }

            return list;

            } catch (SQLException e) {

                e.printStackTrace();
                return null;
            } catch (IOException e) {

                e.printStackTrace();
                return null;
            }  
    } 

依据doExport1方法中的内容，将clob内容写入表中。

public static void doImport1(){

        try
        {
            ArrayList list = doExport1();

            connection2 = cFactory.createConnection2();

            for(int i=0;i<list.size();i++){

                HashMap map = (HashMap) list.get(i);
                String ID = (String) map.get("ID");
                String CREATE_TIME = (String) map.get("CREATE_TIME"); 
                String[] CREATE_TIME1 = CREATE_TIME.split("\\.");
                System.out.println(CREATE_TIME1[0]);

                String CONTENT_CATEGORY_ID = (String) map.get("CONTENT_CATEGORY_ID"); 
                String PROPOSAL_ID = (String) map.get("PROPOSAL_ID"); 
                String ENABLED = (String) map.get("ENABLED"); 
                String CONTENT  = (String) map.get("CONTENT"); 
                String insertSQL = "insert into PROPOSAL_CONTENT values("+ID+",to_date('"+CREATE_TIME1[0]+"','YYYY-MM-DD HH24:MI:SS'),"
                                    +CONTENT_CATEGORY_ID+","+PROPOSAL_ID+","+ENABLED+",?)";

                System.out.println(insertSQL);
                PreparedStatement pState = connection2.prepareStatement(insertSQL);
                StringReader reader = new StringReader(CONTENT);  
                pState.setCharacterStream(1, reader, CONTENT.length());  
                pState.executeUpdate();
                connection2.commit();
            }

            } catch (SQLException e) {

                e.printStackTrace();
            }
    } 

将字CLOB转成STRING类型

// 将字CLOB转成STRING类型 
    public static String ClobToString(CLOB clob) throws SQLException, IOException { 

        String reString = ""; 
        Reader is = clob.getCharacterStream();                                      // 得到流 
        BufferedReader br = new BufferedReader(is); 
        String s = br.readLine(); 
        StringBuffer sb = new StringBuffer(); 
        while (s != null) {                                                         // 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING 
            sb.append(s); 
            s = br.readLine(); 
        } 
        reString = sb.toString(); 
        return reString; 
    } 

转载于:https://my.oschina.net/abcijkxyz/blog/724877