dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #22045
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 10567: Sql view, refactored data retrieval methods to use spring jdbc template
------------------------------------------------------------
revno: 10567
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2013-04-15 20:04:45 +0200
message:
Sql view, refactored data retrieval methods to use spring jdbc template
modified:
dhis-2/dhis-api/pom.xml
dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java
dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java
--
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-api/pom.xml'
--- dhis-2/dhis-api/pom.xml 2013-04-15 15:49:53 +0000
+++ dhis-2/dhis-api/pom.xml 2013-04-15 18:04:45 +0000
@@ -34,8 +34,7 @@
<dependency>
<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports</artifactId>
- </dependency>
-
+ </dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
@@ -51,6 +50,10 @@
<dependency>
<groupId>org.smslib</groupId>
<artifactId>smslib</artifactId>
+ </dependency><dependency>
+ <groupId>org.springframework</groupId>
+ <artifactId>spring-jdbc</artifactId>
+ <version>${spring.version}</version>
</dependency>
</dependencies>
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java 2013-03-21 15:07:48 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java 2013-04-15 18:04:45 +0000
@@ -31,6 +31,8 @@
import java.util.List;
import java.util.Map;
+import org.springframework.jdbc.support.rowset.SqlRowSet;
+
import net.sf.jasperreports.engine.JRDataSource;
/**
@@ -257,6 +259,13 @@
* @param rs the result set.
*/
Grid addHeaders( ResultSet rs );
+
+ /**
+ * Adds a set of headers based on the column names of the given SQL row set.
+ *
+ * @param rs the result set.
+ */
+ Grid addHeaders( SqlRowSet rs );
/**
* Moves the cursor the next row and adds values for each column of the given
@@ -265,4 +274,12 @@
* @param rs the result set.
*/
Grid addRows( ResultSet rs );
+
+ /**
+ * Moves the cursor the next row and adds values for each column of the given
+ * SQL row set.
+ *
+ * @param rs the row set.
+ */
+ Grid addRows( SqlRowSet rs );
}
=== 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 2013-04-15 17:43:49 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2013-04-15 18:04:45 +0000
@@ -27,10 +27,6 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
-import java.sql.Connection;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
import java.util.Map;
import org.apache.commons.logging.Log;
@@ -41,6 +37,7 @@
import org.hisp.dhis.system.util.SqlHelper;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.support.rowset.SqlRowSet;
/**
* @author Dang Duy Hieu
@@ -54,7 +51,6 @@
private static final String PREFIX_CREATEVIEW_QUERY = "CREATE VIEW ";
private static final String PREFIX_DROPVIEW_QUERY = "DROP VIEW IF EXISTS ";
private static final String PREFIX_SELECT_QUERY = "SELECT * FROM ";
- private static final String[] types = { "VIEW" };
// -------------------------------------------------------------------------
// Dependencies
@@ -101,9 +97,9 @@
{
jdbcTemplate.execute( sql );
}
- catch ( BadSqlGrammarException bge )
+ catch ( BadSqlGrammarException ex )
{
- return bge.getCause().getMessage();
+ return ex.getCause().getMessage();
}
return null;
@@ -126,17 +122,10 @@
log.info( "Get view SQL: " + sql );
- try
- {
- ResultSet rs = getResultSet( sql );
-
- grid.addHeaders( rs );
- grid.addRows( rs );
- }
- catch ( SQLException e )
- {
- throw new RuntimeException( "Failed to get data from view " + viewTableName, e );
- }
+ SqlRowSet rs = jdbcTemplate.queryForRowSet( sql );
+
+ grid.addHeaders( rs );
+ grid.addRows( rs );
}
@Override
@@ -154,7 +143,7 @@
dropViewTable( viewNameCheck );
}
- catch ( Exception ex )
+ catch ( BadSqlGrammarException ex )
{
return ex.getCause().getMessage();
}
@@ -169,24 +158,9 @@
{
jdbcTemplate.update( PREFIX_DROPVIEW_QUERY + viewName );
}
- catch ( Exception ex )
+ catch ( BadSqlGrammarException ex )
{
throw new RuntimeException( "Failed to drop view: " + viewName, ex );
}
}
-
- // -------------------------------------------------------------------------
- // Supporting methods
- // -------------------------------------------------------------------------
-
- /**
- * Obtains a scrollable, read-only result set based on the query string.
- */
- private ResultSet getResultSet( String sql )
- throws SQLException
- {
- Connection con = jdbcTemplate.getDataSource().getConnection();
- Statement stm = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
- return stm.executeQuery( sql );
- }
}
\ No newline at end of file
=== modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java'
--- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java 2013-03-21 15:07:48 +0000
+++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java 2013-04-15 18:04:45 +0000
@@ -27,24 +27,33 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
-import com.fasterxml.jackson.annotation.JsonProperty;
-import com.fasterxml.jackson.annotation.JsonView;
-import com.fasterxml.jackson.databind.annotation.JsonSerialize;
+import static org.hisp.dhis.system.util.MathUtils.getRounded;
+
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.Comparator;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRField;
+
import org.apache.commons.math.stat.regression.SimpleRegression;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.common.GridHeader;
import org.hisp.dhis.common.adapter.JacksonRowDataSerializer;
import org.hisp.dhis.common.view.DetailedView;
import org.hisp.dhis.system.util.MathUtils;
-
-import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
-import java.sql.SQLException;
-import java.util.*;
-
-import static org.hisp.dhis.system.util.MathUtils.getRounded;
+import org.springframework.jdbc.support.rowset.SqlRowSet;
+import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
+
+import com.fasterxml.jackson.annotation.JsonProperty;
+import com.fasterxml.jackson.annotation.JsonView;
+import com.fasterxml.jackson.databind.annotation.JsonSerialize;
/**
* @author Lars Helge Overland
@@ -624,6 +633,20 @@
return this;
}
+ public Grid addHeaders( SqlRowSet rs )
+ {
+ SqlRowSetMetaData rsmd = rs.getMetaData();
+
+ int columnNo = rsmd.getColumnCount();
+
+ for ( int i = 1; i <= columnNo; i++ )
+ {
+ addHeader( new GridHeader( rsmd.getColumnLabel( i ), false, false ) );
+ }
+
+ return this;
+ }
+
public Grid addRows( ResultSet rs )
{
try
@@ -647,6 +670,23 @@
return this;
}
+
+ public Grid addRows( SqlRowSet rs )
+ {
+ int cols = rs.getMetaData().getColumnCount();
+
+ while ( rs.next() )
+ {
+ addRow();
+
+ for ( int i = 1; i <= cols; i++ )
+ {
+ addValue( rs.getObject( i ) );
+ }
+ }
+
+ return this;
+ }
// -------------------------------------------------------------------------
// Supportive methods
