Store file into table by using JDBC with example

To store large data files or text files into database table, we will use CLOB (Charecter Large Object) datatype of SQL. By using CLOB it is possible to save entire text book or a file or resume into a column of data table. If your using MySQL database instead of CLOB use LONGTEXT to save file into datatable.

For saving files into a data table, we need to create one table which will contains a column type as CLOB to store files. Here, is the query to create a data table.

1 2 3 4

create table myfiles( file_id int(10), file_name varchar(30), file_type LONGTEXT);

Once table creation completes, these table contains the 3 columns one is file_id represents the file id, file_name represents the name of the file and finally file_type will get save entire file into the table column.

Now, we will move to write a JDBC program which will saves the file into the newly creating table. Here is the JDBC program,

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

package com.javatbrains.jdbc; import java.io.File; import java.io.FileNotFoundException; import java.io.FileReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class StoreTextFile { static Connection con = null; static PreparedStatement pst = null; public static void main(String[] args) throws FileNotFoundException { try { // Load file by using File File myFile = new File("D:/myFile.txt"); // To read data from file object myFile, Connect it to a FileReader. FileReader reader = new FileReader(myFile); // Register Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); // Establish Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test?user=root&password=root"); // Create a preparestatement pst = con.prepareStatement( "Insert into myfiles (file_id, file_name, file_type) values(?,?,?)"); // Set values to PST pst.setInt(1, 1); pst.setString(2, "MyFile"); pst.setCharacterStream(3, reader, (int) myFile.length()); // Execute the PreapreStatement pst.execute(); System.out.println("Inserted Successfully"); } catch (SQLException e) { e.printStackTrace(); } finally { try { con.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

In the above will stores an text file content into your MySQL datatable by reading the file. Here are the steps to create the JDBC program which will store the text file into MySQL database table.

• We need to load text file by using the File class and we will read the file instance by using the FileReader class.
• Then we need to register the Driver by using the registerDriver() method which will present under the DriverManager class.
• We need to create connection by using the getConnection() method which will present under the DriverManager class by providing the database information into String format.
• Create PrepareStatement instance and pass insert query into that.
• Setup the inserting records to PrepareStatement instance by index wise.
• For storing the text file into table, we used method as setCharecterStream() method and pass the FileReader instance and length of the file.
• Finally execute the PrepareStatement by using the execute() method.

Do you know?

• Retrieve data from mysql table by using JDBC example
• JDBC Architecture and insertion example
• Inserting images in MySQL database using JDBC
• Downloading images from MySQL database by using JDBC