CA2292675C - Mapping relational tables to object oriented classes - Google Patents

Abstract

A computer system providing a framework for mapping relational tables to object oriented classes. The framework includes an attribute class for defining data corresponding to columns in a relational table and including data objects used to map between the relational table and a programmer-defined object oriented class. The attribute class also including flags used to indicate modifications to the data. The framework further includes a generator class providing methods for generating SQL text corresponding to the relational table as defined in the attribute class objects for a given table.

Description

MAPPING RELATIONAL TABLES TO OBJECT ORIENTED CLASSES
FIELD OF THE iNVENTION
The present invention is directed to an improvement in computing systems and in S particular to computer systems which provide for the mapping of relational tables to object oriented dasses.
BACKGROUND OF THE INVENTION
Data is often stored in relational database systems. Such relational database systems model the data as tables and offer rclational operators such as SELECT, INSERT, UPDATE and.
to DELETE for data manipulation. Relational database systems are frequently used as "backend"
systems which are in turn accessed by another computer program or system application which accesses the data in the relational database tables. This access may be accomplished by the other application including code which directly uses the application program interface (APT) of the relational database in which the data is stored. For the relational database to be accessed directly IS in this way the programmer writing the application must have knowledge of the underlying APl for the relational database. Typically, such code in the application program is written specifically for a given structure ofdatabase and therefore when the database is changed in some way the code must also be changed. tt is now quite common for object oriented applications to access relational database backcnd systems. For a programmer writing in an object oriented environment 20 it is advantageous to be ablc to model the data as object oriented classes, rather than to require the programmer to have knowlcdgc of the details of the relational database being accessed. With the increasing popularity of object oriented styles of programming, such a mapping of relational tables to classes is relatively common. One approach to this mapping is to store the relational database operators and definitions statically in defined classes in the object oriented system.
25 Classes are defined on a table by table basis and operations for the classes are defined in relation to the structure of the tables mapped. Such a system has limited flexibility.
in addition, the implementation of relational operators in the object oriented system will often result in inefficient relational operations being carried out. For example, an object oriented implementation of a relational UPDATE: opcrator may result in a global update (all fields are updated) where a more 30 selective update may be appropriate and potentially be CA9-1999-txt44 more el~'tcient_ An example of the prior art approach is found in U.S. Patent 5,694,598 (Durand) which discloses a method for mapping data between object oriented object and a relational dstmbase.
The method disclosed requires the generation of a transit object from objects in the object S oriented environment and populating the transit object with data which is then used to populate a relational database. The mapping may also be carried out in reverse.
Another prior approach to the mapping between relational tables and object oriented constructs is provided in U.S. Patent 5,499,371 (Henninger), The method disclosed in that patent requires the definition of an object model which represents the structure of a given relational database table. This object model is used in conjunction with a database schema and transform to create the mapping between the relational table and the object oriented environment.
Another approach is found in U,S. Patent 5,596,746 (Shen) which d'sscloses the use of meta models of database tables to permit relational tables to be mapped to an object model representation.
ts The prior art approaches require specitic data constructs such as transit objects, object models and meta models for the mapping of the relational data to the objcct oriented environment.
Such approaches potentially constrain the object oriented programmer in dcveloping or modifying object oriented models for mapping relational tables.
It is therefore desirable to have a computer system which will permit the mapping of relational database tables to object oriented classes where the object oriented systcm retains flexibility to accornmodate changes in the relational database and which has increased etTiciency_ SUMMARY OF THE INVENT[ON
Accflrding to one aspect of the present invention, there is provided an improved system for mapping relational database tables to object oriented classes.
According to another aspect of the present invention, there is provided a computer system framework for mapping relational database tables to object oriented classes, the framework including an attribute class for mapping data values from a relational table column to values in an object oriented environment, the attribute class comprising data objects representing data types, a .30 data CA9-1999-4"

2 object for data from the relational table, and methods to access the data objects in the attribute class, a generator class for providing methods to permit the generation of SQL
statements for relational tables defined by attribute class objects, whereby a programmer may define a table class corresponding to a relational table, the table class using the attribute class and the generator class s of the framework to define the columns of the table in the object oriented environment and to define specifia operations on the relational table.
According to another aspect of the present invention, there is provided the above framework in which the data objects representing data types in the attribute class comprise a column name object, an SQL type object and an object oriented mapping type object.
According to another aspect of the present invention, therc is provided the above framework in which the attribute class further includes data object flags comprising an index flag indicating whether the relational table column mapped by the attribute class object is part of a target index for a given SQL statement.
According to another aspect of the present invention, there is provided the above 15 framework in which the attribute class further includes data object flags comprising a modified flag indicating whether the attribute value in the attribute class object has been modified.
According to another aspect of the present invention, there is provided the above framework in which the attribute class funher includes data object flags comprising a bindable flag indicating whether the attribute value in the attribute class object is to be bound to a parameter 20 marker for a given SQL statement, and an associated update string data object comprising an SQL fragment to be bound to the parameter marker where the attribute value is not to be so bound.
According to another aspect of the present invention, there is provided the above framework in which the generator class includes methods to generate text for SQL statements 25 comprising SELECT, INSERT, UPDATE, and DELETE and further includes methods to bind values to the statements passed to the relational table database.
According to another aspect of the present invention, there is provided the above framework in which the methods to generate the SELECT statcment further includes a method to extract valueg from a fetched row of a relational table for use in a corresponding attribute class 3o object.

3 According to another aspect of the present invention, there is provided a computer program product for use with a computer oomprising a central processing unit and random access memory, said computer program product comprising a computer usable medium having computer S readable code means embodied in said medium providing a framework for the mapping of relational data tables to object oriented classes, said computer program product comprising computer readable program code means the above framework.
According to another aspect of the present invention, there is provided a computer program product tangibly embodying a program of instructions executable by a computer for providing the above framework for the mapping of relational data tables to object oricnted classes.
According to another aspect of the present invention, there is provided a method for mapping relational database tables to object oriented classes, the method comprising the following steps:
ts defining an attribute class for mapping data values from a relational table column to values in an object oriented environment, the attribute class comprising data objects representing data typcs, a data object for data from the relational table, and methods to access the data objects in the attributc clasfi, defining a generator class for providinb methodx to pcrmit the generation of SQI.
statements for relational tables defined by attribute class objects, defining a table class correspondiiig to a relational table, the table class using the attribute dass and the generator class of thc framework to define the columns of the tabte in the object oriented environntent and to define specific operations on the relational table.
Advantages of the present invention include the fact that use of the framework of the invention permits object oriented code to be written in a flexible and powerful way to permit accesses to relational database tables without the programmer relying on knowledge of details of the underlying database table_ 3n cA9-1 991)-Ixf44

