Skip to content

Instantly share code, notes, and snippets.

@xfn14
Created July 19, 2022 17:26
Show Gist options
  • Save xfn14/1993b322321b7e447df69db4633febf5 to your computer and use it in GitHub Desktop.
Save xfn14/1993b322321b7e447df69db4633febf5 to your computer and use it in GitHub Desktop.
Java Hikari database connection
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
public class HikariConnection {
public interface RowMapper<T> {
T mapRow(ResultSet resultSet) throws SQLException;
}
private HikariDataSource source;
public HikariConnection(String host, int port, String database, String username, String password) {
HikariConfig config = new HikariConfig();
config.setDataSourceClassName("com.mysql.cj.jdbc.MysqlDataSource"); // Class name of the JDBC driver
config.addDataSourceProperty(
"url", "jdbc:mysql://" +
host + ":" + port + "/" + database
);
config.addDataSourceProperty("user", username);
config.addDataSourceProperty("password", password);
config.addDataSourceProperty("cachePrepStmts", true);
config.addDataSourceProperty("prepStmtCacheSize", 250);
config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
config.addDataSourceProperty("useServerPrepStmts", true);
config.addDataSourceProperty("cacheCallableStmts", true);
config.addDataSourceProperty("cacheResultSetMetadata", true);
config.addDataSourceProperty("cacheServerConfiguration", true);
config.addDataSourceProperty("useLocalSessionState", true);
config.addDataSourceProperty("elideSetAutoCommits", true);
config.addDataSourceProperty("alwaysSendSetIsolation", false);
config.setConnectionTestQuery("SELECT 1");
config.setMinimumIdle(3);
config.setMaximumPoolSize(5);
this.source = new HikariDataSource(config);
}
protected <T> List<T> listRows(RowMapper<T> rowMapper, String query, Object... args) throws SQLException {
List<T> rows = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
try {
connection = this.source.getConnection();
statement = connection.prepareStatement(query);
setObjects(statement = connection.prepareStatement(query), args);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next())
rows.add(rowMapper.mapRow(resultSet));
} finally {
closeAll(statement, connection);
}
return rows;
}
protected <T> T getRow(RowMapper<T> rowMapper, String query, Object... args) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = this.source.getConnection();
setObjects(statement = connection.prepareStatement(query), args);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next())
return rowMapper.mapRow(resultSet);
} finally {
closeAll(statement, connection);
}
return null;
}
protected int update(String query, Object... args) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = this.source.getConnection();
setObjects(statement = connection.prepareStatement(query), args);
return statement.executeUpdate();
} finally {
closeAll(statement, connection);
}
}
protected int[] updateBatch(String query, List<Object[]> argsList) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = this.source.getConnection();
statement = connection.prepareStatement(query);
for (Object[] args : argsList) {
setObjects(statement, args);
statement.addBatch();
}
return statement.executeBatch();
} finally {
closeAll(statement, connection);
}
}
private void setObjects(PreparedStatement statement, Object... args) throws SQLException {
for (int i = 1; i <= args.length; i++) {
Object value = args[i - 1];
if (value instanceof UUID) {
statement.setString(i, value.toString());
} else if (value instanceof Enum) {
statement.setString(i, ((Enum<?>) value).name());
} else statement.setObject(i, value);
}
System.out.println(statement.toString());
}
private void closeAll(AutoCloseable... autoCloseables) {
for (AutoCloseable autoCloseable : autoCloseables)
if (autoCloseable != null)
try {
autoCloseable.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
return this.source.getConnection();
}
public synchronized void close() {
if (this.source != null) {
this.source.close();
this.source = null;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment