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

Burchell

Here's a short 3-minute video tutorial that demonstrates using MySQL from Java. Check it out here: Quick Tutorial: Connecting to MySQL database using Java – Damocles

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");
) {
    …
}

Perfectionist

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

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. – Perfectionist

@SeanOwen +1 for Good explanation. I was not aware of this method, because most of the people talk about DriverManager. – Nu

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

Maybe you should add dataSource.setDatabaseName("database"). – Besotted

@SeanOwen Yes: Why do we need to close both of them ? – Interplanetary

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: – Perfectionist

How do I configure the context: java:comp/env/jdbc/myDB and where can it be found/configured? – Shifflett

There is a good tutorial here: journaldev.com/2509/java-datasource-jdbc-datasource-example – Puerile

According to the Java API specification, you do not need to close ResultSet objects: "When a Statement object is closed, its current ResultSet object, if one exists, is also closed." – Uturn

Perhaps this example should be updated to use PreparedStatements instead, as they're generally preferable for security reasons? Or should it be left alone for simplicity's sake? – Zoosporangium

I think PreparedStatement is orthogonal - point here is connecting to the DB. – Perfectionist

@GéryOgam In the past there were some notorious cases of JDBC drivers that failed to properly cascade the closing of such JDBC objects — thereby violating the requirements of the JDBC specification. These crucial flaws taught us to make a practice of explicitly closing each and every individual JDBC resource. This is now trivially easy to do: In modern Java, JDBC resources implement AutoCloseable, so we can use very simple try-with-resources syntax to automatically close. – Sankey

Answer 2

Here's a step by step explanation how to install MySQL and JDBC and how to use it:

1. Download and install the MySQL server. Just do it the usual way. Remember the port number whenever you've changed it. It's by default 3306.

2. Download the JDBC driver and put in classpath, extract the ZIP file and put the containing JAR file in the classpath. The vendor-specific JDBC driver is a concrete implementation of the JDBC API (tutorial here).

   If you're using an IDE like Eclipse or Netbeans, then you can add it to the classpath by adding the JAR file as Library to the Build Path in project's properties.

   If you're doing it "plain vanilla" in the command console, then you need to specify the path to the JAR file in the -cp or -classpath argument when executing your Java application.

   java -cp .;/path/to/mysql-connector.jar com.example.YourClass

   The . is just there to add the current directory to the classpath as well so that it can locate com.example.YourClass and the ; is the classpath separator as it is in Windows. In Unix and clones : should be used.

   If you're developing a servlet based WAR application and wish to manually manage connections (poor practice, actually), then you need to ensure that the JAR ends up in /WEB-INF/lib of the build. See also How to add JAR libraries to WAR project without facing java.lang.ClassNotFoundException? Classpath vs Build Path vs /WEB-INF/lib. The better practice is to install the physical JDBC driver JAR file in the server itself and configure the server to create a JDBC connection pool. Here's an example for Tomcat: How should I connect to JDBC database / datasource in a servlet based application?

3. Create a database in MySQL. Let's create a database javabase. You of course want World Domination, so let's use UTF-8 as well.

    CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
   

4. Create a user for Java and grant it access. Simply because using root is a bad practice.

    CREATE USER 'java'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL ON javabase.* TO 'java'@'localhost' IDENTIFIED BY 'password';
   

   Yes, java is the username and password is the password here.

5. Determine the JDBC URL. To connect the MySQL database using Java you need an JDBC URL in the following syntax:

   jdbc:mysql://hostname:port/databasename

   • hostname: The hostname where MySQL server is installed. If it's installed at the same machine where you run the Java code, then you can just use localhost. It can also be an IP address like 127.0.0.1. If you encounter connectivity problems and using 127.0.0.1 instead of localhost solved it, then you've a problem in your network/DNS/hosts config.

   • port: The TCP/IP port where MySQL server listens on. This is by default 3306.

   • databasename: The name of the database you'd like to connect to. That's javabase.

   So the final URL should look like:

   jdbc:mysql://localhost:3306/javabase

