Spring - Handling large objects with JdbcTemplate and LobHandler

LobHandler is an abstraction for handling large binary and text fields. This abstraction was mainly created to isolate Oracle peculiar handling of LOBs ( OracleLobHandler implementation). DefaultLobHandler (another implementation) should work with other databases as well.

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 and LobHandler

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

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

    @Override
    public void save(Report report) {
        String sql = "insert into REPORT (NAME, CONTENT, IMAGE) values (?, ?, ?)";
        LobHandler lobHandler = new DefaultLobHandler();
        jdbcTemplate.execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
            @Override
            protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                    throws SQLException, DataAccessException {

                ps.setString(1, report.getName());
                Reader reader = new StringReader(report.getContent());
                lobCreator.setClobAsCharacterStream(ps, 2, reader,
                        report.getContent().length());

                ByteArrayInputStream inputStream = new ByteArrayInputStream(report.getImage());
                lobCreator.setBlobAsBinaryStream(ps, 3, inputStream, report.getImage().length);
            }
        });
    }
    .............
    @Override
    public List<Report> loadAll() {
        return jdbcTemplate.query("select * from REPORT", (resultSet, i) -> {
            return toReport(resultSet);
        });
    }

    private Report toReport(ResultSet resultSet) throws SQLException {
        Report report = new Report();
        report.setId(resultSet.getLong("ID"));
        report.setName(resultSet.getString("NAME"));

        LobHandler lobHandler = new DefaultLobHandler();
        String content = lobHandler.getClobAsString(resultSet, "CONTENT");
        report.setContent(content);

        byte[] image = lobHandler.getBlobAsBytes(resultSet, "IMAGE");
        report.setImage(image);

        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 --");
        System.out.println(report);
        reportDao.save(report);
        System.out.println("-- loading all --");
        List<Report> reports = reportDao.loadAll();
        System.out.println(reports);
    }

    private Report getReport() {
        //create dummy report
        Report report = new Report();
        report.setName("dummy report");
        report.setContent("very large dummy content ..");
        report.setImage(new byte[]{1, 4, 3, 3, 3, 5, 5, 3, 6, 7, 6, 7, 9});
        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 --
Report{id=0, name='dummy report', content='very large dummy content ..', image=[1, 4, 3, 3, 3, 5, 5, 3, 6, 7, 6, 7, 9]}
-- loading all --
[Report{id=1, name='dummy report', content='very large dummy content ..', image=[1, 4, 3, 3, 3, 5, 5, 3, 6, 7, 6, 7, 9]}]

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