SQLite数据库保存数据报错

[filekeeping]

当前使用版本(必填,否则不予处理)

3.5.3.1，3.5.3.2均必现，猜测其他早期版本也可能是这样，已经验证并非是sqlite-jdbc的问题

该问题是如何引起的？(确定最新版也有问题再提!!!)

调用mapper的insert方法或者service的saveBatch方法保存数据时均报错，但是可以插入一条数据（saveBatch方法传数组能插入第一条数据后面的插入失败）

重现步骤(如果有就写完整)

pom.xml

        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.43.2.0</version>
        </dependency>

        <!-- MyBatis Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.2</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.5.3.2</version>
        </dependency>

@TableName(value = "person")
public class Person extends Model<Person> {

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @TableField("name")
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

@Mapper
public interface PersonMapper extends BaseMapper<Person> {
}

@Component
public class DataInitRunner implements ApplicationRunner {

    @Autowired
    private PersonMapper mapper;

    @Override
    public void run(ApplicationArguments args) {
        Person person = new Person();
        person.setId((new Random()).nextInt());
        person.setName("test");
        mapper.insert(person);
    }
}

@MapperScan(basePackages = "com.example.demo.mapper")
@SpringBootApplication
public class Demo1Application {

    public static void main(String[] args) {
        SpringApplication.run(Demo1Application.class, args);
    }
}

配置文件：

spring.datasource.driver-class-name=org.sqlite.JDBC
spring.datasource.url=jdbc:sqlite::resource:db/database.db
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

报错信息

java.lang.IllegalStateException: Failed to execute ApplicationRunner
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:768) ~[spring-boot-3.1.4.jar:3.1.4]
	at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:755) ~[spring-boot-3.1.4.jar:3.1.4]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) ~[spring-boot-3.1.4.jar:3.1.4]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1309) ~[spring-boot-3.1.4.jar:3.1.4]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1298) ~[spring-boot-3.1.4.jar:3.1.4]
	at com.example.demo.Demo1Application.main(Demo1Application.java:12) ~[classes/:na]
Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Error getting generated key or setting result to parameter object. Cause: java.sql.SQLFeatureNotSupportedException: not implemented by SQLite JDBC driver
; not implemented by SQLite JDBC driver
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:106) ~[spring-jdbc-6.0.12.jar:6.0.12]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-6.0.12.jar:6.0.12]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-6.0.12.jar:6.0.12]
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91) ~[mybatis-spring-2.0.7.jar:2.0.7]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) ~[mybatis-spring-2.0.7.jar:2.0.7]
	at jdk.proxy2/jdk.proxy2.$Proxy46.insert(Unknown Source) ~[na:na]
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272) ~[mybatis-spring-2.0.7.jar:2.0.7]
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:59) ~[mybatis-plus-core-3.5.3.1.jar:3.5.3.1]
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) ~[mybatis-plus-core-3.5.3.1.jar:3.5.3.1]
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.5.3.1.jar:3.5.3.1]
	at jdk.proxy2/jdk.proxy2.$Proxy51.insert(Unknown Source) ~[na:na]
	at com.example.demo.DataInitRunner.run(DataInitRunner.java:22) ~[classes/:na]
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:765) ~[spring-boot-3.1.4.jar:3.1.4]
	... 5 common frames omitted
Caused by: java.sql.SQLFeatureNotSupportedException: not implemented by SQLite JDBC driver
	at org.sqlite.jdbc3.JDBC3PreparedStatement.unsupported(JDBC3PreparedStatement.java:448) ~[sqlite-jdbc-3.43.2.0.jar:na]
	at org.sqlite.jdbc3.JDBC3Statement.getGeneratedKeys(JDBC3Statement.java:361) ~[sqlite-jdbc-3.43.2.0.jar:na]
	at com.zaxxer.hikari.pool.ProxyStatement.getGeneratedKeys(ProxyStatement.java:229) ~[HikariCP-5.0.1.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.getGeneratedKeys(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:78) ~[mybatis-3.5.10.jar:3.5.10]
	at jdk.proxy3/jdk.proxy3.$Proxy56.getGeneratedKeys(Unknown Source) ~[na:na]
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:79) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:71) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:51) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194) ~[mybatis-3.5.10.jar:3.5.10]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:181) ~[mybatis-3.5.10.jar:3.5.10]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.7.jar:2.0.7]
	... 13 common frames omitted

[nieqiurong]

这看着是驱动不支持呀

[filekeeping]

@nieqiurong 所以这是 mybatis-plus 框架的问题还是 sqlite-jdbc 的问题啊

[nieqiurong]

不是已经给出来SQLFeatureNotSupportedException了么,驱动不实现这个也没办法,只能规避.

[filekeeping]

@nieqiurong 我觉得不是这样的，sqlite-jdbc没有实现insert方法我觉得离谱，另外我不通过mybatis-plus而是直接使用该驱动直接执行SQL是成功的，步骤如下：
1、把 DataInitRunner.java 类中的 mapper.insert(person); 注释掉，
2、下面添加以下代码：

        Connection connection;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite::resource:db/database.db");
            Statement statement = connection.createStatement();
            statement.execute("create table person(id, name)");
            statement.execute("insert into person(id, name) values (1, 'test')");
            statement.close();
            connection.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("insert successful.");

[nieqiurong]

@nieqiurong 我觉得不是这样的，sqlite-jdbc没有实现insert方法我觉得离谱，另外我不通过mybatis-plus而是直接使用该驱动直接执行SQL是成功的，步骤如下： 1、把 DataInitRunner.java 类中的 mapper.insert(person); 注释掉， 2、下面添加以下代码：

        Connection connection;
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite::resource:db/database.db");
            Statement statement = connection.createStatement();
            statement.execute("create table person(id, name)");
            statement.execute("insert into person(id, name) values (1, 'test')");
            statement.close();
            connection.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("insert successful.");

要返回自增主键

[nieqiurong]

jdbc batch

[wp2code]

是驱动不支持 getGeneratedKeys 不是 insert不支持

[duelngmo]

将版本回退到3.42.0.1可以暂时解决，sqlite更改了获取id的方法，因为原来的方法不能正确返回id。现在是需要在sql语句后面添加 RETURNING 子句，然后才会返回id结果，这不是标准sql语法。mybatis-plus还未支持这种写法。
来源
https://www.sqlite.org/lang_returning.html
xerial/sqlite-jdbc#963