WO2004044785A1

WO2004044785A1 - Dynamic transparent object querying generation and parsing

Info

Publication number: WO2004044785A1
Application number: PCT/US2003/035858
Authority: WO
Inventors: Ward Mullins; Alexandre Martins
Original assignee: Thought, Inc.
Priority date: 2002-11-12
Filing date: 2003-11-12
Publication date: 2004-05-27
Also published as: AU2003294257A1

Abstract

A system and method of enhanced database access and performance when correlating or translating one database to another database or to an object programming application, or simply accessing data from an object programming application by utilizing object-based dynamic querying. A simplified interface is provided for inputting an object-based query statement and obtaining data and delegating SQL statement generation and data source access to other software components within the system in order to coordinate multiple software components and provide ease of use. Performance may also be enhanced by utilizing an optional enhanced multiple user data caching system. Thus, the present invention is directed to dynamic mapping of databases to selected objects and a system and method for providing a dynamic object-based querying system in such an environment or system.

Description

DYNAMIC TRANSPARENT OBJECT QUERYING GENERATION AND PARSING

Technical Field

The present invention relates in general to enhancing data source access and application performance that involves data source access by dynamically resolving data source and programming application schema mismatches when using an object querying language, such as Enterprise Java Bean Querying Language (EJB-QL), to query against a data source where the data source uses a JDBC driver that requires a different querying language such as a language with relational expressions, for example, SQL statements containing logic expressions of AND, OR, NOT, etc.

In a preferred implementation the invention enhances data source access by providing a system for accessing and updating data in a data source in an environment that utilizes an object querying language querying system, wherein a resource defines the relationships between two or more objects in at least one object model, a schema mapping resource maps at least one data source schema to at least one object application schema and thereby statically or dynamically resolve any mismatches between relationships in at least one data source schema and at least one object model schema, and a custom parser is used generate SQL statements that are specific for a data source JDBC driver registered with the data source access system. Background Art

Computer databases or similar constructions (hereinafter referred to as data stores) are powerful tools for storage, organization, retrieval and other handling of various types of information. However, there are different database models, or formats, for data access that are incompatible with each other, and may also be incompatible with, or remarkably different from, an object programming application. In this respect, complex relationships between objects present in the object programming application may be entirely absent in a relational or object database being accessed or updated.

Until the present invention there has not been an efficient and flexible way of querying a data source for results by simply using existing complex relationships between objects, or by using dynamically created relationships between objects, without any need for the underling data source to contain or define such relationships in its data source schema, tables or structure. Prior to the present invention object-based querying has been dependent upon table structures corresponding to the data source, and such object queries are inherently linked to and constrained by the underlying data source structure and tables. Nonetheless, a number of relational database types have achieved a high level of popularity and proliferation.

The two most common database models are object and relational, and object programming models are frequently used for internet applications involving database accessing. Concurrently, relational database systems are frequently used as the data source to be accessed by the internet. A strong need exists for software that is capable of object-based querying without regard to the structure of the underlying data source, or is capable of accessing multiple data sources or data source structures without modifying the object-based query.

As an example of common object programming applications, the popular Java language with its server-side component, Enterprise Java Beans, is being widely used to support object model programming applications that access and use data from databases that have a JDBC driver. Thus, it is desirable to be able to adapt and use many of the traditional relational database tools, utilities, systems and corporate procedures with the newer object format of many more recent web-based applications. Since many users may be accessing a single application, there is a need for speeding up database accesses and for synchronizing object programming application transactions with database accesses and updates. Significant drawbacks are associated with using a relational database model in conjunction with an object programming model for the reasons set forth above. However, relational database models are still the most widely accepted traditional format for reliable and quick access to data while using complex data search queries. This model allows the user to store data records and to access the data via a query language that uses relational expressions such as AND, OR, NOT, etc. Over the years, the vast majority of production databases have been relational databases, each having its own relational arrangement, and accessible by a particular query language. While convenient for set-up and arranging data, access is had only by those having substantial knowledge of the various relationships between pieces of data in the database. JDBC drivers have been designed to formulate database specific SQL queries or statements from standard SQL strings passed to them by an object programming application. Ordinarily, such JDBC drivers are specific to a database, and separate statements and separate JDBC drivers must be utilized to access different databases. This is complicated by the fact that many object programming applications either do not permit simultaneous attachment to multiple databases or poorly coordinate transactions that impact multiple databases. Accordingly, relational databases can sometimes be awkward to access, and transferring data from one database to another database can be slow and painful, unless a specific transfer program has been specifically written or tailored to effect that specific transfer of data.

The object database model is a newer model that has rapidly increased in usage due to recent trends such as the world-wide-web and application service provider (ASP) architectures. Such object databases have sometimes been used as the primary source for accessing data, for example, Jasmine and ObjectStore are available from Computer Associates Intl., Inc. and Object Design Inc. (ODI), respectively. However, accessing data in such permanently stored object database has raised serious performance issues for larger applications when complex querying is required (as compared to relational databases). Complex querying is usually faster using relational databases since query engines may be more highly developed for relational database accesses. Others have tried to resolve the slowness problem of complex querying object database by developing a standard object-based querying syntax, such as EJB QL that can be utilized to access data from an object data source, or can be tailored to provide an EJB QL query that uses tables that define the structure of particular relational data source to query that data source. However, EJB QL has had the drawbacks of either requiring a "shadow object data source", either in memory or disk resident, or requiring queries to be constrained by the structures of an underlying data source, such as table structures. In such a case, EJB QL queries could not be entirely abstract and a query needed to be rewritten or tailored to a specific underlying relational data source.

Therefore, a strong need exists for a flexible software package that is capable of quickly and efficiently parsing an object-based query, translating the query into SQL statements necessary to access data and populate objects with the data, and to then query the populated objects for a result set corresponding to the object-based query optionally without regard to the structure of the underlying data source being accessed or the structure of its table definitions. Prior to the present invention a dynamic, flexible software package did not exist that was capable of efficiently providing such features.

If it were possible to successfully provide a commercially viable fast and efficient dynamic object-based querying software system, there would be little need to utilize a separate parallel object data source from which complex object-based querying results sets could be quickly obtained, or to rewrite object-based queries to correspond to the structure of tables for a specific data source. A strong need exists for a real-time dynamic object-based querying system that is independent of data source table definitions or structures.

Prior to the present invention such a dynamic, flexible software package did not exist that was capable of efficiently providing such features or software modules.

Summary of the Invention

Accordingly, it is an object of the present invention to provide a simple software package capable of accepting an object-based query in a defined syntax, such as an EJB QL syntax, wherein the elements of the query are either an object map name or the name of a link between two objects, that is also capable of processing the query, accessing a data source for results corresponding to the query and outputting results for the object based query.

A preferred object of the present invention is to provide such a software package comprising having three software components or features generally described as follows: I. Software Component A comprising an interface for setting a results output source and further comprising: (i) an interface for inputting an object based query in a syntax such as an EJB QL query syntax, wherein elements of the query having this structured syntax are either a map name in an object schema (wherein a map is a defined as a description of an object and its attributes) or a link name in an object schema (wherein a link is defined as a description of a relationship of two maps in an object schema, such as in a navigation model), (ii) an interface for inputting the name or location of an output source for the results obtained from the query, (iii) a compiler designed to parses the query having a known syntax such as an EJB QL query syntax, wherein the compiler is written manually or by using a compiler generating software program (such as standard open source compiler generating software program) and is designed with the ability to understand and parse the object based query syntax, such as the standard EJB QL syntax set forth in the SUN EJB QL BNF document (See Chapter 11.4, pages 257 and 258 of SUN EJB 2.1 Specification.), and (iv) an output module designed to pass the parsed EJB

QL query on to Software Component B. π. Software Component B comprising an input and an output means, and comprising components or features which after receiving the input of: (i) a parsed object based query from Software Component A, (ii) an object model name (such as a navigation model name) corresponding to the object based query and (iii) the name of a mapping repository having the definition of an object to object or object to relational mapping relationships, such as a CocoBase mapping repository (comprising object schema/object map definitions/descriptions, and optionally comprising data source schema/relational map definitions/descriptions) , such software components or features have the ability to generate Java code for custom query objects and to dynamically create any query customized object or O/X map(s), e.g., CocoBase map(s) that might be utilized in obtaining results, based upon existing maps in a mapping repository, optionally generating such custom query objects and map's independently of any table definitions or physical database configuration structure, and comprising the ability to pass custom query objects and map name(s) to Software Component C. UI. Software Component C comprises software modules or features capable of (i) utilizing a mapping repository name, custom query objects and query customized maps as a basis to generate data source access queries, such as SQL, for a specific data source, (ii) populating custom query objects with the information obtained by accessing the data source, (iii) accessing data in populated custom query objects and (iv) outputting the results obtained to a designated output source.

A further preferred object of the present invention is the above software package wherein the object-based query in defined by EJB QL syntax, wherein the elements of the query are either a object map name or the name of a link between two objects, that is also capable of processing the query, accessing a data source for results corresponding to the query and outputting results for the object based query, wherein the software package is integrated with a commercially available, or proprietary O/R mapping software package such as, the commercially available CocoBase Enterprise for O/R Software Package.

Another object of the present invention is to provide an O/R Software package comprising the above object-based query software package integrated with a GUI console tool, optionally having at least one API for a stand alone interface, a command line interface, or a transparent persistence API, and optionally being integrated with object programming language entity beans such as BMP or CMP entity beans (EJB-QL and API for implementation can be defined in the bean descriptor, for example), and the like. In a further preferred embodiment the object-based query software package also comprised the feature of being called from a generic EJB-QL API that is imbedded in an entity bean application such that the object-based query does not need to be pre-defined and can be defined at runtime of the application.

In a further preferred object of the invention, such a software module is provided to access a data source via at least one JDBC driver, a software module is provided to access a resource having one of more of a data source mapping schema, an object schema and/or an object to relational mapping information source, a software module to parse an query statement and generate an object encapsulating the query to access the data source and logic for obtaining and parsing the data returned from the data source, and a software module capable of dynamically creating an object to relational map specific to the object- based query (such as an EJB QL query) and to join objects to create new hybrid objects ready to be populated by data that is returned from the data source (as processed by the query object).

Another preferred object of the invention is to provide such an object-based querying software package having a wrapper or facade interface and API to be utilized to coordinate relational data systems and object data systems and to control multiple software modules that are utilized to load, insert, update and delete data from data systems and objects or object models in object programming systems during implementation of such functions in the overall system or network. In a preferred object, the present invention provides a simple facade and object programming API designed to control the CocoBase O/R Software tools program components, including object based querying functionality, while particularly controlling the more complex components, to provide coordinated object graft and data persistence, including object model navigator components, transaction coordination components, CocoAdmin function components, and optionally caching components.

Another object of the present invention is to overcome the drawbacks of the conventional art and achieve flexibility and dynamic operation in the accessing of data by multiple users by providing an improved caching system for multiple users that leverages objects that can be utilized for fast and efficient object-based querying. It is an additional object of the present invention to provide easy translations between databases and applications having a variety of different formats or data store models, and to provide a flexible dynamic object-based querying functionality that is independent of the table structure of underlying data in a data source.

Another object of the present invention is to provide dynamic object-based querying coordinated with a object/relational mapping system wherein object programming applications are tailored to provide object-based querying of data while delegating parsing of the query, accessing a database and the generation of SQL strings to a runtime library repository and associated software modules, which repository can access the database directly or through a database driver, such as a JDBC driver, without the need to imbed specific database accessing mechanisms in the application code or in the object-based query. It is a preferred object of the invention to provide such a system with a concurrent parameter setting mechanism wherein a given object-based querying syntax that is to be utilized for object-based querying can be set and parsed, and the runtime library repository can be set to utilize standard SQL strings and parse them with changes for accessing a particular database, and to generate database specific database calls and database specific SQL strings.

In one preferred object of the invention, a software program would be provided that would be able to generate application programming code for query customized objects or maps based upon map names or link names that are extracted from an object- based querying statement by a object-based query syntax parser and compiler by utilizing existing mapping repository and navigation model descriptions to dynamically generate programming code for query customized objects and maps and to provide a programming application that will delegate to a runtime library repository the functions of accessing a database and the generation of SQL strings that are specific to a database or JDBC driver based upon an object-based query statement, and to coordinate such data and maps with one or more object models that can be created, updated, and navigated dynamically in response to changes to one or more of the data source, the mapping and runtime repositories, and one or more object models. In a preferred aspect, the software program has a coordination facility to persist any changes of data, objects or object models to a data storage facility and also provides a coordinated multiple user distributed caching facility.

In another prefeπed object of the invention, the runtime library repository can be modified and tailored to optimize database access calls and SQL strings for a particular database.

It still an additional object of the present invention to provide a mapping system wherein different maps for particular objects can be used to provide varying levels of security.

It is again another object of the present invention to provide a mapping system having object based querying capability, wherein data changes related to a particular object can be promulgated with global changes for that object, if desired. A particularly prefeπed object is to provide such a system as described above further comprising a multiple user caching system that may be termed a CANDON (Cache As Needed, Disable On Notification) caching system, where notification that a unit of data or object in a cache is invalid is provided to any user that has a cache containing that unit of data or object which has been updated by using a cache notification systems termed a UNS (Unit Notification System).

It is still a further object of the present invention to provide a mapping system when a data map for an object can be easily edited without extensive knowledge of the relational database as a source of the data.

It is again another object of the present invention to provide a mapping system wherein the metadata describing a map of a datastore can be dynamically evaluated.

It is again another object of the present invention to provide a mapping system, wherein data can be accessed more quickly and with more powerful complex querying than is possible with a conventional data store accessing arrangements.

It is again a further object of the present invention to provide a mapping system in which frequently-used data can be more easily accessed than other types of data.

It is yet a further object of the present invention to provide a mapping system in which a wide variety of different data languages can be easily used.

It is still a further object of the present invention to provide a mapping system wherein virtually any type of data store architecture can be translated so as to be useful by an object software application, or other types of software applications, while providing the ability to access the data from that data store using a dynamic object-based querying system.

It is again a further object of the present invention to provide a mapping system wherein data store to data store mapping is easily facilitated.

It is still another object of the present invention to provide a fully synchronized caching system that utilizes a messaging system such as the Java Messaging Service (JMS) to provide coordination facilities for a multi-user system or distributed caching environment and provides an object-based querying capability system.

In some objects of the present invention, concepts are based in part upon concepts present in U.S. Patent No. 5,857,197, (incorporated herein by reference) or that are reasonably inferable from reviewing that patent, in order for the present invention to provide an improved mappirig system for handling data requested by an object software application model in a manner that is compatible with relational data stores. A dynamic repository-based mapping system is used. The system does not put all of the data access code in java objects, for example metadata (data about Java objects including complex Java objects that have relationships with other java objects) does not need to be stored in a java object. In a preferred aspect, it is an object of the present invention to provide a database access system that does permits a java object application to delegate database accesses to a runtime library repository.

In a preferred object of the present invention, a software programming module (or modules) that can provide dynamic object-based querying and can automatically generate object source code from at least one data base schema map, at least one object programming application schema, or from a combination of at least one database schema map and at least one object programming application schema. The code generated for the application can be set to delegate database access and SQL string generation to the runtime library repository instead of including within the application the more limiting code for non-delegated database access by the application. This arrangement allows the mapping information and associated metadata to be easily accessed, changed and used to convert thousands of lines of code in a data object, as needed. The mapping information can be used to map from objects to relational models or vice versa and generate appropriate code.

Definitions

For the purposes of the present application, the following definitions are given as a meaning for terms used herein throughout this application to avoid any confusion with possible multiple meanings for such terms. Other terms used herein have meanings that are well recognized in the art, and their meanings will be clear from the context in which they are used in this application.

A "module" in the computer programming context is an organized set of computer code designed to act on some externally passed in data, where everything needed for that action is passed in to the module. An "object" in the object oriented programming context is an organized set of encapsulated programming code designed to act on itself at the request of some external system, which system may pass in some additional information to the object when it delegates a task to the object in that request.

