System and method for accessing a database from a task written in an object-oriented programming language

[0002] This invention relates to a technique for accessing a database from a task written in an object-oriented programming language, and more particularly, for accessing the database through the application of a class of objects, written in Java™, and employing a JDBC™ Application Program Interface.

[0003] A database is an organized collection of data that can be readily searched, sorted and updated. The database is often logically arranged as tables identified by table names, with columns identified by column names. Access to the database is typically managed by a software interface known as a database management system (DBMS)

[0004] In a distributed computing environment, an application running on a first computer may require access to a database residing on a second computer. The computers each include some form of memory and a processor for the storage and execution of instructions, but they are also interconnected through communications equipment to collectively form a computer network so that they may share information.

[0005] A three-tier model for database access is a common computer network architecture. As the name implies, the three-tier model includes three layers of processing, namely (1) a client machine, (2) a server machine and (3) a database server. A person, using a client application program that needs to access a database, will be operating the client machine. For example, the client application may be an Internet browser and the database may be a directory of an Internet search engine. The client machine communicates a request to the server machine. The request typically includes a uniform resource locator (URL) that identifies the server machine, the communications protocol, and the desired resource. The server machine determines the location of the database and establishes a connection to the database server, to include any handshaking necessary to secure the connection. Thereafter, the server machine relays the request to the database server. The request is managed by a DBMS also located on the database server. The requested data is transmitted from the database server to the server machine, and it is thereafter relayed to the client machine where it is used by the client application program.

[0006] Structured query language (SQL) is a standard protocol for accessing databases. SQL can be used to formulate a database interface statement for subsequently executing a database transaction. SQL includes features that allow users to query, update, insert data into, and delete data from, a database. Requests to a DBMS are made in the form of an SQL transaction.

[0007] Java™ is an object-oriented programming language developed by Sun Microsystems, Inc. The developers of Java™ intended it to be a platform-neutral language. That is, they intended for Java™ programs to be developed on any first computer, and then executed on any second computer, independent of the operating systems controlling either of the computers.

[0008] Java™ programs can be written as stand-alone applications or as applets. An applet is a Java™ program, embedded within an Internet web page, and designed to run on a Java™-enabled Internet browser.

[0009] As an object-oriented programming language, Java™ includes the constructs of class, object, member variables and methods. A class is a template for defining the characteristics of a specific set of data structures and routines. The variables and routines defined by the class are respectively known as member variables and methods. When a program wishes to use those specific data structures and routines, the program instantiates the class, thus creating an object of the class. Additionally, Java™ includes a vector class that handles arrays of variable size for which memory can be allocated as the need arises.

[0010] In the case where a Java™ program requires access to a database, the program can employ the JDBC™ Application Program Interface (JDBC™ API) and its associated JDBC™ drivers. “JDBC” may have, at one time, been an acronym for Java™ Database Connectivity, but it is now a trademark of Sun Microsystems, Inc. Through JDBC™, programs written in Java™ may access many popular databases using standard SQL protocol. More specifically, a Java- program can use JDBC™ to establish a connection with a database, send SQL statements to the database, process a result from the database and return a result to the Java™ program.

[0011] JDBC™ includes the capability of representing a PreparedStatement object. A PreparedStatement is an object used for SQL statements that take one or more parameters as input arguments. It is pre-compiled and stored for future, repetitive use.

[0012] FIG. 1 illustrates a technique used in the prior art to access a database from a client application in a network organized as a three-tier model. The system includes client machine 10, server machine 50 and database server 20. Assume client application 12 desires access to database 24. Client machine 10 communicates its request to server machine 50, wherein task 60 is created. Task 60 invokes internal subtasks 62, which individually formulate SQL statements and reformat the SQL statements according to JDBC™ protocol. JDBC™ 70 is comprised of JDBC™ API 72 and JDBC™ driver 74. Each of subtasks 62 individually interface with JDBC™ 70 via JDBC™ API 72. In turn, for each subtask 62, JDBC™ driver 74 establishes a connection to database 24 via DBMS 22. When JDBC™ 70 receives a result from database 24, it processes and relays the result to the appropriate subtask 62. Thereafter, each subtask 62 closes its individual database connection.

[0013] The prior art suffers several drawbacks. Typically, each of the various subtasks 62 will support different types of database accesses such as query only, insert only, query and update, or query and delete. Some of subtasks 62 may require a single database access while others may require multiple accesses. An individual subtask 62 opens and closes database connections without regard to other subtasks 62 that may be accessing the database at the same time. Consequently, task 60 may have several database connections open concurrently. This is inefficient and may degrade performance of the application as a whole. In addition, since each of subtasks 62 independently interface with JDBC™ 70, the interfaces are redundant. This redundancy can lead to an increase in the number of defects and increased maintenance costs if the JDBC™ interface changes.

[0014] Accordingly, a first object of the present invention is to provide an object-oriented class that can be instantiated by a task to establish and retain a single database connection until the task is completed, thus avoiding multiple and concurrent connections to the database.

