有条件地将文本添加到字符串生成器的中间两个指定点之间

Question

我有一个包含以下文本的StringBuilder() sb：

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;

为了执行这个查询，我需要在where子句中的第一个参数和每个加法之间添加单词AND。因为有很多可能的参数，而且不知道哪些参数会为空，哪些不会为空，这是实现我目标的有效方法。因此，期望的结果是：

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;

我怎么能做sb.insert([every new line break after 2nd line break after 'WHERE' but before 'ORDER'], 'AND');这样的事情

因此，所需结果的另一个例子可能看起来也像：

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;

因此，我需要在"WHERE"后的第二个换行符后的每个换行符后添加AND，并在单词"ORDER"处停止。

我知道这是非常具体的，所以欢迎其他方式来实现这一点。同样值得注意的是，WHERE和它的换行符总是在同一个位置，所以我可以很容易地得到那个位置。另一方面，ORDER将始终处于不同的位置，但始终是最后一行。

编辑：为了清晰起见，我将展示我所做的整个功能：

@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.BaseLocationn" +
"FROM Program pn" +
"INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramIdn" +
"INNER JOIN Category c on c.CategoryId = r.CategoryIdn" +
"WHEREn");
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;
}

更新：我已经添加了我需要的所有参数，并使用了第一个答案中提供的代码，但我得到了一个我不想要的额外and：

@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.AreaServedn" +
"FROM Program pn" +
"INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramIdn" +
"INNER JOIN Category c on c.CategoryId = r.CategoryIdn" +
"INNER JOIN Ref_ProgramSupport rs on rs.ProgramId = p.ProgramIdn" +
"INNER JOIN Support s on s.SupportId = rs.SupportIdn" +
"WHEREn";
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("ANDn");
}
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;
}

结果：

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您的解决方案

@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.BaseLocationn" +
"FROM Program pn" +
"INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramIdn" +
"INNER JOIN Category c on c.CategoryId = r.CategoryIdn" +
"WHEREn";
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("ANDn");
}
}
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;
}

---

这里有很多需要解包的内容，但我只关注主要问题(在两个特定点之间的字符串中间添加"AND"(。由于此方法(按程序(创建SQL语句，因此可以执行以下操作：

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

您可以在尽可能多的if语句中继续该模式，直到到达ORDER by行。这是最简单的解决方案，尽管不是最优雅的。

或者，您可以完全构建SQL语句，并在StringBuilder对象上调用toString()方法，然后找到插入"；"与"；在所需的多个位置转换为结果字符串。

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

显然，这更困难，因为您必须知道需要插入的精确索引、要替换的字符序列，或者要匹配的正则表达式，才能在正确的位置进行插入。这就是为什么我相信我给你的第一个选择是正确的方法。

现在。。。。如果将其分解为单独的字符串缓冲区，您将获得更大的灵活性。例如，这个

sb.append("SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocationn" +
"FROM Program pn" +
"INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramIdn" +
"INNER JOIN Category c on c.CategoryId = r.CategoryIdn" +
"WHEREn");

可以是它自己的String，因为它都是硬编码的。

String baseStatement = "SELECT p.ProgramId, c.CategoryId, c.CategoryName, p.ProgramName, p.Description, p.Active, p.BaseLocationn" +
"FROM Program pn" +
"INNER JOIN Ref_ProgramCategory r on r.ProgramId = p.ProgramIdn" +
"INNER JOIN Category c on c.CategoryId = r.CategoryIdn" +
"WHEREn";

然后，您可以捕获列表中的后续项目：

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

最后，您可以遍历子句列表并添加"；"与"；在他们之间。这样，您就不需要检查前一个子句是否存在来添加"；"与"；。如果你有两个以上的从句，而中间的一些从句缺失，这尤其有帮助。

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("ANDn");
}
}

您遍历整个列表，但停止插入"；"与"；在CCD_ 9处；"与"；直到倒数第二条。在此之后，您可以拨打sb.toString()。

第页。没有办法在某个任意点上从CCD_ 11到CCD_。只能追加到缓冲区的末尾。您可以使用insert()或replace()，但同样，您需要知道要插入的确切索引位置。这不是一项容易的任务。对于这样的情况，如果你可以在字符串中放置你知道不存在任何其他位置的奇怪字符模式，然后用你想要的字符串值替换该字符序列(模式(的所有出现，效果会很好；在这种情况下；"与"；。