Steps to write JDBC Program

There are 6 steps to connect any java application with the database using JDBC. They are as follows:

1.Load the JDBC driver class or register the JDBC driver.
2.Establish the connection
3.Create a statement
4.Execute the sql commands on database and get the result
5.Print the result
6.Close the connection

1. Register the driver class
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
2.Establish the connection
String url="jdbc:oracle:thin:@localhost:1521:XE";
Connection con = DriverManager.getConnection(url,"System","Sateesh");
3.Create a statement
 Statement stmt = con.createStatement();
4.Execute the sql commands on database and get the result
Prepare Sql Query And Execute Query
•	executeUpdate(): Used for non-select operations.
•	executequery(): Used for select operation.
•	execute(): Used for both select or non-select operation.


5.Print the result
System.out.println("Connection Success");
 6.Close the connection
stmt.close();
con.close();

How To Connect The Jdbc

                                
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcConnection {
    public static void main(String[] args) {
     try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","Sateesh");
        Statement stmt = con.createStatement();
        
            System.out.println("Connection Success");
        stmt.close();
        con.close();
        }
        catch(Exception e)
        {
            System.out.println(""+e);
        }
   }
    
}

Create Table Using Jdbc

import java.sql.*;
class Insert {
    public static void main(String[] args) {
        try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","Sateesh");
        Statement stmt = con.createStatement();
        
        String q="create table emp"+
                   "(name varchar(32),"+
                   "address varchar(50),"+
                   "city varchar(50),"+
                   "state varchar(50),"+
                   "country varchar(50))";
        
        stmt.executeUpdate(q);
        stmt.close();
        con.close();
        }
        catch(Exception e)
        {
            System.out.println(""+e);
        }
       
    }
    
}

Insert Data Into The Table Using Jdbc

import java.sql.*;
import java.util.Scanner;                              
class Insert {
    public static void main(String[] args) {
         try
        {
            Scanner s=new Scanner(System.in);
            String name,addr,city,state,country;
            System.out.println("ENter Your name");
            name=s.nextLine();
            System.out.println("Enter address");
            addr=s.nextLine();
            System.out.println("Enter city");
            city=s.nextLine();
            System.out.println("Enter State");
            state=s.nextLine();
            System.out.println("Enter country ");
            country=s.nextLine();
            
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","Sateesh");
        Statement stmt = con.createStatement();
        
        stmt.executeUpdate( "INSERT INTO emp " +
                   "VALUES ('"+name+"', '"+addr+"', '"+city+"','"+state+"','"+country+"')");
            System.out.println("inserton success");
        
        stmt.close();
        con.close();
        
        }
         
         catch(Exception e)
         {
             System.out.println(""+e);
         }
    }
  
}

View The Data From The table Using Jdbc

import java.sql.*;
public class View {
    
    public static void main(String[] args) {
        try
        {
         DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"usa","usa");
        Statement stmt = con.createStatement();
        
        String s="select * from emp";
         
        ResultSet rs=stmt.executeQuery(s);
       
  
        while(rs.next())    
        {
            System.out.print(rs.getString(1));
            System.out.print(rs.getString(2));
            System.out.print(rs.getString(3));
            System.out.print(rs.getString(4));
            System.out.print(rs.getString(5));
            System.out.println(".........................................");
        }
        
        stmt.close();
        con.close();
        }
        catch(Exception e)
        {
            System.out.println(""+e);
        }
        
    }
    
}

Delete The Data From The Table Using Jdbc


import java.sql.*;
class Delete {
    public static void main(String args[]) throws Exception{
    try
    {
     DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    //Class.forName("Oracle.jdbc.driver.OracleDriver");
    String srt="jdbc:oracle:thin:@localhost:1521:XE";
	Connection con=DriverManager.getConnection(srt,"System","Sateesh");
    Statement stmt = con.createStatement();
    String sql="delete from emp where name='sateesh'";
    int x=stmt.executeUpdate(sql);
    if (x==1)
     {
     System.out.println("Row Deleted Successfully");
     }
    else
     {
      System.out.println("row Delation Failed");    
     }
    }
     catch(Exception e){
     System.out.println(e);
     }
    con.close(); 
    
  }
    
}


Update The Data From The Table Using JDBC

import java.sql.*;
class Update {
    public static void main(String args[]) throws Exception{
    try
    {
     DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    //Class.forName("Oracle.jdbc.driver.OracleDriver");
    String srt="jdbc:oracle:thin:@localhost:1521:XE";
	Connection con=DriverManager.getConnection(srt,"system","sateesh");
    Statement stmt = con.createStatement();
    String sql="update emp set name='Varshini' where name='sateesh'";
    int x=stmt.executeUpdate(sql);
    if (x==1)
     {
     System.out.println("Row updated Successfully");
     }
    else
     {
      System.out.println("row updation Failed");    
     }
    }
     catch(Exception e){
     System.out.println(e);
     }
    con.close(); 
    
  }
    
}

