Retrieve File from table by using JDBC with example

To retrieve file from table by using the JDBC here is the example. Let's, come to the first to database as MySQL and table name as myFiles and column name as file_type. These table data we have already inserted files in previous program.

Now, we should retrieve from the ResultSet using getClob() method.

1	Clob file = rs.getClob();

Here, getClob() method retrieving data from column and storing it into Clob class object as file.

Use getCharacterStream() method which will available under the Clob object, it also gets the file data from Clob object into a reader object.

1	Reader r = file.getCharacterStream();

Here, we are reding data from Reader object r and writing it into FileWriter which stores it into a file as D:/OutPutFile.txt.

Registering the Driver and creating Connection and Statements are same in the all JDBC programs. In query we are fetching only file column from 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 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

package com.javatbrains.jdbc; import java.io.FileWriter; import java.io.IOException; import java.io.Reader; import java.sql.Clob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class RetrieveFiles { static Connection con = null; static Statement st = null; static ResultSet rs = null; public static void main(String[] args) throws IOException { try { // 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 Statement st = con.createStatement(); // Here is the query to fetch file column from table String query = "Select file_type from myfiles"; // Execute the query by using the statement rs = st.executeQuery(query); FileWriter fw = null; while(rs.next()){ // go to first row Clob file = rs.getClob(1); /* * Read file data from Clob instance name as file and sore into * Reader object */ Reader r = file.getCharacterStream(); /* Read data from reader and write into newfile.txt */ int ch; fw = new FileWriter("D:/OutPutFile.txt"); while ((ch = r.read()) != -1) { fw.write((char) ch); } } fw.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { con.close(); st.close(); rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

Do you know?

• Store text files into MySQL database with JDBC example
• Retrieve data from MySQL table with JDBC example
• Inserting images in MySQL database using JDBC
• Downloading images from MySQL database by using JDBC