Mysql inserts binary files and blob types. Problems and Solutions
Mysql inserts binary files and blob types. Problems and Solutions
The first step is to prepare a database:
We need to set the binary field to the Blob type, and select the appropriate Blob type based on the file size. The following figure shows the size of each Blob type that can accommodate binary files.
Four BLOB types in MySQL
Type size (unit: bytes)
TinyBlob Max. 255
Blob Max 65 K
MediumBlob up to 16 MB
Up to 4 GB LongBlob
The specific operation code is as follows:
The Code is as follows:
/**
*
* Store the binary file (which can be a local hard disk path or a network path) to the database.
* Create date: 2009-5-13 author: Administrator
*
* @ Param file
* It can be a local file or a network file
* @ Param conn
*/
Public void saveBinary (String file, Connection conn ){
// Pay attention to the classes used for writing binary files to the database and the class packaging and conversion process
File f = null;
If (file. toLowerCase (). contains ("http :"))
F = DownLoadWithUrl. downLoadFile (file );
Else
F = new File (file );
If (f! = Null ){
Try {
InputStream is = new FileInputStream (f );
PreparedStatement ps = conn
. PrepareStatement ("insert into bankVoice (name, text) values (?,?) ");
Ps. setString (1, file );
Int I = is. available ();
Ps. setBinaryStream (2, is, is. available ());
Ps.exe cuteUpdate ();
System. out. println ("binary file inserted successfully ");
Ps. clearParameters ();
Ps. close ();
Is. close ();
} Catch (Exception e ){
E. printStackTrace ();
System. out. println ("an exception occurred when inserting binary files ");
}
}
}
Note that the following exception will occur during the operation, then we just need to make the settings: Take my local as an example: enter D: \ MySql5.0 \ mysql-5.0.51b-win32 directory, there are the following files can be seen: my-large.ini, my-small.ini, my-medium.ini, my-huge.ini
Change the configuration item max_allowed_packet In the INI file currently loaded by mysql to 16 M.
That is: max_allowed_packet = 16 M the default value is 1 M. We change it to 16 M, and then restart the mysql server so that the following exceptions will not occur.
The Code is as follows:
Com. mysql. jdbc. PacketTooBigException: Packet for query is too large (1048587> 1047552). You can change this value on the server by setting the max_allowed_packet 'variable.
At com. mysql. jdbc. MysqlIO. send (MysqlIO. java: 2632)
At com. mysql. jdbc. MysqlIO. send (MysqlIO. java: 2618)
At com. mysql. jdbc. MysqlIO. sendCommand (MysqlIO. java: 1551)
At com. mysql. jdbc. ServerPreparedStatement. storeStream (ServerPreparedStatement. java: 2180)
At com. mysql. jdbc. ServerPreparedStatement. serverLongData (ServerPreparedStatement. java: 1199)
At com. mysql. jdbc. ServerPreparedStatement. serverExecute (ServerPreparedStatement. java: 1004)
At com.mysql.jdbc.ServerPreparedStatement.exe cuteInternal (ServerPreparedStatement. java: 670)
At com.mysql.jdbc.PreparedStatement.exe cuteUpdate (PreparedStatement. java: 1159)
At com.mysql.jdbc.PreparedStatement.exe cuteUpdate (PreparedStatement. java: 1076)
At com.mysql.jdbc.PreparedStatement.exe cuteUpdate (PreparedStatement. java: 1061)
At SaveBinaryToDB. SaveBinaryToDB. saveBinary (SaveBinaryToDB. java: 33)
At SaveBinaryToDB. SaveBinaryToDB. main (SaveBinaryToDB. java: 17)
/**
* Read the binary file "create date: 2009-5-13 author: Administrator" from the database.
*
* @ Param file
* @ Param conn
*/
Public void getBinary (String file, Connection conn ){
// Pay attention to the classes used for reading binary files from the database and the packaging and conversion process of classes.
Try {
PreparedStatement ps = conn
. PrepareStatement ("select text from bankVoice where name =? ");
Ps. setString (1, file );
Blob blob = null;
ResultSet rs = ps.exe cuteQuery ();
If (rs. next ()){
Blob = (Blob) rs. getBlob ("text ");
}
FileOutputStream fos = new FileOutputStream ("D: \ test1.mp3 ");
Fos. write (blob. getBytes (1, (int) blob. length ()));
System. out. println ("the binary file is successfully obtained ");
Ps. clearParameters ();
Ps. close ();
Fos. close ();
} Catch (Exception e ){
E. printStackTrace ();
System. out. println ("an exception occurred when reading the binary file ");
}
}
Package SaveBinaryToDB;
The Code is as follows:
/**
* The functions of this program are downloaded over the network.
* Download the file from the specified url to the local hard disk.
*
*/
Import java. io .*;
Import java.net .*;
/**
* @ Todo: stores images obtained from the Internet, mp3 files, and other files locally.
*
* @ Version 1.0
*/
Public class DownLoadWithUrl {
Public static File downLoadFile (String fromUrl ){
URL url;
File file = null;
Try {
// Url = new
// URL ("http://count.koubei.com/showphone/showphone.php? F = jpg & w = 96 & h = 10 & bc = 255,255,255 & fc = 236, 0 & fs = 10 & fn = arial & phone = NzMwNzIyNTE1 % aWCXtTNZYkxASrj ");
Url = new URL (fromUrl );
URLConnection uc = url. openConnection ();
InputStream is = uc. getInputStream ();
// Name the object based on the type of the downloaded object
File = new File ("D: \ foreverworkflow ");
FileOutputStream out = new FileOutputStream (file );
/*
* This comment is also a method for writing files. However, mp3 is usually downloaded or smaller than mp3.
* These files are slow to write files using this buffer method. Therefore, the following method is usually used for downloading small files: // byte [] B = new
* Byte [102400*3]; // int size = 0; // while (size = is. read (B ))! =
*-1) {// out. write (B, 1, size );////}
*/
Int I = 0;
While (I = is. read ())! =-1 ){
Out. write (I );
}
Out. flush ();
Is. close ();
} Catch (Exception e ){
// TODO Auto-generated catch block
E. printStackTrace ();
}
Return file;
}
/**
* Delete the file "create date: 2009-5-13 author: Administrator" in the specified path of the local disk.
*
* @ Param file
*/
Public static void delFile (String file ){
File f = new File (file );
If (f. exists ())
F. delete ();
System. out. println (file + "deleted ");
}
Public static void main (String [] args ){
// DelFile ("D: \ foreverworkflow ");
DownLoadFile ("");
}
}