6. Test the connection to MySQL using Java. Create a simple Java class with a main() method to test the connection.

    String url = "jdbc:mysql://localhost:3306/javabase";
    String username = "java";
    String password = "password";
   
    System.out.println("Connecting database ...");
   
    try (Connection connection = DriverManager.getConnection(url, username, password)) {
        System.out.println("Database connected!");
    } catch (SQLException e) {
        throw new IllegalStateException("Cannot connect the database!", e);
    }
   

   If you get a SQLException: No suitable driver, then it means that either the JDBC driver wasn't autoloaded at all or that the JDBC URL is wrong (i.e. it wasn't recognized by any of the loaded drivers). See also The infamous java.sql.SQLException: No suitable driver found. Normally, a JDBC 4.0 driver should be autoloaded when you just drop it in runtime classpath. To exclude one and other, you can always manually load it as below:

    System.out.println("Loading driver ...");
   
    try {
        Class.forName("com.mysql.cj.jdbc.Driver"); // Use com.mysql.jdbc.Driver if you're not on MySQL 8+ yet.
        System.out.println("Driver loaded!");
    } catch (ClassNotFoundException e) {
        throw new IllegalStateException("Cannot find the driver in the classpath!", e);
    }
   

   Note that the newInstance() call is not needed here. It's in case of MySQL just to fix the old and buggy org.gjt.mm.mysql.Driver. Explanation here. If this line throws ClassNotFoundException, then the JAR file containing the JDBC driver class is simply not been placed in the classpath. Please also note that it's very important to throw an exception so that the code execution is immediately blocked, instead of suppressing it of merely printing the stack trace and then continuing the rest of the code.

   Also note that you don't need to load the driver everytime before connecting. Just only once during application startup is enough.

   If you get a SQLException: Connection refused or Connection timed out or a MySQL specific CommunicationsException: Communications link failure, then it means that the DB isn't reachable at all. This can have one or more of the following causes:

   1. IP address or hostname in JDBC URL is wrong.
   2. Hostname in JDBC URL is not recognized by local DNS server.
   3. Port number is missing or wrong in JDBC URL.
   4. DB server is down.
   5. DB server doesn't accept TCP/IP connections.
   6. DB server has run out of connections.
   7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.
      
      

   To solve the one or the other, follow the following advices:

   1. Verify and test them with ping.
   2. Refresh DNS or use IP address in JDBC URL instead.
   3. Verify it based on my.cnf of MySQL DB.
   4. Start the DB.
   5. Verify if mysqld is started without the --skip-networking option.
   6. Restart the DB and fix your code accordingly that it closes connections in finally.
   7. Disable firewall and/or configure firewall/proxy to allow/forward the port.
      
      

   Note that closing the Connection is extremely important. If you don't close connections and keep getting a lot of them in a short time, then the database may run out of connections and your application may break. Always acquire the Connection in a try-with-resources statement. This also applies to Statement, PreparedStatement and ResultSet. See also How often should Connection, Statement and ResultSet be closed in JDBC?

That was it as far the connectivity concerns. You can find here a more advanced tutorial how to load and store fullworthy Java model objects in a database with help of a basic DAO class.

---

Using a Singleton Pattern and/or a static variable for the DB Connection is a bad practice. See among others Is it safe to use a static java.sql.Connection instance in a multithreaded system? This is a #1 starters mistake. Make sure you don't fall in this trap.

Gemmell

I was getting the same error. Updating the driver version worked for me. downloads.mysql.com/archives/c-j – Quaint

What should be the hostname and port value in JDBC url, if connecting to a MySQL docker container (using a docker-compose.yaml)? Is it still localhost or 172.17.0.1 (docker0) or the mysql service name? – Derbent

@marc: hostname is indeed the docker service name (the key of services object). Port is the second value you've specified in ports config. If it's 3306:3306 then it's still 3306. – Gemmell

@Gemmell : I got rid of the connection refused error by changing database.driver value from com.mysql.jdbc.Driver to com.mysql.jdbc.driver. However, does it really because of the capital letter? I just want to make sure, thank you – Derbent

