How can we retrieve file from database using JDBC?
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
Published on 22-Mar-2019 15:51:43
