I need to read in the 'public static NEW_QUERY' from another class, but it has variables inside of the query that is changed when the method is called. How can I call this 'public static' and get the updated QUERY from another class.
Here is the Java Code;
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 PLACE I NEED TO HAVE CORRECTED**********/
/*HOW DO I FORCE THIS TO PUSH VALUES INTO 'match' and 'useFor'*/
/**SO THAT I CAN CALL NEW_QUERY FROM ANOTHER CLASS *****/
/****IT SAYS 'match' and 'useFor' cannot be resolved****/
public static String NEW_QUERY = "select count(userfieldid) from tr_userfield where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'";
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 ******/
count = sql.executeGetInt(con, NEW_QUERY , 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);
}
}
}
In addition to this, which I have not attempted here, but I am supposed to as it is in the specs:
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.
What I am doing is Unit Testing each Query, from another file, to make sure they are correct for data migration from a MSSQL Server 2005 to MYSQL
IS THE BELOW THE BEST SOLUTION GIVEN THE STRING.FORMAT SPEC. IS THIS CORRECT. THE TEST DOES PASS, BUT I THOUGHT I WAS SUPPOSED TO CHANGE AS LITTLE OF THE ORIGINAL CODE AS POSSIBLE
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 PLACE I NEED TO HAVE CORRECTED**********/
/*HOW DO I FORCE THIS TO PUSH VALUES INTO 'match' and 'useFor'*/
/**SO THAT I CAN CALL NEW_QUERY FROM ANOTHER CLASS *****/
/****IT SAYS 'match' and 'useFor' cannot be resolved****/
public static String NEW_QUERY = String.format( "select count(userfieldid) from tr_userfield where calcexpression like %s and usefor like %s", "?", "?");
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 ******/
PreparedStatement stmt = con.prepareStatement(TRBaseScoreCalculator.NEW_QUERY);
stmt.setString(1, "%"+match+"%");
stmt.setString(2, "%"+useFor+"%");
count = sql.executeGetInt(con, stmt.toString() , 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);
}
}
}
Static variables/methods are associated with lass definition and hence you can't use any non-static variable/method in them.
if your match and useFor are not static(that is the case) then you can't have a static qquery like this:
public static String NEW_QUERY = "select count(userfieldid) from tr_userfield where calcexpression like '%" + match + "%'and usefor like '%" + useFor +"%'";
Better to use a place holders in the query as:
public static String NEW_QUERY =
"select count(userfieldid) from tr_userfield "+
" where calcexpression like ? and usefor like ?";
and the place of use, pass the value using query Parameter set methods:
PreparedStatement stmt= con.prepareStatement(TRBaseScoreCalculator.NEW_QUERY);
stmt.setString(1,"%"+match+"%");
stmt.setString(1,"%"+useFor+"%");
EDIT: If you want to use String.format then try below:
public static String NEW_QUERY = "select count(userfieldid) from tr_userfield "+
" where calcexpression like '%s' and usefor like '%s'";
and down the line where you are using the query string, do:
String updatedQuery = String.format(TRBaseScoreCalculator.NEW_QUERY,
"%"+match+"%", "%"+useFor+"%");
count = sql.executeGetInt(con, updatedQuery, null);
But Still I prefer the PraparedStatement route and in that case you don't need to unnecessary String.format.