Spring JDBC - Handpng CLOB

Following example will demonstrate how to update a CLOB 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,
   DESCRIPTION LONGTEXT,
   PRIMARY KEY (ID)
);

Syntax

MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("id", id);
in.addValue("description",  new SqlLobValue(description, new DefaultLobHandler()), Types.CLOB);

String SQL = "update Student set description = :description 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 the 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 Ecppse IDE in place and use the following steps to create a Spring apppcation.

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

package com.tutorialspoint;

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

pubpc interface StudentDAO {
   /** 
      * This is the method to be used to initiapze
      * database resources ie. connection.
   */
   pubpc void setDataSource(DataSource ds);
   
   /** 
      * This is the method to be used to update
      * a record into the Student table.
   */
   pubpc void updateDescription(Integer id, String description);
}

Following is the content of the Student.java file.

package com.tutorialspoint;

pubpc class Student {
   private Integer age;
   private String name;
   private Integer id;
   private String description;

   pubpc void setAge(Integer age) {
      this.age = age;
   }
   pubpc Integer getAge() {
      return age;
   }
   pubpc void setName(String name) {
      this.name = name;
   }
   pubpc String getName() {
      return name;
   }
   pubpc void setId(Integer id) {
      this.id = id;
   }
   pubpc Integer getId() {
      return id;
   }
   pubpc String getDescription() {
      return description;
   }
   pubpc void setDescription(String description) {
      this.description = description;
   }
}

Following is the content of the StudentMapper.java file.

package com.tutorialspoint;

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

pubpc class StudentMapper implements RowMapper<Student> {
   pubpc 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.setDescription(rs.getString("description"));
      return student;
   }
}

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

package com.tutorialspoint;

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;

pubpc class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   pubpc void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
   }
   pubpc void updateDescription(Integer id, String description) {
      MapSqlParameterSource in = new MapSqlParameterSource();
      in.addValue("id", id);
      in.addValue("description",  new SqlLobValue(description, new DefaultLobHandler()), Types.CLOB);

      String SQL = "update Student set description = :description 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.tutorialspoint;

import java.util.List;
import org.springframework.context.ApppcationContext;
import org.springframework.context.support.ClassPathXmlApppcationContext;
import com.tutorialspoint.StudentJDBCTemplate;

pubpc class MainApp {
   pubpc static void main(String[] args) {
      ApppcationContext context = new ClassPathXmlApppcationContext("Beans.xml");
      StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
      
      studentJDBCTemplate.updateDescription(1, "This can be a very long text upto 4 GB of size."); 
   }
}

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 ">

   <!-- Initiapzation for data source -->
   <bean id = "dataSource" 
      class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name = "driverClassName" value = "com.mysql.cj.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.tutorialspoint.StudentJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>
</beans>

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

Updated Record with ID = 1

You can check the description stored by querying the database.