A "composite object", or an object programming "component", in the object programming context each refer to an object comprising a complex and organized set of objects that are encapsulated to form the composite object. The two terms "composite object" and "component" (e.g., a J2EE component such as an EJB, Enteφrise Java Bean) may be utilized in an interchangeable manner in referring to the same type of logical constructs and concepts.

A "delegation" in the object oriented programming context is where an object or a programming application permits another simple object, set of objects, composite object, or a module to perform an action by simply requesting the action from the delegated simple object, set of objects, composite object or a module. A "non-delegation" database access in the object oriented programming context is where an object or a programming application has specific code imbedded in the application which directly controls database calls and the generation of SQL strings, wherein the imbedded code is specifically tailored for accessing a particular database or for a specific database schema. A "user interface" for an object oriented application, such as a Java Server Page

(JSP), a Swing GUI, and the like, refers to a software component or module that provides a feature for a user that will permit the user to interact with an object or programming application in some way, such as the interactions of finding, selecting, inserting, updating and deleting data in a database. A "library" is a set of definitions, data, objects or programming modules that may be accessed by a computer programming application to obtain information or to delegate tasks.

A "repository" in the object programming context and in the context of this application is a special set of libraries that may include, among other things, items related to object to object mapping, object to relational mapping and database accessing information, database parameters, optimized database access routines, and the like. A repository may be a single file or may be a set of files. The format of items in a repository may vary widely according to the desire of a computer programmer user or developer and may be in one or more of formats such as simple text, XML, XMI, UML, JDBC, source code, compiled code, and the like.

An "object map" is defined as the description of an object and its attributes, and such an object may optionally include in its description how data coπesponding to that object is structured in a corresponding data source.

A "map name" is the name of such a descriptor file that contains an object map. A "link name" is the name of a descriptor file that contains the description/definition for the relationship between two objects in an object model.

A "navigation model" is a descriptor file or files which contain(s) the names of objects in one or more object schemas, and a description of the relationships (links) between objects in the model. The navigation model descriptor files may also include the objects maps present in the navigation model or the navigation model may be associated with a mapping repository that includes object maps coπesponding to object names set forth in the navigation model.

Detailed Description of the Prefeπed Embodiments The present invention is based in part on U.S. Patent No. 5,857,197,

(incoφorated herein by reference), and provides a mapping system for handling data requested by an object software application model in a manner that is compatible with relational data stores. A dynamic repository-based mapping system is used. The system does not put all of the data access code in java objects, for example metadata (data about java objects including complex java objects that have relationships with other java objects) does not need to be stored in a java object. Instead, the mapping information related to object definitions and some metadata can be placed in a separate structure that is independently stored. This allows the mapping information and associated metadata to be easily accessed, changed and used to convert thousands of lines of code in a data object, as needed. The mapping information can be used to map from objects to relational models or vice versa, objects to objects, object to COBAL or vice versa, and object to XML and the like.

Certain of the embodiments of the present invention are embodied in a suite of products by Thought, Inc., referred to as "CocoBase". Aspects of the CocoBase ' technology are also described in U.S. Patent No. 5,857,197, supra. Also, a number of details of the system of the present invention are provided now as state of the art in the documents that have been posted on the website of Thought, Inc. or are otherwise available on the internet as publications.

In one embodiment, the mapping information, rules, or metadata can be maintained in a human-readable format and can be inteφreted by an application to apply the rules to data objects. This information can be stored directly in the data source or can be stored in a separate file. In either case, this information is called a "repository". This repository provides an easily maintainable and transportable format for the mapping rules. The rules and java object relationships can be updated, or otherwise changed, dynamically even while other rules or object relationships in the repository are being used to perform conversions. The rules and relationship definitions can be embedded in standard formats such as in Java code, e-mailed, sent as plain text, etc. This allows for flexible transfer and proliferation of the rules to other sites where the rules can be used to access objects (which can be bundled with the rules), used to derive other rules, etc. In a particularly prefeπed embodiment, this repository is in an XML (extensible markup language) format, and the object model definitions can also be present in an XMI (XML metadata interchange) format or can be easily exported to an XMI file format.

A more prefeπed embodiment is such a system and software as provided by an updated CocoBase Enteφrise Object/Relation Software package (hereafter CocoBase), which includes the software tool CocoAdmin. Such updated software package, is available, or is available shortly, from Thought, Inc., San Francisco, California. An embodiment of the invention using this package is described below.

Importing and exporting maps as XML repositories in CocoBase has been extensively described in other publications available at the website www.thoughtinc.com. Therefore, such details will not be repeated herein. For example, the CocoBase Programmer's Guide available at the above website provides further information about how to work with XML format maps and CocoBase, which published document is incoφorated herein by reference. This document also explains how to set CocoBase (or CocoAdmin of CocoBase) and its runtime modules to cache maps that an application will be using in order to speed up user access. Other user hints and instructions are provided therein. For example, one URL option in CocoBase is to specify an XML repository for the maps that an application will be using. Instead of using the database repository, the runtime can use an XML repository exclusively. This technique allows CocoBase to execute against a production database that cannot be modified with map definition tables. Optional mapping server plug-ins are also described.

Data source maps according to the present invention may be utilized to generate application programming code, such as Java source code. For example, the CocoAdmin tool will allow a user to generate Java for a range of targets including most commercial Application Servers and Enteφrise Java Bean Servers. A complete list of pre-configured targets can be viewed from the Connections window in the Generate Java wizard of CocoAdmin. Any Java class can readily be used with CocoBase, whether or not it was generated by the CocoAdmin tool. However, there may be significant advantages that occur from using the code generation facilities of CocoBase to quickly generate your Java classes. For example, the disadvantages inherent with data source specific non- delegation database access code can be avoided by generating code, which delegates the database access functions to CocoBase runtime libraries. Only CocoBase is believed to utilize this type of code that results in dynamic O/R mapping system capabilities. The CocoAdmin tool can use a combination of the map definition and database foreign key relationships (if any exist) to define how the Java class is going to be generated. The Generate Java wizard of CocoAdmin can be started in one of four ways to generate Java code from a CocoBase map. This topic is also covered in more detail in the CocoBase Programmer's Guide available at the above website. Mapping from One Data Source Format to Another Data Source Format

In another embodiment the invention provides a system for mapping from a first database format to a second database format, or from one database to another database of the same type, as a way for transferring data or synchronizing data sources. The system includes: data in the first database format stored in the system; rules for translating from the first format to the second format stored as a separate structure from the data; and means for applying the rules to the data to obtain the second format. This system may include a means for transferring the data from a first data source to a second data source. In one embodiment this system includes a simple computer program in a computer language such as Java, which reads the data from the first database using a repository map that may optionally be cached in the computer memory of the system and then stores the accessed data to a second database using the same or a different repository map.

The above procedures implement a dynamic mapping layer in an exchange format such as XML that can directly exchange data from two data sources in a more dynamically controllable fashion to each other for the puφoses of data import, export and exchange without the requirement of an intermediate third object. In such a model the developer would not be constrained by the structure of the database to which or from where the data is being transfeπed.

Also, the above example is an example of object to "DataSource" mapping. Data may be mapped to (or from) object and relational databases and may also be stored in XML. Also, XML stored maps can be utilized by CocoAdmin as a resource from which to generate object code for user applications. In one implementation, two system users

(same or different system) using a single (or several major recognized) XML standard(s), can export or import (as XML files) either or both of the XML map definitions

