I'm relatively new to connection pooling, but from what I've read it seems ideal to leave some connections idle for faster performance.
I'm currently using JDBI, and after idle periods I'll get a
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
I would assume this is either because my database configuration settings are lacking or that I must be using the framework incorrectly:
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());
}
}
Using the setting
checkConnectionOnBorrow: true
Might work, but I would assume that the ideal solution would be to prevent my initial connections from being closed in the first place?
Any assistance is appreciated
