Wednesday, March 26, 2014

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.


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.