Conditionally adding text to a the middle of a string builder between 2 specified points

Question

I have a StringBuilder() sb that contains the following text:

SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation
FROM Program p
INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId
INNER JOIN Category c on c.CategoryId = r.CategoryId
WHERE
p.ProgramName = 'Jubilee Baltimore'
c.CategoryName = 'Community Stability and Growth'
p.BaseLocation = 'Community Based'
ORDER BY p.ProgramName;

In order for this query to be executed I need to add the word AND in the where clause between the first parameter and every addition one. Because there are very many possible where parameters and it is unknown which ones will and won't be null this is an efficient method of achieving my goal. So the desired outcome would be:

SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation
FROM Program p
INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId
INNER JOIN Category c on c.CategoryId = r.CategoryId
WHERE
p.ProgramName = 'Jubilee Baltimore'
AND
c.CategoryName = 'Community Stability and Growth'
AND
p.BaseLocation = 'Community Based'
ORDER BY p.ProgramName;

How could I do something like sb.insert([every new line break after 2nd line break after 'WHERE' but before 'ORDER'], 'AND');

So an alternate example of a desired outcome might also look like:

SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation, p.Support
FROM Program p
INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId
INNER JOIN Category c on c.CategoryId = r.CategoryId
WHERE
c.CategoryName = 'Community Stability and Growth'
AND
p.BaseLocation = 'Community Based'
AND
p.Support= 'Financially Supported'
ORDER BY p.ProgramName;

So I need to add AND after every line break after the 2nd line break after 'WHERE` and stop at the word 'ORDER.

I know that is incredibly specific so other ways to achieve this are welcome. It is also worth noting that WHERE and it's line break will always be at the same position so I can get that position pretty easily. ORDER on the other hand will always be at a different position but will always be the last line.

EDIT: I'm going to show the entire function in which I am doing this for clarity:

   @Override
    public List<ProgramTable> getProgramsByAdvancedSearch(SearchModel searchContext) {
        System.out.println(searchContext.getName());

        StringBuilder sb = new StringBuilder();

        sb.append("SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation\n" +
                "FROM Program p\n" +
                "INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId\n" +
                "INNER JOIN Category c on c.CategoryId = r.CategoryId\n" +
                "WHERE\n");

        if(searchContext.getName() != null){
            sb.append("p.ProgramName = " + "'" + searchContext.getName() + "'" + "\n");
        }

        if(searchContext.getCategory() != null){
            sb.append("c.CategoryName = " + "'" + searchContext.getCategory() + "'" + "\n");
        }

        sb.append("ORDER BY p.ProgramName;");

        //The line break after WHERE is character number 241
        //Would like to do the AND insert here

      
        //Getting "java.lang.NullPointerException: null" here
        Query query = entityManager.createNativeQuery(sb.toString());

        List<ProgramTable> searchList = query.getResultList();

        return searchList;
    }

UPDATE: I have added all the parameters I need and am using the code provided in the first answer but I am getting an additional AND that I don't want:

@Override
    public List<ProgramTable> getProgramsByAdvancedSearch(SearchModel searchContext) {

        String baseStatement = "SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation, s.SupportDescription, p.StreetAddress, p.AreaServed\n" +
                "FROM Program p\n" +
                "INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId\n" +
                "INNER JOIN Category c on c.CategoryId = r.CategoryId\n" +
                "INNER JOIN Ref_ProgramSupport rs on rs.ProgramId = p.ProgramId\n" +
                "INNER JOIN Support s on s.SupportId = rs.SupportId\n" +
                "WHERE\n";

        List<String> clauses = new ArrayList<>();
        if(searchContext.getName() != null){
            clauses.add("p.ProgramName = " + "'" + searchContext.getName() + "'" + "\n");
        }

        if(searchContext.getCategory() != null){
            clauses.add("c.CategoryName = " + "'" + searchContext.getCategory() + "'" + "\n");
        }

        if(searchContext.getBaseLocation() != null){
            clauses.add("p.BaseLocation = " + "'" + searchContext.getBaseLocation() + "'" + "\n");
        }

        //Might need to strip the brackets off the result here
        if(searchContext.getSupport() != null){
            clauses.add("s.SupportDescription = " + "'" + searchContext.getSupport() + "'" + "\n");
        }

        if(searchContext.getAddress() != null){
            clauses.add("p.StreetAddress = " + "'" + searchContext.getAddress() + "'" + "\n");
        }

        if(searchContext.getAreaServed() != null){
            clauses.add("p.AreaServed = " + "'" + searchContext.getAreaServed() + "'" + "\n");
        }

        StringBuilder sb = new StringBuilder(baseStatement);
        for (int i = 0; i < clauses.size() - 2 ; i++) {
            sb.append(clauses.get(i));
            sb.append("AND\n");
        }

        sb.append("ORDER BY p.ProgramName;");

        System.out.println(clauses);
        System.out.println(sb);

        //Getting "java.lang.NullPointerException: null" here
        Query query = entityManager.createNativeQuery(sb.toString());

        List<ProgramTable> searchList = query.getResultList();

        return searchList;
    }

