How to use Mysql Database in your Java applications?

Hello Friends, Today I’m going to describe the step by step process through which we can easily interact with mysql database using our Java Desktop applications or JSP or Struts web pages.

First of all make sure you have already downloaded and installed MySql Server 5.x.
Now its time to do some code in java which will help you to make database queries veryefficent and in an effective way with very less code.

Note: If you have not downloaded my sql server then please visit this link and look for the options provided there for installation and downloads.

Now lets proceed with our Java coding stuffs :

1.  File name : DBCon.java

package lib.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Sudhanshu
 */
public class DBCon {
    private static Connection con;
    private final static String userName=”root”;//default
    private final static String password=”mysql”;//your password (time of installation)
    private final static String host=”localhost”;
    private final static String port=”3306″;//default port number of mysql server
    private final static String dbName=”mydb”;
   //”mydb” is database name which you have to create manually
    private final static String driver=”com.mysql.jdbc.Driver”;
    private final static String url=”jdbc:mysql://”+host+”:”+port+”/”+dbName;
   
    static{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException ex) {
            System.out.println(ex);
        }
      
    }
   
    public static Connection getConnection() throws SQLException{
   
            if((con==null)||(con.isClosed())){
                con=DriverManager.getConnection(url, userName, password);
            }
      
        return con;
    }
}

For using this connection for CRUD operations. We can get the ref. of connection by writing the following code from where we want to execute or fire the query.

For creating a table using Java program:

2. File name : CreateTable.java


package lib.db;

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


/**
 *
 * @author Sudhanshu
 */
public class CreateTable {
    private MysqlTables mts;
    private String gt;
    private Statement stmt;
    private Connection con = null;
    private int result;
    public CreateTable(){
        this.mts=new MysqlTables();
    }
    public int createTable(String tableName){
        if(tableName!=null){
            this.gt=mts.findTable(tableName);
            if(!gt.equals("false")){
                  try{
                        this.con=DBCon.getConnection();//call to static method


                       //ask for connection ref. from the DBCon.java
                        stmt=con.createStatement();
                    }catch(SQLException e){
                     System.out.println(“Error – In createTable() method – DBCon.getConnection() : con.createStatement():\n”+e);
                    }
        
                try{
                this.result=stmt.executeUpdate(gt);
                stmt.close();
                con.close();
                return result;
                }catch(SQLException e){
                    try{
                    System.out.println(“Error – In createTable() method- stmt.executeUpdate(sql):\n”+e);
                    stmt.close();
                    con.close();
                    }catch (SQLException ex) {
                        System.out.println(“Error – In createTable() method- stmt.close() – con.close():\n”+ex);
                    }finally{
                          return 0;
                    }
               }
            }else{
                return 0;
            }
        }
        return 0;
    }
}


3. MySqlTables.java



 /*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package lib.db;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * @author Sudhanshu
 */
public class MysqlTables {
    public String findTable(String tableName){
        Map ar=new HashMap();
       
      ar.put(“contact”,
      “CREATE TABLE IF NOT EXISTS `contact` (“
      +”`id` varchar(32) NOT NULL,”
      +”`fname` varchar(32) NOT NULL,”
      +”`lname` double NOT NULL,”
      +”`address` varchar(32) NOT NULL DEFAULT \’default\’,”
      +”`mobile` varchar(32) NOT NULL DEFAULT 0,”
      +”`email` varchar(32) NOT NULL DEFAULT 0,”
      +”PRIMARY KEY (`id`)”
      +”)”
       );
     
     
      List

> ls = 
              new ArrayList >( 
                 ar.entrySet()); 
  
      for (Map.Entry entry: ls) 
      { 
         if(tableName.equals(entry.getKey())){ 
               return entry.getValue();
         }
      } 
      return “false”;
    }
}
 




4. Main.java



package main;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import lib.db.CreateTable;

/**
 *
 * @author Sudhanshu
 */
public class Main {
    public static void main(String[] args){
         CreateTable ct=new CreateTable();
                   ct.createTable(“contact”);
}
}  


How it works?
1. After downloading the MySql sever and the MySql jdbc connector i.e. the jar file (Visit downloads section of website for download), install the mysql server and leave the port number to default value and put your choice of password there. In this example it is “mysql” and default user of mysql is always root.

2. To login and create users : visit our MySql commands section.

3. Now after completing steps 1 and 2. Write the DBCon.java code in lib.db package.
4. Create another java file CreateTable.java inside lib.db which basically performs the functionality to create tables. When we create object of this class a corresponding object of MysqlTables.java class is also created inside its constructor.
5. Inside lib.db –  MysqlTables.java can be used to put the structure or tables, to be used as tables in the database.
6. Create the Main.java file in some other package named as main.
7. The Main in its domain i.e. public static …{…} or method first creates object of class CreateTable which in turns create object of class MysqlTables in its constructor.
8. The createTable() is called on CreateTable class object and the name of the table is passed.
9. Corresponding query for the table is fetched from the MyqlTables.java and a query is fired using provided set of structures of one table at a time.


Keep Visiting for further updates on this topic!!!