Spring JDBC - Handling BLOB

Following example will demonstrate how to update a BLOB using an Update Query with the help of Spring JDBC. We'll update the available records in Student Table.

Student Table

CREATE TABLE Student(
   ID INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   IMAGE BLOB,
   PRIMARY KEY (ID)
);

Syntax

MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("id", id);
in.addValue("image",  new SqlLobValue(new ByteArrayInputStream(imageData), 
   imageData.length, new DefaultLobHandler()), Types.BLOB);

String SQL = "update Student set image = :image where id = :id";
NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);
jdbcTemplateObject.update(SQL, in);

Where,

• in − SqlParameterSource object to pass a parameter to update a query.

• SqlLobValue − Object to represent an SQL BLOB/CLOB value parameter.

• jdbcTemplateObject − NamedParameterJdbcTemplate object to update student object in database.

To understand the above-mentioned concepts related to Spring JDBC, let us write an example which will update a query. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application.

Following is the content of the Data Access Object interface file StudentDAO.java.

package com.howcodex;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
   /** 
      * This is the method to be used to initialize
      * database resources ie. connection.
   */
   public void setDataSource(DataSource ds);
   
   /** 
      * This is the method to be used to update
      * a record into the Student table.
   */
   public void updateImage(Integer id, byte[] imageData);
}

Following is the content of the Student.java file.

package com.howcodex;

public class Student {
   private Integer age;
   private String name;
   private Integer id;
   private byte[] image;

   public void setAge(Integer age) {
      this.age = age;
   }
   public Integer getAge() {
      return age;
   }
   public void setName(String name) {
      this.name = name;
   }
   public String getName() {
      return name;
   }
   public void setId(Integer id) {
      this.id = id;
   }
   public Integer getId() {
      return id;
   }
   public byte[] getImage() {
      return image;
   }
   public void setImage(byte[] image) {
      this.image = image;
   }
}

Following is the content of the Student.java file.

package com.howcodex;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class StudentMapper implements RowMapper<Student> {
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setId(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      student.setImage(rs.getBytes("image"));
      return student;
   }
}

Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.

package com.howcodex;

import java.util.List;
import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.DefaultLobHandler;

import java.io.ByteArrayInputStream;
import java.sql.Types;

public class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
   }
   public void updateImage(Integer id, byte[] imageData) {
      MapSqlParameterSource in = new MapSqlParameterSource();
      in.addValue("id", id);
      in.addValue("image",  new SqlLobValue(new ByteArrayInputStream(imageData), 
         imageData.length, new DefaultLobHandler()), Types.BLOB);

      String SQL = "update Student set image = :image where id = :id";
      NamedParameterJdbcTemplate jdbcTemplateObject = new 
         NamedParameterJdbcTemplate(dataSource);
      
      jdbcTemplateObject.update(SQL, in);
      System.out.println("Updated Record with ID = " + id );
   }
}

Following is the content of the MainApp.java file.

package com.howcodex;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.howcodex.StudentJDBCTemplate;

public class MainApp {
   public static void main(String[] args) {
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
      StudentJDBCTemplate studentJDBCTemplate = 
         (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
      
      byte[] imageData = {0,1,0,8,20,40,95};
      studentJDBCTemplate.updateImage(1, imageData);  
   }
}

Following is the configuration file Beans.xml.

<?xml version = "1.0" encoding = "UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" 
   xsi:schemaLocation = "http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

   <!-- Initialization for data source -->
   <bean id = "dataSource" 
      class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name = "driverClassName" value = "com.mysql.jdbc.Driver"/>
      <property name = "url" value = "jdbc:mysql://localhost:3306/TEST"/>
      <property name = "username" value = "root"/>
      <property name = "password" value = "admin"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id = "studentJDBCTemplate" 
      class = "com.howcodex.StudentJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>
      
</beans>

Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message.

Updated Record with ID = 1

You can check the byte[] stored by querying the database.