Copyright © 1999 by Novosoft Inc. All rights reserved

OQL Queries in FL Technology

prepared by
Dmitry E Tishkovsky and Vladislav M Dutov

Contents

  1. Preface
    1. About OQL
    2. About This Document
  2. Addition to OQL Standard 1.2
    1. Additions to 4.11.1.1 Axiom
  3. How to Write FL OQL Query
    1. To get entity
    2. Class Attributes
    3. Class Associations
    4. Association with zero or one multiplicity
    5. Association with zero or more multiplicity
    6. Aggregation operations
  4. How to Execute FL OQL Query
    1. OQLInterpreter
    2. Executing
    3. Simple way
    4. Prepared Statement
      1. OQLQuery
  5. Supported OQL Queries
    1. Axiom
    2. Basic
    3. Simple Expression
    4. Comparison
    5. Boolean Expression
    6. Constructor
    7. Accessor
    8. Collection Expression
    9. Select Expression
    10. Set Expression
    11. Conversion
  6. FL and OQL
    1. FL Transactions
    2. OQL System Responsibilities

Preface

About OQL

OQL is a simple language. It looks like SQL. If you know SQL you may read this document and glance over OQL specification from ODMG to know how to write FL OQL query.

There are some additions. They are contained in this document at Addition to OQL Standard 1.2 chapter.

About This Document

This document describes how to write and how to execute FL OQL queries. Also it contains some additions to ODMG OQL standard 1.2 those may be used at OQL queries.

Addition to OQL Standard 1.2

Additions to:

Additions to 4.11.1.1 Axiom
query_program ::= {define_query;} query
define_query ::= define function_name(type_name identifier {, type_name identifier}) as query

How to Write FL OQL Query

This chapter has some examples of FL OQLs. To learn OQL see ODMG OQL specification.

To get entity

Getting of all entities:

select e from Entity e
e is iterator of collection Entity. SQL query for this OQL query is:
select e.id0, e.typeid0 from Entity0 e where e.id0 > -1
This OQL query result in List<Entity>. Each element of the list is obtained via ObjectManager.getObject(Integer primaryKey, Integer typeId). Thus the result is a list of entity proxies.

Getting of entities with id less than 100:

select e from Entity e where e < 100
SQL for this:
select e.id0, e.typeid0 from Entity0 e where e.id0 < 100 and e.id0 > -1
So when iterator of Entity collection is part of arithmetic or relational operation then it is interpreted as id.

Class attributes
<<entity>>
Type
name:String
attrInt:int

select t.name from Type t where t.attrInt < $1
Results in List<String>. "$1" is bind argument. Its value you may set before execution of the query.

select t, t.attrInt from Type t where t.attrInt < $1
Results in List<List<Type, Integer>>.

select t.name from Type t where t.name like $1
Results in List<String>.

select t from Type t where t in set(101,202,303)
Results in List<Type>. SQL for this:
select t.id0, t.typeid0 from Type0 t where t.id0 in (101,202,303) and t.id0 > -1

Class associations

Association with zero or one multiplicity
<<entity>>
Type
name:String
attrInt:int
role
1
<<entity>>
Role
name:String

select t.role from Type t
Results in List<Role>.


select r from Type t, Role r where t.role = r
Result is the same.


select t, r
from Type t, Role r
where t.name = $1 and t.attrInt = $2 and r.name = $3
Results in List<List<Type, Role>>.

Association with zero or more multiplicity
<<entity>>
Type
name:String
attrInt:int
roleList
0..*
<<entity>>
RoleList
name:String

select t,rl from Type t, t.roleList rl
Results in List<List<Type, Role>>. Result is Cartesian product t*rl.


select rl from Type t, t.roleList rl where rl.name != nil
Results in List<Role>: Role objects those are associated with a Type object and have name attribute of not null. SQL for this OQL query is:
select rl.id0, rl.typeid0
from Type0 t, Role0 rl, Type_rolel92 al_0
where rl.name is not null and
t.id0 = al_0.id0 and al_0.roleList0 = rl.id0 and rl.id0 > -1

To get list attribute elements as list:

select t.roleList from Type t where t.roleList != nil
For this query two SQLs are executed:
select t.id0, t.rolelist_194
from Type0 t
where t.rolelist_194 = 'N'
and
select al_1.id0, al_0.id0, al_0.typeid0
from Type_rolel92 al_1, RoleList0 al_0
where al_1.rolelist0 = al_0.id0 and al_1.id0 in (87,102,203,413)

Aggregation operations

select count(e),min(e),max(e) from Entity e
is translated to
select count(e.id0),min(e.id0),max(e.id0) from Entity0 e where e.id0 > -1

How to Execute FL OQL Query

To execute OQL query it is needed to create instance of OQLInterpreter (ru.novosoft.uml.oql.OQLInterpreter).

OQLInterpreter
Constructor Detail
public OQLInterpreter(com.novosoft.fl.ObjectManager objectManager,
                      java.lang.String mappingFileName)
Parameters:
  • objectManager - is to get entity proxies (using ObjectManager.getObject(Integer id, Integer typeid)), connection, and dbtype.
  • mappingFileName - name of file with FL mapping. See below how to create it.

or