Result:

SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation, s.SupportDescription, p.StreetAddress, p.AreaServed
FROM Program p
INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId
INNER JOIN Category c on c.CategoryId = r.CategoryId
INNER JOIN Ref_ProgramSupport rs on rs.ProgramId = p.ProgramId
INNER JOIN Support s on s.SupportId = rs.SupportId
WHERE
p.ProgramName = 'Jubilee Baltimore  '
AND
c.CategoryName = 'Community Stability and Growth'
AND
p.BaseLocation = 'Community Based '
AND
s.SupportDescription = '[Financially Supported]'
AND
ORDER BY p.ProgramName;

Answer 1

TL:DR Your solution

@Override
public List<ProgramTable> getProgramsByAdvancedSearch(SearchModel searchContext) {
    System.out.println(searchContext.getName());

    String baseStatement = "SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation\n" +
        "FROM Program p\n" +
        "INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId\n" +
        "INNER JOIN Category c on c.CategoryId = r.CategoryId\n" +
        "WHERE\n";

    List<String> clauses = new ArrayList<>();
    if(searchContext.getName() != null){
        clauses.add("p.ProgramName = " + "'" + searchContext.getName() + "'" + "\n");
    }

    if(searchContext.getCategory() != null){
        clauses.add("c.CategoryName = " + "'" + searchContext.getCategory() + "'" + "\n");
    }

    StringBuilder sb = new StringBuilder(baseStatement);
    for (int i = 0; i < clauses.size(); i++) {
        sb.append(clauses.get(i));
        if (i < clauses.size() - 1) {
            sb.append("AND\n");
        }
    }

    sb.append("ORDER BY p.ProgramName;");

    // The line break after WHERE is character number 241
    // Would like to do the AND insert here

    // Getting "java.lang.NullPointerException: null" here
    Query query = entityManager.createNativeQuery(sb.toString());

    List<ProgramTable> searchList = query.getResultList();

    return searchList;
}

---

There's quite a few to unpack here, but I will just focus on the main problem (Adding "AND" in the middle of the string between two specific points). Since this method (procedurally) creates the SQL statement, you could do something like this:

if(searchContext.getName() != null){
    sb.append("p.ProgramName = " + "'" + searchContext.getName() + "'" + "\n");
}

if(searchContext.getCategory() != null){
    if (searchContext.getName() != null) {
        sb.append("AND\n");
    sb.append("c.CategoryName = " + "'" + searchContext.getCategory() + "'" + "\n");
}

And you can continue that pattern in as many if statements until you reach the ORDER by line. This is the simplest solution, albeit not the most elegant.

Alternatively, you can build the SQL statement completely and call the toString() method on the StringBuilder object and then figure out a way to insert "AND" into the resulting string in as many places you need to.

String sqlStatement = sb.toString();
// Call String's replace(...), replaceFirst(...), or replaceAll(...) here

Obviously, this is more difficult because you will have to know either the precise index where the insertion need to occur, the character sequence to replace, or the regular expression to match to make the insertion at the correct location. This is why the I believe the first option I gave you is the correct way to approach this.

Now.... If you break this down into separate string buffers, you will get more flexibility. For example, this

sb.append("SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation\n" +
    "FROM Program p\n" +
    "INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId\n" +
    "INNER JOIN Category c on c.CategoryId = r.CategoryId\n" +
    "WHERE\n");

can be its own String since it is all hard-coded.

String baseStatement = "SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocation\n" +
    "FROM Program p\n" +
    "INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramId\n" +
    "INNER JOIN Category c on c.CategoryId = r.CategoryId\n" +
    "WHERE\n";

Then, you can capture the subsequent items on a list:

List<String> clauses = new ArrayList<>();
if(searchContext.getName() != null){
    clauses.add("p.ProgramName = " + "'" + searchContext.getName() + "'" + "\n");
}

if(searchContext.getCategory() != null){
    clauses.add("c.CategoryName = " + "'" + searchContext.getCategory() + "'" + "\n");
}
// many other clauses

Lastly, you can iterate through the list of clauses and add "AND" in between them. This way, you don't need to check if the previous clause exist to add the "AND". This is especially helpful if you have more than two clauses and some clauses in the middle are missing.

StringBuilder sb = new StringBuilder(baseStatement);
for (int i = 0; i < clauses.size(); i++) {
    sb.append(clauses.get(i));
    if (i < clauses.size() - 1) {
        sb.append("AND\n");
    }
}

You iterate through the entire list, but stop inserting "AND" at size() - 1 so that you only add "AND" up to the second to last clause. After this, you are ready to call sb.toString().

P.S. There is no way to append() to a StringBuffer at some arbitrary point. You can only append to the end of the buffer. You can insert() or replace() but again, you will need to know the exact index location where you want to insert. This is not an easy task. For cases like that, it works well if you can place weird character patterns that you will know won't exist any other spot in your string and then you can replace all occurrences of that character sequence (pattern) with the string value you want; in this case, "AND".