Teach with us

How can we retrieve file from database using JDBC?

MySQLMySQLi DatabaseJava 8JDBC

The ResultSet interface provides the methods named getClob() and getCharacterStream() to retrieve Clob datatype, In which the contents of a file are typically stored.

These methods accept an integer representing the index of the column (or, a String value representing the name of the column) and retrieves the value at the specified column.

The difference is the getClob() method returns a Clob object and the getCgaracterStream() method returns a Reader object which holds the contents of the Clob datatype.

Example

Assume we have created a table named Articles in the database with the following description.

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| Name    | varchar(255) | YES  |     | NULL    |       |
| Article | longtext     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

And, we have inserted three articles in it with names article 1, article 2 and, article 3 as shown below:

Example

Following program retrieves the contents of the table Articles using the getString() and getClob() methods and saves it in the specified files.

import java.io.FileWriter;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RetrievingFileFromDatabase {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating aStatement
      Statement stmt = con.createStatement();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Articles");
      int j = 0;
      System.out.println("Contents of the table are: ");
      while(rs.next()) {
         System.out.println(rs.getString("Name"));
         Clob clob = rs.getClob("Article");
         Reader reader = clob.getCharacterStream();
         String filePath = "E:\Data\clob_output"+j+".txt";
         FileWriter writer = new FileWriter(filePath);
         int i;
         while ((i = reader.read())!=-1) {
            writer.write(i);
         }
         writer.close();
         System.out.println(filePath);
         j++;
      }
   }
}

Output

Connection established......
Contents of the table are:
article1
E:\Data\clob_output0.txt
article2
E:\Data\clob_output1.txt
article3
E:\Data\clob_output2.txt
raja
Krantik Chavan
Updated on 27-Jun-2020 06:18:17

Previous Page Print Page Next Page  
Advertisements