Assembling updates at runtime

Question

When riding SELECTS at runtime we always have the problem of knowing what may or may not be coming depending on the choices made by the user.

When mounting the clause WHERE we come across the question of whether or not to add the AND before each condition.

If it’s the first condition then you won’t have the AND, however if it is the second or greater than this we add to the beginning of the condition AND.

In order not to be hostage to this condition, having to analyze it at all times I add an innocuous clause and then I can add AND to all the others.

Thus my clause WHERE initial is WHERE 0 = 0, then I can add the AND to all other conditions.

At the time of execution my clause WHERE will be like this: WHERE 0 = 0 AND condicao1 = 'cond1' AND condicao2 = 'cond2' AND condicao3 = 3 or just like that: WHERE 0 = 0 and will run smoothly.

When we ride a UPDATE at runtime we have the same problem, but the question now is the comma or the total lack of parameters.

We start with "UPDATE tabelax SET" and we add columns as the program finds these markups.

In the first we add "campo1 = 'campo1'" + ",", in the second "campo2 = 'campo2' + ",". Note that we could not add the comma if the field2 was the last field filled because otherwise our final command would be wrong. UPDATE tabelax SET campo1 = 'campo1', campo2 = 'campo2', WHERE condicao this wrong. In other words, you have to keep asking if it is the last one or not, or add it at all and when you finish the loop check if the last character is a comma and remove it before adding the clause WHERE.

Finally, the question: there is no way, as the case of WHERE, make it easier, more elegant or be smart to solve it?

Answer 1

My technique is to use a variable to represent the separator and always concatenate the next value after the separator; but the separator is initialized empty and only receives the separation text after the first value is added to the query.

In a loop, looks something like this:

sql = "update tabela set ";
paramPattern = "%s = '%s'";
separador = "";

// campos a atualizar, separados por vírgula:

for(campo : campos) {
    sql += separador + String.format(paramPattern, campo.nome, campo.valor);
    separador = ", ";
}

// cláusula where:

sql += " where ";
separador = "";

for(condicao : condicoes) {
    sql += separador + condicao;
    separador = " and ";
}

The idea also works if you don’t have lists of fields and conditions but instead uses ifs. It looks something like this:

sql = "update tabela set ";
paramPattern = "%s = '%s'";
separador = "";

// campos a atualizar, separados por vírgula:

if (atualizarCampo1) {
    sql += separador + String.format(paramPattern, campo1.nome, campo1.valor);
    separador = ", ";
}
if (atualizarCampo2) {
    sql += separador + String.format(paramPattern, campo2.nome, campo2.valor);
    separador = ", ";
}
// outros campos...

// cláusula where:

sql += " where ";
separador = "";

if (filtrarPorData) {
    sql += separador + "DATA between :data1 and :data2";
    separador = " AND ";
}
if (filtrarPorStatus) {
    sql += separador + "STATUS = :status";
    separador = " AND ";
}
// outras condições...

If there is a possibility that there is no filter (command without clause Where), just fill the conditions in another variable, and only concatenate it to sql if it ends differently than empty after going through all the negotiations of conditions. For example:

sql = "select * from tabela";
where = "";
separador = "";

if (filtrarPorData) {
    where += separador + "DATA between :data1 and :data2";
    separador = " AND ";
}
if (filtrarPorStatus) {
    where += separador + "STATUS = :status";
    separador = " AND ";
}
// outras condições...

if (!"".equals(where)) {
    sql += " where " + where;
}

I prefer to do so than add something in the query (Where 1=1) only by code physiology. And I also prefer it that way than adding something unnecessary to the string and then removing it (in this case, an extra comma).

_{And of course this is a pseudo code just to demonstrate the idea of the separator as a variable. I hope you always use parameters instead of concatenating values in the query.}

Answer 2

In C# use the method Join(), He’ll put the separator in the right way.

In Java use the method join().

I don’t know if modern Delphi has something ready, but it’s easy to make a Join():

function Join(const Texts : array of string; const Separator : string) : string;
var
    i : Integer;
begin
    Result := Texts[low(Texts)];
    for i := low(Texts) + 1 to high(Texts) do
        Result := Separator + Result + Texts[i];
end;

I put in the Github for future reference.

Note that it starts by taking the first element of array before the loop (this is the secret to not leave a tab remaining) and then always puts a pair of separator and new item.

Answer 3

Based on this answer I made an adaptation for you that allows you to assemble the update at runtime, see how it turned out:

static string buildUpdate(string table, string nomeCampoId, string idCampo, Dictionary<string, object> parameters)
{           
    StringBuilder builder = new StringBuilder("Update " + table + " SET ");

    foreach (KeyValuePair<string, object> param in parameters)            
        builder.Append(param.Key).Append(" = ").Append(param.Value).Append(",");            

    builder.Remove(builder.Length - 1, 1); /*Aqui remove a virgula.*/
    builder.Append(" WHERE ").Append(nomeCampoId).Append(" = ").Append(idCampo);
    return builder.ToString();
}

To use the method buildUpdate() it is necessary to build your dictionary to popular it with the fields and values, see how was the implementation:

static void Main(string[] args)
{
    Dictionary<string, object> parameters = new Dictionary<string, object>();
    parameters.Add("campoNome", "'Maria'");
    parameters.Add("campoIdade", 25);
    parameters.Add("campoGenero", "'Feminino'");

    string update = buildUpdate("Pessoa", "idPessoa", "2",parameters);
    Console.WriteLine(update);
}

The method buildUpdate() will generate the following console output:

Update Pessoa SET fieldName = 'Maria',field = 25,field 'Female' WHERE idPessoa = 2

You’ll need to adapt it, but I believe it’s already a way.

Answer 4

I usually add the conditions with the comma, and in the end remove the last character of the string. In the case of AND works in the same way only by removing 3 characters.

public String constroiCondicao1(String... condicoes){
    if(condicoes == null)
        return "";
    StringBuilder sb = new StringBuilder();
    sb.append("WHERE ");
    for(String cond : condicoes){
        sb.append(cond + " AND ");
    }
    return sb.toString().substring(0, sb.length()-4);
}

// OU

public String constroiCondicao2(String... condicoes){
    if(condicoes == null)
        return "";
    return "WHERE " + String.join(" AND ", condicoes);
}