How to insert blob data into the embedded HSQLDB of Spring through the sql script?

Question

This is my sql script creating the database:

create table product 
(id integer identity primary key,
name varchar(50) not null,
price decimal(10,2) not null,
image blob(1024),
category_id integer);

This is my sql script, which inserts data to the database:

INSERT INTO product VALUES (1, 'ProductName', 699.95, load_file('full_path'), 1);

According to HSQLDB, the full path is by default a relative path to database location. So what do i have to give as file path for my image files which are located under /resources/img/? (Spring creates the DB by using this URL:jdbc:hsqldb:mem:testdb)

Answer 1

You can derive absolute path of class path resource as follows,

new ClassPathResource("img/abc.gif").getFile().getAbsolutePath()

Answer 2

I ran into the same problem while configuring tests using HSQLDB. Seems like HSQLDB only supports load_file('full_path'), which takes in absolute file-paths only.

Alternatively to achieve the same result, use an Embedded DB Configuration in H2 DB.

The same query would be the below in a H2 Configuration.

INSERT INTO product VALUES (1, 'ProductName', 699.95, FILE_READ('classpath:filename.ext');

Answer 3

See the description of param textdb.allow_full_path.

It allows you to use different variants of file paths.