12/6/2015b.ramamurthy1 java database connectivity b.ramamurthy
TRANSCRIPT
![Page 1: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/1.jpg)
04/21/23 B.Ramamurthy 1
Java Database Connectivity
B.Ramamurthy
![Page 2: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/2.jpg)
04/21/23 B.Ramamurthy 2
Introduction
Most popular form of database system is the relational database system.Examples: MS Access, Sybase, Oracle, MS Sequel Server.Structured Query Language (SQL) is used among relational databases to construct queries.These queries can be stand-alone or embedded within applications. This form of SQL is known as embedded SQL.
![Page 3: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/3.jpg)
04/21/23 B.Ramamurthy 3
Simple Database Application
APPLICATION
DBMS DB
Ex: AccessOracleSybase
![Page 4: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/4.jpg)
04/21/23 B.Ramamurthy 4
Multi-Databases
APPLICATION
DBMS 1
DBMS 2
DBMS 3
DB
DB
DB
![Page 5: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/5.jpg)
04/21/23 B.Ramamurthy 5
Standard Access to DB
APPLICATION
DRIVER
MGR
DBMSDriver 1
DBMSDriver 2
DBMSDriver 3
DBMS 1
DBMS 2
DBMS 3
DB
DB
DB
![Page 6: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/6.jpg)
04/21/23 B.Ramamurthy 6
ODBC Architecture
Application
Class1 Class2
Driver Manager
DriverType1 DriverType2 DriverType3
DataSource2DataSource1 DataSource3
ODBC
![Page 7: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/7.jpg)
04/21/23 B.Ramamurthy 7
Open Database Connectivity (ODBC) Standard
ODBC standard is an interface by which application programs can access and process SQL databases in a DBMS-independent manner. It contains:A Data Source that is the database, its associated DBMS, operating system and network platformA DBMS Driver that is supplied by the DBMS vendor or independent software companiesA Driver Manager that is supplied by the vendor of the O/S platform where the application is running
![Page 8: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/8.jpg)
04/21/23 B.Ramamurthy 8
ODBC Interface
It is a system independent interface to database environment that requires an ODBC driver to be provided for each database system from which you want to manipulate data.The database driver bridges the differences between your underlying system calls and the ODBC interface functionality.
![Page 9: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/9.jpg)
04/21/23 B.Ramamurthy 9
An Example
Application DriverManager
Sybase driver mSQL driver Informix driver
odbc stan
dard API
![Page 10: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/10.jpg)
04/21/23 B.Ramamurthy 10
Application in Java
Application inJava
DriverManager
Sybase driver mSQL driver Informix driver
odbc stan
dard API
jdbc API
![Page 11: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/11.jpg)
04/21/23 B.Ramamurthy 11
Java Support for ODBC : JDBC
When applications written in Java want to access data sources, they use classes and associated methods provided by Java DBC (JDBC) API.JDBC is specified an an “interface”. An interface in Java can have many “implementations”.So it provides a convenient way to realize many “drivers”
![Page 12: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/12.jpg)
04/21/23 B.Ramamurthy 12
Java Support for SQL
Java supports embedded SQL.Also it provides an JDBC API as a standard way to connect to common relational databases.You need a JDBC:ODBC bridge for using the embedded SQL in Java.Java.sql package and an extensive exception hierarchy.We will examine incorporating this bridge using sample code.
![Page 13: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/13.jpg)
04/21/23 B.Ramamurthy 13
Data Source
Local relational database; Ex: OracleRemote relational database on a server; Ex: SQLserverOn-line information service; Ex: Dow Jones, Customer database
![Page 14: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/14.jpg)
04/21/23 B.Ramamurthy 14
Data Source and Driver
Data source is the data base created using any of the common database applications available. Your system should have the driver for the database you will be using.For example your Windows system should have the MS Access Driver. There are a number of JDBC drivers available. Information on installing them is available at : http://industry.java.sun.com/products/jdbc/drivers
![Page 15: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/15.jpg)
04/21/23 B.Ramamurthy 15
JDBC ComponentsDriver Manager: Loads database drivers, and manages the connection between application & driver.Driver: Translates API calls to operations for a specific data source.Connection: A session between an application and a driver.Statement: A SQL statement to perform a query or an update operation.Metadata: Information about the returned data, driver and the database.Result Set : Logical set of columns and rows returned by executing a statement.
![Page 16: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/16.jpg)
04/21/23 B.Ramamurthy 16
JDBC ClassesJava supports DB facilities by providing classes and interfaces for its components DriverManager classConnection interface (abstract class)Statement interface (to be instantiated with values from the actual SQL statement)ResultSet interface
![Page 17: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/17.jpg)
04/21/23 B.Ramamurthy 17
Driver Manager Class
Provides static, “factory” methods for creating objects implementing the connection interface. Factory methods create objects on
demand
when a connection is needed to a DB driver, DriverManager does it using it factory methods.
![Page 18: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/18.jpg)
04/21/23 B.Ramamurthy 18
Connection interface
Connection class represents a session with a specific data source.Connection object establishes connection to a data source, allocates statement objects, which define and execute SQL statements.Connection can also get info (metadata) about the data source.
![Page 19: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/19.jpg)
04/21/23 B.Ramamurthy 19
Statement interface
Statement interface is implemented by the connection object.Statement object provides the workspace for SQL query, executing it, and retrieving returned data. SELECT {what} FROM {table name} WHERE {criteria} ORDER BY {field} Queries are embedded as strings in a Statement object.Types: Statement, PreparedStatement, CallableStatement
![Page 20: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/20.jpg)
04/21/23 B.Ramamurthy 20
ResultSet interface
Results are returned in the form of an object implementing the ResultSet interface.You may extract individual columns, rows or cell from the ResultSet using the metadata.
![Page 21: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/21.jpg)
04/21/23 B.Ramamurthy 21
JDBC Application Architecture
Application
Connection
Driver Manager
Driver Driver Driver
DataSourceDataSource DataSource
Statement Result Set
![Page 22: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/22.jpg)
04/21/23 B.Ramamurthy 22
JDBC Programming Steps
Import necessary packages; Ex: import java.sql.*;
Load JDBC driver(driver should have been installed)
Data source and its location should have been registered.
Allocate Connection object, Statement object and ResultSet object
Execute query using Statement object
Retrieve data from ResultSet object
Close Connection object.
![Page 23: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/23.jpg)
04/21/23 B.Ramamurthy 23
Identifying Data Sources
It is specified using URL format.<scheme>: <sub_scheme>:<scheme-specific-part>
Example(for local source): jdbc:odbc:tech_booksAlternatively, for remote connection,
jdbc:odbc://bina.cse.buffalo.edu:4333/tech_books
![Page 24: 12/6/2015B.Ramamurthy1 Java Database Connectivity B.Ramamurthy](https://reader036.vdocuments.us/reader036/viewer/2022062322/5697bf731a28abf838c7f18c/html5/thumbnails/24.jpg)
04/21/23 B.Ramamurthy 24
SummaryJDBC API of Java language conforms to the ODBC standard.It supports ODBC by providing classes/interfaces for driver manager, driver, connection, statement and resultset and others.You can access various components in JDBC by instantiating or implementing objects and accessing their methods.