JDBC / ODBC

1. JDBC / ODBC JDBC is the java API that facilitate interaction of a java application with the DBMS. FIRST APPROACH:

2. Interact with DBMS using the interface Interface for human user API for application Application Data API provided by the vendor to interect with DBMS. DBMS Package

3. Problem of this approach that is problem of using vendor specific API is that Application becomes dependent on a particular API . If Data Base to be change than changes has to be made in the application apart from this for each data base A new API has to be learned. • ODBC • Open data base connectivity is a common API that is design in “C” & facilitate interaction of application with data bases using a single API.

4. Application Uses ODBC to interact ODBC driver or Definition of ODBC functions provided by vendors. These Uses of function to write connectivity code Set of functions declared in C that are used by the application developer to interact with DBMS ODBC Data Vendors provides implementation of these function Database pkg

5. JDBC driver or implementation of JDBC interfaces provided by vendor Interface for human user Application Uses JDBC to interact Application developer uses these interfaces to e\write connectivity code JDBC Set of java interfaces provided by sun microsystem to facilitate interaction of java Application with databases Vendor provides implementation s of these interfaces

6. Implementation of JDBC interfaces is to be provided by vendors different vendors provides implementation in a different way depending upon the implementation of JDBC interfaces provided by vendor we have four type of JDBC driver. • TYPE-1 Driver • 1) type 1 or (JDBC-ODBC bridge -driver ):(in this implementation vendors defines classes for JDBC interfaces and invoke ODBC functions from these classes.

7. Java Application JDBC Driver JDBC –ODBC Bridge ODBC Driver Type1 or JDBC(ODBC) bridge driver

8. Advantages & disadvantages • 1)From the implementation point of view this is the simplest driver. • Disadvantage:- 1)ODBC driver is required for each machine on which Application is to be executed. • 2) degraded performance is obtain because for each database operation various conversion are perform. • TYPE-2 Driver • Type-2 driver or native java –driver

9. Java Application JDBC Driver Native API

10. In this implementation function of native library provided by vender are invoke from the java classes . • Advantages: 1) better performance is obtained as compared to type 1. • 2) ODBC Driver is not required. • Disadvantages: Native Driver is required for each machine on which application is to executed. • TYPE-3 • It is represents a middle ware that is used to map multiple data sources using different Type-2 drivers.

11. Java application 1 Java Application2 JDBC Driver Java Driver TYPE-3/Native driver Type-2 Driver for Data server 1 Type-2 Driver for Data server 2 Type-2 Driver for Data source 1n

12. Advantage: type-2 driver for each data source need not be installed on each machine. • Disadvantages: an additional middleware is required. • Type-4 (pure java driver ) • Type-4 driver are purely implemented in java that is dependent of JDBC driver on ODBC or native function is removed.

13. Java application TYPE-4 JDBC Pure java Native Driver

14. Advantage: 1) ODBC & native driver is not required . • 2) Better performance is obtained. • Disadvantage: implementation of driver is varies from vender to vender . For each vender implementation classes provided by the vendor are required. • JDBC API is provided in java.sql & javax.sql packages. • Commonly used classes & interfaces of API: • 1)DriverManager (class ) • 2) Connection Interface

15. Interface (2-8) • 3)Statement • 4) PreparedStatement • 5) CollableStatement • 6) ResultSet(Application level cursor) • 7) ResultSetMetaData • 8) DataBaseMetaData • 9) sqlException (class )

16. 1) Driver manager class is responsible to identifying locating & using a specific driver for a database this class acts as a factory for connection object. • 2) Connection: connection Interface provides the abstraction of a database connection & act as a factory of statement, prepared statement ,collable statement. • 3)A statement is used to execute sql queries over a database connection & act as factory of Result Set. • 4) Prepared statement provides the facility of executing parameterize queries. • 5) collable Statement provide the facility of executing stored procedure & function .

17. 6) represents an application curser i.e. result set is used to store result of a select query & act as a factory of result set meta data. • 7) Result set meta data : provide the facility of obtaining information about the data contained in the result set. • 8) provide the facility of obtaining information about the data base . • 9)is the super class of all database related exception .

18. Steps : to connect a java Application to data base: • 1) Explicitly load the driver class . • forName(): method of Class is used to load the driver class . • In case of type-1. • Sun.jdbc.odbc.jdbcodbcDriverclass is loded. • This class is provided by sun microsystem as part of java library . • rt.jar • This class provides the name of connection class ,to be used by the Driver manager to create a connection object as well as provides information required by the driver manager to establish a connection.

19. e,.g. Class.forName(sun.jdbc.odbc.jdbcodbcDriver); • 2) create a connection object , getConnection() factory method of DriverManager class is used to create a connection object. • Public static connection getConnection(String connection string )throws sqlException; • or public static connection getConnection(String connection string ,String user name, String Password)throws sqlexception. • Connection String is used to provide information that is used by the DriverManager to establish a connection with a data source , formate of connection string for type-1 jdbc driver.

20. Main protocol • Jdbc:odbc:DSN (data source name ) • E.g. let there be a DSN named myDSN • Connection con= DriverManager.getConnection(jdbc:odbc:myDSN); • Step3) create a statement object. • CreateStatement() factory method of connection used for this purpose. • Public statement createStatement()throws sqlException; • E.g. Statement stmt= con.CreateStatement();

21. Step 4) Execute query statement interface provides following methods to execute queries. • Public ResultSetexecuteQuery(String selectQuery)throws SQLException; • public intexecuteupdate(String nonselectDML query)throws SQLexception. • Public boolean execute(String nonDmLquery)throws SQLException; • Steps 5: if select query is executed obtain data from the result set. • Obtaining data from the result set is a two step process:

22. Result set Beginning of result set Initial position of of record pointer in the result set . End of ResulSet

23. 1) position the record pointer in the result set on a valid Record for this next() method of ResultSet interface is used . This method advance the position of record pointer by one record. • next(); • Public boolean next(); • 2) Read the value of a field of the current record as a specified java type . • String mS-AceessSQLServer Oracle • text varcharvarchar

24. Result set interface provides various method to obtain the value of a field as a java type. General signature of these methods is • Public type getType(int index)throws SQLException • Actual method: • Public String getString(int index)throws SqlException • Public intgetInt(….); • Public date getDate(); • Public float getFloat(); • Etc…

25. 6)close the connection : • A close method of connection interface used for this purpose. • Public void close() throws sqlexception ;