ResultSet MeteData

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
class resultsetmetadaa {
    public static void main(String[] args) {
        
         try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","Sateesh");
        Statement stmt = con.createStatement();
        ResultSet rs=stmt.executeQuery("select * from emp ");
        ResultSetMetaData rsmd=rs.getMetaData();
            System.out.println(rsmd.getColumnCount());
            System.out.println(rsmd.getColumnName(2));
            System.out.println(rsmd.getColumnTypeName(2));
            System.out.println(rsmd.getColumnDisplaySize(2));
		stmt.close();
		con.close();
        }
        catch(Exception e)
        {
            System.out.println(""+e);
        }
  }
}
    

DataBase MetaData

import java.sql.*;
public class databssemetadata {
    public static void main(String[] args) {
        try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","SAteesh");
        Statement stmt = con.createStatement();
        DatabaseMetaData dbmd=con.getMetaData();
        System.out.println("database server name:"+dbmd.getDatabaseProductName());
		System.out.println("database server version:"+dbmd.getDatabaseProductVersion());
		System.out.println("driver server version:"+dbmd.getDriverVersion());
		System.out.println("driver server name:"+dbmd.getDriverName());
		System.out.println("max columns:"+dbmd.getMaxColumnsInTable());
		stmt.close();
		con.close();
        }
        catch(Exception e)
        {
            System.out.println(""+e);
        }
    }
    
}

Insert Image File Into DataBase Using Jdbc

                                
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class image {
    public static void main(String[] args) {
         try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","sateesh");
        PreparedStatement pstmt=con.prepareStatement("insert into stu10 values(?,?,?) ");
        Scanner s=new Scanner(System.in);
        
        
        System.out.println("enter emp id");
    	int empid=s.nextInt();
		pstmt.setInt(1,empid);
		System.out.println("enter emp name");
		String empname=s.next();
		pstmt.setString(2,empname);
		System.out.println("enter photo file path");
		String path=s.next();

		File f=new File(path);
		int size=(int) f.length();

		FileInputStream fis=new FileInputStream(f);

		pstmt.setBinaryStream(3,fis,size);
		int i=pstmt.executeUpdate();
		System.out.println(i+"row inserted");
		pstmt.close();
		fis.close();
		con.close();
        }
         catch(Exception e)
         {
            System.out.println(""+e);
         }
    }
    
}


Retrive Image File From DataBase Using Jdbc


import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
class retrieveimg {
   public static void main(String[] args) {
      try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"usa","usa");
        
        Scanner s=new Scanner(System.in);
        PreparedStatement pstmt=con.prepareStatement("select img from stu10 where id=?");
		System.out.println("enter emp id");
		int empid=s.nextInt();
		pstmt.setInt(1,empid);
		ResultSet rs=pstmt.executeQuery();
		rs.next();
		InputStream is=rs.getBinaryStream(1);
		rs.close();
        
		FileOutputStream fos=new FileOutputStream("C:\\Users\\Priya\\Desktop\\jdbc\\msktutorials.jpg");
		int k;
		while((k=is.read())!=-1)
		{
		fos.write(k);
		}
		System.out.println("picture is ready open c:drive");
		pstmt.close();
			fos.close();
        }
        catch(Exception e)
        {
            System.out.println(""+e);
        }
    
}
}

Check The SavePoint Using JDBC


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.Statement;

class checksavepoint {
    public static void main(String[] args) { 
        try
        {

         DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
         String url="jdbc:oracle:thin:@localhost:1521:XE";
         Connection con = DriverManager.getConnection(url,"System","sateesh");
         Statement  stmt=con.createStatement();
        
		 DatabaseMetaData dbmd=con.getMetaData();
		 if(dbmd.supportsSavepoints())
		 {
		    System.out.println("This driver support SavePoint");
		 }
		 else
		 {
  			System.out.println("This driver does not support SavePoint");
		 }
        
       }
        catch(Exception e)
        {
            System.out.println(""+e);
        }
    }
    
    
}

Call Back To SavePoint


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.Statement;

class callbacksavepoint {
    public static void main(String[] args) {
        Statement stmt=null;
        try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","Sateesh");
        stmt=con.createStatement();
        con.setAutoCommit(false);
  		DatabaseMetaData dbmd=con.getMetaData();
		if(dbmd.supportsSavepoints())
		{
		//savepoint is supported
		try
		{
		int i1=stmt.executeUpdate("insert into ssss values(8888,'johon')");
		Savepoint point3=con.setSavepoint("spoint");
   		System.out.println("SavePoint created");
		try
		{
		int i2=stmt.executeUpdate("delete from ssss where id=77777");
    	System.out.println("Data Deleted");
  		con.rollback(point3);
    	//System.out.println("Data roll back");
   
		}
		catch (Exception e1)
		{
		try
		{
		con.rollback(point3);
		}
		catch (Exception ee)
		{
    	System.out.println(""+ee);
		}
		}
		con.commit();
		System.out.println("this driver is supported successfully");
		}//end of outer try
		catch ( Exception e2)
		{
		try
		{
		con.rollback();
		}
		catch ( Exception eee)
		{
    	System.out.println(""+eee);
		}

		}//end of catch
		}//end of if
		else
		{
		System.out.println("this driver dosen't support savepoints");
		}//end of else
		stmt.close();
		con.close();

        }
        catch(Exception e)
        {
            System.out.println(""+e);
        }
        
    }
    
}

