JAVA读取CLOB字段

最新推荐文章于 2023-12-28 08:00:00 发布
原创最新推荐文章于 2023-12-28 08:00:00 发布 · 388 阅读
CC 4.0 BY-SA版权
文章标签：
1、将数据库某字段的内容导出到指定的文件

数据库表结构如下：

表MAXPRESENTATION

APP
VARCHAR2(10)

PRESENTATION
CLOB

MAXPRESENTATIONID
NUMBER

ROWSTAMP
VARCHAR2(40)

[注]：以APP字段的内容做为导出的文件名，PRESENTATION字段的内容做为该文件的内容，该字段的编码格式为：UTF-8

程序清单如下：

package file;
import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStreamReader;

import java.io.OutputStreamWriter;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

 * 文件导出


 * 


 * @author songl

 * @version 1.0 2005.12.2

 */

public class ExpFile {



       public static void main(String args[]) throws ClassNotFoundException, IOException {

              Connection con = null;

              Statement stmt = null;

              ResultSet rs = null;

              try{

                     String url = "jdbc:oracle:thin:@IP:1521:system";

                     DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 

                     con = DriverManager.getConnection(url,"maximo","maximo");

                     stmt = con.createStatement();



                     System.out.println("请指定导入的文件名：");

                     InputStreamReader irFile = new InputStreamReader(System.in);

                     BufferedReader inFile = new BufferedReader(irFile);

                     String sFile = inFile.readLine();

                     sFile = sFile.toUpperCase();   

                     if(sFile!=null && !sFile.equalsIgnoreCase("")){

                            rs = stmt.executeQuery("select * from MAXPRESENTATION 

where APP='"+sFile+"'");

                     }else{

                            rs = stmt.executeQuery("select * from MAXPRESENTATION");

                     }

                     while(rs.next()){

                            //新建文件

                            System.out.println(rs.getString(1));

                            String strName = rs.getString(1);

                            strName = strName+".xml";

                            File f1= new File("/epmxes/epmxes/res/presentation",strName);

                            if(f1.isFile()==false){

                                   try {

                                          f1.createNewFile();

                                   } catch (IOException e) {

                                          e.printStackTrace();

                                   }

                                   String strNR = null;

//读取文件内容

                                   Clob clob = rs.getClob(2);


                                   strNR = clob.getSubString( (long) 1, (int) clob.length());


                                   //写入文件，此处要用字节流来读取，还要指定编码格式，以免乱码

                                   OutputStreamWriter isw = new OutputStreamWriter(new 


FileOutputStream(f1),"UTF-8");


                                   BufferedWriter bw = new BufferedWriter(isw);

                                   bw.write(strNR,0,strNR.length());

                                   bw.close();

                            }else{

                                   System.out.println("文件："+strName+"已经存在！是否覆盖？y/n");

                                   InputStreamReader ir;

                                   BufferedReader in;

                                   ir=new InputStreamReader(System.in);

                                   in=new BufferedReader(ir);

                                   String s=in.readLine();

                                   if(s.equalsIgnoreCase("y")){

                                          System.out.println("文件："+strName+"被覆盖!");

                                          String strNR = null;

                                          Clob clob = rs.getClob(2);

                                          //读取文件内容

                                          strNR = clob.getSubString( (long) 1, (int) clob.length());

                                          System.out.println("文件内容如下:");

                                          System.out.println(strNR);

                                          //写入文件

                                          OutputStreamWriter isw = new OutputStreamWriter(new 

FileOutputStream(f1),"UTF-8");

                                          System.out.println(isw.getEncoding());

                                          BufferedWriter bw = new BufferedWriter(isw);

                                          bw.write(strNR,0,strNR.length());

                                          bw.close();

                                   }else if(s.equalsIgnoreCase("n")){

                                          System.out.println("文件："+strName+"没有被覆盖!");

                                   }else{

                                          System.out.println("输入的字符有误！请重新运行程序！");

                                   }

                            }

                     }

              }catch(SQLException ex){

                     ex.printStackTrace();

              }finally{

                     try{

                            rs.close();

                            stmt.close();

                            con.close();

                     }catch(SQLException ex){

                            ex.printStackTrace();

                     }                   

              }

       }

}


2、将文件内容存入数据库的表中相应的字段

程序清单如下：

package file;

import java.io.BufferedReader;

import java.io.File;

import java.io.FileInputStream;

import java.io.InputStreamReader;

import java.io.Writer;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

 * 导入数据库


 * 


 * @author songl

 * @version 1.0 2005.12.2

 */

public class ImpDb {

       public static void main(String args[]) {

              Connection con = null;

              ResultSet resultset = null;

              Statement statement = null;

              Statement pstmt = null;

              try{ 

                     System.out.println("请指定导入数据库的文件名：");

                     InputStreamReader irFile = new InputStreamReader(System.in);

                     BufferedReader inFile = new BufferedReader(irFile);

                     String sFile = inFile.readLine();

                     if(sFile!=null && !sFile.equalsIgnoreCase("")){

                            File f1= new File("/epmxes/epmxes/res/presentation/",sFile+".xml");

                            if(f1.isFile()==true){

                                   //读取文件内容

                                   InputStreamReader isr = new InputStreamReader(new 


FileInputStream(f1),"UTF-8");


                                   BufferedReader br = new BufferedReader(isr);

                                   String strNR = "";

                                   while (true) {

//按行读取

                              String temp = br.readLine();

                              if (temp == null){

                                  break;

                              }

                              strNR += temp + "\n";

                          }

                                   br.close();

                                   System.out.println("文件内容如下：");

                                   System.out.println(strNR);

                                   String url = "jdbc:oracle:thin:@IP:1521:system";

                                   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 

                                   con = DriverManager.getConnection(url,"maximo","maximo");

                                   //将文件导入数据库

                                   sFile = sFile.toUpperCase();   

                                   con.setAutoCommit(false); 

                                   String sql = "update MAXPRESENTATION set PRESENTATION = 

empty_clob() where APP='" + sFile + "'";

                                   pstmt = con.createStatement();

                                   pstmt.executeUpdate(sql); 

                                   statement = con.createStatement();

                                   String sql2="select PRESENTATION from MAXPRESENTATION 

where APP='"+sFile+"'";

                                   resultset = statement.executeQuery(sql2);

                                oracle.sql.CLOB contents = null;

                                while(resultset.next()) {

                                          //要使用oracle中的resultset对象

                                    contents = (oracle.sql.CLOB)resultset.getClob(


"PRESENTATION");


                                }

                                Writer out = contents.getCharacterOutputStream();


                                out.write(strNR);

                                out.flush();

                                out.close();

                                con.commit();

                                con.setAutoCommit(true);


                                System.out.println("导入数据库成功！");

                            }else{

                                   System.out.println("不存在该文件！");

                            }                          

                     }else{

                            System.out.println("请输入文件名！");

                     }

              }catch(Exception ex){

                     try{

                            if(con!=null){

                                   con.rollback();

                            }

                     }catch(SQLException e){

                            e.printStackTrace();

                     }                   

              }finally{

                     try{

                            if(resultset!=null){

                                resultset.close();

                         }

                         if(statement!=null){

                                statement.close();

                         }

                         if(pstmt!=null){

                                pstmt.close();

                         }

                         if(con!=null){

                                con.close();

                         }                          

                     }catch(SQLException ex){

                            ex.printStackTrace();

                     }     

              }

       }

}