JDBC - How to connect MySQL database from Java program with Example

When you start learning JDBC in Java, the first program you want to execute is connected to a database from Java and get some results back by executing some SELECT queries. In this Java program, we will learn How to connect to the MySQL database from the Java program and execute a query against it. I choose the MySQL database because it's free and easy to install and set up. If you are using Netbeans IDE then you can connect MySQL Server directly from Netbeans, Which means in one window you could be writing Java code, and other you can write SQL queries.

Another advantage of using MySQL database is that it provides type 4 JDBC driver bundled in mysql-connector-java-5.1.17-bin.jar which is easy to use. By the way, if you are using Oracle database then you can check the Java program to connect Oracle database, to connect and run SQL queries against Oracle DB.

This Java tutorial also explains some of the common error which comes while working in JDBC code like during connection or reading results. In order to connect to MySQL database, you need three things database URL, username, and password and we are using default user root here, which is created during MySQL installation.

By the way, you can also use PreparedStatement for connection because it’s one of the JDBC best practices to use PreparedStatement for better performance and avoiding SQL Injection.

Java Program to Connect MySQL Database to Execute Query

Here is a complete Java program to connect MySQL database running on localhost and executing queries against that. This example connects to the test database of the MySQL server, running on the local host at port 3306. 

At ground level, we need a JDBC connection object to communicate with the MySQL database, a Statement object to execute the query, and a ResultSet object to get results from the database. By the way, you can also use the Rowset object, and difference between RowSet and ResultSet is one of the frequently asked JDBC Interview questions.

One of the thing which I don’t like about JDBC is lots of boiler plate code e.g. closing connection, statement and result set, and other resources in finally block. Once you move ahead and start using frameworks like Spring, you can use JdbcTemplate to avoid this boilerplate coding. It’s also good to set up your table and data before writing a Java program. We will be using the following table for query:

mysql> select * from stock;
+---------+-------------------------+--------------------+
| RIC     | COMPANY                 | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T  | Sony                    | T                  |
| GOOG.O  | Google Inc              | O                  |
| GS.N    | Goldman Sachs Group Inc | N                  |
| INDIGO  | INDIGO Airlines         | NULL               |
| INFY.BO | InfoSys                 | BO                 |
| VOD.L   | Vodafone Group PLC      | L                  |
+---------+-------------------------+--------------------+
6 rows in set (0.00 sec)

Java Program to Connect MySQL Database 

And this is our Java program to connect MySQL database and you need to add mysql-connector-java-5.1.17-bin.jar into the classpath, which contains JDBC type 4 driver required to connect MySQL database. If you don’t include this JAR in your classpath, you will get the following error java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * Java program to connect to MySQL Server database running on localhost,
 * using JDBC type 4 driver.
 *
 * @author http://java67.blogspot.com
 */
public class MySQLTest{

    public static void main(String args[]) {
        String dbURL = "jdbc:mysql://localhost:3306/test";
        String username ="root";
        String password = "root";
       
        Connection dbCon = null;
        Statement stmt = null;
        ResultSet rs = null;
       
        String query ="select count(*) from stock";
       
        try {
            //getting database connection to MySQL server
            dbCon = DriverManager.getConnection(dbURL, username, password);
           
            //getting PreparedStatment to execute query
            stmt = dbCon.prepareStatement(query);
           
            //Resultset returned by query
            rs = stmt.executeQuery(query);
           
            while(rs.next()){
             int count = rs.getInt(1);
             System.out.println("count of stock : " + count);
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(CollectionTest.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
           //close connection ,stmt and resultset here
        }
       
    }  
   
}

Output:
count of stock : 6

ResultSet is used to retrieve query result If you don't call rs.next() and directly call rs.getInt(columnName) and getIndex(), you will get following error, so always call rs.next() before calling any getXXX() method.

java.sql.SQLException: Before start of result set
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
        at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
        at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2674)

That’s all on How to connect to MySQL database from the Java program. We have seen step-by-step details to connect MySQL 5.5. database and executed SELECT query against it. We have also touched base on some of the common SQLException which comes in JDBC code during connection or reading results via ResultSet.

Related JDBC Tutorials you may like

• 5 Tips to improve performance of JDBC Java application
• Difference between Time, TimeStamp and Date in Java
• Difference between java.util.Date and java.sql.Date in Java
• How to convert java.util.Date to java.sql.Date in Java
• How to fix java.sql.SQLException: Invalid column index