Large Object Access for mysql, sqlserver, and oracle databases

Mysql large object access:
The mediumblod type is generally used,
Blob can only store 16 bytes to the power of 2,
Mediumblod is the power of 24,
Generally enough. longblob is a bit large to the power of 32.
 
By default, MYSQL can only store files of 1 MB in size. to modify the configuration, the Windows version is in the mysql. ini file.
Modify max_allowed_packet, net_buffer_length, and other parameters, or directly set global varName = value.
In linux, you can add parameters such as-max_allowed_packet = xxM after the startup parameter.
 
It is recommended that MYSQL store large objects directly with setBinaryStream, which is fast and convenient.
Instead of inserting null, styling it as BLOB, and then setBlob.
 
Example:
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;
// Insert
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.exe cuteUpdate ();
Ps. close ();
}
Else {
// Retrieve
PreparedStatement ps = conn. prepareStatement ("select * from tb_file where filename =? ");
Ps. setString (1, "aaa.exe ");
ResultSet rs = ps.exe cuteQuery ();
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 ){}
}
}
}
 
 
There is nothing to say about large object access to sqlserver, as long as it is of the image type. Note that this is the column type and some people think it can only be stored.
Image. image indicates the object 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;
// Insert
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.exe cuteUpdate ();
Ps. close ();
}
Else {
// Retrieve
PreparedStatement ps = conn. prepareStatement ("select * from tb_file where filename =? ");
Ps. setString (1, "aaa.exe ");
ResultSet rs = ps.exe cuteQuery ();
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's large object storage service is abnormal. Both Blob and CLOB require that a null value be inserted first, and then
Query and lock this record, obtain the Lob reference, and then fill in the record. There are too many examples on the Internet. I personally think
This method does not need to be written. You can search for it by yourself.
This special operation not only increases the complexity of the operation, but also violates the JDBC interface specification, so I strongly oppose this
If you and I share the same view, then I provide another general method, that is, you don't need to use LOB.
Oracle's long raw is used to replace them. In this way, you can operate like other objects:
 
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. OracleDriver ";
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;
// Insert
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.exe cuteUpdate ();
Ps. close ();
}
Else {
// Retrieve
PreparedStatement ps = conn. prepareStatement ("select * from tb_file where filename =? ");
Ps. setString (1, "aaa.exe ");
ResultSet rs = ps.exe cuteQuery ();
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 ){}
}
}
}

From learning IT from Mars