How to connect to MySQL database using Java

This is the beginning of my J2EE series. In this series I just do only coding with simple notes. In JavaSE section I have completely done more than 70% of OCPJP 1Z0-851 syllabus with examples and complete theories. But in J2EE, I just want to show how to do coding practically with examples explaining them simply.  

Set up your machine

In this section I use NetBeans IDE (version 7.4 or you can use 8.0 also) and MySQL 5.6 community version as my database partner.

• NetBeans: https://netbeans.org/downloads/
• MySQL:  http://www.mysql.com/downloads/ 

In the main menu of this blog, there is a tab called MySQL and you will able to see how to install MySQL correctly using those steps. Here is the link.
http://easyjavase.blogspot.com/2014/03/how-to-install-mysql-server-in-your.html#.UzFYAfmSxio


In this example I use the database called j2ee and within this database I created a table called student. There is a post to familiar with MySQL and it shows how to do this stuff. Here is the link.
http://easyjavase.blogspot.com/2014/03/familiar-with-mysql.html#.UzGYS_mSxiq 

How to connect to MySQL database and obtain data from it.

package database;
import java.sql.*;

public class ConnectDb {
    public static void main(String args[]){
        
        try{
            
            String str = "SELECT * FROM student";
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/j2ee","root","1234");
            
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(str);
            
            System.out.println("ID\tName\tAge\n");
            
            while(rs.next()){
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                
                if(id.length()<=3){
                    System.out.print(id + "\t");
                }
                else{
                    System.out.print(id + "\t");
                }
                
                if(name.length()<=3){
                    System.out.print(name + "\t");
                }
                else{
                    System.out.print(name + "\t");
                }
                
                if(age.length()<=3){
                    System.out.print(age + "\t");
                }
                else{
                    System.out.print(age + "\t");
                }
                
                System.out.println("");
            }
            con.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}

That is my simple code to connect to MySQL database and obtain data from it. I think it is not difficult to create a project using NetBeans. 

• In this program I am using MySQL JDBC Driver. So you have to add this library to your project folder.

• Expand you project(In above picture it is MyStuff).
• Right click on Libraries and then click Add Library.
• Then you have to choose the MySQL JDBC Driver from popped up menu.

Import java.sql.*;

• To work with SQL, you have to import sql package to your project.

Try - catch block

• In these type of programs that uses SQL must be handled exceptions using try-catch block or throw an exception.
• Without handling you cannot execute your program.
• In above program I have used try-catch block. If you want you can throws an exception like this.

package Database;

import java.sql.*;

public class ConnectDb{
    public static void main(String[] args) throws SQLException {
        
    }

SQL command

• This is the SQL command that I am going to execute using Java.

Class.forName()

• This is a static method that is used to load the driver and register it.
• In this example I want to load MySQL JDBC driver. 
• If you want to use JDBC-ODBC driver,

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

• If you are using MS SQL database you have to code it as, 

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

• If you use ORACLE database,

Class.forName("oracle.jdbc.driver.OracleDriver");

DriverManager.getConnection() method

• This is used to establish the connection with the database.
• DriverManager class provide the getConnection() method.
• In this method you can see 3 parameters.
• First one is for URL, second one is for User Name of the database and third one is for Password of the database. 

createStatement() method

• Statement object is used to send requests and also retrieve data from a database. 
• This createStatement() method creates a Statement objects.
• In this program we want to execute SELECT command of MySQL.
• We are not going to INSERT or UPDATE. If it is about INESRT or UPDATE then we have to use preparedStatement() method.
• This method create Connection object using Statement interface.

executeQuary() method

• This method returns a ResultSet object.
• This method is used to get a ResultSet. It means the result is stored in a ResultSet object.

I have used while loop to display data. It is about core Java concepts. I am not going to explain it here. After this coding you can see I have close the connection using close() method.