8/8/2019 236607 JDBC

1/38

8/8/2019 236607 JDBC

2/38

8/8/2019 236607 JDBC

3/38

8/8/2019 236607 JDBC

4/38

8/8/2019 236607 JDBC

5/38

8/8/2019 236607 JDBC

6/38

8/8/2019 236607 JDBC

7/38

8/8/2019 236607 JDBC

8/38

8/8/2019 236607 JDBC

9/38

8/8/2019 236607 JDBC

10/38

10

1. Loading the Driver

Class.forName( com.mysql.jdbc.Driver );Class.forName loads t he given class dynamically

When t he driver is loaded, it a utomaticallycreates an instance of itself registers t his instance wit hin DriverManager

Anot her way:Driver driver = new com.mysql.jdbc.Driver ();DriverManager.registerDriver(driver);MySql JDBC driver can be downloaded from

here .

8/8/2019 236607 JDBC

11/38

8/8/2019 236607 JDBC

12/38

12

2. Define t he connection U RL

Every database is identified by a U RLGiven a U RL, DriverManager looks for t he

driver t hat can talk to t he correspondingdatabaseDriverManager tries all registered drivers, untila s uitable one is fo und

8/8/2019 236607 JDBC

13/38

8/8/2019 236607 JDBC

14/38

14

An Example

// A driver for imaginary1Class.forName( "ORG.img.imgSQL1.imaginary1Driver"

); // A driver for imaginary2 Driver driver = new

ORG.img.imgSQL2.imaginary2Driver ();

DriverManager.registerDriver(driver); // A driver for PostgreSQLClass.forName( "org.postgresql.Driver" );

8/8/2019 236607 JDBC

15/38

15

3 . Establis h t he connection

Connection con =DriverManager.getConnection(" jdbc:imaginaryDB1

");

8/8/2019 236607 JDBC

16/38

8/8/2019 236607 JDBC

17/38

8/8/2019 236607 JDBC

18/38

8/8/2019 236607 JDBC

19/38

19

5 . Exec ute a q uery using t he Statement

exec uteQ uery ret urns a Res ultSet ob ject representing t he q uery res ult (disc ussed later )

8/8/2019 236607 JDBC

20/38

8/8/2019 236607 JDBC

21/38

8/8/2019 236607 JDBC

22/38

22

6 . Process t he res ult

We will disc uss Res ultSet in a w hile

8/8/2019 236607 JDBC

23/38

8/8/2019 236607 JDBC

24/38

24

7 . Close t he connection

Close Connections, Statements, andRes ult Sets

con.close();stmt.close();rs.close()

8/8/2019 236607 JDBC

25/38

8/8/2019 236607 JDBC

26/38

26

R esultSet

R esultSet ob jects provide access to t hetables generated as res ults of exec utingStatement q ueries.Only one R es ultSet per Statement can beopen at a given time!T he table rows are retrieved in seq uence:

A R esultSet maintains a cursor pointing toits current row.next() moves t he cursor to t he next row

8/8/2019 236607 JDBC

27/38

27

R esultSet M ethodsboolean next()

Activates t he next rowFirst call to next() activates t he first rowRet urns false if t here are no more rowsNot all of t he next calls act ually involve t he DB

void close()Disposes of t he Res ultSet

Allows to re- use t he Statement t hat created it Automatically called by most Statement met hods

Type getType(int col umnIndex)

Ret urns t he given field as t he given typeIndices start at 1 and not 0! Add t he col umn name as a comment if it is known!

Type getType(String col umnName)Same, b ut uses name of field

int findCol umn(String col umnName)

Looks up col umn index given col umn name

8/8/2019 236607 JDBC

28/38

28

T imeo ut

Use setQ uery T imeO ut(int seconds) of Statement to set a timeo ut for t he driver to

wait for a q uery to be completed.If t he operation is not completed in t he giventime, an SQLException is t hr ownWha t is it good fo r ?

8/8/2019 236607 JDBC

29/38

29

M apping Java Types to SQL Types

8/8/2019 236607 JDBC

30/38

3 0

Null Values

In SQL, NULL means t he field is emptyNot t he same as 0 or !

In JDBC, yo u must explicitly ask if t he last read field was n ullR esultSet.w asN ull(column)

For ex am ple, getInt(colu mn) w ill retur n 0 if

t he v al ue i s eit her 0 or N ULL!

8/8/2019 236607 JDBC

31/38

3 1

Database TimeT imes in SQL are notorio usly non-standardJava defines t hree classes to help java.sql.Date

y ear, month, da y ja va .sql. T im e

h o u r s, m in u t es , sec ond s ja va .sql. T im es tam p

y ear, mont h , da y , h o u r s, m in u t es , sec ond s, nano sec ond sUsu a lly use t his on e

8/8/2019 236607 JDBC

32/38

3 2

Exceptions

An SQLException is act ually a list of exceptions

8/8/2019 236607 JDBC

33/38

33

Prepared Statements

T he PreparedStatement ob ject contains not just an SQL statement, b ut an SQL statement

t hat has been precompiled.T his means t hat w hen t he PreparedStatement isexec uted, t he DBMS can just r un t hePreparedStatement SQL statement wit hout havingto compile it first.

Most often used for SQL statements t hat takeparameters.

8/8/2019 236607 JDBC

34/38

3 4

Creating a PreparedStatement Ob ject

As wit h Statement ob jects, yo u createPreparedStatement ob jects wit h aConnection met hod.T he following code create aPreparedStatement ob ject t hat takes

two inp ut parameters:

8/8/2019 236607 JDBC

35/38

3 5

Supplying Val ues forPreparedStatement Parameters

Yo u need to s upply val ues to be usedin place of t he q uestion markplace holders (if t here are any) beforeyou can exec ute a PreparedStatement ob ject. Yo u do t his by calling one of t he

setXXX met hods defined in t hePreparedStatement class.

8/8/2019 236607 JDBC

36/38

3 6

Example

t he following line of code sets t he first q uestion markplace holder to a Java int wit h a val ue of 75 :

updateSales.setInt(1, 75 );

T he next example sets t he second place holder

parameter to t he string " Colombian":

updateSales.setString( 2, "Colombian");

8/8/2019 236607 JDBC

37/38

3 7

Anot her Example

8/8/2019 236607 JDBC

38/38

3 8

Callable Statements

Execute a call to a database storedproced ure.