JDBC | java-key-points

JDBC(Java Database Connectivity)

INTRODUCTION

Every real-time application contains the following logics

Presentation logic (HTML, SERVLETS, JSP)
- Creating screens for accepting input values of a client
Business logic (CORE JAVA CONCEPTS)
- Reading input values and process the given input based on business rules
Persistence logic(JDBC)
- Responsible for transferring the data generated by a business logic into a permanent area like a file or database

File as a backend

Files which are used as backend are generally called as flat files
Flat file means, it is a file which is no way related with any programming language or any technology
We can transfer data in the form of text (i/o streams) or in the form of objects (serialization)

Limitations

Don’t have any query language support
Doesn’t support relationship between the data
Less secured

Why JDBC?

Initial days, database vendor provided functions (API) are used to for communicating with a database
The applications which are using the vendor API are called vendor dependent applications, it makes applications as database dependent
Example, for connecting C or C++ programs with oracle database, we use the functions given by oracle in header file called orcl.h
To solve database dependency problem a community was formed with professionals of different MNC’s called X/open
X/open created a set of rules for transferring commands to a database and for reading results from a database in a database independent manner
By using X/open CLI (Call Level Interface), Microsoft and Simba technologies jointly created ODBC (Open Database Connectivity) API

ODBC API

ODBC api is database independent for connecting front end application with different databases

Limitations of ODBC along work with java

ODBC is a C-API with pointers coding (incomparability)
Non pointer code will be converted to pinter code when a request is going to database and pointer code is converted into non pointer code when a response is coming from a database. This conversion makes application execution very slow
ODBC api is platform dependent

In order to overcome the problems of java and ODBC combination, SUN MICRO SYSTEM created its own API as JDBC API

WHAT IS JDBC?

JDBC is an API specification given by Sun Micro System, for developing java applications to communicate with database

JDBC API contains the following 2-packaes, with ore number of interfaces and less number of classes

java.sql
javx.sql

JDBC API is a part of Java API. It means JDBC API comes along with jdk software

JDBC DRIVER

A JDBC Driver is a software which contains a set of classes and acts as a mediator to connect a java application with a database
As java application developer, we need JDBC API and JDBC Driver to store or read the data from a database

JDBC DRIVERS

There are 4 types of JDBC drivers:

JDBC-ODBC bridge driver
Native-API driver (partially java driver)
Network Protocol driver (fully java driver)

4. Thin driver (fully java driver)

JDBC-ODBC BRIDGE DRIVER

This driver is a built-in driver of Java software
The JDBC-ODBC bridge driver translates all JDBC calls into ODBC calls and when getting response ODBC calls are converted back to JDBC calls
This driver is one, to talk with multiple databases. Because it uses internally an ODBC driver belong to a particular database to obtain a connection
The JDBC-ODBC Bridge driver is recommended only for experimental use or when no other alternative is available

Advantages:

easy to use.
can be easily connected to any database.

Disadvantages

Performance degraded because JDBC method call is converted into the ODBC funcion calls.
The ODBC driver needs to be installed on the client machine.

Native-API/partly Java driver

The Native-API/partly Java driver convert JDBC calls into database-specific calls i.e. this driver is specific to a particular database.
It is not written entirely in java

Advantage:

performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

The Native driver needs to be installed on the each client machine.
The Vendor client library needs to be installed on client machine.

All Java/Net-protocol driver

In Java/Net-Protocol driver database requests are passed through the network to the middle-tier server.
The middle-tier then translates the request to the database.
If the middle-tier server can in turn use Type1, Type 2 or Type 4 drivers.

Advantage

This driver is server-based, so there is no need for any vendor database library to be present on client machines.
This driver is fully written in Java and hence Portable. It is suitable for the web.
This driver is very flexible allows access to multiple databases using one driver
They are the most efficient amongst all driver types.

Disadvantage

It requires another server application to install and maintain. Traversing the record set may take longer, since the data comes through the backend server.

Native-protocol/all-Java driver

The thin driver converts JDBC calls directly into the vendor-specific database protocol.
That is why it is known as thin driver. It is fully written in Java language.

Advantage

The major benefit of using this drivers are that they are completely written in
Java to achieve platform independence and eliminate deployment administration issues.
It is most suitable for the web.
Number of translation layers is very less

Disadvantags

With type 4 drivers, the user needs a different driver for each database.

Basic Steps in Using JDBC

