JDBC Examples

set classpath=%classpath%;C:\oracle\ora90\jdbc\lib\classes111.jar;

1)

import java.sql.*;
class jdbcexample2{

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

System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println(con);
System.out.println("Connection established");

}//try close

catch(Exception ex){}
}
}


***********************************************************
2)

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established");

Statement smt = con.createStatement();
ResultSet rs = smt.executeQuery("select * from my_employees");
System.out.println("Connected");

rs.next();

System.out.println("ok");
System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t");

}//try close

catch(Exception ex){}
}
}

***********************************************************
3)

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established");

Statement smt = con.createStatement();
ResultSet rs = smt.executeQuery("select * from my_employees");
System.out.println("Connected");

ResultSetMetaData meta = rs.getMetaData();
int columns = meta.getColumnCount();

while(rs.next())
{
for(int i=1;i<=columns;i++){ System.out.println(rs.getString(i)+"\t"); } System.out.println("\n"); } }//try close catch(Exception ex){} } } *********************************************************** 4) import java.sql.*; class jdbcexample2{ public static void main(String args[]){ try{ System.out.println("ok"); Class.forName("oracle.jdbc.driver.OracleDriver"); System.out.println("ok"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle"); System.out.println("Connection established"); Statement smt = con.createStatement(); ResultSet rs = smt.executeQuery("select * from my_employees"); System.out.println("Connected"); while(rs.next()) { System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); } } //try close catch(Exception ex){} } } *********************************************************** 5) SCROLLABLE (//Set first parameter to ResultSet.TYPE_SCROLL_SENSITIVE) import java.sql.*; class jdbcexample2{ public static void main(String args[]){ try{ System.out.println("ok"); Class.forName("oracle.jdbc.driver.OracleDriver"); System.out.println("ok"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle"); System.out.println("Connection established"); //Set first parameter to ResultSet.TYPE_SCROLL_SENSITIVE Statement smt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs = smt.executeQuery("select * from my_employees"); System.out.println("Connected\n\n"); rs.last(); System.out.println("Last row\n"); System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); rs.first(); System.out.println("First row\n"); System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); rs.relative(3); System.out.println("4th row\n"); System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); rs.relative(-3); System.out.println("First row\n"); System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); rs.last(); System.out.println("Last row\n"); System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); rs.absolute(3); System.out.println("3rd row\n"); System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); rs.previous(); System.out.println("2nd row\n"); System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); rs.first(); boolean b1 = rs.isFirst(); boolean b2 = rs.isLast(); System.out.println(b1+ "\tie : In First row\n"); System.out.println(b2+ "\tie : Not in In Last row\n"); } //try close catch(Exception ex){} } } *********************************************************** 6) Updatable Resultset Example import java.sql.*; class jdbcexample2{ public static void main(String args[]){ try{ System.out.println("ok"); Class.forName("oracle.jdbc.driver.OracleDriver"); System.out.println("ok"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle"); System.out.println("Connection established"); Statement smt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs = smt.executeQuery("select * from my_employees where id =1"); System.out.println("Connected\n\n"); rs.next(); System.out.println("Before Updation"); System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+rs.getString(4)+"\t"+rs.getString(5)+"\t"); System.out.println("\n"); ResultSet rs1 = smt.executeQuery("select LAST_NAME,SALARY from my_employees where id =1"); rs1.next(); //must update all fields in the select query rs1.updateString("LAST_NAME","k"); rs1.updateInt("SALARY",30000); rs1.updateRow(); //Save updated row System.out.println("Updation completed\n"); ResultSet rs2 = smt.executeQuery("select * from my_employees where id =1"); rs2.next(); System.out.println("After Updation"); System.out.println(rs2.getString(1)+"\t"+rs2.getString(2)+"\t"+rs2.getString(3)+"\t"+rs2.getString(4)+"\t"+rs2.getString(5)+"\t"); System.out.println("\n"); } //try close catch(Exception ex){} } } *********************************************************** 7) Batch Updation --> (Deletion)

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established");


Statement smt = con.createStatement();
smt.addBatch("delete from my_employees where first_name='mmmm'");
smt.addBatch("delete from my_employees where first_name='eeeee'");
System.out.println("ok");
int[] updateCounts = smt.executeBatch();
System.out.println("Connected\n\n");

System.out.println("updateCounts[0] = "+updateCounts[0]+"\n");
System.out.println("updateCounts[1] = "+updateCounts[1]+"\n");

System.out.println("Batch Updation Completed\n");


} //try close

catch(Exception ex){}
}
}

***********************************************************
8)Batch Updation --> (Updation)

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established");


