Blob and Clob processing
In this tutorial, we are going to discuss Blob and Clob processing in Spring DAO. Blob is a data type available at Databases to represent large volumes of binary data, and Clob is a data type available at Database to represent large volumes of character data.
In Spring JDBC Applications, to process BLOB and CLOB Data, Spring JDBC has mainly provided the following three interfaces.
1. AbstractLobCreatingPreparedStatementCallback
It will be used to store Blob and Clob related data in Database.
protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException2. AbstractLobStreamingResultSetExtractor
It will be used to retrive BLOB and CLOB data from database.
streamData(ResultSet rs)throws SQLException, IOException, DataAccessException3. LobCreator
It contains the following methods to prepare Binary stream and character streams to send blob and clob data to database.
setBlobAsBinaryStream()
setClobAsCharacterStream()4. LobHolder
It contains the following methods to get Binary stream and character stream to get blob and clob data.
getBlobAsBinaryStream()
getClobAsCharacterStream() E.g.
getBlobAsBinaryStream()
getClobAsCharacterStream() package com.ashok.spring.dao.lob.beans;
import java.io.File;
/**
*
* @author ashok.mariyala
*
*/
public class Employee {
private String empId;
private String empName;
private File empImage;
private File empResume;
public String getEmpId() {
return empId;
}
public void setEmpId(String empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public File getEmpImage() {
return empImage;
}
public void setEmpImage(File empImage) {
this.empImage = empImage;
}
public File getEmpResume() {
return empResume;
}
public void setEmpResume(File empResume) {
this.empResume = empResume;
}
}package com.ashok.spring.dao.lob;
import com.ashok.spring.dao.lob.beans.Employee;
/**
*
* @author ashok.mariyala
*
*/
public interface EmployeeDao {
public void insertEmployee(Employee emp);
public Employee readEmployee();
}package com.ashok.spring.dao.lob;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.util.FileCopyUtils;
import com.ashok.spring.dao.lob.beans.Employee;
/**
*
* @author ashok.mariyala
*
*/
public class EmployeeDaoImpl implements EmployeeDao {
private LobHandler lobHolder;
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
public void setLobHolder(LobHandler lobHolder) {
this.lobHolder = lobHolder;
}
public LobHandler getLobHolder() {
return lobHolder;
}
@Override
public void insertEmployee(Employee emp) {
String sql_Query = "insert into emp values(?,?,?,?)";
jdbcTemplate.execute(sql_Query, new AbstractLobCreatingPreparedStatementCallback(lobHolder) {
@Override
protected void setValues(PreparedStatement ps, LobCreator lobCreator)
throws SQLException, DataAccessException {
FileInputStream fis = null;
FileReader fr = null;
try {
ps.setString(1, emp.getEmpId());
ps.setString(2, emp.getEmpName());
fis = new FileInputStream(emp.getEmpImage());
fr = new FileReader(emp.getEmpResume());
lobCreator.setBlobAsBinaryStream(ps, 3, fis, (int) emp.getEmpImage().length());
lobCreator.setClobAsCharacterStream(ps, 4, fr, (int) emp.getEmpResume().length());
} catch (IOException e) {
e.printStackTrace();
}
}
});
}
@Override
public Employee readEmployee() {
Employee emp = new Employee();
String sql = "select * from emp";
jdbcTemplate.query(sql, new AbstractLobStreamingResultSetExtractor() {
@Override
protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
emp.setEmpId(rs.getString(1));
emp.setEmpName(rs.getString(2));
File file1 = new File("E:/ashok/personal/ashokimage.jpg");
FileOutputStream fos = new FileOutputStream(file1);
FileCopyUtils.copy(lobHolder.getBlobAsBinaryStream(rs, 3), fos);
emp.setEmpImage(file1);
File file2 = new File("E:/ashok/personal/ashokresume.docx");
FileWriter fw = new FileWriter(file2);
FileCopyUtils.copy(lobHolder.getClobAsCharacterStream(rs, 4), fw);
emp.setEmpResume(file2);
}
});
return emp;
}
}<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<bean id="empDao" class="com.ashok.spring.dao.lob.EmployeeDaoImpl">
<property name="dataSource" ref="dataSource" />
<property name="lobHolder" ref="lobHolder" />
</bean>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="lobHolder" class="org.springframework.jdbc.support.lob.DefaultLobHandler">
</bean>
<context:property-placeholder location="com/ashok/spring/dao/batchprocessing/config/jdbc.properties" />
</beans>jdbc.driverClassName = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/employee
jdbc.username = root
jdbc.password = ashokpackage com.ashok.spring.dao.lob;
import java.io.File;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.ashok.spring.dao.lob.beans.Employee;
/**
*
* @author ashok.mariyala
*
*/
public class TestSpringLobApplication {
public static void main(String[] args) throws Exception {
String configFile = "/com/ashok/spring/dao/lob/conf/applicationContext.xml";
ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
EmployeeDao dao = (EmployeeDao) context.getBean("empDao");
File file1 = new File("E:/ashok/personal/myimage.jpg");
File file2 = new File("E:/ashok/personal/myresume.docx");
Employee emp1 = new Employee();
emp1.setEmpId("E0087");
emp1.setEmpName("Ashok Kumar");
emp1.setEmpImage(file1);
emp1.setEmpResume(file2);
dao.insertEmployee(emp1);
System.out.println("Employee Inserted Successfully");
Employee emp2 = dao.readEmployee();
System.out.println("Employee Retrived Successfully");
System.out.println("Employee Details");
System.out.println("---------------------");
System.out.println("Employee Number :" + emp2.getEmpId());
System.out.println("Employee Name :" + emp2.getEmpName());
System.out.println("Employee Image :" + emp2.getEmpImage().getAbsolutePath());
System.out.println("Employee Resume :" + emp2.getEmpResume().getAbsolutePath());
}
}Output
Employee Inserted Successfully
Employee Retrieved Successfully
Employee Details
---------------------
Employee Number :E0087
Employee Name :Ashok Kumar
Employee Image :E:\ashok\personal\ashokimage.jpg
Employee Resume :E:\ashok\personal\ashokresume.docxBlob and Clob processing