mysql,sqlserver,oracle三种数据库的大对象存取.doc

mysql,sqlserver,oracle三种数据库的大对象存取来源：诗剑书生专栏CSDN播客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 byte1024; 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 byte1024; 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 byte1024; int len = 0;