程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql,sqlserver,oracle三種數據庫的大對象存取

mysql,sqlserver,oracle三種數據庫的大對象存取

編輯:MySQL綜合教程

mysql 大對象存取:
類型一般應該用mediumblod,
blob只能存2的16次方個byte,
mediumblod是24次方,
一般來說夠用了.longblob是32次方有些大.
 
MYSQL默認配置只能存1M大小的文件,要修改配置,WIN版本的在mysql.ini文件中
修改max_allowed_packet,net_buffer_length等幾個參數,或直接SET GLOBAL varName=value.
linux版本可以在啟動參數後加-max_allowed_packet=xxM等幾個參數.
 
MYSQL存大對象最好直接就setBinaryStream,又快又方便.
而不要先插入空再造型成BLOB然後再setBlob
 
例子:
import java.sql.*;
import java.io.*;
public class DBTest {
 
 
  static String driver = "org.gjt.mm.mysql.Driver";
  static String url = "jdbc:mysql://localhost:3306/test";
  static String user = "root";
  static String passwd = "passwd";
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName(driver);
      conn = DriverManager.getConnection(url,user,passwd);
      
      int op = 1;
      //插入
      if (op == 0) {
        PreparedStatement ps = conn.prepareStatement("insert into tb_file values (?,?)");
        ps.setString(1, "aaa.exe");
        InputStream in = new FileInputStream("d:/aaa.exe");
        ps.setBinaryStream(2,in,in.available());
        ps.executeUpdate();
        ps.close();
      }
      else {
        //取出
        PreparedStatement ps = conn.prepareStatement("select * from  tb_file where filename = ?");
        ps.setString(1, "aaa.exe");
        ResultSet rs = ps.executeQuery();
        rs.next();
        InputStream in = rs.getBinaryStream("filecontent");
        System.out.println(in.available());
        FileOutputStream out = new FileOutputStream("d:/bbb.exe");
        byte[] b = new byte[1024];
        int len = 0;
        while ( (len = in.read(b)) != -1) {
          out.write(b, 0, len);
          out.flush();
        }
        out.close();
        in.close();
        rs.close();
        ps.close();
      }
    }
    catch (Exception ex) {
      ex.printStackTrace(System.out);
    }
    finally {
      try {conn.close();}
      catch (Exception ex) { }
    }
  }
}
 
 
sqlserver 大對象存取沒有什麼多說的,只要是image類型就行了,注意這是column類型,有人以為它只能存
圖象.image是文件鏡象的意思.
import java.sql.*;
import java.io.*;
public class DBTest {
 
 
  static String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
  static String url = "jdbc:microsoft:sqlserver://192.168.0.202:9999999999;DatabaseName=dddd";
  static String user = "sa";
  static String passwd = "ps";
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName(driver);
      conn = DriverManager.getConnection(url,user,passwd);
      int op = 0;
      //插入
      if (op == 0) {
        PreparedStatement ps = conn.prepareStatement("insert into tb_file values (?,?)");
        ps.setString(1, "aaa.exe");
        InputStream in = new FileInputStream("d:/aaa.exe");
        ps.setBinaryStream(2,in,in.available());
        ps.executeUpdate();
        ps.close();
      }
      else {
        //取出
        PreparedStatement ps = conn.prepareStatement("select * from  tb_file where filename = ?");
        ps.setString(1, "aaa.exe");
        ResultSet rs = ps.executeQuery();
        rs.next();
        InputStream in = rs.getBinaryStream("filecontent");
        System.out.println(in.available());
        FileOutputStream out = new FileOutputStream("d:/bbb.exe");
        byte[] b = new byte[1024];
        int len = 0;
        while ( (len = in.read(b)) != -1) {
          out.write(b, 0, len);
          out.flush();
        }
        out.close();
        in.close();
        rs.close();
        ps.close();
      }
    }
    catch (Exception ex) {
      ex.printStackTrace(System.out);
    }
    finally {
      try {conn.close();}
      catch (Exception ex) { }
    }
  }
}
 
 
 
ORACLE的大對象存儲有些變態,要無論是Blob,還是CLOB都要求先插入一個空值,然後
查詢並鎖定這一條記錄,獲取對Lob的引用再進行填充,網上有太多的例子.我個人認為
這種方法垃圾得連寫都不想寫了,你可以自己去搜索一下.
這種特別的操作既增加操作的復雜度,又違反了JDBC接口的規范,所以我極力反對這樣
使用,如果你和我有同樣的觀點.那麼我提供另一種通用的方法.就是你不用LOB而用
oracle的LONG RAW來代替它們.這樣就可以象其它對象一樣操作了:
 
create table tb_file(filename varchar2(255),filecontent LONG RAW);
 
 
import java.sql.*;
import java.io.*;
 
public class BlobTest {
 
  static String driver = "oracle.jdbc.driver.OracleDriver";
  static String url = "jdbc:oracle:thin:@localhost:1521:test";
  static String user = "system";
  static String passwd = "passwd";
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    try {
      Class.forName(driver);
      conn = DriverManager.getConnection(url, user, passwd);
      int op = 1;
      //插入
      if (op == 0) {
        PreparedStatement ps = conn.prepareStatement("insert into tb_file values (?,?)");
        ps.setString(1, "aaa.exe");
        InputStream in = new FileInputStream("d:/aaa.exe");
        ps.setBinaryStream(2,in,in.available());
        ps.executeUpdate();
        ps.close();
      }
      else {
        //取出
        PreparedStatement ps = conn.prepareStatement("select * from  tb_file where filename = ?");
        ps.setString(1, "aaa.exe");
        ResultSet rs = ps.executeQuery();
        rs.next();
        InputStream in = rs.getBinaryStream("filecontent");
        System.out.println(in.available());
        FileOutputStream out = new FileOutputStream("d:/bbb.exe");
        byte[] b = new byte[1024];
        int len = 0;
        while ( (len = in.read(b)) != -1) {
          out.write(b, 0, len);
          out.flush();
        }
        out.close();
        in.close();
        rs.close();
        ps.close();
      }
    }
    catch (Exception ex) {
      ex.printStackTrace(System.out);
    }
    finally {
      try {
        conn.close();
      }
      catch (Exception ex) {}
    }
  }
}

摘自 Mars學IT

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved