本篇內(nèi)容主要講解“MySQL、sqlserver和oracle三種數(shù)據(jù)庫(kù)的大對(duì)象存取方式”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“mysql、sqlserver和oracle三種數(shù)據(jù)庫(kù)的大對(duì)象存取方式”吧!
創(chuàng)新互聯(lián)主營(yíng)芝罘網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,app軟件開(kāi)發(fā),芝罘h5成都小程序開(kāi)發(fā)搭建,芝罘網(wǎng)站營(yíng)銷推廣歡迎芝罘等地區(qū)企業(yè)咨詢
大對(duì)象存取:
類型一般應(yīng)該用mediumblod,
blob只能存2的16次方個(gè)byte,
mediumblod是24次方,
一般來(lái)說(shuō)夠用了.longblob是32次方有些大.
MYSQL默認(rèn)配置只能存1M大小的文件,要修改配置,WIN版本的在mysql.ini文件中
修改max_allowed_packet,net_buffer_length等幾個(gè)參數(shù),或直接SET GLOBAL varName=value.
linux版本可以在啟動(dòng)參數(shù)后加-max_allowed_packet=xxM等幾個(gè)參數(shù).
MYSQL存大對(duì)象最好直接就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 大對(duì)象存取沒(méi)有什么多說(shuō)的,只要是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的大對(duì)象存儲(chǔ)有些變態(tài),要無(wú)論是Blob,還是CLOB都要求先插入一個(gè)空值,然后
查詢并鎖定這一條記錄,獲取對(duì)Lob的引用再進(jìn)行填充,網(wǎng)上有太多的例子.我個(gè)人認(rèn)為
這種方法垃圾得連寫都不想寫了,你可以自己去搜索一下.
這種特別的操作既增加操作的復(fù)雜度,又違反了JDBC接口的規(guī)范,所以我極力反對(duì)這樣
使用,如果你和我有同樣的觀點(diǎn).那么我提供另一種通用的方法.就是你不用LOB而用
oracle的LONG RAW來(lái)代替它們.這樣就可以象其它對(duì)象一樣操作了:
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.Driver";
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) {}
}
}
}
到此，相信大家對(duì)“mysql、sqlserver和oracle三種數(shù)據(jù)庫(kù)的大對(duì)象存取方式”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
重慶分公司
重慶分公司
