Day-1

1. Corejava & adv java are not official terms.

Core Java

Adv java

as per the sun micro systems standreds the java divided into three parts

a. J2SE/JSE(java 2 standard edition) : corejava , JDBC

b. J2EE/JEE(java 2 enterprise edition) : servlets , jsp , jstl , EJB , xml, webservices , JPA , JMS

c. J2ME/JME(java 2 micro edition)

2. adv java syllabus : some part of j2se & some part of j2ee

JDBC

Servlets

JSP : java server pages

JSTL : java standred tag library

3. Technologies vs. frameworks

Technologies :

j2se

j2ee

j2me

frame works :

spring

hibernate

structs

JSF

a. Technologies are sources based on tech the frameworks are developed.

JDBC ---- hibernate

servlets ---- structs

EJB ---- spring

JSP ---- JSF

b. The framework will support all the features of technologies,

 It supports extra features.

It overcomes the limitations of technologies.

c. frame work is flexible.

d. Tech performance is good......

e. frame work will give more predefined support : So we can write less lines of code

when compare to f/w the Tech we will have less predeinfed support: everything we have to write : more lines of
code

4. java learning process : Java Developer:

level-1

corejava

adv java

level-2

spring

hibernate

level-3

web serivces : SOAP,REST

spring boot,security

spring micro services

java ---- oracle

python ---- postgre

hadoop ---- mangoDB

.net ---- sql server

PHP ---- mysql

https://www.protectedtext.com/advjava9am

password : java

5. java is open source

a. it is a free of cost.

b. source code open: we can download the source code & we can do the modifications.
 Day-2

//Different types of application

1. stand alone apps : J2se : .jar

2. web application : servlets , jsp || structs , JSF : .war

3. enterprize application : EJB || spring : .ear

4. distributed application : RMI ,web services : .aar

stand alone/ window / desktop :

a. These applications are specefic to machine

b. client-server architecture not required

c. internet connection not required

d. in stand alone apps we are designing GUI using AWT , swings,applets.

e. it contians main method execution starts from main method & executed by JVM.

f. we are developing standalone apps using : j2se

Web apps :

1. web application runs over : www

2. client-server architecture required

3. internet connection required

4. in web apps we are designing GUI using HTML,css,JS,jsp.

5. web applications no main method : it contians life cycle method : init service destory these are executed by servers.

6. we are developing web apps using : j2ee

Note:

JDBC code we can write the main method because it is belongs to J2SE

servlets,jsp code not possible to declare the main method it contains only life cycle methods.

//Web application architecture : 3-tier

client server database

client:

who sends the request & takes the response.

ex: all browsers.

server :

server contians the projects.

server takes the request.

identify the requested resource

process the request

render the response to client.

ex: tomcat ---- apache

weblogic --- oraccle

websphere --- IBM

glass fish --- sun micro system/oracle

jboss ---- red hat

datase :

used to store the details like project details, client details

ex: oracle , mysql

Client side technologies:

The technologies which are used to write the programming at client side are called client side technologies.

ex: jsp,html,css,…etc

Server side technologies:

The technologies which are used to write the programming at server side are called server side technologies.

ex: servlets , jsp , PHP, .net , spring ......

//web application layers

1. presentation layer

html , css , js

2. business layer

servlets , jsp

3. persistance/database layer

JDBC

JDBC :

connect the java application with databse.

 used to design persistance logics.

persitance logics means the logics which are connecting with the database.

servlets :

it’s used for server side programming.

it is used to write main business logics.

JSP :

it’s used for server side programming.

it is used to write main business logics.

Day-3

//oracle database download & install

oracle 9i

oracle 10g

oracle 11g

oracle 12c

oracle 18c

oracle 19c

express edition : xe

default user name : system

password : at the time of install // manager

enterprize edition : orcl

default user name : scoot

password : at the time of intall // tiger

https://www.oracle.com/database/technologies/xe-prior-releases.html

//oracle queries

create table emp(eid number,ename varchar2(30),esal number);

desc emp;

insert into emp values(111,'ratan',10000.45)

insert into emp values(222,'anu',20000.45)

select * from emp;

select eid,ename from emp;

select * from emp where esal>10000;

update emp set esal=esal+100 where esal>10000;

delete from emp where esal>10000;

delete from emp where eid=111;

truncate table emp; // here data is dropped but table is available.

drop table emp; // here both data & table is dropped.

//oracle statments

1. DML (Data Manipulation Language)

insert update delete select

2. DDL (Data Definition Language)

create alter drop

3. DCL (Data Control Language)

grant revoke

4. TCL (Transaction Control Language)

commit rollback

in application level DML,TCL is responsibility of Developer.

in application level DDL,DCL is responsibility of database admin.

//eclipse IDE :

h --- helious

i --- indigo

j ---- juno

k ---- kepler

l --- luna

m --- mars
n --- neon

o ---- oxygen

p --- photon

j2se/java : corejava ,jdbc

j2ee : corejava , JDBC ,servlet , jsp

https://www.eclipse.org/downloads/packages/release/oxygen/r/eclipse-ide-java-ee-developers

1. interfaces

2. Exception handling

checked exception : JDBC mostly throws checked exception

uchecked exception

3. Scanner class

4. try-with resources.

5. how to load the class file[Demo.class] into memory.

Day - 4

JDBC : java database connection

DRIVER

java ---------------- database

String q1 = "create table..."; java API sql API DBE

driver : it is trransaltor who taranslate java syntax queries into database syntax.

steps to design first application :

1. Loading & register the driver.

The driver class is given by Database vendor.

The driver class-name is,

oracle.jdbc.driver.OracleDriver

package name : oracle.jdbc.driver

class-name : OracleDriver

The vendor given driver class-name in the form of jar file.The jar file location is,

C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib
oracle 10g:

ojdbc14.jar

oracle 11g:

ojdbc5.jar [.jar class files are compiled with jdk5]

ojdbc6.jar [.jar class files are compiled with jdk6]

oracle12c :

ojdbc6.jar [.jar class files are compiled with jdk6]

ojdbc7.jar [.jar class files are compiled with jdk7]

To check the predefined support use javap

E:\>javap java.lang.Class

public static java.lang.Class<?> forName(java.lang.String) throws java.lang.ClassNotFoundException;

The forname() method throws ClassNotFoundException & it is a checked exception so handle the checked exception in two
ways,

a. using throws keyword.

b. using try-catch blocks.

To load the driver class-name use forName(), it is a static method of Class.

Class.forName("oracle.jdbc.driver.OracleDriver")

To see the OracleDriver information just extract the jar file(ojdbc6.jar) check the location of class-name & open the driver class
using java decompiler.