Load the driver
Define the Connection URL
Establish the Connection
Create a Statement object
Execute a query
Process the results
Close the connection

Load the Driver

Loading a JDBC driver is nothing but creating an object of JDBC driver class and registering that driver object with DriverManger service
DriverManager is a service which holds all the loaded driver class objects
To load a JDBC driver class into JVM at run time, we use a method forName() of Class class.

Sample Code

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

Here, “sun.jdbc.odbc” is package name and JdbcOdbcDriver is a class name, It is a Sun MicroSystems developed driver

Define the Connection URL

We need to supply the database URL for connectivity purpose. This URL is known as connection string. When we are using Sun driver, connecting string will be in the following form

String cs=“jdbc:odbc:dsn”;

DataSourceName we have configure externally.
After configuring, we give some name to the configuration. That name we use in the connection string

Establish the Connection

Java program calls the following method on the DriverManager to get the database connection

Connection con=DriverManager.getConnection(cs,”username”,”password”);

Create a Statement object

Statement st =con.createStatement();

“st” is the JDBC object used to submit SQL statements from the java program.

It has two important methods

executeUpdate() [UPDATE, INSERT, DELETE]
executeQuery() [select]

Execute a query

String query ="SELECT col1, col2, col3 FROM sometable";

ResultSet resultSet =statement.executeQuery(query);

Process the results

while(resultSet.next())

{

System.out.println(resultSet.getString(1) + " " +

resultSet.getString(2) + " " +

resultSet.getString(3));

}

– First column has index 1, not 0

Close the connection

connection.close();

Since opening a connection is expensive, postpone this step if additional database operations are expected

The following examples based on type-1 driver and Statement interface

Example1: Create a table

import java.sql.*;

public class CreateTable {

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

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

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

Statement st=con.createStatement();

int x=st.executeUpdate("create table java911(sno number(5), sname varchar2(10))");

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

}}

Example2: insert data into table

import java.sql.*;

public class CreateTable {

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

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

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

Statement st=con.createStatement();

int x=st.executeUpdate("insert into java911 values(1,'ram')");

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

Example3: update table data

import java.sql.*;

public class CreateTable {

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

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

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

Statement st=con.createStatement();

int x=st.executeUpdate("update java911 set sname='raju' where sno=1");

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

}}

Example 4: delete table data

import java.sql.*;

public class CreateTable {

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

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

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

Statement st=con.createStatement();

int x=st.executeUpdate("delete from emp_info where eno=1");

System.out.println("data updated");}}

Example 5: select table data

import java.sql.*;

public class SelectData {

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

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

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

Statement st=con.createStatement();

String query="select *from sku_data";

ResultSet rs=st.executeQuery(query);

while(rs.next())

{

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

}

System.out.println("End of Data");

}}

Example6: Getting the connection properties from other class and do all database operations using user defined methods

Connection given class

import java.sql.*;

public class GetConnection {

public static Connection getConnection() throws ClassNotFoundException, SQLException{

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

Connection con=DriverManager.getConnection("jdbc:odbc:java911","system","manager"); return con;

}

Connection getting class

import java.sql.*

public class MethodOperations {

Statement st;.

public Connection gettingConnection() throws ClassNotFoundException, SQLException{

Connection con=GetConnection.getConnection();

return con;

}

public int insertData() throws ClassNotFoundException, SQLException {

Connection con=gettingConnection();

st=con.createStatement();

int x=st.executeUpdate("insert into emp_info values(111,'sprint',1000)");

System.out.println("DataInserted");

return x;

}

public int updateData() throws ClassNotFoundException, SQLException{

Connection con=gettingConnection();

st=con.createStatement();

int x=st.executeUpdate("update emp_info set sal=2000 where eno=111");

System.out.println("DataUpdated");

return x;

}

public int deleteData() throws ClassNotFoundException, SQLException {

Connection con=gettingConnection();

st=con.createStatement();

int x=st.executeUpdate("delete from emp_info where eno=111");

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

return x;

}

public ResultSet selectData() throws ClassNotFoundException, SQLException{

Connection con=gettingConnection();

st=con.createStatement();

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

while(rs.next()){

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

}

return rs;

}

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

MethodOperations m=new MethodOperations();

m.gettingConnection();

m.insertData();

m.updateData();

m.deleteData();

m.selectData();

}

Example7: Insert values dynamically into a table

import java.sql.*;

import java.util.Scanner;

public class DynamicInsert {

Statement st=null;

public int insertData() throws ClassNotFoundException, SQLException{

int k = 0;

Scanner sc=new Scanner(System.in);

System.out.println("Enter Student Id");

int n=sc.nextInt();

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

String str=sc.next();

System.out.println("Enter Student Marks");

int m=sc.nextInt();

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

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

st=con.createStatement();

k=st.executeUpdate("insert into sku_data values("+n+",' "+str+" ',"+m+")");

System.out.println(k+"rows is inserted-----");

st.close();

con.close();

return k;

}

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

DynamicInsert di=new DynamicInsert();

di.insertData();

}}

