JDBC

clutteredreverandΔιαχείριση Δεδομένων

31 Οκτ 2013 (πριν από 3 χρόνια και 8 μήνες)

121 εμφανίσεις

UNIT
-
VIII

JDBC

Introducing JDBC : Java Data Base Connectivity


What is JDBC



Java SE Technologies provides Database Integrity


through



Java DB



Java DB i s Sun's supported di stri buti on of the open source Apache Derby database.



Its ease of use, standards compl i ance, ful l feature set, and smal l footpri nt make i t the i deal database for
Java devel opers.



Java DB i s wri tten i n the Java programmi ng l anguage,

Java Data Objects (JDO)



The Java Data Objects (JDO) API i s a standard i nterf
ace
-
based Java model abstracti on of persi stence.



Appl i cati on programmers can use JDO technol ogy to di rectl y store Java domai n model i nstances i nto the
persi stent store (database).

The Java Database Connectivity (JDBC)



The Java Database Connecti vi ty (JDBC)

API i s the i ndustry standard for database
-
i ndependent connecti vi ty

between the Java programmi ng l anguage and



A wi de range of databases SQL databases and other tabul ar data sources, such as spreadsheets or fl at
fi l es.



The Java Database Connecti vi ty
(JDBC) API provi des uni versal data access from the Java programmi ng
l anguage.



Usi ng the JDBC 3.0 API, you can access vi rtual ly any data source, from rel ati onal databases to
spreadsheets and fl at fi l es.



The
JDBC 3.0 API

i s compri sed of two packages:



the
jav
a.sql

package



the
javax.sql

package, whi ch adds server
-
si de capabil ities

J
J
D
D
B
B
C
C


i
i
s
s


a
a
n
n


A
A
P
P
I
I


f
f
o
o
r
r


a
a
c
c
c
c
e
e
s
s
s
s
i
i
n
n
g
g


d
d
a
a
t
t
a
a
b
b
a
a
s
s
e
e
s
s


i
i
n
n


a
a
n
n


u
u
n
n
i
i
f
f
o
o
r
r
m
m


w
w
a
a
y
y
.
.






JDBC Versions


JDBC Versions available with different version of JDK as under.

JDK Version

JDBC Version

JDK 1.0

JDBC

JDK 1.1

JDBC 1.0 API

JDK 1.2



JDBC 2.0 API



JDK 1.3

JDK 1.4

JDBC 3.0

JDK 1.5

JDBC 4.0

JDK 1.6

JDK 1.7



JDBC Architecture



JDBC Architecture provides the mechanism to translate Java Statements into SQL statements


The JDBC API has two levels of interface:



Application
Layer :
-

Appl i cati on Layer al l ows the devel oper to make cal l s to the database vi a SQL and retri eve the resul ts.



Driver Layer :
-


The Dri ver Layer handl es communi cati on wi th a speci fi c dri ver i mpl ementati on.




JDBC Drivers


and its Type



JDBC Drivers

:



It Convert SQL Statements i nto a form that a certai n database can i nterpret.



It al so Retri eve the resul t of SQL Statements and convert the resul t i nto equi val ent JDBC API Object.



There are Four Types of Driver :

o

JDBC
-
ODBC Bridge Driver

o

Native
API/Partly
-
Java Driver

o

JDBC
-
Net Pure Java Driver

o

Native
-
Protocol Pure Java Driver


Type 1 Driver :
-



Requi res ODBC dri ver to be i nstal led i n cl i ent Machi ne.


Type 2 Driver :



Requi res ODBC dri ver to be i nstal led i n cl i ent Machi ne.



i t converts JDBC cal l s

to nati ve API Cal l s of the database


Type 3 Driver :







Type 4 Driver :



Thi s i s al l Java Pure Dri ver i mpl ementati ons that
do not

requi res cl i ent Machi ne confi gurati on.






How to Connect with JDBC?



JDBC Connection is just 2 Steps far from
You.



1. Register Driver



Programmatically either

o

thought
forName()

or

o

through
registerDriver()

o

Examples

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

or

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

or

Driver d1=new com.mysql.jdbc.Driver();

DriverManger.registerDriver(d1);

or

DriverManger.registerDriver(new
com.mysql.jdbc.Driver());



Manually

o

By Setting System Property

o

Examples

c:
\
> java


-
Djdbc.drivers=com.mysql.jdbc.Driver


myFirstJDBCProg





2. Get Connection

The JDBC URL i s a stri ng that provi des a way of i denti fyi ng a database.

Syntax for Database URL :

<protocol>:<subprotocol>:<subname>



<protocol > i n a JDBC URL i s al ways
jdbc



<subprotocol > i s the name of the database connecti vi ty mechani sm.



i f

i t i s ODBC
-
JDBC bri dge, the subprotocol must be odbc and i t i s type1 dri ver.



<subname
>

i s used to i denti fy the database


Some Other Driver Details are as Under:
-

RDBMS

JDBC Driver Name

Mysql

Driver Name

com.mysql.jdbc.Dri ver

Database URL format :



jdbc
:mysql://hostname/databaseName

Eg.

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

