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.






    Tuesday, March 25, 2014

    Familiar with MySQL


    How to see existing databases in MySQL server


    SHOW databases;
    
    


    • You can type this and then click where I circled in red colour.
    • now you see all the databases which are created in your MySQL server.






    How to create a database



    CREATE DATABASE blog;
    
    


    • This code will create a database called blog.
    • Once you create a database it will be not shown in the left side section. You have to refresh on this to see the updates.
    • Then you can see the database that you have created.



    How to use a database


    USE blog;
    
    

    • There are few databases. So you have to select a database to create tables.

    How to create a table


    USE blog;
    
    CREATE TABLE student 
    ( 
     id int,
     name varchar(20),
     age int
    );
    
    

    • You have to go through the syntax and enter the column name and its data type as follows.
    • If your syntax is correct, you can see the message in OUTPUT with green ticks.
    • You need not to mention the size for "int" data type. But if you using "varchar", you have to mention the size within brackets.  



    Insert data to the table


    INSERT INTO student (id,name,age)
    VALUES(1,'John',22),(2,'Ann',18),(3,'Jesmi',26),(4,'Annie',24);
    
    
    





    How to see the updates


    SELECT * FROM student
    
    




    These are the very simple SQL queries that I'm gonna use in this series. There are many more things more than this. It is better you can learn the basic commands of MySQL.


    You need to understand about, DESCRIBE, DROP, LIKE, DISTINCT, ORDER BY, WHERE... etc.





    How to install MySQL server in your machine



    In my Java EE series I use MySQL server as my database. You have to install it to work with JDBC in Java EE. This is very simple introduction to MySQL that shows how to use MySQL server for your Java applications. It is very easy to install MySQL server in your machine. Here you can see how to install MySQL community server in your machine.


    How to install MySQL server.




    You have to choose Install MySQL Products


    If you want you can go with updates

    I have chosen full install, you can customize your selections using Custom. 

    Click on execute



    Products are installing.


    MySQL sever has successfully installed. Then it need to configure.

    It is better to use as default settings. Or else you can change the port number and other settings.

    You have to enter a password, my one is weak. But it is easy to use.

    Set it as default, don't change.

    MySQL server is configuring.

    Samples and examples are configuring. 


    Installation complete. 

    This is the very first interface of MySQL workbench. Double click on "Local instance MySQL56".

    Then it asking the password.

    You are totally complete, then you can create your databases.

    You can use either workbench or MySQL command line client. This is same as workbench, but it doesn't provide the friendly GUI. All the commands are same as workbench. I feel this is fast than workbench.








    Saturday, March 1, 2014

    IO Class

    What is IO class ?


    You know that Java provides so many predefined classes to make your code easily. IO class is another special class that helps you to create files and modify them. There are special sub classes within IO class to do these things. In this post I want to talk about following classes. Because they are the main classes that you want to learn for OCPJP 1Z0-851 examination.

    • File
    • FileWriter
    • BufferedWriter
    • FileReader
    • BufferedReader
    • PrintWriter
    • Console

    In OCPJP 1Z0-851 examination, we have to consider only about above things. In additionally I have noted down few things over the syllabus. Keep these thing in your mind and try to do examples to improve your knowledge with them. 
       

    File class


    File class in an abstract representation of file and directory path names. This is not used to write or read data. It gives you information about files. This class is used for making new empty files, searching files, deleting files, making directories and working with paths

    Try this example. This is a sample example for checking a file in a known directory.

    • For example I have created a folder in C directory and created a txt file within it called "myFile.txt". You can create either .txt, .php, .html or whatever file you like. In this demonstration I use .txt file.
    • Then try this code and check your result. If you create that file correctly, you will be able to get "Your file is there" message.

    import java.io.*;
    public class FileTest {
     public static void main(String args[]){
      File f = new File("C:\\Test\\myFile.txt");
      
      if(f.exists()){
       System.out.println("Your file is there");
      }
      else{
       System.out.println("Your file is not there");
      }
     }
    }
    
    
    

    • In this example I have imported java.io class. 
    • It is necessary to add it. Because File class is a sub class of IO class. So you have import IO class manually. 
    • Then in line four, I have created File object, its reference variable is "f".
    • In this line you can see I have used "\\" to set path instead of using "\". This is necessary in windows.
    • Next line I checked existence, using exist( ) function.
    • This is very simple example shows you the importance of File class.


    How to create a file ?


    Now we know about file class. Before we go to other things I thought to show you how to create files in Java. There are many ways to do it.

    Using createNewFile( ) function



    import java.io.*;
    public class CreateFile {
     public static void main(String args[]){
      
      try{
       File f = new File("C:\\Test\\file1.txt");
       System.out.println(f.exists());
       
       f.createNewFile();
       System.out.println(f.exists());
      }
      catch(Exception e){
       System.out.println("There is an error.");
      }
     }
    }
    
    
    

    • In this example also, first I have created file object and gave the path of this file.
    • Then I have used createNewFile( ) function to create a new file in that given destination.
    • At the very first time you will be able to see the output as false and then true. If you run it again it will give true and true as a output. 
    • You can understand what is the reason for that. Very first time there is no file in that destination. But second time there is a file in that destination.

    This createNewFile( ) method is Boolean type method. Using this method you cannot create files in same file name. In above example it creates file1.txt file, if it does not already exist. You can run it again and again, but it will not create more file1.txt files, just only one.

    Using formatter( ) method



    import java.util.*;
    
    public class CreateFile2 {
     public static void main(String args[]){
      
      try{
       new Formatter("C:\\Test\\FormatterFile.txt");
       
       System.out.println("You have created a file");
      }
      catch(Exception e){
       System.out.println("There is an error.");
      }
     }
    }
    
    

    • This is another method of creating files.
    • In this method we use Formatter( ) class to create files.
    • Special thing is there, you can see I have not imported IO package, instead of that I have imported util package. Because Formatter class belongs to java.util package.
    • This method is not thread safe for multithreaded access. You need not to worry about this method in your examination and pay your attention for the first method to create files. In later sessions we discuss about Threads. 

    Using FileWriter( ) method



    import java.io.*;
    public class CreateFile3 {
     public static void main(String args[]){
      
      try{
       File f = new File("C:\\Test\\Create File 3.txt");
       new FileWriter(f).close();
      }catch(Exception e){
       System.out.println("There is an error");
      }
     }
    }
    
    
    

    • FileWriter( ) method is generally used for writing characters. 
    • But this method also create files.
    • In this program I have used close( ) method. This is used to prevent resource leak. You can run it without using close( ) method. But It is good practice to use close( ) to prevent resource leak. You may learn more about close( ) in later.


    How to write in a file ?


    Now we know how to create files. Then lets try to write something on the file that we have created. There are also few methods to do it.

    Using write( ) method


    import java.io.*;
    public class WriteFile {
     public static void main(String args[]){
      
      try{
       File f = new File("C:\\Test\\write.txt");
       FileWriter fw = new FileWriter(f);
       
       fw.write("This is a test");
       fw.close();
       
      }catch(Exception e){
       System.out.println("There is an error");
      }
     }
    }
    
    
    

    • This is also like previous examples.
    • write( ) method allows you to write characters and string in to your file.
    • close ( ) method is used to prevent resource leak and stop writing to the file. If you try to write something after close( ) method, it will not printed to the file( write.txt ) and there can be an run-time exception. In this program you will be able to see a message "There is an error".  

    Using format( ) method


    import java.util.*;
    
    public class WriteFile2 {
     public static void main(String args[]){
      
      try{
       
       Formatter f = new Formatter("C:\\Test\\WriteFile2.txt");
       f.format("%s%s", "Ravi ", "easyjavase@live.com ");
       f.close();
       
      }catch(Exception e){
       System.out.println("There is an error");
      }
     }
    }
    
    

    • You know how to create file using Formatter( ) class.
    • Then what I did is, using format( ) method to write something to a file you have created.


    How to read a file ?


    Already you know how to create files and how to write in to the files. Then we have to discuss how to read a file.

    Using FileReader( ) method


    • This is a very simple method that can be used to read a file.
    • Following example shows the way, that how can you do it.
    • As a Java programmer we should have to familiar with other operating systems also, specially Ubuntu.
    • In this program I code this program in Ubuntu OS. So I have to use path in different way. In Ubuntu it is easy to fine the path. Just you have to drag and drop the file to the ubuntu terminal. Then you will able to see the path and then you can copy it and paste it in your program.
    • If you are using Windows You can use the path as we used before (C:\\Test\\ReadFile.txt). 



    import java.io.*;
    public class FileReaderTest {
     public static void main(String args[]){
      
      try{
       /*this program is created in ubuntu. If you are with windows, 
         you have to change the file path as above programs.*/
    
       File f = new File("/home/ravi/workspaceUbuntu/IOTest/src/Reader.txt");
       FileReader fr = new FileReader(f);
       
       char a[] = new char[50];
       fr.read(a);
       
       for(char c: a){
        System.out.print(c);
       }
       fr.close();
       
      }catch(Exception e){
       System.out.println("There is an error");
      }
     }
    
    }
    
    
    

    I think post was too long, I hadn't any idea about termination point. In next post I am going to talk about BufferWriter, BufferReader, PrintWriter and differences between FileWriter, FileReader and so on. Hope you enjoy...!!!