or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

advanced-patterns.md core-operations.md database-init.md datasource.md embedded-database.md exception-translation.md index.md key-generation.md lob-support.md result-parameter-handling.md simplified-operations.md sqlrowset.md transactions.md

tile.json

LOB Support

Spring JDBC provides support for handling Large Objects (BLOBs and CLOBs) with streaming capabilities. This includes reading and writing binary and character data efficiently.

Core Interfaces

LobHandler

Interface for LOB handling abstraction.

public interface LobHandler {
    // Reading LOBs
    byte[] getBlobAsBytes(ResultSet rs, int columnIndex) throws SQLException;
    byte[] getBlobAsBytes(ResultSet rs, String columnName) throws SQLException;
    InputStream getBlobAsBinaryStream(ResultSet rs, int columnIndex) throws SQLException;
    InputStream getBlobAsBinaryStream(ResultSet rs, String columnName) throws SQLException;
    String getClobAsString(ResultSet rs, int columnIndex) throws SQLException;
    String getClobAsString(ResultSet rs, String columnName) throws SQLException;
    Reader getClobAsCharacterStream(ResultSet rs, int columnIndex) throws SQLException;
    Reader getClobAsCharacterStream(ResultSet rs, String columnName) throws SQLException;

    // Creating LOBs
    LobCreator getLobCreator();
}

LobCreator

Interface for creating LOB values.

public interface LobCreator {
    void setBlobAsBytes(PreparedStatement ps, int paramIndex, byte[] content) throws SQLException;
    void setBlobAsBinaryStream(PreparedStatement ps, int paramIndex, InputStream binaryStream, int contentLength) throws SQLException;
    void setClobAsString(PreparedStatement ps, int paramIndex, String content) throws SQLException;
    void setClobAsCharacterStream(PreparedStatement ps, int paramIndex, Reader characterStream, int contentLength) throws SQLException;
    void close();
}

Implementation

DefaultLobHandler

Default implementation for standard JDBC.

public class DefaultLobHandler implements LobHandler {
    public DefaultLobHandler() { }
    public void setWrapAsLob(boolean wrapAsLob) { }
    public void setStreamAsLob(boolean streamAsLob) { }
}

Usage Examples

Reading BLOBs

import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobHandler;

LobHandler lobHandler = new DefaultLobHandler();

// Read BLOB as bytes
byte[] imageData = jdbcTemplate.query(
    "SELECT image FROM products WHERE id = ?",
    rs -> {
        if (rs.next()) {
            return lobHandler.getBlobAsBytes(rs, "image");
        }
        return null;
    },
    productId
);

// Read BLOB as stream
InputStream stream = jdbcTemplate.query(
    "SELECT document FROM files WHERE id = ?",
    rs -> {
        if (rs.next()) {
            return lobHandler.getBlobAsBinaryStream(rs, "document");
        }
        return null;
    },
    fileId
);

// Process stream
try {
    Files.copy(stream, Paths.get("output.pdf"));
} finally {
    stream.close();
}

Reading CLOBs

// Read CLOB as String
String content = jdbcTemplate.query(
    "SELECT content FROM articles WHERE id = ?",
    rs -> {
        if (rs.next()) {
            return lobHandler.getClobAsString(rs, "content");
        }
        return null;
    },
    articleId
);

// Read CLOB as Reader
Reader reader = jdbcTemplate.query(
    "SELECT description FROM products WHERE id = ?",
    rs -> {
        if (rs.next()) {
            return lobHandler.getClobAsCharacterStream(rs, "description");
        }
        return null;
    },
    productId
);

// Process reader
try (BufferedReader br = new BufferedReader(reader)) {
    String line;
    while ((line = br.readLine()) != null) {
        System.out.println(line);
    }
}

Writing BLOBs

import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;

LobHandler lobHandler = new DefaultLobHandler();

// Write BLOB from byte array
byte[] imageBytes = Files.readAllBytes(Paths.get("image.jpg"));

jdbcTemplate.execute(
    "INSERT INTO products (name, image) VALUES (?, ?)",
    new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
        @Override
        protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                throws SQLException {
            ps.setString(1, "Product Name");
            lobCreator.setBlobAsBytes(ps, 2, imageBytes);
        }
    }
);

// Write BLOB from stream
try (InputStream inputStream = new FileInputStream("document.pdf")) {
    jdbcTemplate.execute(
        "INSERT INTO files (name, document) VALUES (?, ?)",
        new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
            @Override
            protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                    throws SQLException {
                ps.setString(1, "Document Name");
                lobCreator.setBlobAsBinaryStream(ps, 2, inputStream, (int) new File("document.pdf").length());
            }
        }
    );
}

Writing CLOBs

// Write CLOB from String
String content = "Long article content...";

jdbcTemplate.execute(
    "INSERT INTO articles (title, content) VALUES (?, ?)",
    new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
        @Override
        protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                throws SQLException {
            ps.setString(1, "Article Title");
            lobCreator.setClobAsString(ps, 2, content);
        }
    }
);

// Write CLOB from Reader
try (Reader reader = new FileReader("article.txt")) {
    jdbcTemplate.execute(
        "INSERT INTO articles (title, content) VALUES (?, ?)",
        new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
            @Override
            protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                    throws SQLException {
                ps.setString(1, "Article Title");
                lobCreator.setClobAsCharacterStream(ps, 2, reader, (int) new File("article.txt").length());
            }
        }
    );
}

Update LOB

// Update existing BLOB
byte[] newImage = Files.readAllBytes(Paths.get("new_image.jpg"));

jdbcTemplate.execute(
    "UPDATE products SET image = ? WHERE id = ?",
    new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
        @Override
        protected void setValues(PreparedStatement ps, LobCreator lobCreator)
                throws SQLException {
            lobCreator.setBlobAsBytes(ps, 1, newImage);
            ps.setLong(2, productId);
        }
    }
);

LOB RowMapper

public class ProductWithImageMapper implements RowMapper<Product> {
    private final LobHandler lobHandler;

    public ProductWithImageMapper(LobHandler lobHandler) {
        this.lobHandler = lobHandler;
    }

    @Override
    public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
        Product product = new Product();
        product.setId(rs.getLong("id"));
        product.setName(rs.getString("name"));
        product.setImage(lobHandler.getBlobAsBytes(rs, "image"));
        product.setDescription(lobHandler.getClobAsString(rs, "description"));
        return product;
    }
}

// Usage
List<Product> products = jdbcTemplate.query(
    "SELECT id, name, image, description FROM products",
    new ProductWithImageMapper(new DefaultLobHandler())
);

Streaming Large LOBs

// Stream BLOB to file
jdbcTemplate.query(
    "SELECT document FROM files WHERE id = ?",
    rs -> {
        if (rs.next()) {
            try (InputStream is = lobHandler.getBlobAsBinaryStream(rs, "document");
                 OutputStream os = new FileOutputStream("output.pdf")) {
                byte[] buffer = new byte[8192];
                int bytesRead;
                while ((bytesRead = is.read(buffer)) != -1) {
                    os.write(buffer, 0, bytesRead);
                }
            }
        }
        return null;
    },
    fileId
);

Best Practices

Use streams for large LOBs - Avoid loading into memory
Close streams properly - Use try-with-resources
Use DefaultLobHandler - Works with most databases
Specify content length - For stream operations
Handle NULL LOBs - Check for null before processing
Use appropriate buffer size - 8KB typical for streaming
Consider database-specific LOB handling - Some databases need special handling
Clean up LOB resources - Call lobCreator.close()