Insert file data into MySQL database using JDBC

In this JDBC tutorial, you will learn how to insert binary data (or file data) into database.

You know, to store a file into a database table, the table must have a column whose data type is BLOB (Binary Large OBject). Assuming we have a MySQL table called person which is created by the following SQL script:

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=latin1

We can notice that the column photohas type of mediumblob - which is one of four MySQL’s blob types:

• • TINYBLOB: 255 bytes
  • BLOB: 65,535 bytes (64 KB)
  • MEDIUMBLOB: 16,777,215 bytes (16 MB)
  • LONGBLOB: 4 GB

That means the photo column can store a file up to 16 MB. You can choose which blob type, depending on your need.

Now we discuss some techniques used to insert content of a file into the table person, using JDBC.

1. Insert file using standard JDBC API (database independent)

To store content of a file (binary data) into the table, we can use the following method defined by the interface java.sql.PreparedStatement:

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();

Where connection is a database connection represented by a java.sql.Connection object.

The following program connects to a MySQL database called contactdb and inserts a record with an image file into the table person:

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.

2. Insert file using specific MySQL syntax

Beside using JDBC’s method setBlob() of the PreparedStatement interface, we can also use MySQL syntax to achieve the same thing, with the LOAD_FILE() command:

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.

3. MySQL’s limitation on packet size

By default, MySQL sets a limit on the amount of data can be sent in a query (including both the file data and other query’s data). This limit is 1MB and can be configured via a property called max_allowed_packet. If we are trying to store a file whose size exceeds this limit, MySQL will throw this error:

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:

• Via MySQL’s configuration file my.ini:

  Open my.ini file and append the following line at the end:

  max_allowed_packet=104857600

  That sets the limit for about 10 MB (104,857,600 bytes).

• Via SQL statement:

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.

Other JDBC Tutorials:

• How to read file data from database with JDBC
• How to connect to a database with JDBC
• JDBC CRUD Tutorial
• JDBC Transaction Tutorial
• How to call stored procedure with JDBC
• How to read database metadata in JDBC

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:

JdbcInsertFileOne.java	[ ]	1 kB
JdbcInsertFileSetLimit.java	[ ]	1 kB
JdbcInsertFileTwo.java	[ ]	0.8 kB
PersonTableScript.sql	[ ]	0.2 kB