Transcript Distributed Application Development

Distributed Application
Development
B. Ramamurthy
Working with Oracle
• Use the Oracle DBMS on UB machines. Information
about this is available here.
• You can access UB db from your laptop as long you
have enabled VPN and installed the appropriate
driver and connected the data source. (and of course
you have an internet connectivity).
Standard Access to DB
A
P
P
L
I
C
A
T
I
O
N
D
R
I
V
E
R
M
G
R
DBMS
Driver 1
DBMS 1
DB
DBMS
Driver 2
DBMS 2
DB
DBMS 3
DB
DBMS
Driver 3
ODBC Architecture
Application
Class1
Class2
ODBC
Driver Manager
DriverType1
DataSource1
DriverType2
DriverType3
DataSource2
DataSource3
Open Database Connectivity (ODBC)
Standard
• ODBC standard is an interface by which application
programs can access and process SQL databases in a DBMSindependent manner. It contains:
• A Data Source that is the database, its associated DBMS,
operating system and network platform
• A DBMS Driver that is supplied by the DBMS vendor or
independent software companies
• A Driver Manager that is supplied by the vendor of the O/S
platform where the application is running
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.
An Example
Application
Access driver
DriverManager
mySQL driver Oracle driver
Application in Java
Application in
Java
Sybase driver
DriverManager
mSQL driver
Informix driver
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”
Java Support for SQL
• Java supports embedded SQL.
• Also it provides an JDBC API as a standard way to
connect to common relational databases.
• Java.sql package and an extensive exception
hierarchy.
Data Source
• Local relational database; Ex: Oracle
• Remote relational database on a server; Ex:
InfoSource
• On-line information service; Ex: Dow Jones,
Customer database
SQL Statements
• SELECT {what} FROM {table name}
• SELECT {what} FROM {table name} WHERE
{criteria}
• SELECT {what} FROM {table name} WHERE
{criteria} ORDER BY {field}
• Others
• Queries are embedded as strings in a
Statement object.
JDBC Components
• Driver 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.
JDBC Classes
• Java supports DB facilities by providing classes
and interfaces for its components
• DriverManager class
• Connection interface (abstract class)
• Statement interface (to be instantiated with
values from the actual SQL statement)
• ResultSet interface
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.
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.
Statement interface
• Statement interface is implemented by the
connection object.
• Statement object provides the workspace for
SQL query, executing it, and retrieving
returned data.
• Types: Statement, PreparedStatement,
CallableStatement
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.
JDBC Application Architecture
Application
Connection
Statement
Result Set
Driver Manager
Driver
DataSource
Driver
Driver
DataSource
DataSource
JDBC Programming Steps
• https://wiki.cse.buffalo.edu/services/content/how-use-jdbc-oracle
• Import necessary packages; Ex: import java.sql.*;
• Include jdbc classes as a jar library.
• Connect to the data source using “identifying” string ,
a user name and password.
• Allocate Connection object, Statement object and
ResultSet object
• Execute query using Statement object
• Retrieve data from ResultSet object
• Close Connection object.
Identifying Data Sources
• It is specified using URL format.
• <scheme>: <sub_scheme>:<scheme-specific-part>
Example(for local source): jdbc:odbc:tech_books
• Alternatively, for remote connection,
jdbc:odbc:thin:@aos.acsu.buffalo.edu:1521/aos.buffalo.edu
Connecting to a database on Netbeans
1.
2.
3.
4.
5.
6.
Add the driver jar file to the library for inclusion during
compiling and building of the executable.
Add a new driver (jdbc:odbc) to the databases tab.
Open connection to the data source (in this case to an
Oracle 11g instance)
On successful connection you can view the data source
details (tables etc.).
You can programmatically access the content of the
database from a Java program.
You can execute SQL command from the source editor
window.
Connecting to UB’s Oracle 11g DB
• Add the oracle driver to the library jars of your project.
Here is the oracle driver:
• In order to load the JDBC driver in your Java programs,
include the line:
• Class.forName("oracle.jdbc.driver.OracleDriver");
• try {
Class.forName ("oracle.jdbc.driver.OracleDriver"); }
catch (ClassNotFoundException e){
System.out.println("Could not load the JDBC
driver. Check your CLASSPATH."); System.exit(0); }
Connecting to UB’s Oracle
• Use this connection string syntax to connect
to AOS:
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:"+user+"/"+
passwd+"@aos.acsu.buffalo.edu:1521/aos.buffalo.edu");
• Rest of the programming is similar to how we programmed
the Derby database..no difference!!
• Essentially different drivers shield the differences in the
databases, exactly similar to how the device drivers operate!