Insert Program With Using Prepared Statements

import java.sql.*;
import java.util.Scanner;
import javax.sql.*;

class pre {
    public static void main(String[] args) {
         try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","Sateesh");
        PreparedStatement pstmt=con.prepareStatement("insert into stu values(?,?,?) ");
        Scanner s=new Scanner(System.in);
		String Choice="yes";
		while(Choice.equals("yes"))
		{
		System.out.println("Enter Student ID");
		int sid=s.nextInt();
		System.out.println("Enter Student Name");
		String sname=s.next();
		System.out.println("Enter Student Marks");
		int marks=s.nextInt();

		//setting the values
		pstmt.setInt(1,sid);
		pstmt.setString(2,sname);
		pstmt.setInt(3,marks);
		int i=pstmt.executeUpdate();
		System.out.println(i+"Row inserted");
		System.out.println("do you want to inset another row(Yes/no)");
		Choice=s.next();
		}//end while
		pstmt.close();
		con.close();
        }
        catch(Exception e)
        {
             System.out.println(""+e);
        }
    }
    
}

Insert Program WithOut Using Prepared Statements


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Scanner;

class nppre {
    public static void main(String[] args) {
       int sid=0,marks=0;
       String sname="";
       try
        {

		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","Sateesh");
        Scanner s=new Scanner(System.in);
 		String Choice="yes";
		while(Choice.equals("yes"))
		{
		System.out.println("enter student id");
		sid=s.nextInt();
		System.out.println("enter student name");
 		sname=s.next();
		System.out.println("enter Student marks");
		marks=s.nextInt();
		System.out.println("do you want to inset another row(Yes/no)");
		Choice=s.next();    
   		Statement stmt = con.createStatement();
     	stmt.executeUpdate( "INSERT INTO stu " +
                   "VALUES ('"+sid+"', '"+sname+"', '"+marks+"')");
        System.out.println("inserton success");
     
		}
		}
       
         catch(Exception e)
         {
             System.out.println(""+e);
         }
    }
    
}

 

Callablestatement in Jdbc(Using Function)

 Let's create the simple function in the database first.   
 Step1                        
 
create or replace function Addition
(n1 in number,n2 in number) 
return number 
is  
temp number(8);  
begin  
temp :=n1+n2;
return temp;
end; 
/               
 Step2    
              
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
class fun {
    public static void main(String[] args) {
        try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"System","Sateesh");
      
        CallableStatement stmt=con.prepareCall("{?= call Addtion(?,?)}"); 
        stmt.setInt(2,10);  
		stmt.setInt(3,113);  
		stmt.registerOutParameter(1,Types.INTEGER); 
		stmt.execute();  
		System.out.println(stmt.getInt(1));  
        
    	}
    	catch(Exception e)
    	{
        	System.out.println(""+e);
    	}
    
	}
    
}

                                
                                

Callablestatement in Jdbc(Using Procedure)

 Let's create the simple Table in the database first.   
Step1      
create table Emp(id number(10), name varchar2(200),location varchar(200));  
 Step2    
  Let's create the simple Procedure in the database first.                  
 
create or replace procedure  Insert_value 
(id IN NUMBER,  
name IN VARCHAR2,
location IN VARCHAR2
)  
is  
begin  
insert into emp values(id,name,loction);  
end;  
/               
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

class CALLABLE {
    public static void main(String[] args) {
        try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        Connection con = DriverManager.getConnection(url,"usa","usa");
        CallableStatement stmt=con.prepareCall("{call Insert_value(?,?,?)}");  
		
        stmt.setInt(1,1011);  
		stmt.setString(2,"Amit"); 
		stmt.setString(3,"chennai");  
		stmt.execute();  
        
    	}
    	catch(Exception e)
    	{
        	System.out.println(""+e);
   		}
    
	}
}

 
 
 
                 

Batch Processing in Jdbc

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

class Batch_Processing {
    public static void main(String[] args) {
    	 Statement stmt=null;
         Connection con=null;
        try
        {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        String url="jdbc:oracle:thin:@localhost:1521:XE";
        con = DriverManager.getConnection(url,"usa","usa");
        stmt=con.createStatement();

        //create batch
		stmt.addBatch("insert into student values(901,'ABC',788)");
		stmt.addBatch("update emp set esal=8888 where eno=102");
		stmt.addBatch("delete from customers where custid=11");
        //disabl auto-commit mode
		con.setAutoCommit(false);
		try
		{
		int i[]=stmt.executeBatch();
 		con.commit();
		System.out.println("batch is successfully executed");
		}
		catch (Exception e)
		{
		try
		{
			con.rollback();
			System.out.println("batch is failed");
			System.out.println("Exception is"+e);
			 stmt.close();
		     con.close();
		}
		catch (Exception e1)
		{
        	System.out.println(e1);
		}
		} 
        
    	}
    	catch(Exception e)
		{
        	System.out.println(""+e);
   		}
       
    
	}
}