Sometimes we have a requirement to insert file into database rather then storing it in the file system. For a developer, it is always hard to decide this. You can find good discussion in different forums on web over storing the file either in database or file system. I believe choosing the same depends on the project requirement rather than preferences. You can choose database over file system when file size is small and user’s file needs to be more tightly coupled, secured and confidential. Since this discussion is out of scope of this article, here is a good resource, Which is Better ? Saving Files in Database or File System for your reference.
Well, today we will see how to insert image file into MySQL database using JDBC. We will use BLOB datatype of MySQL for storing files.
Tools & Technologies
- MySQL database
- Eclipse Oxygen
- Google Guava : guava-27.0.1-jre.jar
- MySQL Connector : mysql-connector-java-5.1.6.jar
The MySQL BLOB types and size
A BLOB (Binary Large Object) is a data type that can be used to store a large collection of binary data in a database table. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. So BLOB would be a suitable choice type when we want to store files of varying types like pdf, doc, audio or videos, images etc.
Creating table in MySQL database
We will create a table called “student” in MySQL database where a column of type MEDIUMBLOB is specified to insert the student image.
CREATE TABLE 'student' ( 'student_id' INT(11) NOT NULL AUTO_INCREMENT, 'full_name' VARCHAR(30) DEFAULT NULL, 'last_name' VARCHAR(30) DEFAULT NULL, 'photo' MEDIUMBLOB, PRIMARY KEY ('student_id') );
Creating a Java Project In Eclipse
Create a java project in eclipse with name “mysql-file-insert-demo” or name of your choice. The final project structure in eclipse will be the following.
Create a folder called “lib” inside “mysql-file-insert-demo” with following two dependencies.
Guava is google’s core libraries for java. I have used this only to print the stack trace in case of any exception. MySQL connector jar is JDBC Type 4 driver for MySQL.
Make sure you have added both dependencies to the project’s build path.
The Properties file
We are not going to hard-code the database related metadata in the code. So create a folder “mysql-file-insert-config” and also create a properties file “DBConnection.properties” inside it with the following content.
The Configuration file
Create a configuration file named “DBConfig.java” inside “com.dev.code.adda.config” package. This configuration file is created to read the key from the properties file “DBConnection.properties” and store it into the java variables so that it can be used to create database connection.
The Utility class
Create a utility class called “DBUtility.java” inside package “com.dev.code.adda.utility“. This class contains the utilities method to create the database connection and close the database connection.
Main class to insert image into database
Create a class named “InsertFile.java” inside package “com.dev.code.adda.service“. This is the main class where we will create a database connection and use the “void java.sql.PreparedStatement.setBlob(int parameterIndex, InputStream inputStream) throws SQLException” in order to set the image to be inserted into the database.
Set the value for the SQL_INSERT_QUERY “INSERT INTO student(full_name,last_name,photo) VALUES (?,?,?)” where “photo” is the actual image file to be inserted into the database. Run the “InsertFile.java” as “Java Application”.
Check if the file has been inserted into the database. Run the following query in the MySQL Command Line Tool.
select count(*) from student;
You can even see the actual image file if you have any SQL Client installed in your machine.
Thats all about this article. Its pretty simple to insert files into MySQL database. I hope you have enjoyed this article. Kindly subscribe our blog to get updates on similar interesting article. Keep Learning…Happy Koding!