Connect to MySQL with JDBC driver

A JDBC example to show you how to connect to a MySQL database with a JDBC driver.

Tested with:

  • Java 8
  • MySQL 5.7
  • MySQL JDBC driver mysql-connector-java:8.0.16

1. Download MySQL JDBC Driver

Visit https://dev.mysql.com/downloads/connector/j/ to download the latest MySQL JDBC Driver.

mysql-connector-j

2. JDBC Connection

2.1 Make a connection to the MySQL database.

JDBCExample.java

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

public class JDBCExample {

    public static void main(String[] args) {

        // https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html#package.description
        // auto java.sql.Driver discovery -- no longer need to load a java.sql.Driver class via Class.forName

        // register JDBC driver, optional since java 1.6
        /*try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }*/

        // auto close connection
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1:3306/test", "root", "password")) {

            if (conn != null) {
                System.out.println("Connected to the database!");
            } else {
                System.out.println("Failed to make connection!");
            }

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Test:


# compile
> javac JDBCExample.java

# run
> java JDBCExample

SQL State: 08001
No suitable driver found for jdbc:mysql://127.0.0.1:3306/test

To run it with java command, we need to load the MySQL JDBC driver manually. Assume everything is stored in the c:\test folder, run it again with this -cp option.

project layout

> java -cp "c:\test\mysql-connector-java-8.0.16.jar;c:\test" JDBCExample
Connected to the database!

3. Maven Project

3.1 The MySQL JDBC driver is available in the Maven central repository.

pom.xml

	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.16</version>
    </dependency>

3.2 A simple JDBC select example.

JDBCExample2.java

import com.mkyong.jdbc.model.Employee;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCExample2 {

    public static void main(String[] args) {

        System.out.println("MySQL JDBC Connection Testing ~");

        List<Employee> result = new ArrayList<>();

        String SQL_SELECT = "Select * from EMPLOYEE";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1:3306/test", "root", "password");
             PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT)) {

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                long id = resultSet.getLong("ID");
                String name = resultSet.getString("NAME");
                BigDecimal salary = resultSet.getBigDecimal("SALARY");
                Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");

                Employee obj = new Employee();
                obj.setId(id);
                obj.setName(name);
                obj.setSalary(salary);
                // Timestamp -> LocalDateTime
                obj.setCreatedDate(createdDate.toLocalDateTime());

                result.add(obj);

            }
            result.forEach(x -> System.out.println(x));

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}
Employee.java

import java.math.BigDecimal;
import java.time.LocalDateTime;

public class Employee {

    private Long id;
    private String name;
    private BigDecimal salary;
    private LocalDateTime createdDate;

    //...
}

Table definition.


CREATE TABLE EMPLOYEE
(
    ID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    SALARY DECIMAL(15, 2) NOT NULL,
    CREATED_DATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ID)
);

Download Source Code

References

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
66 Comment threads
30 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
70 Comment authors
emirMatanimanRahul Kushwahaddwwww Recent comment authors
newest oldest most voted
casiana
Guest
casiana

MySQL JDBC Connection Testing ————

MySQL JDBC Driver Registered!

Connection Failed! Check output console

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1129)

at com.mysql.jdbc.MysqlIO.(MysqlIO.java:358)

at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2498)

at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2535)

at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2320)

at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:834)

at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)

at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:347)

at java.sql.DriverManager.getConnection(DriverManager.java:582)

at java.sql.DriverManager.getConnection(DriverManager.java:185)

at my.Connect.main(Connect.java:32)

Caused by: java.net.ConnectException: Connection refused: connect

at java.net.PlainSocketImpl.socketConnect(Native Method)

at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)

at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)

at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)

at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)

at java.net.Socket.connect(Socket.java:529)

at java.net.Socket.connect(Socket.java:478)

at java.net.Socket.(Socket.java:375)

at java.net.Socket.(Socket.java:218)

at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)

at com.mysql.jdbc.MysqlIO.(MysqlIO.java:308)

… 15 more

trackback
JDBC Tutorials

[…] Connect to MySQL with JDBC driver Here’s an example to show you how to connect to MySQL database via JDBC driver. […]

Roque Folminar
Guest
Roque Folminar

thank you so much for this article.
Can I also apply this connection to my SQL mail database connecting a ticketing system that requires connection to SQL? Please advise . Thank you.

viroth
Guest
viroth

Helpful, Thank you sir.

githin
Guest
githin

really helpful.thank you

githin
Guest
githin

after dwnloading the driver, will it be added to jdk or is it independent?

trackback
JSF 2.0 + JDBC integration example

[…] JDBC example in Java […]

ivan
Guest
ivan

just what i was looking for simple and usefull.

Nidheesh
Guest
Nidheesh

hey,
im new to the language and i really wanted to do a java application having a database connection.I just happend to saw ur example,but when i run the program i got the message “where is ur Mysql jdbc driver?”…what do i do now?

dileep
Guest
dileep

why we need class.forname(…), instead new to create object

sudeep
Guest
sudeep

I am not getting, i downlaoded driver and placed in E:directory, codes also in the E dir

give path as c:java\jdk1.6.9\bin
then classpath as e:mysql-connector-java-5.1.8-bin.jar
java file get compiled but at the time of running it showing could not found main class error