http://www.javadecompilers.com

the OracleDriver class contians the static block. This static block will register the driver into DriverManager service.

static {

DriverManager.registerDriver(OracleDriver.defaultDriver = (OracleDriver)new oracle.jdbc.OracleDriver());

DriverManager: It is a service where all the drivers are register(available).

//buildpath vs. class-path

The IDE will take the jar file data from buildpath

When we run the applciation using command prompt in this case the command prompt will locate the jar file in class-path.

once we set the jar in classpath this will work when we are running the appication using ccommand prompt.
package com.tcs;

public class TestDb1 {

public static void main(String[] args) throws ClassNotFoundException {

//step 1: load & register the driver

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("Driver Class is loaded successfully.......");

2. Create the connection between java ----- database

3. create the statment object.

4. write & process the query using statement object.

5. Release the resources.

Day - 5

JDBC : java database connection

DRIVER

java ---------------- database

String q1 = "create table..."; java API sql API DBE

driver : it is trransaltor who taranslate java syntax queries into database syntax.

steps to design first application :

1. Loading & register the driver.

The driver class is given by Database vendor.

The driver class-name is,

oracle.jdbc.driver.OracleDriver

package name : oracle.jdbc.driver

class-name : OracleDriver

The vendor given driver class-name in the form of jar file.The jar file location is,

C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib

oracle 10g:

ojdbc14.jar
oracle 11g:

ojdbc5.jar [.jar class files are compiled with jdk5]

ojdbc6.jar [.jar class files are compiled with jdk6]

oracle12c :

ojdbc6.jar [.jar class files are compiled with jdk6]

ojdbc7.jar [.jar class files are compiled with jdk7]

To check the predefined support use javap

E:\>javap java.lang.Class

public static java.lang.Class<?> forName(java.lang.String) throws java.lang.ClassNotFoundException;

The forname() method throws ClassNotFoundException & it is a checked exception so handle the checked exception in two
ways,

a. using throws keyword.

b. using try-catch blocks.

To load the driver class-name use forName(), it is a static method of Class.

Class.forName("oracle.jdbc.driver.OracleDriver")

To see the OracleDriver information just extract the jar file(ojdbc6.jar) check the location of class-name & open the driver class
using java decompiler.

http://www.javadecompilers.com

the OracleDriver class contians the static block. This static block will register the driver into DriverManager service.

static {

DriverManager.registerDriver(OracleDriver.defaultDriver = (OracleDriver)new oracle.jdbc.OracleDriver());

DriverManager: It is a service where all the drivers are register(available).

//buildpath vs. class-path

The IDE will take the jar file data from buildpath

When we run the applciation using command prompt in this case the command prompt will locate the jar file in class-path.

once we set the jar in classpath this will work when we are running the appication using ccommand prompt.

package com.tcs;

public class TestDb1 {

 public static void main(String[] args) throws ClassNotFoundException {

//step 1: load & register the driver

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("Driver Class is loaded successfully.......");

2. Create the connection between java ----- database

The JDBC predefined support in two packages

java.sql

javax.sql

public static java.sql.Connection getConnection(java.lang.String, java.lang.String, java.lang.String) throws java.sql.SQLException;

this method throws SQLException & it is a checked exception so handle the checked exception using

a. try-catch blocks

b. throws

Connection con = DriverManager.getConnection("url","uaername","password");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

3. create the statment object.

Statement st = connection.createStatement();

syntax:

public abstract java.sql.Statement createStatement() throws java.sql.SQLException;

this method throws SQLException & it is a checked exception so handle the checked exception using

a. try-catch blocks

b. throws

4. write & process the query using statement object.

Statement:

int --- executeUpdate() used to execute update queries

 create insert update delete drop

0 1 row rowcount 0

ResultSet -- executeQuery() used to execute select queries

select * from emp;

boolean ----- execute() --- used to execute both

5. Release the resources.

connection.close()

syntax :

public abstract void close() throws java.sql.SQLException;

this method throws SQLException & it is a checked exception so handle the checked exception using

a. try-catch blocks

b. throws

ex-1: using throws keyword

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class TestDb1 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

//step 1: load & register the driver

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("Driver Class is loaded successfully.......");

//step 2: create the connection

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully....");

//step3: create the statement object

Statement statement = connection.createStatement();

 //step 4: write & process the query using statement object.

String q1 = "create table emp(eid number,ename varchar2(20),esal number)";

int x = statement.executeUpdate(q1);

System.out.println("Table created successfully......"+x);

//step 5: release the resources

connection.close();

System.out.println("Connection closed successfully.....");

In above example we are using throws keyword,

If the application terminated normally then database closed.

If the application terminated abnormally then database is not closed.

To overcome above limitation to close the database both normal & abnormal cases use finally block.

The finally block code is executed both normal & abnormal terminations.

ex2: application with try-catch-finally.

step 1: Declare the resources

step 2: try block do the operations.

step 3: catch bock handle the exception.

step 4: finally block release the resources.

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class TestDb2 {

public static void main(String[] args) {

Connection connection = null;

Statement statement = null;

 try{

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("Driver Class is loaded successfully.......");

connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully....");

statement = connection.createStatement();

String q1 = "insert into emp values(111,'ratan',10000.45)";

String q2 = "insert into emp values(222,'anu',20000.45)";

String q3 = "insert into emp values(333,'sravya',30000.45)";

int x = statement.executeUpdate(q1);

int y = statement.executeUpdate(q2);

int z = statement.executeUpdate(q3);

System.out.println("Data inserted successfully......"+x+" "+y+" "+z);

catch(SQLException|ClassNotFoundException e)

{ e.printStackTrace();

finally{

try {

if(statement!=null)statement.close();

if(connection!=null)connection.close();

} catch (SQLException e) {

e.printStackTrace();

System.out.println("Connection closed successfully.....");

ex: Assignment : write a example to shows try-with-resources.

Day - 6

ex-1: try-with-resources.

when we create the ResultSet the cursor is pointing to before first record.

to move the cursor record by record use next() method.

to read the multiple records use while loop.

-->rs

111 ratan 10000.45

222 anu 20000.45

333 sravya 30000.54

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class TestDb3 {

public static void main(String[] args) {

try(Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

Statement statement = connection.createStatement())

String q = "select * from emp";

ResultSet set = statement.executeQuery(q);

while(set.next())

{ System.out.println(set.getInt(1)+" "+set.getString(2)+" "+set.getFloat(3));

catch(SQLException e)

{ e.printStackTrace();

}
}

ex-2: Assignment : try-catch-finally.

Create the connection

create the student table : sid sname smarks

insert four records

print the records

update the records marks+2 where marks<35

stop the execution 30sec : go and check the dabase the data is updated or not

drop the table.

close the connection.

ex-3: execute method return boolean value.

Statement :

executeUpdate() ----- update

executeQuery() ---- select

boolean --- execute() ----- both queries

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

public class TestDb4 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection
=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

Statement statement = connection.createStatement();

 Scanner scanner = new Scanner(System.in);

System.out.println("Enter query.....");

String query = scanner.nextLine();

boolean status = statement.execute(query);

if(status)

{ ResultSet set = statement.getResultSet();

while(set.next())

{ System.out.println(set.getInt(1)+" "+set.getString(2)+" "+set.getFloat(3));

else

{ int x = statement.getUpdateCount();

System.out.println("updated records..."+x);

scanner.close();

statement.close();

connection.close();

System.out.println("Connection closed successfully.....");

ex-4: Assignment

Take the input eid,ename,esal from end user using scanner class

insert the data into database.

after insertion give the message do you want one more record(yes/no)

yes : again take the insert the data

no : stop the execution

Assignments

Assignment 1- Student table using Try-catch-Finally Block - Sriharsha

package com.tcs;

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

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class Assignment2 {

public static void main(String[] args) {

Connection con = null;

Statement stmt = null;

try {

//Load and register the driver

Class.forName("oracle.jdbc.driver.OracleDriver");

System.out.println("Driver loaded successfully");

//Open Connection

con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","manager");

//Create Statement object

stmt = con.createStatement();

//Write and process query

String q1 = "create table student(sid number, sname varchar2(30), smarks number)";

String q2 = "insert into student values(1, 'Suresh', 95)";

String q3 = "insert into student values(2, 'Ramesh', 34)";

String q4 = "insert into student values(3, 'Kamesh', 33)";

String q5 = "insert into student values(4, 'Naresh', 95)";

String q6 = "Select * from student";

String q7 = "Update student set smarks=smarks+2 where smarks<35";

int x = stmt.executeUpdate(q1);

System.out.println("Create query executed with value "+x);

int y = stmt.executeUpdate(q2);
 System.out.println("Insert query executed with value "+y);

int z = stmt.executeUpdate(q3);

System.out.println("Insert query executed with value "+z);

int a = stmt.executeUpdate(q4);

System.out.println("Insert query executed with value "+a);

int b = stmt.executeUpdate(q5);

System.out.println("Insert query executed with value "+b);

ResultSet set = stmt.executeQuery(q6);

System.out.println("Select query executed");

while(set.next()) {

System.out.println(set.getInt(1)+" "+set.getString(2)+" "+set.getInt(3));

int c = stmt.executeUpdate(q7);

System.out.println("update query executed with value "+c);

//Pause the execution for 30 seconds

Thread.currentThread();

Thread.sleep(30000);

}catch(ClassNotFoundException|SQLException|InterruptedException e)

e.printStackTrace();

finally {

try {

if(con!=null&&stmt!=null) {

stmt.close();

con.close();
 System.out.println("Connection closed successfully.");

}catch (SQLException f) {

f.printStackTrace();

Assignment 2 - Accept details from user and execute insert queries - Sriharsha

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Scanner;

public class Assignment3 {

public static void main(String[] args) throws SQLException {

//establishing connections and creating statement object with try-with-resources

try(Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","manager");

Statement stmt = con.createStatement()){

Scanner in = new Scanner(System.in);

int i;

//loop to accept details from user and insert them into database

do{

System.out.println("Enter Employee ID");

int eid = in.nextInt();

 System.out.println("Enter Employee Name");

String ename = in.next();

System.out.println("Enter Employee Salary");

float esal = in.nextFloat();

String query = "insert into emp values("+eid+", " +"'"+ename+"'"+", "+esal+")";

//System.out.println(query); -- To confirm that query is correct

int x = stmt.executeUpdate(query);

System.out.println("Inserted successfully with return value "+x);

System.out.println("Press 1 to insert another record or 0 to exit. ");

i = in.nextInt();

} while(i==1);

in.close();

System.out.println("Closing the connections");

==============================================================

Raj

/*

* ex-2: Assignment : try-catch-finally.

Create the connection

create the student table : sid sname smarks

insert four records

print the records

update the records marks+2 where marks<35

stop the execution 30sec : go and check the dabase the data is updated or not

drop the table.

close the connection.

*/

package ExerciseAdvJava;

import java.sql.*;

import java.util.*;

public class Pgm1Jdbc {

public static void main(String[] args) throws ClassNotFoundException, SQLException{

Connection con = null;

try {

Class.forName("oracle.jdbc.OracleDriver");

con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1522:xe", "system", "manager");

System.out.println("connection created");

Statement st = con.createStatement();

int a = st.executeUpdate("create table student(sid number, sname varchar2(10), smarks number)");

String query1 = "insert into student values(111, 'Raman', 70)";

String query2 = "insert into student values(222, 'Aman', 40)";

String query3 = "insert into student values(333, 'Amit', 30)";

String query4 = "insert into student values(444, 'Sukesh', 60)";

String query5 = "select * from student";

String queryUpdate = "update student set smarks = smarks + 2 where smarks <= 35";

String queryDrop = "drop table student";

int res1 = st.executeUpdate(query1);

int res2 = st.executeUpdate(query2);

int res3 = st.executeUpdate(query3);

int res4 = st.executeUpdate(query4);

 ResultSet rs = st.executeQuery(query5);

while (rs.next()) {

System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getInt(3));

int resUpdate = st.executeUpdate(queryUpdate);

Thread.sleep(10000);

int dropTable = st.executeUpdate(queryDrop);

catch(Exception e) {

finally{

try{if(con != null) con.close();}

catch(Exception e){e.printStackTrace();}

System.out.println("Connection Closed");

-----------

/*

* ex-4: Assignment

Take the input eid,ename,esal from end user using scanner class

insert the data into database.

after insertion give the message do you want one more record(yes/no)

yes : again take the insert the data

no : stop the execution

*/

package ExerciseAdvJava;

import java.sql.*;

import java.util.*;

public class PgmJdbc2 {

public static void main(String[] args) throws ClassNotFoundException, SQLException{

try (Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1522:xe", "system",

"manager");){

System.out.println("connection created");

Statement st = con.createStatement();

int a = st.executeUpdate("create table employee(eid number, ename varchar2(10), eSal number)");

while (true) {

System.out.println("Enter the table record values");

Scanner sc = new Scanner(System.in);

System.out.println("Enter the Emp ID value");

int empID = sc.nextInt();

System.out.println("Enter the Emp Name value");

String empName = sc.next();

System.out.println("Enter the Emp Salary value");

int empSal = sc.nextInt();

String query = String.format("insert into employee values (%d,'%s',%d)", empID,empName,

empSal);

int res = st.executeUpdate(query);

System.out.println(res);
 System.out.println("record inserted - Do you want to insert another record type yes/no ");

String choice = sc.next();

if (choice.equalsIgnoreCase("no"))

break;

String querySelect = "select * from employee";

ResultSet rs = st.executeQuery(querySelect);

while (rs.next()) {

System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getDouble(3));

String queryDrop = "drop table employee";

int dropTable = st.executeUpdate(queryDrop);

catch(Exception e) {

e.printStackTrace();

Day - 7

package ExerciseAdvJava;

import java.sql.*;

import java.util.*;

public class PgmJdbc2 {

 public static void main(String[] args) throws ClassNotFoundException, SQLException{

try (Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1522:xe", "system",

"manager");){

System.out.println("connection created");

Statement st = con.createStatement();

while (true) {

Scanner sc = new Scanner(System.in);

System.out.println("Enter the Emp ID value");

int empID = sc.nextInt();

System.out.println("Enter the Emp Name value");

String empName = sc.next();

System.out.println("Enter the Emp Salary value");

int empSal = sc.nextInt();

String query = String.format("insert into employee values (%d,'%s',%d)", empID,empName,

empSal);

int res = st.executeUpdate(query);

System.out.println(res);

System.out.println("record inserted - Do you want to insert another record type yes/no ");

String choice = sc.next();

if (choice.equalsIgnoreCase("no"))

break;

catch(Exception e) {

e.printStackTrace();

1000 ---times
DBE : dtabase engine will execute the query.

a. query tokeniztion

b. query parsing.

c. query optimization

d. query execution.

1000*4 = 4k steps

PreparedStatement : used to execute the parametarzed query.

PreparedStatement("insert into emp values(?,?,?)")

when we declare the query using PS the query compilation is done only once.

Statement object every time four steps will be performed

when we declare the query using PS the query compilation is done only once. So steps are reduced.

ex-1:

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.Scanner;

public class PSEx1 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection
=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

PreparedStatement preparedStatement = connection.prepareStatement("insert into emp values(?,?,?)");

Scanner scanner = new Scanner(System.in);

while(true)

{
 System.out.println("Enter emp id:");

int eid = scanner.nextInt();

System.out.println("Enter emp name:");

String ename = scanner.next();

System.out.println("Enter emp sal:");

double esal = scanner.nextDouble();

preparedStatement.setInt(1, eid);

preparedStatement.setString(2, ename);

preparedStatement.setDouble(3, esal);

preparedStatement.executeUpdate();

System.out.println("data inserted successfully.....do you want one more record(yes/no)");

String option = scanner.next();

if(option.equalsIgnoreCase("no"))

break;

scanner.close();

preparedStatement.close();

connection.close();

System.out.println("Connection closed successfully.....");

ex-2:

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class PSEx2 {

 public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection
=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

PreparedStatement preparedStatement = connection.prepareStatement("update emp set esal = esal+? where

esal>?");

preparedStatement.setInt(1, 500);

preparedStatement.setInt(2, 5000);

int row_count = preparedStatement.executeUpdate();

System.out.println("updated records....."+row_count);

preparedStatement.close();

connection.close();

System.out.println("Connection closed successfully.....");

ex-3:

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class PSEx3 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection
=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

 PreparedStatement preparedStatement = connection.prepareStatement("select * from emp where esal<?");

preparedStatement.setInt(1, 10000);

ResultSet resultSet = preparedStatement.executeQuery();

while(resultSet.next())

{ System.out.println(resultSet.getInt("eid")+" "+resultSet.getString(2)+" "+resultSet.getFloat(3));

resultSet.close();

connection.close();

System.out.println("Connection closed successfully.....");

ex-1: Store the binary data in database using PreparedStatement.

ex-2: Assignent : Read the data from emp table in previous direction.

=========================================================================

Assignment

Solution - Assignment - Raj

ex-2: Assignent : Read the data from emp table in previous direction.

package ExerciseAdvJava;

import java.sql.*;

import java.util.*;

public class PgmJdbc8 {

public static void main(String[] args) throws ClassNotFoundException, SQLException{

Class.forName("oracle.jdbc.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1522:xe","system","manager");

 System.out.println("connection created successfully");

PreparedStatement ps = con.prepareStatement("select * from emp order by eid desc");

ResultSet rs = ps.executeQuery();

while(rs.next()) {

System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getInt(3));

con.close();

System.out.println("connection closed successfully");

ResultSet:

111 ratan 10000.45

222 anu 20000.43

333 sravya 3000.56

--->rs

ResultSet type :

forward direction

ResultSet.TYPE_FORWARD_ONLY

scrollable

ResultSet.TYPE_SCROLL_SENSITIVE

The ResultSet reflects changes made by others to the underlying data source while it remains opened.

ResultSet.TYPE_SCROLL_INSENSITIVE

The ResultSet does NOT reflect change made by others to the underlying data source while it is opened,
hence, insensitive to changes.

ResultSet concurency :

readonly

ResultSet.CONCUR_READ_ONLY

updatable
 ResultSet.CONCUR_UPDATABLE

Note: ResultSet is by default forward only & readonly.

rs : 3-records

print the data : 3 records

stop the execution : 30 added 3-records in database

print the data : 6

ex-1:

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class ResultSetEx1 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

Statement statement =
connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet set = statement.executeQuery("select * from emp");

set.afterLast();

while(set.previous())

{ System.out.println(set.getInt(1)+" "+set.getString(2)+" "+set.getFloat(3));

set.first();

System.out.println(set.getInt(1)+" "+set.getString(2));
 set.last();

System.out.println(set.getInt(1)+" "+set.getString(2));

set.absolute(3);

System.out.println(set.getInt(1)+" "+set.getString(2));

set.close();

connection.close();

System.out.println("Connection closed successfully.....");

ex:

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class ResultSetEx2 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

Statement statement =
connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet set = statement.executeQuery("select eid,ename,esal from emp");

set.absolute(3);

set.updateInt(1, 3);
 set.updateString(2, "srav");

set.updateFloat(3, 30000);

set.updateRow();

System.out.println("record updated successfully......");

set.close();

connection.close();

System.out.println("Connection closed successfully.....");

ex: Assignment : Using ResultSet print the data in forward & backword direction.

ex: using resultset object we can do the insertion operations.

set.moveToInsertRow();

set.updateInt(1, 55);

set.updateString(2, "ratan");

set.updateFloat(3,2000);

set.insertRow();

ex: Check the example Scroll sensitive & insensitive

ex: write one example about the : properties file

Standalone apps

Steps to design standalone applications:- [not required]

Step 1:- prepare the component and add the components to the frame

Step 2:- set the particular layout to the frame.

Step 3:-conversion of static component into the dynamic component.(by adding listeners)

Example 1:

import java.sql.*;

import java.awt.*;

import java.awt.event.*;

class SearchFrame extends Frame implements ActionListener

{ Label l;
TextField tf;

Button b;

Connection con;

Statement st;

ResultSet rs;

SearchFrame()

{ try

{ this.setVisible(true);

this.setSize(500,400);

this.setBackground(Color.pink);

this.setTitle("JDBC - AWT Application");

this.setLayout(new FlowLayout());

l=new Label("productId");

tf=new TextField(15);

b=new Button("Search");

b.addActionListener(this);

this.add(l);

this.add(tf);

this.add(b);

Class.forName("oracle.jdbc.driver.OracleDriver");

con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","manager");

st=con.createStatement();

catch (Exception e)

{ e.printStackTrace();

public void actionPerformed(ActionEvent ae)

{ try

{ rs=st.executeQuery("select * from emp where eno='"+tf.getText()+"'");

repaint();

catch (Exception e)

{ e.printStackTrace();

}
 }

public void paint(Graphics g)

{ try

Font f=new Font("arial",Font.BOLD,30);

g.setFont(f);

boolean b=rs.next();

if(b==true)

{ g.drawString("emp id....."+rs.getInt(1),50,100);

g.drawString("emp Name....."+rs.getString(2),50,150);

g.drawString("emp sal....."+rs.getInt(3),50,200);

else

{ g.drawString("emp does not exists",50,150);

catch (Exception e)

{ e.printStackTrace();

class Test

{ public static void main(String[] args)

{ new SearchFrame();

Example:-

import java.awt.*;

import java.awt.event.*;

import java.sql.*;

class MyFrame extends Frame implements ActionListener

{ Button b1,b2;
Connection con;

ResultSet rs;

Statement st;

String label;

MyFrame()

{ try{

this.setVisible(true);

this.setSize(500,500);

this.setBackground(Color.red);

b1=new Button("NEXT");

b2=new Button("PREVIOUS");

this.setLayout(new FlowLayout());

this.add(b1);

this.add(b2);

b1.addActionListener(this);

b2.addActionListener(this);

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

con=DriverManager.getConnection("jdbc:odbc:ratan","system","manager");

st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

rs=st.executeQuery("select * from emp");

catch(Exception e)

{ System.out.println(e);

public void actionPerformed(ActionEvent e)

{ label=e.getActionCommand();

repaint();

public void paint(Graphics g)

{ try{

if (label.equals("NEXT"))

{ boolean b=rs.next();

if (b==true)

{ g.drawString("emp number"+rs.getInt(1),50,100);

g.drawString("emp name"+rs.getString(2),50,200);
 g.drawString("emp sal"+rs.getInt(3),50,300);

else

{ g.drawString("no record",50,500);

if (label.equals("PREVIOUS"))

{ boolean b=rs.previous();

if (b==true)

{ g.drawString("emp number"+rs.getInt(1),50,100);

g.drawString("emp name"+rs.getString(2),50,200);

g.drawString("emp sal"+rs.getInt(3),50,300);

else

{ g.drawString("no record",50,500);

catch(Exception e)

{ System.out.println(e); }

};

class Test

{ public static void main(String[] args)

{ MyFrame f=new MyFrame();

oracle.properties

comman file

read the data from properties file create the connection return the connection

f1 f2 f3 f4

read code read code read code read code

database metadata is stored in : DatabaseMetaData

size size

number varchar

eid ename esal

111 ratan 10000.45

222 anu 20000.54

333 sravya 30000.45

Table data is stored in : ResultSet

Table metadata is stored in : ResultSetMetaData

CallableStatement

Transaction Management

Connection Pooling

Blob CLOB

MYsql Database

RowSet examples

PL/SQL : procedures & function

create or replace procedure getSal(id IN number, sal OUT number)

as

BEGIN

select esal into sal from emp where eid=id;

END getSal;

[email protected]

//oracle.properties

username=system

password=ratan

driver=oracle.jdbc.driver.OracleDriver

url=jdbc:oracle:thin:@localhost:1521:xe
//TestConnection

package com.tcs;

import java.io.FileInputStream;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

public class TestConnection {

public static Connection createConnection()

Connection connection = null;

try{

//Load the properties file

FileInputStream inputStream = new FileInputStream("oracle.properties");

Properties properties = new Properties();

properties.load(inputStream);

//read the data from properties file create the connection

Class.forName(properties.getProperty("driver"));

connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),

properties.getProperty("password"));

catch(IOException | ClassNotFoundException | SQLException e)

e.printStackTrace();

return connection;

//DBMDex.java

package com.tcs;
import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.SQLException;

public class DBMDex {

public static void main(String[] args) throws SQLException {

Connection connection = TestConnection.createConnection();

DatabaseMetaData metaData = connection.getMetaData();

System.out.println(metaData.getDatabaseProductName());

System.out.println(metaData.getDatabaseProductVersion());

System.out.println(metaData.getUserName());

System.out.println(metaData.getURL());

System.out.println(metaData.getMaxUserNameLength());

System.out.println(metaData.getDriverName());

connection.close();

System.out.println("Connection closed successfully.....");

//RSMDEx

package com.tcs;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

public class RSMDEx {

public static void main(String[] args) throws SQLException {

 Connection connection = TestConnection.createConnection();

Statement statement = connection.createStatement();

ResultSet set = statement.executeQuery("select * from emp");

ResultSetMetaData metaData = set.getMetaData();

System.out.println(metaData.getColumnCount());

System.out.println(metaData.getColumnClassName(1));

System.out.println(metaData.getColumnClassName(2));

System.out.println(metaData.getColumnName(1));

System.out.println(metaData.getColumnName(2));

System.out.println(metaData.getColumnDisplaySize(1));

System.out.println(metaData.getColumnDisplaySize(2));

System.out.println(metaData.getColumnTypeName(1));

System.out.println(metaData.getColumnTypeName(2));

connection.close();

System.out.println("Connection closed successfully.....");

PL/SQl :

both are to take the inputs & process the data & gives the response.

procedures :

no return value.

IN OUT

we need both IN OUT

functions :

can return the value.

the function return value is output so there no OUT params

we have only IN

//procedure taking eid as input & giving salary as output.

create or replace procedure getSal(id IN number, sal OUT number)

as

BEGIN

select esal into sal from emp where eid=id;

END getSal;

package com.tcs;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

public class CallableStatementEx1 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection
=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

CallableStatement callableStatement = connection.prepareCall("{call getSal(?,?)}");

callableStatement.setInt(1, 111);

callableStatement.registerOutParameter(2,Types.FLOAT);

callableStatement.executeUpdate();

float sal = callableStatement.getFloat(2);

System.out.println("Result...."+sal);

callableStatement.close();

connection.close();

System.out.println("Connection closed suceccsully.....");

 }

create or replace procedure insert11(id IN number,name IN varchar2, sal IN number)

as

BEGIN

insert into emp values(id,name,sal);

END ;

package com.tcs;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class CallableStatementEx2 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection
=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

CallableStatement callableStatement = connection.prepareCall("{call insert11(?,?,?)}");

callableStatement.setInt(1, 11);

callableStatement.setString(2, "raj");

callableStatement.setFloat(3, 50000);

callableStatement.executeUpdate();

System.out.println("data inserted successfully.....");

callableStatement.close();

connection.close();
 System.out.println("Connection closed suceccsully.....");

create or replace procedure getDetails(id IN number, o1 OUT number,o2 OUT varchar,o3 OUT number)

as

BEGIN

select eid,ename,esal into o1,o2,o3 from emp where eid=id;

END getDetails;

package com.tcs;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

public class CallableStatementEx3 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection
=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

CallableStatement callableStatement = connection.prepareCall("{call getDetails(?,?,?,?)}");

callableStatement.setInt(1, 11);

callableStatement.registerOutParameter(2, Types.INTEGER);

callableStatement.registerOutParameter(3, Types.VARCHAR);

callableStatement.registerOutParameter(4, Types.FLOAT);

callableStatement.execute();
 System.out.println("Emp id="+callableStatement.getInt(2));

System.out.println("Emp name="+callableStatement.getString(3));

System.out.println("Emp sal="+callableStatement.getFloat(4));

callableStatement.close();

connection.close();

System.out.println("Connection closed suceccsully.....");

ex-4:

PL/SQL : write a procedure to take sal input gives the multiple records as output. sal>10000

IN : 1 : number

OUT : 1 : CURSOR

JDBC :

call the procedure give the input & store the all records & print it.

ex-5 : this function is taking two eid as input : gives avg salary.

create or replace function getAvg(id1 IN number, id2 IN number) return number

as

sal1 number;

sal2 number;

BEGIN

select esal into sal1 from emp where eid=id1;

select esal into sal2 from emp where eid=id2;

return (sal1+sal2)/2;

END getAvg;

package com.tcs;

import java.sql.CallableStatement;
import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

public class CallableStatementEx4 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection
=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("Connection created successfully.....");

CallableStatement callableStatement = connection.prepareCall("{? = call getAvg(?,?)}");

callableStatement.setInt(2, 111);

callableStatement.setInt(3, 222);

callableStatement.registerOutParameter(1,Types.FLOAT);

callableStatement.execute();

System.out.println("Result....."+callableStatement.getFloat(1));

callableStatement.close();

connection.close();

System.out.println("Connection closed suceccsully.....");

ex-6:

PL/SQL : write a function to take sal input gives the multiple records as output. sal>10000

IN : 1 : number

JDBC :

call the function give the input & store the all records & print it.

Transaction : set of operations

main prinicpal

a. do everyting : commit()
 b. do nathing : rollback()

Local Transaction: A local transaction means, all operation in a transaction are executed against one database.

For example; If transfer money from first account to second account belongs to same bank then transaction is local transaction.

Global Transaction: A global transaction means, all operations in a transaction are executed against multiple database.

For Example; If transfer money from first account to second account belongs to different banks then the transaction is a global
transaction.

Note: Jdbc technology performs only local transactions. For global transaction in java we need either hibernate or spring
framework.

Note: In transaction management DDL(create ,alter drop) commands are not allowed.

In JDBC the database autocommit by default : true

but to perfrom transaction management we have to setAutocommit(false)

once if we set autocommit is false then we can use commit() & rollback()

ex-1:

step 1 : declare the resources

step 2 : try block do the operations use commit.

step 3 : catch block use rollback

step 4 : finally block release the resources

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class TxnEx1 {

public static void main(String[] args) {

 Connection connection = null;

Statement statement = null;

try

{ Class.forName("oracle.jdbc.driver.OracleDriver");

connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("connection created successfully…….");

connection.setAutoCommit(false);

statement = connection.createStatement();

statement.executeUpdate("insert into emp values(33,'sriharsha',30000)");

statement.executeUpdate("update emp set esal = esal+1800 where esal>10000");

statement.executeUpdate("delete from emp where eid=111");

connection.commit();

System.out.println("Transaction Successfull....");

catch (Exception e) {

try {

connection.rollback();

System.out.println("Transaction is fail.....");

} catch (SQLException e1) {

e1.printStackTrace();

finally

{ try{

if(statement!=null)statement.close();

if(connection!=null)connection.close();

catch(SQLException e)

{ e.printStackTrace();

System.out.println("Connection closed successfully.....");

}
ex: savepoint

package com.tcs;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Savepoint;

import java.sql.Statement;

public class TxnEx2 {

public static void main(String[] args) {

Connection connection = null;

Statement statement = null;

Savepoint savepoint = null;

try

{ Class.forName("oracle.jdbc.driver.OracleDriver");

connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("connection created successfully…….");

connection.setAutoCommit(false);

statement = connection.createStatement();

statement.executeUpdate("insert into emp values(111,'ratan',10000)");

statement.executeUpdate("insert into emp values(222,'anu',20000)");

savepoint = connection.setSavepoint();

statement.executeUpdate("update emp set esal = esal+100 where esal>10000");

System.out.println(".....");

statement.executeUpdate("delete from emp where eid==888");

System.out.println(".....");

connection.commit();

System.out.println("Transaction Successfull....");

catch (Exception e) {
 try {

connection.rollback(savepoint);

System.out.println("Transaction is fail.....");

} catch (SQLException e1) {

e1.printStackTrace();

finally

{ try{

if(statement!=null)statement.close();

if(connection!=null)connection.close();

catch(SQLException e)

{ e.printStackTrace();

System.out.println("Connection closed successfully.....");

MYSQL :

https://dev.mysql.com/downloads/installer/

default user name : root

password : at the time of install // root

default port number : 3306

mysql> create database raj;

Query OK, 1 row affected (0.13 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| ahmad |

| mysql |
| naren |

| raj |

| ratan |

| test |

+--------------------+

7 rows in set (0.21 sec)

mysql> use raj;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> create table emp(eid int,ename varchar(30),esal decimal(10,5));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into emp values(111,'ratan',10000.45);

Query OK, 1 row affected (0.08 sec)

mysql> insert into emp values(222,'anish',20000.45);

Query OK, 1 row affected (0.03 sec)

mysql> select * from emp;

+------+-------+-------------+

| eid | ename | esal |

+------+-------+-------------+

| 111 | ratan | 10000.45000 |

| 222 | anish | 20000.45000 |

+------+-------+-------------+

2 rows in set (0.00 sec)

ex:

the driver cls jar file name is : mysql-connector.jar

https://dev.mysql.com/downloads/file/?id=496255

package com.tcs;
import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class MySqlEx {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("com.mysql.jdbc.Driver");

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

System.out.println("connection created successfully…….");

Statement statement = connection.createStatement();

int x = statement.executeUpdate("create table student(sid int,sname varchar(30),smarks int)");

System.out.println("Table created successfully....."+x);

connection.close();

System.out.println("Connection closed successfully....");

ex-2:

create product table : pid pname pcost

insert four records

print the records

update the records cost = cost-3 where cost>100

stop the execution 20sec : in thos time check the database is updated or not

drop the table.

MySQL tool

sqlyog

https://en.softonic.com/download/sqlyog/windows/post-download

package com.tcs;
import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class MySqlEx2 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("com.mysql.jdbc.Driver");

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

System.out.println("connection created successfully…….");

Statement statement = connection.createStatement();

ResultSet set = statement.executeQuery("select * from emp");

while(set.next())

System.out.println(set.getInt(1)+" "+set.getString(2)+" "+set.getFloat(3));

connection.close();

System.out.println("Connection closed successfully....");

Assignments

1) Procedure to fetch and print more than 1 records

SQL> create or replace procedure getAllEmp(emps OUT SYS_REFCURSOR)

2 as

3 BEGIN

4 open emps for

5 select * from emp;

6 END;
 7 /

Procedure created.

package com.tcs;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import oracle.jdbc.OracleTypes;

public class CallableStatementEx4 {

public static void main(String[] args) throws SQLException {

Connection con = TestConnection.createConnection();

System.out.println("connection created");

CallableStatement cs = con.prepareCall("{call getAllEmp(?)}");

cs.registerOutParameter(1, OracleTypes.CURSOR);

cs.execute();

ResultSet rs = (ResultSet)cs.getObject(1);

while (rs.next()) {

System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getInt(3));

con.close();

System.out.println("connection close");

}
}

2) Function to get all employees

SQL> create or replace function getAllEmpFunction(enum IN number) return SYS_REFCURSOR

2 as

3 emps SYS_REFCURSOR;

4 BEGIN

5 Open emps for

6 select * from emp where eid > enum;

7 return emps;

8 END;

9 /

Function created.

package com.tcs;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import oracle.jdbc.OracleTypes;

public class CallableStatementEx5 {

public static void main(String[] args) throws SQLException {

Connection con = TestConnection.createConnection();

System.out.println("connection created");
 CallableStatement cs = con.prepareCall("{? = call getAllEmpFunction(?)}");

cs.setInt(2, 666);

cs.registerOutParameter(1, OracleTypes.CURSOR);

cs.execute();

ResultSet rs = (ResultSet)cs.getObject(1);

while (rs.next()) {

System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getInt(3));

con.close();

System.out.println("connection close");

=============================================================

BLOB CLOB : data types in oracle

BLOB : to store the images.

FileInputStream

FileOutputStream

CLOB : to store the text files.

FileReader

FileWriter

ex-1: we will store the image in database.

create table imgtable(name varchar2(20),image blob);

FileInputStream

package com.tcs;
import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class ImgStoreEx {

public static void main(String[] args) throws ClassNotFoundException, SQLException, FileNotFoundException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("connection created successfully.....");

//read the file from HD

File f = new File("Desert.jpg");

FileInputStream inputStream = new FileInputStream(f);

PreparedStatement preparedStatement = connection.prepareStatement("insert into imgtable values(?,?)");

preparedStatement.setString(1, "Desert");

preparedStatement.setBinaryStream(2,inputStream,f.length());

preparedStatement.executeUpdate();

System.out.println("Data inserted successfully.....");

preparedStatement.close();

connection.close();

System.out.println("Connection closed successfully........");

ex-2: we will read the image from the database.

package com.tcs;
import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class ImgReadEx {

public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("connection created successfully.....");

PreparedStatement preparedStatement = connection.prepareStatement("select image from imgtable where

name=?");

preparedStatement.setString(1, "quote");

ResultSet set = preparedStatement.executeQuery();

set.next();

InputStream inputStream = set.getBinaryStream(1);

//write the image to HD : read the image from inputstream write to HD(writer)

FileOutputStream writer = new FileOutputStream("xyz.jpg");

int c;

while((c=inputStream.read())!=-1)

{ writer.write(c);

System.out.println("file writing operations are completed.....");

set.close();

writer.close();

preparedStatement.close();
 connection.close();

System.out.println("Connection closed successfully........");

ex-3: we will store text file in database.

Create table ftable(id number,name clob);

FileReader

package com.tcs;

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileReader;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public class FileStoreEx2 {

public static void main(String[] args) throws ClassNotFoundException, SQLException, FileNotFoundException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("connection created successfully.....");

//read the file from HD

File f = new File("sample.txt");

FileReader reader = new FileReader(f);

PreparedStatement preparedStatement = connection.prepareStatement("insert into ftable values(?,?)");

preparedStatement.setInt(1, 111);

preparedStatement.setCharacterStream(2,reader,f.length());

preparedStatement.executeUpdate();

System.out.println("Data inserted successfully.....");

 preparedStatement.close();

connection.close();

System.out.println("Connection closed successfully........");

ex-4: we will read the text file from database.

package com.tcs;

import java.io.FileWriter;

import java.io.IOException;

import java.io.Reader;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class fileReadEx {

public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("connection created successfully.....");

PreparedStatement preparedStatement = connection.prepareStatement("select name from ftable where id=?");

preparedStatement.setInt(1, 111);

ResultSet set = preparedStatement.executeQuery();

set.next();

Reader reader = set.getCharacterStream(1);

//write the file to HD : read the file from reader write to HD(Filewriter)

FileWriter writer = new FileWriter("ratan.txt");

 int c;

while((c=reader.read())!=-1)

{ writer.write(c);

System.out.println("file writing operations are completed.....");

set.close();

writer.close();

preparedStatement.close();

connection.close();

System.out.println("Connection closed successfully........");

ResultSet vs. RowSet:

by default Read only & forward only

java.sql

not implements Serializable

RowSet:

by default Scrollable & updatable

javax.sql

it implements Serializable iterfaces.

ex-1: RowSet with Scrollable.

package com.tcs;

import java.sql.SQLException;

import javax.sql.rowset.JdbcRowSet;

import javax.sql.rowset.RowSetFactory;

import javax.sql.rowset.RowSetProvider;

public class RowSetEx1 {

public static void main(String[] args) throws SQLException {

 RowSetFactory factory = RowSetProvider.newFactory();

JdbcRowSet rowSet = factory.createJdbcRowSet();

//JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();

rowSet.setUrl("jdbc:oracle:thin:@localhost:1521:xe");

rowSet.setUsername("system");

rowSet.setPassword("ratan");

rowSet.setCommand("select * from emp");

rowSet.execute();

rowSet.afterLast();

while(rowSet.previous())

{ System.out.println(rowSet.getInt(1)+" "+rowSet.getString(2)+" "+rowSet.getFloat(3));

rowSet.first();

System.out.println(rowSet.getInt(1));

rowSet.last();

System.out.println(rowSet.getInt(1));

rowSet.absolute(3);

System.out.println(rowSet.getInt(1));

rowSet.close();

ex-2: Assignment : write the example rowset with updatable.

Batch operations:

ex: statement object batch operations

package com.tcs;
import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class BatchEx1 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("connection created successfully..........");

Statement statement = connection.createStatement();

String q1="create table emp2(eid number, ename varchar2(30), esalary number)";

String q2="insert into emp2 values(11,'vishwada',10000)";

String q3="insert into emp2 values(22,'ajay',15000)";

String q4="update emp2 set esalary=esalary+500 where esalary>6000";

String q5="drop table emp2";

statement.addBatch(q1);

statement.addBatch(q2);

statement.addBatch(q3);

statement.addBatch(q4);

statement.addBatch(q5);

int[] result = statement.executeBatch();

for(int res : result)

{ System.out.println(res);

ex-2: PreparedStatement with batch operations.

package com.tcs;
import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class BatchEx1 {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

System.out.println("connection created successfully..........");

Statement statement = connection.createStatement();

String q1="create table emp2(eid number, ename varchar2(30), esalary number)";

String q2="insert into emp2 values(11,'vishwada',10000)";

String q3="insert into emp2 values(22,'ajay',15000)";

String q4="update emp2 set esalary=esalary+500 where esalary>6000";

String q5="drop table emp2";

statement.addBatch(q1);

statement.addBatch(q2);

statement.addBatch(q3);

statement.addBatch(q4);

statement.addBatch(q5);

int[] result = statement.executeBatch();

for(int res : result)

{ System.out.println(res);

Note : in batch operations it is not possible to add the select query.

ex:
package com.tcs;

import java.awt.Button;

import java.awt.Color;

import java.awt.FlowLayout;

import java.awt.Font;

import java.awt.Frame;

import java.awt.Graphics;

import java.awt.Label;

import java.awt.TextField;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.awt.event.WindowAdapter;

import java.awt.event.WindowEvent;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

class SearchFrame extends Frame

{ Label l;

TextField tf;

Button b;

Connection con;

Statement st;

ResultSet rs;

SearchFrame()

{ try

{ this.setVisible(true);

this.setSize(500,400);

this.setBackground(Color.pink);

this.setTitle("JDBC - AWT Application");

this.addWindowListener(new WindowAdapter() {

@Override

public void windowClosing(WindowEvent e) {

System.exit(0);
 }

});

this.setLayout(new FlowLayout());

l=new Label("Enter Emp Id:");

tf=new TextField(15);

b=new Button("Search");

this.add(l);

this.add(tf);

this.add(b);

Class.forName("oracle.jdbc.driver.OracleDriver");

con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","ratan");

st=con.createStatement();

b.addActionListener(new ActionListener() {

@Override

public void actionPerformed(ActionEvent e) {

try

String q = "select * from emp where eid='"+tf.getText()+"'";

System.out.println(q);

rs = st.executeQuery("select * from emp where eid='"+tf.getText()+"'");

repaint(); //it will call paint method

catch (Exception e1)

{ e1.printStackTrace();

});

catch (Exception e)

{ e.printStackTrace();

}
 }

public void paint(Graphics g)

{ try

Font f=new Font("arial",Font.BOLD,30);

g.setFont(f);

boolean b = rs.next();

if(b==true)

{ g.drawString("emp id....."+rs.getInt(1),50,100);

g.drawString("emp Name....."+rs.getString(2),50,150);

g.drawString("emp sal....."+rs.getInt(3),50,200);

else

{ g.drawString("emp does not exists",50,150);

catch (Exception e)

{ e.printStackTrace();

public class Test

{ public static void main(String[] args)

{ new SearchFrame();

ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE

Assignment-1
package com.Advjava.jdbc;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class RsEx5 {

public static void main(String[] args) throws SQLException, InterruptedException {

Connection connection = TestConnection.createConnection();

Statement statement =
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

String q = "select * from student_detail";

ResultSet set = statement.executeQuery(q);

while (set.next())

{ System.out.println(set.getInt(1)+" "+set.getString(2)+" "+set.getInt(3));

System.out.println("Then wait for 25 seconds...");

Thread.sleep(25000);

set.beforeFirst();

while (set.next())

{ System.out.println(set.getInt(1)+" "+set.getString(2)+" "+set.getInt(3));

set.close();

connection.close();

statement.close();

System.out.println("Connection closed successfully...");

}
ASSIGNMENT-2

ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE

package com.Advjava.jdbc;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class RsEx6 {

public static void main(String[] args) throws SQLException, InterruptedException {

Connection connection = TestConnection.createConnection();

Statement statement =
connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

String q1 = "select STUDENT_ID,STUDENT_NAME,SCORE from student_detail";

ResultSet set = statement.executeQuery(q1);

set.absolute(5);

set.updateInt(1,100604);

set.updateString(2,"Bhav");

set.updateInt(3,98);

set.updateRow();

System.out.println("Record updated successfully...");

String q2= "insert into student values(1006060,'Megha',90)";

int a = statement.executeUpdate(q2);

System.out.println("Records inserted successfully..."+a);

 System.out.println("Wait for 30 seconds...");

Thread.sleep(30000);

String q3 = "delete from student_detail" +

"where student_id=1006047";

int b = statement.executeUpdate(q3);

System.out.println("Row deleted successfully....");

set.close();

connection.close();

statement.close();

System.out.println("Connection closed successfully...");