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,
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.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(); } } } } |
- 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? |