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 @ 44764
History | View | Annotate | Download (6.14 KB)
1 | 43020 | jjdelcerro | package org.gvsig.fmap.dal.store.jdbc2.spi.operations; |
---|---|---|---|
2 | |||
3 | import java.sql.Connection; |
||
4 | import java.sql.ResultSet; |
||
5 | import java.sql.SQLException; |
||
6 | import java.sql.Statement; |
||
7 | 44727 | jjdelcerro | import java.util.List; |
8 | 43020 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
9 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder; |
10 | 44727 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder; |
11 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.exception.DataException; |
12 | 44727 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureQuery; |
13 | import org.gvsig.fmap.dal.feature.FeatureQueryOrder; |
||
14 | 44376 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureType; |
15 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
16 | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
||
17 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
||
18 | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
||
19 | 44058 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
20 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
21 | 44727 | jjdelcerro | import org.gvsig.tools.evaluator.Evaluator; |
22 | 43020 | jjdelcerro | |
23 | public class CountOperation extends AbstractConnectionOperation { |
||
24 | |||
25 | 44058 | jjdelcerro | private final TableReference table; |
26 | 43020 | jjdelcerro | private final String baseFilter; |
27 | 44727 | jjdelcerro | private final FeatureQuery query; |
28 | 44376 | jjdelcerro | private final FeatureType featureType; |
29 | 43020 | jjdelcerro | |
30 | public CountOperation(
|
||
31 | JDBCHelper helper |
||
32 | ) { |
||
33 | 44376 | jjdelcerro | this(helper, null, null, null, null); |
34 | 43020 | jjdelcerro | } |
35 | |||
36 | public CountOperation(
|
||
37 | JDBCHelper helper, |
||
38 | 44376 | jjdelcerro | FeatureType featureType, |
39 | 44058 | jjdelcerro | TableReference table, |
40 | 43020 | jjdelcerro | String baseFilter,
|
41 | 44727 | jjdelcerro | FeatureQuery query |
42 | 43020 | jjdelcerro | ) { |
43 | super(helper);
|
||
44 | 44376 | jjdelcerro | this.featureType = featureType;
|
45 | 44058 | jjdelcerro | this.table = table;
|
46 | 43020 | jjdelcerro | this.baseFilter = baseFilter;
|
47 | 44727 | jjdelcerro | this.query = query;
|
48 | 43020 | jjdelcerro | } |
49 | |||
50 | @Override
|
||
51 | public final Object perform(Connection conn) throws DataException { |
||
52 | 44678 | jjdelcerro | return this.count(conn); |
53 | 43020 | jjdelcerro | } |
54 | |||
55 | 44678 | jjdelcerro | public String getSQL() { |
56 | 43020 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
57 | 44198 | jjdelcerro | ExpressionBuilder expbuilder = sqlbuilder.expression(); |
58 | 43020 | jjdelcerro | |
59 | 44727 | jjdelcerro | SelectBuilder select = sqlbuilder.select(); |
60 | |||
61 | if( this.query!=null && this.query.hasGroupByColumns() ) { |
||
62 | JDBCSQLBuilderBase subsqlbuilder = this.createSQLBuilder();
|
||
63 | SelectBuilder subselect = subsqlbuilder.select(); |
||
64 | subselect.column().value(subsqlbuilder.count().all()); |
||
65 | subselect.from().table() |
||
66 | .database(this.table.getDatabase())
|
||
67 | .schema(this.table.getSchema())
|
||
68 | .name(this.table.getTable());
|
||
69 | subselect.from().subquery(this.table.getSubquery());
|
||
70 | Evaluator filter = query==null? null:query.getFilter(); |
||
71 | if( filter != null ) { |
||
72 | String sqlfilter = filter.getSQL();
|
||
73 | if( ! StringUtils.isEmpty(sqlfilter) ) {
|
||
74 | if( this.helper.supportFilter(this.featureType, filter) ) { |
||
75 | subselect.where().set(expbuilder.toValue(sqlfilter)); |
||
76 | } |
||
77 | } |
||
78 | } |
||
79 | if( ! StringUtils.isEmpty(baseFilter) ) {
|
||
80 | subselect.where().and(expbuilder.toValue(baseFilter)); |
||
81 | } |
||
82 | |||
83 | FeatureQueryOrder order = query==null? null:query.getOrder(); |
||
84 | if( order != null ) { |
||
85 | for( FeatureQueryOrder.FeatureQueryOrderMember member : order.members() ) {
|
||
86 | if( member.hasEvaluator() ) {
|
||
87 | String sqlorder = member.getEvaluator().getSQL();
|
||
88 | if( ! StringUtils.isEmpty(sqlorder) ) {
|
||
89 | subselect.order_by().custom(sqlorder); |
||
90 | } |
||
91 | } else {
|
||
92 | subselect.order_by() |
||
93 | .column(member.getAttributeName()) |
||
94 | .ascending(member.getAscending()); |
||
95 | } |
||
96 | } |
||
97 | } |
||
98 | |||
99 | List<String> groupbyColumns = query==null? null:query.getGroupByColumns(); |
||
100 | if( groupbyColumns!=null && !groupbyColumns.isEmpty() ) { |
||
101 | for(String columnName : groupbyColumns ) { |
||
102 | subselect.group_by(expbuilder.column(columnName)); |
||
103 | } |
||
104 | } |
||
105 | subsqlbuilder.setProperties( |
||
106 | ExpressionBuilder.Variable.class, |
||
107 | PROP_TABLE, table |
||
108 | ); |
||
109 | String subsql = subselect.toString();
|
||
110 | select.from().table() |
||
111 | .database(this.table.getDatabase())
|
||
112 | .schema(this.table.getSchema())
|
||
113 | .name(this.table.getTable());
|
||
114 | select.from().subquery(subsql); |
||
115 | |||
116 | |||
117 | } else {
|
||
118 | select.column().value(sqlbuilder.count().all()); |
||
119 | select.from().table() |
||
120 | .database(this.table.getDatabase())
|
||
121 | .schema(this.table.getSchema())
|
||
122 | .name(this.table.getTable());
|
||
123 | select.from().subquery(this.table.getSubquery());
|
||
124 | if (!StringUtils.isEmpty(baseFilter)) {
|
||
125 | sqlbuilder.select().where().set( expbuilder.custom(baseFilter) ); |
||
126 | } |
||
127 | if( this.query!=null ) { |
||
128 | if( this.query.getFilter()!=null && !StringUtils.isBlank(this.query.getFilter().getSQL()) ) { |
||
129 | // El and() hace un set() si no hay un filtro previo
|
||
130 | select.where().and(expbuilder.toValue(this.query.getFilter().getSQL()));
|
||
131 | } |
||
132 | } |
||
133 | 43020 | jjdelcerro | } |
134 | 44748 | jjdelcerro | this.helper.processSpecialFunctions(sqlbuilder, featureType, null); |
135 | 44376 | jjdelcerro | |
136 | 44727 | jjdelcerro | select.remove_all_columns(); |
137 | select.column().value(sqlbuilder.count().all()); |
||
138 | 44385 | jjdelcerro | |
139 | 44198 | jjdelcerro | sqlbuilder.setProperties( |
140 | ExpressionBuilder.Variable.class, |
||
141 | PROP_TABLE, table |
||
142 | ); |
||
143 | 44727 | jjdelcerro | |
144 | String sql = select.toString();
|
||
145 | 44678 | jjdelcerro | return sql;
|
146 | } |
||
147 | |||
148 | public long count(Connection conn) throws DataException { |
||
149 | 43020 | jjdelcerro | |
150 | 44678 | jjdelcerro | String sql = this.getSQL(); |
151 | 43020 | jjdelcerro | Statement st = null; |
152 | ResultSet rs = null; |
||
153 | try {
|
||
154 | st = conn.createStatement(); |
||
155 | rs = JDBCUtils.executeQuery(st, sql); |
||
156 | if (!rs.next()) {
|
||
157 | return 0; |
||
158 | } |
||
159 | return rs.getLong(1); |
||
160 | |||
161 | } catch (SQLException ex) { |
||
162 | throw new JDBCSQLException(ex); |
||
163 | } finally {
|
||
164 | JDBCUtils.closeQuietly(st); |
||
165 | JDBCUtils.closeQuietly(rs); |
||
166 | } |
||
167 | } |
||
168 | |||
169 | } |