I'll be back!

            Focus on BPM, celebrate PegaRULES Process Commander (PRPC)
          posts - 76, comments - 161, trackbacks - 0, articles - 2
            BlogJava :: 首頁 :: 新隨筆 :: 聯系 :: 聚合  :: 管理

          Java讀寫Oracle BLOB字段示例

          Posted on 2013-05-28 13:33 zolly 閱讀(1950) 評論(1)  編輯  收藏
          導入導出Oracle BLOB字段不能使用SQL,可以使用EXP或者EXPDP

          EXP導出示例:
          exp name/password@orcl file=E:/exp.dump log=E:/exp.log table=(tb_emp) query="""where UserName='FN'"""

          EXPDP導出示例:
          Sample.bat
          expdp name/password@orcl parfile=E:/expdp.par
          expdp.par
          DIRECTORY=dir_dump
          DUMPFILE=backup.dump
          LOGFILE=backup.log
          TABLES=pr_data_admin
          QUERY=pr_data_admin:"where UserName='FN'"

          以上方法因為我電腦上的Oracle版本對不上,總是報錯,后來采用java的方法,本文主要介紹如何使用java讀寫Oracle的BLOB字段,達到復制轉移BLOB數據的目的。代碼如下,加入ojdbc.jar,復制代碼可以直接運行:
          DBConnection.java
          package com.zolly.blob;

          import java.sql.Connection;
          import java.sql.DriverManager;

          public class DBConnection {

              public static Connection getDBConnectionFrom() throws Exception {
                  Connection con = null;
                  String driver = "oracle.jdbc.driver.OracleDriver";// database driver
                  String url = "jdbc:oracle:thin:@192.168.1.2:1545:ORCL";// database URL
                  String user = "user1"; // database name
                  String password = "password1"; // database Password
                  Class.forName(driver);
                  con = DriverManager.getConnection(url, user, password);
                  return con;
              }
              
              public static Connection getDBConnectionTo() throws Exception {
                  Connection con = null;
                  String driver = "oracle.jdbc.driver.OracleDriver";// database driver
                  String url = "jdbc:oracle:thin:@192.168.1.3:1521:ORCL";// database URL
                  String user = "user2"; // database name
                  String password = "password2"; // database Password
                  Class.forName(driver);
                  con = DriverManager.getConnection(url, user, password);
                  return con;
              }

          }

          BLOBUtil.java
          package com.zolly.blob;

          import java.io.File;
          import java.io.FileInputStream;
          import java.io.FileOutputStream;
          import java.io.InputStream;
          import java.io.OutputStream;
          import java.sql.Connection;
          import java.sql.ResultSet;
          import java.sql.Statement;

          import oracle.sql.BLOB;

          public class BLOBUtil {
              @SuppressWarnings("deprecation")
              public static void writeBLOB() {
                  Connection con = null;
                  long start = System.currentTimeMillis(); // count runtime
                  InputStream fin = null;
                  OutputStream outStream = null;
                  String path = "E:\\requestor.txt";
                  File file = new File(path);
                  try {
                      con = DBConnection.getDBConnectionTo();
                      con.setAutoCommit(false);

                      Statement stmt = con.createStatement();
                      ResultSet rs = stmt
                              .executeQuery("select pzPVStream from pr_data_admin where pxInsName='PEGA!BROWSER' for update");
                      // get specially columns and rows for update
                      while (rs.next()) {
                          // System.out.print(rs.getInt(1)+rs.getString(2)+"\n");//print
                          
          // select sql for debug
                          BLOB blob = (BLOB) rs.getBlob("pzPVStream");
                          outStream = blob.getBinaryOutputStream();
                          fin = new FileInputStream(file); // put file into stream
                          byte[] b = new byte[blob.getBufferSize()];
                          int len = 0;
                          while ((len = fin.read(b)) != -1) {
                              outStream.write(b, 0, len);
                          }

                          fin.close();
                          outStream.flush();
                          outStream.close();
                      }
                      System.out.print("\nupdate ok\n");

                      con.commit();

                      con.close();
                  }

                  catch (Exception e) {
                      e.printStackTrace();
                  }

                  long end = System.currentTimeMillis();
                  System.out.println(end - start);
              }

              public static void readBLOB() {
                  Connection con = null;
                  long start = System.currentTimeMillis(); // count runtime
                  String path = "E:\\requestor.txt";
                  File file = new File(path);
                  try {
                      con = DBConnection.getDBConnectionFrom();
                      con.setAutoCommit(false);
                      Statement stmt = con.createStatement();
                      ResultSet rs = stmt
                              .executeQuery("select pzPVStream from pr_data_admin where pxInsName='PEGA!BROWSER'");
                      // get blob form your table
                      if (rs.next()) {
                          BLOB blob = (BLOB) rs.getBlob("pzPVStream");
                          // get word column
                          FileOutputStream output = new FileOutputStream(file);
                          // define a file output stream
                          InputStream input = blob.getBinaryStream();// put blob into
                                                                      
          // input
                          byte[] buffer = new byte[blob.getBufferSize()];
                          // if use 1024 it will lose some bytes
                          int len = 0;
                          while ((len = input.read(buffer)) != -1) {
                              // get all input stream into output file stream
                              output.write(buffer, 0, len);
                              input.close();
                              output.flush();
                              output.close();
                          }
                          System.out.print("\ndownload ok\n");
                      }
                      con.close();
                  } catch (Exception e) {
                      e.printStackTrace();
                  }

                  long end = System.currentTimeMillis();
                  System.out.println(end - start);

              }

              public static void main(String[] args) {
                  BLOBUtil.readBLOB();
                  BLOBUtil.writeBLOB();
              }

          }

          評論

          # re: Java讀寫Oracle BLOB字段示例  回復  更多評論   

          2014-03-14 16:59 by rivers
          while ((len = input.read(buffer)) != -1)
          循環錯了。
          while ((len = input.read(buffer)) != -1) {
          // get all input stream into output file stream
          output.write(buffer, 0, len);
          }
          input.close();
          output.flush();
          output.close();

          只有注冊用戶登錄后才能發表評論。


          網站導航:
           
          主站蜘蛛池模板: 贵港市| 葵青区| 延安市| 惠安县| 昭苏县| 延长县| 景宁| 柯坪县| 磐石市| 根河市| 尼勒克县| 象山县| 新和县| 利津县| 麻阳| 禹城市| 聂荣县| 昭平县| 襄垣县| 吉水县| 嫩江县| 石渠县| 金平| 仙居县| 临澧县| 陇西县| 新巴尔虎右旗| 南京市| 高青县| 信阳市| 和静县| 屏东市| 平陆县| 太仆寺旗| 县级市| 武定县| 八宿县| 汕头市| 普定县| 宜阳县| 如皋市|