Friday, July 23, 2021

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.

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

24 comments:

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

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

    or only local host?

    ReplyDelete
  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?

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

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

    ReplyDelete
    Replies
    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.

      Delete
    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??

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

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

    ReplyDelete
  6. First time m writing database connectivity program..i m getting following errors..could you guys help me out???

    package connectivity;
    import java.sql.*;
    public class Connection {
    static final String JDBC_DRIVER="com.mysql.jdbc.Driver";
    static final String DB_URL="jdbc:mysql://localhost/";
    static final String USER="username";
    static final String PASS="password";
    public static void main(String[] args)
    {
    java.sql.Connection con=null;
    Statement st=null;
    try
    {
    Class.forName(JDBC_DRIVER);
    System.out.println("connecting to a database");
    con=DriverManager.getConnection(DB_URL,USER,PASS);
    System.out.println("Creating a statement");
    st=con.createStatement();
    String sql="CREATE DATABASE STUDENTS";
    st.executeUpdate(sql);
    System.out.print("database created successfully");
    }
    catch(SQLException se)
    {

    se.printStackTrace();
    }
    catch(Exception e)
    {

    e.printStackTrace();
    }
    finally
    {

    try
    {
    if(st!=null)
    {
    st.close();
    }
    }catch(SQLException se2)
    {
    }
    try{
    if(con!=null)
    {
    con.close();
    }
    }
    catch(SQLException se)
    {
    se.printStackTrace();
    }
    }
    System.out.println("Goodbye!");

    }


    }



    #ERROR

    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 connectivity.Connection.main(Connection.java:14)
    Goodbye!

    ReplyDelete
    Replies
    1. Hi Subhash, It seems you don't have mysql connector JAR in your classpath. Please check here to learn more about how to solve this error.

      Delete
  7. Thankyou so much for spending time from your busy life and writing this for us. :)

    ReplyDelete
  8. Hi, I'm getting an exception everytime i run my file, Though there aren't any errors. I was getting the output yesterday, when i opened the code today and tried running it ,its just printing exception.The sql server is running and i can execute queries from the query editor in netbeans.But not from my code , please help


    String dbURL = "jdbc:mysql://localhost:3306/Project1";
    String username = "root";
    String password = "";
    Connection dbCon = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
    Class.forName("com.mysql.jdbc.Driver");
    dbCon = DriverManager.getConnection(dbURL, username, password);
    stmt = dbCon.prepareStatement("SELECT itemname,priceperplate from rest WHERE ItemNo=4");
    rs = stmt.executeQuery("SELECT itemname,priceperplate from rest WHERE ItemNo=4");
    while (rs.next()) {
    String itemname = rs.getString("ItemName");
    String price = rs.getString("priceperplate");
    System.out.println(price);
    System.out.println(itemname);
    }

    } catch (Exception e) {
    System.out.print("Exception");}

    ReplyDelete
  9. Hi, I'm getting an exception everytime i run my file, Though there aren't any errors. I was getting the output yesterday, when i opened the code today and tried running it ,its just printing exception.The sql server is running and i can execute queries from the query editor in netbeans.But not from my code , please help


    String dbURL = "jdbc:mysql://localhost:3306/Project1";
    String username = "root";
    String password = "";
    Connection dbCon = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
    Class.forName("com.mysql.jdbc.Driver");
    dbCon = DriverManager.getConnection(dbURL, username, password);
    stmt = dbCon.prepareStatement("SELECT itemname,priceperplate from rest WHERE ItemNo=4");
    rs = stmt.executeQuery("SELECT itemname,priceperplate from rest WHERE ItemNo=4");
    while (rs.next()) {
    String itemname = rs.getString("ItemName");
    String price = rs.getString("priceperplate");
    System.out.println(price);
    System.out.println(itemname);
    }

    } catch (Exception e) {
    System.out.print("Exception");}

    ReplyDelete
  10. String dbURL = "jdbc:mysql://localhost:3306/Project1";
    String username = "root";
    String password = "";
    Connection dbCon = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
    Class.forName("com.mysql.jdbc.Driver");
    dbCon = DriverManager.getConnection(dbURL, username, password);
    stmt = dbCon.prepareStatement("SELECT itemname,priceperplate from rest WHERE ItemNo=4");
    rs = stmt.executeQuery("SELECT itemname,priceperplate from rest WHERE ItemNo=4");
    while (rs.next()) {
    String itemname = rs.getString("ItemName");
    String price = rs.getString("priceperplate");
    System.out.println(price);
    System.out.println(itemname);
    }

    } catch (Exception e) {
    System.out.print("Exception");
    }





    the code has no errors, it was running till yesterday but now it shows exception , the ysql server is running and i can execute queries from the query editor in netbeans but not from my java code?
    please help me out! Thanks

    ReplyDelete
    Replies
    1. Hello @terminatorjameso , you need to print the exception stack trace to solve the problems. It's not good practice to just print exception in catch block instead you should print the stack trace using exception.printStackTrace() e.g.

      catch (Exception e) {
      e.printStackTrace();
      }

      Delete
  11. Ther was way to add username password.
    First i have connected to localhost database using host as 127.0.0.1. now i am trying to connect to 54.35.xx.xxx Mysql database but i m getting this error.

    ERROR java.sql.SQLException: Access denied for user 'root'@'50.16.35.xxx' (using password: NO)

    In error massage its trying to connect my local machine again because my local machine IP address is 50.16.35.xxx instead of 54.35.xx.xxx

    Can anyone please tell me how this is happening.
    Plz help me : lakhveer.singh51@gmail.com

    ReplyDelete
  12. import java.sql.*;
    import javax.swing.JOptionPane;

    try
    {
    Class.forName("java.sql.Driver");
    Connection con=DriverManager.getConnection("jdbc:mysql://localhost/SJ","root","school");
    Statement st=con.createStatement();
    String cd,n,s,gra,gr,q;
    cd=t1.getText();
    n=t2.getText();
    s=t3.getText();
    gra=t4.getText();
    gr=t5.getText();

    q="insert into employees values("+cd+",'"+n+"','"+s+"','"+gra+"','"+gr+"')";
    st.executeUpdate(q);
    JOptionPane.showMessageDialog(null,"Saved");

    }
    catch(Exception e)
    {
    JOptionPane.showMessageDialog(null, e.toString());
    }

    the following error occured:-
    java.sql,SQLException:no sutable driver found for jdbc:mysql://localhost/SJ

    ReplyDelete
  13. How to create a class and call it in the main method

    ReplyDelete
  14. Hello @Unknown, you don't call a call, you call a method. A class can have method and you need to create instances of class to access that method. Here is an example of a class with a method. I created an instance of that and I called it on main method.

    class MyClass{

    public static void main (String args[]){
    MyClass m = new MyClass();
    m.myMedho();
    }

    public void myMethod(){
    System.out.println("my method");
    }

    }

    ReplyDelete
  15. Hi

    I'm using sakila database in mySql, and I'm getting this error
    Apr 22, 2020 11:15:52 AM MyMain main
    SEVERE: null
    java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/sakila
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at MyMain.main(MyMain.java:31)

    BUILD SUCCESSFUL (total time: 0 seconds)

    ReplyDelete
    Replies
    1. Hello @Unknown, most likely you don't have mysql-jdbc.jar in your classpath. I have written about it also, yoy can check No suitable driver found for jdbc:mysql to find out how to solve this error

      Delete

Feel free to comment, ask questions if you have any doubt.