(relationships of data) and data (dataset) of the database. This would permit a user to distribute parts or all of a dataset (and/or maps) to a distributed network, or to multiple network users in useable XML format files. Such a phenomenon may be refeπed to generically as O/X mapping (object to XML) or R/X (relational to XML) in addition to O/R (object to relational mapping). In one embodiment, O/X mapping step would permit importation or exportation of data (that was converted to XML) from XML to and from any business object. For example, data from any business implementation object could be exported as XML along with its relationships as defined by the mapping tool. This would implement a more flexible and dynamic mapping facility for taking XML datasets and using them to populate object instances other than the original ones that may have created the data. One good example of such an implementation would be to take a first XML document that is presented as a user screen interface to a dataset and utilize the information (dataset) populating the first XML document to seamless populate a second XML document (datasource to datasource via a XML (or another exchange format similar to XML) translation step.

There are many ways to utilize the above above implementations as part of either uni-directional or bi-directional mapping. Intranets and e-commerce applications can both take advantage of these important features. There are distinctive advantages when using a dynamic mapping layer where a java object provides translation by mapping such objects of a first data source (relational or object database) and also mapping such object to an XML or other second format data source. This allows more developer control as to how datasets are exchanged, filtered and/or validated between a first data source and a second data source.

One business use would be for a first party to use XML as a data export document to send information to a second party (e.g., items offered for sale) and the second party would import that information in order to generate a second XML data document (e.g., purchase order) that the first party can import. This would provide a much needed exchange format for the newly developing business to business market that might be able to rely on standardized XML data formats and avoid problems caused by object programming models that may vary widely from company to company.

Such a dynamic mapping facility would provide a critical missing facility to easily translate data models and object model into a format that can be readily used by business to business companies, or by the same company for department to department interactions. Documents describing the use of CocoBase products that do mapping of data sources other than relational databases have been published, and such products may be used in conjunction with the above embodiments. Examples of mapping include, mapping from object to object databases and from COBAL (mainframe computers of businesses often use this language data source) to object. See the IBM "white paper" on COBAL to object mapping (download from URL http://www.thoughtinc.com/websrjhere.html).

In one embodiment of the system of the present invention a translation layer translates between an object application (or a potential object application, i.e. an object model) to at least one relational database which includes data entries organized as tables and records. Examples are database architectures supported by companies such as Oracle, Sybase, Informix, etc. Such an organization is well-suited for manipulation by relational query languages such as SQL. However, the traditional relational database organization is not ideally suited for manipulation by an object-based system. Some tools, such as Java Database Connectivity (JDBC) exist to achieve some degree of indirect relational to object mapping by accommodating some differences between an object model or application and the structure of the relational database. However, these tools also have drawback in that they are often not scalable, require extensive manual recoding for different object applications and are complex to use. Even tools (often mistakenly refeπed to as object to relational, i.e., O/R tools) that generate JDBC code (instead of generating a pure SQL string that a JDBC driver can utilize to subsequently create a pure SQL statement or query bundled in a JDBC format) are limited since they are often application specific, database specific, or specific to both.

In one prefeπed embodiment, a translation or abstract layer communicates with at least one JDBC (relational database driver) and at least one primitive Extended Java

Bean (EJB) construct. The function of such a translation layer (generally called the O/R layer in CocoBase documentation, for example) is to translate object-based queries for the data into queries that JDBC can, translate into queries for a relational database. In a prefeπed embodiment, the translation layer can generate an SQL string (or strings) based upon the object-based queries, which can be passed to at least one JDBC, which JDBC can then generate an SQL statement from the SQL string. Similarly, the abstract layer accepts results from the queries and provides them to one or more of the EJB constructs in a suitable object format. The existence of JDBC is not necessary for all implementations of the invention. Also, different types of databases, data models, computation architectures, and the like can be used within the basic principle of the present invention. This mapping can be one-to-one, many-to-one, many-to-many, or any variation.

Another prefeπed embodiment of the present invention allows for mapping tables to be plain text, or to be text-based XML repository files. This not only allows the maps to be human readable and editable with standard editions, email programs, web browsers, etc., but allows for easy transportability and delivery of the maps.

Another feature of the maps is that they can be dynamically loaded into a computer system to provide for new dynamic mapping in a system that can that can continue to run while the new maps are loaded and executed. As described supra, the use of the translation maps provides advantages in organization, standardization of interfaces, efficiency of design, compatibility, scalability, portability and other advantages. This translation system of the present invention provides an entire set of tools to simplify and improve an operators ability to manipulate the maps without requiring a high level of programming knowledge or database query language familiarity.

The present invention can be utilized in such a manner as to leverage the features of the above dynamic mapping system. The present invention provides a simple software package capable of accepting an object-based query in a defined syntax, such as an EJB QL syntax, wherein the elements of the query are either an object map name or the name of a link between two objects, that is also capable of processing the query, accessing a data source for results coπesponding to the query and outputting results for the object based query.

In a prefeπed embodiment the present invention provides such a software package comprising three software components or features generally described as follows:

I. Software Component A comprising an interface for setting a results output source and further comprising: (i) an interface for inputting an object based query in a syntax such as an EJB QL query syntax, wherein elements of the query having this structured syntax are either a map name in an object schema (wherein a map is a defined as a description of an object and its attributes) or a link name in an object schema (wherein a link is defined as a description of a relationship of two maps in an object schema, such as in a navigation model), (ii) an interface for inputting the name or location of an output source for the results obtained from the query, (iii) a compiler designed to parses the query having a known syntax such as an EJB QL query syntax, wherein the compiler is written manually or by using a compiler generating software program (such as standard open source compiler generating software program) and is designed with the ability to understand and parse the object based query syntax, such as the standard EJB QL syntax set forth in the SUN

EJB QL BNF document (See Chapter 11.4, pages 257 and 258 of SUN EJB 2.1 Specification.), and (iv) an output module designed to pass the parsed EJB QL query on to Software Component B. II. Software Component B comprising an input and an output means, and comprising components or features which after receiving the input of: (i) a parsed object based query from Software Component A, (ii) an object model name (such as a navigation model name) coπesponding to the object based query and (iii) the name of a mapping repository having the definition of an object to object or object to relational mapping relationships, such as a CocoBase mapping repository (comprising object schema/object map definitions/descriptions, and optionally comprising data source schema/relational map definitions/descriptions) , such software components or features have the ability to generate Java code for custom query objects and to dynamically create any query customized object or O/X map(s), e.g., CocoBase map(s) that might be utilized in obtaining results, based upon existing maps in a mapping repository, optionally generating such custom query objects and map's independently of any table definitions or physical database configuration structure, and comprising the ability to pass custom query objects and map name(s) to Software Component C. in. Software Component C comprises software modules or features capable of (i) utilizing a mapping repository name, custom query objects and query customized maps as a basis to generate data source access queries, such as SQL, for a specific data source, (ii) populating custom query objects with the information obtained by accessing the data source, (iii) accessing data in populated custom query objects and (iv) outputting the results obtained to a designated output source.

In a prefeπed embodiment of the present invention, the above software package utilizes an object-based query defined by an EJB QL syntax, wherein the elements of the query are either a object map name or the name of a link between two objects, that is also capable of processing the query, accessing a data source for results coπesponding to the query and outputting results for the object based query, wherein the software package is integrated with a commercially available, or proprietary O/R mapping software package such as, the commercially available CocoBase Enteφrise for O/R Software Package. In one embodiment, the present invention provides an O/R Software package comprising the above object-based query software package integrated with a GUI console tool, optionally having at least one API for a stand alone interface, a command line interface, or a transparent persistence API, and optionally being integrated with object programming language entity beans such as BMP or CMP entity beans (EJB-QL and API for implementation can be defined in the bean descriptor, for example), and the like. In a further prefeπed embodiment the object-based query software package also comprised the feature of being called from a generic EJB-QL API that is imbedded in an entity bean application such that the object-based query does not need to be pre-defined and can be defined at runtime of the application. In a further embodiment, such a software module is provided to access a data source via at least one JDBC driver, a software module is provided to access a resource having one of more of a data source mapping schema, an object schema and/or an object to relational mapping information source, a software module to parse an query statement and generate an object encapsulating the query to access the data source and logic for obtaining and parsing the data returned from the data source, and a software module capable of dynamically creating an object to relational map specific to the object-based query (such as an EJB QL query) and to join objects to create new hybrid objects ready to be populated by data that is returned from the data source (as processed by the query object). In another prefeπed embodiment, the invention provides such an object-based querying software package having a wrapper or facade interface and API to be utilized to coordinate relational data systems and object data systems and to control multiple software modules that are utilized to load, insert, update and delete data from data systems and objects or object models in object programming systems during implementation of such functions in the overall system or network.

In a further prefeπed embodiment, the present invention provides a simple facade and object programming API designed to control the CocoBase O/R Software tools program components, including object based querying functionality, while particularly controlling the more complex components, to provide coordinated object graft and data persistence, including object model navigator components, transaction coordination components, CocoAdmin function components, and optionally caching components.

DESCRIPTION OF THE APPENDICES

To further exemplify the invention or provide technical resources, attached to this application are 4 non-limiting appendices that are described generally as follows:

Appendix 1 is a reproduction of the EJB QL BNF notation summary as published by SUN Microsystems, Inc., that may also be found at pages 257 and 258 of their Enteφrise JavaBeans 2.1 Specification, proposed final draft. This is an example of an object-based querying syntax. Appendix 1 is incoφorated herein in its entirety by reference.

Appendix 2 is a draft of a practical guide for conducting object-based EJB QL querying utilizing an implementation of a prefeπed embodiment of the present invention in the context of the commercially available software package CocoBase Enteφrise O/R version 4.5, which is to be released (published) shortly after the filing of this document. Appendix 2 is incoφorated herein in its entirety by reference.

Appendix 3 is a more detailed publicly available description of the syntax for EBJ QL as described in Appendix 1, but with more technical details and examples that utilize the EJB QL syntax of Appendix 1. Appendix 3 is incoφorated herein in its entirety by reference. Appendix 4 is a set of practical examples of using EJB QL (object- based querying) with CocoBase Enteφrise O/R Version 4.5 (or later). The demo utilizes a demo data source, a demo mapping repository and the Version 4.5 software (may be utilized with the technical resources of Appendices 1-3) to follow the general procedures as set forth in Appendix 2 to formulate object-based querying statements from data questions that may be run against the data source to retrieve the desired result sets. This exercise is designed to familiarize a user with EJB QL syntax and with the CocoBase Enteφrise O/R Version 4.5 dynamic object-based querying capabilities. Appendix 4 is is incoφorated herein in its entirety by reference. I

While a number of prefeπed embodiments of the present invention have been discussed by way of example, the present invention is not to be limited thereby. Rather, the present invention is to be construed as including any and all variations, modifications, permutations, adaptations and embodiments that would occur to one skilled in this art once having been taught the present invention. Appendix 1

SUN EJB QL SPECIFICATION BNF

(Backus Naur Form - formal notation for describing a language syntax)

EJB QL BNF notation summary:

• { ... } grouping

• [ ... ] optional constructs

• boldface keywords The following is the complete BNF notation for EJB QL:

EJB QL ::= select_clau.se from_clause [w ere_clause] [orderby_clause] from_clause ::=FROM identification_variable_declaration [, identification_variable_declaration]* identifιcation_variable_declaration ::= collection_member_declaration | range_variable_declaration collection_member_decIaration ::=IN (collection_valued_path_expression) [AS] identifier range_variable_declaration ::= abstract_schema_name [AS] identifier cmp_path_expression ::= {identification_variabIe | single_valued_cmr_path_expression}.cmp_fieId single_valued_cmr_path_expression ::= identification_variable.[single_valued_cmr_field.]* single_valued_cmr_field single_valued_path_expression ::= cmp_path_expression | single_valued_cmr_path_expression collection_valued_path_expression ::= identification_variable.[single_valued_cmr_field.]*collection_valued_cmr_field select_clause ::=SELECT [DISTINCT] {select_expression | OBJECT(identification_variable)} select_expression ::= single_valued_path_expression | aggregate_select_expression aggregate_select_expression ::= {AVG | MAX | MIN | SUM | COUNT} ([DISTINCT] cmp_path_expression) | COUNT ([DISTINCT] identification_variable | single_valued_cmr_path_expression) where_clause ::=WHERE conditional_expression conditional_expression ::= conditional_term | conditional_expression OR conditionaI_term conditiona l_term ::= conditional_factor | conditional_term AND conditional_factor conditional_factor ::= [NOT] conditional_primary conditional_primar ::= simple_cond_expression | (conditiona l_expression) simple_cond_expression ::= comparison_expression | between_expression | like_expression | in_expression | null_comparison_expression | empty_collection_comparison_expression | collection_member_expression between_expression ::= arithmetic_expression [NOT] BETWEEN arit metic_expression AND arithmetic_expression in_expression ::= cmp_path_expression [NOT] IN ({literal | input_parameter} [, { literal | input_parameter}]*) like_expression ::= cmp_path_expression [NOT] LIKE pattern_value [ESCAPE escape_character] null_comparison_expression ::= {single_valued_path_expression | input_parameter} IS [NOT] NULL empty_collection_comparison_expression ::= collection_valued_path_expression IS [NOT] EMPTY collection_member_expression ::= {single_valued_cmr_path_expression | identification_variable | input_parameter}

[NOT] MEMBER [OF] collection_valued_path_expression comparison_expression ::= string_value comparison_operator string_expression | boolean_value boolean_expression} | datetime_value comparison_operator datetime_expression | entity_bean_value

{= |<>} entity_bean_expression | arithmetic_value comparison_operator arithmetic__expression arithmetic_value ::= cmp_path_expression | functions_returning_numerics comparison_operator ::= = |> |>= |< |<= |<> arithmetic_expression ::= arithmetic_term | arithmetic_expression {+|-} arithmetic_term arithmetic_term ::= ahthmetic_factor | arithmetic_term {* |/ } arithmetic_factor arithmetic_factor ::= [{+ |- }] arithmetic_primary arithmetic_primary ::= cmp_path_expression | literal | (arithmetic_expression) | input_parameter | functions_returning_numerics string_value ::= cmp_path_expression | functions_returning_strings string_expression ::= string_primary | input_parameter string_primary ::= cmp_path_expression | literal | (string_expression) | functions_returning_strings datetime_value ::= cmp_path_expression datetime_expression ::= datetime_ alue | input_parameter boolean_value ::= cmp_path_expression boolean_expression ::= cmp_path_expression | literal | input_parameter entity_bean_value ::= single_vaIued_cmr_path_expression | identification_variable entity_bean_expression ::= entity_bean_value | input_parameter functions__returning_strings ::= CONCAT(string_expression, string_expression) | SUBSTRING(string_expression, arithmetic_expression, arithmetic_expression) functions_retuming_numerics::= LENGTH(string_expression) [ LOCATE(string_expression, string_expression[, arithmetic_expression]) | ABS(arithmetic_expression) SQRT(arithmetic_expression) | MOD(arithmetic_expression, arithmetic_expression) orderby_clause ::= ORDER BY orderbyjtem [, orderbyjtem]* orderbyjtem ::= cmp_patr_expression [ASC | DESC]

Appendix 2

EJB QL Querying with CocoBase CocoBase Enterprise extends the EJB QL specification to support generalized queries against any object oriented system. The querying framework for CMP entity beans described in Chapter 11 of the EJB 2.0 specification now forms a subset of the CocoBase EJB QL query capabilities. The CocoBase query system also supports the EJB QL syntax extensions described in the public draft of the EJB 2.1 specification. CocoBase EJB QL Mechanism

CocoBase supports dynamic queries based on the EJB QL syntax, as described in the EJB 2.0 specification. This allows standard EJB QL styled queries to be issued, on the fly, against any abstract database schema described by a CocoBase map. EJB QL support is provided through the thought . CocoBase . E JBQLQuery class. This layer of abstraction, above the CocoBase mapping layer, processes statements formulated in standard EJB QL syntax and compiles them into a form that can be further processed by the underlying CocoDriverlnterf ace class. Compiled EJB QL statements are passed into a CocoDriverlnterf ace instance in the form of dynamically generated custom query objects and CocoBase maps, which are then used to (transparently) generate and issue SQL based queries on the JDBC connection. Only the basic maps for corresponding database tables are required in the CocoBase repository. A basic map implements a direct relationship between database and object attributes.

The CocoBase EJBQLQuery API

The thought . CocoBase . EJBQLQuery class is the foundation for all EJB QL syntax driven queries. It requires a navigation model describing the object model relationships and an optional inheritance model if inheritance relationships exist in the object model. An instance of thought . CocoBase . CocoDriverlnterf ace and a CocoBase navigation model are passed into the constructor of EJBQLQuery as follows:

// Instantiate the CocoDriverlnterface class // Note : dynamic . querying must be set to true

CocoDriverlnterface myBase = CocoDriver . getCocoDriver ( "thought . CocoBase . CocoPo der" , "oracle . j dbc . driver . OracleDriver" , " j dbc : oracle : hin : Olocalhost : 1521 : orcl : cocoprop= dynamic . querying=true" ,

"EJBQL" , "EJBQL" ) ;

// Make a ODBC connection myBase . connect ( ) ; myBase . setT rowExceptions (true) ;

// Create an instance of thought . CocoBase . EJBQLQuery and pass in the // CocoDriverlnterface instance and a valid navigation model EJBQLQuery ejbQuery = new EJBQLQuery (myBase , "MyNavigationModel " ) ;

// Enter and compile the query ejbQuery . compile ( "SELECT DISTINCT p . name FROM Person p WHERE p . address . city = ' San Francisco' " ) ;

// Execute the query

Vector qResult = ejbQuery . execute { ) ;

Because p .name represents a j ava . lang . String, the Vector qResult contains a set of j ava . lang . String objects whose values are the distinct names of the selected persons. This example assumes that a relationship is defined for the Person and Address classes in the navigation model, and that the CocoBase map, Address, maps the VARCHAR field: CITY to the Java String field city. The field names used in the query reference the CocoBase map field names and have no direct relation to the Java attribute names. The map field names determine which Java field names are referenced by the query. In the Figure shown below, the field names that can be referenced in a query are the map field names ID, DATE and ORDER_ID (not the Java attribute names eyedee, dayt, toetul).

Inteqer eye ee=8443584008 String doyt="310902" JAVA float toetul=73.45 OBJECT

Figure - Java and Database Field

The following EJB QL query is valid:

SELECT OBJECT (o) FROM ORDER O WHERE o.id = 6443584006 while the following query will fail:

SELECT OBJECT(o) FROM ORDER o WHERE o. eyedee = 6443584006

OVERRIDING THE DEFAULT INSTANTIATION BEHAVIOR

The following EJBQLQuery query returns a Vector of com. foo . Per son objects by default: // Specify the query ejbQuery.compile ("SELECT OBJECT(p) FROM com. foo. Person p WHERE p. address. city = 'San Francisco' ") ;

// Exectute the query

Vector qResult = ejbQuery. execute (]

The implied result set class can be overridden by specifying an explicit class name through the method setExtent ( ) . The following example sets the result set class to be of type

Person:

// Explicitly set the result set class type e j bQuery . setExtent (Person . class )

// Specify the query ejbQuery . compile ( "SELECT OBJECT (p) FROM com. foo . Person p WHERE p . address . city = ' San Francisco ' " ) ; // Exectute the query

Vector qResult = ej bQuery . execute ( ) ,-

If instance creation fails because of a type mismatch or non-existent class, the Vector qResult will contain a set of thought . CocoBase . GenPOb j ect objects containing result set values in generic form.

The EJB QL Console

An EJB QL workspace console is provided with the CocoBase distribution. This console can be used to issue dynamic queries on an arbitrary JDBC connection using the EJBQLQuery and CocoPowder classes. The console start script, EJBQLConsole .bat, is located under the %COCO_HOME%\demos directory. The EJBQLQuery class installs a set of example records against which EJB QL commands can be issued. The database record entries and table relationships are shown in a figure below and the corresponding object model is shown in another figure just below the first figure. The EJBQLConsole is also provided as source and can be modified to include custom behavior.

Figure- Database Table and Record Configuration for EJB QL Example

Figure - Object Model for EJB QL Example

The following files (included under the %C0C0_H0ME%\deτnos directory) must also be available on the classpath at runtime:

^■ connection. properties Specifies CocoDriver and database connection parameters

♦ EσBQ Modei . properties Navigation model for example database

♦ EJBQLHσdei inherit .propert es Inheritance model for example database

♦ _E-r_BQL_R.epositorγ.xmi CocoBase database map definitions ,

Leveraging the Mapping Layer Abstraction

The EJBQLQuery architecture extends query reusability by allowing queries to be formulated against the abstract schemas described by CocoBase maps rather than requiring absolute database schema references in the query. For example, if a column name or foreign key relationship in the database changes, only the CocoBase map and navigation model need to be redefined. Once the database column or foreign key relationship is remapped, the same EJB QL query statement can be re-issued.

Two figures below captioned as "Initial Database Foreign Key Relationship" and "Re-mapped Column and Navigation Model", respectively, describe the process or re-mapping the primary and foreign key relationships in the Customer and Order tables. The intervening figures show the logic behind the mapping process, while the later figure that is captioned "Result of Query using CUSTOMER.SSN" shows the change in results from the re-mapping process.

Figure - Initial Database Foreign Key Relationship

The ORDER.CUSTOMER D column initially references the CUSTOMER.ID field. The CocoBase Map hides the SSN field of the CUSTOMER table, therefore, in this example, this field is not propagated to the resulting object instances. The foreign key relationship is mapped to object links through the navigation model.

Figure - Initial Mapping Configuration

If the following EJB QL query is issued:

SELECT OBJECT (c) FROM Customer c, IN (c . orders) o WHERE o . total > 65 the result set might include the following Order objects:

Figure - Result Set from an EJB QL Based Query

The Customer object customer and its dependent objects, order_0 and order_l, are the object representation of corresponding database records.

If the database structure is changed so that the foreign key field, ORDER. CUTOMERJD, references CUSTOMER.SSN instead of CUSTOMER.ID, only the CocoBase map and navigation model need to be updated. Once this occurs, the same EJB QL syntax: SELECT OBJECT (c) FROM Customer c, IN (c . orders) o WHERE o . total > 65 can be used to perform an identical query.

Figure - Remapped Column and Navigation Model

NOTE: The figure shows the CUSTOMER.SSN column being mapped to the customer . id field in the object space. For this to occur successfully, the datatypes must match, or be matched through the CocoBase map or in the object space.

Figure - Result of Query using CUSTOMER.SSN as the Primary Key

Query Range Restriction The queries listed in this section can be performed against the example EJB QL database using the

EJBQLConsole utility introduced in the above Section entitled "The EJB QL Console"

Underlying CocoBase maps can be used to restrict the range of a EJB QL based query. Map controlled range restrictions can be used to expose only data that is immediately required, or to restrict sensitive data to authorized end users only. By adding additional WHERE clause restrictions in the map, the query results can be confined to a column range subset. For example, if a default Employee map is used to select against EMPLOYEE records in the database,

the following EJB QL query:

SELECT OBJECT (c) FROM Customer c WHERE cage > 30 selects all employees whose age is greater than 30. The result of the query against the example database is:

Person [0] id=12 name=Mary age=43 Person [1] id=13 name=Joe age=31 Person [2] id=14 name=Rose age=33

If a restrictive WHERE clause (CUSTOMER.AGE > 35) replaces the default equality WHERE clause in the Customer map,

the same EJB QL query produces the following results: Perso [0] : id=12 name=Mary age=43

The original records, id=13 & id = 14, are disqualified from the query by the additional WHERE clause restriction in the map. If design constraints require EJB QL literals to be embedded in the application source code, the ability to redefine queries by altering the CocoBase map provides convenient alternative to rewriting application classes.

EJB PL Syntax

CocoBase supports standard and proposed standard EJB QL syntax as defined in the final release of the EJB 2.0 specification, and public draft of the EJB 2.1 specification with the following database dependent exceptions.

EJB QL Syntax Limitations

1. IS NULL object comparisons and IS EMPTY and MEMBER OF collection expressions

Proper execution of these expressions is dependent on the level of support provided by the underlying JDBC driver and/or database, is NULL object comparisons and is EMPTY and MEMBER OF collection expressions: e.g. SELECT OBJECT (C) PROM Customer AS c WHERE c. Order IS NULL require the underlying JDBC connection to support correlated EXISTS (subquery) SQL clauses, as in the SQL expression below:

SELECT * FROM TI WHERE EXISTS (SELECT * FROM T2 WHERE FI = TI. Field)

If the target database does not support this type of SQL sentence, then any EJB QL queries containing IS NULL object comparisons and IS EMPTY and MEMBER OF collection expressions will fail to execute. Regular field IS NULL comparisons and other types of comparison expressions: e.g. SELECT OBJECT (o) FROM Order AS o WHERE o.orderDate IS NULL are fully supported.

2. ESCAPE and FUNCTIONS

Some of the EJB QL query sentences are sent directly to the underlying JDBC connection. This is the case for "LIKE ' xxxx ' ESCAPE ' y'" sentences, and functions such as LENGTH ( ) , SUBSTRING ( ) , LOCATE ( ) , SQRT ( ) . Therefore, the target SQL used by the underlying JDBC connection must also support these functions to properly execute the queries.

3. ORDER BY Support for the ORDER BY clause, while not official (as it appears only in the public draft of the

EJB 2.1 specification as of this writing), is provided by CocoBase Enterprise EJB QL facilities. Query results can be ordered by a specified field by appending the ORDER BY syntax to the end of the query sentence. This topic will be revisited as soon as the final release of the specification is made available.

APPENDIX 3 <EJB-OL> The Ultimate Reference for the EJB Query Language.

This page contains a description of the EJB-QL language, in two forms:

• General syntax

• BNF notation

THE EJB-QL LANGUAGE EJB-QL is used in container-managed persistence EJBs to describe finders and select methods. The aim of this language is strictly to represent a query of in-memory objects, not a query in a database.

But the syntax for EJB-QL resembles SQL. There are 3 main clauses: SELECT, FROM, WHERE. These clauses may contain expressions that can be used in countless combinations. It is not the objective of this document to enumerate all possible uses of EJB-QL. It is meant to give the general syntax and give a few examples illustrating this syntax. Go to top.

<EJB-OL> TAG SYNTAX

The EJB-QL language cannot be used on its own. It has to be specified inside the file ejb-jar.xml, in the <eib-ql> tag. This tag is is inside the <querv> tag, which defines a query (finder or select method) inside an EJB. The EJB container can transform each query into the implementation of the finder or select method. Here's an example of an <ejb-ql> tag.

<ejb- j ar>

<enterprise-beans> <entity> <ejb-name>hotelEJB</ejb-rιame>

<abstract-schema-name>hotelSchemaName</abstract-schema-name> <cmp-field>... <query>

<query-method>

<method-name>findByCity</method-name> <method-params>

<method-param>java. lang.String</method-param> </method-params>

</query-method> <ejb-ql>

<! [CDATA[SELECT OBJECT (h) FROM hotelSchemaName AS h WHERE .city = ?1] ] > </ejb-ql>

</query> </entity>

< /enterprise -beans > </ejb-jar>

Here the EJB-QL query is in blue, and the surrounding XML is in red. Note that the _< ! [CDATA [ . . . ] ] > is a special XML notation to escape any reserved characters, e.g. > and '. Always use it. Go to top.

General Syntax

SELECT OBJECT (variable)

FROM abstractSchemaName [AS] variable

[WHERE value comparison value] This is the most general syntax, and as we'll see later on, there are some more syntax constituents that may be used, hi this syntax, there are one or more variables declared in the FROM clause, and these are referenced in the SELECT and WHERE clauses. Also note the abstractSchemaName, which is declared in ejb-iar.xml, under the entity tag.

The value can be a parameter, the result of a numeric or string function, a variable (e.g. hotel), a member inside this variable separated using the dot '.' operator (e.g. hotel.name), or a constant (e.g. 'Pleasant Inn', 23, TRUE). A query also has a return type. Values and return types can be of 6 different data types.

SELECT OBJECT (h)

FROM hotelSchemaName AS h Returns all hotels.

SELECT OBJECT (h)

FROM hotelSchemaName AS h Returns hotels in San

WHERE h.city = 'San Diego¹ Diego. Inside the SELECT clause, the keyword DISTINCT specifies that each occurrence being returned must appear only once (when the method signature returns a Collection). In theory, finder methods using a simple syntax will return entity EJBs wich are already distinct (their primary keys are different), but for select methods, there may be duplicates.

Select Method Syntax

When dealing with a select method, the return type (the SELECT clause) does not have to be OBJECT(x). It can be any value of any type. The syntax for non-OBJECT queries is:

SELECT [DISTINCT] value

FROM abstractSchemaName [AS] variable

[WHERE ...]

Reserved Words

FYI, the EJB-QL reserved words are:

Return Type

Any query must return one of:

• An object of the same type as the object in the SELECT clause (the first occurrence is returned)

• A Collection containing this type (duplicates are allowed except when using SELECT DISTINCT)

• A Set containing this type (duplicates are not allowed, may have to use SELECT DISTINCT) This choice is up to you, and is specified in the finder or select method signature.

FROM hotelSchemaName AS h abstract public Set findByCapacity(integer numberOfPeople); WHERE h.capacity >= ?1

Variables

A variable is declared in the FROM clause, and represents an EJB. The variable can then be used in the SELECT and WHERE clauses. The type of a variable is specified as the abstract schema name of the EJB, as defined in ejb-jar.xml.

We'll talk about range variables a bit later.

Member Navigation

You may specify a member inside an object using the dot . operator. Following the dot . operator, you may specify any cmp-field names, or single-value cmr-field names, as defined in ejb-jar.xml. This lets you navigate through single-value objects, including data members and chains of relationships.

(here the cmp-field name is 'city') (here the cmr-field 'owner' contains a single

. h.capacity > 100

SELECT OBJECT(h) Owner object) . h.owner = ?1 FROM hotelSchemaName (here the owner contains a cmp-field 'name') . h.owner.name = 'Harry' h (here the owner contains a cmr-field 'hotel'; this . h.owner.hotel = h WHERE ... condition is always true!) . h.rooms = ?1 (ILLEGAL SYNTAX- can 't use a Collection cmr- field - use the range variable syntax instead)

Range Variable Syntax

When a member of a variable is a Collection, you cannot navigate to it using the dot . operator. You must specify a new variable that will iterate through each item in the Collection. Here's the syntax:

SELECT [DISTINCT] OBJECT(vαri We)

FROM abstractSchemaName [AS] variable, IN (yariablexollectionMember) [AS] variable2

[WHERE ...]

(Note that this IN operator is not the same as in the WHERE clause.) You may use the_. second variable in the WHERE clause just like the regular object variable.

SELECT DISTINCT OBJECT(h) FROM hotelSchemaName AS h, IN (h.rooms) AS r WHERE r.smoking = TRUE (returns all hotels with smoking rooms)

WebLogic Server 6.1 NOTE: the syntax for range variables in WLS is different. Here's an example:

SELECT DISTINCT OBJECT(h)

FROM hotelSchemaName AS h, r IN h.rooms

WHERE r.smoking = TRUE

Parameters

When a finder or select method has one or more parameters, you may refer to the value of the parameter by using the syntax "?99" where 99 is the position of the wanted parameter. For example, here's a finder, method signature: abstract public Hotel findByCityAndState(String city, String state);

You may refer to the parameter city by using ? 1 , and to state by using ?2. For example: SELECT OBJECT(h)

FROM hotelSchemaName AS h

WHERE h.city = ?1 AND h.state = ?2

Conditional Operators Conditional operators are numerous, can be prioritized with parenthesis ( ), and combined using AND and OR, like this:

... WHERE (h.owner.name = 'Harry' AND Incapacity > 100) OR (h.owner.name = 'Joe' AND h.capacity > 200)

Here's a list of condition operators: NOT

Inverts the current condition.

... WHERE NOT (h.capacity > 100) is equivalent to: ... WHERE (h.capacity <= 100)

[NOT] LIKE Compares a string against a pattern. The pattern may contain wildcards: % to represent any string, or _ to represent a single character. The wildcards can be escaped by appending ESCAPE followed by the escape character.

... WHERE h.state LIKE 'K.%* (would return the hotels in Kentucky or Kansas) ... WHERE h.owner.name LIKE ' J_'

(two underline characters '_') (would return hotels owned by Joe)

... WHERE h.id LIKE 'A\_l%' ESCAPE 'V (would return the hotels whose code begins with "A_l". Here the underscore character "_" is escaped by the backslash "\".)

[NOT] BETWEEN x AND v

Checks for ranges of numeric values.

... WHERE Incapacity BETWEEN 100 AND 200

is ΓNOTI NULL

Checks for objects with the value 'null'.

... WHERE h.owner IS NULL (returns the hotels with no associated owners) [NOT] IN HstOfStrines

In the WHERE clause, checks for the presence of a string inside a collection of strings. This is not the same as the IN operator in the FROM clause.

... WHERE h.state IN ('Ohio', 'Texas', ?1) ΓNOTI MEMBER [QFI

Checks for the presence of a value inside a collection of values.

... WHERE ?1 MEMBER OF h.rooms (returns the hotel containing a certain room) ≡^>

Checks for equality. Similar to isldentical() for EJBs, or equals() for other types.

... WHERE h.owner.name = 'Harry' ... WHERE h.owner.name <> 'Joe'

Checks for numeric comparison. This works on numbers and dates. ... WHERE h.capacity >= 100 is ΓNOTI EMPTY

Checks if a Collection value has elements.

... WHERE h.rooms IS EMPTY (checks for hotels with no rooms)

Data Types

There are 6 different types in EJB-QL, for return types, parameters, constants and variables:

Objects

This is a general Java object. Such an object can be directly mapped to an abstract schema name, or a member of another obj ect.

SELECT OBJECT(h) FROM hotelSchemaName h WHERE h.owner = ?l (here the objects are h, h.owner and ?1) Collections

This is represented by a range variable, a member of an object, or when using a select method, the return type may be a Collection.

SELECT OBJECT(h) FROM hotelSchemaName h WHERE ? 1 MEMBER OF h.rooms

(here the Collection is h.rooms)

SELECT OBJECT(h)

FROM hotelSchemaName h, F h.rooms AS r WHERE r = ?l (equivalent to last query, the Collections are r and h.rooms)

SELECT h

FROM hotelSchemaName (returns a Collection of all hotels) Strings

This could be a String parameter, member variable, or a constant delimited by single quotes '. To represent a single quote inside a String, use two single quotes ".

... WHERE h.owner.name = 'Harry' (the Strings are h.owner.name (variable) and 'Harry' (constant))

... WHERE h.owner.name = 'O"Sullivan' (here the constant string contains a single quote)

Numbers

This could be a numeric parameter or member variable, or a constant. The number can be negative (minus sign -) or decimal (floating point .), or even using the scientific notation (E). Casting from integer to float is automatic.

13 -16 17.2 1.98E4

45 + 12.1 (45 is cast to a float and the result is a float)

Dates

This is a special case of a number. When it is represented by a constant, it is the number of seconds since 01/01/1970 at 00:00:00 GMT. When is a parameter or member variable, it must be a Date object. ... WHERE reservation.arrivalDate = ? 1

(here the dates are the member variable arrivalDate and the parameter)

... WHERE reservation.arrivalDate < ???????

TODO: calculate this number (here the arrival date must be before Christmas 2001) Booleans

This could be a boolean parameter or member variable, or the constants TRUE or FALSE.

... WHERE room. smoking = TRUE String Functions LENGTHS

Returns the size of a String, an a number.

... WHERE LENGTH(h.owner.name) > 3 (returns hotels owned by people who's name is more than 3 characters long - Joe's hotel would not be returned) LOCATED, substr [, startPosition])

Returns the position of a substring in a string, as a number. When the starting position is specified, the search for the substring starts at that position. The index starts at 0????? If the search is unsuccessful, ?????? TODO: find this out

... WHERE LOCATE(h.owner.name, 'rr') = 2 (Harry's hotel would be returned)

... WHERE LOCATE(h.owner.name, 'rr', 3) >= 0 (starting the search at position 3, Harry's hotel would be NOT returned)

CONCATfrΛ s2)

Concatenates two strings and returns the new string.

SELECT CONCAT (CONCAT( h.owner.name, ', the owner of '), h.name)

FROM hotelSchemaName AS h (select method returning the list of all owners of all hotels, as a Collection of Strings ready to display)

SUBSTRINGS, startPositiorim length)

Extracts a substring from a string, starting from the specified position, and for the specified number of characters. The first position is 0. ???

TODO: find this out

... WHERE SUBSTRTNG(h.owner.name, 0, 2) = 'Jo' (would return Joseph, Joe, etc. Same as h.owner.name LIKE 'Jo%')

Numeric Functions and Operators ABS(n)

Returns the absolute (positive) value of a number.

SELECT ABS(a.balance) FROM accountSchemaName AS a WHERE a.balance < 0 (select method returning all negative balances, as a list of positive values)

SQKT( )

Returns the square root of a number.

SELECT OBJECT(a) FROM accountSchemaName AS a WHERE SQRT(a.balance) = 2

(returns the accounts with a balance of 4)

+ - * /

Addition, subtraction, multiplication and division operators. SELECT ABS(a.balance) FROM accountSchemaName AS a WHERE a.balance + 1 = 5

(returns the accounts with a balance of 4)

SELECT OBJECT(h) FROM hotelSchemaName AS h WHERE h.capacity / 2 >= ?1 SELECT OBJECT(h) FROM hotelSchemaName AS h WHERE h.capacity >= ?1 * 2 (both queries return the hotels that can hold double the specified capacity)

SELECT h.capacity - 10 FROM hotelSchemaName AS h (returns the list of hotel capacities, minus 10 spare rooms)

Go to top.

BNF NOTATION The BNF notation describes the syntax for the EJB-QL language. (BNF stands for Backus Naur Form, which invented a formal notation for describing a language syntax.)

This language begins with the general EJB-QL expression, and then each subexpression is described. In this notation:

The symbol '::=' is the equivalence operator.

The square brackets '[' ']' represent an optional clause.

The star '*' means 'zero or more occurences'.

The vertical bar '|' separates choices. The choices may be surrounded by curly brackets '{' '}'.

Constants and reserved words are in bold.

Italics represent an identifier declared in eib-iar.xml.

Some terminal symbols are described in plain English at the end.

Notes about the terminal symbols:

An escape-character is a single character that can escape the '%' and '_' in a pattern- value. (SEE LIKE)

An identifier is a name of a variable, starting with a letter, with no space or special character.

An input-parameter is a question mark, followed by a positive number, a parameter position for the query. Example: ?1 (first parameter), ?2 (second parameter), ?3 (third parameter)... (SEE Parameters) A literal is a positive number. A pattern-value is a string that may contain a percent sign '%' (any characters) or underscore '_' (any single character). (SEE LIKE)

A string-literal is a string surrounded by single quote characters ' (example: 'Larry talks.'). To represent a quote inside a string-literal, use the single quote twice (example: 'Larry said "yes".'). (SEE Strings)

END of Document

Appendix 4 EJB QL Query Syntax Structure Examples

Using CocoBase Enterprise O/R Software Package and EJB QL implementation:

Notes:

Refer to database_contents.txt of CocoBase Enteφrise O/R Software Package Release 4.5 or later (to be released after 11/1/2002) to see what is in the example query database.

Refer to ΞJBQLModel . gif to visualize the abstract OO mapped schema for these examples .

Run the EJBQLConsole script to start the EJB-QL console application if you'd like to play with some of these queries.

Examples:

1) get all customers that live in a Main St.

SELECT OBJECT(c) FROM Customer c WHERE c. address . street = 'Main St. ' 2) get the name of all customers that live in a zip code ending with '50'

SELECT c . name FROM Customer c WHERE c . address . zip LIKE ' %50 '

3) get all persons (Person is a subclass of Customer) whose age is below 35 ordered by age

