Select Query

package jdbc;

import java.sql.*;

public class SelectQuery
{
public static void main(String[] args) throws ClassNotFoundException, SQLException
{
String driverClassName="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:orcl";
String user="hr";
String password="hr";
String query ="select * from COUNTRIES";
int count=0;
Class.forName(driverClassName);
Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query);
while(rs.next())
{
System.out.println(rs.getString(1)+"..."+rs.getString(2)+"..."+rs.getInt(3));
count++;
}
System.out.println("No. of Rows = "+count);
con.close();
}

}

Procedure Call

package jdbc;
import java.sql.*;
public class ProcedureCall_Salary {

public static void main(String[] args) throws SQLException , ClassNotFoundException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","hr","hr");
CallableStatement cst =con.prepareCall("{call proc_getsal(?,?)}");
cst.setInt(1,101);                 
cst.registerOutParameter(2,Types.INTEGER);
cst.execute();
//System.out.println("Salary is :"+cst.getInt(2));
con.close();

}

}

Procedure_Call

package jdbc;
import java.sql.*;
public class Proc_Call
{

public static void main(String[] args) throws ClassNotFoundException, SQLException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");
CallableStatement cst = con.prepareCall("{call proc_sum(?,?,?)}");
cst.setInt(1,100);
cst.setInt(2,500);
cst.registerOutParameter(3,Types.INTEGER);
cst.execute();
System.out.println("Sum Is: "+cst.getInt(3));
con.close();

}

}

Query_Formatting

package jdbc;

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

public class Insert_QueryFormatting {

public static void main(String[] args) throws ClassNotFoundException ,SQLException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","hr","hr");
Statement st = con.createStatement();
Scanner sc = new Scanner(System.in);
System.out.println("Enter  FBI Code:");
int code = sc.nextInt();
System.out.println("Enter FBI Name:");
String name = sc.next();
System.out.println("Enter FBI Latitude:");
int lati = sc.nextInt();
System.out.println("Enter FBI Longitude:");
int longi = sc.nextInt();
String query = String.format("Insert into JDBC_SAMPLE_FBI values(%d,'%s',%d,%d)",code,name,lati,longi);
System.out.println(query);
int count = st.executeUpdate(query);
if(count>0)
{System.out.println("Data Inserted in JDBC_SAMPLE_FBI table");}
else {System.out.println("Something went wrong, pleaase contact Customer Services");}

}

}

Prepapred Statement INSERT

package jdbc;

import java.sql.Connection;
import java.sql.*;
import java.sql.SQLException;

public class Insert_PreparedStatement {

public static void main(String[] args) throws ClassNotFoundException,SQLException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","hr","hr");
//Statement st = con.createStatement();
String query = "insert into JDBC_SAMPLE_FBI values(?,?,?,?)";
PreparedStatement ps = con.prepareStatement(query);
ps.setInt(1, 490);
ps.setString(2, "Amit");
ps.setInt(3, 777);
ps.setInt(4,999);
int res = ps.executeUpdate();
if(res>0)
System.out.println("Values inserted");
con.close();





}

}

First JDBC

package jdbc;

import java.sql.*;
import java.sql.DriverManager;
import java.sql.SQLException;

public class First_Jdbc {

public static void main(String[] args) throws ClassNotFoundException, SQLException
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con= DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from fan");
while(rs.next())
{
System.out.println(rs.getInt(1)+"...."+rs.getString(2)+"...."+rs.getString(3));
}
rs.close();
st.close();
con.close();


}

}

Drop Table

package jdbc;

import java.sql.DriverManager;
import java.sql.*;
import java.util.*;

public class DropTableDemo {

public static void main(String[] args)throws ClassNotFoundException,SQLException 
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","hr","hr");
Statement st = con.createStatement();
st.executeUpdate("drop table demo2");
System.out.println("table deleted");
con.close();
}

}

Create Table

package jdbc;

import java.sql.*;
import java.sql.Statement;

public class CreateTableDemo
{
public static void main(String[] args) throws SQLException, ClassNotFoundException
{ try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","hr", "hr");
Statement st = con.createStatement();
st.executeUpdate("create table demo(d_num number , d_name varchar2(10), d_rate number)");
//st.executeQuery("commit");
System.out.println("Table created");
con.close();
}
catch(SQLException e)
{
System.out.println(e);
}
catch(ClassNotFoundException e2)
{
System.out.println(e2);
}
}
}