Database Connection, Create and Delete Table, Insert and Fetch values in Java

Hello Friends, 
Today I am going to describe you step by step procedure to get connected with mysql database and perform create table, delete table, insert into table and fetch from table queries.

To do so we will first create a package db and create the all database related java programs inside this package.



Here is the first program:

1. DBCon.java  

//Info : It return the Connection object whenever the method getConnection() of this class is called.

package db;

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

/**
 *
 * @author Sudhanshu
 */
public class DBCon {
    private static Connection con;
    private final static String userName=”root”;
    private final static String password=”mysql”;
    private final static String host=”localhost”;
    private final static String port=”3306″;
    private final static String dbName=”mydatabasename”;
    private final static String driver=”com.mysql.jdbc.Driver”;
    private final static String url=”jdbc:mysql://”+host+”:”+port+”/”+dbName;
   
    static{
        try {
           
            System.out.println(“Driver Loading….”);
            Class.forName(“com.mysql.jdbc.Driver”);
            System.out.println(“Driver Loaded”);
        } catch (ClassNotFoundException ex) {
           System.out.println(ex);
        }
      
    }
     
    public static Connection getConnection() throws SQLException{
   
            if((con==null)||(con.isClosed())){
                System.out.println(“Creating Connection….”);
                con=DriverManager.getConnection(url, userName, password);
                System.out.println(“Connection…. created”);
            }
       System.out.println(“returning obj. of  Connection….”);
        return con;
    }

}
 

2. CreateTable.java

//Info: this java program will create a table with the name user having two columns.

package db;

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

/**
 *
 * @author Sudhanshu
 */
public class CreateTable {
   
    private Statement stmt;
    private Connection con = null;

    private String query=”CREATE TABLE IF NOT EXISTS `user` (“
            + “username varchar(30) primary key NOT NULL, “
            + “password varchar(30) NOT NULL”
            + “)”;
   
   
    public void createTable(){
        try{
    
                   
            this.con=DBCon.getConnection();
            stmt=con.createStatement();
            stmt.executeUpdate(query);
          
          
        }catch(SQLException e){
         System.out.println(“Error in creating table : “+e);
        }
    }
}
 
 3. Insert.java

//Info: this program will insert the values passed by the calling method to the method insert inside this class.

package db;

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

/**
 *
 * @author Sudhanshu
 */
public class Insert {
    private Connection con;
    private Statement stmt;
   
    public Insert(){
        try{
            this.con=DBCon.getConnection();
            this.stmt=con.createStatement();
        }catch(SQLException ex){
            System.out.println(“GET: “+ex);
        }
    }
    public int insert(String tablename, String username, String pswd) throws SQLException{
          
       
            int r=stmt.executeUpdate(“INSERT INTO “+tablename+” VALUES(‘”+username+”‘, ‘”+pswd+”‘)”);
            stmt.close();
            con.close();
           
            return r;
      
    }
   
}
 

4. Query.java
//Info: This program will make query to the database table “user” and will print the values present inside the rows of the table.

package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Sudhanshu
 */
public class Query {
   
    private Connection con;
    private PreparedStatement pstm;
   
   
    public void show() throws SQLException{
        this.con=DBCon.getConnection();
        this.pstm=con.prepareStatement(“SELECT * FROM user”);
       
        ResultSet rset=pstm.executeQuery();

        while(rset.next()){
        
           System.out.print(“Username: “+rset.getString(“username”)+”\t”);
           System.out.print(“Password: “+rset.getString(“password”));
           System.out.println();
         
           }
       }
}
 
5. Delete.java

//Info: This program will drop the table user from the database.

package db;

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

/**
 *
 * @author Sudhanshu
 */
public class Delete {
    private Statement stmt;
    private Connection con = null;
    public void deleteTable(String tablename){
       
         try{  
                this.con=DBCon.getConnection(); 
                stmt=con.createStatement();
                stmt.executeUpdate(“DROP TABLE IF EXISTS “+tablename);
                stmt.close();
                con.close();
            }catch(SQLException e){
                System.out.println(“Error – In deleteTable() method – DBCon.getConnection() : con.createStatement():\n”+e);
            }            
       
    }
}


MAIN PROGRAM:

6. Main.java

/**
Info: When the main method gets called by the runtime environment a series of database operations will be performed:
1. Table will be created
2. Values will be inserted
3. Output will be shown
4. Table will be deleted from the database
*/


package main;

import db.CreateTable;
import db.Delete;
import db.Insert;
import db.Query;
import java.sql.SQLException;

/**
 *
 * @author Sudhanshu
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {

      

Insert obj=new Insert();
        try {
            obj.insert(“user”, “user1”, “user1”);
        } catch (SQLException ex) {
            System.out.println(“Exception Caused :” +ex);
        }
     
     

        Query q=new Query();
        try {
            q.show();
        } catch (SQLException ex) {
            System.out.println(“Exception Caused :” +ex);
        }
      
         
       
          Delete d=new Delete();
          d.deleteTable(“user”);
     
       
    }
}

Note:
1. Create two packages 1st with the name db and 2nd with the name main.
2.  From 1-5, put all the programs inside the db package and the last program in the main package.
3. Compile the programs respectively by visiting the package locations using cmd.
4. Execute the program and you are done.


*Make sure your have properly installed mysql database server and have already created a database there.


Best of Luck!

%d bloggers like this: