JDBC- Insert/Store/save FILE in database by using PreparedStatement's executeUpdate and setCharacterStream methods, using CLOB data type - in java



In this tutorial we will learn how to Insert/Store/save FILE in database by using PreparedStatement's executeUpdate and setCharacterStream methods, using CLOB data type in java jdbc.



We must use CLOB data type for text files, why not BLOB?

What is CLOB data type in Oracle database >
CLOB stands for Character Large Object.

CLOB stores values in character streams.

CLOB is used for storing single-byte character data (Character string made up of single-byte character data).

CLOB data type is appropriate for storing text information. Example >
  • text files,
  • pdf,
  • doc,
  • docx and
  • odf formats.

CLOB must be used when amount of information to be stored is more than varchar data type (i.e. more than 32kB).


--Before executing java program execute these database scripts  >
create table TEST_FILES(ID number(4), FILE_COL clob);

And keep some file in c:/myTxt.txt (or you may change directory here and in below program)

--If table already exists then execute the DROP command >
drop table TEST_FILES;


Before execution of below program TEST_FILES table will look like this >

After execution of below program TEST_FILES table will look like this >


Read : IMAGE - Storing in and retrieving out from database

Example/ Full Programs JDBC- Insert/Store/save file in database by using PreparedStatement's executeUpdate method, using CLOB data type - in java
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class StoreFileExample {
   public static void main(String... arg) {
          Connection con =null;
          PreparedStatement prepStmt = null;
          File file= null;
          FileReader fr = null;
          String filePath="c:/myTxt.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!");
                
                 //create File object
                 file=new File(filePath);
                 //create a FileReader object which will read from File
                 fr=new FileReader(file);
                
                 prepStmt=con.prepareStatement("INSERT into TEST_FILES (ID, FILE_COL) "
                              + "values (1, ?)");
                 prepStmt.setCharacterStream(1,fr);
                 //execute insert query
                 int numberOfRowsInserted = prepStmt.executeUpdate();
                 System.out.println("numberOfRowsInserted = " + numberOfRowsInserted);
                 System.out.println(filePath+" > File stored in database");
                
                
          } catch (Exception e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(fr!=null) fr.close(); //close reader
                       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!
numberOfRowsInserted = 1
c:/myTxt.txt > File stored in database
*/


In this tutorial we learned how to Insert/Store/save FILE in database by using PreparedStatement's executeUpdate and setCharacterStream methods, using CLOB data type in java jdbc.


RELATED LINKS>

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


Oracle 11g and SQL Developer installation and setup on 32/64bit windows - explained step by step with screenshots


JDBC tutorial program- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC tutorial program- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC tutorial program- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


JDBC tutorial program- Calling Oracle database FUNCTION - CallableStatement example in java


Must read for you :
Labels:

No comments:

Post a Comment