1 / 24

Distributed Application Development

Learn how to use the Oracle DBMS and ODBC standard to develop distributed applications on UB machines, with access to UB databases from your laptop.

emanning
Download Presentation

Distributed Application Development

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Distributed Application Development B. Ramamurthy

  2. 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).

  3. A P P L I C A T I O N DBMS Driver 1 D R I V E R M G R DBMS 1 DB DBMS Driver 2 DBMS 2 DB DBMS Driver 3 DBMS 3 DB Standard Access to DB

  4. DriverType1 DriverType2 DriverType3 ODBC Architecture Application Class1 Class2 ODBC Driver Manager DataSource3 DataSource2 DataSource1

  5. 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 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

  6. 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.

  7. An Example odbc standard API Application DriverManager Access driver mySQL driver Oracle driver

  8. Application in Java jdbc API odbc standard API Application in Java DriverManager Sybase driver mSQL driver Informix driver

  9. 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”

  10. 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.

  11. Data Source • Local relational database; Ex: Oracle • Remote relational database on a server; Ex: InfoSource • On-line information service; Ex: Dow Jones, Customer database

  12. 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.

  13. 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.

  14. 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

  15. 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.

  16. 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.

  17. 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

  18. 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.

  19. Driver Driver Driver JDBC Application Architecture Application Result Set Connection Statement Driver Manager DataSource DataSource DataSource

  20. https://wiki.cse.buffalo.edu/services/content/how-use-jdbc-oraclehttps://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. JDBC Programming Steps

  21. 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

  22. Connecting to a database on Netbeans • 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.

  23. 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); }

  24. 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!

More Related