dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #18279
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 7649: Data browser and Sql View - Replaced StatementManger by JdbcTemplate.
------------------------------------------------------------
revno: 7649
committer: Hieu <hieu.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2012-07-20 16:12:57 +0700
message:
Data browser and Sql View - Replaced StatementManger by JdbcTemplate.
modified:
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/JDBCDataBrowserStore.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/util/DataBrowserUtils.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java
dhis-2/dhis-services/dhis-service-administration/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-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/JDBCDataBrowserStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/JDBCDataBrowserStore.java 2011-12-11 13:47:04 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/JDBCDataBrowserStore.java 2012-07-20 09:12:57 +0000
@@ -2,7 +2,6 @@
import java.util.List;
-import org.amplecode.quick.StatementManager;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.common.GridHeader;
import org.hisp.dhis.databrowser.DataBrowserGridStore;
@@ -10,6 +9,7 @@
import org.hisp.dhis.jdbc.StatementBuilder;
import org.hisp.dhis.organisationunit.OrganisationUnitService;
import org.hisp.dhis.system.grid.ListGrid;
+import org.springframework.jdbc.core.JdbcTemplate;
/**
* @author joakibj, martinwa, briane, eivinhb
@@ -23,11 +23,11 @@
// Dependencies
// -------------------------------------------------------------------------
- private StatementManager statementManager;
+ private JdbcTemplate jdbcTemplate;
- public void setStatementManager( StatementManager statementManager )
+ public void setJdbcTemplate( JdbcTemplate jdbcTemplate )
{
- this.statementManager = statementManager;
+ this.jdbcTemplate = jdbcTemplate;
}
private OrganisationUnitService organisationUnitService;
@@ -72,7 +72,7 @@
dataSetGrid.addHeader( new GridHeader( "drilldown_data_set", false, false ) );
dataSetGrid.addHeader( new GridHeader( "counts_of_aggregated_values", false, false ) );
- fillUpDataBasic( dataSetGrid, sqlsb, isZeroAdded, statementManager );
+ fillUpDataBasic( dataSetGrid, sqlsb, isZeroAdded, jdbcTemplate );
return dataSetGrid;
}
@@ -95,7 +95,7 @@
gridDEG.addHeader( new GridHeader( "drilldown_data_element_group", false, false ) );
gridDEG.addHeader( new GridHeader( "counts_of_aggregated_values", false, false ) );
- fillUpDataBasic( gridDEG, sqlsb, isZeroAdded, statementManager );
+ fillUpDataBasic( gridDEG, sqlsb, isZeroAdded, jdbcTemplate );
return gridDEG;
}
@@ -118,7 +118,7 @@
gridOUG.addHeader( new GridHeader( "drilldown_orgunit_group", false, false ) );
gridOUG.addHeader( new GridHeader( "counts_of_aggregated_values", false, false ) );
- fillUpDataBasic( gridOUG, sqlsb, isZeroAdded, statementManager );
+ fillUpDataBasic( gridOUG, sqlsb, isZeroAdded, jdbcTemplate );
return gridOUG;
}
@@ -138,7 +138,7 @@
sqlsb.append( "ORDER BY de.name) " );
grid.addHeader( new GridHeader( "drilldown_data_element", false, false ) );
- setMetaStructure( grid, sqlsb, metaIds, statementManager );
+ setMetaStructure( grid, sqlsb, metaIds, jdbcTemplate );
}
public void setDataElementStructureForDataElementGroup( Grid grid, Integer dataElementGroupId, List<Integer> metaIds )
@@ -153,7 +153,7 @@
sqlsb.append( "ORDER BY de.name) " );
grid.addHeader( new GridHeader( "drilldown_data_element", false, false ) );
- setMetaStructure( grid, sqlsb, metaIds, statementManager );
+ setMetaStructure( grid, sqlsb, metaIds, jdbcTemplate );
}
public void setDataElementGroupStructureForOrgUnitGroup( Grid grid, Integer orgUnitGroupId, List<Integer> metaIds )
@@ -171,7 +171,7 @@
sqlsb.append( "ORDER BY deg.name ASC) " );
grid.addHeader( new GridHeader( "drilldown_data_element_group", false, false ) );
- setMetaStructure( grid, sqlsb, metaIds, statementManager );
+ setMetaStructure( grid, sqlsb, metaIds, jdbcTemplate );
}
@@ -185,7 +185,7 @@
sqlsb.append( "ORDER BY o.name)" );
grid.addHeader( new GridHeader( "drilldown_orgunit", false, false ) );
- setMetaStructure( grid, sqlsb, metaIds, statementManager );
+ setMetaStructure( grid, sqlsb, metaIds, jdbcTemplate );
}
public void setDataElementStructureForOrgUnit( Grid grid, Integer orgUnitId, List<Integer> metaIds )
@@ -195,7 +195,7 @@
sqlsb.append( statementBuilder.queryDataElementStructureForOrgUnit() );
grid.addHeader( new GridHeader( "drilldown_data_element", false, false ) );
- setMetaStructure( grid, sqlsb, metaIds, statementManager );
+ setMetaStructure( grid, sqlsb, metaIds, jdbcTemplate );
}
// -------------------------------------------------------------------------
@@ -229,7 +229,7 @@
sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" : " UNION " );
}
- return fillUpDataAdvance( grid, sqlsb, metaIds, isZeroAdded, statementManager );
+ return fillUpDataAdvance( grid, sqlsb, metaIds, isZeroAdded, jdbcTemplate );
}
public Integer setCountDataElementsForDataElementGroupBetweenPeriods( Grid grid, Integer dataElementGroupId,
@@ -254,7 +254,7 @@
sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" : " UNION " );
}
- return fillUpDataAdvance( grid, sqlsb, metaIds, isZeroAdded, statementManager );
+ return fillUpDataAdvance( grid, sqlsb, metaIds, isZeroAdded, jdbcTemplate );
}
public Integer setCountDataElementGroupsForOrgUnitGroupBetweenPeriods( Grid grid, Integer orgUnitGroupId,
@@ -283,7 +283,7 @@
sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY ColumnHeader" : " UNION " );
}
- return fillUpDataAdvance( grid, sqlsb, metaIds, isZeroAdded, statementManager );
+ return fillUpDataAdvance( grid, sqlsb, metaIds, isZeroAdded, jdbcTemplate );
}
public Integer setCountOrgUnitsBetweenPeriods( Grid grid, Integer orgUnitParent, List<Integer> betweenPeriodIds,
@@ -294,7 +294,7 @@
boolean valid = this.setUpQueryForDrillDownDescendants( sqlsbDescentdants, orgUnitParent, betweenPeriodIds,
maxLevel );
- return (valid ? fillUpDataAdvance( grid, sqlsbDescentdants, metaIds, isZeroAdded, statementManager ) : 0);
+ return (valid ? fillUpDataAdvance( grid, sqlsbDescentdants, metaIds, isZeroAdded, jdbcTemplate ) : 0);
}
@@ -307,7 +307,7 @@
sqlsb.append( statementBuilder.queryRawDataElementsForOrgUnitBetweenPeriods( orgUnitId, betweenPeriodIds ) );
- return fillUpDataAdvance( grid, sqlsb, metaIds, isZeroAdded, statementManager );
+ return fillUpDataAdvance( grid, sqlsb, metaIds, isZeroAdded, jdbcTemplate );
}
// -------------------------------------------------------------------------
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/util/DataBrowserUtils.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/util/DataBrowserUtils.java 2011-04-13 04:23:59 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/util/DataBrowserUtils.java 2012-07-20 09:12:57 +0000
@@ -27,7 +27,6 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
-
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
@@ -35,30 +34,28 @@
import java.util.ArrayList;
import java.util.List;
-import org.amplecode.quick.StatementHolder;
-import org.amplecode.quick.StatementManager;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.common.GridHeader;
import org.hisp.dhis.databrowser.MetaValue;
+import org.springframework.jdbc.core.JdbcTemplate;
/**
* @author Dang Duy Hieu
* @version $Id$
*/
public class DataBrowserUtils
-{ protected static final Log log = LogFactory.getLog( DataBrowserUtils.class );
- public static void setMetaStructure( Grid grid, StringBuffer sqlsb, List<Integer> metaIds,
- StatementManager statementManager )
+{
+ protected static final Log log = LogFactory.getLog( DataBrowserUtils.class );
+
+ public static void setMetaStructure( Grid grid, StringBuffer sqlsb, List<Integer> metaIds, JdbcTemplate jdbcTemplate )
{
- final StatementHolder holder = statementManager.getHolder();
-
try
{
Integer metaId = null;
String metaName = null;
- ResultSet resultSet = getScrollableResult( sqlsb.toString(), holder );
+ ResultSet resultSet = getScrollableResult( sqlsb.toString(), jdbcTemplate );
while ( resultSet.next() )
{
@@ -70,21 +67,17 @@
}
}
catch ( SQLException e )
- { log.error( "Failed to add meta value\n" + sqlsb.toString());
- throw new RuntimeException( "Failed to add meta value\n" , e );
+ {
+ log.error( "Failed to add meta value\n" + sqlsb.toString() );
+ throw new RuntimeException( "Failed to add meta value\n", e );
}
catch ( Exception e )
{
throw new RuntimeException( "Oops. Something else went wrong in setMetaStructure()", e );
}
- finally
- {
- holder.close();
- }
}
- public static void setHeaderStructure( Grid grid, ResultSet resultSet, List<Integer> headerIds,
- boolean isZeroAdded, StatementManager statementManager )
+ public static void setHeaderStructure( Grid grid, ResultSet resultSet, List<Integer> headerIds, boolean isZeroAdded )
{
try
{
@@ -111,7 +104,7 @@
}
}
catch ( SQLException e )
- {
+ {
throw new RuntimeException( "Failed to add header\n", e );
}
catch ( Exception e )
@@ -120,16 +113,13 @@
}
}
- public static int fillUpDataBasic( Grid grid, StringBuffer sqlsb, boolean isZeroAdded,
- StatementManager statementManager )
+ public static int fillUpDataBasic( Grid grid, StringBuffer sqlsb, boolean isZeroAdded, JdbcTemplate jdbcTemplate )
{
- final StatementHolder holder = statementManager.getHolder();
-
int countRows = 0;
try
{
- ResultSet resultSet = getScrollableResult( sqlsb.toString(), holder );
+ ResultSet resultSet = getScrollableResult( sqlsb.toString(), jdbcTemplate );
while ( resultSet.next() )
{
@@ -140,26 +130,20 @@
}
catch ( SQLException e )
{
- log.error( "Error executing" + sqlsb.toString());
- throw new RuntimeException( "Failed to get aggregated data value\n" , e );
+ log.error( "Error executing" + sqlsb.toString() );
+ throw new RuntimeException( "Failed to get aggregated data value\n", e );
}
catch ( Exception e )
{
throw new RuntimeException( "Oops. Something else went wrong", e );
}
- finally
- {
- holder.close();
- }
return countRows;
}
public static int fillUpDataAdvance( Grid grid, StringBuffer sqlsb, List<Integer> metaIds, boolean isZeroAdded,
- StatementManager statementManager )
+ JdbcTemplate jdbcTemplate )
{
- final StatementHolder holder = statementManager.getHolder();
-
int countRows = 0;
int rowIndex = -1;
int columnIndex = -1;
@@ -167,10 +151,10 @@
try
{
- ResultSet rs = getScrollableResult( sqlsb.toString(), holder );
+ ResultSet rs = getScrollableResult( sqlsb.toString(), jdbcTemplate );
List<Integer> headerIds = new ArrayList<Integer>();
- setHeaderStructure( grid, rs, headerIds, isZeroAdded, statementManager );
+ setHeaderStructure( grid, rs, headerIds, isZeroAdded );
if ( rs.first() != true )
{
@@ -191,7 +175,7 @@
}
catch ( SQLException e )
{
- log.error( "Error executing" + sqlsb.toString());
+ log.error( "Error executing" + sqlsb.toString() );
throw new RuntimeException( "Failed to get aggregated data value\n", e );
}
@@ -199,16 +183,10 @@
{
throw new RuntimeException( "Oops. Somthing else went wrong", e );
}
- finally
- {
- holder.close();
- }
return countRows;
}
-
-
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
@@ -221,18 +199,20 @@
* @param holder the StatementHolder object
* @return null or the ResultSet
*/
- private static ResultSet getScrollableResult( String sql, StatementHolder holder )
+ private static ResultSet getScrollableResult( String sql, JdbcTemplate jdbcTemplate )
throws SQLException
{
- Connection con = holder.getConnection();
+ Connection con = jdbcTemplate.getDataSource().getConnection();
Statement stm = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
stm.execute( sql );
log.debug( sql );
+
return stm.getResultSet();
}
private static String checkValue( String value, boolean isZeroAdded )
- { if ( value == null )
+ {
+ if ( value == null )
{
return "null";
}
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2012-04-04 10:48:27 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2012-07-20 09:12:57 +0000
@@ -37,8 +37,6 @@
import java.util.Set;
import java.util.regex.Pattern;
-import org.amplecode.quick.StatementHolder;
-import org.amplecode.quick.StatementManager;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.sqlview.SqlView;
import org.hisp.dhis.sqlview.SqlViewExpandStore;
@@ -68,13 +66,6 @@
// Dependencies
// -------------------------------------------------------------------------
- private StatementManager statementManager;
-
- public void setStatementManager( StatementManager statementManager )
- {
- this.statementManager = statementManager;
- }
-
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate( JdbcTemplate jdbcTemplate )
@@ -89,13 +80,12 @@
@Override
public Collection<String> getAllSqlViewNames()
{
- final StatementHolder holder = statementManager.getHolder();
DatabaseMetaData mtdt;
Set<String> viewersName = new HashSet<String>();
try
{
- mtdt = holder.getConnection().getMetaData();
+ mtdt = jdbcTemplate.getDataSource().getConnection().getMetaData();
ResultSet rs = mtdt.getTables( null, null, PREFIX_VIEWNAME + "%", types );
@@ -103,16 +93,11 @@
{
viewersName.add( rs.getString( "TABLE_NAME" ) );
}
-
}
catch ( SQLException e )
{
e.printStackTrace();
}
- finally
- {
- holder.close();
- }
return viewersName;
@@ -121,12 +106,11 @@
@Override
public boolean isViewTableExists( String viewTableName )
{
- final StatementHolder holder = statementManager.getHolder();
DatabaseMetaData mtdt;
try
{
- mtdt = holder.getConnection().getMetaData();
+ mtdt = jdbcTemplate.getDataSource().getConnection().getMetaData();
ResultSet rs = mtdt.getTables( null, null, viewTableName.toLowerCase(), types );
return rs.next();
@@ -135,10 +119,6 @@
{
return false;
}
- finally
- {
- holder.close();
- }
}
@Override
@@ -163,12 +143,11 @@
@Override
public void setUpDataSqlViewTable( Grid grid, String viewTableName )
{
- final StatementHolder holder = statementManager.getHolder();
-
ResultSet rs;
+
try
{
- rs = this.getScrollableResult( PREFIX_SELECT_QUERY + viewTableName, holder );
+ rs = this.getScrollableResult( PREFIX_SELECT_QUERY + viewTableName, jdbcTemplate );
}
catch ( SQLException e )
{
@@ -177,8 +156,6 @@
grid.addHeaders( rs );
grid.addRow( rs );
-
- holder.close();
}
@Override
@@ -214,20 +191,14 @@
@Override
public void dropViewTable( String viewName )
{
- final StatementHolder holder = statementManager.getHolder();
-
try
{
- holder.getStatement().executeUpdate( PREFIX_DROPVIEW_QUERY + viewName );
+ jdbcTemplate.update( PREFIX_DROPVIEW_QUERY + viewName );
}
- catch ( SQLException ex )
+ catch ( Exception ex )
{
throw new RuntimeException( "Failed to drop view: " + viewName, ex );
}
- finally
- {
- holder.close();
- }
}
// -------------------------------------------------------------------------
@@ -242,10 +213,10 @@
* @param holder the StatementHolder object
* @return null or the ResultSet
*/
- private ResultSet getScrollableResult( String sql, StatementHolder holder )
+ private ResultSet getScrollableResult( String sql, JdbcTemplate jdbcTemplate )
throws SQLException
{
- Connection con = holder.getConnection();
+ Connection con = jdbcTemplate.getDataSource().getConnection();
Statement stm = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
stm.execute( sql );
return stm.getResultSet();
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-services/dhis-service-administration/src/main/resources/META-INF/dhis/beans.xml 2012-03-07 11:09:24 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/resources/META-INF/dhis/beans.xml 2012-07-20 09:12:57 +0000
@@ -56,7 +56,7 @@
<bean id="org.hisp.dhis.databrowser.DataBrowserGridStore" class="org.hisp.dhis.databrowser.jdbc.JDBCDataBrowserStore">
<property name="statementBuilder" ref="statementBuilder" />
- <property name="statementManager" ref="statementManager" />
+ <property name="jdbcTemplate" ref="jdbcTemplate" />
<property name="organisationUnitService" ref="org.hisp.dhis.organisationunit.OrganisationUnitService" />
</bean>
@@ -128,7 +128,6 @@
</bean>
<bean id="org.hisp.dhis.sqlview.SqlViewExpandStore" class="org.hisp.dhis.sqlview.jdbc.JdbcSqlViewExpandStore">
- <property name="statementManager" ref="statementManager" />
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