4 BRIEF DESCRIPTION OF THE DRAWINGS
The preferred embodiment of the invention is shown in the drawings, wherein Figure 1 is a schematic diagram representing the mapping from an example relational table to an example object oryented class in accordance with the preferred embodiment.
In the drawings, the preferred embodiment of the invention is illustrated by way of example. It is to be expressly understood that the description and drawings are only for the purpose of illustration and as an aid to understanding, and are not intended as a definition of the linuts of the invention, 11) DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT
The mapping of the preferred embodiment is illustrated by way of the example shown in the block diagram of Figure 1. Figure I shows an example relational database table 10 with the name F.mployee. An object oriented class into which table 10 is mapped is shown in Figure t as class 12, also named Employee, 'C'able 10 has columns 14, 16, 18, 20, named Empno, Firstname, Lastnamc and Position, respectively.
As is described in greater detail below, the system of the preferred embodiment permits table 10 to be mapped into class 12 by the creation of an object of type attribute for each column in table 10. Associated with each object of type attribute is a pair of methods, to get and set values of the attribute object. This is shown in the example of Figure 1 by the mapping from 2t1 Empno column 14 to Empno attribute object and associated get/set methods 22. Similarly Firstnamc column 16 is mapped by Firstname attribute object and associated get/sct methods 24.
Lastname column 18 and Position column 20 arc in the same way mapped to Lastname and Position attribute objects and associated methods 26, 28, respectiveiy.
The example of Figure l also illustrates the fact that the preferred embodiment supports the definition of methods 30, 32, 34 in class 12 which implement specific relational operations on the relational data, as is described in more detail below, in the preferred embodiment, the relational database is an SQI, relational DBMS. The examples provided are in the Java(D object oriented programming language. As will be apparent to those skilled in the art the object oriented framework provided in the preferred embodiment S

my be implemented in other object oriented lsnguages and other relational DBMS& may be mapped by the preferred embodiment.
The preferred embodimont as impfcmented in Java makes use of the Java extension JDBCBI which provides an API to permit Java programs to access SQL relational database tables.
S JDBC includes, for arample, a method for executing defined queries on an SQL
data table.
The object oriented framework of the preferred embodiment provides two classes which are available to map relational database tables. The first class (named attribute in the preferred embodiment) handles the mapping of the database column to the class property.
The second class uses this mapping class (attribute) to generatc the SQL code for use by the relational DBMS, bind parameters and retrieve values from the result sets.
An instance of the attribute class is used tu map data values from a column in a given relational database table. The attribute class defines the following data;
. ci)lrimi- rrame: This is the name of the relational database column which is mapped by the instance of the attribute class;
=.5Y)1, Itipe: This is the data type of the mapped SQL column;
= vbjecr nriL'illc=d mapping tyt)e: This is the data type in the object oriented environment into which the SQL type is mapped;
+ indexflax: This is a flag which indicates whether the column in the database table is part of the targct index for a given SQL statement;
. mmiified bil: "I'his is a flag which indicates that the object oricnted application has modified the attribute value and that when the corresponding SQL statement is generated, there will be a change to the corresponding column in the relational table.
Use of the modified bit permits SQL statements to be gcnerated which are more efficient by updating only those aspects of the table required to be updated by the 21 modified bit flag.
a biirdable flag: This flag indicates if a parameter marker should be used and if so the attribute value will be bound to the parameter marker in the SQL statement generated.
= lipdale ,s-rnig: This data is used when an SQI. fragment (the update string) is to be M used for a parameter marker instead ot'the attribute value.
CA9-1999-nO44 = ohjecl one ted -Wue: This represents the actual value of the data in the column of the table in the database.

