Connect to a database in Java

Goal

In my last post I showed how to change the default password of mysql in wamp. In this post I will show how can we connect to a database(mysql is this case) using Java. You must have mysql database installed for this.

Setup

Prior to start coding lets see what we need to complete this goal. We have mysql installed.

1. Create a database called testDB . We will connect to this DB.
2. Use that DB.
3. Create a table name employee with columns as name, sex and age.
4. Use describe command to verify the table details.
5. Use slelect query on this newly formed table. No data is displayed.
6. Add two enteries.
7. Use select query again to verify data is added.

Getting Started

Lets start with writing code now. 

1. Create a project name DBDemo.
2. In that create a java class file named DBConnectionDemo.

In that write the following source code.

package com.opensourceforgeeks;

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

public class DBConnectionDemo {
    
    public final static String query = "select * from employee";
    
    
    public static void main(String args[]) {
        
        String connectionURL = "jdbc:mysql://localhost:3306/testDB";
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            System.out.println("Driver not found. Provide the driver jar in the class path.");
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(connectionURL, "root", "thakur");
            Statement stmnt = connection.createStatement();
            ResultSet resultSet = stmnt.executeQuery(query);
            while(resultSet.next()){
                System.out.println("Name : " + resultSet.getString(1) + " Sex : " + resultSet.getString(2) + " Age : " + resultSet.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        
    }
}

Expected output at this point :

Driver not found. Provide the driver jar in the class path.
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 com.opensourceforgeeks.DBConnectionDemo.main(DBConnectionDemo.java:19)
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/testDB
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at com.opensourceforgeeks.DBConnectionDemo.main(DBConnectionDemo.java:25)

This is expected as we have not added the required driver jar file yet. So add the jar to your classpath.

1. Right click the project and selectproperties.
2. Go to java build path -> libraries
3. Select add external library and add the mysql connector jar(download)

Now rerun the program. You should get the correct output.

Name : Aniket Sex : Male Age : 23
Name : Abhijit Sex : Male Age : 21

NOTE :  If you are using java 7 like me the there is no need to even load you driver class using Class.forName() because java7 comes with JDBC 4 and for JDBC 4 appropriate driver class will automatically be picked up from the class path. So all you have to do is add the jar in the class path.

Related Links

• Changing default mysql password in WAMP server.