com.viaoa.ds.jdbc.query.QueryConverter Maven / Gradle / Ivy
Show all versions of oa-core Show documentation
/* Copyright 1999 Vince Via [email protected]
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
package com.viaoa.ds.jdbc.query;
import java.util.*;
import java.util.logging.Logger;
import com.viaoa.object.*;
import com.viaoa.util.OAArray;
import com.viaoa.util.OAString;
import com.viaoa.ds.query.*;
import com.viaoa.ds.jdbc.*;
import com.viaoa.ds.jdbc.db.*;
import com.viaoa.ds.jdbc.delegate.*;
/**
Used internally for queries/selects to convert OA object queries into SQL.
Uses JOINs or LEFT OUTER JOINS to connect super/sub classes and ONE type references.
Uses EXISTS for MANY type references.
Will work with any valid object path.
Allows OR, ||, AND, &&, (), =, !=, NULL, <, <=, >, >=
Converts to SQL using OAQueryTokenizer to parse object query into tokens, along with Database to Object Mapping.
@see #getUseDistinct to see if the query will require the use of the "DISTINCT" keyword.
*/
public class QueryConverter {
private static Logger LOG = Logger.getLogger(QueryConverter.class.getName());
private DBMetaData dbmd;
private Database database;
private boolean bEmpty; // if set, then the whereObject does not have any records to select
private Object selectObject; // the object to use in place of select, instead of using query
private Linkinfo root; // root of tree that keeps track of tables/objects that are needed for query. Method getJoins() uses this to build Join clause
private int counter; // used by LinkInfo
private boolean bUseLeftJoin; // all joins need to use "LEFT OUTER JOIN"
private boolean bUsingOR; // an "OR" is used in where clause. If other Joins will be needed then bUseLeftJoin will be set to true
private boolean bUseExists;
private String LB, RB;
private String extraWhere;
private boolean bUseDistinct;
public QueryConverter(OADataSourceJDBC ds) {
this.dbmd = ds.getDBMetaData();
this.database = ds.getDatabase();
reset();
}
public QueryConverter(Database database, DBMetaData dbmd) {
this.database = database;
this.dbmd = dbmd;
reset();
}
/**
* returns true if this query will only be able to select the Pk columns
* and the "Distinct" keyword should be used by the query to make sure that duplicates
* are not selected.
*/
public boolean getUseDistinct() {
return bUseDistinct;
}
protected void reset() {
this.LB = dbmd.leftBracket;
this.RB = dbmd.rightBracket;
this.bUseExists = dbmd.useExists;
this.arguments = null;
}
/** returns list of columns to select for clazz primary key column */
public String getPrimaryKeyColumns(Class clazz) {
Table table = database.getTable(clazz);
if (table == null) return "";
if (table.selectPKColumns != null) return table.selectPKColumns;
String strColumn = null;
DataAccessObject dao = table.getDataAccessObject();
if (dao != null) {
strColumn = dao.getPkeySelectColumns();
}
else {
String tableName = table.name.toUpperCase();
Column[] cols = table.getColumns();
for (int i=0; cols != null && i < cols.length; i++) {
if (!cols[i].primaryKey) continue;
if (strColumn == null) strColumn = "";
else strColumn += ", ";
strColumn += LB + tableName + RB + "." + LB + cols[i].columnName.toUpperCase()+RB;
}
}
table.selectPKColumns = strColumn;
return strColumn;
}
// only used if table.DataAccessObject is not used
public Column[] getSelectColumnArray(Class clazz) {
Table tableMain = database.getTable(clazz);
if (tableMain.selectColumnArray != null) return tableMain.selectColumnArray;
Class[] classes = getSelectClasses(clazz);
ArrayList al = null;
for (int ii=0; classes != null && ii < classes.length; ii++) {
Class c = classes[ii];
OAObjectInfo oi = OAObjectInfoDelegate.getObjectInfo(c);
Table table = database.getTable(c);
String tableName = table.name.toUpperCase();
// get all columns that map to properties
Column[] columns = table.getSelectColumns();
if (ii == 0 && classes.length == 1) return columns;
if (al == null) al = new ArrayList(25);
for (int i=0; columns != null && i < columns.length; i++) {
// 20121001 skip byte[]
Class cz = OAObjectInfoDelegate.getPropertyClass(oi, columns[i].propertyName);
if (cz.isArray()) {
cz = cz.getComponentType();
if (cz.equals(byte.class)) {
continue;
}
}
al.add(columns[i]);
}
}
Column[] cols = new Column[al.size()];
al.toArray(cols);
tableMain.selectColumnArray = cols;
return cols;
}
/** returns list of columns to select for clazz, not pkey or fkey columns, includes columns for superClasses */
public String getSelectColumns(Class clazz, boolean bDirty) {
Table tableMain = database.getTable(clazz);
if (!bDirty && tableMain.selectColumns != null) return tableMain.selectColumns;
Class[] classes = getSelectClasses(clazz);
String strColumn = "";
for (int ii=0; classes != null && ii < classes.length; ii++) {
Class c = classes[ii];
Table table = database.getTable(c);
DataAccessObject dao = table.getDataAccessObject();
if (!bDirty && dao != null) {
if (strColumn.length() > 0) strColumn += ", ";
strColumn += dao.getSelectColumns();
tableMain.selectColumns = strColumn;
}
else {
String tableName = table.name.toUpperCase();
// get all columns that map to properties
Column[] columns = table.getSelectColumns();
for (int i=0; columns != null && i < columns.length; i++) {
if (ii > 0 || i > 0) strColumn += ", ";
strColumn += LB + tableName + RB + "." + LB + columns[i].columnName.toUpperCase()+RB;
}
}
}
return strColumn;
}
public Class[] getSelectClasses(Class startClass) {
Table table = database.getTable(startClass);
if (table.selectClasses != null) return table.selectClasses;
Vector vec = new Vector(10,10);
getSelectSuperclasses(vec, startClass);
getSelectSubclasses(vec, startClass, false);
Class[] cs = new Class[vec.size()];
vec.copyInto(cs);
table.selectClasses = cs;
return cs;
}
private void getSelectSuperclasses(Vector vec, Class clazz) {
Table table = database.getTable(clazz);
if (table == null) return;
Class c = clazz.getSuperclass();
if (c != null && !(c.equals(OAObject.class)) ) getSelectSuperclasses(vec, c);
vec.addElement(clazz);
}
private void getSelectSubclasses(Vector vec, Class clazz, boolean bIncludeThis) {
Table table = database.getTable(clazz);
if (table == null) return;
if (bIncludeThis) vec.addElement(clazz);
Class[] subclasses = table.subclasses;
for (int i=0; subclasses != null && i < subclasses.length; i++) {
getSelectSubclasses(vec, subclasses[i], true);
}
}
// 20121013
public String convertForPreparedStatmentSql(Class selectClass, Object whereObject, String propertyFromWhereObject, String orderBy) {
reset();
Vector vecParam = new Vector(3,3);
String where = convertToWhere(selectClass, whereObject, propertyFromWhereObject, vecParam);
Object[] params = new Object[vecParam.size()];
vecParam.copyInto(params);
String sql = convertToSql(selectClass, where, params, orderBy, true, true);
return sql;
}
/**
propertyFromWhereObject name of link to use, from whereObject from Table(clazz).getLinks()[].propertyName = propertyFromWhereObject
*/
public String convertToSql(Class selectClass, Object whereObject, String extraWhere, Object[] args, String propertyFromWhereObject, String orderBy) {
reset();
Vector vecParam = new Vector(3,3);
String s = convertToWhere(selectClass, whereObject, propertyFromWhereObject, vecParam);
if (selectObject != null || bEmpty) return "";
if (!OAString.isEmpty(extraWhere)) {
if (!OAString.isEmpty(s)) s = "(" + s + ") AND ";
s += extraWhere;
}
for (int i=0; args != null && i > args.length; i++) {
vecParam.add(args[i]);
}
Object[] params = new Object[vecParam.size()];
vecParam.copyInto(params);
return convertToSql(selectClass, s, params, orderBy);
}
// 20121013
public String convertToPreparedStatementSql(Class selectClass, Object whereObject, String extraWhere, Object[] args, String propertyFromWhereObject, String orderBy) {
reset();
Vector vecParam = new Vector(3,3);
String s = convertToWhere(selectClass, whereObject, propertyFromWhereObject, vecParam);
if (selectObject != null || bEmpty) return "";
if (!OAString.isEmpty(extraWhere)) {
if (!OAString.isEmpty(s)) s = "(" + s + ") AND ";
s += extraWhere;
}
for (int i=0; args != null && i > args.length; i++) {
vecParam.add(args[i]);
}
Object[] params = new Object[vecParam.size()];
vecParam.copyInto(params);
String sql = convertToSql(selectClass, s, params, orderBy, true, true);
return sql;
}
/** used when calling convertToPreparedStatementSql */
private Object[] arguments;
public Object[] getArguments() {
return arguments;
}
private String convertToWhere(Class selectClass, Object whereObject, String propertyFromWhereObject, Vector vecParam) {
String s = null;
Table toTable;
Class c;
Class whereClass = whereObject.getClass();
for ( ; s == null && whereClass!=null && !(whereClass.equals(OAObject.class)); whereClass = whereClass.getSuperclass()) {
toTable = database.getTable(whereClass);
if (toTable == null) return ""; // select all
c = selectClass;
for ( ;s == null && c!=null && !(c.equals(OAObject.class)); c = c.getSuperclass()) {
Table fromTable = database.getTable(c);
if (fromTable == null) throw new RuntimeException("QueryConverter.convertToWhere() cant find link");
Link[] links = fromTable.getLinks();
s = getWhere(links, fromTable, toTable, whereObject, propertyFromWhereObject, vecParam);
}
}
// could not find it in direct links, now check any Link tables
whereClass = whereObject.getClass();
for (; s == null && whereClass!=null && !(whereClass.equals(OAObject.class)); whereClass = whereClass.getSuperclass()) {
toTable = database.getTable(whereClass);
c = selectClass;
for ( ;s == null && c!=null && !(c.equals(OAObject.class)); c = c.getSuperclass()) {
Table fromTable = database.getTable(c);
if (fromTable == null) continue;
Link[] links = fromTable.getLinks();
for (int i=0; s==null && links!=null && i < links.length; i++) {
if (links[i].toTable.bLink) {
s = getWhere(links[i].toTable.getLinks(), links[i].toTable, toTable, whereObject, propertyFromWhereObject, vecParam);
bUseExists = false; // dont use EXISTS, use JOIN instead
}
}
}
}
if (s == null) {
String test = "selectClass="+selectClass.getName()+" whereObject="+whereObject+" propertyFromWhereObject="+propertyFromWhereObject;
throw new RuntimeException("QueryConverter.convertToWhere() Can not find links between objects/database: "+test);
}
return s;
}
private String getWhere(Link[] links, Table fromTable, Table toTable, Object whereObject, String propertyFromWhereObject, Vector vecParams) {
// these are used to see if the object can be found without using select
bEmpty = false;
selectObject = null;
for (int i=0; links!=null && i < links.length; i++) {
Link link = links[i];
Table newFromTable = null;
if (link.toTable != toTable) {
if (link.toTable == null) continue;
if (!link.toTable.bLink) continue;
if (propertyFromWhereObject == null) continue;
if (!propertyFromWhereObject.equalsIgnoreCase(link.reversePropertyName)) continue;
// check link table links
Link[] linx = link.toTable.getLinks(); // will have 2 links
if (linx == null || linx.length != 2) continue;
newFromTable = link.toTable; // will need to be changed if it is using link table
if (linx[0].toTable == toTable && propertyFromWhereObject.equalsIgnoreCase(linx[0].reversePropertyName)) {
link = linx[0];
}
else {
if (linx[1].toTable == toTable && propertyFromWhereObject.equalsIgnoreCase(linx[1].reversePropertyName)) {
link = linx[1];
}
else continue;
}
}
if (propertyFromWhereObject != null && propertyFromWhereObject.length() > 0) {
if (!propertyFromWhereObject.equalsIgnoreCase(link.reversePropertyName)) continue;
}
if (newFromTable != null) fromTable = newFromTable;
Column[] fkeys = link.fkeys;
Column[] toFkeys = fromTable.getLinkToColumns(link, link.toTable);
if (fkeys == null || toFkeys == null || fkeys.length != toFkeys.length) {
throw new RuntimeException("different number of links between table "+fromTable.name+" and "+toTable.name );
}
String s = null;
OAObjectKey key;
if (toFkeys[0].primaryKey) {
key = OAObjectKeyDelegate.getKey((OAObject)whereObject);
}
else {
// 20090621
Object obj = OAObjectReflectDelegate.getRawReference((OAObject)whereObject, propertyFromWhereObject);
if (obj instanceof OAObjectKey) key = (OAObjectKey) obj;
else {
if (obj instanceof OAObject) key = OAObjectKeyDelegate.getKey((OAObject) obj);
else key = null;
}
/* was: bug: this could cause a inf loop for one2one links
Object obj = ((OAObject)whereObject).getProperty(propertyFromWhereObject);
key = OAObjectKeyDelegate.getKey((OAObject) obj);
*/
}
Object[] ids;
if (key != null) ids = key.getObjectIds();
else ids = null;
for (int j=0; fkeys!=null &&j 0) {
if (!propertyFromWhereObject.equalsIgnoreCase(links[i].reversePropertyName)) continue;
}
Column[] fkeys = links[i].fkeys;
Column[] toFkeys = fromTable.getLinkToColumns(links[i], toTable);
if (fkeys == null || toFkeys == null || fkeys.length != toFkeys.length) {
throw new RuntimeException("different number of links between table "+fromTable.name+" and "+toTable.name );
}
String s = null;
OAObjectKey key;
if (toFkeys[0].primaryKey) key = OAObjectKeyDelegate.getKey((OAObject)whereObject);
else {
// 20090621
Object obj = OAObjectReflectDelegate.getRawReference((OAObject)whereObject, propertyFromWhereObject);
if (obj instanceof OAObjectKey) key = (OAObjectKey) obj;
else {
if (obj instanceof OAObject) key = OAObjectKeyDelegate.getKey((OAObject) obj);
else key = null;
}
/* was: bug: this could cause a inf loop for one2one links
Object obj = ((OAObject)whereObject).getProperty(propertyFromWhereObject);
key = OAObjectKeyDelegate.getKey((OAObject) obj);
*/
}
Object[] ids;
if (key != null) ids = key.getObjectIds();
else ids = null;
for (int j=0; fkeys!=null &&j 0) {
orderBy = parseOrderBy(clazz, orderBy);
orderBy = " ORDER BY " + orderBy; // "ORDER BY" must be in caps, since OADataSourceJDBC might look for it
}
// create where clause
if (where != null && where.length() > 0) {
if (bUseExists) where = parseWhereUseExist(clazz, where, params, bUsingPreparedStatement);
else where = parseWhereUseJoin(clazz, where, params, bUsingPreparedStatement);
}
extraWhere = "";
String joins = getJoins();
if (extraWhere != null && extraWhere.length() > 0) {
where = extraWhere + ((where != null && where.length() > 0)?(" AND ("+where+")"):"");
}
if (where!=null && where.length()>0) where = " WHERE "+where;
else where = "";
String s = "FROM " + joins + where + ((orderBy!=null)?orderBy:"");
return s;
}
protected String parseOrderBy(Class clazz, String line) {
if (line == null) return null;
Table table = database.getTable(clazz);
String tableName = table.name.toUpperCase();
String newString="";
boolean bAllowDesc = false;
StringTokenizer st = new StringTokenizer(line," ,",true);
for (;st.hasMoreTokens();) {
String s = st.nextToken();
if (s.equals(",")) {
bAllowDesc = false;
}
else if (s.equals(" ")) {
}
else {
if (bAllowDesc && s.equalsIgnoreCase("desc")) {
bAllowDesc = false;
}
else {
// 2006/09/13
String funcName = null;
int pos = s.indexOf('(');
if (pos >= 0) {
funcName = s.substring(0, pos);
s = s.substring(pos+1);
pos = s.indexOf(')');
if (pos >= 0) s = s.substring(0, pos);
}
Vector vecLink = new Vector(5,5);
Column column = parseLink(vecLink, s); // populates vecLink with Linkinfos to match path in token.value
if (column == null) {
throw new RuntimeException("Cant use find column in ORDER clause: property \"" + s + "\" in query \""+line+"\"");
}
int xx = vecLink.size();
for (int ii=1; ii 0 && !colName.equalsIgnoreCase(column.columnName)) {
}
else {
colName = column.columnName;
funcName = dbmd.lowerCaseFunction;
}
}
else {
colName = column.columnName;
}
s = (funcName==null?"":(funcName + "(")) + LB+li.table.name.toUpperCase()+(li.number>0?li.number+"":"")+RB+"."+LB + colName.toUpperCase() + RB + (funcName==null?"":")");
bAllowDesc = true;
}
}
newString += s;
}
return newString;
}
protected String parseWhereUseJoin(Class clazz, String whereClause, Object[] params, boolean bUsingPreparedStatement) {
OAQueryTokenizer qa = new OAQueryTokenizer();
Vector vecToken = qa.convertToTokens(whereClause);
cleanTokens(vecToken, params);
// 20100821 if where column in joined table uses "is null", then need to also
// add "is not null" to "id" column, to offset records that dont exist
// ex: pet.exam.examitems.endDate = null - would return all with endDate=null and where no examItems exists for the pet
int[] nullColumns = new int[0];
int x = vecToken.size();
for (int i=1; i<(x-1); i++) {
OAQueryToken token = (OAQueryToken) vecToken.elementAt(i);
if (!"IS".equals(token.value)) continue;
token = (OAQueryToken) vecToken.elementAt(i+1);
if (!"NULL".equals(token.value)) continue;
token = (OAQueryToken) vecToken.elementAt(i-1);
if (token.value.indexOf('.') < 0) continue;
int colPos = (i - 1);
nullColumns = OAArray.add(nullColumns, colPos+1);
// found one
// need to add "(" and add "AND" for "is not null" column
// (
OAQueryToken t = new OAQueryToken();
t.value = "(";
t.type = OAQueryTokenType.SEPERATORBEGIN;
vecToken.insertElementAt(t, colPos);
// AND
t = new OAQueryToken();
t.type = OAQueryTokenType.AND;
t.value = "AND";
i += 3;
vecToken.insertElementAt(t, i++);
// COLUMN.PKEY
token = (OAQueryToken) vecToken.elementAt(colPos+1);
t = new OAQueryToken();
t.type = token.type;
t.value = token.value;
vecToken.insertElementAt(t, i++);
// NOTEQUAL
t = new OAQueryToken();
t.type = OAQueryTokenType.NOTEQUAL;
t.value = "IS NOT";
vecToken.insertElementAt(t, i++);
// NULL
t = new OAQueryToken();
t.type = OAQueryTokenType.NULL;
t.value = "NULL";
vecToken.insertElementAt(t, i++);
// )
t = new OAQueryToken();
t.type = OAQueryTokenType.SEPERATOREND;
t.value = ")";
vecToken.insertElementAt(t, i++);
}
String where = "";
Table table = null;
Column column = null;
Column origColumn = null;
String fullTextIndex = null;
boolean bLastColumnWasInFunction = false; // 20121120
int paramPos = 0;
x = vecToken.size();
for (int i=0; i 1 && column != null && column.primaryKey) {
Linkinfo li = (Linkinfo) vecLink.elementAt(xx-1);
if (!li.bMany) {
if (li.parent.bMany) {
/*
// many2many that uses a link table
int pos = 0; // currently only using one column, could be multipart id
for (int j=0; j" with select
}
}
Linkinfo li = (Linkinfo) vecLink.elementAt(xx-1);
// 2006/09/22 check for using case sensitive searches.
String colName;
// 20151206 check for fulltextindex
if (column.fullTextIndex && (dbmd.getDatabaseType() == DBMetaData.SQLSERVER)) {
colName = column.columnLowerName;
colName = LB+li.table.name.toUpperCase()+(li.number>0?li.number+"":"")+RB+"."+LB + colName + RB;
fullTextIndex = colName;
colName = null;
}
else if (dbmd.caseSensitive && column.type == java.sql.Types.VARCHAR && !column.primaryKey) {
colName = column.columnLowerName;
if (colName != null && colName.trim().length() > 0 && !colName.equalsIgnoreCase(column.columnName)) {
colName = colName.toUpperCase();
colName = LB+li.table.name.toUpperCase()+(li.number>0?li.number+"":"")+RB+"."+LB + colName + RB;
}
else {
colName = column.columnName.toUpperCase();
colName = LB+li.table.name.toUpperCase()+(li.number>0?li.number+"":"")+RB+"."+LB + colName + RB;
colName = dbmd.lowerCaseFunction + "(" + colName + ")";
}
// need to make search string is lower case
if (i+1 < x) {
OAQueryToken t = (OAQueryToken) vecToken.elementAt(i+1);
if (t.isOperator()) {
// find next var and make lowercase 2006/09/22
for (int j=i+2; j0?li.number+"":"")+RB+"."+LB + colName + RB;
}
s = colName;
}
else if (token.type == OAQueryTokenType.QUESTION) {
if (params == null || paramPos >= params.length) {
throw new RuntimeException("wrong number of params in query");
}
if (origColumn == null) throw new RuntimeException("column not found for parameter");
Object param = params[paramPos++];
if (origColumn != column) {
if (param instanceof OAObject) {
param = ((OAObject) param).getProperty(origColumn.propertyName);
}
}
// 20121013
if (fullTextIndex == null && bUsingPreparedStatement) s = "?";
else {
if (bLastColumnWasInFunction) {
bLastColumnWasInFunction = false;
if (param instanceof String) s = ConverterDelegate.convert(dbmd, origColumn, param);
else s = ConverterDelegate.convert(dbmd, null, param);
}
else s = ConverterDelegate.convert(dbmd, origColumn, param);
}
if (fullTextIndex != null) {
s = "CONTAINS(" + fullTextIndex + ", '" + s + "')";
fullTextIndex = null;
}
}
else if (token.type == OAQueryTokenType.STRINGSQ || token.type == OAQueryTokenType.STRINGDQ) {
if (column == null) throw new RuntimeException("column not found for parameter");
s = ConverterDelegate.convert(dbmd, column, token.value);
if (fullTextIndex != null) {
s = "CONTAINS(" + fullTextIndex + ", '" + s + "')";
fullTextIndex = null;
}
}
else if (token.type == OAQueryTokenType.NUMBER) {
if (column == null) throw new RuntimeException("column not found for parameter");
s = ConverterDelegate.convert(dbmd, column, token.value);
if (fullTextIndex != null) {
s = "CONTAINS(" + fullTextIndex + ", '" + s + "')";
fullTextIndex = null;
}
}
else if (token.type == OAQueryTokenType.FUNCTIONBEGIN) {
bLastColumnWasInFunction = true;
s = token.value;
}
else {
s = token.value;
if (fullTextIndex != null) s = null;
}
if (s != null && s.length() > 0) {
if (where.length() > 0) where += " ";
where += s;
}
}
if (where.indexOf(" NULL") >= 0) {
where = OAString.convert(where, " = NULL", " IS NULL");
where = OAString.convert(where, " != NULL", " IS NOT NULL");
where = OAString.convert(where, " <> NULL", " IS NOT NULL");
}
return where;
}
protected String parseWhereUseExist(Class clazz, String whereClause, Object[] params, boolean bUsingPreparedStatement) {
OAQueryTokenizer qa = new OAQueryTokenizer();
Vector vecToken = qa.convertToTokens(whereClause);
cleanTokens(vecToken, params);
int existCount = 0;
Vector vecLink = new Vector(5,5);
Vector vecPreLink = null;
String where = "";
OAQueryToken holdToken = null;
Column column = null;
Column origColumn = null;
int paramPos = 0;
int x = vecToken.size();
boolean bLastColumnWasInFunction = false; // 20121120
for (int i=0; i 1 && column != null && column.primaryKey) {
Linkinfo li = (Linkinfo) vecLink.elementAt(xx-1);
if (!li.bMany) {
// use fkey from previous Linkinfo
column = li.linkFromParent.fkeys[0];
li = (Linkinfo) vecLink.elementAt(xx-2);
xx--;
vecLink.removeElementAt(xx);
}
}
// see if path is same as prev path
if (vecPreLink != null) {
int xxx = vecPreLink.size();
if (xxx > 0 && xxx <= xx && column != null) {
for (int j=0; j 0) where += " ";
where += holdToken.value;
holdToken = null;
}
// set up variable name
Linkinfo li = ( (xx == 0) ? root : (Linkinfo) vecLink.elementAt(0) );
// check for MANY relationships within property path
Linkinfo prev;
Linkinfo liMany = null;
String exists = "";
for (int ii=1; ii 0) {
newVar += extraWhere + " AND ";
}
existCount++;
}
exists = LB + prev.table.name.toUpperCase() + (prev.number>0?prev.number+"":"") + RB;
exists += ".";
exists += LB + li.linkFromParent.fkeys[0].columnName + RB;
exists += " = ";
Linkinfo lix = (li.base == null) ? li : li.base; // base link is the "real" link to the parent link
exists += LB+ lix.table.name.toUpperCase() + (lix.number>0?li.number+"":"") + RB;
exists += ".";
exists += LB + lix.linkToParent.fkeys[0].columnName + RB;
liMany = li;
}
if (liMany != null) {
if (column == null) {
// ex: orders.orderItems = null
// orders.orderItems != null
// need to look ahead
OAQueryToken t = null;
if (i+1 < x) t = (OAQueryToken) vecToken.elementAt(i+1);
if (t != null && t.type == OAQueryTokenType.EQUAL) {
newVar += "NOT ";
vecLink.removeAllElements(); // so that it can not be shared with next path
}
}
extraWhere = ""; // built by getJoins for Oracle
boolean bHold = bUseLeftJoin;
bUseLeftJoin = false;
newVar += "EXISTS (SELECT * FROM " + getJoins(vecLink, liMany, null) + " WHERE " + exists;
bUseLeftJoin = bHold;
if (extraWhere != null && extraWhere.length() > 0) {
newVar += " AND " + extraWhere;
}
if (column != null) newVar += " AND ";
existCount++;
}
if (column != null) {
// 2006/09/22 check for using case sensitve searches.
String colName;
if (dbmd.caseSensitive && column.type == java.sql.Types.VARCHAR && !column.primaryKey) {
colName = column.columnLowerName;
if (colName != null && colName.trim().length() > 0 && !colName.equalsIgnoreCase(column.columnName)) {
colName = colName.toUpperCase();
colName = LB+li.table.name.toUpperCase()+(li.number>0?li.number+"":"")+RB+"."+LB + colName + RB;
}
else {
colName = column.columnName.toUpperCase();
colName = LB+li.table.name.toUpperCase()+(li.number>0?li.number+"":"")+RB+"."+LB + colName + RB;
colName = dbmd.lowerCaseFunction + "(" + colName + ")";
}
// need to make search string is lower case
if (i+1 < x) {
OAQueryToken t = (OAQueryToken) vecToken.elementAt(i+1);
if (t.isOperator()) {
// find next var and make lowercase 2006/09/22
for (int j=i+2; j0?li.number+"":"")+RB+"."+LB + colName + RB;
}
newVar += colName;
}
else {
// ignore "= null" or "!= null"
for (int j=0; j= params.length) {
throw new RuntimeException("wrong number of params in query");
}
if (origColumn == null) throw new RuntimeException("column not found for parameter");
Object param = params[paramPos++];
if (origColumn != column) {
if (param instanceof OAObject) {
param = ((OAObject) param).getProperty(origColumn.propertyName);
params[paramPos-1] = param;
}
}
// 20130113
if (bUsingPreparedStatement) newVar = "?";
else {
if (bLastColumnWasInFunction) {
bLastColumnWasInFunction = false;
if (param instanceof String) newVar = ConverterDelegate.convert(dbmd, origColumn, param);
else newVar = ConverterDelegate.convert(dbmd, null, param);
}
else newVar = ConverterDelegate.convert(dbmd, origColumn, param);
}
/*qqqqqqq was:
if (!bUsingPreparedStatement) {
newVar = ConverterDelegate.convert(dbmd, origColumn, param);
}
*/
}
else if (token.type == OAQueryTokenType.STRINGSQ || token.type == OAQueryTokenType.STRINGDQ) {
if (column == null) throw new RuntimeException("column not found for parameter");
newVar = ConverterDelegate.convert(dbmd, column, token.value);
}
else if (token.type == OAQueryTokenType.NUMBER) {
if (column == null) throw new RuntimeException("column not found for parameter");
newVar = ConverterDelegate.convert(dbmd, column, token.value);
}
else if (token.type == OAQueryTokenType.FUNCTIONBEGIN) {
bLastColumnWasInFunction = true;
newVar = token.value;
}
else {
newVar = token.value;
if (token.type == OAQueryTokenType.OR || token.type == OAQueryTokenType.AND) {
if (token.type == OAQueryTokenType.OR) bUsingOR = true;
holdToken = token;
newVar = null;
}
else if (token.type == OAQueryTokenType.SEPERATORBEGIN) {
for (int j=0; j 0) {
if (where.length() > 0) where += " ";
where += newVar;
}
}
for (int j=0; j 0) continue;
s = ""; // might not have a property name if a "none (1 w/o method)" to many
*/
}
if (!st.hasMoreElements()) {
Column c = linkinfo.findColumn(vec, s);
if (c != null) return c;
}
linkinfo = linkinfo.findLink(vec, s);
if (linkinfo == null) {
throw new RuntimeException("QueryConverter.parseLink() cant find link for \""+s+"\"");
}
if (!st.hasMoreElements()) {
vec.addElement(linkinfo);
// ex: dept.emps.orders = null
// 20110415 removed this, so that it will use the pkey column
// if (linkinfo.bMany) return null;
// ex: hubEmp.select("dept = null") -> "emp.dept.id = NULL"
Column[] pcols = linkinfo.table.getPrimaryKeyColumns();
return pcols[0];
}
}
}
protected void cleanTokens(Vector vec, Object[] params) {
OAQueryToken prevToken = null;
int questionCnt = 0;
int x = vec.size();
for (int i=0; i= params.length || params[questionCnt++] == null) {
if (prevToken.type == OAQueryTokenType.EQUAL) prevToken.value = "IS";
else if (prevToken.type == OAQueryTokenType.NOTEQUAL) prevToken.value = "IS NOT";
}
break;
case OAQueryTokenType.NULL:
token.value = "NULL";
if (prevToken.type == OAQueryTokenType.EQUAL) prevToken.value = "IS";
else if (prevToken.type == OAQueryTokenType.NOTEQUAL) prevToken.value = "IS NOT";
break;
case OAQueryTokenType.STRINGSQ:
for ( ;; ) {
// see if next token is also a single quote
if (i+1 == x) break;
OAQueryToken tok = (OAQueryToken) vec.elementAt(i+1);
if (tok.type != OAQueryTokenType.STRINGSQ) break;
token.value += "''" + tok.value;
vec.removeElementAt(i+1);
x--;
}
break;
case OAQueryTokenType.STRINGDQ:
break;
case OAQueryTokenType.STRINGESC:
token.value = "{" + token.value + "}";
break;
case OAQueryTokenType.NUMBER:
break;
case OAQueryTokenType.VARIABLE:
if (token.value.equalsIgnoreCase("IS")) {
if ((i+1) != x) {
OAQueryToken token2 = (OAQueryToken) vec.elementAt(i+1);
if (token2.type == OAQueryTokenType.NULL) token.type = OAQueryTokenType.EQUAL;
else {
if (token2.value.equalsIgnoreCase("NOT") && (i+2) != x) {
token2 = (OAQueryToken) vec.elementAt(i+2);
if (token2.type == OAQueryTokenType.NULL) token.type = OAQueryTokenType.NOTEQUAL;
}
}
}
}
else if (token.value.equalsIgnoreCase("TRUE")) {
token.type = OAQueryTokenType.TRUE;
}
else if (token.value.equalsIgnoreCase("FALSE")) {
token.type = OAQueryTokenType.FALSE;
}
break;
case OAQueryTokenType.EOF:
break;
default:
}
prevToken = token;
}
}
protected String getJoins() {
Table table = root.table;
String s = LB + table.name.toUpperCase() + RB;
s = getJoins(s, root, true, true);
return s;
}
// strJoin = "(" + strJoin + " {oj LEFT OUTER JOIN " + toTable.name + " ON ";
// {oj dept A left outer join emp B on A.deptNo = B.deptNo}
/** uses root Linkinfo to build JOIN clause */
private String getJoins(String strJoin, Linkinfo li, boolean bSuper, boolean bSub) {
Table table = li.table;
Column[] cols = table.getColumns();
if (bSuper && li.superLink != null && !li.bMany) {
if (li.superLink.bUsed) {
// li might not be a used link, but a superLink above it can be. If superLink is used, then need to
// join it with first subLink under it that is used
// find used sub link closest to li.superLink
Linkinfo lix = li;
for (Linkinfo l = li.base; l != null && l != li.superLink; l=l.superLink) {
if (l.bUsed) lix = l;
}
table = lix.table;
cols = table.getColumns();
String tname = LB + li.superLink.table.name.toUpperCase() + RB;
if (li.superLink.number > 0) {
tname += " " + LB + li.superLink.table.name.toUpperCase() + li.superLink.number + RB;
}
if (dbmd.databaseType == dbmd.ORACLE) {
strJoin += "," + tname;
}
else {
String s = "INNER JOIN";
if (bUseLeftJoin || lix.bUseLeftJoin) s = "LEFT OUTER JOIN"; // 2006/11/08
strJoin = "(" + dbmd.outerjoinStart + strJoin + " "+s+" " + tname + " ON ";
}
for (int i=0, j=0; i 0) {
extraWhere += " AND ";
}
if (j > 0 && dbmd.databaseType != dbmd.ORACLE) {
strJoin += " AND ";
}
j++;
tname = table.name.toUpperCase() + ((lix.number>0)?lix.number+"":"");
if (dbmd.databaseType == dbmd.ORACLE) {
extraWhere += LB+ tname + RB+"."+LB+ cols[i].columnName.toUpperCase() + RB;
extraWhere += " = ";
tname = li.superLink.table.name.toUpperCase() + ((li.superLink.number>0)?li.superLink.number+"":"");
extraWhere += LB+ tname + RB+"."+LB+ cols[i].columnName.toUpperCase() + RB +"(+)";
}
else {
strJoin += LB+ tname + RB+"."+LB+ cols[i].columnName.toUpperCase() + RB;
strJoin += " = ";
tname = li.superLink.table.name.toUpperCase() + ((li.superLink.number>0)?li.superLink.number+"":"");
strJoin += LB+ tname + RB+"."+LB+ cols[i].columnName.toUpperCase() + RB;
}
}
if (dbmd.databaseType != dbmd.ORACLE) {
strJoin += dbmd.outerjoinEnd+")";
}
}
// even if this "super" is not used, a super above it might be
strJoin = getJoins(strJoin, li.superLink, true, false);
}
if (bSub && li.subLinks != null) {
for (int i=0; i 0) {
tname += " " + LB + li.subLinks[i].table.name.toUpperCase() + li.subLinks[i].number + RB;
}
if (dbmd.databaseType == dbmd.ORACLE) {
strJoin += "," + tname;
}
else {
String s = "LEFT OUTER JOIN"; // always OUTER join subclasses, in case subclass does not exist
strJoin = "(" + dbmd.outerjoinStart + strJoin + " " + s + " " + tname + " ON ";
}
for (int ii=0, j=0; ii 0) {
extraWhere += " AND ";
}
if (j > 0 && dbmd.databaseType != dbmd.ORACLE) {
strJoin += " AND ";
}
j++;
tname = table.name.toUpperCase() + ((li.number>0)?li.number+"":"");
if (dbmd.databaseType == dbmd.ORACLE) {
extraWhere += LB+ tname + RB+"."+LB+ cols[i].columnName.toUpperCase() + RB;
extraWhere += " = ";
tname = li.superLink.table.name.toUpperCase() + ((li.number>0)?li.number+"":"");
extraWhere += LB+ tname + RB+"."+LB+ cols[i].columnName.toUpperCase() + RB +"(+)";
}
else {
strJoin += LB + tname + RB+"."+LB + cols[ii].columnName.toUpperCase() + RB;
strJoin += " = ";
tname = li.subLinks[i].table.name.toUpperCase() + ((li.number>0)?li.number+"":"");
strJoin += LB + tname + RB+"."+LB + cols[ii].columnName.toUpperCase() + RB;
}
}
if (dbmd.databaseType != dbmd.ORACLE) {
strJoin += dbmd.outerjoinEnd+")";
}
strJoin = getJoins(strJoin, li.subLinks[i], false, true);
}
}
// links ...
for (int i=0; li.links != null && i0?liStart.number+"":"") + RB;
int x = vec.size();
boolean b = false;
for (int i=0; i 0) tname += " " + LB + li.table.name.toUpperCase()+li.number + RB;
if (dbmd.databaseType == dbmd.ORACLE) {
strJoin += ", " + tname;
}
else {
String s = "INNER JOIN";
if (bUseLeftJoin || li.bUseLeftJoin) s = "LEFT OUTER JOIN";
strJoin = "(" + dbmd.outerjoinStart + strJoin + " "+s+" " + tname + " ON ";
}
Column[] cols = li.table.getColumns();
for (int ii=0, j=0; ii 0) {
extraWhere += " AND ";
}
if (j > 0 && dbmd.databaseType != dbmd.ORACLE) {
strJoin += " AND ";
}
j++;
tname = li.table.name.toUpperCase() + ((li.number>0)?li.number+"":"");
if (dbmd.databaseType == dbmd.ORACLE) {
extraWhere += LB+ tname + RB+"."+LB+ column.columnName.toUpperCase() + RB;
extraWhere += " = ";
tname = li.base.table.name.toUpperCase() + ((li.base.number>0)?li.base.number+"":"");
extraWhere += LB+ tname + RB+"."+LB+ column.columnName.toUpperCase() + RB +"(+)";
}
else {
strJoin += LB+ tname + RB+"."+LB+ column.columnName.toUpperCase() + RB;
strJoin += " = ";
tname = li.base.table.name.toUpperCase() + ((li.base.number>0)?li.base.number+"":"");
strJoin += LB+ tname + RB+"."+LB+ column.columnName.toUpperCase() + RB;
}
}
if (dbmd.databaseType != dbmd.ORACLE) {
strJoin += dbmd.outerjoinEnd+")";
}
}
if (li == liEnd) break;
}
return strJoin;
}
private String createLinkJoins(boolean bUseLeftJoin, String strJoin, Link link, Table fromTable, Table toTable, int fromTableNumber, int toTableNumber) {
Column[] fkeys = link.fkeys;
Column[] toFkeys = fromTable.getLinkToColumns(link, toTable);
if (fkeys.length != toFkeys.length) throw new RuntimeException("different number of links between tables");
String tname = LB + toTable.name.toUpperCase() + RB;
if (toTableNumber > 0) tname += " " + LB + toTable.name.toUpperCase() + toTableNumber + RB;
if (dbmd.databaseType == dbmd.ORACLE) {
strJoin += "," + tname;
}
else {
String s = "INNER JOIN";
if (this.bUseLeftJoin || bUseLeftJoin) s = "LEFT OUTER JOIN";
strJoin = "(" + dbmd.outerjoinStart + strJoin + " " + s + " " + tname + " ON ";
}
for (int i=0, j=0; i 0) {
extraWhere += " AND ";
}
if (j > 0 && dbmd.databaseType != dbmd.ORACLE) {
strJoin += " AND ";
}
j++;
tname = fromTable.name.toUpperCase() + ((fromTableNumber>0)?fromTableNumber+"":"");
if (dbmd.databaseType == dbmd.ORACLE) {
extraWhere += LB+ tname + RB+"."+LB+ fkeys[i].columnName.toUpperCase() + RB;
extraWhere += " = ";
tname = toTable.name.toUpperCase() + ((toTableNumber>0)?toTableNumber+"":"");
extraWhere += LB+tname + RB+"."+LB + toFkeys[i].columnName.toUpperCase() + RB +"(+)";
}
else {
strJoin += LB+ tname + RB+"."+LB+ fkeys[i].columnName.toUpperCase() + RB;
strJoin += " = ";
tname = toTable.name.toUpperCase() + ((toTableNumber>0)?toTableNumber+"":"");
strJoin += LB+tname + RB+"."+LB + toFkeys[i].columnName.toUpperCase() + RB;
}
}
if (dbmd.databaseType != dbmd.ORACLE) {
strJoin += dbmd.outerjoinEnd + ")";
}
return strJoin;
}
/**
Linkinfo represent a single link from a parent Linkinfo. Ex: Emp -> Dept
**/
class Linkinfo {
Table table;
Class clazz;
int number; // unique number
Link linkFromParent;
Link linkToParent;
boolean bLink; // link table
Linkinfo base; // base link, used for super/sub links
Linkinfo parent;
Linkinfo superLink;
Linkinfo[] subLinks;
Linkinfo[] links; // same order as Table.Links
boolean bMany;
boolean bUsed;
boolean bUseLeftJoin;
/** used internally by this class for setting nodes from root. */
private Linkinfo(Class c, int num, Linkinfo parent) {
this.clazz = c;
number = num;
this.parent = parent;
if (c != null) table = database.getTable(c);
}
/** root linkinfo */
public Linkinfo(Class c) {
this(c,0, null);
counter = 0;
// load all super and sub classes for root
loadSuper();
loadSub();
}
/** @param vec list of Linkinfos to find property. */
public Column findColumn(Vector vec, String propertyName) {
// first look in columns of this class and its superclass(es)
Column[] columns = table.getColumns();
// see is column is a match
for (int i=0; i < columns.length; i++) {
if ( columns[i].propertyName.equalsIgnoreCase(propertyName) || propertyName.equalsIgnoreCase(columns[i].columnName) ) {
vec.addElement(this);
return columns[i];
}
}
// check for property in superclass
if ((loadSuper() != null)) {
Column c = superLink.findColumn(vec, propertyName);
if (c != null) return c;
}
return null; // property not found
}
/** @param vec list of Linkinfos to find property. */
public Linkinfo findLink(Vector vec, String propertyName) {
loadLinks();
Link[] tabLinks = table.getLinks();
// see if link is a match
for (int i=0; i < links.length; i++) {
if ( tabLinks[i].propertyName.equalsIgnoreCase(propertyName) ) {
vec.addElement(this);
if (links[i].bLink) return links[i].findLink(vec, propertyName);
return links[i];
}
}
// check for property in superclass
loadSuper();
if (superLink != null) {
Linkinfo li = superLink.findLink(vec, propertyName);
if (li != null) return li;
}
return null; // not found
}
private void loadSub() {
if (subLinks == null) {
Class[] subclasses = table.subclasses;
this.subLinks = new Linkinfo[subclasses == null?0:subclasses.length];
for (int i=0; subclasses != null && i < subclasses.length; i++) {
subLinks[i] = new Linkinfo(subclasses[i], number, this.parent);
subLinks[i].linkFromParent = this.linkFromParent;
subLinks[i].linkToParent = this.linkToParent;
subLinks[i].bMany = this.bMany;
subLinks[i].base = (this.base == null) ? this : this.base;
if (number == 0) {
// load all for root object
subLinks[i].loadSub();
}
}
}
}
private Linkinfo loadSuper() {
if (superLink == null) {
Class c = clazz.getSuperclass();
if (c == null || c.equals(OAObject.class)) return null;
superLink = new Linkinfo(c, number, this.parent);
superLink.linkFromParent = this.linkFromParent;
superLink.linkToParent = this.linkToParent;
superLink.bMany = this.bMany;
superLink.base = (this.base == null) ? this : this.base;
if (number == 0) {
// load all for root object
superLink.loadSuper();
}
}
return superLink;
}
private void loadLinks() {
if (links != null || table == null) return;
Link[] tabLinks = table.getLinks();
links = new Linkinfo[tabLinks.length];
for (int i=0; i < links.length; i++) {
counter++;
Table toTable = tabLinks[i].toTable;
if (toTable == null) continue;
links[i] = new Linkinfo(toTable.clazz, counter, this);
if (toTable.bLink) {
links[i].table = toTable;
links[i].bLink = true;
links[i].bMany = true;
}
else {
// MANY ?
OAObjectInfo oi = OAObjectInfoDelegate.getOAObjectInfo(table.clazz);
OALinkInfo li = OAObjectInfoDelegate.getLinkInfo(oi, tabLinks[i].propertyName);
if (li != null && li.getType() == li.MANY) links[i].bMany = true;
}
links[i].linkFromParent = tabLinks[i];
links[i].linkToParent = tabLinks[i].getReverseLink();
}
}
}
}
/** Testing from VP:
// OASelect sel = new OASelect(Exam.class, "examItems.id = null", "");
// FROM (EXAM LEFT OUTER JOIN EXAMITEM EXAMITEM9 ON EXAM.ID = EXAMITEM9.EXAMID) WHERE ( EXAMITEM9.ID IS NULL AND EXAMITEM9.ID IS NOT NULL )
// OASelect sel = new OASelect(Exam.class, "examItems.id != null", "");
// FROM (EXAM LEFT OUTER JOIN EXAMITEM EXAMITEM9 ON EXAM.ID = EXAMITEM9.EXAMID) WHERE EXAMITEM9.ID IS NOT NULL
// OASelect sel = new OASelect(Exam.class, "examItems != null", "");
// FROM (EXAM LEFT OUTER JOIN EXAMITEM EXAMITEM9 ON EXAM.ID = EXAMITEM9.EXAMID) WHERE EXAMITEM9.ID IS NOT NULL
// OASelect sel = new OASelect(Exam.class, "examItems = null", "");
// FROM (EXAM LEFT OUTER JOIN EXAMITEM EXAMITEM9 ON EXAM.ID = EXAMITEM9.EXAMID) WHERE EXAMITEM9.ID IS NULL
// OASelect sel = new OASelect(Exam.class, "examItems.item.id != null", "");
// FROM (EXAM LEFT OUTER JOIN EXAMITEM EXAMITEM9 ON EXAM.ID = EXAMITEM9.EXAMID) WHERE EXAMITEM9.ITEMID IS NOT NULL
//OASelect sel = new OASelect(Exam.class, "examItems.item != null", "");
// FROM (EXAM LEFT OUTER JOIN EXAMITEM EXAMITEM9 ON EXAM.ID = EXAMITEM9.EXAMID) WHERE EXAMITEM9.ITEMID IS NOT NULL
// OASelect sel = new OASelect(Exam.class, "examItems.item.name != null", "");
// FROM ((EXAM LEFT OUTER JOIN EXAMITEM EXAMITEM9 ON EXAM.ID = EXAMITEM9.EXAMID) LEFT OUTER JOIN ITEM ITEM14 ON EXAMITEM9.ITEMID = ITEM14.ID) WHERE ITEM14.NAME IS NOT NULL
OASelect sel = new OASelect(Exam.class, "examItems.item.name == null", "");
// FROM ((EXAM LEFT OUTER JOIN EXAMITEM EXAMITEM9 ON EXAM.ID = EXAMITEM9.EXAMID) LEFT OUTER JOIN ITEM ITEM14 ON EXAMITEM9.ITEMID = ITEM14.ID) WHERE ( ITEM14.NAME IS NULL AND EXAMITEM9.ITEMID IS NOT NULL )
sel.select();
*/