I am developing a Spring Boot web (REST) application where I need to serve many requests. Therefore I wanted my application to be able to handle requests concurrently. Since Spring Boot REST-Services are out-of-the-box concurrently usable, I only need to make the (PostgreSQL) database access concurrently accessible. For that I am using the HikariCP data source.
Since a lot of my statements are prepared statements, I collected them in one method where I call pstmt = connection.prepareStatement("SQLCODE"); once for every statemment. Those prepared statements are then used in various methods when user interaction from the REST service is processed.
Now, when I use the HikariCP I can't do that anymore, can I? When I prepare a statement, this statement is bound to one connection. If I then try to access it concurrently, I can't because the connection is not shared.
Am I missing something? How can I solve this? Do I need to retrieve a connection from the pool, prepare the statement locally, execute my query, and close the connection? If so, what's the point of using a prepared statement then (other than preventing SQL injection)?
I know that the statements are cached by on the PostreSQL side. So would it be a good idea to keep the method where all prepared statements are prepared? To sent them to the database cache. And then just creating locally the same statements again. That way, one might still leverage the caching possibilities of the database. But on the other hand it would be really ugly code.
Im am using Spring: 5.3.10, Java: 11, PostgreSQL: 14.0
@RestController
public class RESTController {
/** The database controller. */
private DBController dbc;
/** The data source object serving as a connection pool. */
private HikariDataSource ds;
/** The logger object for this class. */
private static Logger logger = LoggerFactory.getLogger(RESTController.class);
public RESTController(DBController dbc, Config config) {
this.dbc = dbc;
// Create the database
if (!this.dbc.createDB(config)) {
logger.error("Couldn't create the database. The service will now exit.");
Runtime.getRuntime().halt(1);
}
// Create a connection pool
ds = new HikariDataSource();
ds.setJdbcUrl(config.getUrl());
ds.setUsername(config.getUser());
ds.setPassword(config.getPassword());
ds.addDataSourceProperty("cachePrepStmts", "true");
ds.addDataSourceProperty("prepStmtCacheSize", "250");
ds.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
// Create the necessary tables
if (!this.dbc.createTables(ds)) {
logger.error("Couldn't create the tables. The service will now exit.");
ds.close();
Runtime.getRuntime().halt(1);
}
// Prepare SQL statements
if (!this.dbc.prepareStatements(ds)) {
logger.error("Couldn't prepare the SQL statements. The service will now exit.");
ds.close();
Runtime.getRuntime().halt(1);
}
}
@PostMapping("/ID")
public ResponseEntity<String> createNewDomain(@RequestParam(name = "name", required = true) String name) {
// Do stuff ...
}
// [...]
}
@Component
public class DBController {
/** The logger object for this class. */
private static Logger logger = LoggerFactory.getLogger(DBController.class);
// Prepared Statements
private PreparedStatement stmt1, stmt2, stmt3;
public boolean prepareStatements(HikariDataSource ds) {
try {
// Get connection from the pool
Connection c = ds.getConnection();
// Prepare all the statements
stmt1 = c.prepareStatement("SQLCODE");
stmt2 = c.prepareStatement("SQLCODE1");
stmt2 = c.prepareStatement("SQLCODE1");
// [...]
} catch (SQLException e) {
logger.debug("Could not prepare the SQL statements: " + e.getMessage());
return false;
}
logger.debug("Successfully prepared the SQL statements.");
return true;
}
public boolean m1(int i) {
stmt1.setInt(i);
ResultSet rs = stmt1.executeQuery();
}
public boolean m2(int j) {
stmt1.setInt(j);
ResultSet rs = stmt1.executeQuery();
}
public boolean m3(String a) {
stmt2.setString(a);
ResultSet rs = stmt2.executeQuery();
}
// [...]
}
Thanks in advance.