Answer 3

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.

Yorgos

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. – Lassalle

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

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 ...) – Ticon

I would suggest using a DataSource implementation rather than these hard-coded properties. Ex: com.mysql.cj.jdbc.MysqlDataSource. See documentation. – Sankey

I would suggest using a DataSource implementation rather than these hard-coded properties. Ex: com.mysql.cj.jdbc.MysqlDataSource. See documentation. See Answer by Sean Oven. – Sankey

Answer 4

String url = "jdbc:mysql://127.0.0.1:3306/yourdatabase";
String user = "username";
String password = "password";

// Load the Connector/J driver
Class.forName("com.mysql.jdbc.Driver").newInstance();
// Establish connection to MySQL
Connection conn = DriverManager.getConnection(url, user, password);

Earnest

what is yourdatabase in here? database name? – Swinford

newInstance() is not necessary. Is it? – Ferneferneau

Nope. It isn't. And since Java 6 this whole approach is out of date. And the driver class name has changed and .... – Ticon

What is interesting is that in the doc for DriverManager docs.oracle.com/javase/6/docs/api/java/sql/DriverManager.html it is stated that no longer Class.forName() is needed but instead some properties file java.sql.Driver could be used – Aeroscope

database name here seems to be "yourdatabase" as you can see in the url – Aeroscope

Answer 5

Here's the very minimum you need to get data out of a MySQL database:

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection
   ("jdbc:mysql://localhost:3306/foo", "root", "password");

Statement stmt = conn.createStatement();
stmt.execute("SELECT * FROM `FOO.BAR`");
stmt.close();
conn.close();

Add exception handling, configuration etc. to taste.

Whencesoever

why do you need Class.forName(...).newInstance()? – Secondbest

@mmcrae You don't, since 2007. – Raquelraquela

Answer 6

you need to have mysql connector jar in your classpath.

in Java JDBC API makes everything with databases. using JDBC we can write Java applications to
1. Send queries or update SQL to DB(any relational Database) 2. Retrieve and process the results from DB

with below three steps we can able to retrieve data from any Database

Connection con = DriverManager.getConnection(
                     "jdbc:myDriver:DatabaseName",
                     dBuserName,
                     dBuserPassword);

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table");

while (rs.next()) {
    int x = rs.getInt("a");
    String s = rs.getString("b");
    float f = rs.getFloat("c");
}

Bula

Answer 7

You can see all steps to connect MySQL database from Java application here. For other database, you just need to change the driver in first step only. Please make sure that you provide right path to database and correct username and password.

Visit http://apekshit.com/t/51/Steps-to-connect-Database-using-JAVA

Philologian

Answer 8

Short and Sweet code.

try {       
    Class.forName("com.mysql.jdbc.Driver");
    System.out.println("Driver Loaded");
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB","root","");
    //Database Name - testDB, Username - "root", Password - ""
    System.out.println("Connected...");         
} catch(Exception e) {
    e.printStackTrace();
}

For SQL server 2012

try {
    String url = "jdbc:sqlserver://KHILAN:1433;databaseName=testDB;user=Khilan;password=Tuxedo123"; 
    //KHILAN is Host    and 1433 is port number     
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    System.out.println("Driver Loaded");
    conn = DriverManager.getConnection(url);
    System.out.println("Connected...");
} catch(Exception e) {
    e.printStackTrace();
}

Acerbate

Answer 9

MySQL JDBC Connection with useSSL.

private String db_server = BaseMethods.getSystemData("db_server");
private String db_user = BaseMethods.getSystemData("db_user");
private String db_password = BaseMethods.getSystemData("db_password");

