Question

为了建立数据库系统的目的,我正在使用一个简单的构建器来生成基于用户选择的选择查询。它有几个布尔, 然后进展如下。

    StringBuilder builder = new StringBuilder();
    builder.append("SELECT ");
    if(addOpen)
        builder.append("Open ");
    if(addHigh)
        builder.append("High ");
    if(addLow)
        builder.append("Low ");
    if(addSettle)
        builder.append("Settle ");
    builder.append("FROM " + tableName);

现在,我的问题是无关紧要的——我需要包括逗号,但如果我包括一个逗号,那么它必定会有价值,所以我无法打开,或打开,关闭等等。这个小问题是否有一个完美的解决方案,但对我来说却令人惊讶地困难重重?

Answer 1

我使用的伎俩(在通用半非型伪代码中)是:

 pad = ""    # Empty string

 builder = "SELECT ";
 if (addOpen)
     builder += pad + "Open";   pad = ", ";
 if (addHigh)
     builder += pad + "High";   pad = ", ";
 if (addLow)
     builder += pad + "Low";    pad = ", ";
 if (addSettle)
     builder += pad + "Settle"; pad = ", ";
 builder += "FROM " + TableName;

我所看到的另一种选择是,在条件之后总是包括逗号(或逗号空间),但在添加从条款中添加的字符之前,将最后两个字符从值中剪除。您选择了.即使您正在做输出并且无法解析附件,垫板技术仍然有效。

Answer 2

http://commons.apache.org/lang/api/org/apache/commons/lang3/StringUtils.html#join%28java.lang.html。可以使用,%20char%29" rel=“nofollow” >Apache Commons StringUtils.join () 方法?

Collection<String> selections = Arrays.asList("Open", "Low");
String clause = StringUtils.join(selections,  , );

然后,就刚刚

String sql = "SELECT " + clause + " FROM " + TableName;

Answer 3

1) 典型的情况是,您先验地知道您有多少项目。所以您只需环绕“ n-1” 键, 然后不要在最后一个项目上附加逗号。

(2) 一个可能的解决办法:

  ArrayList<string> items = new ArrayList<String>();
  if(addOpen);
    items.add("Open ");
  if(addHigh)
    items.add("High ");
  if(addLow)
    items.add("Low ");
  if(addSettle)
    items.add("Settle ");

  StringBuilder builder = new StringBuilder();
  int i=0;
  for (i=0; i < items.size() - 1; i++) {
    builder.append(items[i] + ",");
  }
  builder.append(items[i] + "FROM " + tableName);
  ...

Answer 4

有几种方法。第一种,这是我的第一选择, 就是不要构建您的 SQL 语句, 并且不要显示字段。

第二个是,建立字符串,然后删除最后一个逗号。

第三个是将每个字段名称放入数组,然后绕过数组,而不是将末端逗号放在最后一个元素上。

Answer 5

有一个常见的把戏: 总是选择一个常数, 您对此不感兴趣 :

builder.append ("SELECT 1 ");
if (addOpen)
    builder.append (", Open ");
if addHigh)
    builder.append (", High ");
if (addLow)
    builder.append (", Low ");
if (addSettle)
    builder.append (", Settle ");
builder.append ("FROM " + tableName);

另一种办法在另一个方向起作用,有后遗症:

builder.append ("SELECT ");
if (addOpen)
    builder.append ("Open, ");
if (addHigh)
    builder.append ("High, ");
if (addLow)
    builder.append ("Low, ");
if (addSettle)
    builder.append ("Settle, ");
builder.append ("1 FROM " + tableName);

Answer 6

In your situation I use a solution similarto that suggested by Ryan Stewart, but I prefer Google Guava instead of Apache Commons. I prefer this library because I feel that Apache libraries are to gigantic and interlinked. Here below is how I would build your SQL string:

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.base.Preconditions;
import com.google.common.base.Strings;
import java.util.List;

public class SqlJoiner {
    public String buildSql(
            boolean addOpen,
            boolean addHigh,
            boolean addLow,
            boolean addSettle,
            String tableName
    ) {
        Preconditions.checkArgument(!Strings.isNullOrEmpty(tableName));
        Preconditions.checkArgument(addOpen | addHigh | addLow | addSettle );

        final List<String> clauses = Lists.newArrayList();

        if (addOpen) clauses.add("Open");
        if (addHigh) clauses.add("High");
        if (addLow) clauses.add("Low");
        if (addSettle) clauses.add("Settle");

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT ");
        builder.append(Joiner.on( , ).join(clauses));
        builder.append(" FROM " + tableName);
        return builder.toString();
    }

}

方法正文起始处的先决条件是要确定至少一个布林选项总是真实的, 而该表名不是空的或空的。总是检查您的代码的前提条件, 当您将来使用代码出错时, 它会省下很多麻烦!

友情链接