spring jdbctemplate 集成duckdb
以下是关于spring jdbctemplate 集成duckdb的说明
项目准备
- pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.dalong</groupId>
<artifactId>duckdb-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>duckdb-demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId>org.duckdb</groupId>
<artifactId>duckdb_jdbc</artifactId>
<version>0.2.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.4.0-M4</version>
<type>maven-plugin</type>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
<compilerArguments>
<extdirs>${project.basedir}/libs</extdirs>
</compilerArguments>
</configuration>
</plugin>
</plugins>
<resources>
<resource>
<directory>${basedir}/src/main/resources</directory>
<filtering>true</filtering>
<includes>
<include>**/application*.properties</include>
</includes>
</resource>
</resources>
</build>
</project>
- jdbctemplate bean && datasource bean
使用hikari进行数据库连接池的管理
package com.dalong.duckdbdemo;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
@SpringBootApplication
public class DuckdbDemoApplication {
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource){
JdbcTemplate duckdb =new JdbcTemplate();
duckdb.setDataSource(dataSource);
return duckdb;
}
@Bean
public DataSource dataSource() {
HikariConfig config = new HikariConfig();
config.setDriverClassName("org.duckdb.DuckDBDriver");
config.setMaximumPoolSize(10);
config.setMaxLifetime(3);
config.setJdbcUrl("jdbc:duckdb:");
HikariDataSource ds = new HikariDataSource(config);
return ds;
}
public static void main(String[] args) {
SpringApplication.run(DuckdbDemoApplication.class, args);
}
}
- rest api
init 进行数据表的创建，insert 插入测试数据，q 进行数据查询
package com.dalong.duckdbdemo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class APIController {
@Autowired
private JdbcTemplate jdbcTemplate;
// 创建表
@RequestMapping(value = {"/init"})
public Object demo(){
jdbcTemplate.execute("CREATE TABLE items (item VARCHAR, value DECIMAL(10,2), count INTEGER)");
return "ok";
}
// 插入数据
@RequestMapping(value = {"/insert"})
public Object insert(){
for (int i = 0; i < 10000 ; i++) {
jdbcTemplate.execute("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)");
}
return "ok";
}
// 查询
@RequestMapping(value = {"/q"})
public Object query(){
return jdbcTemplate.queryForList("select * from items");
}
}
问题说明
- 目前duckdb jdbc使用的glibc 版本太高了，需要libstdc++ 26的，需要使用高版本的操作系统或者自己编译提供jdbc 的驱动（这个官方做的
很不好，说是c++ 11 实际上编译版本的需要的gcc 更高）,可以自己构建一个高版本的然后替换(github 有编译好的，但是注意风险) - 同时使用spring-boot-starter-data-jdbc 2.3 有一个问题就是sql 方言，解决方法
参考如下的连接
参考资料
https://stackoverflow.com/questions/61999807/new-error-in-spring-boot-2-3-0-release-unsatisfieddependencyexception-for-orac
https://spring.io/blog/2020/05/20/migrating-to-spring-data-jdbc-2-0
https://duckdb.org/docs/data/overview
https://github.com/rongfengliang/libstdc-so
https://github.com/rongfengliang/duckdb-spring-jdbc