sudeep
Guest
sudeep

its not working with mysql-connector-java-5.1.20-bin.jar

Any solutions

prem
Guest
prem

i need to compile jdbc programe via mysql. plz tell me the full procedure .

prem
Guest
prem

i download mysql-connector-java-5.1.20-bin.jar file and what should do next steps .
may i need to give path some where

Madhvi
Guest
Madhvi

i made one directory name hey in that i paste java file n that connecter jar while compile is sucess but as i run this prog i got error is where is ur driver what i do to solve this error can you help me for that…..

Raul
Guest
Raul

Hi MkYong
I just wonder, what should I change from your code here,

connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/mkyongcom”,”root”, “password”);

?
especially in this line, jdbc:mysql://localhost:3306/mkyongcom, since I do not have your directory in my computer (obviously).

thanks.

ThePCWizard
Guest
ThePCWizard

thanks. nice Tutorial!!
BTW, If you use Netbeans IDE it already comes with a MySQL JDBC Driver.
So you can just Right Click your project » Properties » Libraries » Add Library » MySql JDBC Driver.

eastwood
Guest
eastwood

DataConnectionpackage.DataConnection at localhost:50089
Thread [main] (Suspended (exception ClassNotFoundException))
ClassLoader.findBootstrapClass(String) line: not available [native method]
Launcher$ExtClassLoader(ClassLoader).findBootstrapClass0(String) line: not available
Launcher$ExtClassLoader(ClassLoader).loadClass(String, boolean) line: not available
Launcher$AppClassLoader(ClassLoader).loadClass(String, boolean) line: not available
Launcher$AppClassLoader.loadClass(String, boolean) line: not available
Launcher$AppClassLoader(ClassLoader).loadClass(String) line: not available
Launcher$AppClassLoader(ClassLoader).loadClassInternal(String) line: not available

Abhijith
Guest
Abhijith

when am try to connect i get this Exception

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Amogh
Guest
Amogh

aweswomee !! thanxx

amil rohani dar
Guest
amil rohani dar

Aslam o Alakkum
I really found your code snippets very helpful in connecting my Eclipse to Localhost.
Thanks

Ricardo
Guest
Ricardo

Hi mkyong, I would like to say thank you for your tutorial it works great, but I was wondering if you could help me and take this one step further.. $ javac JDBCExample.java && java -cp `pwd`/mysql-connector-java-5.1.22-bin.jar:. JDBCExample ——– MySQL JDBC Connection Testing ———— MySQL JDBC Driver Registered! You made it, take control your database now! What I need your help is I would like to have this working in a executable jar file: jar cvfm JDBCExample.jar Manifest.txt JDBCExample.class mysql-connector-java-5.1.22-bin.jar added manifest adding: JDBCExample.class(in = 1345) (out= 809)(deflated 39%) adding: mysql-connector-java-5.1.22-bin.jar(in = 832960) (out= 797090)(deflated 4%) ricardo@lonss05823:~/Programming/java/mysql> java -jar JDBCExample.jar… Read more »

Prashant
Guest
Prashant

Hi Yong, I have below code. import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException; public class Test { public static void main(String[] argv) { System.out.println("-------- MySQL JDBC Connection Testing ------------"); try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { System.out.println("Where is your MySQL JDBC Driver?"); e.printStackTrace(); return; } System.out.println("MySQL JDBC Driver Registered!"); Connection connection = null; try { connection = DriverManager .getConnection("jdbc:mysql://localhost:3306/hebernate", "root", "password"); } catch (SQLException e) { System.out.println("Connection Failed! Check output console"); e.printStackTrace(); return; } if (connection != null) { System.out.println("You made it, take control your database now!"); } else { System.out.println("Failed to make connection!"); } } } I am getting… Read more »

ashok
Guest
ashok

hi yong! this is my code import java.sql.SQLException; public class JDBCExample { public static void main(String[] argv) { System.out.println("-------- MySQL JDBC Connection Testing ------------"); try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { System.out.println("Where is your MySQL JDBC Driver?"); e.printStackTrace(); return; } } } i am getting exceptions like this.can u help me………… D:\ashok\user&gt;java JDBCExample -------- MySQL JDBC Connection Testing ------------ Where is your MySQL JDBC Driver? java.lang.ClassNotFoundException: com.mysql.jdbc.Driver at java.net.URLClassLoader$1.run(Unknown Source) at java.net.URLClassLoader$1.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Unknown Source) at JDBCExample.main(JDBCExample.java:13)

nova
Guest
nova
java -cp \tmp\mysql-connector-java-5.1.22-bin.jar:\tmp JDBCExample

i’m using java 1.7.0_03
the result is :

"Error: Could not find or load main class JDBCExample"

what could happen?

venkat
Guest
venkat

hi yong,I want jdbc with xml programs..

Shivangi Nigam
Guest
Shivangi Nigam

Nice example great help just a suggestion mention the jar used in the start of the example because after wards it gets confusing

sayed Ahmad Naweed
Guest
sayed Ahmad Naweed

very nice topic, I appreciated thanks.

Keyur Makwana
Guest
Keyur Makwana

Its really very useful. Thank you so much.

Ankit
Guest
Ankit

Hi mkyong thanks, work fine in first attempt…