This is a basic tutorial intended to showcase best practices for connecting to a SQL database. It will not tell you everything, but it is a good start.
Another assumption is that you will be using HikariCP. A connection pooling library will handle recreating Connections for you in a proper manner, while respecting network timeouts and max lifetime settings.
First make a class to wrap your HikariDataSource:
public class TutorialDatabase {
private final HikariDataSource dataSource;
public TutorialDatabase(HikariConfig hikariConf) {
dataSource = new HikariDataSource(hikariConf);
}
}You can setup this class by using a HikariConfig and instantiating your class:
String host, database, username, password;
int port;
HikariConfig hikariConf = new HikariConfig();
/*
* Each database vendor has its own driver
*
* There are 2 ways to specify the driver you want to use
* 1. Using dataSourceClassName
* 2. Using the traditional jdbcUrl
*
* Either way, you need the JDBC driver on your classpath.
*/
// For example, here is usage of jdbcUrl with MariaDB
hikariConf.setJdbcUrl("jdbc:mariadb://" + host + ':' + port + '/' + database);
hikariConf.setUsername(username);
hikariConf.setPassword(password);
// HikariCP recommends a fixed connection pool
int poolSize;
hikariConf.setMinimumIdle(poolSize);
hikariConf.setMaximumPoolSize(poolSize);
TutorialDatabase tutorialDatabase = new TutorialDatabase(hikariConf);You can create a method execute a basic which takes the values you want to insert into the database.
public void executeSomethingInDatabase(Object column1Value, Object column2Value) {
try (Connection conn = dataSource.getConnection();
PreparedStatement prepStmt = conn.prepareStatement("INSERT INTO table (column1, column2) VALUES (?, ?)")) {
// Set parameters
prepStmt.setObject(1, column1Value);
prepStmt.setObject(2, column2Value);
// Execute query
prepStmt.execute();
} catch (SQLException ex) {
ex.printStackTrace();
}
}In this example, PreparedStatement.setObject is used to generalise the kinds of parameters one can set. If column1Value
was an integer, for example, you would use prepStmt.setInteger(1, column1Value) and you would change the parameter
column1Value to be an int.
If you need to get the query's update count, you can use prepStmt.getUpdateCount() after you execute the query.
This example gets the first result from the database. It assumes there is a column called "columnName" which stores strings.
It then maps the row of the result set to a POJO (new Type). Creating a Java bean from JDBC results is standard. Also,
in this example, if the query encounters an error or no results are present, null is returned.
public Type getSomethingFromDatabase() {
try (Connection conn = dataSource.getConnection();
PreparedStatement prepStmt = conn.prepareStatement("SELECT * FROM table LIMIT 1");
ResultSet rs = prepStmt.executeQuery()) {
if (rs.next()) {
Object columnValue = rs.getString("columnName");
return new Type(columnValue);
}
} catch (SQLException ex) {
ex.printStackTrace();
}
return null;
}Most of the time, you want to select results matching specific details. This example achieves this by setting parameters,
iterating over the ResultSet, and mapping each row to a new Type.
public List<Type> getListFromDatabaseWhereColumnEquals(Object value) {
try (Connection conn = dataSource.getConnection();
PreparedStatement prepStmt = conn.prepareStatement(
"SELECT * FROM table WHERE value = ?")) {
// Sets the first parameter
prepStmt.setObject(1, value);
try (ResultSet rs = prepStmt.executeQuery()) {
List<Type> result = new ArrayList<>();
while (rs.next()) {
boolean columnValue = rs.getBoolean("columnName");
result.add(new Type(columnValue));
}
return result;
}
} catch (SQLException ex) {
ex.printStackTrace();
}
return List.of();
}List.of() is a Java 9 construct creating an immutable empty list. You can use Collections.emptyList on Java 8.
You may have noticed all these examples use try-with-resources blocks. That's intentional. This way, all the JDBC objects (Connection, PreparedStatement, ResultSet) are obtained and closed within scope. There is an implicit finally block which closes each of them if nonnull.
In these examples the exception is logged and the application continues. However, I recommend you create an unchecked wrapper for SQLException and rethrow any exceptions which arise. This will make your program more fail-fast, which is good.
Another thing you may want to consider is using a logging framework instead of ex.printStackTrace(). Whatever you do, don't just ignore it.
Do not keep a Connection open indefinitely. Do not keep one in a static field. If you want to re-use connections the right way, use a connection pool.
The autoReconnect property is often used in the absence of HikariCP when the developer wants to avoid the cost of re-establishing Connections. It is usually used along with a homemade single-Connection pool, as in the last pitfall.
This property is discouraged by Connector-J. It can sometimes lead to funny behaviour. Stay away from it.
It's true. JDBC is incredibly verbose. If you want to mitigate this, use a library such as:
Alternatively, you can use an ORM such as Hibernate.
However, you should NOT usually return PreparedStatements and ResultSet. This leaves the possibility that the caller will neglect the close them, leading to resource leaks. Always close your resources!
If you really want to return a ResultSet, look into CachedRowSet. Returning a populated CachedRowSet as a ResultSet is acceptable and will allow the caller to get results without worrying about closing Connections or having the Connection closed before the ResultSet can be used.