Spring - Handling CLOB and BLOB with JdbcTemplate

In Spring we can persist and load large objects by using the JdbcTemplate. These large objects are called BLOBs (Binary Large Object) for binary data and CLOBs (Character Large Object) for character data.

Following example demonstrates how to persist large objects directly from runtime memory to database tables.

Example

src/main/resources/createReportTable.sql

CREATE TABLE REPORT(
ID BIGINT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL,
CONTENT CLOB,
IMAGE BLOB
);

public class Report {
  private long id;
  private String name;
  private String content;
  private byte[] image;
    .............
}

Using JdbcTemplate

@Repository
public class ReportDao implements Dao<Report> {
  @Autowired
  private DataSource dataSource;
  private JdbcTemplate jdbcTemplate;

  @PostConstruct
  private void postConstruct() {
      jdbcTemplate = new JdbcTemplate(dataSource);
  }

  @Override
  public long save(Report report) {
      String sql = "insert into REPORT (NAME, CONTENT, IMAGE) values (?, ?, ?)";
      KeyHolder holder = new GeneratedKeyHolder();
      jdbcTemplate.update(new PreparedStatementCreator() {
          @Override
          public PreparedStatement createPreparedStatement(Connection connection)
                  throws SQLException {
              PreparedStatement ps = connection.prepareStatement(sql.toString(),
                      Statement.RETURN_GENERATED_KEYS);
              ps.setString(1, report.getName());
              Reader reader = new StringReader(report.getContent());
              ps.setClob(2, reader);
              ByteArrayInputStream inputStream = new ByteArrayInputStream(report.getImage());
              ps.setBlob(3, inputStream);
              return ps;
          }
      }, holder);
      Number key = holder.getKey();
      if (key != null) {
          return key.longValue();
      }
      throw new RuntimeException("No generated primary key returned.");
  }

  @Override
  public Report load(long id) {
      List<Report> persons = jdbcTemplate.query("select * from Report where id =?",
              new Object[]{id}, (resultSet, i) -> {
                  return toReport(resultSet);
              });
      if (persons.size() == 1) {
          return persons.get(0);
      }
      throw new RuntimeException("No item found for id: " + id);
  }
    .............
  private Report toReport(ResultSet resultSet) throws SQLException {
      Report report = new Report();
      report.setId(resultSet.getLong("ID"));
      report.setName(resultSet.getString("NAME"));
      InputStream contentStream = resultSet.getClob("CONTENT")
                                           .getAsciiStream();
      String content =
              new Scanner(contentStream, "UTF-8").useDelimiter("\\A").next();
      report.setContent(content);
      Blob blob = resultSet.getBlob("IMAGE");
      byte[] bytes = blob.getBytes(1, (int) blob.length());
      report.setImage(bytes);
      return report;
  }
}

Persisting and loading

@Component
public class ReportClientBean {
  @Autowired
  private Dao<Report> reportDao;

  public void persistReport() {
      Report report = getReport();
      System.out.println("-- persisting report --");
      long generatedId = reportDao.save(report);
      System.out.println("saved report generated id: "+generatedId);

      System.out.println("-- loading report --");
      Report loadedReport = reportDao.load(generatedId);
      System.out.println(loadedReport);
  }

  private static void logException(Exception e) {
      System.out.println("-- exception message --");
      System.err.println(e.getMessage());
      System.out.println("---------");
  }

  private  Report getReport() {
      //create dummy report
      Report report = new Report();
      report.setName("test report");
      report.setContent("dummy large content ..");
      report.setImage(new byte[]{1, 4, 3, 32, 2, 1, 5, 3, 6, 7, 6, 7,});
      return report;
  }
}

Java Config

@Configuration
@ComponentScan
public class AppConfig {

  @Bean
  public DataSource h2DataSource() {
      return new EmbeddedDatabaseBuilder()
              .setType(EmbeddedDatabaseType.H2)
              .addScript("createReportTable.sql")
              .build();
  }
}

Main class

public class ExampleMain {
  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      ReportClientBean clientBean = context.getBean(ReportClientBean.class);
      clientBean.persistReport();
  }
}

Output

-- persisting report --
saved report generated id: 1
-- loading report --
Report{id=1, name='test report', content='dummy large content ..', image=[1, 4, 3, 32, 2, 1, 5, 3, 6, 7, 6, 7]}

Example Project

Dependencies and Technologies Used:

• spring-context 4.3.10.RELEASE: Spring Context.
• spring-jdbc 4.3.10.RELEASE: Spring JDBC.
• h2 1.4.196: H2 Database Engine.
• JDK 1.8
• Maven 3.3.9