Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is JDBC Clob data type? how to store and read data from it?
CLOB stands for Character Large Object in general, an SQL Clob is a built-in datatype and is used to store large amount of textual data. Using this datatype, you can store data up to 2,147,483,647 characters.
The java.sql.Clob interface of the JDBC API represents the CLOB datatype. Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL CLOB (not the data).
MYSQL database provides support for this datatype using four variables.
TINYTEXT: A CLOB type with a maximum of 28-1 (255) characters.
TEXT: A CLOB type with a maximum of 216-1 (65535) characters.
MEDIUMTEXT: A CLOB type with a maximum of 224-1 (16777215) characters.
LONGTEXT: A CLOB type with a maximum of 232-1 (4294967295 ) characters.
Storing Clob datatype in to table in a database
To store Clob datatype to database, using JDBC program follow the steps given below
Step 1: Connect to the database
You can connect to a database using the getConnection() method of the DriverManager class.
Connect to the MySQL database by passing the MySQL URL which is jdbc:mysql://localhost/sampleDB (where sampleDB is the database name), username and password as parameters to the getConnection() method.
String mysqlUrl = "jdbc:mysql://localhost/sampleDB"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
Step 2: Create a Prepared statement
Create a PreparedStatement object using the prepareStatement() method of the Connection interface. To this method pass the insert query (with place holders) as a parameter.
PreparedStatement pstmt = con.prepareStatement("INSERT INTO Technologies(Name,
Type, Article ) VALUES (?, ?, ?)");Step 3: Set values to the place holders
Set the values to the place holders using the setter methods of the PreparedStatement interface. Chose the methods according to the datatype of the column. For Example if the column is of VARCHAR type use setString() method and if it is of INT type you can use setInt() method.
And if it is of Clob type you can set value to it using the setCharacterStream() or setClob() methods. To these methods pass an integer variable representing the parameter index and an object of the Reader class as parameters.
pstmt.setString(1, "JavaFX");
pstmt.setString(2, "Java Library");
FileReader reader = new FileReader("E:\images\javafx.txt");
pstmt.setClob(3, reader);
pstmt.execute();Step 4: Execute the statement
Execute the above created PreparedStatement object using the execute() method of the PreparedStatement interface.
Retrieving blob from database
The getClob() method of the ResultSet interface accepts 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 and returns it in the form of a Clob object.
while(rs.next()) {
System.out.println(rs.getString("Name"));
System.out.println(rs.getString("Type"));
Clob clob = rs.getClob("Article");
}The getCharacterStream() method of the Clob Interface retrieves the contents of the current Clob object and returns as a Reader object.
Using the getClob() method you can get the contents of the Clob as a Reader object and create text file with the retrieved contents, using the write() method of the FileOutputStream object.
Reader r = clob.getCharacterStream();
char cbuf[] = new char[r.read()];
r.read(cbuf);
FileOutputStream outPutStream = new
FileOutputStream("E:\images\clob_output"+i+".txt");
outPutStream.write(cbuf.toString().getBytes());Example
Following example creates a table in MySQL database with a Clob datatype in it. Inserts large text data from a file to it. Retrieves the text back and stores it in another text file.
import java.io.FileReader;
import java.io.FileWriter;
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.Statement;
public class ClobExample {
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 a table
Statement stmt = con.createStatement();
stmt.execute("CREATE TABLE Technologies( Name VARCHAR(255), Type VARCHAR(255), Article LONGTEXT)");
System.out.println("Table Created......");
//Inserting values
String query = "INSERT INTO Technologies(Name, Type, Article ) VALUES (?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1, "JavaFX");
pstmt.setString(2, "Java Library");
FileReader reader = new FileReader("E:\images\javafx.txt");
pstmt.setClob(3, reader);
pstmt.execute();
pstmt.setString(1, "CoffeeScript");
pstmt.setString(2, "Scripting Language");
reader = new FileReader("E:\images\coffeescript.txt");
pstmt.setClob(3, reader);
pstmt.execute();
pstmt.setString(1, "Cassandra");
pstmt.setString(2, "NoSQL Database");
reader = new FileReader("E:\images\cassandra.txt");
pstmt.setClob(3, reader);
pstmt.execute();
//Retrieving the data
ResultSet rs = stmt.executeQuery("select * from Technologies");
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 r = clob.getCharacterStream();
String filePath = "E:\Data\clob_output"+j+".txt";
FileWriter writer = new FileWriter(filePath);
int i;
while ((i=r.read())!=-1) {
writer.write(i);
}
writer.close();
System.out.println(filePath);
j++;
}
}
}Output
Connection established...... Table Created...... Contents of the table are: JavaFX Java Library E:\images\clob_output1.txt CoffeeScript Scripting Language E:\images\clob_output2.txt Cassandra NoSQL Database E:\images\clob_output3.txt
9K+ Views
