• iconJava Online Training In Andhra Pradesh and Telangana
  • icon9010519704

Opening Hours :7AM to 9PM

Java Database Connectivity

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

Oracle:
String url="jdbc:oracle:thin:@localhost:1521:XE";
Connection con = DriverManager.getConnection(url,"System","Sateesh");
Mysql:
Class.forName("com.mysql.jdbc.Driver").newInstance();
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");


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();


Key Points

Image
Database Connectivity Program

How To Connect The Jdbc Using Oracle Database

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);
}
}
}

Output:
Connection Success
How To Connect The Jdbc Using Oracle Database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcConnection {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
Statement stmt = con.createStatement();
System.out.println("Connection Success");
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Connection Success
Create Table Using Jdbc

How To Create Table Using Oracle Database

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();

        String q="create table emp"+
                   "(name varchar(32),"+
                   "address varchar(50),"+
                   "city varchar(50),"+
                   "state varchar(50),"+
                   "country varchar(50))";
stmt.executeUpdate(q);
System.out.println("Table Creation Success");
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Table Creation Success
How To Create Table Using MySql Database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcConnection {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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);
System.out.println("Table Creation Success");
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Table Creation Success
Insert Data into the table Using Jdbc

How To Insert data into the Table Using Oracle Database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;
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();
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();

String s= "INSERT INTO emp " +
        "VALUES ('"+name+"', '"+addr+"', '"+city+"','"+state+"','"+country+"')"
stmt.executeUpdate(s);

System.out.println("Data Inserted successfully");
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Data Inserted successfully
How To Insert data into the Table Using MySql Database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;
public class JdbcConnection {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
Statement stmt = con.createStatement();
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();

String s= "INSERT INTO emp " +
        "VALUES ('"+name+"', '"+addr+"', '"+city+"','"+state+"','"+country+"')"
stmt.executeUpdate(s);

System.out.println("Data Inserted successfully");
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Data Inserted successfully
Retrieve the Data from the Table Using Jdbc

How To Retrieve data from the Table Using Oracle Database

import java.sql.*;
public class Main {
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 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);
}
}
}

Output:
How To Retrieve data from the Table Using MySql Database

import java.sql.*;
public class Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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);
}
}
}

Output:
Delete The Data From The Table Using Jdbc

How To Delete data from the Table Using Oracle Database

import java.sql.*;
public class Main {
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 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");
}
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Row Deleted Successfully
How To Delete data from the Table Using MySql Database

import java.sql.*;
public class Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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");
}
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Row Deleted Successfully
Update The Data From The Table Using Jdbc

How To Update data from the Table Using Oracle Database

import java.sql.*;
public class Main {
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 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");
}
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Row updated Successfully
How To Update data from the Table Using MySql Database

import java.sql.*;
public class Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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");
}
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Row updated Successfully
ResultSet MeteData Using Jdbc

ResultSet MeteData Oracle Database

import java.sql.*;
public class Main {
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);
}
}
}

Output:
ResultSet MeteData MySql Database

import java.sql.*;
public class Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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);
}
}
}

Output:
DataBase MetaData Using Jdbc

DataBase MetaData Oracle Database

import java.sql.*;
public class Main {
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);
}
}
}

Output:
DataBase MetaData MySql Database

import java.sql.*;
public class Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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);
}
}
}

Output:
Insert Image File Into DataBase Using Jdbc
Table Creation:
                              
create table emp10(
empid int ,empname varchar(20),
image blob
)

Insert Image File Into DataBase Oracle Database

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 Main {
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 emp10 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);
}
}
}

Output:
Insert Image File Into DataBase Using MySql Database

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 Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
PreparedStatement pstmt=con.prepareStatement("insert into emp10 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);
}
}
}

Output:
Retrive Image File From DataBase Using Jdbc

Retrive Image File From DataBase Oracle Database

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 Main {
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");
Scanner s=new Scanner(System.in);
PreparedStatement pstmt=con.prepareStatement("select image from emp10 where empid= ? ");
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\\sateesh\\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);
}
}
}

