dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #15373
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 5549: Using jdbc template instead of statement manager for report table
------------------------------------------------------------
revno: 5549
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2011-12-22 17:18:28 +0100
message:
Using jdbc template instead of statement manager for report table
modified:
dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/chart/impl/DefaultChartService.java
dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java
dhis-2/dhis-services/dhis-service-reporting/src/main/resources/META-INF/dhis/beans.xml
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/chart/impl/DefaultChartService.java'
--- dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/chart/impl/DefaultChartService.java 2011-12-22 16:01:28 +0000
+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/chart/impl/DefaultChartService.java 2011-12-22 16:18:28 +0000
@@ -48,6 +48,7 @@
import java.util.Map;
import java.util.Map.Entry;
+import org.amplecode.quick.StatementManager;
import org.apache.commons.math.MathException;
import org.apache.commons.math.analysis.SplineInterpolator;
import org.apache.commons.math.analysis.UnivariateRealFunction;
@@ -61,7 +62,6 @@
import org.hisp.dhis.common.NameableObject;
import org.hisp.dhis.dataelement.DataElement;
import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
-import org.hisp.dhis.dataset.DataSet;
import org.hisp.dhis.datavalue.DataValue;
import org.hisp.dhis.datavalue.DataValueService;
import org.hisp.dhis.i18n.I18nFormat;
@@ -118,8 +118,6 @@
private static final Font labelFont = new Font( "Tahoma", Font.PLAIN, 10 );
private static final String TREND_PREFIX = "Trend - ";
- private static final String TITLE_SEPARATOR = " - ";
- private static final String DEFAULT_TITLE_PIVOT_CHART = "Pivot Chart";
private static final Color[] colors = {Color.decode( "#d54a4a" ), Color.decode( "#2e4e83" ),
Color.decode( "#75e077" ), Color.decode( "#e3e274" ), Color.decode( "#e58c6d" ), Color.decode( "#df6ff3" ),
@@ -158,20 +156,6 @@
this.minMaxDataElementService = minMaxDataElementService;
}
- private AggregationService aggregationService;
-
- public void setAggregationService( AggregationService aggregationService )
- {
- this.aggregationService = aggregationService;
- }
-
- private SystemSettingManager systemSettingManager;
-
- public void setSystemSettingManager( SystemSettingManager systemSettingManager )
- {
- this.systemSettingManager = systemSettingManager;
- }
-
private CurrentUserService currentUserService;
public void setCurrentUserService( CurrentUserService currentUserService )
@@ -193,6 +177,29 @@
this.reportTableManager = reportTableManager;
}
+ // TODO remove support for aggregation service
+
+ private AggregationService aggregationService;
+
+ public void setAggregationService( AggregationService aggregationService )
+ {
+ this.aggregationService = aggregationService;
+ }
+
+ private SystemSettingManager systemSettingManager;
+
+ public void setSystemSettingManager( SystemSettingManager systemSettingManager )
+ {
+ this.systemSettingManager = systemSettingManager;
+ }
+
+ private StatementManager statementManager;
+
+ public void setStatementManager( StatementManager statementManager )
+ {
+ this.statementManager = statementManager;
+ }
+
// -------------------------------------------------------------------------
// ChartService implementation
// -------------------------------------------------------------------------
=== modified file 'dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java'
--- dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java 2011-12-22 13:09:31 +0000
+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java 2011-12-22 16:18:28 +0000
@@ -29,12 +29,9 @@
import static org.hisp.dhis.reporttable.ReportTable.getIdentifier;
-import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;
-import org.amplecode.quick.StatementHolder;
-import org.amplecode.quick.StatementManager;
import org.hisp.dhis.chart.Chart;
import org.hisp.dhis.dataelement.DataElement;
import org.hisp.dhis.dataelement.DataElementCategoryOption;
@@ -59,13 +56,6 @@
// -------------------------------------------------------------------------
// Dependencies
// -------------------------------------------------------------------------
-
- private StatementManager statementManager;
-
- public void setStatementManager( StatementManager statementManager )
- {
- this.statementManager = statementManager;
- }
private JdbcTemplate jdbcTemplate;
@@ -80,10 +70,6 @@
public Map<String, Double> getAggregatedValueMap( ReportTable reportTable )
{
- // TODO use jdbc template
-
- StatementHolder holder = statementManager.getHolder();
-
Map<String, Double> map = new HashMap<String, Double>();
String dataElementIds = TextUtils.getCommaDelimitedString(
@@ -97,114 +83,103 @@
String unitIds = TextUtils.getCommaDelimitedString(
ConversionUtils.getIdentifiers( OrganisationUnit.class, reportTable.getAllUnits() ) );
- try
- {
- if ( reportTable.hasDataElements() )
- {
- final String sql = "SELECT dataelementid, periodid, organisationunitid, SUM(value) FROM aggregateddatavalue " +
- "WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ") " +
- "GROUP BY dataelementid, periodid, organisationunitid"; // Sum of category option combos
-
- ResultSet resultSet = holder.getStatement().executeQuery( sql );
-
- while ( resultSet.next() )
- {
- String id = getIdentifier( getIdentifier( DataElement.class, resultSet.getInt( 1 ) ),
- getIdentifier( Period.class, resultSet.getInt( 2 ) ),
- getIdentifier( OrganisationUnit.class, resultSet.getInt( 3 ) ) );
-
- map.put( id, resultSet.getDouble( 4 ) );
- }
- }
-
- if ( reportTable.hasIndicators() )
- {
- final String sql = "SELECT indicatorid, periodid, organisationunitid, value FROM aggregatedindicatorvalue " +
- "WHERE indicatorid IN (" + indicatorIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
-
- ResultSet resultSet = holder.getStatement().executeQuery( sql );
-
- while ( resultSet.next() )
- {
- String id = getIdentifier( getIdentifier( Indicator.class, resultSet.getInt( 1 ) ),
- getIdentifier( Period.class, resultSet.getInt( 2 ) ),
- getIdentifier( OrganisationUnit.class, resultSet.getInt( 3 ) ) );
-
- map.put( id, resultSet.getDouble( 4 ) );
- }
- }
-
- if ( reportTable.hasDataSets() )
- {
- final String sql = "SELECT datasetid, periodid, organisationunitid, value FROM aggregateddatasetcompleteness " +
- "WHERE datasetid IN (" + dataSetIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
-
- ResultSet resultSet = holder.getStatement().executeQuery( sql );
-
- while ( resultSet.next() )
- {
- String id = getIdentifier( getIdentifier( DataSet.class, resultSet.getInt( 1 ) ),
- getIdentifier( Period.class, resultSet.getInt( 2 ) ),
- getIdentifier( OrganisationUnit.class, resultSet.getInt( 3 ) ) );
-
- map.put( id, resultSet.getDouble( 4 ) );
- }
- }
-
- if ( reportTable.isDimensional() )
- {
- final String sql = "SELECT dataelementid, categoryoptioncomboid, periodid, organisationunitid, value FROM aggregateddatavalue " +
- "WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
-
- ResultSet resultSet = holder.getStatement().executeQuery( sql );
-
- while ( resultSet.next() )
- {
- String id = getIdentifier( getIdentifier( DataElement.class, resultSet.getInt( 1 ) ),
- getIdentifier( DataElementCategoryOptionCombo.class, resultSet.getInt( 2 ) ),
- getIdentifier( Period.class, resultSet.getInt( 3 ) ),
- getIdentifier( OrganisationUnit.class, resultSet.getInt( 4 ) ) );
-
- map.put( id, resultSet.getDouble( 5 ) );
- }
- }
-
- if ( reportTable.doTotal() )
- {
- for ( DataElementCategoryOption categoryOption : reportTable.getCategoryCombo().getCategoryOptions() )
- {
- String cocIds = TextUtils.getCommaDelimitedString(
- ConversionUtils.getIdentifiers( DataElementCategoryOptionCombo.class, categoryOption.getCategoryOptionCombos() ) );
-
- final String sql = "SELECT dataelementid, periodid, organisationunitid, SUM(value) FROM aggregateddatavalue " +
- "WHERE dataelementid IN (" + dataElementIds + ") AND categoryoptioncomboid IN (" + cocIds +
- ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds +
- ") GROUP BY dataelementid, periodid, organisationunitid"; // Sum of category option combos
-
- ResultSet resultSet = holder.getStatement().executeQuery( sql );
-
- while ( resultSet.next() )
- {
- String id = getIdentifier( getIdentifier( DataElement.class, resultSet.getInt( 1 ) ),
- getIdentifier( Period.class, resultSet.getInt( 2 ) ),
- getIdentifier( OrganisationUnit.class, resultSet.getInt( 3 ) ),
- getIdentifier( DataElementCategoryOption.class, categoryOption.getId() ) );
-
- map.put( id, resultSet.getDouble( 4 ) );
- }
- }
- }
-
- return map;
- }
- catch ( Exception ex )
- {
- throw new RuntimeException( "Failed to get aggregated value map", ex );
- }
- finally
- {
- holder.close();
- }
+ if ( reportTable.hasDataElements() )
+ {
+ final String sql = "SELECT dataelementid, periodid, organisationunitid, SUM(value) FROM aggregateddatavalue " +
+ "WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ") " +
+ "GROUP BY dataelementid, periodid, organisationunitid"; // Sum of category option combos
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ while ( rowSet.next() )
+ {
+ String id = getIdentifier( getIdentifier( DataElement.class, rowSet.getInt( 1 ) ),
+ getIdentifier( Period.class, rowSet.getInt( 2 ) ),
+ getIdentifier( OrganisationUnit.class, rowSet.getInt( 3 ) ) );
+
+ map.put( id, rowSet.getDouble( 4 ) );
+ }
+ }
+
+ if ( reportTable.hasIndicators() )
+ {
+ final String sql = "SELECT indicatorid, periodid, organisationunitid, value FROM aggregatedindicatorvalue " +
+ "WHERE indicatorid IN (" + indicatorIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ while ( rowSet.next() )
+ {
+ String id = getIdentifier( getIdentifier( Indicator.class, rowSet.getInt( 1 ) ),
+ getIdentifier( Period.class, rowSet.getInt( 2 ) ),
+ getIdentifier( OrganisationUnit.class, rowSet.getInt( 3 ) ) );
+
+ map.put( id, rowSet.getDouble( 4 ) );
+ }
+ }
+
+ if ( reportTable.hasDataSets() )
+ {
+ final String sql = "SELECT datasetid, periodid, organisationunitid, value FROM aggregateddatasetcompleteness " +
+ "WHERE datasetid IN (" + dataSetIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ while ( rowSet.next() )
+ {
+ String id = getIdentifier( getIdentifier( DataSet.class, rowSet.getInt( 1 ) ),
+ getIdentifier( Period.class, rowSet.getInt( 2 ) ),
+ getIdentifier( OrganisationUnit.class, rowSet.getInt( 3 ) ) );
+
+ map.put( id, rowSet.getDouble( 4 ) );
+ }
+ }
+
+ if ( reportTable.isDimensional() )
+ {
+ final String sql = "SELECT dataelementid, categoryoptioncomboid, periodid, organisationunitid, value FROM aggregateddatavalue " +
+ "WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ while ( rowSet.next() )
+ {
+ String id = getIdentifier( getIdentifier( DataElement.class, rowSet.getInt( 1 ) ),
+ getIdentifier( DataElementCategoryOptionCombo.class, rowSet.getInt( 2 ) ),
+ getIdentifier( Period.class, rowSet.getInt( 3 ) ),
+ getIdentifier( OrganisationUnit.class, rowSet.getInt( 4 ) ) );
+
+ map.put( id, rowSet.getDouble( 5 ) );
+ }
+ }
+
+ if ( reportTable.doTotal() )
+ {
+ for ( DataElementCategoryOption categoryOption : reportTable.getCategoryCombo().getCategoryOptions() )
+ {
+ String cocIds = TextUtils.getCommaDelimitedString(
+ ConversionUtils.getIdentifiers( DataElementCategoryOptionCombo.class, categoryOption.getCategoryOptionCombos() ) );
+
+ final String sql = "SELECT dataelementid, periodid, organisationunitid, SUM(value) FROM aggregateddatavalue " +
+ "WHERE dataelementid IN (" + dataElementIds + ") AND categoryoptioncomboid IN (" + cocIds +
+ ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds +
+ ") GROUP BY dataelementid, periodid, organisationunitid"; // Sum of category option combos
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ while ( rowSet.next() )
+ {
+ String id = getIdentifier( getIdentifier( DataElement.class, rowSet.getInt( 1 ) ),
+ getIdentifier( Period.class, rowSet.getInt( 2 ) ),
+ getIdentifier( OrganisationUnit.class, rowSet.getInt( 3 ) ),
+ getIdentifier( DataElementCategoryOption.class, categoryOption.getId() ) );
+
+ map.put( id, rowSet.getDouble( 4 ) );
+ }
+ }
+ }
+
+ return map;
}
public Map<String, Double> getAggregatedValueMap( Chart chart )
=== modified file 'dhis-2/dhis-services/dhis-service-reporting/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-services/dhis-service-reporting/src/main/resources/META-INF/dhis/beans.xml 2011-12-21 20:06:49 +0000
+++ dhis-2/dhis-services/dhis-service-reporting/src/main/resources/META-INF/dhis/beans.xml 2011-12-22 16:18:28 +0000
@@ -14,7 +14,6 @@
</bean>
<bean id="org.hisp.dhis.reporttable.jdbc.ReportTableManager" class="org.hisp.dhis.reporttable.jdbc.JDBCReportTableManager">
- <property name="statementManager" ref="statementManager" />
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
@@ -85,10 +84,11 @@
<property name="periodService" ref="org.hisp.dhis.period.PeriodService" />
<property name="dataValueService" ref="org.hisp.dhis.datavalue.DataValueService" />
<property name="minMaxDataElementService" ref="org.hisp.dhis.minmax.MinMaxDataElementService" />
- <property name="aggregationService" ref="org.hisp.dhis.aggregation.AggregationService" />
- <property name="systemSettingManager" ref="org.hisp.dhis.options.SystemSettingManager" />
<property name="currentUserService" ref="org.hisp.dhis.user.CurrentUserService" />
<property name="reportTableManager" ref="org.hisp.dhis.reporttable.jdbc.ReportTableManager" />
+ <property name="aggregationService" ref="org.hisp.dhis.aggregation.AggregationService" />
+ <property name="systemSettingManager" ref="org.hisp.dhis.options.SystemSettingManager" />
+ <property name="statementManager" ref="statementManager" />
</bean>
<!-- Document -->
