Blob and Clob processing

Blob and Clob processing

BLOB: It is a data type available at Databases to represent large volumes of binary data.

CLOB: It 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 provided the following three interfaces mainly.

1. AbstractLobCreatingPreparedStatementCallback

It will be used to store Blob and Clob related data in Database.

protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException

2. AbstractLobStreamingResultSetExtractor

It will be used to retrive BLOB and CLOB data from database.

streamData(ResultSet rs)throws SQLException, IOException, DataAccessException

3. 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() 
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.properties

jdbc.driverClassName = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/employee
jdbc.username = root
jdbc.password = ashok
package 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());
   }
}
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.docx

Blob and Clob processing
Scroll to top