Categories
java jdbc mysql

Connect Java to a MySQL database

350

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

1

227

DriverManager is a fairly old way of doing things. The better way is to get a DataSource, either by looking 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 instantiating and configuring one from your database driver directly:

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();

12

  • 1

    how come the other examples use com.mysql.jdbc.Driver? is this method better?

    – Jason S

    May 21, 2010 at 3:21

  • 8

    I think this is the old-style Driver class that works with the old-style driver mechanism. MysqlDataSource implements javax.sql.DataSource which is the newer mechanism.

    – Sean Owen

    May 21, 2010 at 9:00

  • 1

    Hi @SeanOwen I wonder that, why do we close rs and stmt? Why not just conn?

    Apr 14, 2016 at 6:37

  • 4

    Maybe you should add dataSource.setDatabaseName(“database”).

    – Myoch

    May 22, 2016 at 11:14


  • 1

    It’s good practice to close() things explicitly, though it is more code. Any good implementation would have to close the resources when the connection closes, yes. Consider other contexts where you want to reuse a statement or connection though. In Java 7’s try-with-resources, you get this behavior for free anyway:

    – Sean Owen

    May 29, 2016 at 13:22

227

DriverManager is a fairly old way of doing things. The better way is to get a DataSource, either by looking 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 instantiating and configuring one from your database driver directly:

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();

12

  • 1

    how come the other examples use com.mysql.jdbc.Driver? is this method better?

    – Jason S

    May 21, 2010 at 3:21

  • 8

    I think this is the old-style Driver class that works with the old-style driver mechanism. MysqlDataSource implements javax.sql.DataSource which is the newer mechanism.

    – Sean Owen

    May 21, 2010 at 9:00

  • 1

    Hi @SeanOwen I wonder that, why do we close rs and stmt? Why not just conn?

    Apr 14, 2016 at 6:37

  • 4

    Maybe you should add dataSource.setDatabaseName(“database”).

    – Myoch

    May 22, 2016 at 11:14


  • 1

    It’s good practice to close() things explicitly, though it is more code. Any good implementation would have to close the resources when the connection closes, yes. Consider other contexts where you want to reuse a statement or connection though. In Java 7’s try-with-resources, you get this behavior for free anyway:

    – Sean Owen

    May 29, 2016 at 13:22

47

Initialize database constants

Create constant properties database username, password, URL and drivers, polling limit etc.

// init database constants
// com.mysql.jdbc.Driver
private static final String DATABASE_DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/database_name";
private static final String USERNAME = "root";
private static final String PASSWORD = "";
private static final String MAX_POOL = "250"; // set your own limit

Initialize Connection and Properties

Once the connection is established, it is better to store for reuse purpose.

// init connection object
private Connection connection;
// init properties object
private Properties properties;

Create Properties

The properties object hold the connection information, check if it is already set.

// create properties
private Properties getProperties() {
    if (properties == null) {
        properties = new Properties();
        properties.setProperty("user", USERNAME);
        properties.setProperty("password", PASSWORD);
        properties.setProperty("MaxPooledStatements", MAX_POOL);
    }
    return properties;
}

Connect the Database

Now connect to database using the constants and properties initialized.

// connect database
public Connection connect() {
    if (connection == null) {
        try {
            Class.forName(DATABASE_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, getProperties());
        } catch (ClassNotFoundException | SQLException e) {
            // Java 7+
            e.printStackTrace();
        }
    }
    return connection;
}

Disconnect the database

Once you are done with database operations, just close the connection.

// disconnect database
public void disconnect() {
    if (connection != null) {
        try {
            connection.close();
            connection = null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Everything together

Use this class MysqlConnect directly after changing database_name, username and password etc.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class MysqlConnect {
    // init database constants
    private static final String DATABASE_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/database_name";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";
    private static final String MAX_POOL = "250";

    // init connection object
    private Connection connection;
    // init properties object
    private Properties properties;

    // create properties
    private Properties getProperties() {
        if (properties == null) {
            properties = new Properties();
            properties.setProperty("user", USERNAME);
            properties.setProperty("password", PASSWORD);
            properties.setProperty("MaxPooledStatements", MAX_POOL);
        }
        return properties;
    }

    // connect database
    public Connection connect() {
        if (connection == null) {
            try {
                Class.forName(DATABASE_DRIVER);
                connection = DriverManager.getConnection(DATABASE_URL, getProperties());
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    // disconnect database
    public void disconnect() {
        if (connection != null) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

How to Use?

Initialize the database class.

// !_ note _! this is just init
// it will not create a connection
MysqlConnect mysqlConnect = new MysqlConnect();

Somewhere else in your code …

String sql = "SELECT * FROM `stackoverflow`";
try {
    PreparedStatement statement = mysqlConnect.connect().prepareStatement(sql);
    ... go on ...
    ... go on ...
    ... DONE ....
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    mysqlConnect.disconnect();
}

This is all 🙂 If anything to improve edit it! Hope this is helpful.

3

  • Mark, does each class need to maintain it’s own separate MysqlConnect instance open at all times – assuming they need to interact wit the data? I’m just wondering how this setup works between classes.

    Nov 19, 2017 at 16:42

  • in place of com.mysql.jdbc.Driver this jdbc:mysql://localhost:3306/stocks should be used as the former is deprecated.

    Oct 23, 2018 at 12:20

  • If you are going to hardwire account name, password, database name etc, this way is very clumsy. Just put all of those details into the JDBC URL string. (Including the pool size …)

    – Stephen C

    Mar 6, 2020 at 2:12