ADVANCED POGRAMMING JAVA COURSE Topics Java Database Connectivity

ADVANCED POGRAMMING (JAVA) COURSE Topics: § Java Database Connectivity (JDBC) § Graphics Programming with Java 2 D and Java 3 D § Security § Servlets § Java Server Pages § Java-Based Wireless Applications

Part I Java Database Connectivity (JDBC)

Java Database Connectivity (JDBC) §A common programming interface writing programs üthat access information stored in q databases, qspreadsheets, and qother data sources either directly or through middleware

JDBC cont’d § By using the JDBC interface, v. Java programmers can request a connection with a database, v Send query statements using SQL v. Receive the results for processing. § Java runs on many different hardware platforms and operating systems, v Developers can use JDBC to write applications üaccess data across incompatible database management systems running on varied platforms.

Internet Resources for JDBC http: //java. sun. com/docs/books/tutorial/jdbc/index. html www. sql. org industry. java. sun. com/products/jdbc/drivers http: //servlet. java. sun. com/products/jdbc/drivers java. sun. com/products/jdbc/faq. html http: //java. sun. com/products/jdbc/articles/package 2. html http: //java. sun. com/developer/Books/JDBCTutorial/chapter 5. html www. jguru. com/jguru/faqpage. jsp? name=JDBC

What is the JDBC API? § A set of classes and interfaces written in the Java va standard API for tool/database developers & vmakes possible to write database applications using an Java API. § Obtaining a variety of different data sources is possible for Java applications § Makes possible to publish a web page containing an applet that uses information obtained from a remote data source. For example: v. To connect all employees (even if they are using Windows, Macintosh, and UNIX machines) is possible as one or more internal databases via an intranet

The JDBC 3. 0 API Includes two packages: § java. sql package § javax. sql package (adds server-side capabilities) While downloading the Java 2 Platform Standard Edition, Version 1. 4 (J 2 SE), we can automatically get both packages § The JDBC-ODBC Bridge provides a limited subset of the JDBC 3. 0 API. § Summary of new features has to be read at http: //www-106. ibm. com/developerworks/java/library/j-jdbcnew/ .

The java. sql Package

The java. sql Package § Contains the entire JDBC API that sends SQL statements to relational databases and retrieves the results of executing those SQL statements. § The Driver interface represents a specific JDBC implementation for a particular database system. § Connection represents a connection to a database. § The Statement, Prepared. Statement, and Callable. Statement interfaces support the execution of various kinds of SQL statements. § Result. Set is a set of results returned by the database in response to a SQL query. § The Result. Set. Meta. Data interface provides metadata about a result set § Database. Meta. Data provides metadata about the database as a whole.

