CREATE TABLE `person` ( `person_id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(45) DEFAULT NULL, `last_name` varchar(45) DEFAULT NULL, `photo` mediumblob, PRIMARY KEY (`person_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1We can notice that the column photohas type of mediumblob - which is one of four MySQL’s blob types:
void setBlob(int parameterIndex, InputStream inputStream)
And we have to supply an input stream of the file to be stored. For example:String filePath = "D:/Photos/Tom.jpg"; InputStream inputStream = new FileInputStream(new File(filePath)); String sql = "INSERT INTO person (photo) values (?)"; PreparedStatement statement = connection.prepareStatement(sql); statement.setBlob(1, inputStream); statement.executeUpdate();
package net.codejava.jdbc; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class JdbcInsertFileOne { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/contactdb"; String user = "root"; String password = "secret"; String filePath = "D:/Photos/Tom.png"; try { Connection conn = DriverManager.getConnection(url, user, password); String sql = "INSERT INTO person (first_name, last_name, photo) values (?, ?, ?)"; PreparedStatement statement = conn.prepareStatement(sql); statement.setString(1, "Tom"); statement.setString(2, "Eagar"); InputStream inputStream = new FileInputStream(new File(filePath)); statement.setBlob(3, inputStream); int row = statement.executeUpdate(); if (row > 0) { System.out.println("A contact was inserted with photo image."); } conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } catch (IOException ex) { ex.printStackTrace(); } } }In this case, the file’s content is transferred from client computer to the MySQL server. This technique is database independent because almost database engines support blob type and the JDBC driver takes care the data transfer transparently.
LOAD_FILE(file_path)
For example, the following program inserts a record into the person table with only the image file:package net.codejava.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class JdbcInsertFileTwo { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/contactdb"; String user = "root"; String password = "secret"; String filePath = "D:/Photos/Tom.png"; try { Connection conn = DriverManager.getConnection(url, user, password); String sql = "INSERT INTO person (photo) values (LOAD_FILE(?))"; PreparedStatement statement = conn.prepareStatement(sql); statement.setString(1, filePath); int row = statement.executeUpdate(); if (row > 0) { System.out.println("A contact was inserted with photo image."); } conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } } }In this case, the file must reside in the same machine as MySQL server. This technique is specific to MySQL only.
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4384068 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.Because 1 MB limit is quite small for binary file, so we usually have to set a higher value when working with blob type. There are two common ways for setting this limit:
max_allowed_packet=104857600
That sets the limit for about 10 MB (104,857,600 bytes).We can also configure the max_allowed_packet variable from the client by sending the following SQL statement before inserting the file:
SET GLOBAL max_allowed_packet=104857600;
That statement becomes effective until the server restarts.
For example, the following program sends a query to set new value for max_allowed_packet variable before inserting the file:package net.codejava.jdbc; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; public class JdbcInsertFileSetLimit { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/contactdb"; String user = "root"; String password = "secret"; String filePath = "D:/Photos/Tom.png"; try { Connection conn = DriverManager.getConnection(url, user, password); String querySetLimit = "SET GLOBAL max_allowed_packet=104857600;"; // 10 MB Statement stSetLimit = conn.createStatement(); stSetLimit.execute(querySetLimit); String sql = "INSERT INTO person (first_name, last_name, photo) values (?, ?, ?)"; PreparedStatement statement = conn.prepareStatement(sql); statement.setString(1, "Tom"); statement.setString(2, "Eagar"); InputStream inputStream = new FileInputStream(new File(filePath)); statement.setBlob(3, inputStream); int row = statement.executeUpdate(); if (row > 0) { System.out.println("A contact was inserted with photo image."); } conn.close(); inputStream.close(); } catch (SQLException ex) { ex.printStackTrace(); } catch (IOException ex) { ex.printStackTrace(); } } }NOTE:If the max_allowed_packet is already configured in the my.ini file, then the file will take precedence.