Example8: getting connection without using DriverManager class

import java.sql.*;

import java.util.Properties;

import sun.jdbc.odbc.JdbcOdbcDriver;

public class WithoutDM {

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

Properties p=new Properties();

p.put("user", "system");

p.put("password","manager");

JdbcOdbcDriver jd=new JdbcOdbcDriver();

Connection con=jd.connect("jdbc:odbc:oradsn",p);

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

con.close();

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

}}

Example 9: execute method of Statement interface

import java.sql.*

import java.util.Scanner;

public class ExecuteDemo {

Statement st=null;

boolean b;

ResultSet rs=null;

public void executeDemo()throws Exception{

Connection c=TestConnection.getConnection();

st=c.createStatement();

Scanner sc=new Scanner(System.in);

System.out.println("Enter The SQL Command");

String str=sc.nextLine();

boolean b=st.execute(str);

if(b){

ResultSet rs=st.getResultSet();

while(rs.next()) {

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

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

}

else{

int k = 0;

k = st.getUpdateCount();

System.out.println(k+"RowsEffectted");

}//else

st.close();

c.close();

}

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

ExecuteDemo ed=new ExecuteDemo();

ed.executeDemo();

}}

Getting Connection Properties Using Properties File

#talent.properties

jdbc.driver=sun.jdbc.odbc.JdbcOdbcDriver

jdbc.url=jdbc:odbc:oradsn

jdbc.user=system

jdbc.password=manager

This file is stored in Project level

In JDBC Application

Properties p=new Properties();

FileInputStream fin=new FileInputStream("talent.properties");

p.load(fin);

String s1=p.getProperty("jdbc.driver");

String s2=p.getProperty("jdbc.url");

String s3=p.getProperty("jdbc.user");

String s4=p.getProperty("jdbc.password");

Example 10: Getting the connection properties by using Properties file

talent.properties

jdbc.driver=sun.jdbc.odbc.JdbcOdbcDriver

jdbc.url=jdbc:odbc:oradsn

jdbc.user=system

jdbc.password=manager

import java.io.*;

import java.sql.*;

import java.util.Properties;

public class ResourceBundle {

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

Properties p=new Properties();

FileInputStream fin=new FileInputStream("talent.properties");

p.load(fin);

String s1=p.getProperty("jdbc.driver");

String s2=p.getProperty("jdbc.url");

String s3=p.getProperty("jdbc.user");

String s4=p.getProperty("jdbc.password");

Class.forName(s1);

System.out.println(s1+"Driver is Loaded");

Connection con=DriverManager.getConnection(s2,s3,s4);

Statement st=con.createStatement();

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

while(rs.next()) {

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

}

rs.close();

st.close();

con.close();

}}

Statement Interface

Through the Statement object, SQL statements are sent to the database.
For executing a simple SQL statement

Useful Statement methods

executeQuery()

Executes the SQL query and returns the data in a table (ResultSet)

The resulting table may be empty but never null

ResultSet rs=statement.executeQuery("SELECT a, b FROM table");

executeUpdate()

Used to execute for INSERT, UPDATE, or DELETE SQL statements

The return is the number of rows that were affected in the database

