Connect Java to a MySQL database

Question

How do you connect to a MySQL database in Java?

When I try, I get

java.sql.SQLException: No suitable driver found for jdbc:mysql://database/table
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)

Or

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

Or

java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver

Answer 1

DataSource

DriverManager is a fairly old way of doing things. The better way is to get a DataSource object. Either by using JNDI to look one up that your app server container already configured for you:

Context context = new InitialContext();
DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/myDB");

… or by instantiating and configuring one from your database driver directly, such as com.mysql.cj.jdbc.MysqlDataSource (see documentation):

MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUser("scott");
dataSource.setPassword("tiger");
dataSource.setServerName("myDBHost.example.org");

… and then obtain connections from it, same as above:

Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT ID FROM USERS");
…
rs.close();
stmt.close();
conn.close();

In modern Java, use try-with-resources syntax to automatically close JDBC resources (now AutoCloseable).

try (
    Connection conn = dataSource.getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT ID FROM USERS");
) {
    …
}