SELECT OBJECT (p) FROM Person p WHERE p. age < 35 ORDER BY p. age

4) get the address of all persons

SELECT p. address FROM Person p 5) get all customers with no orders

SELECT OBJECT (c) FROM Customer c WHERE c . orders IS EMPTY

6) get all customers whose orders contain items not yet shipped

SELECT DISTINCT o . customer FROM Order o , IN (o . lineltems) AS 1 WHERE 1 . shipped = FALSE

7) get all customers whose orders do not contain a line item with a quantity above 4

SELECT DISTINCT OBJΞCT(c) FROM Customer c, IN (c. orders) o, IN (o. lineltems) 1 WHERE NOT 1. quantity > 4

8) get all orders with at least two line items

SELECT DISTINCT 11. order FROM Lineltem 11, Lineltem 12 WHERE 11 <> 12 AND 11. order = 12. order

9) get the name of all products ordered by customer 'Carl'

SELECT DISTINCT p . name FROM Product p , IN (p . lineltems ) 1 WHERE 1 . order . customer . name = ' Carl '

10) get all customers whose orders contain quantities greater than the quantity of books ordered by customer 'Carl' SELECT DISTINCT 11.order. customer FROM Lineltem 11, Lineltem 12

WHERE 11. quantity > 12. quantity AND 12.orde . customer .name = 'Carl' and 12.product .name = 'BOOK'

