JDBI:空闲后自动关闭连接

Question

我对连接池比较陌生，但从我所读到的信息来看，为了获得更快的性能，让一些连接处于空闲状态似乎是理想的。

我目前正在使用JDBI，在空闲时间过后，我将获得一个

 com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

我假设这要么是因为我的数据库配置设置不足，要么是因为我肯定是错误地使用了框架：

config.yml：

database:
  # whether or not idle connections should be validated
  checkConnectionWhileIdle: false

  # the maximum amount of time to wait on an empty pool before throwing an exception
  maxWaitForConnection: 10s

  # Limits for simultaneous connections to DB
  minSize: 10
  initialSize: 10
  maxSize: 100

DAOs：

public class AccountDAO {

    private final Jdbi jdbi;

    public AccountDAO(Jdbi jdbi) {
        this.jdbi = jdbi;
    }

    public void addAccount(String id) {
        jdbi.useHandle(h ->
                h.createUpdate("INSERT INTO Account(id) values (:id)")
                        .bind("id", id)
                        .execute());
    }
}

public class RestaurantDAO {

    private final Jdbi jdbi;

    public RestaurantDAO(Jdbi jdbi) {
        this.jdbi = jdbi;
    }

    public Optional<RestaurantDTO> getRestaurantByName(String restName) {
        return jdbi.withHandle(h ->
                h.createQuery("SELECT * FROM Restaurant WHERE restName =:restName")
                        .bind("restName", restName)
                        .mapToBean(RestaurantDTO.class)
                        .findOne());
    }

    public void addRestaurant(String restName) {
        jdbi.useHandle(h ->
            h.createUpdate("INSERT INTO Restaurant(restName) values (:restName)")
                    .bind("restName", restName)
                    .execute()
        );
    }
}

public class ReviewDAO(Jdbi jdbi) {
        this.jdbi = jdbi;
    }

    public Optional<ReviewDTO> getReviewByAuthorAndRestaurant(String author, String restName) {
        return jdbi.withHandle(h ->
            h.createQuery("SELECT * FROM Review WHERE author=:author AND restName =:restName")
                .bind("author", author)
                .bind("restName", restName)
                .mapToBean(ReviewDTO.class)
                .findOne());
    }

    public List<ReviewDTO> getReviewsByAuthor(String author) {
        return jdbi.withHandle(h ->
                h.createQuery("SELECT * FROM Review WHERE author =:author ORDER BY created DESC")
                        .bind("author", author)
                        .mapToBean(ReviewDTO.class)
                        .list());
    }

    public List<ReviewDTO> getReviewsByRestaurant(String restName) {
        return jdbi.withHandle(h ->
                h.createQuery("SELECT * FROM Review WHERE restName =:restName ORDER BY created DESC")
                        .bind("restName", restName)
                        .mapToBean(ReviewDTO.class)
                        .list());
    }

    public List<ReviewDTO> getRecentReviews() {
        return jdbi.withHandle(h ->
                h.createQuery("SELECT top 5 * FROM Review ORDER BY created DESC")
                        .mapToBean(ReviewDTO.class)
                        .list());
    }

    public void addReview(String author, String restName, String title, String bodyText, int rating) {
        jdbi.useHandle(h ->
                    h.createUpdate("INSERT INTO Review(bodyText, rating, restName, author, title) values (:bodyText, :rating, :restName, :author, :title)")
                            .bind("bodyText", bodyText)
                            .bind("rating", rating)
                            .bind("restName", restName)
                            .bind("author", author)
                            .bind("title", title)
                            .execute());
    }

    public void updateReview(String author, String restName, String title, String bodyText, int rating) {
        jdbi.useHandle(h ->
                h.createUpdate("UPDATE Review SET bodyText=:bodyText, rating=:rating, title=:title where author=:author AND restName=:restName")
                        .bind("bodyText", bodyText)
                        .bind("rating", rating)
                        .bind("title", title)
                        .bind("author", author)
                        .bind("restName", restName)
                        .execute());
    }
    public void deleteReview(String author, String restName) {
        jdbi.useHandle(h ->
                h.createUpdate("DELETE FROM Review WHERE author=:author AND restName=:restName")
                .bind("author", author)
                .bind("restName", restName)
                .execute());

    }
}

使用设置

checkConnectionOnBorrow: true

可能行得通，但我假设最理想的解决方案是首先防止我的初始连接关闭？

任何帮助都是非常感谢的

Answer 1

原来我的数据库主机Azure会在30分钟后自动关闭空闲连接。目前，我已经在我的配置中添加了积极的验证设置，以相应地更新池。可能只是切换主机，因为看起来你不能在Azure端配置超时。

  validationQuery: "/* APIService Health Check */ SELECT 1"
  validationQueryTimeout: 3s
  checkConnectionWhileIdle: true
  minIdleTime: 25m
  evictionInterval: 5s
  validationInterval: 1m