SQL

You can choose SQL as a query language in Signsoft intelliBO too.

An SQLQuery can be defined as shown in the next example:

SQLQuery sqlQuery = QueryFactory.newSQLQuery(null);
sqlQuery.setClass(Person_DFM.class);
sqlQuery.setFilter("select * from test_tperson");

Query q = pm.newQuery(QueryLanguage.SQL, sqlQuery);
Collection col = (Collection)q.execute();

• The result set column name has to be the original name of the database column or has to be defined using a mapping in the descriptor.
• All primary key fields have to be part of the result set (candidate class).
• All fields of the default fetch group must be part of the result set (candidate class). If fields, that are part of the default fetch group, are not retrieved by the SQL query, those attributes are never retrieved when accessing them, because lazy loading is not supported for fields in the default fetch group.
• It is no mistake to specify that a query uses the cache ( setIgnoreCache(false) ). However, when dealing with SQL queries cache stability is not guaranteed. We strongly suggest not to use the cache in this case ( setIgnoreCache(true)).

You can define simple SQL queries without specifying the resultset in detail:

sqlQuery.setFilter("select * from tperson t0, " +
  "tcustomer t1 where (t0.FID = t1.FID)");
// use result-set field-names
sqlQuery.setResultSetTranslation(new HashMap());

If result set names are not unique, a manual mapping can be applied using an SQLQuery as shown below:

// define and execute query
SQLQuery sqlQuery = QueryFactory.newSQLQuery(null);
sqlQuery.setClass(Person.class);

Map rsFieldMap = new HashMap();
rsFieldMap.put("TPERSON.FFIRSTNAME", "my1");
rsFieldMap.put("TPERSON.FLASTNAME", "my3");
rsFieldMap.put("TPERSON.FID", "FID");
sqlQuery.setResultSetTranslation(rsFieldMap);
sqlQuery.setFilter(
  "select FFIRSTNAME as my1, FFIRSTDAYVALID as my2, FLASTNAME as my3, FID"
  + "from TPERSON");
Query query = pm.newQuery(QueryLanguage.SQL, sqlQuery);