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 @ 44854
History | View | Annotate | Download (9.77 KB)
1 | 43020 | jjdelcerro | package org.gvsig.fmap.dal.store.jdbc2.spi.operations; |
---|---|---|---|
2 | |||
3 | import java.sql.Connection; |
||
4 | import java.util.ArrayList; |
||
5 | import java.util.List; |
||
6 | 43358 | jjdelcerro | import org.apache.commons.lang3.ArrayUtils; |
7 | 43020 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
8 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder; |
9 | 44727 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionUtils; |
10 | 44682 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder; |
11 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.exception.DataException; |
12 | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
||
13 | import org.gvsig.fmap.dal.feature.FeatureQuery; |
||
14 | import org.gvsig.fmap.dal.feature.FeatureQueryOrder; |
||
15 | 43026 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureQueryOrder.FeatureQueryOrderMember; |
16 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureType; |
17 | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
||
18 | 44058 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
19 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.ResulSetControler.ResultSetEntry; |
20 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
21 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_FEATURE_TYPE; |
22 | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
||
23 | 43020 | jjdelcerro | import org.gvsig.fmap.geom.DataTypes; |
24 | import org.gvsig.tools.evaluator.Evaluator; |
||
25 | |||
26 | public class ResultSetForSetProviderOperation extends AbstractConnectionOperation { |
||
27 | 44058 | jjdelcerro | private final TableReference table; |
28 | 43020 | jjdelcerro | private final String baseFilter; |
29 | private final String baseOrder; |
||
30 | private final FeatureType storeType; |
||
31 | private final FeatureType setType; |
||
32 | private final FeatureQuery query; |
||
33 | private final long limit; |
||
34 | private final long offset; |
||
35 | private final int fetchSize; |
||
36 | |||
37 | public ResultSetForSetProviderOperation(
|
||
38 | JDBCHelper helper, |
||
39 | 44058 | jjdelcerro | TableReference table, |
40 | 43020 | jjdelcerro | String baseFilter,
|
41 | String baseOrder,
|
||
42 | FeatureQuery query, |
||
43 | FeatureType storeType, |
||
44 | FeatureType setType, |
||
45 | long limit,
|
||
46 | long offset,
|
||
47 | int fetchSize
|
||
48 | ) { |
||
49 | super(helper);
|
||
50 | this.table = table;
|
||
51 | this.baseFilter = baseFilter;
|
||
52 | this.baseOrder = baseOrder;
|
||
53 | this.storeType = storeType;
|
||
54 | this.setType = setType;
|
||
55 | this.query = query;
|
||
56 | this.limit = limit;
|
||
57 | this.offset = offset;
|
||
58 | this.fetchSize = fetchSize;
|
||
59 | } |
||
60 | |||
61 | @Override
|
||
62 | 43377 | jjdelcerro | protected Object perform_operation() throws Exception { |
63 | 44678 | jjdelcerro | ResultSetEntry rs = createResultSet(); |
64 | 43020 | jjdelcerro | return rs;
|
65 | } |
||
66 | 43377 | jjdelcerro | |
67 | @Override
|
||
68 | public Object perform(Connection conn) throws DataException { |
||
69 | 44678 | jjdelcerro | throw new UnsupportedOperationException("Not supported yet."); |
70 | 43377 | jjdelcerro | } |
71 | 44678 | jjdelcerro | |
72 | public String getSQL() { |
||
73 | 43358 | jjdelcerro | List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
74 | 43020 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
75 | 44682 | jjdelcerro | String sql = this.getSQL(sqlbuilder, columns, null); |
76 | 44678 | jjdelcerro | return sql;
|
77 | } |
||
78 | |||
79 | 44682 | jjdelcerro | public String getSQL( |
80 | JDBCSQLBuilderBase sqlbuilder, |
||
81 | List<FeatureAttributeDescriptor> columns,
|
||
82 | List<String> extraColumnNames |
||
83 | ) { |
||
84 | 44678 | jjdelcerro | double tolerance = -1 ; //query.getScale(); |
85 | 44198 | jjdelcerro | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
86 | 44682 | jjdelcerro | SelectBuilder select = sqlbuilder.select(); |
87 | 43020 | jjdelcerro | |
88 | List<String> primaryKeys = new ArrayList<>(); |
||
89 | for(FeatureAttributeDescriptor attr : storeType.getPrimaryKey() ) {
|
||
90 | primaryKeys.add(attr.getName()); |
||
91 | } |
||
92 | 43706 | jjdelcerro | List<String> forcedColumns = new ArrayList<>(primaryKeys); |
93 | |||
94 | 43358 | jjdelcerro | String[] constantsAttributeNames = null; |
95 | if(query !=null && query.hasConstantsAttributeNames() ) { |
||
96 | constantsAttributeNames = query.getConstantsAttributeNames(); |
||
97 | } |
||
98 | 43020 | jjdelcerro | for(FeatureAttributeDescriptor attr : setType ) {
|
99 | 44324 | jjdelcerro | if( attr.isComputed() ) {
|
100 | continue;
|
||
101 | } |
||
102 | 43358 | jjdelcerro | if( ArrayUtils.contains(constantsAttributeNames, attr.getName()) ) {
|
103 | continue;
|
||
104 | } |
||
105 | 43020 | jjdelcerro | if( attr.isPrimaryKey() ) {
|
106 | 43706 | jjdelcerro | forcedColumns.remove(attr.getName()); |
107 | 43020 | jjdelcerro | } |
108 | 44712 | jjdelcerro | if( query !=null && query.hasGroupByColumns()) { |
109 | 44727 | jjdelcerro | String aggregate = query.getAggregate(attr.getName());
|
110 | if( this.query.isAGroupByColumn(attr.getName()) ) { |
||
111 | select.column().name(attr.getName()); |
||
112 | } else if( aggregate == null ) { |
||
113 | select.column().value(expbuilder.constant(null)).as(attr.getName());
|
||
114 | 44712 | jjdelcerro | } else {
|
115 | 44727 | jjdelcerro | select.column() |
116 | .value(ExpressionUtils.compile(aggregate).toValue(expbuilder)) |
||
117 | .as(attr.getName()); |
||
118 | 44712 | jjdelcerro | } |
119 | 43020 | jjdelcerro | } else {
|
120 | 44712 | jjdelcerro | if( attr.getType() == DataTypes.GEOMETRY ) {
|
121 | select.column().name(attr.getName()).as_geometry(); |
||
122 | // if( tolerance<=0 || !sqlbuilder.getConfig().has_functionality(Config.ST_Simplify)) {
|
||
123 | // select.column().name(attr.getName()).as_geometry();
|
||
124 | // } else {
|
||
125 | // select.column().value(
|
||
126 | // sqlbuilder.ST_Simplify(
|
||
127 | // sqlbuilder.column(attr.getName()),
|
||
128 | // sqlbuilder.constant(tolerance)
|
||
129 | // )
|
||
130 | // ).as_geometry();
|
||
131 | // }
|
||
132 | } else {
|
||
133 | select.column().name(attr.getName()); |
||
134 | } |
||
135 | 43020 | jjdelcerro | } |
136 | 44727 | jjdelcerro | columns.add(attr); |
137 | 43020 | jjdelcerro | } |
138 | 44712 | jjdelcerro | if( query !=null && query.hasGroupByColumns() ) { |
139 | for(String attrName : query.getGroupByColumns() ) { |
||
140 | select.group_by(expbuilder.column(attrName)); |
||
141 | 44374 | jjdelcerro | } |
142 | 44854 | omartinez | } else {
|
143 | for(String attrName : forcedColumns ) { |
||
144 | select.column().name(attrName); |
||
145 | columns.add(setType.getAttributeDescriptor(attrName)); |
||
146 | } |
||
147 | 43020 | jjdelcerro | } |
148 | |||
149 | 44682 | jjdelcerro | select.from().table() |
150 | 44058 | jjdelcerro | .database(this.table.getDatabase())
|
151 | .schema(this.table.getSchema())
|
||
152 | .name(this.table.getTable());
|
||
153 | 44682 | jjdelcerro | select.from().subquery(this.table.getSubquery());
|
154 | 43020 | jjdelcerro | |
155 | 44058 | jjdelcerro | Evaluator filter = query==null? null:query.getFilter(); |
156 | 43020 | jjdelcerro | if( filter != null ) { |
157 | String sqlfilter = filter.getSQL();
|
||
158 | if( ! StringUtils.isEmpty(sqlfilter) ) {
|
||
159 | 44198 | jjdelcerro | if( this.helper.supportFilter(this.storeType, filter) ) { |
160 | 44682 | jjdelcerro | select.where().set(expbuilder.toValue(sqlfilter)); |
161 | 44198 | jjdelcerro | } |
162 | 43020 | jjdelcerro | } |
163 | } |
||
164 | if( ! StringUtils.isEmpty(baseFilter) ) {
|
||
165 | 44682 | jjdelcerro | select.where().and(expbuilder.toValue(baseFilter)); |
166 | 43020 | jjdelcerro | } |
167 | |||
168 | 44058 | jjdelcerro | FeatureQueryOrder order = query==null? null:query.getOrder(); |
169 | 43020 | jjdelcerro | if( order != null ) { |
170 | 43026 | jjdelcerro | for( FeatureQueryOrderMember member : order.members() ) {
|
171 | 43020 | jjdelcerro | if( member.hasEvaluator() ) {
|
172 | String sqlorder = member.getEvaluator().getSQL();
|
||
173 | if( ! StringUtils.isEmpty(sqlorder) ) {
|
||
174 | 44682 | jjdelcerro | select.order_by().custom(sqlorder); |
175 | 43020 | jjdelcerro | } |
176 | } else {
|
||
177 | 44682 | jjdelcerro | select.order_by() |
178 | 43020 | jjdelcerro | .column(member.getAttributeName()) |
179 | .ascending(member.getAscending()); |
||
180 | } |
||
181 | } |
||
182 | } |
||
183 | if( !StringUtils.isEmpty(baseOrder) ) {
|
||
184 | 44682 | jjdelcerro | select.order_by().custom(baseOrder); |
185 | 43020 | jjdelcerro | } |
186 | 44727 | jjdelcerro | if( !select.has_order_by() ) {
|
187 | // Si no tenemos order by comprobamos si lo necesitamos y lo a?adimos.
|
||
188 | if( offset>0 || (offset==0 && limit>0) ) { |
||
189 | // No tengo claro que (offset==0 && limit>0) sea lo mas correcto,
|
||
190 | // Pero cuando se va a paginar y se pide la primera pagina offset es
|
||
191 | // 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados
|
||
192 | // que se obtienen no son correctos, ya que la primera pagina se saca
|
||
193 | // sin ordenar y el resto ordenadas.
|
||
194 | // Probablemente deberiamos tener alguna otra forma de detectar que
|
||
195 | // estamos paginanado ya que asi no distinguimo si solo queremos
|
||
196 | // obtener los primeros elementos sin importarnos su orden.
|
||
197 | if( primaryKeys.isEmpty() ) {
|
||
198 | // Muy probablemente si no tiene pk sea una vista, asi que
|
||
199 | // pasaremos de ordenar y esperemos que la vista este ya ordenada.
|
||
200 | select.disable_check_order_and_offset(); |
||
201 | } else {
|
||
202 | for(String attrName : primaryKeys ) { |
||
203 | // Se precisa indicar un orden para usar OFFSET.
|
||
204 | select.order_by().column(sqlbuilder.as_identifier(attrName)).ascending(); |
||
205 | } |
||
206 | 44687 | jjdelcerro | } |
207 | 44727 | jjdelcerro | } |
208 | 43706 | jjdelcerro | } |
209 | 43020 | jjdelcerro | if( limit > 0 ) { |
210 | 44682 | jjdelcerro | select.limit(limit); |
211 | 43020 | jjdelcerro | } else {
|
212 | 44682 | jjdelcerro | select.limit(query==null? null:query.getLimit()); |
213 | 43020 | jjdelcerro | } |
214 | if( offset>0 ) { |
||
215 | 44682 | jjdelcerro | select.offset(offset); |
216 | 44376 | jjdelcerro | } |
217 | 44198 | jjdelcerro | sqlbuilder.setProperties( |
218 | 44369 | jjdelcerro | null,
|
219 | 44198 | jjdelcerro | PROP_FEATURE_TYPE, this.storeType,
|
220 | PROP_TABLE, table |
||
221 | 44682 | jjdelcerro | ); |
222 | 44748 | jjdelcerro | this.helper.processSpecialFunctions(sqlbuilder, storeType, extraColumnNames);
|
223 | 43020 | jjdelcerro | String sql = sqlbuilder.toString();
|
224 | 44678 | jjdelcerro | return sql;
|
225 | } |
||
226 | |||
227 | public ResultSetEntry createResultSet() throws DataException { |
||
228 | List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
||
229 | 44682 | jjdelcerro | List<String> extraColumnNames = new ArrayList<>(); |
230 | |||
231 | 44678 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
232 | 44682 | jjdelcerro | String sql = this.getSQL(sqlbuilder, columns, extraColumnNames); |
233 | 44678 | jjdelcerro | |
234 | 43020 | jjdelcerro | ResultSetEntry resultSetEntry = this.helper.getResulSetControler().create(
|
235 | 44376 | jjdelcerro | sql, fetchSize, |
236 | columns.toArray(new FeatureAttributeDescriptor[columns.size()]),
|
||
237 | 44682 | jjdelcerro | extraColumnNames.toArray(new String[extraColumnNames.size()]) |
238 | 43020 | jjdelcerro | ); |
239 | return resultSetEntry;
|
||
240 | } |
||
241 | |||
242 | } |