Statement smt = con.createStatement();
smt.addBatch("UPDATE my_employees SET salary=5000 WHERE id=1");
smt.addBatch("UPDATE my_employees SET salary=8000 WHERE id=4");
//smt.addBatch("INSERT INTO my_employees VALUES(13,'m','sahil',919,4000,3000)");
System.out.println("ok");
int[] updateCounts = smt.executeBatch();
System.out.println("Connected\n\n");

System.out.println("updateCounts[0] = "+updateCounts[0]+"\n");
System.out.println("updateCounts[1] = "+updateCounts[1]+"\n");

System.out.println("Batch Updation Completed\n");


} //try close

catch(Exception ex){}
}
}

***********************************************************


***********************************************************

ADVANCED JDBC

***********************************************************


***********************************************************
1) PreparedStatement : Example

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established\n");


PreparedStatement presmt = con.prepareStatement("UPDATE my_employees SET salary =? WHERE id=?");
presmt.setInt(1,2000);
presmt.setInt(2,10);

int n=presmt.executeUpdate();

System.out.println("Value in Variable n="+n+"\n");

if(n==1)
{
System.out.println("Updation Completed\n");
}
else
System.out.println("No row found\n");


} //try close

catch(Exception ex){}
}
}

***********************************************************
2) CallableStatement ---- Function that takes one parameter ,(call from java code)


a) Function

CREATE OR REPLACE FUNCTION annual_comp(a NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN(2000*12);
END;


b) Java code

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established\n");

CallableStatement callsmt = con.prepareCall("{?=call annual_comp(2)}");
callsmt.registerOutParameter(1,Types.INTEGER);
callsmt.execute();
//or executeQuery();
String retvalue = callsmt.getString(1);

System.out.println("Function Return Value="+retvalue);

} //try close

catch(Exception ex){}
}
}


***********************************************************
3)CallableStatement ---- Function in java code. --- one OUT parameter.

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established\n");

String ss= "CREATE OR REPLACE FUNCTION annual_comp1(a NUMBER) \n"+
"RETURN NUMBER\n"+
" IS\n"+
" BEGIN\n"+
" RETURN(a);\n"+
" END;";

Statement smt = con.createStatement();
smt.executeQuery(ss);

CallableStatement callsmt = con.prepareCall("{?=call annual_comp1(2)}");
callsmt.registerOutParameter(1,Types.INTEGER);
callsmt.executeQuery();
String retvalue = callsmt.getString(1);

System.out.println("Function Return Value="+retvalue);

} //try close

catch(Exception ex){}
}
}

***********************************************************
4)CallableStatement ---- Function in java code. --- one OUT parameter and two IN parameter.

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established\n");

String ss= "CREATE OR REPLACE FUNCTION annual_comp2(a NUMBER,b NUMBER) \n"+
"RETURN NUMBER\n"+
" IS\n"+
" BEGIN\n"+
" RETURN(a*b);\n"+
" END;";

Statement smt = con.createStatement();
smt.executeQuery(ss);

CallableStatement callsmt = con.prepareCall("{?=call annual_comp2(?,?)}");
callsmt.registerOutParameter(1,Types.INTEGER);
callsmt.setInt(2,10);
callsmt.setInt(3,10);
callsmt.executeQuery();
String retvalue = callsmt.getString(1);

System.out.println("Function Return Value="+retvalue);

} //try close

catch(Exception ex){}
}
}


***********************************************************
5)CallableStatement ---- Procudure --- one IN parameter.

a) Procedure -- first compile procedure.

CREATE OR REPLACE PROCEDURE raise_salary(v_id IN my_employees.id%TYPE)
IS
BEGIN
UPDATE my_employees
SET salary=salary+400
WHERE id = v_id;
END raise_salary;


b) java code

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established\n");

CallableStatement callsmt = con.prepareCall("{call raise_salary(1)}");
//or callsmt.setInt(1,1);
ResultSet rs = callsmt.executeQuery();
//or execute();
System.out.println("End");

} //try close

catch(Exception ex){}
}
}
***********************************************************
6)CallableStatement ---- Procedure in java code.

import java.sql.*;

class jdbcexample2{

public static void main(String args[]){
try{
System.out.println("ok");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@ws-8:1521:oratemp","ora1","oracle");
System.out.println("Connection established\n");

String ss= "CREATE OR REPLACE PROCEDURE raise_salary(v_id IN my_employees.id%TYPE)\n"+
"IS\n"+
"BEGIN\n"+
"UPDATE my_employees\n"+
"SET salary=salary+400\n"+
"WHERE id = v_id;\n"+
"END raise_salary;";

Statement smt = con.createStatement();
smt.executeQuery(ss);

CallableStatement callsmt = con.prepareCall("{call raise_salary(1)}");

callsmt.executeQuery();
System.out.println("END");

} //try close

catch(Exception ex){}
}
}

PL SQL *****

Introduction to JavaMail API

***********************************************************