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
How to write data into BLOB and CLOB type columns in a table using JDBC?
CLOB stands for Character Large Object. in general, an SQL Clob is a built-in datatype which is used to store large amount of textual data. Using this datatype, you can store data up to 2,147,483,647 characters. MYSQL database provides support Clob datatype TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.
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).
Inserting Data into a column of Clob type
You can insert a CLOB type value using the setCharacterStream() or, setClob() methods of the PreparedStatement interface. These methods accepts two parameters −
- An integer holding the index of the bind variable. (representing the column that stores data of type CLOB)
- A reader object holding the file containing the data, you need to store into the table.
FileReader fileReader = new FileReader("javafx_contents.txt");
pstmt.setClob(1, fileReader);
or,
pstmt.setCharacterStream(1, fileReader);A BLOB is a binary large object that can hold a variable amount of data with a maximum length of 65535 characters.
These are used to store large amounts of binary data, such as images or other types of files. MySQL supports BLOB datatype using TINYBLOB, BLOB, MEDIUMBLOB and, LONGBLOB.
The java.sql.Blob interface of the JDBC API represents the BLOB datatype. Since the Blob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL BLOB (not the data).
Inserting Data into a column of Blob datatype
You can insert BLOB data into a table using the setBinaryStream() or, setBlob() methods of the PreparedStatement interface. These methods accepts two parameters −
- An integer holding the index of the bind variable. (representing the column that stores data of type BLOB)
- A InputStream object holding the file containing the binary data (image), you need to store into the table.
InputStream inputStream = new FileInputStream("javafx_logo.jpg");
pstmt.setBlob(1, inputStream);
or,
pstmt.setBinaryStream(1, inputStream);Following JDBC program establishes connection with the MySQL and inserts BLOB and CLOB values into the table articles_data.
Example
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class InsertingDataToBlobandClob {
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/sampledatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Inserting values
String query = "INSERT INTO articles_data(Name, Article, Logo) VALUES (?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1, "JavaFX");
FileReader fileReader = new FileReader("E:\images\javafx_contents.txt");
pstmt.setClob(2, fileReader);
InputStream inputStream = new FileInputStream("E:\images\javafx_logo.jpg");
pstmt.setBlob(3, inputStream);
pstmt.execute();
pstmt.setString(1, "CoffeeScript");
fileReader = new FileReader("E:\images\coffeescript_contents.txt");
pstmt.setClob(2, fileReader);
inputStream = new FileInputStream("E:\images\coffeescript_logo.jpg");
pstmt.setBlob(3, inputStream);
pstmt.execute();
pstmt.setString(1, "JavaFX");
fileReader = new FileReader("E:\images\cassandra_contents.txt");
pstmt.setClob(2, fileReader);
inputStream = new FileInputStream("E:\images\cassandra_logo.jpg");
pstmt.setBlob(3, inputStream);
pstmt.execute();
System.out.println("Records inserted......");
}
}Output
Records inserted......
Verification
If you verify the contents of the articles_data table using MySQL workbench you can observe the inserted image and text values as −
2K+ Views
