diff options
author | Frank Schoenheit [fs] <frank.schoenheit@oracle.com> | 2010-09-20 14:22:44 +0200 |
---|---|---|
committer | Frank Schoenheit [fs] <frank.schoenheit@oracle.com> | 2010-09-20 14:22:44 +0200 |
commit | b952a10ddcc3f832c5e129ed4e604eec6ae78939 (patch) | |
tree | 26a9c3d6a8cc532c61caaa58952277f1a25cd769 /wizards | |
parent | d70aabb7bda9dc0a4bc14e00e6080a12ef8f238c (diff) |
dba33j: #i114566# +getNormalizedStructuredFilter
Diffstat (limited to 'wizards')
-rw-r--r-- | wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java | 3 | ||||
-rw-r--r-- | wizards/com/sun/star/wizards/db/SQLQueryComposer.java | 82 | ||||
-rw-r--r-- | wizards/com/sun/star/wizards/ui/FilterComponent.java | 51 |
3 files changed, 101 insertions, 35 deletions
diff --git a/wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java b/wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java index 5c65d73794f0..e03323c6ebbc 100644 --- a/wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java +++ b/wizards/com/sun/star/wizards/db/DatabaseObjectWizard.java @@ -57,7 +57,8 @@ public abstract class DatabaseObjectWizard extends WizardDialog { try { - m_docUI.loadComponent( i_type, i_name, i_forEditing ); + if ( m_docUI != null ) + m_docUI.loadComponent( i_type, i_name, i_forEditing ); } catch ( IllegalArgumentException ex ) { diff --git a/wizards/com/sun/star/wizards/db/SQLQueryComposer.java b/wizards/com/sun/star/wizards/db/SQLQueryComposer.java index 0a52f6a3a7a9..235da2d7c04a 100644 --- a/wizards/com/sun/star/wizards/db/SQLQueryComposer.java +++ b/wizards/com/sun/star/wizards/db/SQLQueryComposer.java @@ -47,6 +47,7 @@ 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.*; @@ -60,7 +61,7 @@ public class SQLQueryComposer // String m_sFromClause; public XSingleSelectQueryAnalyzer m_xQueryAnalyzer; Vector composedCommandNames = new Vector(1); - private XSingleSelectQueryComposer m_xQueryComposer; + private XSingleSelectQueryComposer m_queryComposer; XMultiServiceFactory xMSF; boolean bincludeGrouping = true; @@ -72,7 +73,7 @@ public class SQLQueryComposer xMSF = (XMultiServiceFactory) UnoRuntime.queryInterface(XMultiServiceFactory.class, CurDBMetaData.DBConnection); final Object oQueryComposer = xMSF.createInstance("com.sun.star.sdb.SingleSelectQueryComposer"); m_xQueryAnalyzer = (XSingleSelectQueryAnalyzer) UnoRuntime.queryInterface(XSingleSelectQueryAnalyzer.class, oQueryComposer); - m_xQueryComposer = (XSingleSelectQueryComposer) UnoRuntime.queryInterface(XSingleSelectQueryComposer.class, m_xQueryAnalyzer); + m_queryComposer = (XSingleSelectQueryComposer) UnoRuntime.queryInterface(XSingleSelectQueryComposer.class, m_xQueryAnalyzer); XSQLQueryComposerFactory xSQLComposerFactory; xSQLComposerFactory = (XSQLQueryComposerFactory) UnoRuntime.queryInterface(XSQLQueryComposerFactory.class, CurDBMetaData.DBConnection); // /* XSQLQueryComposer */ xSQLQueryComposer = xSQLComposerFactory.createQueryComposer(); @@ -160,7 +161,7 @@ public class SQLQueryComposer { for (int i = 0; i < CurDBMetaData.getFilterConditions().length; i++) { - m_xQueryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions()); + m_queryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions()); } } catch (Exception exception) @@ -172,7 +173,7 @@ public class SQLQueryComposer public void prependSortingCriteria() throws SQLException { XIndexAccess xColumnIndexAccess = m_xQueryAnalyzer.getOrderColumns(); - m_xQueryComposer.setOrder(""); + m_queryComposer.setOrder(""); for (int i = 0; i < CurDBMetaData.getSortFieldNames().length; i++) { appendSortingCriterion(i, false); @@ -186,7 +187,7 @@ public class SQLQueryComposer if (JavaTools.FieldInTable(CurDBMetaData.getSortFieldNames(), sName) == -1) { boolean bascend = AnyConverter.toBoolean(xColumnPropertySet.getPropertyValue("IsAscending")); - m_xQueryComposer.appendOrderByColumn(xColumnPropertySet, bascend); + m_queryComposer.appendOrderByColumn(xColumnPropertySet, bascend); } } catch (Exception e) @@ -203,13 +204,13 @@ public class SQLQueryComposer String sSort = CurDBMetaData.getSortFieldNames()[_SortIndex][1]; boolean bascend = (sSort.equals("ASC")); - m_xQueryComposer.appendOrderByColumn(xColumn, bascend); + m_queryComposer.appendOrderByColumn(xColumn, bascend); } public void appendSortingcriteria(boolean _baddAliasFieldNames) throws SQLException { String sOrder = ""; - m_xQueryComposer.setOrder(""); + m_queryComposer.setOrder(""); for (int i = 0; i < CurDBMetaData.getSortFieldNames().length; i++) { String sSortValue = CurDBMetaData.getSortFieldNames()[i][0]; @@ -223,7 +224,7 @@ public class SQLQueryComposer } sOrder += CurDBMetaData.AggregateFieldNames[iAggregate][1] + "(" + CurDBMetaData.AggregateFieldNames[iAggregate][0] + ")"; sOrder += " " + CurDBMetaData.getSortFieldNames()[i][1]; - m_xQueryComposer.setOrder(sOrder); + m_queryComposer.setOrder(sOrder); } else { @@ -232,7 +233,7 @@ public class SQLQueryComposer sOrder = m_xQueryAnalyzer.getOrder(); } // just for debug! - sOrder = m_xQueryComposer.getOrder(); + sOrder = m_queryComposer.getOrder(); int dummy = 0; } @@ -241,7 +242,7 @@ public class SQLQueryComposer for (int i = 0; i < CurDBMetaData.GroupFieldNames.length; i++) { XPropertySet xColumn = CurDBMetaData.getColumnObjectByFieldName(CurDBMetaData.GroupFieldNames[i], _baddAliasFieldNames); - m_xQueryComposer.appendGroupByColumn(xColumn); + m_queryComposer.appendGroupByColumn(xColumn); } String s = m_xQueryAnalyzer.getQuery(); } @@ -309,7 +310,7 @@ public class SQLQueryComposer if (CurDBMetaData.getFilterConditions().length > 0) { CurDBMetaData.setFilterConditions(replaceConditionsByAlias(CurDBMetaData.getFilterConditions())); - m_xQueryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions()); + m_queryComposer.setStructuredFilter(CurDBMetaData.getFilterConditions()); } } } @@ -319,7 +320,7 @@ public class SQLQueryComposer appendGroupByColumns(_baddAliasFieldNames); if (CurDBMetaData.GroupByFilterConditions.length > 0) { - m_xQueryComposer.setStructuredHavingClause(CurDBMetaData.GroupByFilterConditions); + m_queryComposer.setStructuredHavingClause(CurDBMetaData.GroupByFilterConditions); } } appendSortingcriteria(_baddAliasFieldNames); @@ -426,8 +427,63 @@ public class SQLQueryComposer 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_xQueryComposer; + return m_queryComposer; } } diff --git a/wizards/com/sun/star/wizards/ui/FilterComponent.java b/wizards/com/sun/star/wizards/ui/FilterComponent.java index 4720b40ca55f..50b855ab3693 100644 --- a/wizards/com/sun/star/wizards/ui/FilterComponent.java +++ b/wizards/com/sun/star/wizards/ui/FilterComponent.java @@ -27,7 +27,6 @@ package com.sun.star.wizards.ui; // import java.util.Vector; -import com.sun.star.awt.ItemEvent; import com.sun.star.awt.TextEvent; import com.sun.star.awt.VclWindowPeerAttribute; import com.sun.star.awt.XControl; @@ -40,6 +39,7 @@ import com.sun.star.beans.XPropertySet; import com.sun.star.lang.EventObject; import com.sun.star.lang.IllegalArgumentException; import com.sun.star.lang.XMultiServiceFactory; +import com.sun.star.sdb.SQLFilterOperator; import com.sun.star.sdbc.DataType; import com.sun.star.uno.Any; import com.sun.star.uno.AnyConverter; @@ -53,6 +53,7 @@ import com.sun.star.wizards.common.Helper; import com.sun.star.wizards.common.JavaTools; import com.sun.star.wizards.db.FieldColumn; import com.sun.star.wizards.db.QueryMetaData; +import com.sun.star.wizards.db.SQLQueryComposer; import java.util.logging.Level; import java.util.logging.Logger; @@ -119,7 +120,6 @@ public class FilterComponent class ItemListenerImpl implements com.sun.star.awt.XItemListener { - public void itemStateChanged(com.sun.star.awt.ItemEvent EventObject) { int iKey = CurUnoDialog.getControlKey(EventObject.Source, CurUnoDialog.ControlList); @@ -237,6 +237,7 @@ public class FilterComponent int nFilterCount = getFilterCount(); if (nFilterCount > 0) { + final SQLQueryComposer composer = oQueryMetaData.getSQLQueryComposer(); try { final String serviceName = "com.sun.star.beans.PropertyBag"; @@ -247,29 +248,29 @@ public class FilterComponent column.addProperty("Value", (short)( PropertyAttribute.MAYBEVOID | PropertyAttribute.REMOVABLE ), null ); final XPropertySet columnSet = UnoRuntime.queryInterface(XPropertySet.class, column); - if ( oQueryMetaData.getSQLQueryComposer().getQuery().length() == 0) + if ( composer.getQuery().length() == 0) { - final String fromClause = oQueryMetaData.getSQLQueryComposer().getFromClause(); + final String fromClause = composer.getFromClause(); StringBuilder sql = new StringBuilder(); - sql.append(oQueryMetaData.getSQLQueryComposer().getSelectClause(true)); + sql.append(composer.getSelectClause(true)); sql.append(' '); sql.append(fromClause); - oQueryMetaData.getSQLQueryComposer().getQueryComposer().setElementaryQuery(sql.toString()); + composer.getQueryComposer().setElementaryQuery(sql.toString()); } - oQueryMetaData.getSQLQueryComposer().getQueryComposer().setStructuredFilter( new PropertyValue[][] {} ); + composer.getQueryComposer().setStructuredFilter( new PropertyValue[][] {} ); for (int i = 0; i < RowCount; i++) { - ControlRow CurControlRow = oControlRows[i]; - if (CurControlRow.isEnabled()) + ControlRow currentControlRow = oControlRows[i]; + if (currentControlRow.isEnabled()) { - if (CurControlRow.isConditionComplete()) + if (currentControlRow.isConditionComplete()) { - String sFieldName = CurControlRow.getSelectedFieldName(); - int nOperator = (int) CurControlRow.getSelectedOperator(); + String sFieldName = currentControlRow.getSelectedFieldName(); + int nOperator = (int) currentControlRow.getSelectedOperator(); FieldColumn aFieldColumn = oQueryMetaData.getFieldColumnByDisplayName(sFieldName); columnSet.setPropertyValue("Name", aFieldColumn.getFieldName()); columnSet.setPropertyValue("Type", aFieldColumn.getXColumnPropertySet().getPropertyValue("Type")); - Object value = CurControlRow.getValue(); + Object value = currentControlRow.getValue(); switch(aFieldColumn.getFieldType()) { case DataType.TIMESTAMP: @@ -278,14 +279,23 @@ public class FilterComponent break; } column.removeProperty( "Value" ); - column.addProperty("Value", (short)( PropertyAttribute.MAYBEVOID | PropertyAttribute.REMOVABLE ), value ); + final short operator = currentControlRow.getSelectedOperator(); + if ( ( operator == SQLFilterOperator.SQLNULL ) + || ( operator == SQLFilterOperator.NOT_SQLNULL ) + || AnyConverter.isVoid( value ) + ) + { + column.addProperty("Value", (short)( PropertyAttribute.MAYBEVOID | PropertyAttribute.REMOVABLE ), new String() ); + value = new Any( new Type( TypeClass.VOID ), null ); + } + else + column.addProperty("Value", (short)( PropertyAttribute.MAYBEVOID | PropertyAttribute.REMOVABLE ), value ); columnSet.setPropertyValue("Value", value); - oQueryMetaData.getSQLQueryComposer().getQueryComposer().appendFilterByColumn(columnSet, getfilterstate() == this.SOI_MATCHALL,nOperator); + composer.getQueryComposer().appendFilterByColumn(columnSet, getfilterstate() == this.SOI_MATCHALL,nOperator); } } } - final String test = oQueryMetaData.getSQLQueryComposer().getQueryComposer().getQuery(); - filterconditions = oQueryMetaData.getSQLQueryComposer().getQueryComposer().getStructuredFilter(); + filterconditions = composer.getNormalizedStructuredFilter(); int[] iduplicate = JavaTools.getDuplicateFieldIndex(filterconditions); if (iduplicate[0] != -1) { @@ -520,7 +530,6 @@ public class FilterComponent // ------------------------------------------------------------------------- final class ControlRow { - private final static int SOLSTFIELDNAME = 3; private final static int SOLSTOPERATOR = 4; private final static int SOTXTVALUE = 5; @@ -936,9 +945,9 @@ public class FilterComponent protected String getDateTimeString(boolean bgetDate) { - double dblValue = ((Double) getValue()).doubleValue(); - NumberFormatter oNumberFormatter = oQueryMetaData.getNumberFormatter(); - return oNumberFormatter.convertNumberToString(iDateTimeFormat, dblValue); + double dblValue = ((Double) getValue()).doubleValue(); + NumberFormatter oNumberFormatter = oQueryMetaData.getNumberFormatter(); + return oNumberFormatter.convertNumberToString(iDateTimeFormat, dblValue); } } } |