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 @ 44764
History | View | Annotate | Download (9.57 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 | 43020 | jjdelcerro | } |
143 | |||
144 | 44682 | jjdelcerro | select.from().table() |
145 | 44058 | jjdelcerro | .database(this.table.getDatabase())
|
146 | .schema(this.table.getSchema())
|
||
147 | .name(this.table.getTable());
|
||
148 | 44682 | jjdelcerro | select.from().subquery(this.table.getSubquery());
|
149 | 43020 | jjdelcerro | |
150 | 44058 | jjdelcerro | Evaluator filter = query==null? null:query.getFilter(); |
151 | 43020 | jjdelcerro | if( filter != null ) { |
152 | String sqlfilter = filter.getSQL();
|
||
153 | if( ! StringUtils.isEmpty(sqlfilter) ) {
|
||
154 | 44198 | jjdelcerro | if( this.helper.supportFilter(this.storeType, filter) ) { |
155 | 44682 | jjdelcerro | select.where().set(expbuilder.toValue(sqlfilter)); |
156 | 44198 | jjdelcerro | } |
157 | 43020 | jjdelcerro | } |
158 | } |
||
159 | if( ! StringUtils.isEmpty(baseFilter) ) {
|
||
160 | 44682 | jjdelcerro | select.where().and(expbuilder.toValue(baseFilter)); |
161 | 43020 | jjdelcerro | } |
162 | |||
163 | 44058 | jjdelcerro | FeatureQueryOrder order = query==null? null:query.getOrder(); |
164 | 43020 | jjdelcerro | if( order != null ) { |
165 | 43026 | jjdelcerro | for( FeatureQueryOrderMember member : order.members() ) {
|
166 | 43020 | jjdelcerro | if( member.hasEvaluator() ) {
|
167 | String sqlorder = member.getEvaluator().getSQL();
|
||
168 | if( ! StringUtils.isEmpty(sqlorder) ) {
|
||
169 | 44682 | jjdelcerro | select.order_by().custom(sqlorder); |
170 | 43020 | jjdelcerro | } |
171 | } else {
|
||
172 | 44682 | jjdelcerro | select.order_by() |
173 | 43020 | jjdelcerro | .column(member.getAttributeName()) |
174 | .ascending(member.getAscending()); |
||
175 | } |
||
176 | } |
||
177 | } |
||
178 | if( !StringUtils.isEmpty(baseOrder) ) {
|
||
179 | 44682 | jjdelcerro | select.order_by().custom(baseOrder); |
180 | 43020 | jjdelcerro | } |
181 | 44727 | jjdelcerro | if( !select.has_order_by() ) {
|
182 | // Si no tenemos order by comprobamos si lo necesitamos y lo a?adimos.
|
||
183 | if( offset>0 || (offset==0 && limit>0) ) { |
||
184 | // No tengo claro que (offset==0 && limit>0) sea lo mas correcto,
|
||
185 | // Pero cuando se va a paginar y se pide la primera pagina offset es
|
||
186 | // 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados
|
||
187 | // que se obtienen no son correctos, ya que la primera pagina se saca
|
||
188 | // sin ordenar y el resto ordenadas.
|
||
189 | // Probablemente deberiamos tener alguna otra forma de detectar que
|
||
190 | // estamos paginanado ya que asi no distinguimo si solo queremos
|
||
191 | // obtener los primeros elementos sin importarnos su orden.
|
||
192 | if( primaryKeys.isEmpty() ) {
|
||
193 | // Muy probablemente si no tiene pk sea una vista, asi que
|
||
194 | // pasaremos de ordenar y esperemos que la vista este ya ordenada.
|
||
195 | select.disable_check_order_and_offset(); |
||
196 | } else {
|
||
197 | for(String attrName : primaryKeys ) { |
||
198 | // Se precisa indicar un orden para usar OFFSET.
|
||
199 | select.order_by().column(sqlbuilder.as_identifier(attrName)).ascending(); |
||
200 | } |
||
201 | 44687 | jjdelcerro | } |
202 | 44727 | jjdelcerro | } |
203 | 43706 | jjdelcerro | } |
204 | 43020 | jjdelcerro | if( limit > 0 ) { |
205 | 44682 | jjdelcerro | select.limit(limit); |
206 | 43020 | jjdelcerro | } else {
|
207 | 44682 | jjdelcerro | select.limit(query==null? null:query.getLimit()); |
208 | 43020 | jjdelcerro | } |
209 | if( offset>0 ) { |
||
210 | 44682 | jjdelcerro | select.offset(offset); |
211 | 44376 | jjdelcerro | } |
212 | 44198 | jjdelcerro | sqlbuilder.setProperties( |
213 | 44369 | jjdelcerro | null,
|
214 | 44198 | jjdelcerro | PROP_FEATURE_TYPE, this.storeType,
|
215 | PROP_TABLE, table |
||
216 | 44682 | jjdelcerro | ); |
217 | 44748 | jjdelcerro | this.helper.processSpecialFunctions(sqlbuilder, storeType, extraColumnNames);
|
218 | 43020 | jjdelcerro | String sql = sqlbuilder.toString();
|
219 | 44678 | jjdelcerro | return sql;
|
220 | } |
||
221 | |||
222 | public ResultSetEntry createResultSet() throws DataException { |
||
223 | List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
||
224 | 44682 | jjdelcerro | List<String> extraColumnNames = new ArrayList<>(); |
225 | |||
226 | 44678 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = createSQLBuilder(); |
227 | 44682 | jjdelcerro | String sql = this.getSQL(sqlbuilder, columns, extraColumnNames); |
228 | 44678 | jjdelcerro | |
229 | 43020 | jjdelcerro | ResultSetEntry resultSetEntry = this.helper.getResulSetControler().create(
|
230 | 44376 | jjdelcerro | sql, fetchSize, |
231 | columns.toArray(new FeatureAttributeDescriptor[columns.size()]),
|
||
232 | 44682 | jjdelcerro | extraColumnNames.toArray(new String[extraColumnNames.size()]) |
233 | 43020 | jjdelcerro | ); |
234 | return resultSetEntry;
|
||
235 | } |
||
236 | |||
237 | } |