Insert Image using Spring JdbcTemplate

http://www.technicalkeeda.com/spring-tutorials/insert-image-using-spring-jdbctemplate

Insert Image using Spring JdbcTemplate

Posted On 2013-02-02 | Yashwant Chavan

2 0

To save image file into database we need to define BLOB datatype (Binary Large Object) column in the table. It store the data in the form of binary format. Basically we are using Mysql data to store the image file.You can refer my previous article How to Write / Insert Image Into Mysql Database Using Java

Useful to whom

This tutorial is useful for beginners and experience developers. It will helps you to learn step by step with the help of attached code.

Tools and Technologies

We use below technologies to execute below program

Maven 3.0.4
JDK 1.6
Spring 3.0.5.RELEASE
Mysql 5.1

BLOB Cloumn Table "trn_imgs"

Column img_data is BLOB type. Which hold the image into binary format.

CREATE TABLE  `technicalkeeda`.`trn_imgs` (
  `img_id` int(10) unsigned NOT NULL auto_increment,
  `img_title` varchar(45) collate latin1_general_ci NOT NULL,
  `img_data` blob NOT NULL,
  PRIMARY KEY  (`img_id`)
);

Maven Dependancy (pom.xml)

Define Spring and Mysql dependencies in pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>SpringExamples</groupId>
 <artifactId>SpringExamples</artifactId>
 <packaging>war</packaging>
 <version>1.0</version>
 <name>SpringExamples</name>
 <description></description>
 <build>
  <finalName>SpringExamples</finalName>
  <plugins>
   <plugin>
    <artifactId>maven-compiler-plugin</artifactId>
    <configuration>
     <source>1.6</source>
     <target>1.6</target>
    </configuration>
   </plugin>
  </plugins>
 </build>
 
 <properties>
  <spring.version>3.0.5.RELEASE</spring.version>
 </properties>
 
 <dependencies>
  <dependency>
   <groupId>junit</groupId>
   <artifactId>junit</artifactId>
   <version>3.8.1</version>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.9</version>
  </dependency>


  <dependency>
   <groupId>dom4j</groupId>
   <artifactId>dom4j</artifactId>
   <version>1.6.1</version>
  </dependency>

  <dependency>
   <groupId>commons-logging</groupId>
   <artifactId>commons-logging</artifactId>
   <version>1.1.1</version>
  </dependency>

  <dependency>
   <groupId>commons-collections</groupId>
   <artifactId>commons-collections</artifactId>
   <version>3.2.1</version>
  </dependency>

  <dependency>
   <groupId>cglib</groupId>
   <artifactId>cglib</artifactId>
   <version>2.2</version>
  </dependency>

  <dependency>
   <groupId>asm</groupId>
   <artifactId>asm</artifactId>
   <version>3.1</version>
  </dependency>

  <dependency>
   <groupId>javax.transaction</groupId>
   <artifactId>jta</artifactId>
   <version>1.1</version>
  </dependency>

  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring</artifactId>
   <version>2.5.6</version>
                </dependency>
 </dependencies>
</project>

Spring Bean Configuration (spring-beans.xml)

This is maven base project so create spring-beans.xml is file under "src\main\resources\spring-beans.xml" folder. Define spring datsource connection properties and imageDao bean.

<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:context="http://www.springframework.org/schema/context"
 xmlns:mvc="http://www.springframework.org/schema/mvc" 
 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
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">
 
 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
  <property name="url"><value>jdbc:mysql://localhost:3306/technicalkeeda</value></property>
  <property name="username"><value>root</value></property>
  <property name="password"><value></value></property>
 </bean>
 <bean id="imageDao" class="com.technicalkeeda.dao.ImageDaoImpl"> 
  <property name="dataSource" ref="dataSource" />
 </bean>
</beans>

Spring Jdbc Dao Class (ImageDao.java)

Define ImageDao interface with insertImage() method.

package com.technicalkeeda.dao;

public interface ImageDao {
 public void insertImage();
}

Implementation Class (ImageDaoImpl.java)

Define ImageDaoImpl class and implement the insertImage() method. LobHandler is the default handler.

package com.technicalkeeda.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobHandler;

public class ImageDaoImpl implements ImageDao {

 private DataSource dataSource;

 private JdbcTemplate jdbcTemplate;

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
  this.jdbcTemplate = new JdbcTemplate(this.dataSource);
 }

 @Override
 public void insertImage() {

  try {
   final File image = new File("C:\\puppy.jpg");
   final InputStream imageIs = new FileInputStream(image);   
   LobHandler lobHandler = new DefaultLobHandler(); 
   jdbcTemplate.update(
         "INSERT INTO trn_imgs (img_title, img_data) VALUES (?, ?)",
         new Object[] {
           "Puppy",
           new SqlLobValue(imageIs, (int)image.length(), lobHandler),
         },
         new int[] {Types.VARCHAR, Types.BLOB});
   
   
  } catch (DataAccessException e) {
   System.out.println("DataAccessException " + e.getMessage());
  } catch (FileNotFoundException e) {
   System.out.println("DataAccessException " + e.getMessage());
  }

 }
}

Test Class (SpringImageInsertTestExample.java)

This is the test program, inserts the Image Blob object into table.

package com.technicalkeeda.dao.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.technicalkeeda.dao.ImageDao;

public class SpringImageInsertTestExample {

 public static void main(String[] args) {

  ApplicationContext context = new ClassPathXmlApplicationContext("spring-beans.xml");
  ImageDao imageDao = (ImageDao) context.getBean("imageDao");
  imageDao.insertImage();

 }
}

Some times you will get the below error , if your image size large than defined BLOB column type.

DataAccessException PreparedStatementCallback; SQL [INSERT INTO trn_imgs (img_title, img_data) VALUES (?, ?)]; 
Data truncation: Data too long for column 'img_data' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: 
Data truncation: Data too long for column 'img_data' at row 1

To overcome this issue, define your column as per your need.

TINYBLOB   :     maximum length of 255 bytes  
BLOB       :     maximum length of 65,535 bytes  
MEDIUMBLOB :     maximum length of 16,777,215 bytes  
LONGBLOB   :     maximum length of 4,294,967,295 bytes