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 >
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>