Spring Boot 1.5.10+H2 1.4.196：Classpath/URI插入Blob失败求助

Hey there, let's break down why your BLOB inserts using classpath paths or URIs are failing while absolute paths work in your Spring Boot 1.5.10 (H2 1.4.196) setup. Here's what's going on and how to fix it:

Key Reasons for the Failure #

• H2 doesn't natively recognize Spring's classpath: prefix: When you run INSERT INTO CBM_DATA(DATA) VALUES ('classpath:/samplepdfs/test1.pdf'), H2 just stores the string literal 'classpath:/samplepdfs/test1.pdf' as the BLOB value, not the actual file content. It has no idea how to resolve Spring's classpath resources on its own.
• Incorrect URI syntax in your INSERT statement: Using <> around the HTTPS URL isn't valid H2 syntax for reading remote files. H2 requires specific functions to load content from external sources.
• Classpath resources in JARs are not accessible via H2's file functions: If your app is packaged as a JAR, the test1.pdf inside src/main/resources isn't a standalone file on the filesystem—H2's built-in file-reading functions can't directly access resources embedded in a JAR.

Fixes Tailored to Your Setup #

1. Use Spring's ResourceLoader to Read Classpath Resources and Insert as Byte Arrays #

This is the most reliable way to handle classpath resources in Spring Boot. Spring can easily load embedded resources, convert them to byte arrays, and then you can insert those bytes into the BLOB column.

Here's a code example using JdbcTemplate:

import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.FileCopyUtils;
import org.springframework.stereotype.Component;

import java.io.IOException;

@Component
public class BlobInsertService {

    private final ResourceLoader resourceLoader;
    private final JdbcTemplate jdbcTemplate;

    // Constructor injection (preferred over @Autowired in Spring 4.3+)
    public BlobInsertService(ResourceLoader resourceLoader, JdbcTemplate jdbcTemplate) {
        this.resourceLoader = resourceLoader;
        this.jdbcTemplate = jdbcTemplate;
    }

    public void insertPdfFromClasspath() throws IOException {
        // Load the classpath resource
        Resource pdfResource = resourceLoader.getResource("classpath:/samplepdfs/test1.pdf");
        
        // Convert resource to byte array
        byte[] pdfBytes = FileCopyUtils.copyToByteArray(pdfResource.getInputStream());
        
        // Insert into H2 database
        jdbcTemplate.update("INSERT INTO CBM_DATA(DATA) VALUES (?)", pdfBytes);
    }
}

2. Use H2's READ_FILE() Function for Remote HTTPS Files #

If you want to load the file directly from your internal HTTPS server via SQL, you need to use H2's READ_FILE() function with the full URL. You also need to ensure H2 is configured to allow URL access.

Step 1: Update your H2 connection URL

Add the ALLOW_URL_ACCESS=TRUE parameter to your H2 connection URL in application.properties to permit H2 to fetch content from remote URLs:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;ALLOW_URL_ACCESS=TRUE

Step 2: Use the correct SQL syntax

Replace your invalid <> syntax with READ_FILE():

INSERT INTO CBM_DATA(DATA) VALUES (READ_FILE('https://your-internal-server/path/to/test1.pdf'));

3. Why Absolute Paths Work #

When you use an absolute file path with READ_FILE() (e.g., READ_FILE('/home/user/test1.pdf')), H2 can directly access the file on the local filesystem, which is why that approach works. This doesn't apply to classpath resources in JARs since they aren't physical files.

内容的提问来源于stack exchange，提问作者Harsh Mehta