1
package org.hisp.dhis.aggregation.jdbc;
4
* Copyright (c) 2004-2007, University of Oslo
7
* Redistribution and use in source and binary forms, with or without
8
* modification, are permitted provided that the following conditions are met:
9
* * Redistributions of source code must retain the above copyright notice, this
10
* list of conditions and the following disclaimer.
11
* * Redistributions in binary form must reproduce the above copyright notice,
12
* this list of conditions and the following disclaimer in the documentation
13
* and/or other materials provided with the distribution.
14
* * Neither the name of the HISP project nor the names of its contributors may
15
* be used to endorse or promote products derived from this software without
16
* specific prior written permission.
18
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
19
* ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
20
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
21
* DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
22
* ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
23
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
24
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
25
* ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
26
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
27
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
30
import java.sql.ResultSet;
31
import java.sql.SQLException;
32
import java.util.ArrayList;
33
import java.util.Collection;
34
import java.util.HashSet;
36
import org.hisp.dhis.aggregation.AggregationStore;
37
import org.hisp.dhis.jdbc.StatementHolder;
38
import org.hisp.dhis.jdbc.StatementManager;
39
import org.hisp.dhis.datavalue.DataValue;
40
import org.hisp.dhis.period.Period;
43
* @author Lars Helge Overland
44
* @version $Id: JdbcAggregationStore.java 5942 2008-10-16 15:44:57Z larshelg $
46
public class JdbcAggregationStore
47
implements AggregationStore
49
// ----------------------------------------------------------------------
51
// ----------------------------------------------------------------------
53
private StatementManager statementManager;
55
public void setStatementManager( StatementManager statementManager )
57
this.statementManager = statementManager;
60
// ----------------------------------------------------------------------
62
// ----------------------------------------------------------------------
64
public Collection<DataValue> getDataValues( Collection<Integer> sourceIds, int dataElementId, int optionComboId, Collection<Integer> periodIds )
66
if ( sourceIds != null && sourceIds.size() > 0 && periodIds != null && periodIds.size() > 0 )
68
StatementHolder holder = statementManager.getHolder();
73
"SELECT periodid, value " +
75
"WHERE dataelementid = " + dataElementId + " " +
76
"AND categoryoptioncomboid = " + optionComboId + " " +
77
"AND periodid IN ( " + getCommaDelimitedString( periodIds ) + " ) " +
78
"AND sourceid IN ( " + getCommaDelimitedString( sourceIds ) + " )";
80
ResultSet resultSet = holder.getStatement().executeQuery( sql );
82
return getDataValues( resultSet );
84
catch ( Exception ex )
86
throw new RuntimeException( "Failed to get DataValues", ex );
94
return new ArrayList<DataValue>();
97
public Collection<DataValue> getDataValues( int sourceId, int dataElementId, int optionComboId, Collection<Integer> periodIds )
99
if ( periodIds != null && periodIds.size() > 0 )
101
StatementHolder holder = statementManager.getHolder();
106
"SELECT periodid, value " +
108
"WHERE dataelementid = " + dataElementId + " " +
109
"AND categoryoptioncomboid = " + optionComboId + " " +
110
"AND periodid IN ( " + getCommaDelimitedString( periodIds ) + " ) " +
111
"AND sourceid = " + sourceId;
113
ResultSet resultSet = holder.getStatement().executeQuery( sql );
115
return getDataValues( resultSet );
117
catch ( SQLException ex )
119
throw new RuntimeException( "Failed to get DataValues", ex );
127
return new ArrayList<DataValue>();
130
public Collection<String> getDataValueIdentifiers()
134
final int limit = 10000;
136
Collection<String> identifiers = new HashSet<String>();
138
Collection<String> temp = null;
140
while ( ( temp = getDataValueIdentifiers( min, limit ) ).size() > 0 )
142
identifiers.addAll( temp );
150
private Collection<String> getDataValueIdentifiers( int min, int limit )
152
StatementHolder holder = statementManager.getHolder();
157
"SELECT dataelementid, periodid, sourceid " +
159
"ORDER BY dataelementid, periodid, sourceid " +
160
"LIMIT " + min + ", " + limit;
162
ResultSet resultSet = holder.getStatement().executeQuery( sql );
164
Collection<String> identifiers = new HashSet<String>();
166
while ( resultSet.next() )
168
identifiers.add( resultSet.getInt( 1 ) + "-" + resultSet.getInt( 2 ) + "-" + resultSet.getInt( 3 ) );
173
catch ( SQLException ex )
175
throw new RuntimeException( "Failed to get DataValue identifiers", ex );
183
// ----------------------------------------------------------------------
184
// Supportive methods
185
// ----------------------------------------------------------------------
187
private Collection<DataValue> getDataValues( ResultSet resultSet )
191
Collection<DataValue> list = new ArrayList<DataValue>();
193
while ( resultSet.next() )
195
Period period = new Period();
197
period.setId( Integer.parseInt( resultSet.getString( 1 ) ) );
199
DataValue dataValue = new DataValue();
201
dataValue.setPeriod( period );
202
dataValue.setValue( resultSet.getString( 2 ) );
204
list.add( dataValue );
209
catch ( SQLException ex )
211
throw new RuntimeException( "Failed to transform resultset into collection", ex );
215
private String getCommaDelimitedString( Collection<Integer> elements )
217
if ( elements != null && elements.size() > 0 )
219
StringBuffer sourceSqlBuffer = new StringBuffer();
221
for ( Integer element : elements )
223
sourceSqlBuffer.append( element.toString() + ", " );
226
return sourceSqlBuffer.substring( 0, sourceSqlBuffer.length() - ", ".length() );