int rows =statement.executeUpdate(“INSERT INTO EMP VALUES(1001,’RAM’,800”);

execute()

Used to execute for SELECT or NON-SELECT SQL statements

Limitation of Statement interface

It is not possible to insert binary data into database
Set dynamic values to an sql command then we need to write complex query
Statement interface compiles an sql command each time, before it is going to transfer to a database. Even though the same command is executing for multiple times, the Statement interface compiles it every time.

PreparedStatement Interface

It is extended from Statement interface
It supports binary data columns of the database
PreparedStatement create dynamic sql quires very easily by putting ‘?’symbols
PreparedStatement interface compiles sql command for once and then it can be executed for any number of times by without recompiling that command again

PreparedStatement pst=con.prepareStatement(“sql command”);

pst-contains compiled code of SQL command

To make an sql command as dynamic, we put ‘?’ symbols in place of values in an sql command. This ‘?’ symbol is called as a place resolution operator or a replacement operator

PreparedStatement pst=con.prepareStatement(“insert into values sku_data values(?,?,?)”);

To set values into each ‘?’ symbol place, we need to call setXXX() methods

pst.setInt(1,101);

pst.setString(2,”abc”);

pst.setInt(3,500);

int k=pst.executeUpadate();

Example 11: Insert values into table dynamically by using PrepareStatement interface

import java.sql.*;

import java.util.Scanner;

public class PreDynamic {

public int insertData() throws SQLException, ClassNotFoundException{

int k = 0;

Scanner sc=new Scanner(System.in);

System.out.println("Enter Student Id");

int n=sc.nextInt();

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

String str=sc.next();

System.out.println("Enter Student Marks");

int m=sc.nextInt();

Connection con=GetConnection.getConnection();

PreparedStatement stmt=con.prepareStatement("insert into sku_data values(?,?,?)");

stmt.setInt(1,n);

stmt.setString(2, str);

stmt.setInt(3, m);

int i=stmt.executeUpdate();

System.out.println(i+" records inserted");

stmt.close();

con.close();

return k;

}

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

PreDynamic di=new PreDynamic();

di.insertData();

}}

Example 12: Geeting records from database by using type-2 driver of oracle and PreapredStatement interface

import java.sql.*;

public class Type2Connection {

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

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

Connection con=DriverManager.getConnection("jdbc:oracle:oci:@XE","system","manager");

String s="select * from student_info where sid=?";

PreparedStatement st=con.prepareStatement(s);

st.setInt(1,2);

ResultSet rs=st.executeQuery();

while(rs.next()){

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

}

rs.close();

st.close();

con.close();

}}

Example 13: Working with oracle type-4 driver

import java.sql.*;

public class Type4Connection {

public static void main(String[] args) throws ClassNotFoundException, SQLException Class.forName("oracle.jdbc.OracleDriver");

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

String s="select * from student_info where sid=?";

PreparedStatement st=con.prepareStatement(s);

st.setInt(1,2);

ResultSet rs=st.executeQuery();

while(rs.next()) {

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

}

rs.close();

st.close();

con.close();

}}

Insert image into Database table

Inserting a picture into a database table is nothing but storing binary data of a picture in a BLOB type of column in a database table
To insert a picture into a database table, we need to read its binary data using FileInputStream object and then we need to set the binary stream of data into the SQL inset command by using setBinaryStream() method

setBinaryStream(int, inputstream object, int)

Retrieve image from Database table

When we select a picture from a database table then directly a picture doesn’t come, but its binary data will be selected
We need to convert the binary data into a picture, with the help of FileOutputStream class
When we execute a select operation for selecting a picture then the ResultSet object is going to contain binary stream of data and to read it we need to call a method getBinaryStream(int)

Example 14: insert image into database table

import java.io.*;

import java.sql.*;

public class InsertImage {

private Connection con;

private PreparedStatement pst;

public void openConnection() throws ClassNotFoundException, SQLException {

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

con=DriverManager.getConnection("jdbc:oracle:oci:@XE","system","manager");

pst=con.prepareStatement("insert into company_info values(?,?)");

}

public void insertRow() throws SQLException, FileNotFoundException {

pst.setString(1, "talent");

File f=new File("c:\\SonicBigAni.gif");

FileInputStream fis=new FileInputStream(f);

int length=(int)f.length();

pst.setBinaryStream(2, fis,length);

int k=pst.executeUpdate();

System.out.println(k+"rows inserted");

}

public void closeConnection() throws SQLException{

pst.close();

con.close();}

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

InsertImage im=new InsertImage();

im.openConnection();

im.insertRow();

im.closeConnection();

}

Example 15: Retrieve image from database table

import java.io.*;

import java.sql.*;

public class RetrieveImage {

private Connection con;

private PreparedStatement pst;

public void openConnection() throws ClassNotFoundException, SQLException {

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

con=DriverManager.getConnection("jdbc:oracle:oci:@XE","system","manager");

pst=con.prepareStatement("select company_logo from company_info where company_name=?");

}

public void selectImage() throws SQLException, IOException{

pst.setString(1, "talent");

ResultSet rs=pst.executeQuery();

rs.next();

InputStream is=rs.getBinaryStream(1);

FileOutputStream fos=new FileOutputStream("BigAni.gif");

int k;

while((k=is.read())!=-1)

{

fos.write(k);

}

fos.close();

rs.close();

}

public void closeConnection() throws SQLException{

pst.close();

con.close();

}

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

RetrieveImage ri=new RetrieveImage();

ri.openConnection();

ri.selectImage();

ri.closeConnection();

}}