11) get all suppliers of batteries

SELECT DISTINCT OBJECT(s) FROM Product p, in (p . suppliers) s WHERE p.name = 'BATTERIES'

12) get the name of all products not supplied by 'Material h e.' and supplied by some other supplier

SELECT DISTINCT p.name FROM Supplier m, Supplier s, in (s.products) p WHERE m.name = 'Material Inc.¹ AND p NOT MEMBER OF m.products 12 Custom Querying with CocoBase

The CocoBase custom querying classes extend CocoBase mapping functionality by providing the ability to dynamically generate custom CocoBase maps and queries. Maps are generated transparently and are based on the user defined parameters of a given query. CocoBase Enterprise provides custom querying capabilities through the following classes.

♦ thought . CocoBase . CBQuery

This class forms the basis for CocoBase query customization and extension capability. This mid-level API provides fine granularity for building custom queries that can be compiled directly into an application. Additionally, the CBQuery class interfaces directly with thought . CocoBase . CocoPo der to provide dynamic mapping behavior. The CBQueryBuilder and EJBQLQuery APIs extend the functionality of the CBQuery class and provide a simplified, high level querying interface. The CBQuery API is presented in Section 12.1.

♦ thought . CocoBase . CBQueryBuilder

This class presents a simplified API for query customization and extension based on the CBQuery class. It exposes the most useful functionality of the CBQuery class through an intuitive, SQL92 based query syntax. The CBQueryBuilder API is presented in Section 12.2.

♦ thought. CocoBase. EJBQLQuery

The CocoBase EJBQLQuery class, also based on the CBQuery class, extends the EJB QL specification to support EJB QL queries against any Java based object model. The CMP entity bean querying framework described in Chapter 11 of the EJB 2.0 specification forms a subset of the CocoBase EJB QL query capabilities. The CocoBase EJB QL query system also supports the EJB QL syntax extensions described in the public draft of the EJB 2.1 specification. The EJBQLQuery API is presented in Section 12.4.

12.1 CBQuery Class

The CBQuery class provides the foundation for custom querying with CocoBase. The primary function of the CBQuery class is to allow the formulation of custom queries, and to dynamically translate those queries into a maps and query objects that can be further processed by the underlying thought . CocoBase . CocoDriverlnterf ce class. The CBQuery class defines the following static int variables, representing the logical, comparison and join operations that are available for the class:

♦ static final int INNER_JOIN ;

♦ static final int LEFT_JOIN;

♦ static final int AND 4- static final int OR

♦ static final int EQUAL TO

♦ static final int GREATER THAN

Custom Querying with CocoBase - 42 - 219 ♦ static final int GREATER_THAN_EQUAL_TO

♦ static final int LESS_THAN

♦ static final int LESS_THAN_EQUAL_TO

♦ static final int LIKE

♦ static final int NOT_EQTJAL_TO

♦ static final int BETWEEN

♦ static final int IN

The CBQuery class defines the following methods:

♦ public CBQuery (CocoPowder cdi)

This constructor takes a thought . CocoBase . CocoPowder instance as an argument and attempts to determine the target database type. If the target database can be identified, the SQL syntax (notably, the JOIN syntax) is set for that database. SQL92 is the default syntax.

♦ public void addMa (String mapName)

This method adds the Cocobase map, mapName, to the CBQuery map pool, by invoking addMa (mapName , mapName) .

♦ public void addMap (String mapName , String mapAlias)

This method adds the map, mapName, to the CBQuery map pool and references it using mapAlias.

addMapfCustomer") ^■ > «-addMap("Address", "A")

Figure 12.1 Adding Unique H/iap instances using addMap()

Two or more identical maps can be added to the pool using different aliases. Calling addMap ( ) will cause the CBQuery object to be recompiled prior to executing the query.

Map Pool addMaρ("Custo er") - -addMapC'Address", "A")

add ap("Address")

Figure 12.2 Adding Redundant Map Instances using AddWlap()

- 43 - CocoBase Enterprise 01 R v4.0 Programmer's Guide public setExtent (String mapAlias )

This method sets a query extent map for a CBQuery object. The query extent map, mapAlias, restricts the range of return values to those included in the specified map. If several maps have been added to the map pool, setExtent ( ) allows a query to be limited to the fields (or a subset of fields) of a single map.

Figure 12.3 Setting the Extent Map

When the mapAlias is set to null (default), all fields of all maps included in the map pool are eligible to be included in the result. In this case, result fields are named MapAlias .MapFieldName for each map alias participating in the query. setExtentclass ( ) can be used in conjunction with setExtent ( ) to specify which objects are instantiated when returning the result. The class name that is passed in as the argument is the class that is returned in the result Vector.

Calling setExtent ( ) will cause the CBQuery object to be recompiled prior to executing the query.

public void setProjection (String mapAlias , String !] mapFields)

This method defines a projection on the fields of mapAlias. The projected fields are specified by the string array mapFields. Only the projected fields are returned in the query result.

Figure 12.4 Setting the Projection for the Customer Map

Custom Querying with CocoBase ■ 44 - 221 When an extent is defined, only the projection relating to the current extent is considered. Projections over fields of other map aliases are disregarded. It is possible to set subsequent projections on a different map alias, thereby altering the set of projected fields that result from the query.

Map Pool

- addMapC'Address", "A")

- setExtent("A") setProjeotionC'A", new Stringπf'ID",...) -W ID I ZIP I

Figure 12.5 Setting the Extent Map and Extent Map Projection

♦ public void setFieldAlias (String mapAlias , String mapFieldName , String fieldAlias)

This method defines an alias name to be used in place of the original map field name for the query result. This method allows an alias to be specified for a map field name when identical field names exist in two or more maps (e.g Customer.ID & Address.ID). This method is used internally, and is not recommended for general use.

♦ public void setExtentClass (Class extentClass)

This method sets the CBQuery extent class. If extentClass is a valid Java class, CBQuery returns instances of extentClass with fields populated according to the CocoBase extent map that was set using setExtent ( ) . If extentClass is null execution results in a Vector of thought . CocoBase . GenPOb j ect instances.

NOTE: When an extent class is specified, a corresponding extent map must also be specified.

♦ public void setExtentlsCountOnly (boolean count)

This method sets CBQuery to return the number of selected entries and not the actual entries. The underlying database query is a "select count(*) from ... " type of query. Calling setExtentClass ( ) will cause the CBQuery object to be recompiled prior to executing the query.

^•Φ public void setExtentlsDistinct (boolean distinct)

This method sets CBQuery to return distinct entries. The underlying database query is a "select distinct ... " type of query. Calling setExtentlsDistinct ( ) will cause the CBQuery object to be recompiled prior to executing the query.

^• public void setProperties (Properties props)

This method sets the property values for the CBQuery object. Currently supported properties are:

-45 - CocoBase Enterprise OIR v4.0 Programmer's Guide ■ useOracleSyntax = "true" | " false" (default) - a String indicating that the underlying maps use Oracle syntax specifics and that the resulting SQL outer join expressions should use Oracle outer join syntax. If set to " false", generated SQL uses standard SQL 92 "LEFT OUTER JOIN"- like clauses.

■ useNestedSelects = "true" (default) | " false" - a String indicating that the resulting SQL should be based on sub-selects, where the SQL of each participant map instance will be nested in the FROM clause of the outermost select. This is the preferred select notation since it assures that OUTER JOINs will always work, even if the original maps contain INNER JOIN conditions that constrain the result.

If set to " false", the generated SQL will combine all tables, fields and WHERE conditions of the underlying maps into one, flat SQL statement, and doesn't guarantee that OUTER JOINs will work when INNER JOIN conditions are used in the maps.

The argument props contains the configuration properties for this CBQuery public void addJoin (int j oinType , String leftMapAlias , String U leftFields , String rightMapAlias , String [] rightFields)

This method adds a JOIN condition to the CBQuery. Calling addJoin ( ) forces the CBQuery object to be recompiled when a subsequent compile ( ) is called.

■ The argument j oinType is the join type.

B The argument leftMapAlias is the alias that corresponds to the map on the left side of the join.

■ The argument leftFields is the String array of left map field names that compose the join condition.

■ The argument rightMapAlias is the alias that corresponds to the map on the right side of the join.

■ The argument rightFields is the string array of right map field names that compose the join condition public void addCondition ( int conjunctive, String mapAlias , String mapFieldName , int operand, Obj ect value)

This method adds a predicate condition to the CBQuery object.

■ The argument conjunctive is the type of conjunctive that links this condition the its preceding conditions (e.g. QBQuery . AND or CBQuery. OR).

H The argument mapAlias is the alias that corresponds to the map used in the condition.

■ The argument mapFieldName is the name of the field used in the condition. The argument operand is the type of operand used for comparison.

M The argument value is the value for comparison; it should be a Vector containing 2 values if operand is BETWEEN public String translateField (String mapAlias , String mapFieldName)

This method translates a field of a given map alias into its internal SQL naming convention. This method is used internally, and is not recommended for general use.

Custom Querying with CocoBase - 46 - 223 ♦ public void setRawSQLWhere (String sql here)

This method sets a raw SQL WHERE condition for the CBQuery object. This method should be used with translateField (mapAlias , field) to ensure that field names are properly translated to their internal naming convention. This method is used internally, and is not recommended for general use.

♦ public void setBindVector (Vector bindObjs)

Sets the Vector of objects to be bound for each '?' (parameter) in the raw SQL string. This allows parametric values to be defined at runtime. This method should be used with setRawSQLWhere (String sqlwhere) , and only if there is a raw SQL String containing bind '?' characters. This method is used internally, and is not recommended for general use.

♦ public void compile ( )

This method compiles the CBQuery, and is automatically called when execute ( ) is called. This method should not be explicitly called unless further processing of the internally generated query map is required.

♦ public Vector execute ()

This method executes the CBquery. The query is recompiled only if required (methods that force re-compilation are indicated above). This method returns a Vector of objects of the type specified using the setExtentClass ( ) method. If no extent class was specified, the result is a Vector of thought . CocoBase . GenPOb j ect instances whose properties are named MapAlias .MapFieldName for each map alias participating in the query.

♦ public CBObject getCompiledMap ()

Returns the query map that was generated the last time the CBQuery was compiled, or null if a compiled does not exist. This method is useful when the query map is needed for further processing or debugging purposes.

12.1.1 CBQuery Usage

This section provides code samples that demonstrates the usage of the CBQuery class. The CBQuery class is designed principally to provide fine grained query control though a relatively complex API. The CBQueryBuilder and EJBQLQuery APIs, which build upon the CBQuery class, are designed to simplify the query formulation syntax (e.g. EJBQLQuery requires standard EJB QL syntax and CBQueryBuilder syntax resembles SQL) and to provide an intuitive, "ready to assemble" set of querying tools. These simplified APIs are presented in the following sections.

224 - 47 - CocoBase Enterprise OIR v4.0 Programmer's Guide The database configuration shown in Figure 12.6 is used in CBQueryDemo examples presented below.

Figure 12.6 Database Tables used for CBQueryDemo Example

The following code fragments from the CBQueryDemo example are included in the CocoBase Enterprise distribution under %COCO_HOME%\demos\pguide\Chapterl2\CBQuery.

// Instantiate CocoPowder

CocoDriverlnterface myBase = CocoDriver.getCocoDrlver ( "CocoPowder" , driverName, cocoUrl, user, passwd) ; myBase. setThrowExceptions (true) ; // Open the database connection myBase . connect ( ) ;

// Instantiate CBQuery

CBQuery query = new CBQuery ( (CocoPowder) myBase)

Vector result;

Custom Querying with CocoBase -48 - 225 When an extent class is specified, a corresponding extent map must also be specified.

// First query gets the name of all customer in the database

String [] pfieldsl = {"NAME"};

// Add a map to the query object's (empty) map pool query. ddMap ( "Customer" ) ;

// Sets the return class type query. setExtentClass (Customer .class) ;

// Sets the Extent Map query. setExtent ("Customer") ;

// Sets "NAME" to be the only populated field in the returned Customer

// objects query . setProj ection ( " Customer" , pfieldsl) ;

// Compile and executes the query result = query. execute ( ) ;

In the following query, CUSTOMER.NAME and ADDRESS.ZIP column values are retrieved from the database. An INNER JOIN is performed to retrieve related ADDRESS records. CUSTOMER records that do not have a corresponding ADDRESS entry are disregarded.

Because an extent class (and corresponding map) are not specified in the following query, the results can span all fields of the Customer and Address maps. The query results are returned in the form of thought . CocoBase . GenPOb j ects.

// Second query gets the name and zip code of all customers - query = new CBQuery ( (CocoPowder) myBase) ;

// Add the Customer and Address maps to the map pool query. addMap ( "Customer" ) ; query . addMa ( "Address " ) ;

// Add an INNER JOIN (Address on Customer) query . addJoin (CBQuery . INNER_JOIN, "Customer" , new

String [] { "ADDRESS_ID" } , "Address " , new String [] { " ID" } ) ; // Set proj ections for the Customer and Address Maps query. setProj ection ( " Customer" , new String [] { "NAME" } ) ; query. setProjection ( "Address " , new String [] { "ZIP" } ) ; // Compile and executes the query result = query. execute { ) ;

The following query uses an OUTER JOIN to perform a similar query as the previous example. Because of the OUTER JOIN, all CUSTOMER records are returned, regardless of whether or not a corresponding ADDRESS record exists.

// Third query gets the name of all customers with their respective zip

// codes if applicable query = new CBQuery ( (CocoPowder) myBase) ; query . addMa ( "Customer" ) ; query . addMap ( "Address " ) ;

// Specify an OUTER JOIN instead of an INNER JOIN

- 49 - CocoBase Enterprise 01 R v4.0 Programmer's Guide query . addJoin ( CBQuery. OUTER_JOIN, "Customer" , new

String !] { "ADDRESS_ID" } , "Address " , new String [] { " ID" } ) ; query. setProj ection ( "Customer" , new String [] { "NAME" } ) ; query. setProj ection ( "Address " , new String [] { " ZIP" } ) ; // Compile and execute the query result = query. execute ( ) ;

The following query specifies a series of three INNER JOINS and a single WHERE condition to perform a query that returns the product name and price for a specific customer.

// Fourth query retrieves the name and price of each product ordered by

// Carl query = new CBQuery ( (CocoPowder) myBase) ;

// Add the Product, Lineltem, Order and Customer maps to the CBQuwery

// map pool query. addMap ("Product") ; query. addMap ( "Lineltem" ) ; query. ddMap ( "Order" ) ; query.addMap ( "Customer" ) ;

// INNER JOIN Lineltem on Product query. addJoin (CBQuery. INNER_JOIN, "Lineltem", new

String [] { "PRODUCT_ID" } , "Product", new String []{ "ID" }) ; // INNER JOIN Lineltem on Order query. addJoin (CBQuery. INNER_JOIN, "Lineltem", new

String [] { "ORDER_ID" } , "Order", new String []{ "ID" }) ; // INNER JOIN Order on Customer query.addJoin (CBQuery. INNER_JOIN, "Order", new

String [] { "CUSTOMER_ID" } , "Customer" , new String [] { "ID" } ) ; // Add a WHERE CONDITION: CUSTOMER.NAME = "Carl" query. addCondition(CBQuery.AND, "Customer", "NAME", CBQuery. EQUAL_TO,

"Carl" ) ; query. setExtentClass (Product .class) ; query. setExtent ( "Product" ) ; query. setProjection ("Product" , new String[] { "NAME" , "PRICE" }) ; // Compile and execute the query result = query. execute () ;

In the following query, two operators are used to define a query range. The

CBQuery.BETWEEN operator allows a boundary range to be specified on the product price