[0015] A second object of the present invention is to provide an object-oriented class bundling all database functions such as connect, query, update, and delete, and further defining methods by which a task can easily and efficiently interface with an application program interface to access a database, wherein a programmer developing the task need not be familiar with either SQL or the details of the application program interface.

[0016] A third object of the present invention is to provide an object-oriented class defining methods where database queries are constructed in advance, such as with JDBC™ PreparedStatement objects, and then employed to expedite repetitive SQL transactions.

[0017] In a computer-implemented process, a task requiring access to a database uses an object-oriented class to enable one database connection for the duration of the task, avoiding multiple and concurrent database connections, thus allowing a more efficient use of the database connection.

[0018] The present invention can be implemented on a computer network configured as a three-tier model, which includes a client machine, a server machine and a database server. The server machine includes a JDBC™ Application Program Interface and a JDBC™ driver. Additionally, the server machine includes an embodiment of the present invention, that is, a class (“Database class”) that defines all of the methods necessary to interface between a task and JDBC™, and bundles all of the database functions such as connect, query, update and delete.

[0019] In the case where an application executing a program on the client machine requires access to a database on the database server, the client machine communicates a request to the server machine. The server machine creates a task that instantiates the Database class to create a Database object. The Database object establishes a single connection to the database and retains parameters that will allow for subsequent use of the database connection for the duration of the task. Multiple and concurrent connections to the database are thus avoided.

[0020] Subsequently, the task can invoke Database object methods to format SQL statements and execute SQL transactions with the database via JDBC™. The Database object encapsulates the interface with JDBC™ and relieves the task, and the programmer of the task, of the burden of formatting the SQL statements. The exchange of parameters between the task and the Database object can be in the form of vectors to accommodate a variable number of arguments.

[0021] The methods defined by the Database class are designed to take advantage of JDBC™ PreparedStatement objects. Specifically, a first method can prepare an SQL Transaction in the form of a PreparedStatemen and a second method can subsequently use the PreparedStatement to expedite access to the database. The PreparedStatement can be used repetitively.

[0022] FIG. 1 is a block diagram of a prior art computer network configured as a three-tier model.

[0023] FIG. 2 is a block diagram of a computer network configured as a three-tier model, particularly adapted to carry out the present invention.

[0024] FIG. 3 is a block diagram of a server machine with a more detailed representation of a Database object.

[0025] FIG. 4 is a block diagram illustrating the exchange of parameters between a Database object and a JDBC™ API when initially establishing a connection to a database.

[0026] FIG. 5 is a block diagram illustrating the exchanges of parameters between a task, a Database object and a JDBC™ API for the case where the task wishes to insert data into a database.

[0027] FIG. 6 is a block diagram illustrating the exchange of parameters between a task, a Database object and a JDBC™ API for the case where the task wishes to query a database

[0028] FIG. 7 is a block diagram illustrating the exchanges of parameters between a task, a Database object and a JDBC™ API for the case where the task wishes to drop a connection to a database.

[0029] FIG. 2 illustrates a three-tier model for database access particularly adapted to carry out the present invention. The three major components of this model are client machine 110, server machine 150 and database server 120.

[0030] Database server 120 includes a database 124 under management of DBMS 122. Server machine 150, serves as an interface between client machine 110 and database server 120.

[0031] Server machine 150 includes JDBC™ for passing SQL statements to DBMS 122. Server machine 150 also includes the present invention, Database class 180, which defines a plurality of Database class methods 182. When Database class 180 is instantiated, Database object 200 is created and thereafter provides an interface for accessing database 124.

[0032] Client machine 110 is running a client application 112 that desires access to database 124. To achieve this objective, client machine 110 communicates a request to server machine 150. In response, server machine 150 creates task 160, which instantiates Database class 180 to create Database object 200. Thereafter, task 160, and its subtasks 162, can interface with database 124 via Database object 200.

[0033] JDBC™ 170 is comprised of a JDBC™ API 172 and a JDBC™ driver 174. Database object 200 will typically format a SQL statement according to the specification of JDBC™ API 172. In the case of a complicated SQL statement for a one-time request, Database object 200 can accommodate a SQL “where” clause to be constructed by task 160.

[0034] FIG. 3 shows server machine 150 with a more detailed representation of Database object 200 as it provides an interface between task 160 and JDBC™ 170. Upon creation, Database object 200 establishes a connection to database 124 via JDBC™ 170, and saves, as connection member variable 205, a connection handle which permits subsequent use of the database connection. Task 160 utilizes Database object 200 by exchanging parameters in the form of vectors and invoking Database object methods. This illustration includes table name vector 305, column name vector 310, key column name vector 315, key data vector 320, and resultant data vector 325, and some exemplary Database object methods, namely, the dbPInsert method 210, dbPInsert method 220, dbPQuery method 230, dbQuery method 240 and dbDrop method 250. These vectors and object methods are described below.

