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){}
}
}
}