public OQLInterpreter(com.novosoft.fl.ObjectManager objectManager,
                      java.io.InputStream mappingFile)
Parameters:
  • objectManager - is to get entity proxies (using ObjectManager.getObject(Integer id, Integer typeid)), connection, and dbtype.
  • mappingFile - stream of file with FL mapping. See below how to create FL mapping.

Mapping file is property file. To create mapping file FL packaging (since fl4_2_2-jdk1_2.zip) has createMapping.cmd scenario. This cmd-file builds mapping on UML model and atr-file.

Executing

There some ways to execute OQL query. Among them there is a possibility to create a prepared statement.

Simple way

One of OQLInterpreter methods to execute OQL query is Object executeQuery(String oqlQuery, Object[] args).

Example. Let we want to execute

select e from Entity e where e < $1
OQL query and set $1 to Integer of 100: 

import ru.novosoft.uml.oql.OQLInterpreter;


public static void main(String[] args) throws Exception
{
  String mappingFileName = args[0];
  String oqlQuery = "select e from Entity e where e < $1";
  Object[] params = new Object[1];
  
  params[0] = new Integer(100);
  
  executeMyOQLQuery(oqlQuery, params, mappingFileName);
}


private static void executeMyOQLQuery(String query,
                                      Object[] params,
                                      String mappingFileName)
  throws Exception
{
  OQLInterpreter interpreter = new OQLInterpreter(objectManager, mappingFileName);
  
  Object obj = interpreter.executeQuery(query, params);
  
  System.out.println(obj);
}

Prepared statement usage
OQLQuery
 
OQLQuery OQLInterpreter.createOQLQuery(java.lang.String oqlQuery,
                                       java.sql.Connection connection)

A OQLQuery object represents OQL query after parsing, and translation to SQL. More than it is a java.sql.PreparedStatement wrapper. To bind OQL query parameter OQLQuery has bindXXX methods.

Example of OQLQuery usage. Steps:

  1. Create an instance of OQLQuery.
  2. Bind arguments to the instance.
  3. Execute.
  4. Close.
import ru.novosoft.uml.oql.OQLInterpreter;
import ru.novosoft.uml.oql.OQLQuery;
import java.sql.Connection;


private static void executeMyOQLQuery(OQLInterpreter interpreter,
                                        Connection connection)
  throws Exception
{
  String query = "select e from Entity e where e >= $1 and e < $2";
  // preparing of the query
  OQLQuery oqlQuery = interpreter.createOQLQuery(query);

  // getting Entity0 layers
  for(int i = 0 ; i < 10 ; i++)
  {
    // binding
    oqlQuery.bindInt(i * 100);
    oqlQuery.bindInt((i + 1) * 100);
    // execution
    obj = oqlQuery.executeQuery(true); // "true" to reset the binding

    System.out.println("Layer " + i + " : " + obj);
  }
  // closing
  oqlQuery.close();
}

Supported OQL Queries

Supported features are described in Grammar terms. (see Grammar chapter in ODMG OQL specification)

Axiom

All including:

query_program ::= {define_query;} query
define_query ::= define function_name(type_name identifier {, type_name identifier}) as query

Basic

All.

Note: a bind argument has name with prefix of dollar sign "$" and postfix of a positive integer.

Simple Expression

All excepting:

  1. The operator + for list and array concatenation.

Comparison

All.

Boolean Expression

All.

Constructor

Supported:

  1. query ::= array([(literal|bind_argument) {, (literal|bind_argument)}])
  2. query ::= bag([(literal|bind_argument) {, (literal|bind_argument)}])
  3. query ::= set([(literal|bind_argument) {, (literal|bind_argument)}])

Accessor

Supported:

  1. query ::= query dot attribute_name
  2. query ::= query dot relationship_name
  3. dot ::= . | ->

Collection Expression

All excepting:

  1. query ::= unique(query)

Select Expression

Supported:

query ::= select [ distinct] projection_attributes
from variable_declaration {, variable_declaration}
[where query]
[group by partition_atlributes]
[having query]
[order by sort_criterion {, sort_criterion}]
projection_attributes ::= projection {, projection}
proiection_attributes ::= *
projection ::= identifier dot attribute_name
variable_declaration ::= query as identifier
sort_criterion ::= query [ordering]
ordering ::= asc
ordering ::= desc

Set Expression

None.

Conversion

Supported:

  1. query ::= (class_name) query

FL and OQL

FL Transactions

OQL system does not use FL transactions at all. So you are free to decide must be or not OQL query execution within FL transaction.

OQL System Responsibilities
OQL System Responsibilities Collaborators
SQL mapping and class metadata FL atr-file: FL System (FLGen)
UML model: rosepetal and nsuml
OQL mapping tools: com.novosoft.fl.oql.flmap.FLMapping and com.novosoft.fl.oql.sql.mapping.SQLNamesImplFLUML.
SQL query execution:
getting of a connection to the database		 FL System:
ObjectManager.getReadOnlySQLConnection()
or user connection.
SQL query execution:
getting of entity references by its id and typeid		 FL System:
ObjectManager.getObject(Integer primaryKey,Integer typeId).
SQL query execution:
building objects with value interface		 OQL mapping tool (com.novosoft.fl.oql.sql.mapping.SQLNamesImplFLUML) and Java reflect API.