The CBQuery. LIKE operator limits the eligible product names to those beginning with "B".

// Fifth query retrives product fields for products names beginning

// with "B" or that cost between $10.00 and $30.00

Vector values = new Vector () ; values . addElement (new Float (10) ) ; values . addElement (new Floa (30) ) ; query = new CBQuery ( (CocoPowder) myBase) ;

// Add the Product map to the pool and alias as P

Custom Querying with CocoBase - 50 - 227 query.addMap { "Product" , "P" ) ;

// Add a WHERE CONDITION: 10 <= P. PRICE <= 30 query. addCondition (CBQuery.AND, "P", "PRICE", CBQuery.BETWEEN, values) ; // Add a WHERE CONDITION: P.NAME LIKE B% query. addCondition (CBQuery.OR, "P", "NAME", CBQuery. LIKE, "B%" // Compile and execute the query result = query. execute () ;

The following query is similar to the previous query. The price range constraint has been changed to any value greater than 10.

// Sixth query selects all products whose name begins with B and

// that cost more than $10.00 query = new CBQuery ( (CocoPowder) myBase) ; query. addMap ( "Product" , "P" ) ; query. addCondition (CBQuery.AND, "P" "PRICE" , CBQuery.GREATER THAN, new Float (10) ) ; query. addCondition (CBQuery.AND, "P" "NAME", CBQuery. IKE, "B%"); // Compile and execute the query result = query. execute () ;

The following query produces a Cartesian product

// Seventh query selects all possible combinations of CUSTOMER X

// PRODUCT and diplays the customer and product names - uses a

// projection over a Cartesian product query = new CBQuery ( (CocoPowder) myBase) ; query. addMap ( "Produc " ) ; query. setProjection ( "Product" , new String []{ "NAME" }) ; query. addMap ("Customer") ; query. setPro ection ( "Customer" , new String [] { "NAME" } ) ;

// Compile and execute the query result = query. execute () ;

The following query modifies the previous query to specify that only the number ofrecords, and not the records themselves, are to be returned.

// Eighth query selects all possible combinations between customers

// and products and displays the customer and product names query = new CBQuery ( (CocoPowder) myBase) ; query. addMap ( "Product" ) ; query. setProjection("Product" , new String []{ "NAME" }) ; query. addMap ( "Customer" ) ; query. setProjection ( "Customer" , new String []{ "NAME" }) ;

- 51 - CocoBase Enterprise OIR v4.0 Programmer's Guide // Specify that the number of records and not the records are to be

// returned query. setExtentIsCountOnly (true) ;

// Compile and execute the query result = query. execute () ;

The following query retrieves customers that have orders.

// Ninth query selects the names of all customers who have orders query = new CBQuery ( (CocoPowder) myBase) ; query. ddMap ( "Customer" , "C" ) ; query. setProjection ("C", new String [] {"NAME" }) ; query. addMap ("Order" ) ;

// INNER JOIN - Order on Customer query. addJoin (CBQuery. INNER_JOIN, "C", new String []{ "ID" } , "Order", new String [] { "CUSTOMER_ID" }) ; // Compile and execute the query result = query. execute () ;

The following query limits the previous query so that the results are distinct.

// Tenth query similar to previous query, selects all distinct

// customers who have orders query = new CBQuery ( (CocoPowder) myBase) ,^■ query. addMap ("Customer" , "C") ; query. setProj ection ("C" , new String []{ "NAME" }) ; query. addMap ( "Order" ) ; query. addJoin (CBQuery. INNER_JOIN, "C", new String []{ "ID" } , "Order" new String [] { "CUSTOMER lD" } ) ; ^"// Specify distinct results query. setExtentlsDistinct (true) ; // Compile and execute the query result = query. execute () ;

// Close the database connection myBase. close () ;

12.2 The CBQueryBuilder Class

The thought . CocoBase . CBQueryBuilder API provides the ability to formulate queries that are similar in syntax and structure to SQL92 syntax and structure. The resulting queries are statically compiled into the application and benefit from compiler type checking services, which prevent incorrect query syntax ordering. Incorrect syntax ordering results in a Java compiler error.

The CBQueryBuilder class architecture is shown graphically in Figure 12.7. The outer class method signatures are presented later in this section. Inner class method signatures are presented in following sub-sections.

Custom Querying with CocoBase - 52 - 229 The CBQueryBuider class defines the following static variables representing logical and comparison operations that are available:

♦ static final int EQUAL TO

♦ static final int GREATER THAN

♦ static final int GREATER_THAN_EQUAL_TO

♦ static final int LESS_THAN

♦ static final int LESS_THAN_EQUAL_TO

♦ static final int LIKE

♦ static final int NOT_EQUAL_TO

♦ static final int BETWEEN

♦ static final int IN

The CBQueryBuilder class defines the following interfaces that support compile time syntax order checking:

♦ static interface CBQueryBuilder.AsFromClause

♦ static interface CBQueryBuilder. FromClause

♦ static interface CBQueryBuilder. FromWhereClause

♦ static interface CBQueryBuilder. oinClause ♦' static interface CBQueryBuilder.WhereClause

The CBQueryBuilder class defines the methods listed below, which return an instance of either one of the interface types listed above, a j ava . util .Vector, if execute ( ) is called, or a CBQuery object if getQuery ( ) is called. This behavior limits the associated method calls (an query formulations) to those belonging to the returned class.

The following legend applies to the method arguments: eC = extentClass, pv = parameterValue, f E = f ieldExpression:

♦ Vector execute ()

♦ Vector execute (Class eC)

♦ Vector execute (Class eC, Object pVl)

♦ Vector execute (Class eC, Object pVl, Object pV2)

♦ Vector execute (Class eC, Object pVl, Object pV2, Object pV3)

♦ Vector execute (Class eC, Object [] pVs)

♦ Vector execute (Object pVl)

♦ Vector execute (Object pVl, Object pV2)

♦ Vector execute (Object pVl, Object pV2, Object pV3)

♦ Vector execute (Object [] pVs)

♦ CBQuery getQuery ()

♦ FromClause select ()

♦ AsFromClause select (String fEl)

♦ AsFromClause select (String fEl, String fE2)

- 53 - CocoBase Enterprise 01 R v4.0 Programmer's Guide ♦ AsFromClause select (String fEl, String fE2 , String fE3) - AsFromClause selec (String [] fEs)

♦ FromClause selectCount ()

^•♦ FromClause selectDistinct ()

♦ AsFromClause selectDistinct (String fE) > AsFromClause selectDistinct (String fEl, String fE2)

♦ AsFromClause selectDistinct (String fEl, String fE2, String fE3)

♦ AsFromClause selectDistinct (String [] fEs)

♦ AsFromClause toAsFromClause ()

♦ FromClause toFromClause ()

♦ FromWhereClause toFromWhereClause ( )

♦ WhereClause toWhereClause ()

12.2.1 CBQueryBuilder.FromClause Methods

The CBQueryBuilder . FromClause class defines the following methods that return type CBQuery . FromWhereClause.

♦ FromWhereClause from (String map)

♦ FromWhereClause from(String map, String mapAlias)

12.2.2 CBQueryBuilder.AsFromCIause Methods

The CBQueryBuilder .AsFromClause class defines the following methods that return either CBQuery . FromClause or CBQuery.From WhereClause.

The following legend applies to the method arguments: f A = f ieldAlias, f E = fieldExpression:

♦ FromClause as (String fA) FromClause as (String [] fEs) FromClause as (String fAl, String fA2) FromClause as (String fAl, String A2 , String fA3)

♦ FromWhereClause from(String map)

♦ FromWhereClause from(String map, String mapAlias)

12.2.3 CBQueryBuilder.FromWhereClause Methods

The CBQueryBuilder . FromWhereClause class defines the following methods that return either j ava . util . Vector, CBQuery. hereClause, CBQuery . FromWhereClause, or CBQuery . JoinClause.

Custom Querying with CocoBase - 54 - 231 The following legend applies to the method arguments: pv = parametervalue, eC = extentClass:

♦ Vector execute ()

♦ Vector execute (Class eC)

♦ Vector execute (Class eC, Object pV)

♦ Vector execute (Class eC, Object pVl, Object pV2)

♦ Vector execute (Class eC, Object pVl, Object pV2, Object pV3)

♦ Vector execute (Class eC, Object [] pVs)

♦ Vector execute (Object pV)

♦ Vector execute (Object pVl, Object pV2)

♦ Vector execute (Object pVl, Object pV2, Object pV3)

♦ Vector execute (Object [] pVs)

♦ JoinClause innerJoin (String map)

♦ JoinClause innerJoin (String map, String mapAlias)

♦ JoinClause leftJoin (String map)

♦ JoinClause leftJoin (String map, String mapAlias)

♦ WhereClause where (String fE, int operand, boolean value)

♦ WhereClause where (String fE, int operand, byte value)

♦ WhereClause where (String fE, int operand, char value)

♦ WhereClause where (String fE, int operand, double value)

♦ WhereClause where (String fE, int operand, int value)

♦ WhereClause where (String fE, int operand, long value)

♦ WhereClause where (String fE, int operand. Object value)

♦ WhereClause where (String fE, int operand, short value)

♦ FromWhereClause (String map)

♦ FromWhereClause X (String map, String mapAlias)

♦ OrderByClause orderBy (String fE)

♦ OrderByClause orderBy (String fE, int orderSpecifier)

12.2.4 CBQueryBuilder. JoinClause Methods

The CBQueryBuilder . JoinClause class defines the following methods that return type CBQuery . FromWhereClause.

The following legend applies to the method arguments: IKF = lef tKeyField, rKF = rightKeyField.

♦ FromWhereClause on (String IKF, String rKF)

♦ FromWhereClause on (String IKFl, String 1KF2 , String rKFl, String rKF2)

232 - 55 - CocoBase Enterprise 01 R v4.0 Programmer's Guide ♦ FromWhereClause on (String IKFl, String 1KF2, String 1KF3, String rKFl, String rKF2, String rKF3)

♦ FromWhereClause on (String [] lKFs, String!] rKFs)

12.2.5 CBQueryBuilder. WhereClause Methods

The CBQueryBuilder . FromWhereClause class defines the following methods which return either CBQueryBuilder .WhereClause or j ava . util . Vector.

The following legend applies to the method arguments: f E = f ieldExpression, eC = extentClass, pv = parameterValue.

♦ WhereClause and(String fE, int operand, boolean value)

♦ WhereClause and (String fE, int operand, byte value)

♦ WhereClause and(String fE, int operand, char value) WhereClause and (String fE, int operand, double value)

♦ WhereClause and (String fE, int operand, int value) Φ WhereClause and (String fE, int operand, long value)

♦ WhereClause and (String fE, int operand, Object value)

♦ WhereClause and (String fE, int operand, short value)

♦ Vector execute ()

♦ Vector execute (Class eC)

♦ Vector execute (Class eC, Object pV)

♦ Vector execute (Class eC, Object pVl, Object pV2)

♦ Vector execute(Class eC, Object pVl, Object pV2, Object pV3)

♦ Vector execute (Class extentClass, Object [] pVs)

♦ Vector execute (Object pV)

♦ Vector execute (Object pVl, Object pV2)

♦ Vector execute (Object.pVl, Object pV2, Object pV3)

♦ Vector execute (Object [] pVs)

♦ WhereClause or (String fE, int operand, boolean value)

♦ WhereClause or (String fE, int operand, byte value)

♦ WhereClause or (String fE, int operand, char value)

♦ WhereClause or (String fE, int operand, double value)

♦ WhereClause or (String fE, int operand, int value) WhereClause or (String fE, int operand, long value)

♦ WhereClause or (String fE, int operand, Object value)

♦ WhereClause or (String fE, int operand, short value)

♦ OrderByClause orderBy (String fE)

♦ OrderByClause orderBy (String fE, int orderSpecifier)

Custom Querying with CocoBase - 56 - 233 12.2.6 CBQueryBuilder.OrderByClause Methods

The CBQueryBuilder . OrderByClause class defines the following methods which return either CBQueryBuilder . OrderByClause or j ava . util .Vector.

♦ Vector execute ()

♦ Vector execute (Class eC)

♦ Vector execute (Class eC, Object pV)

♦ Vector execute (Class eC, Object pVl, Object pV2)

♦ Vector execute (Class eC, Object pVl, Object pV2, Object pV3)

♦ Vector execute (Class eC, Object!] pVs)

♦ Vector execute (Object pV)

♦ Vector execute (Object pVl, Object pV2)

♦ Vector execute (Object pVl, Object pV2, Object pV3)

♦ Vector execute (Object [] pVs)

♦ OrderByClause orderBy (String fE)

♦ OrderByClause orderBy (String fE, int orderSpecifier)

234 - 57 - CocoBase Enterprise 01 R v4.0 Programmer's Guide 12.3 CBQueryBuilder Usage

The state diagram for the CBQueryBuilder class is shown in Figure 12.7. This diagram specifies the possible return types for the CBQueryBuilder class and each of its inner classes. It also specifies the possible method calls for the returned class.

Figure 12.7 CBQueryBuilder State Diagram

A query can be executed by calling CBQueryBuilder . FromWhereClause . execute ( ) , CBQueryBuilder . WhereClause . execute ( ) , or

CBQueryBuilder . OrderByClause . execute ( ) after obtaining a reference to one of these classes. The following query statement represents a correctly structured query, and should compile correctly, provided a CUSTOMER table and a corresponding Customer map exist with the listed attributes.

