How to move a SELECT statement into a public static

Question

The Specs for this assignment is as follows:

• Locate all embedded sql statements and implement constants for embedded sql which does not already use constants.
• Replace any string concatenation within Sql statement creation with String.format().
• For any String.format commands you must escape out any existing % symbols. These symbols are often used in LIKE operations.

How can I move this SQL query into the 'public static String MY_QUERY' so that it will not be forward referencing and the 'match' and 'useFor' variables can be resolved. What I will do with the MY_QUERY is access it from a JUnit Test and then execute the query against a source and target database to make sure that the query performs the required query on both databases for data migration.

package artemispm.autocalc;

import java.sql.*;
import java.util.*;
import a7.unittests.dao.UnitTestHelper;
import artemispm.serverutil.*;
import artemispm.trdo.*;
import artemispm.parser.*;

public abstract class TRBaseScoreCalculator implements ExpressionParserLookup {

/*****THIS IS THE NEW QUERY THAT WILL BE ACCESSED FROM JUNIT TEST****/

public static String MY_QUERY = 

public Connection               m_con;
protected String                m_characName        = "";

public boolean isThisCharacInUse(Connection con, String characName,String useFor) 
throws SQLException, TRException {
boolean result;        
String match = "[" + TRBaseSql.rewrapQuotes(characName) + "]";

if(TRBaseSql.getDatabaseType(con) == TRBaseSql.DBTYPESQLSERVER) {
    match = "[[]" + TRBaseSql.rewrapQuotes(characName) + "]";
}

TRSystemSQL sql = new TRSystemSQL();
TRSystem sys=sql.getSystem(con);
result =    (   sys.getScoreCalculation() != null 
            &&  sys.getScoreCalculation().indexOf(match) >= 0  );
if (result) return(result);
else {

    int count;

    /*****THIS IS THE PLACE I NEED HELP AT  12/5/2012 ******/
/*I NEED TO SOMEHOW MOVE THE BELOW STATEMENT USING THE SPECS INTO MY_QUERY**/
/**"select count(userfieldid) from tr_userfield where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'"***/

    count = sql.executeGetInt(con, "select count(userfieldid) from tr_userfield where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'" , null);

    if (count > 0) return true;
    count = sql.executeGetInt(con, 
            "select count(characid) from tr_charac where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'" , null);
    return (count > 0);
    }
}
}

Answer 1

You can cheat and do the following:

Constant:

private static final String FORMAT = "select count(userfieldid) " + 
    "from tr_userfield " + 
    "where calcexpression like '%c%s%c' and usefor like '%c%s%c'";

private static final char WILDCARD = '%';

Creating the query:

String calcExpression = "a";
String useFor = "b";

String query = String.format(FORMAT, WILDCARD, calcExpression, WILDCARD, 
    WILDCARD, useFor, WILDCARD);
System.out.println(query);

Which gives you:

select count(userfieldid) from tr_userfield where calcexpression like '%a%' and usefor like '%b%'