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 / CountOperation.java @ 45534
History | View | Annotate | Download (8.9 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.sql.ResultSet; |
||
28 | import java.sql.SQLException; |
||
29 | import java.sql.Statement; |
||
30 | 44727 | jjdelcerro | import java.util.List; |
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 | 45155 | omartinez | import org.gvsig.expressionevaluator.ExpressionUtils; |
36 | 44727 | 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 | 44727 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureQuery; |
41 | 44376 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureType; |
42 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
43 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
44 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
||
45 | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
||
46 | 44058 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
47 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
48 | 44727 | jjdelcerro | import org.gvsig.tools.evaluator.Evaluator; |
49 | 43020 | jjdelcerro | |
50 | public class CountOperation extends AbstractConnectionOperation { |
||
51 | |||
52 | 44058 | jjdelcerro | private final TableReference table; |
53 | 43020 | jjdelcerro | private final String baseFilter; |
54 | 44727 | jjdelcerro | private final FeatureQuery query; |
55 | 44376 | jjdelcerro | private final FeatureType featureType; |
56 | 43020 | jjdelcerro | |
57 | public CountOperation(
|
||
58 | JDBCHelper helper |
||
59 | ) { |
||
60 | 44376 | jjdelcerro | this(helper, null, null, null, null); |
61 | 43020 | jjdelcerro | } |
62 | |||
63 | public CountOperation(
|
||
64 | JDBCHelper helper, |
||
65 | 44376 | jjdelcerro | FeatureType featureType, |
66 | 44058 | jjdelcerro | TableReference table, |
67 | 43020 | jjdelcerro | String baseFilter,
|
68 | 44727 | jjdelcerro | FeatureQuery query |
69 | 43020 | jjdelcerro | ) { |
70 | super(helper);
|
||
71 | 44376 | jjdelcerro | this.featureType = featureType;
|
72 | 44058 | jjdelcerro | this.table = table;
|
73 | 43020 | jjdelcerro | this.baseFilter = baseFilter;
|
74 | 44727 | jjdelcerro | this.query = query;
|
75 | 43020 | jjdelcerro | } |
76 | |||
77 | @Override
|
||
78 | public final Object perform(Connection conn) throws DataException { |
||
79 | 44678 | jjdelcerro | return this.count(conn); |
80 | 43020 | jjdelcerro | } |
81 | |||
82 | 44678 | jjdelcerro | public String getSQL() { |
83 | 43020 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
84 | 44198 | jjdelcerro | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
85 | 43020 | jjdelcerro | |
86 | 44727 | jjdelcerro | SelectBuilder select = sqlbuilder.select(); |
87 | |||
88 | 45155 | omartinez | if (this.query != null && this.query.hasGroupByColumns()) { |
89 | JDBCSQLBuilderBase subsqlbuilder = this.createSQLBuilder();
|
||
90 | SelectBuilder subselect = subsqlbuilder.select(); |
||
91 | subselect.column().value(subsqlbuilder.count().all()); |
||
92 | subselect.from().table() |
||
93 | .database(this.table.getDatabase())
|
||
94 | .schema(this.table.getSchema())
|
||
95 | .name(this.table.getTable());
|
||
96 | subselect.from().subquery(this.table.getSubquery());
|
||
97 | Evaluator filter = query == null ? null : query.getFilter(); |
||
98 | if (filter != null) { |
||
99 | String sqlfilter = filter.getSQL();
|
||
100 | if (!StringUtils.isEmpty(sqlfilter)) {
|
||
101 | if (this.helper.supportFilter(this.featureType, filter)) { |
||
102 | subselect.where().set(expbuilder.toValue(sqlfilter)); |
||
103 | } |
||
104 | } |
||
105 | 44727 | jjdelcerro | } |
106 | 45155 | omartinez | if (!StringUtils.isEmpty(baseFilter)) {
|
107 | subselect.where().and(expbuilder.toValue(baseFilter)); |
||
108 | } |
||
109 | |||
110 | |||
111 | List<String> groupbyColumns = query == null ? null : query.getGroupByColumns(); |
||
112 | if (groupbyColumns != null && !groupbyColumns.isEmpty()) { |
||
113 | for (String columnName : groupbyColumns) { |
||
114 | if (this.featureType.getAttributeDescriptor(columnName) != null) { |
||
115 | subselect.group_by(expbuilder.column(columnName)); |
||
116 | 45162 | omartinez | } else if(this.featureType.getExtraColumns().get(columnName)!=null) { |
117 | EditableFeatureAttributeDescriptor attr = this.featureType.getExtraColumns().get(columnName);
|
||
118 | subselect.group_by(expbuilder.column(columnName)); |
||
119 | Expression exp = ((FeatureAttributeEmulatorExpression) attr.getFeatureAttributeEmulator()).getExpression();
|
||
120 | subselect.column().value(exp.getCode().toValue()).as(columnName); |
||
121 | 45155 | omartinez | } else {
|
122 | try {
|
||
123 | try {
|
||
124 | Code groupByColumnCode = ExpressionUtils.compile(columnName); |
||
125 | 45166 | omartinez | // if (groupByColumnCode.code() == Code.CALLABLE) {
|
126 | // Code.Callable callable = (Code.Callable) groupByColumnCode;
|
||
127 | // if (callable.name().equalsIgnoreCase(FUNCTION_LET)) {
|
||
128 | // Code exp = callable.parameters().get(1);
|
||
129 | // Code name = callable.parameters().get(0);
|
||
130 | // subselect.column().value(exp.toValue())
|
||
131 | // .as((String) ((Code.Constant) name).value());
|
||
132 | // // nombre que se pone en la parte del groupby debe de ser el nombre de la var del set
|
||
133 | // groupByColumnCode = exp;
|
||
134 | // }
|
||
135 | // }
|
||
136 | 45155 | omartinez | subselect.group_by(groupByColumnCode.toValue()); |
137 | } catch (Exception ex) { |
||
138 | throw new RuntimeException("Not able to create column by expression in groupby query", ex); |
||
139 | } |
||
140 | } catch (Exception ex) { |
||
141 | throw new RuntimeException("Not able to create column by expression in groupby query", ex); |
||
142 | } |
||
143 | } |
||
144 | } |
||
145 | } |
||
146 | this.helper.processSpecialFunctions(subsqlbuilder, featureType, null); |
||
147 | subsqlbuilder.setProperties( |
||
148 | ExpressionBuilder.Variable.class, |
||
149 | PROP_TABLE, table |
||
150 | ); |
||
151 | String subsql = subselect.toString();
|
||
152 | select.from().table() |
||
153 | .database(this.table.getDatabase())
|
||
154 | .schema(this.table.getSchema())
|
||
155 | .name(this.table.getTable());
|
||
156 | select.from().subquery(subsql); |
||
157 | |||
158 | 44727 | jjdelcerro | } else {
|
159 | 45155 | omartinez | select.column().value(sqlbuilder.count().all()); |
160 | select.from().table() |
||
161 | .database(this.table.getDatabase())
|
||
162 | .schema(this.table.getSchema())
|
||
163 | .name(this.table.getTable());
|
||
164 | select.from().subquery(this.table.getSubquery());
|
||
165 | if (!StringUtils.isEmpty(baseFilter)) {
|
||
166 | sqlbuilder.select().where().set(expbuilder.custom(baseFilter)); |
||
167 | 44727 | jjdelcerro | } |
168 | 45155 | omartinez | if (this.query != null) { |
169 | if (this.query.getFilter() != null && !StringUtils.isBlank(this.query.getFilter().getSQL())) { |
||
170 | // El and() hace un set() si no hay un filtro previo
|
||
171 | select.where().and(expbuilder.toValue(this.query.getFilter().getSQL()));
|
||
172 | } |
||
173 | } |
||
174 | 43020 | jjdelcerro | } |
175 | 44748 | jjdelcerro | this.helper.processSpecialFunctions(sqlbuilder, featureType, null); |
176 | 45155 | omartinez | |
177 | 44727 | jjdelcerro | select.remove_all_columns(); |
178 | select.column().value(sqlbuilder.count().all()); |
||
179 | 45155 | omartinez | |
180 | 44198 | jjdelcerro | sqlbuilder.setProperties( |
181 | 45155 | omartinez | ExpressionBuilder.Variable.class, |
182 | 44198 | jjdelcerro | PROP_TABLE, table |
183 | ); |
||
184 | 44727 | jjdelcerro | |
185 | String sql = select.toString();
|
||
186 | 44678 | jjdelcerro | return sql;
|
187 | } |
||
188 | 45155 | omartinez | |
189 | 44678 | jjdelcerro | public long count(Connection conn) throws DataException { |
190 | 43020 | jjdelcerro | |
191 | 44678 | jjdelcerro | String sql = this.getSQL(); |
192 | 43020 | jjdelcerro | Statement st = null; |
193 | ResultSet rs = null; |
||
194 | try {
|
||
195 | st = conn.createStatement(); |
||
196 | rs = JDBCUtils.executeQuery(st, sql); |
||
197 | if (!rs.next()) {
|
||
198 | return 0; |
||
199 | } |
||
200 | return rs.getLong(1); |
||
201 | |||
202 | } catch (SQLException ex) { |
||
203 | throw new JDBCSQLException(ex); |
||
204 | } finally {
|
||
205 | JDBCUtils.closeQuietly(st); |
||
206 | JDBCUtils.closeQuietly(rs); |
||
207 | } |
||
208 | } |
||
209 | 45155 | omartinez | |
210 | 43020 | jjdelcerro | } |