Chapter 3
Java Database Connectivity
3.0 Introduction
When writing database applications for relational databases, it is vital that the applications be able to communicate with the data source (Lotus, 1996). As mentioned already this is complicated by the fact that heterogeneous databases exist which implement non compatible Application Programming Interfaces (API). Extensive research has been carried out to enable these heterogeneous databases to interoperate and to provide database independence.
Structured Query Language (SQL) was created to standardise the way relational databases are accessed and the information they contain manipulated (Mohseni, 1996). It was standardised in 1986 by ANSI and since then has been widely accepted by all the major database vendors (XDB, 1996). SQL is not a complete programming language, instead it is more of a structured English for talking to databases (Linthicum, 1997). As a result database applications normally have SQL commands embedded within the programming language, e.g. COBOL or C. A database specific pre-processor is normally provided by each database vendor, enabling the SQL queries and results to be used by the applications variables and methods. In this scenario SQL is efficient and can be ported to different hardware and operating system environments but the source code can not. It must be recompiled for each environment.
3.2 SQL Access Group
Although SQL was standardised the database vendors did not fully implement the standard, instead each provides a proprietary version of SQL for their own database.
The SQL Access Group (SAG) was set up to address these issues of interoperability and database independence. SAG was founded in the early 1990’s, it is made up of operating system vendors, database vendors and other corporations. Their initial solution was to provide database independence using a network protocol but this was designed too close to the hardware level and was not accepted by the industry. SAG then defined a database independent API the SAG Call Level Interface (CLI) (XDB, 1996).
3.3 Open Database Connectivity
Microsoft (1998) then developed Open Database Connectivity (ODBC) based on the SAG CLI and the x-open API’s. ODBC enables applications to communicate with heterogeneous databases using database drivers and a driver manager. Each of the ODBC compliant database vendors supplies drivers for their own database. ODBC enables database programmers to access nearly all relational databases from a single application (Visgenic, 1996). The ODBC API is currently the de facto database interface, offering the ability to connect to almost all relational databases on almost all platforms and some non relational data formats (Hamilton et al., 1997).
Even though ODBC provides on open solution for accessing relational databases, it still faces a number of problems. The main problem is that it takes a back seat to the native interfaces provided by the database vendors. For example Oracle (Oracle, 1996) supports ODBC as an optional API, but it’s native CLI is the Oracle CLI (OCI) (Orfali & Harkey, 1997). Secondly ODBC does not facilitate a platform independent solution. This is due to the fact that ODBC is implemented in the C language (Reese, 1997). This makes ODBC inappropriate for many database applications deployed across the Internet and implemented as a Java applet. This is because Java applets employ a zero installation policy, i.e. Java applets are stored on a Web server, downloaded on request and executed within the user’s Web browser. If ODBC were to be implemented in this scenario it would need to be pre-installed on the client machine, or would need to be downloaded with the applet, making the applet platform dependent.
Java is an excellent language for developing database applications (Hamilton et al., 1997), as Java is secure, robust, multithreaded, platform independent, easy to use, easy to understand and automatically downloadable on a network (Flanagan, 1996). Due to the fact that Java is platform independent (Idhen & Edwards, 1996), it easily overcomes the problems faced by database application developers when writing applications that must operate on different platforms. This enables a single database application to be written, which can be executed on any platform that supports the Java language, without recompiling the source code. Paolini & Poulson (1996) verify this;
“Heterogeneous access to corporate data is a natural extension to Java.”
As Java is object oriented, both data and programming details are encapsulated in classes. This can further simplify database programming (North, 1996). In addition, Java’s support for multithreading and multiprocessing makes it suitable for large database applications that involve large numbers of concurrent users.
Java is being used to write serious business applications for Intranets as well as interactive programs for business to business and business to customer communications on the Internet. The key to developing these applications is the ability to perform data access and transaction processing from Java (XDB, 1996). Williamson & Moran (1996) agrees with Mohseni (1996) when he states;
“For Java to be seriously considered as an application development language, it must support database operations.”
Java provides support for database interactions via JDBC.
3.5 JDBC
As the Java language grew in popularity, software developers expressed the need for a mechanism to access databases from Java. In order to prevent a proliferation of different API’s Javasoft developed the JDBC API. Javasoft (1996) verifies this;
“We’ve created JDBC to encourage adoption of an industry standard for developing open, portable, platform-independent database solutions.”
The JDBC specification is divided into two distinct parts (Mohseni, 1996).
1. The JDBC API.
2. The JDBC driver interface.
3.5.1 JDBC API
JDBC is a database access Application Programming Interface (API) that enables developers to write Java applications that access databases (Javasoft, 1996).
JDBC gives developers a framework to create portable solutions which access a variety of databases (Mohseni, 1996). JDBC supports interchangeable DBMS drivers through a driver manager that automatically loads the right JDBC driver to a given database (Paolini & Poulson, 1996). As a result a Java database application does not care what it’s database engine is. No matter how many times the database engine changes, the application itself need never change (Reese, 1997).
The JDBC API is closely modelled on the x/open SQL CLI which is a widely accepted ANSI standard interface and the design base for ODBC (Willliamson & Moran, 1997).
JDBC implements a SQL level API. That is, it provides a mechanism for programmers to communicate with the database engine using SQL commands (Linthicum, 1997). This is a very low level interface but it provides a common denominator to a wide range of relational databases (Mohseni, 1996). JDBC requires that the database engine uses the SQL-92 entry level (Sun, 1996).
The JDBC API provides Java programmers with a uniform interface and a common base on which higher level tools and interfaces can be built. The JDBC API defines Java classes to represent database connections, resultsets, database metadata etc. (Hamilton et al., 1997). It allows a Java programmer to issue SQL statements and process the results.
Generally JDBC enables an application to do three things (Sun, 1996), see fig 3.2.
1.Establish a connection with a database.
2.Send SQL statements.
Process the results.
JDBC is the primary API for database access in Java (Sun, 1996). As JDBC developers were not working from scratch, they were able to examine the current database connectivity methodologies and better them. Consequently, JDBC is by far the best interface to SQL databases on any platform. Orfali & Harkey (1997) agree and predicts that JDBC might become an even more important database API standard than ODBC.
3.5.2 JDBC Drivers
The role of the drivers is to enable communications between the Java application and the required database. Typically, each database vendor provides the drivers. It is desirable that the JDBC drivers be written in Java as this enables an applet to be downloaded and executed with zero installation on the client side.
Javasoft (1996) divides JDBC drivers into four categories which specify the situations in which the JDBC application can be used (Reese, 1997);
1. JDBC-ODBC bridge and ODBC driver.
2. Native-API partly-Java driver.
3. JDBC-Net pure Java driver.
4. Native-protocol pure Java driver.
Driver categories one and two are temporary solutions where direct pure Java solutions are not available (Hamilton et al., 1997). Javasoft includes the ODBC-bridge with the JDK 1.1 to enable JDBC to get off to a good start (Javasoft, 1996). It is expected that categories three and four will be the preferred method to access databases in the future.
3.5.2.1 JDBC-ODBC bridge and ODBC driver.
These drivers allow JDBC to access a database by using ODBC drivers, see fig 3.3. Bridge solutions generally require ODBC binary code and database client code to be installed on the clients, making them unsuitable for situations in which drivers can not be loaded on the client (Reese, 1997). Web applets is an example of such a situation. This type of driver is most appropriate for Intranets where client installations can be easily performed (Hamilton et al., 1997).
3.5.2.2 Native-API partly-Java driver
Type two drivers convert JDBC calls into the required database API calls (Hamilton et al., 1997), see fig 3.4. As a result the driver contains Java code that calls native C or C++ methods provided by the individual database vendors, that perform the database access (Reese, 1997). Like bridge drivers, some code is required on the client requiring that it be used in similar situations.
3.5.2.3 JDBC-Net pure Java driver
Type three drivers are the most flexible and can be used for both Intranet and Internet applications (Hamilton et al., 1997). The driver translates JDBC calls into a DBMS-independent protocol, which is then translated to a DBMS protocol by a server, see fig 3.5. These drivers do not require code to be installed on the client, as it is contained within the server. The net server middleware enables access to multiple databases (Reese, 1997), easy installation and centralised maintenance.
3.5.2.4. Native-protocol pure Java driver
Like type three, type four drivers do not require code to be installed on the client. This type of driver translates JDBC calls directly into the specific network protocol used by the DBMS (Hamilton et al., 1997), see fig 3.6. Therefore, the main source of this type of driver will be the database vendors. These drivers provide the most pure Java solution (Reese, 1997) and is an excellent solution for Intranet access (Hamilton et al., 1997).
Java is unique among programming languages as it provides two modes of operation.
1. Applets.
2. Applications.
Developing database applications for applets and applications differ significantly due to the architectural and security differences that exist between both methods of development (Jaworski, 1997).
3.6.1 Applets
Applets can be stored on a Web server, downloaded on request and executed within the user’s browser as part of a Web page. Database access applets can also be downloaded, and can use JDBC as the mechanism to enable the applet to communicate
with the database, see fig 3.7.
When an applet is downloaded across the Internet it can be difficult to trust the intentions of the applet or who will use the information that the applet is given. Consequently applets are by default not trusted and have severe security restrictions placed on them. These restrictions do not allow the applet to access the users hard disk and only allow the applet to open network connections back to the server machine they were downloaded from (Mohseni, 1996).
3.6.2 Applications
Java can also be used to write applications. Java applications perform in the same manner as applications written in any other programming language, with the exception that Java applications can be ported to any platform (Hamilton et al., 1997). In this case the Java code is trusted and is allowed to read and write files and open network connections, just like any other application code. Java database applications can be written for both Internet and Intranet use (Reese, 1997), see fig 3.8
. 
3.6.3 Trusted Applets
Trusted applets are applets that have convinced the Java virtual machine that they are trustworthy (Hamilton & Cattell, 1997). Applets can be trusted if they have been signed with a particular cryptographic key, or because they are downloaded from a trusted source. For security purposes trusted applets are treated as applications, but are treated more like applets in other cases, e.g. locating a database on the Internet.
3.6.4 Three-tier access
Java can access a database using a three-tier approach. In the three-tier approach the Java application makes a request to a middle tier, which is located on a network and can be used to provide database access (Reese, 1997), see fig 3.9. These requests can be made through remote procedure calls (RPC) or through an ORB. In chapter 2 we saw the benefits of three-tier client server, Hamilton & Cattell (1996) agrees about these benefits and predicts that three-tier access will become more common for Java applets and applications.
In the past the middle tier has been implemented using C or C++. Java can now be used to create these middle tiers due to the introduction of optimising compilers which translates Java bytecodes into efficient machine specific code. This enables Java to operate at speeds comparable to C. For an all Java solution JDBC can be implemented on the middle tier to provide data access (Hamilton & Cattell, 1997).
There are many benefits to developing applications that implement Java and JDBC.
As we have seen Java is platform independent, enabling an application to be coded once and ported to any platform which supports the Java virtual machine (Mohseni, 1996). Java easily supports applications that require multiple concurrent access as it is multithreaded. Java’s networking capabilities and Java applets enables Java to be deployed over a local Intranet or the Internet using either a two-tier or three-tier approach. The JDBC API is similar to ODBC making it easier for developers familiar with ODBC to use JDBC (Linthicum, 1997). But JDBC is easier to use than ODBC as it keeps everything simple. JDBC is a low-level API, this enables developers to tailor it to suit their own needs. JDBC is used to provide a foundation on which higher level API’s and frameworks for object relational mapping can be constructed (Hamilton et al., 1997).
Java and JDBC provide developers with a viable alternative approach for developing database applications. These applications can operate across the Internet. Java adds a new dimension to Web application development, rather than relying on the server and a CGI script to handle processing and data manipulation, all processing can occur within the client’s Web browser (Mohseni, 1996). CGI applications by comparison are slow and cumbersome and do not reflect the future of Web computing (Orfali & Harkey, 1997). Compared to ActiveX, Java provides a more secure approach. Java can be used to create distributed systems and compliments the CORBA approach, with CORBA providing network transparency and Java providing implementation transparency (Orfali et al., 1997).
JDBC was designed as a low-level API, this can be both an advantage and a disadvantage. Developers implementing JDBC to access, query and update a database must perform the tasks of opening database Connections, creating Statements to enable queries to be issued, and creating ResultSets to allow retrieved data to be viewed and manipulated. These tasks are not difficult but they are time consuming and repetitive. Therefore, a higher level API would save the developer a lot of work enabling faster application development (Hamilton & Cattell, 1997). JDBC currently does not offer all the features provided by ODBC (Visigenic, 1996), but Javasoft intend to address these in the future (Hamilton & Cattell, 1997).
3.9 Conclusion
As we have seen, JDBC is not perfect. Two ways of improving it is to use a higher level abstraction of JDBC, which carries out the lower level tasks in the background. This enables the developer to concentrate on developing the database application. A second approach to simplifying JDBC is to use a framework for object relational mapping (Reese, 1997).
Both solutions are considered in this project. The higher level abstraction of JDBC will provide an easier interface to the JDBC functionality. A framework for object relational mapping is useful in situations when an object oriented language, such as Java uses a relational database to store details about objects which make up a particular application (Kerz, 1995). Ideally though, when working with an object oriented language, it is best to use an object-oriented database to provide data persistence for the objects (Parkhill & Friedman, 1995). But due to the wide usage of relational databases this is not always possible. It may be necessary to use the existing relational database (Chung et al., 1995). Object relational mapping caters for this by translating objects onto a flat tabular form suitable for storage in a relational database (Fussell, 1997). Details stored are typically object attributes, links to other objects and their relationships. This automated mapping enables the programmer to concentrate on the object-oriented task at hand, while the mapping framework performs the necessary translations in the background.
In the next chapter a system is first designed that will operate on top of the JDBC API to provide a simpler connection mechanism. A second system is then designed which provides object relational mapping and persistence for the attributes of Java objects.