Example 16: insert Date into database table

import java.sql.*;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.Scanner;

public class DateInsert {

public void dateInsert() throws SQLException, ParseException, ClassNotFoundException{

Connection con=TestConnection.getConnection();

PreparedStatement pst=con.prepareStatement("insert into customer_info values(?,?,?)"); Scanner sc=new Scanner(System.in);

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

int cid=sc.nextInt();

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

String cname=sc.next();

System.out.println("Enter Customer Date of Birth(yyyy-mm-dd)");

String dob=sc.next();

pst.setInt(1,cid);

pst.setString(2,cname);

//create SimpleDateFormat class object

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");

//convert string into java.util.Date

java.util.Date d1=sdf.parse(dob);

//convert java.util.Date into java.sql.Date

long ms=d1.getTime();

java.sql.Date d2=new java.sql.Date(ms);

pst.setDate(3,d2);

int k=pst.executeUpdate();

System.out.println(k+"Rows inserted");

pst.close();

con.close();

}

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

DateInsert d=new DateInsert();

d.dateInsert();

}}

Example 17: Retrieve date from database

import java.sql.*;

import java.text.SimpleDateFormat;

public class RetrieveDate {

public void retrieveDate() throws SQLException, ClassNotFoundException{

Connection con=TestConnection.getConnection();

PreparedStatement pst=con.prepareStatement("select dob from customer_info" ); //pst.setInt(1,100);

ResultSet rs=pst.executeQuery();

rs.next();

java.sql.Date d2=rs.getDate(1);

System.out.println(d2);

java.util.Date d1=(java.util.Date)d2;

SimpleDateFormat sdf=new SimpleDateFormat("EEE-MMMM-yyyy");

String str=sdf.format(d1);

System.out.println(str);

rs.close();

pst.close();

con.close();

}

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

// TODO Auto-generated method stub

RetrieveDate rd=new RetrieveDate();

rd.retrieveDate();

}}

BatchUpdates

A batch update is a set of multiple update statements that is submitted to the database for processing as a batch

Methods

addBatch() is used to add individual statements to the batch

public void addBatch(java.lang.String sql)

executeBatch() method is used to start the execution of all the statements

public int[] executeBatch()

Example:

Statement st=con.createStatement();

st.addBatch(“insert into emp values(‘raju’,100);

st.addBatch(“delete from emp where eno=100”);

st.executeBatch();

Example 18: example on batch updates

importjava.sql.*;

classBatchUpdates{

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

String driver="sun.jdbc.odbc.JdbcOdbcDriver";

String cs="jdbc:odbc:oradsn";

String user="system";

String pwd="password";

Class.forName(driver);

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

Connection con=DriverManager.getConnection(cs,user,pwd);

Statement st=con.createStatement();

st.addBatch("insert into student_info values(10,'money',500)");

st.addBatch("update student_info set sname='monday' where sid=666");

st.addBatch("delete from student_info123 where sid=1");

con.setAutoCommit(false);

try{

int k[]=st.executeBatch();

con.commit();

System.out.println("Batch Successfully executed"); }

catch(Exception e){

try{

con.rollback();

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

e.printStackTrace();

}

catch(Exception e1){ }

}

st.close();

con.close();

}}

CallableStatement

PreparedStatement execute select or non-select SQL commands. But a CallableStatement can execute not only SQL commands, but also a procedure or a function of a database
To get a CallableStatement object, we call prepareCall() method given by Connection interface

CallableStatement cst=con.prepareCall(“sql command”);

CallableStatement cst=con.prepareCall(“ {call procedure name/ functionname}”);

Example19: Working with CallableStaatement

SQL Procedure

create or replace procedure experiencepro(eno in number, e out number)

d1 date;

d2 date;

begin

select hire_date into d1 from emp_hire where eno=eno;

select sysdate into d2 from dual;

e:=(d2-d1)/365;

end;

importjava.sql.*;

importjava.util.Scanner;

