Spring应用执行无结果SQLite查询时崩溃，求解决方案

问题场景 #

使用SQLite数据库时，执行预期无结果的查询会导致应用崩溃，但切换到MariaDB时无此异常。

报错信息 #

2022-08-10 16:10:47.160  WARN 15512 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: null
2022-08-10 16:10:47.160 ERROR 15512 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Query does not return results
2022-08-10 16:10:47.207 ERROR 15512 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet] with root cause

相关代码 #

查询语句 #

@Query(value = "SELECT * FROM Port WHERE user_id=:userId", nativeQuery = true)
Port checkIfUserHasPort(@Param("userId") Long userId);

Port实体 #

@Entity
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Port {
    @Id
    private int port;
    private boolean active;
    private LocalDateTime expiration;
    @OneToOne
    @JoinColumn(name="id")
    private User user;
}

User实体 #

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "user")
public class User {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "id_Sequence")
    @SequenceGenerator(name = "id_Sequence", sequenceName = "ID_SEQ")
    private Long id;
    @JsonIgnore
    private String username;
    @JsonIgnore
    private String password;
    @JsonIgnore
    @OneToOne(mappedBy = "user")
    private Port port;
    @JsonIgnore
    @ManyToMany(fetch = EAGER)
    private Collection<Role> roles = new ArrayList<>();
    @JsonIgnore
    @OneToMany(mappedBy = "user")
    private Set<Log> logs=new HashSet<>();
}

解决方案 #

1. 修改方法返回类型为Optional<Port> #

原方法直接返回Port，当查询无结果时，SQLite的JDBC驱动会抛出无结果异常，而MariaDB会返回null，导致数据库间行为差异。将返回类型改为Optional<Port>，Hibernate会在无结果时返回Optional.empty()，避免抛出异常：

@Query(value = "SELECT * FROM Port WHERE user_id=:userId", nativeQuery = true)
Optional<Port> checkIfUserHasPort(@Param("userId") Long userId);

调用时通过Optional的API处理结果：

Optional<Port> portOpt = portRepository.checkIfUserHasPort(userId);
if (portOpt.isPresent()) {
    Port port = portOpt.get();
    // 存在端口的业务逻辑
} else {
    // 无端口的业务逻辑
}

2. 替换原生SQL为JPA查询 #

使用JPA查询而非原生SQL，让Hibernate自动适配不同数据库的语法和结果处理逻辑，消除数据库差异带来的问题：

@Query("SELECT p FROM Port p WHERE p.user.id = :userId")
Optional<Port> checkIfUserHasPort(@Param("userId") Long userId);

3. 更新SQLite JDBC驱动版本 #

部分旧版本的SQLite JDBC驱动对无结果集的处理存在兼容性bug，更新到最新稳定版（如xerial/sqlite-jdbc的最新版）可解决此类问题。

4. 正确捕获异常（可选） #

若必须保留原返回类型，需确保捕获正确的异常类型。原try-catch无效大概率是因为捕获范围不对，应捕获JpaSystemException或其上层的RuntimeException：

try {
    Port port = portRepository.checkIfUserHasPort(userId);
    // 结果处理逻辑
} catch (JpaSystemException e) {
    // 处理无结果或JPA相关异常
}

此方案优先级低于前两种，仅作为临时兼容方案使用。

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