|
Posted on 2006-12-29 19:30 路易 阅读(555) 评论(0) 编辑 收藏 所属分类: 东方夜谭
1
mysql 大对象存取:
2
类型一般应该用mediumblod,
3
blob只能存2的16次方个byte,
4
mediumblod是24次方,
5
一般来说够用了.longblob是32次方有些大.
6
7
MYSQL默认配置只能存1M大小的文件,要修改配置,WIN版本的在mysql.ini文件中
8
修改max_allowed_packet,net_buffer_length等几个参数,或直接SET GLOBAL varName
=
value.
9
linux版本可以在启动参数后加
-
max_allowed_packet
=
xxM等几个参数.
10
11
MYSQL存大对象最好直接就setBinaryStream,又快又方便.
12
而不要先插入空再造型成BLOB然后再setBlob
13
14
例子:
15
import
java.sql.
*
;
16
import
java.io.
*
;
17
public
class
DBTest
{
18
19
20
static
String driver
=
"
org.gjt.mm.mysql.Driver
"
;
21
static
String url
=
"
jdbc:mysql://localhost:3306/test
"
;
22
static
String user
=
"
root
"
;
23
static
String passwd
=
"
passwd
"
;
24
public
static
void
main(String[] args)
throws
Exception
{
25
Connection conn
=
null
;
26
try
{
27
Class.forName(driver);
28
conn
=
DriverManager.getConnection(url,user,passwd);
29
30
int
op
=
1
;
31
//
插入
32
if
(op
==
0
)
{
33
PreparedStatement ps
=
conn.prepareStatement(
"
insert into tb_file values (?,?)
"
);
34
ps.setString(
1
,
"
aaa.exe
"
);
35
InputStream in
=
new
FileInputStream(
"
d:/aaa.exe
"
);
36
ps.setBinaryStream(
2
,in,in.available());
37
ps.executeUpdate();
38
ps.close();
39
}
40
else
{
41
//
取出
42
PreparedStatement ps
=
conn.prepareStatement(
"
select * from tb_file where filename = ?
"
);
43
ps.setString(
1
,
"
aaa.exe
"
);
44
ResultSet rs
=
ps.executeQuery();
45
rs.next();
46
InputStream in
=
rs.getBinaryStream(
"
filecontent
"
);
47
System.out.println(in.available());
48
FileOutputStream out
=
new
FileOutputStream(
"
d:/bbb.exe
"
);
49
byte
[] b
=
new
byte
[
1024
];
50
int
len
=
0
;
51
while
( (len
=
in.read(b))
!=
-
1
)
{
52
out.write(b,
0
, len);
53
out.flush();
54
}
55
out.close();
56
in.close();
57
rs.close();
58
ps.close();
59
}
60
}
61
catch
(Exception ex)
{
62
ex.printStackTrace(System.out);
63
}
64
finally
{
65
try
{conn.close();}
66
catch
(Exception ex)
{ }
67
}
68
}
69
}
70
71
72
sqlserver 大对象存取没有什么多说的,只要是image类型就行了,注意这是column类型,有人以为它只能存
73
图象.image是文件镜象的意思.
74
import
java.sql.
*
;
75
import
java.io.
*
;
76
public
class
DBTest
{
77
78
79
static
String driver
=
"
com.microsoft.jdbc.sqlserver.SQLServerDriver
"
;
80
static
String url
=
"
jdbc:microsoft:sqlserver://192.168.0.202:9999999999;DatabaseName=dddd
"
;
81
static
String user
=
"
sa
"
;
82
static
String passwd
=
"
ps
"
;
83
public
static
void
main(String[] args)
throws
Exception
{
84
Connection conn
=
null
;
85
try
{
86
Class.forName(driver);
87
conn
=
DriverManager.getConnection(url,user,passwd);
88
int
op
=
0
;
89
//
插入
90
if
(op
==
0
)
{
91
PreparedStatement ps
=
conn.prepareStatement(
"
insert into tb_file values (?,?)
"
);
92
ps.setString(
1
,
"
aaa.exe
"
);
93
InputStream in
=
new
FileInputStream(
"
d:/aaa.exe
"
);
94
ps.setBinaryStream(
2
,in,in.available());
95
ps.executeUpdate();
96
ps.close();
97
}
98
else
{
99
//
取出
100
PreparedStatement ps
=
conn.prepareStatement(
"
select * from tb_file where filename = ?
"
);
101
ps.setString(
1
,
"
aaa.exe
"
);
102
ResultSet rs
=
ps.executeQuery();
103
rs.next();
104
InputStream in
=
rs.getBinaryStream(
"
filecontent
"
);
105
System.out.println(in.available());
106
FileOutputStream out
=
new
FileOutputStream(
"
d:/bbb.exe
"
);
107
byte
[] b
=
new
byte
[
1024
];
108
int
len
=
0
;
109
while
( (len
=
in.read(b))
!=
-
1
)
{
110
out.write(b,
0
, len);
111
out.flush();
112
}
113
out.close();
114
in.close();
115
rs.close();
116
ps.close();
117
}
118
}
119
catch
(Exception ex)
{
120
ex.printStackTrace(System.out);
121
}
122
finally
{
123
try
{conn.close();}
124
catch
(Exception ex)
{ }
125
}
126
}
127
}
128
129
130
131
ORACLE的大对象存储有些变态,要无论是Blob,还是CLOB都要求先插入一个空值,然后
132
查询并锁定这一条记录,获取对Lob的引用再进行填充,网上有太多的例子.我个人认为
133
这种方法垃圾得连写都不想写了,你可以自己去搜索一下.
134
这种特别的操作既增加操作的复杂度,又违反了JDBC接口的规范,所以我极力反对这样
135
使用,如果你和我有同样的观点.那么我提供另一种通用的方法.就是你不用LOB而用
136
oracle的LONG RAW来代替它们.这样就可以象其它对象一样操作了:
137
138
create table tb_file(filename varchar2(
255
),filecontent LONG RAW);
139
140
141
import
java.sql.
*
;
142
import
java.io.
*
;
143
144
public
class
BlobTest
{
145
146
static
String driver
=
"
oracle.jdbc.driver.OracleDriver
"
;
147
static
String url
=
"
jdbc:oracle:thin:@localhost:1521:test
"
;
148
static
String user
=
"
system
"
;
149
static
String passwd
=
"
passwd
"
;
150
public
static
void
main(String[] args)
throws
Exception
{
151
Connection conn
=
null
;
152
try
{
153
Class.forName(driver);
154
conn
=
DriverManager.getConnection(url, user, passwd);
155
int
op
=
1
;
156
//
插入
157
if
(op
==
0
)
{
158
PreparedStatement ps
=
conn.prepareStatement(
"
insert into tb_file values (?,?)
"
);
159
ps.setString(
1
,
"
aaa.exe
"
);
160
InputStream in
=
new
FileInputStream(
"
d:/aaa.exe
"
);
161
ps.setBinaryStream(
2
,in,in.available());
162
ps.executeUpdate();
163
ps.close();
164
}
165
else
{
166
//
取出
167
PreparedStatement ps
=
conn.prepareStatement(
"
select * from tb_file where filename = ?
"
);
168
ps.setString(
1
,
"
aaa.exe
"
);
169
ResultSet rs
=
ps.executeQuery();
170
rs.next();
171
InputStream in
=
rs.getBinaryStream(
"
filecontent
"
);
172
System.out.println(in.available());
173
FileOutputStream out
=
new
FileOutputStream(
"
d:/bbb.exe
"
);
174
byte
[] b
=
new
byte
[
1024
];
175
int
len
=
0
;
176
while
( (len
=
in.read(b))
!=
-
1
)
{
177
out.write(b,
0
, len);
178
out.flush();
179
}
180
out.close();
181
in.close();
182
rs.close();
183
ps.close();
184
}
185
}
186
catch
(Exception ex)
{
187
ex.printStackTrace(System.out);
188
}
189
finally
{
190
try
{
191
conn.close();
192
}
193
catch
(Exception ex)
{}
194
}
195
}
196
}
197
原地址: http://dev.csdn.net/author/axman/1ca2ede425e44dba9ac20c2e262e4fb8.html
|