将BLOB从文件插入到SQL脚本中以嵌入H2数据库 [英] Insert BLOB from a file into a sql script to embed H2 database

问题描述

我正在创建一个SQL脚本来创建一个新的架构，并将一些值插入到一个嵌入式H2数据库中，以用于Spring Boot应用程序中的集成测试.我需要插入的值之一是sql表上的BLOB字段.

I'm creating a SQL script to create a new schema and insert some values to an embed H2 database for use with integration tests in a Spring Boot application. One of the values I need to insert is a BLOB field on the sql table.

我已经按照此处的说明成功使用了FILE_READ函数. .

I've succesfully used the FILE_READ function as described here.

INSERT INTO MY_TABLE(ID, NAME, LOGO) 
VALUES('1', 'test1', FILE_READ('C:/myproject/logo.png'));

该功能在完整路径下效果很好，但是我无法在相对路径下做到这一点.当在除我的以外的任何其他机器上下载并编译源代码(加上测试)时，效果就不好.

That function works well with full paths but I'm not been able to do that with relative paths. That doesn't work well when the sources are downloaded and compiled (plus testing) in any other machine than mine.

我需要将二进制文件中的BLOB字段插入到SQL脚本中，该二进制文件是从拥有该脚本的项目的相对路径中加载的.

我已搜索并找到此方法:通过sql插入BLOB脚本? 但是RAWTOHEX函数似乎适用于Strings，而我的输入是一个二进制文件.

I've searched and found this aproach: insert a BLOB via a sql script? But RAWTOHEX function seems to work with Strings, and my input is a binary file.

有什么想法吗?

推荐答案

来自 FILE_READ 文档:

支持文件名和URL.要从 classpath，请使用前缀classpath:

File names and URLs are supported. To read a stream from the classpath, use the prefix classpath:

似乎无法使用相对路径；那么可能的解决方案是将具有所需二进制内容的文件包含在classpath中，并使用FILE_READ中的classpath:访问该文件.这样，您可以将其部署在任何其他计算机上，而不必担心绝对路径.

Seems that the use of a relative path it's not possible; then a possible solution is to include the file with the desired binary content in the classpath and access it using classpath: in FILE_READ. This way you can deploy it in any other machine without worries about the absolute paths.

通过使用RunScript

By code using RunScript

因此，如果在执行测试之前，通过使用类似以下代码的代码来设置运行脚本的数据库:

So if before perform your test you setup the DB running the script by code using something like:

RunScript.execute(conn, new FileReader("yourScript.sql"));

然后将logo.png添加为项目的资源，这样您就可以使用classpath:表示法FILE_READ('classpath:/your/package/resource/logo.png')在脚本中引用它.

Then add the logo.png as a resource of your project this way you can refer it inside the script using classpath: notation: FILE_READ('classpath:/your/package/resource/logo.png').

从命令行工具使用RunScript

Using RunScript from command line tool

如果使用命令行工具，则可以创建 .jar 来打包资源，例如resource.jar并将其添加到cmd中的classpath中:

If you use the command line tool, you can create a .jar to package your resources, e.g resource.jar and add it to classpath in your cmd:

java -cp h2*.jar;resource.jar org.h2.tools.RunScript -url jdbc:h2:~/test -script yourScript.sql

然后，作为脚本中的前一种情况，您可以使用FILE_READ('classpath:/your/package/resource/logo.png')

Then as the previous case in your script you can refer your binary file using FILE_READ('classpath:/your/package/resource/logo.png')

希望有帮助，

这篇关于将BLOB从文件插入到SQL脚本中以嵌入H2数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！