svn-gvsig-desktop / trunk / org.gvsig.desktop / org.gvsig.desktop.compat.cdc / org.gvsig.fmap.dal / org.gvsig.fmap.dal.db / org.gvsig.fmap.dal.db.jdbc / src / main / java / org / gvsig / fmap / dal / store / jdbc2 / spi / operations / ResultSetForSetProviderOperation.java @ 45534
History | View | Annotate | Download (13.5 KB)
1 | 45065 | jjdelcerro | /**
|
---|---|---|---|
2 | * gvSIG. Desktop Geographic Information System.
|
||
3 | *
|
||
4 | * Copyright (C) 2007-2020 gvSIG Association.
|
||
5 | *
|
||
6 | * This program is free software; you can redistribute it and/or
|
||
7 | * modify it under the terms of the GNU General Public License
|
||
8 | * as published by the Free Software Foundation; either version 3
|
||
9 | * of the License, or (at your option) any later version.
|
||
10 | *
|
||
11 | * This program is distributed in the hope that it will be useful,
|
||
12 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
||
13 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
||
14 | * GNU General Public License for more details.
|
||
15 | *
|
||
16 | * You should have received a copy of the GNU General Public License
|
||
17 | * along with this program; if not, write to the Free Software
|
||
18 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
|
||
19 | * MA 02110-1301, USA.
|
||
20 | *
|
||
21 | * For any additional information, do not hesitate to contact us
|
||
22 | * at info AT gvsig.com, or visit our website www.gvsig.com.
|
||
23 | */
|
||
24 | 43020 | jjdelcerro | package org.gvsig.fmap.dal.store.jdbc2.spi.operations; |
25 | |||
26 | import java.sql.Connection; |
||
27 | import java.util.ArrayList; |
||
28 | import java.util.List; |
||
29 | 45166 | omartinez | import java.util.Map; |
30 | 43358 | jjdelcerro | import org.apache.commons.lang3.ArrayUtils; |
31 | 43020 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
32 | 45155 | omartinez | import org.gvsig.expressionevaluator.Code; |
33 | 45162 | omartinez | import org.gvsig.expressionevaluator.Expression; |
34 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder; |
35 | 44727 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionUtils; |
36 | 44682 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder; |
37 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.exception.DataException; |
38 | 45162 | omartinez | import org.gvsig.fmap.dal.expressionevaluator.FeatureAttributeEmulatorExpression; |
39 | import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
||
40 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
41 | import org.gvsig.fmap.dal.feature.FeatureQuery; |
||
42 | import org.gvsig.fmap.dal.feature.FeatureQueryOrder; |
||
43 | 43026 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureQueryOrder.FeatureQueryOrderMember; |
44 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureType; |
45 | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
||
46 | 44058 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
47 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.ResulSetControler.ResultSetEntry; |
48 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
49 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_FEATURE_TYPE; |
50 | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
||
51 | 43020 | jjdelcerro | import org.gvsig.fmap.geom.DataTypes; |
52 | import org.gvsig.tools.evaluator.Evaluator; |
||
53 | |||
54 | public class ResultSetForSetProviderOperation extends AbstractConnectionOperation { |
||
55 | 44058 | jjdelcerro | private final TableReference table; |
56 | 43020 | jjdelcerro | private final String baseFilter; |
57 | private final String baseOrder; |
||
58 | private final FeatureType storeType; |
||
59 | private final FeatureType setType; |
||
60 | private final FeatureQuery query; |
||
61 | private final long limit; |
||
62 | private final long offset; |
||
63 | private final int fetchSize; |
||
64 | |||
65 | public ResultSetForSetProviderOperation(
|
||
66 | JDBCHelper helper, |
||
67 | 44058 | jjdelcerro | TableReference table, |
68 | 43020 | jjdelcerro | String baseFilter,
|
69 | String baseOrder,
|
||
70 | FeatureQuery query, |
||
71 | FeatureType storeType, |
||
72 | FeatureType setType, |
||
73 | long limit,
|
||
74 | long offset,
|
||
75 | int fetchSize
|
||
76 | ) { |
||
77 | super(helper);
|
||
78 | this.table = table;
|
||
79 | this.baseFilter = baseFilter;
|
||
80 | this.baseOrder = baseOrder;
|
||
81 | this.storeType = storeType;
|
||
82 | this.setType = setType;
|
||
83 | this.query = query;
|
||
84 | this.limit = limit;
|
||
85 | this.offset = offset;
|
||
86 | this.fetchSize = fetchSize;
|
||
87 | } |
||
88 | |||
89 | @Override
|
||
90 | 43377 | jjdelcerro | protected Object perform_operation() throws Exception { |
91 | 44678 | jjdelcerro | ResultSetEntry rs = createResultSet(); |
92 | 43020 | jjdelcerro | return rs;
|
93 | } |
||
94 | 43377 | jjdelcerro | |
95 | @Override
|
||
96 | public Object perform(Connection conn) throws DataException { |
||
97 | 44678 | jjdelcerro | throw new UnsupportedOperationException("Not supported yet."); |
98 | 43377 | jjdelcerro | } |
99 | 44678 | jjdelcerro | |
100 | public String getSQL() { |
||
101 | 43358 | jjdelcerro | List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
102 | 43020 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
103 | 44682 | jjdelcerro | String sql = this.getSQL(sqlbuilder, columns, null); |
104 | 44678 | jjdelcerro | return sql;
|
105 | } |
||
106 | |||
107 | 44682 | jjdelcerro | public String getSQL( |
108 | JDBCSQLBuilderBase sqlbuilder, |
||
109 | List<FeatureAttributeDescriptor> columns,
|
||
110 | List<String> extraColumnNames |
||
111 | ) { |
||
112 | 44678 | jjdelcerro | double tolerance = -1 ; //query.getScale(); |
113 | 44198 | jjdelcerro | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
114 | 44682 | jjdelcerro | SelectBuilder select = sqlbuilder.select(); |
115 | 43020 | jjdelcerro | |
116 | List<String> primaryKeys = new ArrayList<>(); |
||
117 | for(FeatureAttributeDescriptor attr : storeType.getPrimaryKey() ) {
|
||
118 | primaryKeys.add(attr.getName()); |
||
119 | } |
||
120 | 43706 | jjdelcerro | List<String> forcedColumns = new ArrayList<>(primaryKeys); |
121 | |||
122 | 43358 | jjdelcerro | String[] constantsAttributeNames = null; |
123 | if(query !=null && query.hasConstantsAttributeNames() ) { |
||
124 | constantsAttributeNames = query.getConstantsAttributeNames(); |
||
125 | } |
||
126 | 43020 | jjdelcerro | for(FeatureAttributeDescriptor attr : setType ) {
|
127 | 44324 | jjdelcerro | if( attr.isComputed() ) {
|
128 | continue;
|
||
129 | } |
||
130 | 43358 | jjdelcerro | if( ArrayUtils.contains(constantsAttributeNames, attr.getName()) ) {
|
131 | continue;
|
||
132 | } |
||
133 | 43020 | jjdelcerro | if( attr.isPrimaryKey() ) {
|
134 | 43706 | jjdelcerro | forcedColumns.remove(attr.getName()); |
135 | 43020 | jjdelcerro | } |
136 | 44712 | jjdelcerro | if( query !=null && query.hasGroupByColumns()) { |
137 | 45162 | omartinez | String aggregate = query.getAggregate(this.table.getTable(), attr.getName()); |
138 | 44727 | jjdelcerro | if( this.query.isAGroupByColumn(attr.getName()) ) { |
139 | select.column().name(attr.getName()); |
||
140 | } else if( aggregate == null ) { |
||
141 | select.column().value(expbuilder.constant(null)).as(attr.getName());
|
||
142 | 44712 | jjdelcerro | } else {
|
143 | 44727 | jjdelcerro | select.column() |
144 | .value(ExpressionUtils.compile(aggregate).toValue(expbuilder)) |
||
145 | .as(attr.getName()); |
||
146 | 44712 | jjdelcerro | } |
147 | 43020 | jjdelcerro | } else {
|
148 | 44712 | jjdelcerro | if( attr.getType() == DataTypes.GEOMETRY ) {
|
149 | select.column().name(attr.getName()).as_geometry(); |
||
150 | // if( tolerance<=0 || !sqlbuilder.getConfig().has_functionality(Config.ST_Simplify)) {
|
||
151 | // select.column().name(attr.getName()).as_geometry();
|
||
152 | // } else {
|
||
153 | // select.column().value(
|
||
154 | // sqlbuilder.ST_Simplify(
|
||
155 | // sqlbuilder.column(attr.getName()),
|
||
156 | // sqlbuilder.constant(tolerance)
|
||
157 | // )
|
||
158 | // ).as_geometry();
|
||
159 | // }
|
||
160 | } else {
|
||
161 | select.column().name(attr.getName()); |
||
162 | } |
||
163 | 43020 | jjdelcerro | } |
164 | 44727 | jjdelcerro | columns.add(attr); |
165 | 43020 | jjdelcerro | } |
166 | 45155 | omartinez | |
167 | if( query !=null && query.hasGroupByColumns() ) { |
||
168 | 45166 | omartinez | for (Map.Entry<String, String> entry : query.getAggregateFunctions().entrySet()) { |
169 | EditableFeatureAttributeDescriptor attr = query.getExtraColumn().get(entry.getKey()); |
||
170 | if( attr!=null ) { |
||
171 | Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();
|
||
172 | ExpressionBuilder.Function aggregateExp = expbuilder.function(entry.getValue(),exp.getCode().toValue()); |
||
173 | if (!select.has_column(attr.getName())) {
|
||
174 | select.column().value(aggregateExp).as(attr.getName()); |
||
175 | } |
||
176 | if (!extraColumnNames.contains(attr.getName())) {
|
||
177 | extraColumnNames.add(attr.getName()); |
||
178 | } |
||
179 | } |
||
180 | } |
||
181 | 44712 | jjdelcerro | for(String attrName : query.getGroupByColumns() ) { |
182 | 45162 | omartinez | if (this.setType.getExtraColumns().get(attrName) != null) { |
183 | EditableFeatureAttributeDescriptor attr = this.setType.getExtraColumns().get(attrName);
|
||
184 | 45305 | omartinez | select.group_by(expbuilder.column(attrName)); |
185 | 45162 | omartinez | Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();
|
186 | 45166 | omartinez | if (!select.has_column(attrName)) {
|
187 | select.column().value(exp.getCode().toValue()).as(attrName); |
||
188 | } |
||
189 | if (!extraColumnNames.contains(attr.getName())) {
|
||
190 | extraColumnNames.add(attrName); |
||
191 | } |
||
192 | 45162 | omartinez | } else if (setType.get(attrName) == null) { |
193 | 45155 | omartinez | try {
|
194 | 45162 | omartinez | Code code = ExpressionUtils.compile(attrName); |
195 | select.group_by(code.toValue()); |
||
196 | 45155 | omartinez | } catch (Exception ex) { |
197 | throw new RuntimeException("Not able to create column by expression in groupby query", ex); |
||
198 | } |
||
199 | } else {
|
||
200 | 45294 | omartinez | ExpressionBuilder.Function atrcolumn = expbuilder.getattr(this.table.getTable(), attrName);
|
201 | select.group_by(atrcolumn); |
||
202 | 45155 | omartinez | } |
203 | } |
||
204 | 44854 | omartinez | } else {
|
205 | for(String attrName : forcedColumns ) { |
||
206 | select.column().name(attrName); |
||
207 | columns.add(setType.getAttributeDescriptor(attrName)); |
||
208 | } |
||
209 | 43020 | jjdelcerro | } |
210 | 45155 | omartinez | |
211 | 44682 | jjdelcerro | select.from().table() |
212 | 44058 | jjdelcerro | .database(this.table.getDatabase())
|
213 | .schema(this.table.getSchema())
|
||
214 | .name(this.table.getTable());
|
||
215 | 44682 | jjdelcerro | select.from().subquery(this.table.getSubquery());
|
216 | 43020 | jjdelcerro | |
217 | 44058 | jjdelcerro | Evaluator filter = query==null? null:query.getFilter(); |
218 | 43020 | jjdelcerro | if( filter != null ) { |
219 | String sqlfilter = filter.getSQL();
|
||
220 | if( ! StringUtils.isEmpty(sqlfilter) ) {
|
||
221 | 44198 | jjdelcerro | if( this.helper.supportFilter(this.storeType, filter) ) { |
222 | 44682 | jjdelcerro | select.where().set(expbuilder.toValue(sqlfilter)); |
223 | 44198 | jjdelcerro | } |
224 | 43020 | jjdelcerro | } |
225 | } |
||
226 | if( ! StringUtils.isEmpty(baseFilter) ) {
|
||
227 | 44682 | jjdelcerro | select.where().and(expbuilder.toValue(baseFilter)); |
228 | 43020 | jjdelcerro | } |
229 | |||
230 | 44058 | jjdelcerro | FeatureQueryOrder order = query==null? null:query.getOrder(); |
231 | 43020 | jjdelcerro | if( order != null ) { |
232 | 43026 | jjdelcerro | for( FeatureQueryOrderMember member : order.members() ) {
|
233 | 43020 | jjdelcerro | if( member.hasEvaluator() ) {
|
234 | String sqlorder = member.getEvaluator().getSQL();
|
||
235 | if( ! StringUtils.isEmpty(sqlorder) ) {
|
||
236 | 44682 | jjdelcerro | select.order_by().custom(sqlorder); |
237 | 43020 | jjdelcerro | } |
238 | } else {
|
||
239 | 44682 | jjdelcerro | select.order_by() |
240 | 43020 | jjdelcerro | .column(member.getAttributeName()) |
241 | .ascending(member.getAscending()); |
||
242 | } |
||
243 | } |
||
244 | } |
||
245 | if( !StringUtils.isEmpty(baseOrder) ) {
|
||
246 | 44682 | jjdelcerro | select.order_by().custom(baseOrder); |
247 | 43020 | jjdelcerro | } |
248 | 44727 | jjdelcerro | if( !select.has_order_by() ) {
|
249 | // Si no tenemos order by comprobamos si lo necesitamos y lo a?adimos.
|
||
250 | if( offset>0 || (offset==0 && limit>0) ) { |
||
251 | // No tengo claro que (offset==0 && limit>0) sea lo mas correcto,
|
||
252 | // Pero cuando se va a paginar y se pide la primera pagina offset es
|
||
253 | // 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados
|
||
254 | // que se obtienen no son correctos, ya que la primera pagina se saca
|
||
255 | // sin ordenar y el resto ordenadas.
|
||
256 | // Probablemente deberiamos tener alguna otra forma de detectar que
|
||
257 | // estamos paginanado ya que asi no distinguimo si solo queremos
|
||
258 | // obtener los primeros elementos sin importarnos su orden.
|
||
259 | 45162 | omartinez | if (select.has_group_by()) {
|
260 | ExpressionBuilder.Value group = select.getGroups().get(0);
|
||
261 | // if(!(group instanceof ExpressionBuilder.Function)) {
|
||
262 | // expbuilder.getattr(this.table.getTable(), group.XXX);
|
||
263 | // }
|
||
264 | select.order_by().column(group).ascending(); |
||
265 | |||
266 | } else if( primaryKeys.isEmpty() ) { |
||
267 | 44727 | jjdelcerro | // Muy probablemente si no tiene pk sea una vista, asi que
|
268 | // pasaremos de ordenar y esperemos que la vista este ya ordenada.
|
||
269 | select.disable_check_order_and_offset(); |
||
270 | 45162 | omartinez | } else {
|
271 | for(String attrName : primaryKeys ) { |
||
272 | // Se precisa indicar un orden para usar OFFSET.
|
||
273 | // select.order_by().column(sqlbuilder.as_identifier(attrName)).ascending();
|
||
274 | select.order_by().column(attrName).ascending(); |
||
275 | } |
||
276 | |||
277 | 44687 | jjdelcerro | } |
278 | 44727 | jjdelcerro | } |
279 | 43706 | jjdelcerro | } |
280 | 43020 | jjdelcerro | if( limit > 0 ) { |
281 | 44682 | jjdelcerro | select.limit(limit); |
282 | 43020 | jjdelcerro | } else {
|
283 | 44682 | jjdelcerro | select.limit(query==null? null:query.getLimit()); |
284 | 43020 | jjdelcerro | } |
285 | if( offset>0 ) { |
||
286 | 44682 | jjdelcerro | select.offset(offset); |
287 | 44376 | jjdelcerro | } |
288 | 44198 | jjdelcerro | sqlbuilder.setProperties( |
289 | 44369 | jjdelcerro | null,
|
290 | 44198 | jjdelcerro | PROP_FEATURE_TYPE, this.storeType,
|
291 | PROP_TABLE, table |
||
292 | 44682 | jjdelcerro | ); |
293 | 44748 | jjdelcerro | this.helper.processSpecialFunctions(sqlbuilder, storeType, extraColumnNames);
|
294 | 43020 | jjdelcerro | String sql = sqlbuilder.toString();
|
295 | 44678 | jjdelcerro | return sql;
|
296 | } |
||
297 | |||
298 | public ResultSetEntry createResultSet() throws DataException { |
||
299 | List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
||
300 | 44682 | jjdelcerro | List<String> extraColumnNames = new ArrayList<>(); |
301 | |||
302 | 44678 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
303 | 44682 | jjdelcerro | String sql = this.getSQL(sqlbuilder, columns, extraColumnNames); |
304 | 44678 | jjdelcerro | |
305 | 43020 | jjdelcerro | ResultSetEntry resultSetEntry = this.helper.getResulSetControler().create(
|
306 | 44376 | jjdelcerro | sql, fetchSize, |
307 | columns.toArray(new FeatureAttributeDescriptor[columns.size()]),
|
||
308 | 44682 | jjdelcerro | extraColumnNames.toArray(new String[extraColumnNames.size()]) |
309 | 43020 | jjdelcerro | ); |
310 | return resultSetEntry;
|
||
311 | } |
||
312 | 45155 | omartinez | |
313 | 43020 | jjdelcerro | } |