Vector result = queryBuilder . selectDistinc ( "Customer . NAME" , "Customer . AGE" ) . as ( "NAME" , "AGE) . from ( " Customer" , "C" ) . execute d

Custom Querying with CocoBase ■ 58 ^< 235 The following query statement will fail to compile because a where ( ) method cannot be called on the AsFromClause instance that is returned by the selectDistinct ( ) method:

Vector result = queryBuilder . selectDistinct ( " Customer . NAME" , " Customer . AGE" ) . where ( "CUSTOMER. AGE" , ueryBuilder . GREATER THAN, 10 ) . execute ( )

12.3.1 CBQueryBuilder Examples

The code fragments presented below are included the CBQueryBuilderDemo application provided under the %COCO_HOME%\demos\pgude\chapterl2\CBQueryBuilderDemo directory. This application is similar to the CBQueryDemo application presented in 12.1.1. The database table structure shown in Figure 12.6 is also used for this example application.

To instantiate a new CBQueryBuilder instance:

// Instantiate CocoPowder CocoDriverlnterface database =

CocoDr iver . getCocoDriver ( " thought . CocoBase . CocoPowder " , driverName , cocoUrl , user, passwd) ; database . setThrowExceptions (true) ; // Open the database connection database . connect ( ) ;

// Instantiate CBQueryBuilder

CBQueryBuilder qb = new CBQueryBuilder (database) ;

Vector result ;

The first query performs a simple projection of the Cutomer.NAME field from the Customer map. When a single argument is passed into the select ( ) (or selectcount ( ) , selectDistinct ( ) ) method, a subsequent as ( ) is not required because map field - object attribute associations are un-arbitrary. The CBQueryBuilder instance automatically performs these associations in this case.

If execute ( ) is called with no arguments, the result Vector will contain a set of thought . CocoBase . GenPOb j ects containing the fields specified by the query.

// First query gets the name of all customer in the database result = qb . select ( "Customer . NAME" ) . from ( " Customer" ) . execute (Customer . class ) ;

When one or more arguments are passed to select ( . . . ) (i.e. a projection), an instance of CBQueryBuilder . AsFromClause is returned. If more than one map is involved in the query, or if an extent class is specified in the subsequent call to execute ( ) (e.g.

236 - 59 - CocoBase Enterprise 01 R v4.0 Programmer's Guide execute (Custome . class) ), an as ( ) clause must also be included in the query statement. The as ( ) method takes arbitrary string arguments, and the resulting objects are populated based on the argument ordering between the select ( ) and as ( ) methods. e.g.

// Returns a Vector of Customer obj ects qb . select ( " Customer . NAME " , " Custome . AGE " )

. as C'NAME" , "AGE" ) . from ( " Customer" ) . execute (Customer . class ) ;

Because map field names can overlap (e.g. Customer.ID & Address. ID), the as ( ) method can be used to distinguish between overlapping fields. Calling the as ( ) method is mandatory when two or more maps are specified in the query. No extent class is specified for execute ( ) and the second query returns a Vector of thought . CocoBase . GenPOb j ects.

// Second query gets the name and zip code of all customers // Query strategy : proj ection combined with inner j oin result = qb . select ( " Customer . NAME" , "Address . ZIP" ) . as ( "NAME" , " ZIP" ) . from ( " Customer" ) . inner oin ( "Address " ) . on ( "ADDRESS_ID" , " ID" ) . execute { ) ;

// Third query gets the name of all customers with their respective zip // codes if applicable . Query strategy : proj ection combined with left // outer j oin result = qb . select ( "Customer . NAME" , "Address . ZIP" ) . as ( "NAME" , " ZIP" ) . from ( " Customer" ) . lef t Join ( "Address " ) . on ( "ADDRESS_ID" , " ID" ) . execute ( ) ;

// Fourth query gets the name and price of each product P. The query is // ordered by 'Carl' . Query strategy: projection combined with chained // joins and condition predicate result = qb. select ("P.NAME" , "P. PRICE") .asC'NAME", "PRICE")

. from ("Product" , "P") . innerJoin ( "Lineltem" ) .on ("ID", "PRODUCT_ID" ) .innerJoin ("Order") . on("ORDER_ID" , "ID") .innerJoin ("Customer") . on ( "CUSTOMER_ID" , " ID" )

. here ( "Customer.NAME^» , CBQuery.EQUAL TO, "Carl" ) .execute () ;

Custom Querying with CocoBase - 60 - 237 In the following query, two operators are used to define a query range. The CBQueryBuilder . BETWEEN operator allows a boundary range to be specified on the product price The CBQueryBuilder . LIKE operator limits the eligible product names to those beginning with "B".

// Fifth query gets all product information for products that cost

// between $10 and $30 or name starts with 'B' . Query strategy: regular

// selection with condition predicates\n") ;

Vector values = new Vector (); values . addElement (new Float (10) ) ; values . addElemen (new Floa (30) ) ; result = qb. select () . from (" Product " , "P")

. here ( " P . PRICE " , CBQuery . BETWEEN, alues )

.or ("P. NAME", CBQuery. LIKE, "B%") . execute () ;

// Sixth query select all products that cost more than $10 and whose // name starts with ' B'. Query strategy: regular selection with // condition predicates result = qb. select () . from (" Product " , "P")

. here ( " P . PRI CE " , CBQuery . GREATER_THAN ,10) ,

. and("P. NAME", CBQuery. LIKE, "B%") .executed ;

The following query forms a Cartesian product and demonstrates the lack of affinity between arguments passed into the select (...) and as ( ... ) methods. The resulting (thought . CocoBase . GenPOb j ect) object attributes are assigned values based on the field ordering of the select (...) and as ( ... ) methods (i.e. Customer .NAME is assigned to the NAME1 attribute and Product . NAME is assigned to the NAME2 attribute). The Vector result contains the GenPOb j ect instances.

// Seventh query selects all possible combinations between customers // and products and show their names. Query strategy: projection over // cartesian product result = qb. select ("Customer. NAME" , "Product. NAME")

// Assign arbitrary field names

.asf'NAMEl", "NAME2") . from ( "Customer" ) .Xf'Product") .executed ;

238 - 61 - CocoBase Enterprise O/R v4.0 Programmer's Guide The following query modifies the previous query to specify that only the number of records, and not the records themselves, are to be returned.

// Eight query counts all possible combinations between customers and // products . Query strategy: regular count on cartesian product result = qb . selectCount ( ) . from ( "Customer" ) . X ( " Product " ) . execute d ;

The following query retrieves customers that have orders.

// Ninth query select the name of all customers who have orders // Query strategy: regular projection over inner join result = qb. select ("C.NAME") .asC'NAME") . from ( "Customer" , "C" ) . innerJoin ( "Order" ) . on ( "ID" , "CUSTOMER_ID" ) .execute () ;

The following query modifies the previous query so that the results are distinct, and instances ofCustomer . class are returned instead ofthought . CocoBase .GenPObject.

// Tenth query select all distinct names of all customers who have // orders. Query strategy: distinct projection over inner join result = qb. selectDistinct ("C.NAME") .asC'NAME") . rom ("Customer" , "C") . innerJoin ( "Order" ) . on ( "ID" , "CUSTOMER_ID" ) .execute (Customer. class) ;

// Close the database connection database. close () ;

12.4 The CocoBase EJBQLQuery Class

CocoBase supports dynamic queries based on the EJB QL syntax, as described in the EJB 2.0 specification. This allows standard EJB QL styled queries to be issued, on the fly, against any abstract database schema described by a CocoBase map. EJB QL support is provided through the thought . CocoBase . EJBQLQuery class. This layer of abstraction, above the CocoBase mapping layer, processes statements formulated in standard EJB QL syntax and compiles them into a form that can be further processed by the underlying CocoDriverlnterface class. Compiled EJB QL statements are passed into a CocoDriverlnterface instance in the form of dynamically generated custom query objects and CocoBase maps, which are then used to (transparently) generate and issue SQL based queries on the JDBC connection. Only the basic maps for corresponding database tables are required in the CocoBase repository. A basic map implements a direct relationship between database and object attributes.

Custom Querying with CocoBase - 62 - 239 12.4.1 EJBQLQuery API

The EJBQLQuery class provides the following methods:

♦ void compile (java. lang. String queryString)

This method compiles the EJB QL query string that is passed in into the EJBQLquery object.

♦ j va.util.Vector executed

This method executes the compiled query.

♦ int getParameterCoun ( )

This method returns the number of parameters declared in the EJB QL query string.

♦ java.util.Vector getParameterlds ()

This method returns a Vector of Integer objects that indicate the identifiers of the declared parameters for the compiled EJB QL query.

♦ java.util . Vector getParameterTypes ( )

This method returns a Vector of objects that indicate the corresponding Java types of the declared parameters for the compiled EJB QL query.

♦ boolean getQuerylsDistict ( )

This method tests the compiled EJB QL query to determine if distinct values are returned.

♦ boolean getQuerylsProj ection O

This method tests the compiled EJB QL to determine if the query is a projection over a field.

♦ java. lang. String getQueryMap ()

This method returns the map name that describes the selecting variable of the EJB-QL query.

♦ static void setDebug (boolean debug)

This method sets the debug mode. The default setting is false (off).

♦ void setExtent (j ava . lang. Class j avaCls)

This method sets the extent class for the EJB QL query object.

♦ void setParameter (int id, j ava . lang . Ob j ect value)

This method binds a parameter identified by id to the value passed in

12.4.2 EJBQLQuery Usage

The thought . CocoBase . EJBQLQuery class is the foundation for all EJB QL syntax driven queries. It requires a navigation model describing the object model relationships and can be configured to use an inheritance model if inheritance hierarchies occur in the object model (see Section 11.1, Defining and Using Inheritance Models ).

240 - 63 - CocoBase Enterprise OIR v4.0 Programmer's Guide An instance of thought . CocoBase . CocoDriverlnterface and a CocoBase navigation model are passed into the constructor of the EJBQLQuery class as follows:

// Instantiate the CocoDriverlnterface class // Note : dynamic . uerying must be set to true CocoDriverlnterface myBase = CocoDriver . getCocoDriver (

" thought . CocoBase . CocoPowder" ,

" oracle . j dbc . driver . OracleDriver " ,

" EJBQL" ,

"EJBQL" ) ;

// Make a JDBC connection myBase . connect ( ) ; myBase . setThrowExceptions (true) ;

// Enter and compile the query ejbQuery . compile ( " SELECT DISTINCT p . name FROM Person p WHERE p . address . city = ' San Francisco' " ) ;

// Execute the query

Vector qResult = ejbQuery. execute ( ) ;

Because p . name is of type j av . lang . String, qResult will contain a set of j ava . lang . String objects whose values are the distinct names of the selected persons. This example assumes that a relationship is defined in the navigation model, for the Person and Address classes, and that the Address map, maps the VARCHAR field: ADDRESS.CITY to the Java String field address . city.

The field names used in the query (e.g p . name) are derived from the CocoBase map field names, and have no direct relation to Java attribute names. That is, the field names used in the EJB QL statements are obtained from the abstract schema defined by the map, not from the actual database or object schemas. The map fields act as a dynamic proxy for Java attributes, and they are bound to each other at runtime. Once bound, map field names determine which Java attributes are referenced in the query.

Custom Querying with CocoBase - 64 - 241 ^m Figure 12.8 , the field names that can be referenced in a query are the map field names ID, DATE and TOTAL (not the Java attribute names eyedee, dayt, toetul).

Figure 12.8 Java, Database, and Map Fields The following EJB QL query is valid:

SELECT OBJECT (o) FROM ORDER O WHERE O. id = 6443584006 while the following query will fail:

SELECT OBJECT (o) FROM ORDER o WHERE o. eyedee = 6443584006

242 ■ 65 - CocoBase Enterprise OIR v4.0 Programmer's Guide 12.4.3 Overriding the Default Instantiation Behavior

The following EJBQLQuery query returns a Vector of com. foo . Person objects by default:

// Specify the query ejbQuery. compile ("SELECT OBJECT(p) FROM com. foo. Person p WHERE p. address. city = 'San Francisco'");

// Exectute the query

Vector qResult = ejbQuery. execute () ;

The implied result set class can be overridden by specifying an explicit class name through the method setExtent ( ) . The following example sets the result set class to be of type Person:

// Explicitly set the result set class type e j bQuery . setExtent ( Person .class )

// Specify the query ejbQuery. compile ("SELECT OBJECT(p) FROM, com. foo. Person p WHERE p. address. city = 'San Francisco'");,

// Exectute the query

Vector qResult = ejbQuery. execute () ;

If instance creation fails because of a type mismatch or non-existent class, the Vector qResult will contain a set of thought . CocoBase .GenPOb j ect objects containing result set values in generic form. Further information on using the thought . CocoBase . GenPOb j ect class can be found in Section 3.5 and in the CocoBase Enterprise Javadocs.

12.5 Using EJB QL Input Parameters

CocoBase supports the use of EJB QL input parameters that allow query values to be dynamically specified. The following EJB QL query:

SELECT DISTINCT OBJECT(o) FROM Order o, IN (o. lineltems) 1 WHERE 1. roduct . name = ?1 AND 1. product .price < ?2 lists two input parameter designated by ?ι and ?2. Input parameters values are specified using the thought . CocoBase . EJBQLQuery method: public void setParameter ( int id, Object value )

The following code fragment demonstrates using the EJBQLQuery class with dynamic input parameters.

String s = "SELECT DISTINCT OBJECT(o) FROM Order o, IN (o. lineltems) 1 WHERE 1. product. name = ?1 AND 1.product .price < ?2";

Custom Querying with CocoBase - 66 - 243 Vector params = new Vector () ; params . addElement ( "Dentyne" ) ; params . addElement (1.25) ; this . executeQLQuery (s, params);

public Vector executeQLQuery (String qlstring. Vector lparams)

{ for(int i=0; i<lparams . size () ; i++)

{

// Set the query parameters - there are two in this example ejbQuery. setParameter (i+1, lparams. elementAt (i) ) ; }

// Compile the query ejbQuery. compile (qlstring) ;

// Specify an explicit class name - otherwise a class name matching

// the map name Order is set by def ult. The class myOrder must be

// defined. ejbQuery. setExtent (myOrder. class) ; return ejbQuery. execute () ;

12.6 The EJB QL Console

EJB QL workspace consoles are provided with the CocoBase distribution as both command line and CocoAdmin tools. This console can be used to issue dynamic queries on an arbitrary JDBC connection.

The command line console start script, EJBQLConsole . bat, is located under the %coco_HOME%\demos directory. The EJBQLQuery class installs a set of example records against which EJB QL commands can be issued. The database entries and table relationships are shown in Figure 12.9 and the corresponding object model is shown in Figure 12.10. The EJBQLConsole is also provided as source and can be modified to include custom behavior.

The CocoAdmin EJB QL console is located under the tools option of the main menu.

244 - 67 - CocoBase Enterprise O/R v4.0 Programmer's Guide

Figure 12.9 Database Table and Record Configuration for EJB QL Example

Custom Querying with CocoBase 68 245

Figure 12.10 Object Model for EJB QL Example

The following files (included under the %coco_HOME%\demos directory) must also be available on the classpath at runtime:

♦ connection. roperties Specifies CocoDriver and database connection parameters

♦ EJBQLModel .properties Navigation model for example database

♦ EJBQLModelinheπt .properties Inheritance model for example database

♦ EJBQLRepository.xml CocoBase database map definitions

12.7 Leveraging the Mapping Layer Abstraction

246 69 CocoBase Enterprise OIR v4 0 Programmer's Guide Figure 12.11 and Figure 12.14 describe the process or remapping the primary and foreign key relationships in the Customer and Order tables.

Figure 12.11 Initial Database Foreign Key Relationship

The ORDER.CUSTOMER_ID column initially references the CUSTOMER.ID field. The CocoBase Map hides the SSN field of the CUSTOMER table, therefore, this field is not propagated to the resulting object instances. The foreign key relationship is mapped to object links through the navigation model.

Figure 12.12 initial Mapping Configuration

If the following EJB QL query is issued:

SELECT OBJECT (c) FROM Customer c, IN (c. orders) o WHERE o. total > 65

Custom Querying with CocoBase - 70 247 the result set might include the following Order objects (object references are shown in red):

Figure 12.13 Result Set from an EJB QL Based Query

The Customer object customer and its dependent objects, order_0 and order L, are object representations of corresponding database records.

If the database structure is changed so that the foreign key field, ORDER. CUTOMERJD, references CUSTOMER.SSN instead of CUSTOMER.ID, only the CocoBase map and navigation model need to be updated. Once this occurs, the same EJB QL syntax:

SELECT OBJECT (c) FROM Customer c , IN (c . orders) o WHERE o . total > 65 can be used to perform an identical query.

Figure 12.14 Remapped Column and Navigation Model

NOTE: The figure shows the CUSTOMER.SSN column being mapped to the customer . id field in the object space. For this to occur successfully, the datatypes must match, or be matched through the CocoBase map.

■ 71 - CocoBase Enteφrise 01 R v4.0 Programmer's Guide

Figure 12.15 Result of Query using CUSTOMER.SSN as the Primary Key

12.8 Query Range Restriction

The queries listed in this section can be performed against the example EJB QL database using the EJBQLConsole utility introduced in Section 12 5.

the following EJB QL query:

SELECT OBJECT (c) FROM Customer c WHERE c age > ₃0 selects all employees whose age is greater than 30. The result of the query against the example database is:

Person [ 0] id=12 name=Mary age=43 Person [1] ld=13 name=Joe age=31 Person [2 ] id=14 name=Rose age=33

Custom Querying with CocoBase - 72 - 249 If a restrictive WHERE clause (CUSTOMER. AGE > 35) replaces the default equality WHERE clause in the Customer map,

the same EJB QL query produces the following results:

Person [0] : id=12 name=Mary age=43

12.9 Dynamic EJB QL Support for BMP and CMP

CocoBase Enterprise provides dynamic EJB QL support for selected BMP and CMP Entity Beans targets that are generated using the CocoAdmin tool. Dynamic query capability is provided through the following specialized finder method: java.util . Collection f indByQLQuery (String paraml, java.util . Vector v) throws j va . rmi .RemoteException, javax. ejb. FinderException;

This specialized finder method allows custom query strings to be passed by the client at runtime. The query is processed and executed by the thought . CocoBase .EJBQLQuery and other standard CocoBase runtime classes.

The f indByQLQuery finder method can be generated into the EJB source code by setting the useDynamicEjbql and useNavigator properties to true when generating the source files from CocoAdmin. For further information on implementing and using the EJB QL features for J2EE, see the server specific CocoBase EJB tutorials.

12.10 EJB QL Syntax Limitations

50 ■ 73 - CocoBase Enterprise OIR v4 0 Programmer's Guide 1. IS NULL object comparisons and IS EMPTY and MEMBER OF collection expressions

Proper execution of these expressions is dependent on the level of support provided by the underlying JDBC driver and/or database, is NULL object comparisons and is EMPTY and MEMBER OF collection expressions: e.g. SELECT OBJECT (C) FROM Customer AS c WHERE c . Order IS NULL require the underlying JDBC connection to support correlated EXISTS (subquery) SQL clauses, as in the SQL expression below:

SELECT * FROM TI WHERE EXISTS (SELECT * FROM T2 WHERE FI = T . Field)

If the target database does not support this type of SQL sentence, then any EJB QL queries containing IS NULL object comparisons and IS EMPTY and MEMBER OF collection expressions will fail to execute.

Regular field IS NULL comparisons and other types of comparison expressions: e.g. SELECT OBJECT (o) FROM Order AS o WHERE o. orderDate IS NULL are fully supported.

2. ESCAPE and FUNCTIONS

3. ORDER BY

Support for the ORDER BY clause, while not official (as it appears only in the public draft of the EJB 2.1 specification as of this writing), is provided by CocoBase Enterprise EJB QL facilities. Query results can be ordered by a specified field by appending the ORDER BY syntax to the end of the query sentence. This topic will be revisited as soon as the final release of the specification is made available.

Custom Querying with CocoBase - 74 251 APPENDIX 6

QUERYING CONCEPTS - CocoBase® Whitepaυer

Dynamic Universal Querying™ with CocoBase® For EJB and Java Applications on the J2EE, J2SE and J2ME Platforms

CocoBase® Enterprise O/R, Version 4.5 includes important new innovations for the Java database developer. The 4.5 version of CocoBase® provides powerful Dynamic Universal Querying™ 'for the J2EE, J2SE and J2ME platforms. Developer's can now benefit from an excellent and easy-to-use solution for dynamically querying application data in Java.

The query layer is built on the "Dynamic Object to Relational Mapping" architecture of CocoBase®. This extends for the developer all the benefits of CocoBase®, making for a querying system that decouples the application object from a particular database structure or particular SQL syntax, which allows data objects to be easily generated, maintained and reused. This f rthers the ability of CocoBase® to cut up to 85% of the cost of database access development.

The CocoBase® querying layer can be used with EJB-QL, a standards-based, object oriented syntax as defined in the EJB 2.0 specification. It can also be used at an API level by with the CBQuery Builder which looks and works almost exactly like SQL. The developer has the choice to use either declarative- type querying with EJB-QL or procedural-type querying using the CBQuery Builder API to implement Dynamic Universal Querying™ 'in their Java applications.

The highlight is combining the simplicity of EJB-QL with the dynamic nature of the CocoBase® O/R Mapping layer. It results in unlimited querying for the user without the time-consuming work of pre-coded and pre-compiled SQL statements or custom finders. With the multitude of Object Query Languages that generally only work with one component architecture, THOUGHT Inc.® felt it was important to choose a single query syntax that was powerful enough to issue meaningful queries and simple enough that most developers could easily learn to use it. EJB-QL fits these requirements very well and the CocoBase® implementation uses the syntax for any type of Java persistence and not just Enterprise Java Beans (EJB).

Highlighted Benefits:

□ Powerful and Easy to Use Querying system for issuing Meaningful Queries. α One Querying system for use with CMP & BMP Entity Beans, Session Beans, JSPs, Dynamic Transparent Persistence and Stand-Alone Java persistence. α Provides Unlimited Querying (based on the mapped data) to Application Users without all the work of pre-coded / precompiled SQL statements or custom finders. α Query system is architected to provide powerful queries with a rapid response time. p No changes to the Query are needed when changes are made to the database. α Further increases security of data in the database by limiting access to only the data represented in the map. Two Ways To Express a Query:

Declarative-type, standards-based Querying using the EJB-QL

Syntax as specified in EJB 2.0. Procedural-API type Querying using the CocoBase® CBQuery Builder API, looks almost exactly like SQL, and is based on an

"Abstract Schema (i.e. maps)."

Dynamic Universal Querying^'™

CocoBase Enterprise O/R - Dynamic Universal Querying^'1

II. DYNAMIC FLEXIBLE NATURE OF THE COCOBASE® QUERYING SYSTEM Understanding the CocoBase® Dynamic O/R Mapping Architecture

CocoBase® removes database specific code and predefined SQL from the application source code and instead, relies on user defined database maps to generate application specific SQL at runtime. CocoBase® maps provide a template for dynamically generating the structure of the SQL, and query values are programatically specified and inserted at runtime. This architecture decouples the application object from a particular database structure or particular SQL syntax and allows data objects to be easily generated, maintained and reused. It also centralizes database and object model maintenance since maps stored in database repositories can easily be edited or replaced, and any changes are instantly reflected throughout the enterprise.

Typically, O/R tools are often implemented by statically coding SQL directly into an application, or by tightly coupling the O/R layer to a single class hierarchy, which can be very fragile, or may result in limited reusability and difficulty in maintenance. In contrast, the CocoBase® dynamic mapping layer allows data maps to be shared, changed on the fly, and evolved quickly to meet the needs of the enterprise. The CocoBase® O/R mapping layer also provides many performance and scaling optimizations that can easily be configured to best fit specific application needs without requiring application regeneration and redeployment. Dynamic Universal Querying TM

CocoBase's® Dynamic Universal Querying ™runtime-based solution lets developers construct useful & meaningful Object queries' based on an abstract object model (that is represented in a set of maps), instead of hard-coded to the physical table structure as is typically done. Because a CocoBase® map binds an object structure to a relational structure in real-time, different relational table structures can be used with the same object structure as long as the map expresses how this should occur. This means that a single object model can be used on a variety of different optimized physical layouts without recompile or redevelopment of application code. Simply create a map in CocoBase® and when the application connects to the database, the correct runtime mapping will transparently occur. Because the basis of the query is the map, developers have a lot of flexibility to fine tune exactly how the resulting SQL will be generated, even including specific code required by the database in the map. And developers can protect their investment by building queries at a more abstract level, instead of low level SQL or static EJB-QL.

III. ACCESSING COCOBASE® DYNAMIC UNIVERSAL QUERYING ™WITH EJB-QL

The CocoBase® Dynamic Universal Querying™ system can be accessed using the EJB-QL syntax (an object query language created for the J2EE Container Managed Persistence architecture). This new approach is profoundly different from how developers are accustomed to using EJB-QL. It is a decidedly more dynamic and runtime based architecture. The typical querying systems now available require that queries be pre-compiled prior to deployment and they become statically 'converted' into SQL. This process rigidly binds an object query to a specific relational database structure that severely limits any cross database portability. This also limits the ability of the database administrators (DBA) to evolve the relational schema as needed. Now developers will not have to know in advance, for example at the creation of an Entity Bean, all of the query options that the user will need. The CocoBase® Dynamic Universal Querying™ system lets the client developer or even end user issue an EJB-QL Query at runtime!

The same Entity Bean can be re-used extensively by simply retargeting to another relational database, which may have an entirely different naming convention and table layout, where one is normalized and the other de-normalized. With CocoBase® the same bean would work unchanged and would not require redeployment. This would be accomplished by simply retargeting the JDBC driver. With most built-in CMP solutions, this functionality is not even an option. The beans would have to be re-written for each use to reflect the different table structures of the database which is a costly and time consuming prospect. Using the EJB-QL syntax with the CocoBase® Dynamic Universal Querying ™ system is not limited to just querying against CMP Entity Beans. It can be used with BMP Entity Beans, Session Beans, JSPs, Servlets, and stand-alone Java persistence. The CocoBase® Dynamic Transparent Persistence ™ architecture has also incorporated this new querying system.

CocoBase® includes an EJB-QL Console tool that allows developers to design and experiment with their queries (see below for screen shot). It is integrated with the CocoBase® graphical user-interface

"CocoAdmin" and allows input of free-form EJB-QL with a real-time response. It is also available as a command prompt level as well. These allow developers to directly perform Object Oriented queries on the CocoBase® repository and verify the results against the database. This is a powerful tool that developers can use to quickly and effectively check their queries. Diagram of Dvnamic Universal Ouervins ™ 5s z EJB-QL

_ -. . „.. . < .,» , ,» * .

1

EJBQL Query

(syntax that 1 represents a conceptual query) J

Relational

Database

Layer

EJB-QL Examples

Here is an example of using CocoBase's® Dynamic Universal Querying™ 'with a Finder (notice that you do not need to put in a finder for every possible iteration of the query which saves a lot of time):

Vector params = new Vector(); params . add(getld());

Enumeration customerEnum = customerHome.findByQLQuery(

"SELECT OBJECT(c) from Customer c where c.id = ?1 ", params);

// step through enumeration

Here is an example of using CocoBase's® Dynamic Universal Querying™ -with the EJB-QL syntax actually used directly in the code:

EJBQLQuery ejbQuery = newEJBQLQuery(cocoDataSource,nav.getModel()); ejbQuery.compileC'SELECT OBJECT(c) from Customer c where c.id = ?1 "); ejbQuery.setExtent(com.mypkg.Customer.class); ejbQuery.setParameter(l,myCustomer.getId()); pkv = ejbQuery.execute(); CocoBase® EJB-QL Console Tool For Real-Time Checking Queries

CocoBase® includes an EJB-QL Console tool that allows developers to design and experiment with their queries (see below for screen shot). It is integrated with the CocoBase® graphical user-interface "CocoAdmin" and allows input of free-form EJB-QL with a real-time response. It is also available as a command prompt level as well. These allow developers to directly perform Object Oriented queries on the CocoBase® repository and verify the results against the database. This is a powerful tool for developers to quickly and effectively check their queries.

Screen shot of the CocoBase® EJB-QL Console:

Screen shot of the CocoBase® EJB-QL Console at the Command Prompt Level:

IV. ACCESSING COCOBASE® DYNAMIC UNIVERSAL QUERYING ™WITH CBQUERY BUILDER For developers who do not choose to use EJB-QL, the CocoBase® Dynamic Universal Querying ""system is also available at an API level. The CB Query Builder provides method calls in the form of APIs to program any needed "advanced" queries. This would be used when the standard CocoBase® Query system "Query by Example" does not meet the needs of the developer.

The CBQuery Builder APIs are designed to look almost exactly like SQL. In fact, if you know how to use SQL then using the CBQuery Builder API is almost trivial to learn. The real difference from using the APIs versus SQL, is that the API is focused at the CocoBase® map level and not directly at the database level of tables and rows. This provides the developer a high degree of flexibility to execute a query in a desired manner with fine-grained control over the results.

This is Procedural-API type querying that is based on an "Abstract Schema" and is represented in the CocoBase® Map that defines where the data is held in the database and how it is accessed. This is excellent for low-level querying that is focused on the specifics of creating custom queries that span relationships between maps. Compared to using EJB-QL, it does not require parsing and eliminates the need to compile the query.

The CBQuery Builder can be used in any Java environment or architecture. It is simply a Java API to query against the database, just like JDBC. For example, it can be used with BMP Entity beans, Session beans, JSPs/Servlets as an alternate way to implement finder/selector methods or even with custom session beans. Examples of when the CBQuery Builder API could be a benefit are reporting, high through-put queries such as large result sets, impromptu queries where object model management is not important, and where the limitations of querying under the J2EE EJB specification is not convenient. Where as EJB-QL is constrained by the physical navigation and object model, using the CBQuery Builder is much more free- form. It doesn't require an object or navigation model that matches the query syntax for increased flexibility. Diagram of Dynamic Universal Querying™ 'using the CBQuery Builder

CBQuery ,

CBQuery Builder API Builder Query Querying <

Layer ,

CocoBase

Query

System

Dynamically & Constructs & Runtime Executes SQL Layer

Relational ^"J

Database 4

Layer

CBQuery Builder Example

Example of Using CBQuery Builder API to Execute a Query;

Vector result = qb.select("Customer.NAME","Address.STREET","Address.ZIP") .as("NAME","STRET","ZIP") .from("Customer").innerJoin("Address").on("ADDRESS_ID","ID")

.execute();

V. CHOOSING WHETHER TO USE EJB-QL OR CBQUERY BUILDER Choosing to use EJB-QL or CBQuery Builder depends mostly on the goals of the developer, more than the actual architecture of the application. Please note that both alternatives are valid for use with any Java architecture.

Using EJB-QL can be considered a "best practice" use of CocoBase® for the following cases;

Used at a higher-level programatically,

Is a declarative-type language,

Object-Oriented approach (i.e. data navigation is through path expressions, no joins are needed, collection and object value comparison expressions are allowed, etc.)

Standards-based syntax that is part of the J2EE EJB specification.

However, use of the EJB-QL with the CocoBase® Dynamic Universal Querying™ system requires;

Developers need to know or learn EJB-QL which is fairly simple. Requires use of a navigation model. Follows the Java naming convention that CocoBase® uses to do Java name conversion. Parsing and compilation step required prior to execution of query. Queries need to conform to EJB-QL query syntax guidelines. On the other hand, the CBQuery Builder can be considered a "best practice" use of CocoBase® when;

No object / navigation model is available or used.

Developers are more comfortable with SQL-like algebra (projections, joins, cartesian product) and don't want to know about the EJB-QL object-oriented approach. - When more flexibility is needed than is provided by EJB-QL (i.e. the query cannot be expressed in EJB-QL due to language constraints).

VI. CLOSING THOUGHT Inc.® innovated the world of Object to Relational Mapping with CocoBase® by introducing the concept of Dynamic Object to Relational Mapping ™. The new 4.5 release of CocoBase® expands on this legacy of innovation by providing truly Dynamic Universal Querying ™ (runtime-based) that is accessed with both EJB-QL, a standards-based Object Querying syntax from the J2EE EJB specification, and the CBQuery Builder API by using the CBQuery Builder. The dynamic, runtime-based approach of CocoBase® offers extensive deployment and re-use options that are generally less expensive to develop and maintain. In the current economic environment, a tool feature such as this that increases both efficiency and effectiveness becomes critical to the success of the enteφrise developer.

About CocoBase® Enteφrise O/R. CocoBase® Enterprise O R, Optimized for JZEE, J₂SE and J₂ME Customer Success©, solves the Object to Relational impedance mismatch. It virtually eliminates the need to hand-code database access for EJB and Java Applications. This can directly decrease up to 85% of the cost of database access development for enteφrise customers faced with deploying fine-grained / coarse grained, simple to complex relationships in company applications. About THOUGHT Inc.®.

THOUGHT Inc.®, the Dynamic O/R Mapping™ Company, architects of CocoBase® Enteφrise O/R, was formed in 1993, and subsequently revolutionized object to relational mapping technology with landmark solutions and industry leadership. More information on THOUGHT Inc.® can be obtained online at WWW.THOUGHTINC.COM or by calling, (₄15) 836-9199. LEGAL NOTICES

©This document is copyrighted and owned by THOUGHT Inc.®. CocoBase® and THOUGHT Inc.® are registered trademarks of THOUGHT Inc. ®. Dynamic O/R Mapping™, Dynamic Object to Relational Mapping™ and Dynamic Transparent Persistence™ are pending trademarks of THOUGHT Inc.®. CocoBase® technology is based on US patent #5857197 as well as additional pending patents directed to object navigation, object modeling and caching. All other trademarks are property of their respective company. This publication is provided "as is" without warranty of any kind, either express or implied, including, but not limited to, the implied warranties of merchantability, fitness for a particular puφose, or non-infringement, to also include any and all technical inaccuracies or typographical errors.

Claims

I clai : 1. A software package including:

(a) an interface component for setting results, comprising

(i) an interface for inputting an object base query,

(ii) an interface for inputting a name of an output source obtained from said query, and, (iii) a compiler arranged to parse said query, and,

(iv) an output module ranged to pass the parse query to another software component;

(b) a second software component having an input and an output means, and comprising (i) a parsed object based on said query,

(ii) an object model name responding to said query, (iii) identification of a mapping repository having an object schema/object map; and,

(c) a third software component comprising (i) means for utilizing a mapping repository name,

(ii) means for populating custom query objects, (iii) means for accessing data in populated custom query objects, and, (iv) means for outputting results.