Connecti on
c1=Dri verManager.getConnecti on("
jdbc
:mysql://l ocal host:3306/abcDB","root","admi n");

Oracle

Driver Name:

oracl e.jdbc.dri ver.OracleDri ver

Database URL format :




jdbc
:oracl e:thi n@hostname:portnumber:databaseName

Eg.

Driver d1=new oracle.jdbc.driver.OracleDriver();

DriverManager.registerDriver(d1);

Connecti on
c1=Dri verManager.getConnecti on(“
jdbc
:oracl e:thi n:@
192.168.1.10:1521
:
orcl
”,”scott
”,”ti ger”);

MSAccess

Driver Name:

com.jdbc.odbc.JdbcOdbcDri ver

Database URL format:




jdbc
:odbc:databaseName

DB2

Driver Name:

COM.i bm.db2.jdbc.net.DB2Dri ver

Database URL format:




jdbc
:db2:hostname:portnumber/databaseName

Derby

Driver Name:

org.apache.derby.jdbc.EmbeddedDri ver

Database URL format:




jdbc
:derby://l ocal host:1527/sampl e

Eg.

Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();

Connecti on conn= Dri verManager.getConnecti on("
jdbc
:derby:C:
\
\
Pr
ogram
Fi l es
\
\
Sun
\
\
JavaDB
\
\
demo
\
\
databases
\
\
toursdb");

Microsoft

SQL
Server

Dri ver Name:

com.mi crosoft.jdbc.sql server.SQLServerDri ver

Database URL format:



jdbc
:sql server://l ocal host;user=MyUserName;password=*****;



jdbc
:sql server://l ocal host:1433;databaseName=AdventureWorks;i ntegratedSecuri ty=true;

Eg.

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();

Connecti on conn= Dri verManager.getConnecti on("
jdbc
:sql server://l ocal host:1433;

databaseName=Ad
ventureWorks;i ntegratedSecuri ty=true;");

Note :
-

For this you need to download the Jar file.

Methods in java.sql.Connection

Sel ected Methods i n
java.sql.Connection

Method

Purpose

Statement


createStatement()

Returns a statement object that i s used to
send SQL to the
database.

PreparedStatement


preparedStatement(Stri ng
sql )

Returns an object that can be used for sendi ng parameteri zed
SQL statements.

CallableStatement




prepareCal l (Stri ng sql )

Returns an object that can be used for cal l i ng stored
procedures.

DatabaseMetaData


getMetaData()

Gets an object that suppl i ed database confi gurati on i nformation.

boolean


i sCl osed()

Reports whether the database i s currentl y open or not.

void

commi t()

Makes al l changes permanent si nce previ ous commi t.rol l b
ack.

void

rol l back()

Undoes and di scards al l changes done si nce the previ ous
commi t/rol l back.

void

setAutoCommi t
(bool ean yn)

Restores/removes auto
-
commi t mode, whi ch does an automati c
commi t after each statement.


The defaul t case i s AutoCommi t i s
on.

void

cl ose()

Cl oses the connecti on and rel eases the JDBC resources for the
connecti on.

boolean

i sReadOnl y()

Retri eves whether thi s Connecti on object i s i n read
-
onl y mode.

void

setReadOnl y(bool ean yn)

Puts thi s connecti on i n read
-
onl y mode as a hi nt to th
e dri ver to
enabl e database opti mi zati ons.



Classes for sending SQL Statements



JDBC API provides three classes for sending SQL Statements to Database.

1.

Statement Object

2.

PreparedStatement Object

3.

CallableStatement Object

Statement :



It i s used to execute queri es



It Can be created by cal l ing the
createStatement()

method from the Connecti on object.



Has three methods that can be used for queryi ng a database:

o

The Resul tSet

executeQuery()

method executes a
simple select query
and returns a si ngl e
ResultSet

object

o

The i nt

executeUpdate()

method executes the DML SQL
INSERT
,
UPDATE

and
DELETE

statements

o

The bool ean

execute()

method executes a SQL statement that may return true or fal se and can
be use when creati ng tabl e.

PreparedStatement :



Can be created by cal l ing the
preparedStatement()

method from the Connecti on object



Is used for sendi ng queri es wi th
INPUT

parameters



Tak
es an SQL statement as a parameter



The SQL statement contai ns
placeholders

( ? )

that can be repl aced by
INPUT

parameters

CallableStatement :



Can be created by cal l ing the
prepareCall()

method from the Connecti on object



Contai ns the functi onal i ty of cal l ing a
stored procedure



Is used for queri es wi th INPUT as wel l as OUTPUT parameters of
Stored Procedure.

ResultSet:



Is generated as a resul t of executi ng a statement



Provi des getXXX() methods to access data from the tabl e w
here XXX refers to the data type of the
parameter



Mai ntai ns a cursor poi nting to i ts current row of data



It has the method next() to move to the next row



It has the method previ ous() to move the previ ous row.

Examples

How to Select


all

Records from Customer Table:
-

Use Statement and executeQuery()






How to Select


specific Record from Customer Table:
-

Use PreparedStatement and executeQuery()






How to insert


Record in Customer Table:
-

Use PreparedStatement and executeUpdate()







How to delete Records from Customer Table
-

Use PreparedStatement and executeUpdate()






How to update Records from Customer Table:
-

Use PreparedStatement and executeUpdate()






How to Select


all

Records from procedure based on Customer Table:
-
Use CallableStatement and executeQuery()




How to Select


Column name , table name etc from Customer Table:
-

Use ResultSetMetaData and
getMetaData()















How to Select


Database

information of any Connection :
-

Use DatabaseMetaData and getMetaData()