dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #29306
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 14771: Improved sql code
------------------------------------------------------------
revno: 14771
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2014-04-10 16:52:30 +0200
message:
Improved sql code
modified:
dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java
dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java
dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/TextUtilsTest.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-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java'
--- dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java 2014-04-08 09:18:14 +0000
+++ dhis-2/dhis-services/dhis-service-tracker/src/main/java/org/hisp/dhis/trackedentity/hibernate/HibernateTrackedEntityInstanceStore.java 2014-04-10 14:52:30 +0000
@@ -30,6 +30,7 @@
import static org.hisp.dhis.common.IdentifiableObjectUtils.getIdentifiers;
import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString;
+import static org.hisp.dhis.system.util.TextUtils.*;
import static org.hisp.dhis.trackedentity.TrackedEntityInstance.PREFIX_PROGRAM;
import static org.hisp.dhis.trackedentity.TrackedEntityInstance.PREFIX_PROGRAM_EVENT_BY_STATUS;
import static org.hisp.dhis.trackedentity.TrackedEntityInstance.PREFIX_PROGRAM_INSTANCE;
@@ -77,7 +78,6 @@
import org.hisp.dhis.program.ProgramStatus;
import org.hisp.dhis.system.grid.GridUtils;
import org.hisp.dhis.system.util.SqlHelper;
-import org.hisp.dhis.system.util.TextUtils;
import org.hisp.dhis.system.util.Timer;
import org.hisp.dhis.trackedentity.TrackedEntityAttribute;
import org.hisp.dhis.trackedentity.TrackedEntityInstance;
@@ -151,7 +151,7 @@
sql += col + ".value as " + col + ", ";
}
- sql = sql.substring( 0, sql.length() - 2 ) + " "; // Remove last comma
+ sql = removeLastComma( sql ) + " ";
// ---------------------------------------------------------------------
// From and where clause
@@ -286,7 +286,7 @@
sql += hlp.whereAnd() + " ous.idlevel" + level + " in (" + getCommaDelimitedString( getIdentifiers( levelOuMap.get( level ) ) ) + ") or ";
}
- sql = sql.substring( 0, sql.length() - 3 ); // Remove last or
+ sql = removeLastOr( sql );
}
else if ( params.isOrganisationUnitMode( OrganisationUnitSelectionMode.ALL ) )
{
@@ -326,7 +326,7 @@
{
sql += hlp.whereAnd() + " (";
- List<String> queryTokens = TextUtils.getTokens( params.getQuery() );
+ List<String> queryTokens = getTokens( params.getQuery() );
for ( String queryToken : queryTokens )
{
@@ -341,10 +341,10 @@
sql += "lower(" + col + ".value) " + regexp + " '" + wordStart + StringUtils.lowerCase( query ) + wordEnd + "' or ";
}
- sql = sql.substring( 0, sql.length() - 3 ) + ") and "; // Remove last or
+ sql = removeLastOr( sql ) + ") and ";
}
- sql = sql.substring( 0, sql.length() - 4 ) + ") "; // Remove last and
+ sql = removeLastAnd( sql ) + ") ";
}
return sql;
@@ -819,7 +819,7 @@
if ( keys[4].equals( "-1" ) )
{
instanceWhere += " and psi.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ + getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
@@ -841,7 +841,7 @@
if ( keys[4].equals( "-1" ) )
{
instanceWhere += " and psi.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ + getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
@@ -863,7 +863,7 @@
if ( keys[4].equals( "-1" ) )
{
instanceWhere += " and p.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ + getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
@@ -885,7 +885,7 @@
if ( keys[4].equals( "-1" ) )
{
instanceWhere += " and p.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ + getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
@@ -906,7 +906,7 @@
if ( keys[4].equals( "-1" ) )
{
instanceWhere += " and psi.organisationunitid in( "
- + TextUtils.getCommaDelimitedString( orgunitChilrenIds ) + " )";
+ + getCommaDelimitedString( orgunitChilrenIds ) + " )";
}
// get events by selected orgunit
@@ -965,9 +965,9 @@
if ( orgunits != null && !isSearchEvent )
{
sql += "(select organisationunitid from trackedentityinstance where trackedentityinstanceid=p.trackedentityinstanceid and organisationunitid in ( "
- + TextUtils.getCommaDelimitedString( getOrganisationUnitIds( orgunits ) ) + " ) ) as orgunitid,";
+ + getCommaDelimitedString( getOrganisationUnitIds( orgunits ) ) + " ) ) as orgunitid,";
otherWhere += operator + "orgunitid in ( "
- + TextUtils.getCommaDelimitedString( getOrganisationUnitIds( orgunits ) ) + " ) ";
+ + getCommaDelimitedString( getOrganisationUnitIds( orgunits ) ) + " ) ";
}
sql = sql.substring( 0, sql.length() - 1 ) + " "; // Removing last comma
=== modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java'
--- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java 2014-04-10 12:43:23 +0000
+++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java 2014-04-10 14:52:30 +0000
@@ -210,6 +210,20 @@
return StringUtils.removeEndIgnoreCase( string, "and" );
}
+
+ /**
+ * Removes the last occurence of comma (",") from the given string,
+ * including potential trailing spaces.
+ *
+ * @param string the string.
+ * @return the chopped string.
+ */
+ public static String removeLastComma( String string )
+ {
+ string = StringUtils.stripEnd( string, " " );
+
+ return StringUtils.removeEndIgnoreCase( string, "," );
+ }
/**
* Trims the given string from the end.
=== modified file 'dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/TextUtilsTest.java'
--- dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/TextUtilsTest.java 2014-04-10 12:43:23 +0000
+++ dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/TextUtilsTest.java 2014-04-10 14:52:30 +0000
@@ -106,4 +106,14 @@
assertEquals( "and name='tom' and name='john' ", TextUtils.removeLastAnd( "and name='tom' and name='john' and " ) );
assertEquals( "and name='tom' and name='john' ", TextUtils.removeLastAnd( "and name='tom' and name='john' and " ) );
}
+
+ @Test
+ public void testRemoveLastComma()
+ {
+ assertEquals( null, TextUtils.removeLastComma( null ) );
+ assertEquals( "", TextUtils.removeLastComma( "" ) );
+ assertEquals( "tom,john", TextUtils.removeLastComma( "tom,john," ) );
+ assertEquals( "tom, john", TextUtils.removeLastComma( "tom, john, " ) );
+ assertEquals( "tom, john", TextUtils.removeLastComma( "tom, john, " ) );
+ }
}
