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

mysql大对象存取: 类型一般应该用mediumblod, blob只能存2的16次方个byte, mediumblod是24次方, 一般来说够用了.longblob是32次方有些大. MYSQL默认配置只能存1M大小的文件,要修改配置,WIN版本的在mysql.ini文件中 修改max_allowed_packet,net_buffer_length等几个参数,或直接SETGLOBALvarName=value. linux版本可以在启动参数后加-max_allowed_packet=xxM等几个参数. MYSQL存大对象最好直接就setBinaryStream,又快又方便. 而不要先插入空再造型成BLOB然后再setBlob 例子: importjava.sql.*; importjava.io.*; publicclassDBTest{ staticStringdriver="org.gjt.mm.mysql.Driver"; staticStringurl="jdbc:mysql://localhost:3306/test"; staticStringuser="root"; staticStringpasswd="passwd"; publicstaticvoidmain(String[]args)throwsException{ Connectionconn=null; try{ Class.forName(driver); conn=DriverManager.getConnection(url,user,passwd); intop=1; //插入 if(op==0){ PreparedStatementps=conn.prepareStatement("insertintotb_filevalues(?,?)"); ps.setString(1,"aaa.exe"); InputStreamin=newFileInputStream("d:/aaa.exe"); ps.setBinaryStream(2,in,in.available()); ps.executeUpdate(); ps.close(); } else{ //取出 PreparedStatementps=conn.prepareStatement("select*fromtb_filewherefilename=?"); ps.setString(1,"aaa.exe"); ResultSetrs=ps.executeQuery(); rs.next(); InputStreamin=rs.getBinaryStream("filecontent"); System.out.println(in.available()); FileOutputStreamout=newFileOutputStream("d:/bbb.exe"); byte[]b=newbyte[1024]; intlen=0; while((len=in.read(b))!=-1){ out.write(b,0,len); out.flush(); } out.close(); in.close(); rs.close(); ps.close(); } } catch(Exceptionex){ ex.printStackTrace(System.out); } finally{ try{conn.close();} catch(Exceptionex){} } } } sqlserver大对象存取没有什么多说的,只要是image类型就行了,注意这是column类型,有人以为它只能存 图象.image是文件镜象的意思. importjava.sql.*; importjava.io.*; publicclassDBTest{ staticStringdriver="com.microsoft.jdbc.sqlserver.SQLServerDriver"; staticStringurl="jdbc:microsoft:sqlserver://192.168.0.202:9999999999;DatabaseName=dddd"; staticStringuser="sa"; staticStringpasswd="ps"; publicstaticvoidmain(String[]args)throwsException{ Connectionconn=null; try{ Class.forName(driver); conn=DriverManager.getConnection(url,user,passwd); intop=0; //插入 if(op==0){ PreparedStatementps=conn.prepareStatement("insertintotb_filevalues(?,?)"); ps.setString(1,"aaa.exe"); InputStreamin=newFileInputStream("d:/aaa.exe"); ps.setBinaryStream(2,in,in.available()); ps.executeUpdate(); ps.close(); } else{ //取出 PreparedStatementps=conn.prepareStatement("select*fromtb_filewherefilename=?"); ps.setString(1,"aaa.exe"); ResultSetrs=ps.executeQuery(); rs.next(); InputStreamin=rs.getBinaryStream("filecontent"); System.out.println(in.available()); FileOutputStreamout=newFileOutputStream("d:/bbb.exe"); byte[]b=newbyte[1024]; intlen=0; while((len=in.read(b))!=-1){ out.write(b,0,len); out.flush(); } out.close(); in.close(); rs.close(); ps.close(); } } catch(Exceptionex){ ex.printStackTrace(System.out); } finally{ try{conn.close();} catch(Exceptionex){} } } } ORACLE的大对象存储有些变态,要无论是Blob,还是CLOB都要求先插入一个空值,然后 查询并锁定这一条记录,获取对Lob的引用再进行填充,网上有太多的例子.我个人认为 这种方法垃圾得连写都不想写了,你可以自己去搜索一下. 这种特别的操作既增加操作的复杂度,又违反了JDBC接口的规范,所以我极力反对这样 使用,如果你和我有同样的观点.那么我提供另一种通用的方法.就是你不用LOB而用 oracle的LONGRAW来代替它们.这样就可以象其它对象一样操作了: createtabletb_file(filenamevarchar2(255),filecontentLONGRAW); importjava.sql.*; importjava.io.*; publicclassBlobTest{ staticStringdriver="oracle.jdbc.driver.OracleDriver"; staticStringurl="jdbc:oracle:thin:@localhost:1521:test"; staticStringuser="system"; staticStringpasswd="passwd"; publicstaticvoidmain(String[]args)throwsException{ Connectionconn=null; try{ Class.forName(driver); conn=DriverManager.getConnection(url,user,passwd); intop=1; //插入 if(op==0){ PreparedStatementps=conn.prepareStatement("insertintotb_filevalues(?,?)"); ps.setString(1,"aaa.exe"); InputStreamin=newFileInputStream("d:/aaa.exe"); ps.setBinaryStream(2,in,in.available()); ps.executeUpdate(); ps.close(); } else{ //取出 PreparedStatementps=conn.prepareStatement("select*fromtb_filewherefilename=?"); ps.setString(1,"aaa.exe"); ResultSetrs=ps.executeQuery(); rs.next(); InputStreamin=rs.getBinaryStream("filecontent"); System.out.println(in.available()); FileOutputStreamout=newFileOutputStream("d:/bbb.exe"); byte[]b=newbyte[1024]; intlen=0; while((len=in.read(b))!=-1){ out.write(b,0,len); out.flush(); } out.close(); in.close(); rs.close(); ps.close(); } } catch(Exceptionex){ ex.printStackTrace(System.out); } finally{ try{ conn.close(); } catch(Exceptionex){} } } }