Application Programming for Relational DatabasesSlide 2OverviewClient server architectureSlide 5Slide 6Slide 7Database interaction in AccessSlide 9Slide 10Database interaction in JavaDatabase interaction in Embedded SQLJDBC: ArchitectureJDBC Architecture (Contd.)JDBC packageJDBC, different strategiesConnecting with JDBCConnecting to DB with JDBCSlide 19Slide 20Executing select queriesSlide 22Matching Java and SQL Data TypesExecuting update queriesSlide 25Mapping Objects1C. ShahabiApplication Programming for Relational DatabasesCyrus ShahabiComputer Science DepartmentUniversity of Southern [email protected]. ShahabiOverviewJDBC PackageConnecting to databases with JDBCExecuting select queriesExecuting update queries3C. ShahabiOverviewRole of an application: Update databases, extract info, through:User interfacesNon-interactive programsDevelopment tools (Access, Oracle):For user InterfacesProgramming languages (C, C++, Java,… ):User InterfacesNon-Interactive programsMore professional4C. ShahabiClient server architectureDatabase client:Connects to DB to manipulate data:Software packageApplication (incorporates software package)Client software:Provide general and specific capabilitiesOracle provides different capabilities as Sybase (its own methods, … )5C. ShahabiClient server architectureClient-Server architectures:2 tier3 tierLayer 1: user interfaceLayer 2:MiddlewareLayer 3:DB serverMiddleware:Server for clientClient for DB6C. ShahabiClient server architectureExample: Web interaction with DBLayer 1: web browserLayer 2: web server + cgi programLayer 3: DB server7C. ShahabiClient server architectureApplication layer (1):User interfacesOther utilities (report generator, …)Connect to middlewareCan connect to DB tooCan have more than one connectionCan issue SQL, or invoke methods in lower layers.Middleware layer (2):More reliable than user applications8C. ShahabiDatabase interaction in AccessDirect interaction with DBFor implementing applicationsNot professionalDeveloper edition: Generates stand alone applicationAccess application: GUI + “Visual Basic for Applications” code9C. ShahabiDatabase interaction in AccessConnection to DB through:Microsoft Jet database engine Support SQL accessDifferent file formatsOther Database Connectivity (ODBC)Support SQL DBsRequires driver for each DB server•Driver allows the program to become a client for DBClient behaves Independent of DB server10C. ShahabiDatabase interaction in AccessMaking data source available to ODBC application:Install ODBC driver managerInstall specific driver for a DB serverDatabase should be registered for ODBC managerHow application works with data source:Contacts driver manager to request for specific data sourceManager finds appropriate driver for the source11C. ShahabiDatabase interaction in JavaIncludes:Java.sql packageSet of classesSupports JDBC (java database connectivity?) strategy, independent of the DB serverDifference between JDBC and ODBC:JDBC driver manager is part of the application12C. ShahabiDatabase interaction in Embedded SQLExtension of a language (C++,C) with new commands:Void addEmployee( char *ssn, char *lastname, char *firstname) {•Exec SQL–Insert into customer( ssn, lastname, firstname ) values( :ssn, :lastname, :firstname )}Not legal language Compilation precedes by a translation preprocessor from embedded SQL into legal CAdvantages: ???Disadvantages:Not portable between database systemsDifficult debugging13C. ShahabiJDBC: ArchitectureFour Architectural Components:Application (initiates and terminates connections, submits SQL statements)Driver manager (load JDBC driver)Driver (connects to data source, transmits requests and returns/translates results and error codes)Data source (processes SQL statements)14C. ShahabiJDBC Architecture (Contd.)Four types of drivers:Bridge:Translates SQL commands into non-native API.Example: JDBC-ODBC bridge. Code for ODBC and JDBC driver needs to be available on each client.Direct translation to native API, non-Java driver:Translates SQL commands to native API of data source. Need OS-specific binary on each client.Network bridge:Send commands over the network to a middleware server that talks to the data source. Needs only small JDBC driver at each client.Direction translation to native API via Java driver:Converts JDBC calls directly to network protocol used by DBMS. Needs DBMS-specific Java driver at each client.15C. ShahabiJDBC packageCollection of interfaces and classes:DriverManager: Loads the driverDriver: creates a connectionConnection: represents a collectionDatabaseMetaData: information about the DB serverStatement: executing queriesPreparedStatement: precompiled and stored queryCallableStatment: execute SQL stored proceduresResultSet: results of execution of queriesResultSetMetaData: meta data for ResultSetReminder: Each JDBC package implements the interfaces for specific DB server16C. ShahabiJDBC, different strategiesStrategies to USE JDBCJDBC-ODBC bridgeCon: ODBC must be installedJDBC database clientCon: JDBC driver for each server must be availableJDBC middleware clientPro: Only one JDBC driver is requiredApplication does not need direct connection to DB (e.g., applet)17C. ShahabiConnecting with JDBC Database connection needs two piecesJDBC package driver class namePackage driver provide connection to DBURL of the databaseJDBC package designatorLocation of the serverDatabase designator, in form of:•Server name, Database name, Username, password, …•Properties18C. ShahabiConnecting to DB with JDBC Step 1: Find, open and load appropriate driver1. Class.forName( “sun.jdbc.odbc.JdbcOdbcDriver” );2. Class.forName( “oracle.thin.Driver” );3. Class.forName( “symantec.dbAnywhere.driver” );Or:4. DriverManager.registerDriver( your jdbc driver );Informs availability of the driver to “DriverManager” (registers the driver with DriverManager)(Example 1)19C. ShahabiConnecting to DB with JDBC Step 2: Make connection to the DBConnection conn = DriverManager( URL, Properties);•Properties: specific to the driverURL = Protocol + user•Protocol=
View Full Document