[0035] FIG. 4 illustrates the exchange of parameters between Database object 200 and JDBC™ 170 when initially establishing a connection to database 124. Database object 200 passes to JDBC™ 170 a database URL 330, a user name 335 and a user password 340. JDBC™ 170 establishes the database connection and returns to Database object 200 a connection object 345. Connection object 345 provides a connection handle to allow for subsequent use of the database connection. Database object 200 saves the connection object 345 as connection member variable 205. When Database object 200 needs to subsequently use the database connection, Database object 200 can readily recall connection member variable 205. Note that JDBC™ 170 handles all of the interface with database server 120 thus relieving Database object 200 of this burden, and Database object 200 relieves task 160 of the burden of interfacing with JDBC™ 170.

[0036] FIG. 5 illustrates the exchanges of parameters between task 160, Database object 200 and JDBC™ 170 for the case where task 160 wishes to insert data into database 124. Task 160 invokes dbPInsert method 210 and passes to it a table name vector 305 and a column name vector 310. The dbPInsert method 210 formulates an SQL Insert PreparedStatement, saves it as SQL Insert PreparedStatement member variable 215, and returns to task 160 an SQL Insert PreparedStatement handle 350.

[0037] Subsequently, task 160 invokes the dbPInsert method 220 and passes to it a column data vector 355 and, from earlier, the SQL Insert PreparedStatement handle 350. The dbPInsert method 220 recalls SQL Insert PreparedStatement member variable 215 and uses it and the data identified by column data vector 355 to format SQL insert statement 360. The dbPInsert method 220 then passes to JDBC™ 170 the SQL insert statement 360.

[0038] From this point, JDBC™ 170 handles the Interface to database server 120 for the insertion of the subject data into database 124. Database object 200 thus relieves task 160 of the burdens of formatting SQL insert statements and of interfacing to JDBC™ 170.

[0039] Database object 200 can use SQL Insert. PreparedStatement member variable 215 repetitively for the insertion of data into database 124. That is, in the case where task 160 wishes to make multiple insertions of data into database 124, task 160 can repetitively invoke dbPInsert method 220, which can repetitively recall SQL Insert PreparedStatement member variable 215 to streamline the operation. Thereby, the inefficiency of redundantly formatting an individual SQL insert statement for the insertion of each individual data item is drastically reduced.

[0040] FIG. 6 illustrates the exchange of parameters between task 160, Database object 200 and JDBC™ 170 for the case where task 160 wishes to query database 124. Task 160 invokes dbPQuery method 230 and passes to it table name vector 305, column name vector 310 and key column name vector 315. The dbPQuery method 230 formulates an SQL Query PreparedStatement, saves it as SQL Query PreparedStatement member variable 235, and returns to task 160 an SQL Query PreparedStatement handle 365.

[0041] Subsequently, task 160 invokes the dbQuery method 240 and passes to it a key data vector 370 and, from earlier, the SQL Query PreparedStatement handle 365. The dbQuery method 240 recalls SQL Query Prepared Statement member variable 235 and uses it and the data identified by key data vector 370 to format SQL query statement 375. The dbQuery method 240 then passes to JDBC™ 170 the SQL query statement 375.

[0042] From this point, JDBC™ 170 handles the interface to database server 120 for the query of database 124. JDBC™ 170 returns to Database object 200 a query result 380, which Database object 200 processes, and thereafter returns to task 160 a resultant data vector 325. Database object 200 thus relieves task 160 of the burdens of formatting SQL query statements and of interfacing to JDBC™ 170.

[0043] Database object 200 can use SQL Query PreparedStatement member variable 235 repetitively to query database 124. That is, in the case where task 60 wishes to make multiple queries of database 124, task 160 can repetitively invoke dbQuery method 240 which can repetitively recall SQL Query PreparedStatement member variable 235 to streamline the operation. Thereby, the inefficiency of redundantly formatting an individual SQL query statement each individual query of the database is drastically reduced.

[0044] FIG. 7 illustrates the exchanges of parameters between task 160, Database object 200 and JDBC™ 170 for the case where task 160 wishes to drop the connection to database 124. Task 160 invokes dbDrop method 250. The dbDrop method 250 relinquishes connection member variable 205, SQL Insert PreparedStatement member variable 215 and SQL Query PreparedStatement member variable 235.

[0045] The preceding examples, as illustrated in FIGS. 3 through 8, describe some of the possible applications of Database class 180 and Database object 200. It should be understood that various alternatives and modifications can be devised by those skilled in the art without departing from the invention. For example, Database class 180 may be defined to include methods for updating or deleting data from database 124, or for using more complex SQL features such as the “where” clause. These alternative methods could also utilize JDBC™ PreparedStatements as in the examples.

[0046] Although described here in the context of a three-tier computer network, the present invention may be implemented in a two-tier computer network, or on a stand-alone system where the database resides in the same machine as the task seeking access to the database. Further, while the procedures required to execute the invention hereof are indicated as already loaded into the memory of the various computers, they may be configured on a storage media, such as data memory 100, in FIG. 2, for subsequent loading. Accordingly, the present invention is intended to embrace all such alternatives, modifications and variances that fall within the scope of the appended claims.