文件的导入导出（CLOB类型字段）_Oracle_java

 

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(); } } } }