Array § Provides an interface to SQL ARRAY objects. § get. Array() method returns a standard Java array of objects of the type returned by get. Base. Type(). § The contents of the array can also be returned as a Result. Set using the various get. Result. Set() methods. public abstract interface Array { // Public Instance Methods public abstract Object get. Array () throws SQLException; public abstract int get. Base. Type () throws SQLException public abstract String get. Base. Type. Name () throws SQLException; public abstract Result. Set get. Result. Set () throws SQLException; }

import java. sql. Connection; import java. sql. Driver. Manager; import java. sql. SQLException; public class Jdbc. Exam { public static void main (String args[]) { Connection con = null; try { Class. for. Name ("com. mysql. jdbc. Driver"). new. Instance(); con =Driver. Manager. get. Connection ("jdbc: mysql: ///test", "root", "secret"); if (!con. is. Closed()) System. out. println ("Successfully connected to My. SQL server using TCP/IP. . . "); } catch(Exception e) { System. err. println ("Exception: " + e. get. Message()); } finally { try { if(con != null) con. close(); } catch (SQLException e) {} }

import java. sql. Connection; import java. sql. Driver. Manager; import java. sql. SQLException; we imported 3 classes from java. sql package. § Connection represents a connection to a database § Driver. Manager manages JDBC drivers vused to create connections to databases § SQLException is an exception class vwhich gets thrown in case any error occurs in the program

§ We declare a local variable to hold our java. sql. Connection object that we'll retrieve from java. sql. Driver. Manager later on. Connection con = null; § We encapsulate our JDBC code in a try/catch/finally block. § This is to ensure that if any runtime error gets thrown we catch it and display it to the user. try {. . . … } catch(Exception e) { System. err. println("Exception: " + e. get. Message()); } finally {. . . …. }

§ We take the first step in establishing a connection to our My. SQL database by loading the driver explicitly using Class. for. Name() method Class. for. Name("com. mysql. jdbc. Driver"). new. Instance(); § Once the JDBC driver has been loaded in the JVM, we retrieve a connection to our My. SQL database running on the local system using Driver. Manager. get. Connection() method §. The first argument to this method is complete database URL, next parameter is the user name and last parameter is the password. con=Driver. Manager. get. Connection("jdbc: mysql: /// test", "root", "secret");

§ After these steps are completed, it is required to obtain a connection to the My. SQL database. § We check to make sure we are properly connected and display a success message if we are. if(!con. is. Closed()) System. out. println("Successfully connected to " + "My. SQL server using TCP/IP. . . "); § To close the connection, we execute the Connection. close() method in the finally block of our try/catch/finally statement. try { if(con != null) con. close(); } catch (SQLException e) {} § The Connection. close() method can also throw SQLException v so that is why we encapsulate this close() method in a separate try/catch block.

http: //www. mysql. com/get/Downloads/My. SQL 4. 0/mysql-4. 0. 18 -win. zip/from/pick#mirrors is the website which you can download the mysql http: //www. mysql. com/doc/en/Windows_installati on. html is the website which you can install the mysql

javax. sql package

The javax. sql package § Contains the JDBC 2. 0 Standard Extension API. § The classes and interfaces in this package provide new functionality, v connection pooling, that do not fall under the scope of the original JDBC API and can therefore be safely packaged separately. § The Data. Source interface serves as a factory for Connection objects; § Data. Source objects can be registered with a JNDI ( Java Naming and Directory Interface )server, making it possible to get the name of a database from a name service. § Pooled. Connection supports connection pooling, which allows an application to handle multiple database connections in a fairly transparent manner. § Row. Set extends the Result. Set interface to a Java. Beans component that can be manipulated at design time and used with non-SQL data sources

What Is the JDBC-ODBC Bridge? § A JDBC driver vimplements JDBC operations which translate them into ODBC operations. § ODBC acts as a normal application program. § The bridge implements JDBC for any database for which an ODBC driver is available. v contains a native library used to access ODBC. § The Bridge is installed automatically with the Java 2 SDK, Standard Edition, as package sun. jdbc. odbc.

A Base for Other APIs § Java APIs are implemented as Java classes. § The following are just a few of the available APIs except JDBC v Java Real Time v Java Foundation Classes (JFC) v Swing—user interface v Remote Method Invocation (RMI) v Java for XML (JAXP) v Java for XML Messaging (JAXM) v Java Sound v Java Media Framework v Java 2 D and 3 D graphics v Java Naming and Directory Interface (JNDI) http: //www. alumni. caltech. edu/~croft/research/java/snapshot/

What Does the JDBC API Do? § Establish a connection with a data source § Send queries and update statements to the data source § Process the results Connection con = Driver. Manager. get. Connection( "jdbc: my. Driver: wombat", "my. Login", "my. Password"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query ("SELECT a, b, c FROM Table 1"); while (rs. next()) { int x = rs. get. Int ("a") String s = rs. get. String ("b"); float f = rs. get. Float ("c"); }

Why not Use ODBC from Java? § Before the development of the JDBC API v. The most widely used programming interface Microsoft's ODBC (Open Data. Base Connectivity) API ü To access relational databases § ODBC can be used with Java, but § The best application is JDBC API v. Applied as the form of the JDBC-ODBC Bridge § ODBC is not appropriate for direct use from the Java programming language v. Uses C interface. v. Occurs a number of drawbacks in the security, implementation, robustness, automatic portability ü during the calls from Java to native C code QUESTION: What are other important differences between JDBC API and ODBC API? § Once ODBC (as native code) is called, Java programming language can't guarantee that a security violation won't occur (untrusted).

The Bridge’s usage § A JDBC connection is opened v using a URL with the odbc subprotocol. § Loading is done as follows: Class. for. Name ("sun. jdbc. odbc. Jdbc. Odbc. Driver"); § Before any connection, the bridge driver class, sun. jdbc. odbc. Jdbc. Odbc. Driver v added to the java. lang. System property named jdbc. drivers OR vexplicitly loaded using the Java class loader. § After loaded, the ODBC driver creates an instance of itself and registers this with the JDBC driver manager.

What Is the JDBC URL Supported by the Bridge? uses the odbc subprotocol jdbc: odbc: <data-source-name>[; <attribute-name>=<attribute-value>]* jdbc: odbc: sybase jdbc: odbc: mydb; UID=me; PWD=secret jdbc: ora 123; Cachesize =300

JDBC Drivers § Provides JDBC access via one or more Open Database Connectivity (ODBC) drivers. § ODBC, which predates JDBC, is widely used by developers to connect to databases in a non-Java environment. § JDBC supports four categories of drivers: v. JDBC- to ODBC driver (Type 1) v. Native-API, partly driver (Type 2) v. JDBC- Net pure driver (Part 3) v. Native-Protocol pure Java driver (Type 4)

JDBC- to ODBC driver (Type 1) § A good approach for learning JDBC. May be useful for companies that already have ODBC drivers installed on each client machine v The case for Windows-based machines running productivity applications. ü May be the only way to gain access to some low-end desktop databases. § Not for large-scale applications. Performance suffers because there's some overhead associated with the translation work to go from JDBC to ODBC. Doesn't support all the features of Java. User is limited by the functionality of the underlying ODBC driver

Native-API, partly driver (Type 2) § Converts the calls that a developer writes to the JDBC application programming interface into calls that connect to the client machine's application programming interface for a specific database, such as IBM, Informix, Oracle or Sybase. § Performance is better than that of Type 1, in part because the Type 2 driver contains compiled code that's optimized for the back-end database server's operating system. § User needs to make sure the JDBC driver of the database vendor is loaded onto each client machine. Must have compiled code for every operating system that the application will run on. Best use is for controlled environments, such as an intranet.

JDBC- Net pure driver (Type 3) § Pure Java driver for database middleware, which provides connectivity to many different databases. § Translates JDBC calls into the middleware vendor's protocol, which is then converted to a database-specific protocol by the middleware server software. § Better performance than Types 1 and 2. Can be used when a company has multiple databases and wants to use a single JDBC driver to connect to all of them. § Server-based, so no need for JDBC driver code on client machine. § For performance reasons, the back-end server component is optimized for the operating system that the database is running on. § Needs some database-specific code on the middleware server. If the middleware must run on different platforms, a Type 4 driver might be more effective.

Native-Protocol pure (all) Java driver (Type 4) § Written entirely in Java § Converts JDBC calls into packets vthat are sent over the network in the proprietary format üused by the specific database. § Allows a direct call from the client machine to the database. § Better performance than Types 1 and 2. § No need to install special software on client or server. Can be downloaded dynamically. .

Type 4 Java Driver cont’d § Not optimized for server operating system, so the driver can't take advantage of operating system features. v. The driver is optimized for the database and can take advantage of the database vendor's functionality. v. User needs a different driver for each different database § These drivers are also well suited for applet programming, provided that the Java security manager allows TCP/IP connections to the database server.

Driver Types Results § Type 1 and Type 2 drivers were provided primarily to allow Java programmers to create data-driven solutions before database vendors created pure java drivers § Type 3 and Type 4 drivers are preferred, because they are pure Java solutions The cloudscape driver COM. cloudscape. core. RMi. Jdbc. Driver is a Type 4 driver

New APIs from Microsoft § § OLE (Object Linking and Embedding) DB ADO (Active. X Data Objects) RDS (Remote Data Service) UDA (Universal Data Access) van umbrella term that covers OLE DB, ADO, RDS, and ODBC v The JDBC 2. 0 API contains all of the important functionality of UDA plus features not found in UDA, such as SQL 3 support.

OLE DB § Microsoft's strategic low-level application program interface v for access to different data sources. § Includes the SQL capabilities of the Microsoftsponsored standard data interface Open Database Connectivity § Includes access to data other than SQL data. § As a design from Microsoft's Component Object Model (COM), OLE DB is a set of methods, for reading and writing data. § The objects in OLE DB consist mainly of a data source object, a session object, a command object, and a row set object. :

An application using OLE DB § Initialize OLE § Connect to a data source § Issue a command § Process the results § Release the data source object § Uninitialize OLE As a result: § A general-purpose set of interfaces designed to let developers build data access tools as components using the Component Object Model (COM). § OLE DB enables applications to have uniform access to data stored in DBMS and non-DBMS information containers § OLE DB takes advantage of the benefits of database technology without having to transfer data from its place of origin to a DBMS

Active. X Data Objects (ADO) § A language-independent object model v. Expose data by an OLE DB Provider. § The most commonly used OLE DB Provider vthe OLE DB Provider for ODBC Drivers, ü Exposes ODBC Data sources to ADO. § ADO is newer and more like the JDBC API v it is not pure Java http: //msdn. microsoft. com/library/default. asp? url=/downl oads/list/dataaccess. asp

Remote Data Service (RDS) § The part of Microsoft's Active. X Data Objects (ADO) programming model designed for remote data access in internet client/server applications. § On both client and server computers it depends upon the installation of the Microsoft Data Access Components (MDAC). § RDS has three core objects that are used to retrieve, manipulate, and update data in applications.

Remote Data Service (RDS) con’t § The RDS. Data. Control and RDS. Data. Space objects vinstalled on client computers and are directly accessed within applications to retrieve data from servers § The RDSServer. Data. Factory object, a server component vcan be accessed directly to communicate with data sources voperates behind the scenes with the other RDS objects in handling database maintenance

Cloudscape • Cloudscape is a DBMS, a database management system, accessed by applications through the JDBC API. § IBM Cloudscape provides developers a small footprint, standards- based Java database that can be tightly embedded into any Java based solution. § Supports complex SQL, transactions and JDBC so that your applications can be migrated to DB 2 UDB when they need to grow. § Supports data encryption on disk via JCE for secure operation in hostile environments. http: //www. deitel. com/books/advj. HTP 1_Cloudscape. Inst allation. html

Cloudscape con’t § Some standard Java tools and utilities that make it easier to use Cloudscape and to develop Cloudscape applications vij is Cloudscape's interactive JDBC scripting tool. It is a simple utility for running scripts against a Cloudscape database. You can also use it interactively to run ad hoc queries. ij provides several commands for ease in accessing a variety of JDBC features. ü ij can be used in an embedded or a client/server environment. v. Cloudview is a graphical user interface for creating and managing Cloudscape databases. It can be used in an embedded or a client/server environment.

Cloudscape Database § Consists of platform-independent files stored in a directory that has the same name as the database. § Most of the Cloudscape tools are JDBC applications. § A JDBC application is one that uses the classes in the java. sql package to interact with a DBMS. § When we work with JDBC applications, we need to know about the following two JDBC concepts v Drivers v Database connection URLs

Drivers § Before a JDBC application interacts with a database, it must cause the appropriate JDBC driver to be loaded in the Java session. Cloudscape provides three JDBC drivers for use with the Cloudscape database engine. When you use the Cloudscape tools that are JDBC applications, you will need to know which driver to load. § COM. cloudscape. core. JDBCDriver For embedded environments, when Cloudscape runs in the same JVM as the application § COM. cloudscape. core. Web. Logic. Driver For client/server environments in which Cloudscape runs in the Cloudconnector framework and applications connect via the network § COM. cloudscape. core. Rmi. Jdbc. Driver For client/server environments in which Cloudscape runs in the Rmi. Jdbc Server framework and applications connect via the network

Run as two different frameworks • Embedded The simplest Cloudscape environment. Enables Cloudscape as part of a Java application Database accession as only one application at a time No network access Starting of an Cloudscape instance within the current JVM and shutting down before completing • Client/Server The connection to Cloudscape over the network. Running embedded in a connectivity or server framework allows multiple network connections. The Rmi. Jdbc framework enables the Cloudscape to execute as a stand-alone database server (a lightweight JDBC server and client) v Each frame work directory has a bin subdirectory containing batch files

A JDBC URL § Provides a way of identifying a database so that the appropriate driver recognizes it and connects to it. . § After the driver is loaded, an application must specify the correct database connection URL to connect to a specific database. v. The Cloudscape database connection URL allows us to accomplish more tasks than simply connecting. Some of them: § jdbc: cloudscape: database. Name; Cloudscape. URLAttributes For embedded environments § jdbc: cloudscape: weblogic: database. Name; Cloudscape. URLAttributes v For connecting to Cloudscape running inside the Cloudconnector framework, when the server is running on the default host localhost and is using the default port number 7001

Rmi. Jdbc Framework v Change directories to bin directory in Rmi. Jdbc v Set the environment variables for the server ü Execution of the batch file set. Server. Cloudscape. CP v Launch the Cloudspace database server ü Execution of the batch file start. CS v Shut down the server ü Executiong stop. CS

Cloudview § Graphical user interface for creating and managing Cloudscape databases.

Connecting to a Server § You can use Cloudview as a client application that interacts with Cloudscape running in a server framework. § To do that, simply load the appropriate driver and use the appropriate protocol for the server framework, either Rmi. Jdbc or Cloudconnector.

CLOUDSPACE ORDBMS • A Java and SQL based object-relational database management system Description: Founded in 1996, cloudspace is an Internet services company specializing in open-source Website and online software development for the entertainment, technology and professional services industries. Url: http: //www. cloudspace. com