使用列名向量，生成一个sql语句

A problem that we need to solve regularly at my workplace is how to build sql statements based on user supplied table/column names. The issue I am trying to address is the commas between column names.

我们需要在工作场所定期解决的问题是如何根据用户提供的表/列名称构建sql语句。我想解决的问题是列名之间的逗号。

One technique looks something like this.

一种技术看起来像这样。

selectSql  = "SELECT ";

for (z = 0; z < columns.size(); z++)
{
    selectSql += columns[z]._name;
    selectSql += ", "; 
}

selectSql = selectSql(0, selectSql.len() - 2);

selectSql += "FROM some-table";

Another technique looks something like this

另一种技术看起来像这样

selectSql  = "SELECT ";

for (z = 0; z < columns.size(); z++)
{
    selectSql += columns[z]._name;
    if (z < columns.size() - 1) 
        selectSql += ", "; 
}

selectSql += "FROM some-table";

I am not particularly enthralled by either of these implementations.

我并不特别被这些实现中的任何一个所吸引。

I am interesting in hearing ideas for other ways to address this issue, with an eye toward making the code easier to read/understand/maintain.

我很有兴趣听到其他解决这个问题的方法的想法,着眼于使代码更容易阅读/理解/维护。

What alternate techniques are available?

有哪些替代技术?

8 个解决方案

#1

In your case it is probably safe to assume that there is at least one column since otherwise there is no point in doing the select. In that case you could do:

在您的情况下,可以安全地假设至少有一列,否则进行选择没有意义。在这种情况下,您可以这样做:

selectSql  = "SELECT ";
selectSql += columns[0]._name;

for (z = 1; z < columns.size(); z++) {
   selectSql += ", ";
   selectSql += columns[z]._name;
}

selectSql += " FROM some-table";

#2

Rather than applying a work around each time again, you can fix the problem once and for all by writing a function object, and using that like strager proposed (though his implementation is rather not C++):

你可以通过编写一个函数对象来一劳永逸地解决问题,而不是每次都重复使用一个工作,并且使用像strager一样的方法(虽然他的实现不是C ++):

struct join {
    std::string sep;
    join(std::string const& sep): sep(sep) { }

    template<typename Column>
    std::string operator()(Column const& a, Column const& b) const {
        return a._name + sep + b._name;
    }
};

As i don't know your column type, i've left it templated. Now, whenever you want to build a query, just do

由于我不知道你的列类型,我已经把它模板化了。现在,只要你想构建一个查询,就行了

std::string query = std::accumulate(cols.begin(), cols.end(), 
    std::string("SELECT "), join(", ")) + " FROM some-table;";

#3

We don't bother to remove the trailing comma.
This is because you can select a constant and the SQL is still valid.

我们不打算删除尾随的逗号。这是因为您可以选择常量并且SQL仍然有效。

SELECT A FROM T

-- Is the same as 

SELECT A,1 FROM T

-- Apart from there is an extra column named 1 where each value is 1

So using the STL to make it compact:

所以使用STL使其紧凑:

#include <sstream>
#include <iterator>
#include <algorithm>

    std::stringstream           select;

    // Build select statement.
    select << "SELECT ";
    std::copy(col.begin(),col.end(),std::ostream_iterator<std::string>(select," , "));
    select << " 1 FROM TABLE PLOP";

#4

The way I build up statements is usually:

我建立语句的方式通常是:

pad = ""
stmt = "SELECT "

for (i = 0; i < number; i++)
{
    stmt += pad + item[i]
    pad = ", "
}

This is relatively clean - it reassigns to pad each iteration, but that's trivial. I've used any number of trivial variations on this, but it is the cleanest mechanism I know of.

这是相对干净的 - 它重新分配给每次迭代填充,但这是微不足道的。我已经使用过任何数量的微不足道的变化,但它是我所知道的最干净的机制。

Of course, there'll be someone else's answer to learn from too...

当然,也会有别人的答案来学习......

#5

It doesn't have to be so complicated.

它不一定非常复杂。

string sql = "SELECT " + join(cols.begin(), cols.end(), ", ") + " FROM some_table";

where

template <typename I>
string join(I begin, I end, const string& sep){
   ostringstream out;
   for(; begin != end; ++begin){
      out << *begin;
      if(begin+1 != end) out << sep;
   }
   return out.str();
}

#6

Not to belabor the point but take a look at boost::algorithm::join(). Here's an example in case you think that their documentation is too dense for words:

不要强调这一点,但请看一下boost :: algorithm :: join()。这是一个示例,以防您认为他们的文档对于单词过于密集:

std::string
build_sql(std::vector<std::string> const& colNames,
          std::string const& tableName)
{
    std::ostringstream sql;
    sql << "SELECT "
        << boost::algorithm::join(colNames, std::string(","))
        << " FROM " << tableName;
    return sql.str();
}

When in doubt, look at Boost.org. They usually have a solution to most problems like this one already there.

如有疑问,请查看Boost.org。他们通常可以解决大多数问题,例如已存在的问题。

#7

for (z = 0; z < columns.size(); z++)
{
    if( z != 0 )
        selectSql += ", "; 
    selectSql += columns[z]._name;
}

#8

I would suggest building a generic join function to do this. You can use the e.g. accumulate algorithm to join columns.

我建议建立一个通用的连接函数来做到这一点。您可以使用例如累积算法以连接列。

EDIT: See litb's implementation; it's much less naïve.

编辑:见litb的实现;它不那么天真。

// Untested
#include <numeric>

template<std::string separator>
struct JoinColumns {
    std::string operator()(Column a, Column b) {
        return a._name + separator + b._name;
    }

    // Too lazy to come up with a better name
    std::string inArray(T array) {
        stl::accumulate(array.begin(), array.end(), std::string(), *this);
    }
};

selectSql += stl::accumulate(columns.begin(), columns.end(), std::string(), JoinColumns<", ">());
// or
selectSql += JoinColumns<", ">().inArray(columns);

You can get a cleaner syntax by using better wrappers, of course.

当然,您可以通过使用更好的包装器来获得更清晰的语法。

#1