Search This Blog


Saturday, February 2, 2013

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 you can also use 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.
How to connect MySQL Server database from Java program using JDBC

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

count of stock : 6

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

java.sql.SQLException: Before start of result set
        at com.mysql.jdbc.SQLError.createSQLException(
        at com.mysql.jdbc.SQLError.createSQLException(
        at com.mysql.jdbc.SQLError.createSQLException(
        at com.mysql.jdbc.SQLError.createSQLException(
        at com.mysql.jdbc.ResultSetImpl.checkRowPos(
        at com.mysql.jdbc.ResultSetImpl.getInt(

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


  1. cannot find symbol
    symbol: method executeQuery(String)

  2. can i connect to a sever using
    String dbURL = "here the ip addrese of the database in a server"; ????

    or only local host?

  3. Can you use it in Object Oriented programming? Saying to set this connection in a seperate class and use it in other classes in other packages?

    1. Even i am seaching fro the same thing how to use the created connection in another package so as to shorten the code

  4. Getting one error
    Cannot find symbol
    Symbol : class Collectiontest

    1. Change the Collectiontest to MySQLTest in this Logger statement :

      Logger.getLogger(CollectionTest.class.getName()).log(Level.SEVERE, null, ex);

      This should fix the problem.

    2. In the catch (SQLException ex) {
      Logger.getLogger(CollectionTest.class.getName()).log(Level.SEVERE, null, ex);
      The Collection is red and gives the error:
      "Cannot resolve symbol 'CollectionTest' "

      So how can I fix that problem??

    3. Do the same as @Anonymous has suggested, that should be the class name which is MySQLTest in this example.

  5. Hello
    I can not make connection between java and mysql