java implementation inserts mysql binaries blob types encounters problems and fixes
- 2020-05-07 20:34:08
- OfStack
The first is to prepare for the database establishment:
We will set the place 2-base field to Blob type, and select the appropriate Blob type according to the size of the file. 1 is the size of the 2-base file that each Blob type can hold
Four BLOB types of MySQL
Type size (in bytes)
TinyBlob maximum 255
65 K Blob biggest
16 M MediumBlob biggest
4 G LongBlob biggest
1. The following is the specific operation code:
Note that the following exceptions will occur during operation, so we only need to do 1 setting: take my local as an example: go to D:\ MySql5.0 \ mysql-5.0.51 b-win32 directory, the following files can be seen: my-large.ini, my-small.ini, my-medium.ini, my-huge.ini
We simply change the configuration item in the ini file that the mysql service now loads: max_allowed_packet to 16M
max_allowed_packet = 16M the default is 1M let's change to 16M and then restart the mysql server so that the following exception does not occur.
package SaveBinaryToDB;
We will set the place 2-base field to Blob type, and select the appropriate Blob type according to the size of the file. 1 is the size of the 2-base file that each Blob type can hold
Four BLOB types of MySQL
Type size (in bytes)
TinyBlob maximum 255
65 K Blob biggest
16 M MediumBlob biggest
4 G LongBlob biggest
1. The following is the specific operation code:
/**
*
* the 2 A binary file ( the 2 A base file can be a local hard disk path or it can be 1 Network path ) Stored in 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 2 The classes used when the base file is written to the database, and the class wrapper 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.executeUpdate();
System.out.println("2 Base file inserted successfully ");
ps.clearParameters();
ps.close();
is.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("2 An exception occurred while the base file was being inserted ");
}
}
}
Note that the following exceptions will occur during operation, so we only need to do 1 setting: take my local as an example: go to D:\ MySql5.0 \ mysql-5.0.51 b-win32 directory, the following files can be seen: my-large.ini, my-small.ini, my-medium.ini, my-huge.ini
We simply change the configuration item in the ini file that the mysql service now loads: max_allowed_packet to 16M
max_allowed_packet = 16M the default is 1M let's change to 16M and then restart the mysql server so that the following exception does not occur.
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.executeInternal(ServerPreparedStatement.java:670)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061)
at SaveBinaryToDB.SaveBinaryToDB.saveBinary(SaveBinaryToDB.java:33)
at SaveBinaryToDB.SaveBinaryToDB.main(SaveBinaryToDB.java:17)
/**
* Read from the database 2 A binary file create date:2009-5-13 author:Administrator
*
* @param file
* @param conn
*/
public void getBinary(String file, Connection conn) {
// Pay attention to 2 The classes used when the base file is read from the database, and the wrapper transformation process for the classes
try {
PreparedStatement ps = conn
.prepareStatement("select text from bankVoice where name=?");
ps.setString(1, file);
Blob blob = null;
ResultSet rs = ps.executeQuery();
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("2 Base file successful ");
ps.clearParameters();
ps.close();
fos.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println("2 An exception occurred while reading the base file ");
}
}
package SaveBinaryToDB;
/**
* The function of this procedure to achieve network download
* The specified url Download the files to your local hard drive
*
*/
import java.io.*;
import java.net.*;
/**
* @todo Images will be captured online ,mp3 Wait for the file to be stored 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=0,0,0&fs=10&fn=arial&phone=NzMwNzIyNTE1%236aWCXtTNZYkxASrj");
url = new URL(fromUrl);
URLConnection uc = url.openConnection();
InputStream is = uc.getInputStream();
// According to the type of download file, the corresponding file name
file = new File("D:\\forever.mp3");
FileOutputStream out = new FileOutputStream(file);
/*
* So is the one in the comment 1 A way to write to a file, but it is usually downloaded mp3 Or better than mp3 Smaller pictures
* Wait for these files to use this buffered method to write the file is relatively slow, so small file download usually use the following The method of writing a file is ok // 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;
}
/**
* Deletes the file for the specified path on the local disk create date:2009-5-13 author:Administrator
*
* @param file
*/
public static void delFile(String file) {
File f = new File(file);
if (f.exists())
f.delete();
System.out.println(file + " It has been deleted ");
}
public static void main(String[] args) {
// delFile("D:\\forever.mp3");
downLoadFile("");
}
}