Output:
Retrive Image File From DataBase Using MySql Database

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 Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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\\sateesh\\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);
}
}
}

Output:
Check The SavePoint Using Jdbc

Check The SavePoint Oracle Database

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.Statement;
public class Main {
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);
}
}
}

Output:
Check The SavePoint Using MySql Database

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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);
}
}
}

Output:
Call Back To SavePoint Using Jdbc

Call Back To SavePoint Oracle Database

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.Statement;
public class Main {
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(1,'johon')");
                    Savepoint point3=con.setSavepoint("spoint");
                    System.out.println("SavePoint created");
                        try
                        {
                            int i2=stmt.executeUpdate("delete from ssss where id=1");
                            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);
    }
        
    }
    
}

Output:
Call Back To SavePoint MySql Database

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
Statement stmt=null; 

        try
        {
             Class.forName("com.mysql.jdbc.Driver").newInstance();

            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");

            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(1,'johon')");
                    Savepoint point3=con.setSavepoint("spoint");
                    System.out.println("SavePoint created");
                        try
                        {
                            int i2=stmt.executeUpdate("delete from ssss where id=1");
                            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);
    }
        
    }
    
}

Output:
Insert Program Without Using Prepared Statements

create table student1(sid int,sname varchar(20), marks int)
Insert Program Without Using Prepared Statements in Oracle Database

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.Statement;
public class Main {
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");
Statement stmt = con.createStatement();
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();
stmt.executeUpdate( "INSERT INTO student1 VALUES ('"+sid+"', '"+sname+"', '"+marks+"')");
System.out.println("inserton success");
System.out.println("do you want to inset another row(Yes/no)");
Choice=s.next();
}
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Insert Program Without Using Prepared Statements in MySql Database

import java.sql.*;
import java.util.Scanner;
import javax.sql.*;
public class Main {
public static void main(String[] args) {
int sid=0,marks=0;
String sname="";
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
Statement stmt = con.createStatement();
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();
stmt.executeUpdate( "INSERT INTO student1 " + "VALUES ('"+sid+"', '"+sname+"', '"+marks+"')");
System.out.println("inserton success");
System.out.println("do you want to inset another row(Yes/no)");
Choice=s.next();
}
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Insert Program With Using Prepared Statements

Insert Program With Using Prepared Statements in Oracle Database

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Savepoint;
import java.sql.Statement;
public class Main {
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 student1 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);
}
}
}

Output:
Insert Program With Using Prepared Statements in MySql Database

import java.sql.*;
import java.util.Scanner;
import javax.sql.*;
public class Main {
public static void main(String[] args) {
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
PreparedStatement pstmt=con.prepareStatement("insert into student1 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);
}
}
}

Output:
Batch Processing in Jdbc

Batch Processing in Oracle Database

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
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,"System","Sateesh");
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);
}
}
}

Output:
Batch Processing in MySql Database

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
public static void main(String[] args) {
Statement stmt=null;
Connection con=null;
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");
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);
}
}
}

Output:
Callablestatement in Jdbc(Using Function)

Callablestatement in Jdbc(Using Function) Oracle Database

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 Addition(?,?)}"); 
        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);
        }
    
    }
    
}

Output:
Callablestatement in Jdbc(Using Function) MySql Database

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
public static void main(String[] args) {
Statement stmt=null;
Connection con=null;
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");

       CallableStatement stmt=con.prepareCall("{?= call Addition(?,?)}"); 
        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);
        }
}
catch(Exception e)
{
System.out.println(""+e);
}
}
}

Output:
Callablestatement in Jdbc(Using Procedure)

Callablestatement in Jdbc(Using Procedure) Oracle Database


 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,location);  
end;  
/  
             
 
Step3:    
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);
        }
    
    }
}

Output:
Callablestatement in Jdbc(Using Procedure) MySql Database

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
public static void main(String[] args) {
Statement stmt=null;
Connection con=null;
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/Rain","root","");

       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);
}
}
}

Output: