Java Reference
In-Depth Information
Data Access in a Complex Environment
Most large organizations have multiple DBMSs and often many different DBMS types. In these complex
environments, tables are often moved between the DBMSs, database locations are changed, and there are constant
changes to the structure of tables (i.e., adding and deleting fields, changing field sizes, etc.) These types of changes
will require changes to the Java classes. Programmers can minimize the coding needed to implement these changes
by using super classes and inheritance to eliminate duplicate code. In addition, abstract classes should be used to
enforce standards across the different DBMS types.
As an example, we'll assume an organization has one installation of each DBMS type (DB2, Oracle, and Access).
To support the DBMSs, a “DBAccess-like” class should be created for each DBMS. This means the programmer would
create three Java classes (called DB2DB, OracleDB, and AccessDB) with properties to hold the unique information
required to access that particular DBMS. (If the three DBMSs were of the same type, you could put the IP address or
URLs of the different hosts in the Java class name (e.g., OracleDB111. 222.333.4) to distinguish between them.
The smart programmer will also implement a common interface for all the DBMSs. Our example will consist
of public methods called doInsert, doUpdate, and doDelete. Regardless of the DBMS type, internally, each class
should work the same way. So, we will force each class to have non-public methods called createValuesClause,
createSetClause, createWhereClause, getFieldsFromRS, doSelect(String), and doUpdate(String). A superclass (called
DB) will be created that defines each of these required methods as abstract. In addition, DB will have a method (called
init) that creates all the needed DB objects (connection, statement, etc.) and common variables that the table classes
need, such as a
ResultSet
variable called rs, a
Statement
variable called stmt, and so on.
The code for DB would be as follows:
package
c10;
import
java.util.*;
import
java.sql.*;
abstract class
DB {
Connection con =
null
;
String url =
null
;
String user, pw;
Properties p =
new
Properties();
String driver =
null
;
public boolean
conExists =
false
;
ResultSet rs =
null
;
Statement stmt =
null
;
int
returnValue;
public void
init() {
if
(conExists ==
false
) {
try
{
Class.
forName
(driver);
p.put("naming", "sql");
p.put("user", user);
p.put("password", pw);
con = DriverManager.
getConnection
(url, p);
stmt = con.createStatement();
}
catch
(ClassNotFoundException e) {
System.
out
.println("couldn't find driver");
e.printStackTrace();
System.
exit
(1);
}
catch
(SQLException e) {
System.
out
.println("couldn't connect");
e.printStackTrace();
System.
exit
(1);
