How to store file in MySQL database using JDBC

In this post we will look into how to store text file/CLOB data in MYSQL database using JDBC. If you are using some other database like Oracle,Sybase or DB2, still process would be similar.Create a folder InputFiles or folder with any name in project home directory as shown in the given screen shot.
After that add three text files:C language.txt,C++ language.txt and Java language.txt Or you can create text files with some text content with any name as per your choice.

Project structure in eclipse:

SQL Query to create storetextfile_table in MySQL database (jdbcdb schema)
DB.sql

CREATE  TABLE `storetextfile_table` (
  `file_id` INT NOT NULL AUTO_INCREMENT ,
  `file_name` VARCHAR(45) NOT NULL ,
  `file_size_in_kb` BIGINT NOT NULL ,
  `file_extension` CHAR(30) NOT NULL ,
  `file_content` LONGTEXT NOT NULL ,
  PRIMARY KEY (`file_id`) );

CREATE TABLE `storetextfile_table` (

`file_id` INT NOT NULL AUTO_INCREMENT ,

`file_name` VARCHAR(45) NOT NULL ,

`file_size_in_kb` BIGINT NOT NULL ,

`file_extension` CHAR(30) NOT NULL ,

`file_content` LONGTEXT NOT NULL ,

PRIMARY KEY (`file_id`) );

DBUtil.java class which is responsible to connect with MySQL database.

package com.infotech.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
	private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
	private static final String DB_USERNAME="root";
	private static final String DB_PASSWORD="root";
	private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";
	
	private static Connection connection = null;
	static{
		try {
			Class.forName(DB_DRIVER_CLASS);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";

private static final String DB_USERNAME="root";

private static final String DB_PASSWORD="root";

private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";

private static Connection connection = null;

static{

try {

Class.forName(DB_DRIVER_CLASS);

connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

public static Connection getConnection(){

return connection;

}

Below ClientTest.java class uses to save three text files(C language.txt,C++ language.txt and Java language.txt)

package com.infotech.client;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import com.infotech.util.DBUtil;

public class ClientTest {
	
	public static void main(String[] args) throws SQLException   {
		
		String SQL="INSERT INTO storetextfile_table (file_name,file_size_in_kb,file_extension,file_content)VALUES(?,?,?,?)";
		Path dir = Paths.get("InputFiles");
		try(Stream<Path> list = Files.list(dir);Connection connection = DBUtil.getConnection();
				PreparedStatement ps = connection.prepareStatement(SQL)) {
			/*
			 *Below line belong to JDK 1.8 API so make sure you are running this code on JDK 1.8.
			 *And you IDE pointing to compiler version 1.8
			 */
			List<Path> pathList = list.collect(Collectors.toList());
			System.out.println("Following files are saved in database..");
			for (Path path : pathList) {
				System.out.println(path.getFileName());
				File file = path.toFile();
				String fileName = file.getName();
				long fileLength = file.length();
				long fileLengthInKb=fileLength/1024;
				
				ps.setString(1, fileName);
				ps.setLong(2, fileLengthInKb);
				
				ps.setString(3, fileName.substring(fileName.lastIndexOf(".")+1));
				ps.setCharacterStream(4, new FileReader(file), fileLength);
				
				ps.addBatch();
			}
			System.out.println("----------------------------------------");
			int[] executeBatch = ps.executeBatch();
			for (int i : executeBatch) {
				System.out.println(i);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
}

package com.infotech.client;

import java.io.File;

import java.io.FileReader;

import java.io.IOException;

import java.nio.file.Files;

import java.nio.file.Path;

import java.nio.file.Paths;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.List;

import java.util.stream.Collectors;

import java.util.stream.Stream;

import com.infotech.util.DBUtil;

public class ClientTest {

public static void main(String[] args) throws SQLException {

String SQL="INSERT INTO storetextfile_table (file_name,file_size_in_kb,file_extension,file_content)VALUES(?,?,?,?)";

Path dir = Paths.get("InputFiles");

try(Stream<Path> list = Files.list(dir);Connection connection = DBUtil.getConnection();

PreparedStatement ps = connection.prepareStatement(SQL)) {

*Below line belong to JDK 1.8 API so make sure you are running this code on JDK 1.8.

*And you IDE pointing to compiler version 1.8

List<Path> pathList = list.collect(Collectors.toList());

System.out.println("Following files are saved in database..");

for (Path path : pathList) {

System.out.println(path.getFileName());

File file = path.toFile();

String fileName = file.getName();

long fileLength = file.length();

long fileLengthInKb=fileLength/1024;

ps.setString(1, fileName);

ps.setLong(2, fileLengthInKb);

ps.setString(3, fileName.substring(fileName.lastIndexOf(".")+1));

ps.setCharacterStream(4, new FileReader(file), fileLength);

ps.addBatch();

}

System.out.println("----------------------------------------");

int[] executeBatch = ps.executeBatch();

for (int i : executeBatch) {

System.out.println(i);

}

} catch (IOException e) {

e.printStackTrace();

}

After running ClientTest.java program you will look below output on eclipse console:

Following files are saved in database..
C language.txt
C++ language.txt
Java language.txt
----------------------------------------
1
1
1

Following files are saved in database..

C language.txt

C++ language.txt

Java language.txt

----------------------------------------

Above console output clearly shows that all three files are saved in MSQL database.

If you query database table storetextfile_table then you will find three records are inserted as below:

That’s all about how to store file in MySQL database using JDBC

You May Also Like:

How to read files/CLOB data from MySQL database using JDBC
Writing and Reading MySQL BLOB/binary files Using JDBC
ResultSetMetaData in JDBC example
How to get primary key value (auto-generated keys) from inserted queries using JDBC?

If you have any feedback or suggestion please feel free to drop in below comment box.

Leave a Reply Cancel reply