How to connect MySQL database from Java program with Example

When you start learning JDBC in Java, first program you want to execute is connect to database from Java and get some result back by executing some SELECT queries. In this Java program we will learn How to connect to MySQL database from Java program and execute query against it. I choose MySQL database because its free and and easy to install and setup. If you are using Netbeans IDE than 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 than you can check 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 e.g. 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 we have also used PreparedStatement for connection because it’s one of the JDBC best practice to use PreparedStatement for better performance and avoiding SQL Injection.

Java program to connect MySQL database to execute query

Here is complete Java program to connect MySQL database running into localhost and executing queries against that. This example connects to test database of MySQL server, running on local host at port 3306. At ground level we need a JDBC connection object to communicate with MySQL database, a Statement object to execute query and a ResultSet object to get result from database. By the way you can also use Rowset object and difference between RowSet and ResultSet is one of the frequently asked JDBC Interview question. 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 framework like Spring, you can use JdbcTemplate to avoid these boilerplate coding. It’s also good to setup your table and data before writing Java program. We will be using 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)

And this is our Java program to connect MySQL database and you need to add mysql-connector-java-5.1.17-bin.jar into 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 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 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 result 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