學習是為了明天，為了更好的明天，需要更努力的學習！

??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等幾個參數(shù),或直接SET?GLOBAL?varName = value.
??9 linux版本可以在啟動參數(shù)后加 - max_allowed_packet = xxM等幾個參數(shù).
?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的大對象存儲有些變態(tài),要無論是Blob,還是CLOB都要求先插入一個空值,然后
132 查詢并鎖定這一條記錄,獲取對Lob的引用再進行填充,網上有太多的例子.我個人認為
133 這種方法垃圾得連寫都不想寫了,你可以自己去搜索一下.
134 這種特別的操作既增加操作的復雜度,又違反了JDBC接口的規(guī)范,所以我極力反對這樣
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