15_JDBC.pptx

(1)

JDBC

(2)

JDBC Driver Types

• JDBC Driver is a software component that enables

java application to interact with the database.There

are 4 types of JDBC drivers:JDBC-ODBC bridge

driver

• Native-API driver (partially java driver)

• Network Protocol driver (fully java driver)

• Thin driver (fully java driver)

(3)

JDBC-ODBC bridge driver

(4)

• Advantages:

easy to use.

can be easily connected to any database.

• Disadvantages:

Performance degraded because JDBC method call is converted into the ODBC function calls.

The ODBC driver needs to be installed on the client machine.

JDBC-ODBC bridge driver

(5)

Native-API driver

(6)

• 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.

Native-API driver

(7)

• _{The Network Protocol driver uses middleware (application server)}

that converts JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in java.

(8)

• _Advantage:

• _{No client side library is required because of application server that}

can perform many tasks like auditing, load balancing, logging etc.

• _{Disadvantages:}

• _{Network support is required on client machine.}

• _{Requires database-specific coding to be done in the middle tier.} • Maintenance of Network Protocol driver becomes costly because it

requires database-specific coding to be done in the middle tier.

Network Protocol driver

(9)

Thin Driver

• The thin driver converts JDBC calls directly into the

(10)

Thin Driver

• Advantage:

• Better performance than all other drivers.

• No software is required at client side or server side. • Disadvantage:

• Drivers depends on the Database.

(11)

Steps in using JDBC

1.Load the JDBC driver

2. Establish the Connection

3. Create a Statement object

4. Execute a query

(12)

1. Load the driver

For JDBC-ODBC Bridge driver, the following code will load it:

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

• You do not need to create an instance of a driver and register

it with the DriverManager because calling Class.forName will do that for you automatically

• it throws an Exception called ClassNotFoundException

(13)

2. Define the URL and getting

connection

Connection con = DriverManager.getConnection(url,

"myLogin", "myPassword");

For JDBC-ODBC Bridge driver, the JDBC URL will start with jdbc:odbc:

rest of the URL is generally your data source name Example jdbc:odbc:studentDSN

//we associated studentDSN with StudentDataBase

myLoginur login name for database

(14)

3 &4 Create a Statement &execute

Statement statement =connection.createStatement();

The statement object is used to execute querries it has three methods

executeQuery(str)execute simple select statements and resturns a resultSet

executeUpdate(str)execute sql insert,update delete statements

execute(str)execute sql statements that return multiple values

ResultSet rs=st,executeQuery(“Select * from emp”);

(15)

5&6 .Process the Results & close

Connection

while(resultSet.next()) {

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

resultSet.getString(3)); }

First column has index 1, not 0

– The ResultSet provides getXxx methods that take a column index or column name and returns the data

– Can also access result meta data (column names, etc.)

Close the Connection

connection.close();

(16)

T I N Y I N T S M A L L I N T I N T E G E R B I G I N T R E A L F L O A T D O U B L E D E C I M A L N U M E R I C B I T C H A R V A R C H A R L O N G V A R C H A R B I N A R Y V A R B I N A R Y L O N G V A R B I N A R Y D A T E T I M E T I M E S T A M P

getByte X x x x x x x x x x x x x

getShort x X x x x x x x x x x x x

getInt x x X x x x x x x x x x x

getLong x x x X x x x x x x x x x

getFloat x x x x X x x x x x x x x

getDouble x x x x x X X x x x x x x

getBigDecimal x x x x x x x X X x x x x

getBoolean x x x x x x x x x X x x x

getString x x x x x x x x x x X X x x x x x x x

getBytes X X x

getDate x x x X x

getTime x x x X x

getTimestamp x x x x x X

getAsciiStream x x X x x x

getUnicodeStream x x X x x x

getBinaryStream x x X

getObject x x x x x x x x x x x x x x x x x x x

An "x" indicates that the getXXX method may legally be used to retrieve the given JDBC type.

An " X " indicates that the getXXX method is recommended for retrieving the given JDBC type.

(17)

Crate the student table

DataBaseStudent_Info

(18)

Creating DSN

Control Panel administrative toolsODBC

(19)

(20)

Creating DSN…

1

2

3

20 Nihar Ranjan Roy

studDSN

(21)

import java.sql.*; public class JDBC {

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

Connection con=null; ResultSet rs=null; Statement st=null; try

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

con=DriverManager.getConnection("jdbc:odbc:studDSN"); }catch(ClassNotFoundException e){System.out.println(e);} st=con.createStatement();

rs=st.executeQuery("select * from stud_info"); while(rs.next())

{

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

(22)

Moving the Cursor in Scrollable

Result Sets

• _{Statement stmt = con.createStatement}(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY );

• _{ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM} COFFEES");

ResultSet Methods • _next()

• previouse()

• _{absolute(2)// from begining} • _{absolute(-1)// from end}

• _{relative(2) //relative to current record} • _{relative(-1) //relative to current record} • _first()

• _last()

• _{isFirst , isLast , isBeforeFirst , isAfterLast}

(23)

Prepared Statements

If you want to execute a Statement object many

times, it will normally reduce execution time to use

a

PreparedStatement

object.

PreparedStatement object contains not just an SQL

statement, but an SQL statement that has been

(24)

Creating prepared statements

PreparedStatement updateSal =

con.prepareStatement( "UPDATE emp SET SAL = ?

WHERE enp_name= ?");

Setting values for prepared statements

updateSales.setInt(1, 7500);//first ?

updateSales.setString(2, “MaxPayne");//second ?

(25)

Auto commit

• When a connection is created, it is in auto-commit mode

 each individual SQL statement is treated as a transaction

and will be automatically committed right after it is executed.

Disabling auto commit

con.setAutoCommit(false); //enable or diable Now want to commit

(26)

When to call the rollback

If you are trying to execute one or more statements

in a transaction and get an

SQLException

, you

should call the method rollback to abort the

transaction and start the transaction all over again

rollback();

(27)

Stored Procedures

• A stored procedure is a group of SQL statements

that form a logical unit and perform a particular

task

SQL Statements for Creating a Stored Procedure

• create procedure SHOW_SUPPLIERS as select

SUPPLIERS.SUP_NAME, COFFEES.COF_NAME

from SUPPLIERS, COFFEES where

(28)

Creating procedures through java

String createProcedure = "create procedure SHOW_SUPPLIERS " + "as " +

"select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from SUPPLIERS, COFFEES " +

"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " + "order by SUP_NAME";

Statement stmt = con.createStatement(); stmt.executeUpdate(createProcedure);

The procedure SHOW_SUPPLIERS will be compiled and stored in the database as a database object that can be called, similar to the way you would call a method.

(29)

Calling a Stored Procedure from

JDBC

• create a CallableStatement object

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery();

(30)

Batch updates

• _{A batch update is a set of multiple update statements that is submitted}

to the database for processing as a batch. Sending multiple update statements to the database together as a unit can, in some situations, be much more efficient than sending each update statement separately

• _{Statement, PreparedStatement, and CallableStatement objects have the}

ability to maintain a list of commands that can be submitted together as a batch.

• _{They are created with an associated list, which is initially empty. You}

can add SQL commands to this list with the method addBatch,

addbatch()

• _{and you can empty the list with the method clearBatch.}

clearBatch()

• _{You send all of the commands in the list to the database with the}

method executeBatch

executeBatch()

(31)

Example of batch updates

con.setAutoCommit(false);

Statement stmt = con.createStatement();

1. stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)"); 2. stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");

3. stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)"); 4. stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

• _{int [] updateCounts = stmt.executeBatch();}

(32)