The attn`bute class contains information which is used when SQL statements are generated and passed to the DBMS. It also contains inforcnation which is of use when the database table data is to be used in the object oriented system. For the database side the SQL type and name of column are important. p'or the object oriented side it is important to know what data type to map the column to.
Defined in the attribute class for the preferred embodiment is a set of methods to set and to get the data values encapsulated in the class. The implementation of such methods will be apparent to those skilled in the art. An example of such a method is the method setKey() which takes a Boolean parameter and sets the indcx flag in accordance with the parameter passed to it.
The second class in the framework of the preferred embodiment is the SQLGenerator class. This class encapsulates table name and attribute list data. In the preferred embodiment the is SQLGcnerator class contains methods for manipulating the following SQL
statements; SELECT, INSERT, UPDATE, DELETE. Methods are defined in the prefcrrcd embodiment to generate text which corresponds to each of the four SQL operators listed above. Methods are also defined which populate the SQI, statemcnts - values are bound to statements generated by the methods defined in the SQLGenerator clatis.
20 The implementation of the methods set out below will be apparent to one skilled in the art tbr a given set consisting of a relational DBMS, an object oriented environment, and connection objects defining an interface between the two.

The following ntethods are defined in the framework of the preferred embodiment for the SQ1. SELECT statement:
25 = generateSeleclQ: returns a select statement (String) based on the index as defined by the index flags set to true in the attributes defined for a mapped table;
= IwpalateSelecl (Stateme-rt IlatAcJle): binds the attribute value to the WI
ERE clause of the SELECT statement identified by the Statement Handle. A statement handle is an object returned by the prepareStatement method of the JDBC class, ~ exttactYalues (Result Set): extracts the properties from the currently fetched row in the result set. A result set is an object returned from the executeQuery method of the FreparedStatement Class in JDBC.

The following methods are defined in the framework of the preferred embodiment for the SQL UPDATE statement:
.generafe(lIx/ate(): returns a update statement (String) based on the defined index and the modificd propcrties as indicated by the appropriate flag in the attribute objects for the columns in the table.
tv = populate(Ipdate (Slalemenl Ham/le): binds the values to the statement identified by the Statement l=landle.
Similarly, the following methods are deftned for the SQL INSERT and DF.I.ETE
statements:
= generatelhiseri(): returns an inscrt statetnent (String) based on the modified properties;
= pnprdatehrverl (Vatement Hafxlle): binds the values to the statement generated in the previous method.
= generatel~.~Ieteo: returns a delete statement based on the defined index;
= pr,pulatel)elete (Statement Haruile): binds the values to the statement generated in the previous method.

211 The above two clayses de('ine the framework of the preferred embodiment.
The framework may be used to map, a relational database table and the operations on the table in a way which provides functionality and flexibility in the object oriented environment without the need for a programmer writing object oriented code to know about the SQL DBMS
details. An example of how the framework may be used to map a tablc is given below with referertoe to the table represented in Figure 1.

3i!
CA9- t 999-ood4 The SQL table in Figure I has the following definition:

Table: EMP[,OYEE
Column Name SQL Type Empno INTEGER
l+trstname CHAR(15) Lastname CHAR(20) Position VARCHAR(128) HircDate DATE

A class mapping of the table may be iniplcntented using the framework of the preferred embodiment by defining a pubGc class Fmployee (shown as ciass 12 in Figure 1) with the following class constructor definition:
public Employee () t Attribute attributes [] = new Attribute [I
lo (empno, firstnante, lastname; position, hireDate};
generator = new BQLGencrator("EMPLOYEE", attributes);
}
Each column in the table has an object defined in the Employee class and a pair of get and set methods defined (as shown in Figure 1 as object and get/set methods 22, 24, 26, 28), For example, method definition and object instantiatiott for the Finpno attribute may bc implemented as follows:
public Integer getEmpno() (return (integer)empno.getValueO;) public void setEmpno (Integer empno) (this,empno.setValue(empno);}
privatc Attribute cmpno =
new Attribute(Types.INTEGER, "Empno", lntcger.elass);
CAy-1999-IK144 Sintilar pairs of methods and object definitions are created by the object oriented programmer for the other columns in the table of the SQL DBMS in a similar manner. The result is that each column of the EMPLOYEE table is mapped by an attribute object in the Employee s class. The flexibility of the framework permits other columns to be easily added to the class without extensive knowledge of the underlying SQL table.

Further methods are then defined in the Employee class to permit opera6ons to be carried out on the relational database table (shown as SQLGenerator method definitions 30, 32, 34 in Figure 1). An example of such a method deftnition is given below, where connection is an object which permits connection to the database as provided by JDBCT"', and PreparedStatement, ResultSet and executeQuery{) are similarly provided by JDBCTM:
public boolean retrieveByEmpno() throws DBLxception {
synchronized (this) {
try empno.setKey (true);
String selectStatemtn - generator.generateSelectO;
PreparedStatcmcnt stmt =
connection.prepareStaternent( selectStatement );
gcncrator.pupulatcSclcct(stmt);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) return false;
generator.extractVAlues(rs);
rs.close();
stint. closeO;
}
catch (Throwable exc) CA9-1999-0(b14 throw new DBException(exc);
) finally }
empno, setKey(false);
}

The above method definition provides a mechanism in the object oriented environment to retrieve a row ftom thc EMPLOYEE table based on a given employee number. As can be seen, use of the framework of the invention permits the object oriented code to be written with little knowledge of the underlying database table. Property mappings are similarly carried out by the methods which are supplied by the framework of the invention. The code which carries out the interface between the object oriented environment and the relational DBMS is contained in the franiework class definitions and therefore the code written by the object oriented programmer in mapping the table to the cyhject oriented class is reduced.

The use of the modified bit in the attribute class permits the update and insert methods to only updaic the properties which have, in fact, changed, as is indicated by the flag for the respective objects which ntap the columns for the tabic.

As the above example alio indicatcs, the statement handle is niade available to the object oriented programmer (this database construct is not hidden in the framework classes). This results in the programmer being able to take advantage of DBM5 caching for greater efficiency in the code written to iniplcment SQLGenerator methods.

Although a preferred embodimcnt of the present invention has been described here in detail, it will be appreciated by those skilled in the art, that variations may be made thereto, without departing from the spirit of the invention or the scopc of the appended claims.

CA9- l 999-0t)4l

Claims (9)

The embodiments of the invention in which an exclusive property or privilege is claimed are defined as follows:

1. A computer system framework for mapping relational database tables to object oriented classes, the framework comprising.
an attribute class for mapping data values from a relational table column to values in an object oriented environment, the attribute class comprising data objects representing data types, a data object for data from the relational table, and methods to access the data objects in the attribute class, wherein the attribute class defines the run-time mapping between the relational table column and the entity class attributes;
a generator class for providing methods to permit the generation of SQL
statements for relational tables defined by the attribute class data objects, wherein the generator class is an active object for run-time, dynamic generation of the SQL statements used to access the relational data, such that the SQL statements are not constrained by schema mapping used to generate the data objects, whereby mapping can be modified during client program execution and the programmer may define a table class corresponding to a relational table, the table class using the attribute class and the generator class of the framework to define the columns of the table in the object oriented environment and to define specific operations on the relational table

2. The framework of claim 1, in which the data objects representing data types in the attribute class comprise a column name object, an SQL type object and an object oriented mapping type object.

3. The framework of claim 1, in which the attribute class further includes data object flags comprising an index flag indicating whether the relational table column mapped by the attribute object is part of a target index for a given SQL statement.

4 The framework of claim 1 in which the attribute class further includes data object flags comprising a modified bit flag indicating whether the attribute value for the attribute class object has been modified.

5. The framework of claim 1 in which the attribute class further includes data object flags comprising a bindable flag indicating whether the attribute value should be bound to the parameter marker for a given SQL statement, and an associated update string data object comprising an SQL fragment to be bound to the parameter marker in the place of the attribute value.

6. The framework of claim 1 in which the generator class includes methods to generate text for SQL statements comprising SELECT, INSERT, UPDATE, and DELETE and further comprises methods to bind values to the statements passed to the relational table database.

7. The framework of claim 6 in which the methods to generate the SELECT
statement further comprises a method to extract values from a fetched row of a relational table for use in a corresponding attribute class object.

8. A computer system framework for mapping relational database tables to object oriented classes, the framework comprising:
an attribute class for mapping data values from a relational table column to values in an object oriented environment, the attribute class comprising:
data objects representing data types, the data objects representing data types comprising a column name object, an SQL, type object and an object oriented mapping type object, a data object for data from the relational table, data object flags comprising an index flag indicating whether the relational table column mapped by an attribute class object is part of a target index for a given SQL
statement;
a modified bit flag indicating whether the attribute value in the attribute class object has been modified, a bindable flag indicating whether the attribute value for the attribute class object is to be bound to a parameter marker for a given SQL statement, and an associated update string data object comprising an SQL fragment to be bound to the parameter marker where the attribute value is not to be bound, and methods to access the data objects in the attribute class, wherein the attribute class defines the runtime mapping between the relational table column and the entity class attributes a generator class comprising:
methods to generate SQL statements comprising SELECT, INSERT, UPDATE, and DELETE, and methods to bind values to the SQL statements passed to the relational table database, the methods to generate the SELECT statement further comprising a method to extract values from a fetched row of a relational table for use in a corresponding attribute class object, wherein the generator class is an active object for the runtime, dynamic generation of the SQL
statements used to access the relational data;
whereby mapping can be modified during client program execution and the programmer may define a table class corresponding to a relational table, the table class using the attribute class and the generator class of the framework to define the columns of the table in the object oriented environment and to define specific operations on the relational table.

9. A computer program product tangibly embodying a program of instructions executable by a computer for providing the framework of claim 8.