public class CallableTest {

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

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

Connection con=DriverManag JDBer.getConnection("jdbc:odbc:oradsn","system","password");

CallableStatementcst=con.prepareCall("{call experience(?,?)}");

Scanner sc=new Scanner(System.in);

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

inteno=sc.nextInt();

cst.setInt(1,eno);

cst.registerOutParameter(2,Types.INTEGER);

cst.execute();

int b=cst.getInt(2);

System.out.println(b);

cst.close();

con.close()

}

Transaction Management in JDBC

A Tx is a group of operations, which produces either success or fail result
A Tx produces success if all operations in the group are executed successfully otherwise Tx fails
A Tx follows a principle “All or Nothing”

Types of Transactions

Local transaction

If all the operations are executing against a single database then it is called as local transactions

Global transactions (or) distributed transactions

If a transaction operations are executing against more than one database then it is called as global transactions

JDBC only supports local transactions
If we execute any SQL operation from a JDBC program on a database then by default that operation is permently executed on the database. The reason is, in JDBC, a connection object contains autoCommit mode, and its default value is true
To do Tx management in JDBC, first of all we need to disable autoCommit mode. For this we need to call a method setAutoCommit(false)
If all operations in the group are successfully executed on a database then we need to commit the operations. It means Tx is successfully done
If any operation is failed then we cancel all operations in that group
To commit or rollback a Tx, we need to call either commit() method or rollback() method
Finally the following 3-methods are required to do Tx management in JDBC
- setAutoCommit()
- commit()
- rollback()
In a JDBC program, we use try and catch blocks for executing Tx. We put group of opeartions in try block and we commit the operations in try block. In catch block we rollback the oprations

Example 20: example on Transaction api of JDBC

importjava.sql.*;

public class TransactionTest {

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

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

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

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

Statement st=con.createStatement();

con.setAutoCommit(false);

try{

int i1=st.executeUpdate("insert into student_info values(002,'zyx',999)");

int i2=st.executeUpdate("update student_info set sname='xxx' where sid=100");

int i3=st.executeUpdate("delete from student_info where sid=789"); con.commit();

}

catch(Exception e){

try{

con.rollback();

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

}

catch(Exception e1){ }

st.close();

con.close();

}}}

Types of ResultSets

ResultSet is used to retrieve the data from the table
- ResultSet rs=st.executeQuery(“select * from emp”);
ResultSet object holds the table of records returned from the database.
When the ResultSet is opened, the cursor points to the zero record.
Using the ResultSet methods we have to move the cursor to each record and get the column values

ScrollableResultSet with Statement interface

In earlier version of JDBC API, we can move the cursor across the ResultSet only in forward direction.
In JDBC2.0 API is the ability to move a ResultSet cursor backward as well as forward
- Statement st=con.createStatement(CONST1,CONST2);
- PreparedStaement pst=con.prepareStatement(“command”,type,mode);
- CallableStatement cst=con.prepareCall(“command”,type,mode);
CONST1 may be
- ResultSet.TYPE_SCROLL_SENSITIVE (if any changes are made on database will be automatically effected on ResultSet)(1005)
- ResultSet.TYPE_SCROLL_INSENSITIVE (if any changes are made on database then the changes are not effected ResultSet)(1004)
- ResultSet.TYPE_SCROLL_FORWARD_ONLY (1003)
CONST2 maybe
- ResultSet.CONCUR_READ_ONLY (read the data from the ResultSet object)(1007)

ResultSet.CONCUR_UPDATEABLE( it allows insertion, updating,deletion activities)(1008)

Methods of ScrollableResultSet

afterLast()
previous()
absoulte(+/-)
- rs.absoulte(5); moves cursor to the fifth record from top and rs.absoulte(-5); moves the cursor to the fifth record from bottom
relative(+n/-n)
- rs.relative(5); if the cursor is at 10th record then moves the cursor 15th record and rs.relative(-5) moves the cursor to 5th record
first()
last()
beforeFirst()
next()
getRow(); gives current position of the cursor

Example 21: example on ScrollableResultSet

importjava.sql.*;

public class ScrollableTest {

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

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

Connection con=DriverManager.getConnection("jdbc:odbc:oradsn","system","password");

Statement st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

                ResultSetrs=st.executeQuery("select * from student_info");

                //reading in backward direction

                rs.afterLast();

                while(rs.previous()){                      System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));

                }

                //printing 3rd record

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

                rs.absolute(3);

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

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

                //printing one record back from current place

                rs.relative(-1);

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

                st.close();

                con.close();

        }}

JAVA KEY POINTS