JDBC- Retrieve FILE from database by using PreparedStatement's executeQuery and ResultSet's getClob method, using CLOB data type - in java



In this tutorial we will learn how to Retrieve FILE from database by using PreparedStatement's executeQuery and ResultSet's getClob method, using CLOB data type in java jdbc.



--Before executing java program >
Execute previous program to Insert file in database.


Example/ Full Programs JDBC- Retrieve file from database by using PreparedStatement's executeQuery method, using CLOB data type - in java
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class RetrieveFileExample {
   public static void main(String... arg) {
          Connection con =null;
          PreparedStatement prepStmt = null;
          ResultSet rs =null;
          Reader reader =null;
          FileOutputStream fout=null;
          String retrievedFilePath="c:\\myTxtRetrieved.txt";
          try {
                 // registering Oracle driver class
                 Class.forName("oracle.jdbc.driver.OracleDriver");
                 // getting connection
                 con = DriverManager.getConnection(
                              "jdbc:oracle:thin:@localhost:1521:orcl",
                              "ankit", "Oracle123");
                 System.out.println("Connection established successfully!");
                
                 //java.sql.Clob will be used to store file retrieved from database.
                 Clob clob=null;
                
                 prepStmt=con.prepareStatement("select ID, FILE_COL from TEST_FILES "
                              + "where ID = 1");
                 //execute select query
                 rs=prepStmt.executeQuery();
                 while(rs.next()){
                       clob=rs.getClob("FILE_COL");//Now, clob contains the file retrieved from database.
                 }
                
                 //Obtain STREAM OF CHARACTERS from clob into java.Io.Reader object
                 reader=clob.getCharacterStream();
                 fout=new FileOutputStream(retrievedFilePath);
                
                 //now read characters one by one from Reader object
                 int ch;
                 while( (ch=reader.read()) != -1){
                       fout.write(ch); //write characters in output file
                 }
         
                 System.out.println("File retrieved from database at > " +retrievedFilePath);                                     
                
          } catch (Exception e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(fout!=null) fout.close(); //close file
                       if(reader!=null) reader.close(); //close reader
                       if(rs!=null) rs.close(); //close resultSet
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 } catch (IOException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
File retrieved from database at > c:\myTxtRetrieved.txt
*/


Read : IMAGE - Storing in and retrieving out from database

In this tutorial we will learn how to Retrieve FILE from database by using PreparedStatement's executeQuery and ResultSet's getClob method, using CLOB data type in java jdbc.


RELATED LINKS>

java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE in java jdbc.

JDBC tutorial program- Insert/Store/save FILE in database

JDBC tutorial program- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


Execute database STORED PROCEDURE - IN parameter, OUT parameter and IN OUT parameter || call FUNCTION from java jdbc.

Difference between CLOB and CLOB data type in Oracle


No comments:

Post a Comment