| /************************************************************** |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you 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.sun.star.wizards.db; |
| |
| |
| // import com.sun.star.lang.IllegalArgumentException; |
| // import com.sun.star.lang.WrappedTargetException; |
| import com.sun.star.lang.XMultiServiceFactory; |
| import com.sun.star.beans.*; |
| // import com.sun.star.container.NoSuchElementException; |
| import com.sun.star.container.XIndexAccess; |
| import com.sun.star.container.XNameAccess; |
| import com.sun.star.sdbcx.XColumnsSupplier; |
| // import com.sun.star.sdb.XColumn; |
| import com.sun.star.sdb.XSingleSelectQueryComposer; |
| import com.sun.star.sdb.XSingleSelectQueryAnalyzer; |
| import com.sun.star.ui.dialogs.XExecutableDialog; |
| import com.sun.star.uno.AnyConverter; |
| import com.sun.star.uno.Exception; |
| import com.sun.star.uno.UnoRuntime; |
| import com.sun.star.sdbc.SQLException; |
| import com.sun.star.lang.XInitialization; |
| import com.sun.star.awt.XWindow; |
| import com.sun.star.sdb.SQLFilterOperator; |
| |
| import com.sun.star.wizards.common.*; |
| import java.util.ArrayList; |
| |
| public class SQLQueryComposer |
| { |
| |
| public XColumnsSupplier xColSuppl; |
| // XSQLQueryComposer xSQLQueryComposer; |
| QueryMetaData CurDBMetaData; |
| // String m_sSelectClause; |
| // String m_sFromClause; |
| public XSingleSelectQueryAnalyzer m_xQueryAnalyzer; |
| ArrayList<CommandName> composedCommandNames = new ArrayList<CommandName>(1); |
| private XSingleSelectQueryComposer m_queryComposer; |
| XMultiServiceFactory xMSF; |
| boolean bincludeGrouping = true; |
| |
| public SQLQueryComposer(QueryMetaData _CurDBMetaData) |
| { |
| try |
| { |
| this.CurDBMetaData = _CurDBMetaData; |
| xMSF = UnoRuntime.queryInterface(XMultiServiceFactory.class, CurDBMetaData.DBConnection); |
| final Object oQueryComposer = xMSF.createInstance("com.sun.star.sdb.SingleSelectQueryComposer"); |
| m_xQueryAnalyzer = UnoRuntime.queryInterface(XSingleSelectQueryAnalyzer.class, oQueryComposer); |
| m_queryComposer = UnoRuntime.queryInterface(XSingleSelectQueryComposer.class, m_xQueryAnalyzer); |
| } |
| catch (Exception exception) |
| { |
| exception.printStackTrace(System.out); |
| } |
| } |
| |
| private boolean addtoSelectClause(String DisplayFieldName) throws SQLException |
| { |
| return !(bincludeGrouping && CurDBMetaData.xDBMetaData.supportsGroupByUnrelated() && CurDBMetaData.GroupFieldNames != null && JavaTools.FieldInList(CurDBMetaData.GroupFieldNames, DisplayFieldName) > -1); |
| } |
| |
| public String getSelectClause(boolean _baddAliasFieldNames) throws SQLException |
| { |
| String sSelectBaseClause = "SELECT "; |
| String sSelectClause = sSelectBaseClause; |
| for (int i = 0; i < CurDBMetaData.FieldColumns.length; i++) |
| { |
| if (addtoSelectClause(CurDBMetaData.FieldColumns[i].getDisplayFieldName())) |
| { |
| int iAggregate = CurDBMetaData.getAggregateIndex(CurDBMetaData.FieldColumns[i].getDisplayFieldName()); |
| if (iAggregate > -1) |
| { |
| sSelectClause += CurDBMetaData.AggregateFieldNames[iAggregate][1] + "(" + getComposedAliasFieldName(CurDBMetaData.AggregateFieldNames[iAggregate][0]) + ")"; |
| if (_baddAliasFieldNames) |
| { |
| sSelectClause += getAliasFieldNameClause(CurDBMetaData.AggregateFieldNames[iAggregate][0]); |
| } |
| } |
| else |
| { |
| sSelectClause += getComposedAliasFieldName(CurDBMetaData.FieldColumns[i].getDisplayFieldName()); |
| if (_baddAliasFieldNames) |
| { |
| sSelectClause += getAliasFieldNameClause(CurDBMetaData.FieldColumns[i].getDisplayFieldName()); |
| } |
| } |
| sSelectClause += ", "; |
| } |
| } |
| // TODO: little bit unhandy version of remove the append 'comma' at the end |
| if (sSelectClause.equals(sSelectBaseClause)) |
| { |
| sSelectClause = sSelectClause.substring(0, sSelectClause.length() - 1); |
| } |
| else |
| { |
| sSelectClause = sSelectClause.substring(0, sSelectClause.length() - 2); |
| } |
| return sSelectClause; |
| } |
| |
| public String getAliasFieldNameClause(String _FieldName) |
| { |
| String FieldTitle = CurDBMetaData.getFieldTitle(_FieldName); |
| if (!FieldTitle.equals(_FieldName)) |
| { |
| return " AS " + CommandName.quoteName(FieldTitle, CurDBMetaData.getIdentifierQuote()); |
| } |
| else |
| { |
| return ""; |
| } |
| } |
| |
| public void appendFilterConditions() throws SQLException |
| { |
| try |
| { |
| for (int i = 0; i < CurDBMetaData.getFilterConditions().length; i++) |
| { |
| m_queryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions()); |
| } |
| } |
| catch (Exception exception) |
| { |
| exception.printStackTrace(System.out); |
| } |
| } |
| |
| public void prependSortingCriteria() throws SQLException |
| { |
| XIndexAccess xColumnIndexAccess = m_xQueryAnalyzer.getOrderColumns(); |
| m_queryComposer.setOrder(""); |
| for (int i = 0; i < CurDBMetaData.getSortFieldNames().length; i++) |
| { |
| appendSortingCriterion(i, false); |
| } |
| for (int i = 0; i < xColumnIndexAccess.getCount(); i++) |
| { |
| try |
| { |
| XPropertySet xColumnPropertySet = UnoRuntime.queryInterface(XPropertySet.class, xColumnIndexAccess.getByIndex(i)); |
| String sName = (String) xColumnPropertySet.getPropertyValue(PropertyNames.PROPERTY_NAME); |
| if (JavaTools.FieldInTable(CurDBMetaData.getSortFieldNames(), sName) == -1) |
| { |
| boolean bascend = AnyConverter.toBoolean(xColumnPropertySet.getPropertyValue("IsAscending")); |
| m_queryComposer.appendOrderByColumn(xColumnPropertySet, bascend); |
| } |
| } |
| catch (Exception e) |
| { |
| e.printStackTrace(System.out); |
| } |
| } |
| } |
| |
| private void appendSortingCriterion(int _SortIndex, boolean _baddAliasFieldNames) throws SQLException |
| { |
| String sSortValue = CurDBMetaData.getSortFieldNames()[_SortIndex][0]; |
| XPropertySet xColumn = CurDBMetaData.getColumnObjectByFieldName(sSortValue, _baddAliasFieldNames); |
| |
| String sSort = CurDBMetaData.getSortFieldNames()[_SortIndex][1]; |
| boolean bascend = (sSort.equals("ASC")); |
| m_queryComposer.appendOrderByColumn(xColumn, bascend); |
| } |
| |
| public void appendSortingcriteria(boolean _baddAliasFieldNames) throws SQLException |
| { |
| String sOrder = ""; |
| m_queryComposer.setOrder(""); |
| for (int i = 0; i < CurDBMetaData.getSortFieldNames().length; i++) |
| { |
| String sSortValue = CurDBMetaData.getSortFieldNames()[i][0]; |
| int iAggregate = CurDBMetaData.getAggregateIndex(sSortValue); |
| if (iAggregate > -1) |
| { |
| sOrder = m_xQueryAnalyzer.getOrder(); |
| if (sOrder.length() > 0) |
| { |
| sOrder += ", "; |
| } |
| sOrder += CurDBMetaData.AggregateFieldNames[iAggregate][1] + "(" + CurDBMetaData.AggregateFieldNames[iAggregate][0] + ")"; |
| sOrder += " " + CurDBMetaData.getSortFieldNames()[i][1]; |
| m_queryComposer.setOrder(sOrder); |
| } |
| else |
| { |
| appendSortingCriterion(i, _baddAliasFieldNames); |
| } |
| sOrder = m_xQueryAnalyzer.getOrder(); |
| } |
| // just for debug! |
| sOrder = m_queryComposer.getOrder(); |
| } |
| |
| public void appendGroupByColumns(boolean _baddAliasFieldNames) throws SQLException |
| { |
| for (int i = 0; i < CurDBMetaData.GroupFieldNames.length; i++) |
| { |
| XPropertySet xColumn = CurDBMetaData.getColumnObjectByFieldName(CurDBMetaData.GroupFieldNames[i], _baddAliasFieldNames); |
| m_queryComposer.appendGroupByColumn(xColumn); |
| } |
| } |
| |
| public void setDBMetaData(QueryMetaData _oDBMetaData) |
| { |
| this.CurDBMetaData = _oDBMetaData; |
| } |
| |
| private PropertyValue[][] replaceConditionsByAlias(PropertyValue _filterconditions[][]) |
| { |
| XColumnsSupplier columnSup = UnoRuntime.queryInterface(XColumnsSupplier.class, m_xQueryAnalyzer); |
| XNameAccess columns = columnSup.getColumns(); |
| for (int n = 0; n < _filterconditions.length; n++) |
| { |
| for (int m = 0; m < _filterconditions[n].length; m++) |
| { |
| // _filterconditions[n][m].Name = getComposedAliasFieldName(_filterconditions[n][m].Name); |
| final String aliasName = getComposedAliasFieldName(_filterconditions[n][m].Name); |
| if ( columns.hasByName(aliasName)) |
| _filterconditions[n][m].Name = aliasName; |
| } |
| } |
| return _filterconditions; |
| } |
| |
| public String getQuery() |
| { |
| return m_xQueryAnalyzer.getQuery(); |
| } |
| |
| public StringBuilder getFromClause() |
| { |
| StringBuilder sFromClause = new StringBuilder("FROM"); |
| composedCommandNames.clear(); |
| String[] sCommandNames = CurDBMetaData.getIncludedCommandNames(); |
| for (int i = 0; i < sCommandNames.length; i++) |
| { |
| CommandName curCommandName = new CommandName(CurDBMetaData, sCommandNames[i]); //(setComposedCommandName) |
| curCommandName.setAliasName(getuniqueAliasName(curCommandName.getTableName())); |
| sFromClause.append(" ").append(curCommandName.getComposedName()).append(" ").append(quoteName(curCommandName.getAliasName())); |
| if (i < sCommandNames.length - 1) |
| { |
| sFromClause.append(", "); |
| } |
| // fill composedCommandNames |
| composedCommandNames.add(curCommandName); |
| } |
| return sFromClause; |
| } |
| |
| public boolean setQueryCommand(XWindow _xParentWindow, boolean _bincludeGrouping, boolean _baddAliasFieldNames) |
| { |
| return setQueryCommand(_xParentWindow, _bincludeGrouping, _baddAliasFieldNames, true); |
| } |
| |
| public boolean setQueryCommand(XWindow _xParentWindow, boolean _bincludeGrouping, boolean _baddAliasFieldNames, boolean addQuery) |
| { |
| try |
| { |
| bincludeGrouping = _bincludeGrouping; |
| if (addQuery) |
| { |
| StringBuilder fromClause = getFromClause(); |
| String sSelectClause = getSelectClause(_baddAliasFieldNames); |
| StringBuilder queryclause = new StringBuilder(sSelectClause).append(" ").append(fromClause); |
| m_xQueryAnalyzer.setQuery(queryclause.toString()); |
| if (CurDBMetaData.getFilterConditions() != null && CurDBMetaData.getFilterConditions().length > 0) |
| { |
| CurDBMetaData.setFilterConditions(replaceConditionsByAlias(CurDBMetaData.getFilterConditions())); |
| m_queryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions()); |
| } |
| } |
| if (_bincludeGrouping) |
| { |
| appendGroupByColumns(_baddAliasFieldNames); |
| if (CurDBMetaData.GroupByFilterConditions.length > 0) |
| { |
| m_queryComposer.setStructuredHavingClause(CurDBMetaData.GroupByFilterConditions); |
| } |
| } |
| appendSortingcriteria(_baddAliasFieldNames); |
| |
| return true; |
| } |
| catch (Exception exception) |
| { |
| exception.printStackTrace(System.out); |
| displaySQLErrorDialog(exception, _xParentWindow); |
| return false; |
| } |
| } |
| |
| private String getComposedAliasFieldName(String _fieldname) |
| { |
| FieldColumn CurFieldColumn = CurDBMetaData.getFieldColumnByDisplayName(_fieldname); |
| CommandName curComposedCommandName = getComposedCommandByDisplayName(CurFieldColumn.getCommandName()); |
| if (curComposedCommandName == null) |
| { |
| return _fieldname; |
| } |
| String curAliasName = curComposedCommandName.getAliasName(); |
| return quoteName(curAliasName) + "." + quoteName(CurFieldColumn.getFieldName()); |
| } |
| |
| private CommandName getComposedCommandByAliasName(String _AliasName) |
| { |
| if (composedCommandNames != null) |
| { |
| for (CommandName commandName : composedCommandNames) |
| { |
| if (commandName.getAliasName().equals(_AliasName)) |
| { |
| return commandName; |
| } |
| } |
| } |
| return null; |
| } |
| |
| public CommandName getComposedCommandByDisplayName(String _DisplayName) |
| { |
| if (composedCommandNames != null) |
| { |
| for (CommandName commandName : composedCommandNames) |
| { |
| if (commandName.getDisplayName().equals(_DisplayName)) |
| { |
| return commandName; |
| } |
| } |
| } |
| return null; |
| } |
| |
| public String getuniqueAliasName(String _TableName) |
| { |
| int a = 0; |
| String AliasName = ""; |
| boolean bAliasNameexists = true; |
| String locAliasName = _TableName; |
| while (bAliasNameexists) |
| { |
| bAliasNameexists = (getComposedCommandByAliasName(locAliasName) != null); |
| if (bAliasNameexists) |
| { |
| a++; |
| locAliasName = _TableName + "_" + String.valueOf(a); |
| } |
| else |
| { |
| AliasName = locAliasName; |
| } |
| } |
| return AliasName; |
| } |
| |
| private String quoteName(String _sname) |
| { |
| return CommandName.quoteName(_sname, CurDBMetaData.getIdentifierQuote()); |
| } |
| |
| public void displaySQLErrorDialog(Exception _exception, XWindow _xParentWindow) |
| { |
| try |
| { |
| Object oErrorDialog = CurDBMetaData.xMSF.createInstance("com.sun.star.sdb.ErrorMessageDialog"); |
| XInitialization xInitialize = UnoRuntime.queryInterface(XInitialization.class, oErrorDialog); |
| XExecutableDialog xExecute = UnoRuntime.queryInterface(XExecutableDialog.class, oErrorDialog); |
| PropertyValue[] rDispatchArguments = new PropertyValue[3]; |
| rDispatchArguments[0] = Properties.createProperty(PropertyNames.PROPERTY_TITLE, Configuration.getProductName(CurDBMetaData.xMSF) + " Base"); |
| rDispatchArguments[1] = Properties.createProperty("ParentWindow", _xParentWindow); |
| rDispatchArguments[2] = Properties.createProperty("SQLException", _exception); |
| xInitialize.initialize(rDispatchArguments); |
| xExecute.execute(); |
| //TODO dispose??? |
| } |
| catch (Exception typeexception) |
| { |
| typeexception.printStackTrace(System.out); |
| } |
| } |
| |
| /** |
| * retrieves a normalized structured filter |
| * |
| * <p>XSingleSelectQueryComposer.getStructuredFilter has a strange habit of returning the predicate (equal, not equal, etc) |
| * effectively twice: Once as SQLFilterOperator, and once in the value. That is, if you have a term "column <> 3", then |
| * you'll get an SQLFilterOperator.NOT_EQUAL (which is fine), <strong>and</strong> the textual value of the condition |
| * will read "<> 3". The latter is strange enough, but even more strange is that this behavior is not even consistent: |
| * for SQLFilterOperator.EQUAL, the "=" sign is not include in the textual value.</p> |
| * |
| * <p>To abstract from this weirdness, use this function here, which strips the unwanted tokens from the textual value |
| * representation.</p> |
| */ |
| public PropertyValue[][] getNormalizedStructuredFilter() |
| { |
| final PropertyValue[][] structuredFilter = m_queryComposer.getStructuredFilter(); |
| for (int i = 0; i < structuredFilter.length; ++i) |
| { |
| for (int j = 0; j < structuredFilter[i].length; ++j) |
| { |
| if (!(structuredFilter[i][j].Value instanceof String)) |
| { |
| continue; |
| } |
| final StringBuffer textualValue = new StringBuffer((String) structuredFilter[i][j].Value); |
| switch (structuredFilter[i][j].Handle) |
| { |
| case SQLFilterOperator.EQUAL: |
| break; |
| case SQLFilterOperator.NOT_EQUAL: |
| case SQLFilterOperator.LESS_EQUAL: |
| case SQLFilterOperator.GREATER_EQUAL: |
| textualValue.delete(0, 2); |
| break; |
| case SQLFilterOperator.LESS: |
| case SQLFilterOperator.GREATER: |
| textualValue.delete(0, 1); |
| break; |
| case SQLFilterOperator.NOT_LIKE: |
| textualValue.delete(0, 8); |
| break; |
| case SQLFilterOperator.LIKE: |
| textualValue.delete(0, 4); |
| break; |
| case SQLFilterOperator.SQLNULL: |
| textualValue.delete(0, 7); |
| break; |
| case SQLFilterOperator.NOT_SQLNULL: |
| textualValue.delete(0, 11); |
| break; |
| } |
| structuredFilter[i][j].Value = textualValue.toString().trim(); |
| } |
| } |
| return structuredFilter; |
| } |
| |
| public XSingleSelectQueryComposer getQueryComposer() |
| { |
| return m_queryComposer; |
| } |
| } |