private String connectToDb() throws Exception {
   String jdbcDriver = "com.mysql.jdbc.Driver";
   String dbUrl = "jdbc:mysql://" + db_server  +
        "?verifyServerCertificate=false" +
        "&useSSL=true" +
        "&requireSSL=true";
    System.setProperty(jdbcDriver, "");
    Class.forName(jdbcDriver).newInstance();

    Connection conn = DriverManager.getConnection(dbUrl, db_user, db_password);
    Statement statement = conn.createStatement();
    String query = "SELECT EXTERNAL_ID FROM offer_letter where ID =" + "\"" + letterID + "\"";
    ResultSet resultSet = statement.executeQuery(query);
    resultSet.next();
    return resultSet.getString(1);
}

Pelagia

The way the query string is created is an example of a very bad practice that is vulnerable to SQL injection attacks. – Earflap

Answer 10

Connection I was using some time ago, it was looking like the easiest way, but also there were recommendation to make there if statement- exactly

Connection con = DriverManager.getConnection(
                     "jdbc:myDriver:DatabaseName",
                     dBuserName,
                     dBuserPassword);
if (con != null){
 //..handle your code there 
}

Or something like in that way :)

Probably there's some case, while getConnection can return null :)

Pb

Answer 11

HOW

• To set up the Driver to run a quick sample

1. Go to https://dev.mysql.com/downloads/connector/j/, get the latest version of Connector/J

2. Remember to set the classpath to include the path of the connector jar file.
If we don't set it correctly, below errors can occur:

No suitable driver found for jdbc:mysql://127.0.0.1:3306/msystem_development

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

• To set up the CLASSPATH

Method 1: set the CLASSPATH variable.

export CLASSPATH=".:mysql-connector-java-VERSION.jar"
java MyClassFile

In the above command, I have set the CLASSPATH to the current folder and mysql-connector-java-VERSION.jar file. So when the java MyClassFile command executed, java application launcher will try to load all the Java class in CLASSPATH. And it found the Drive class => BOOM errors was gone.

Method 2:

java -cp .:mysql-connector-java-VERSION.jar MyClassFile

Note: Class.forName("com.mysql.jdbc.Driver"); This is deprecated at this moment 2019 Apr.

Hope this can help someone!

Greenwell

Answer 12

MySql JDBC Connection:

Class.forName("com.mysql.jdbc.Driver");     

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/DatabaseName","Username","Password");         
Statement stmt=con.createStatement();            
stmt = con.createStatement();
ResultSet rs=stmt.executeQuery("Select * from Table");  

Goulden

Answer 13

Short Code

public class DB {

    public static Connection c;

    public static Connection getConnection() throws Exception {
        if (c == null) {
            Class.forName("com.mysql.jdbc.Driver");
            c =DriverManager.getConnection("jdbc:mysql://localhost:3306/DATABASE", "USERNAME", "Password");
        }
        return c;
    }

    // Send data TO Database
    public static void setData(String sql) throws Exception {
        DB.getConnection().createStatement().executeUpdate(sql);
    }

    // Get Data From Database
    public static ResultSet getData(String sql) throws Exception {
        ResultSet rs = DB.getConnection().createStatement().executeQuery(sql);
        return rs;
    }
}

Arletha

Answer 14

Download JDBC Driver

Download link (Select platform independent): https://dev.mysql.com/downloads/connector/j/

Move JDBC Driver to C Drive

Unzip the files and move to C:\ drive. Your driver path should be like C:\mysql-connector-java-8.0.19\mysql-connector-java-8.0.19

Run Your Java

java -cp "C:\mysql-connector-java-8.0.19\mysql-connector-java-8.0.19\mysql-connector-java-8.0.19.jar" testMySQL.java

testMySQL.java

import java.sql.*;
import java.io.*;

public class testMySQL {
    public static void main(String[] args) {
    // TODO Auto-generated method stub
        try
        {  
            Class.forName("com.mysql.cj.jdbc.Driver");  
            Connection con=DriverManager.getConnection(  
                "jdbc:mysql://localhost:3306/db?useSSL=false&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC","root","");  
            Statement stmt=con.createStatement();  
            ResultSet rs=stmt.executeQuery("show databases;");  
            System.out.println("Connected");  
        }
        catch(Exception e)
        {
            System.out.println(e);
        }

    }  

}

Wo