optimization of SQL dynamic Query stitching string

optimization of SQL dynamic Query stitching string

The most primitive direct write: String sql= "select * from Testtables where 1=1";
... Such code is inefficient, which affects the index reference of the database
As shown below:
private void Testonemethod ()
{
String querysql = "SELECT * from Testtables where 1=1";
if (hasonecondition)
{
Querysql + = "onecondition= ' onecondition '";
}
if (hastwocondition)
{
Querysql + = "twocondition= ' twocondition '";
}
if (hasthreecondition)
{
Querysql + = "threecondition= ' threecondition '";
}
// .... Other conditions
Excsql (Querysql)
}

Optimization Scenario A:
Remove string sql= "where 1=1"
Then, define a variable at the time of use, to flag whether the haswhere=false should exist, or haswhere=!haswhere if it already exists. So the next time you use it,

This can be expressed in the following form:
private void Testonemethod ()
{
String querysql = "SELECT * from Testtables";
BOOL _haswhere = false;
if (hasonecondition)
{
Querysql + = _haswhere? "WHERE": "and" + "onecondition= ' onecondition '";
_haswhere = true;
}
if (hastwocondition)
{
Querysql + = _haswhere? "WHERE": "and" + "twocondition= ' twocondition '";
_haswhere = true;
}
if (hasthreecondition)
{
Querysql + = _haswhere? "WHERE": "and" + "threecondition= ' threecondition '";
_haswhere = true;
}
// .... Other conditions
Excsql (Querysql)
}
....

After optimization, the efficiency of SQL is improved, but there are still problems, where is the problem? If we do this all the time, it is not very laborious, then we propose a common side

method, this generic method first has to have a return value of type bool, which is used to confirm whether the hasstring is currently required. As below, at the time of application:

private bool Seachhelper (string wherestring, bool haswhere)
{
if (!haswhere)
wherestring = "where" + wherestring;
Else
wherestring = "and" + wherestring;
return true;
}

private void Testtwomethod ()
{
String querysql = "SELECT * from Testtables";
BOOL _haswhere = false;
if (hasonecondition)
{
_haswhere = Seachhelper (Querysql, _haswhere);
Querysql + = "onecondition= ' onecondition '";
}
if (hasthreecondition)
{
_haswhere = Seachhelper (Querysql, _haswhere);
Querysql + = "twocondition= ' twocondition '";
}
if (hasthreecondition)
{
_haswhere = Seachhelper (Querysql, _haswhere);
Querysql + = "threecondition= ' threecondition '";
}
// .... Other conditions
Excsql (Querysql);
}

The code is a lot more concise, but the problem is still coarse, then what is the problem?
Well, the concatenation of strings back and forth is a waste of resources, so, with StringBuilder ah, good next continue.

private void Testthreemethod ()
{
StringBuilder querysql = new StringBuilder ();
Querysql.append ("SELECT * from Testtables");
BOOL _haswhere = false;
if (hasonecondition)
{
_haswhere = Seachhelper (Querysql, _haswhere);
Querysql.append ("onecondition= ' onecondition '");
}
if (hasthreecondition)
{
_haswhere = Seachhelper (Querysql, _haswhere);
Querysql.append ("twocondition= ' twocondition '");
}
if (hasthreecondition)
{
_haswhere = Seachhelper (Querysql, _haswhere);
Querysql.append ("threecondition= ' threecondition '");
}
// .... Other conditions
Excsql (Querysql.tostring ());
}

Wait a minute, that common method also has to change, of course to change!

private bool Seachhelper (StringBuilder wherestring, bool haswhere)
{
if (!haswhere)
Wherestring.append ("where");
Else
Wherestring.append ("and");
return true;
}

Above is the implementation of dynamic query of the gradual optimization, said the optimization, in fact, there is a certain premise, in addition to the first case of the lowest possible, the rest to be based on the actual situation to determine.
If the query condition is very small, even if there are only two cases, it does not matter, but if the query conditions more, then the efficiency of the latter method is higher. Also for readable code

The stronger, think about, a project usually how many people are writing SQL queries, if not give a different style, then the future maintenance personnel will suffer, by using a common method, then

Later to the code maintenance personnel to bring convenience, while stitching the strings of the extra overhead is not to be ignored, good code habits are important, a system from the small place to pay attention to, that

In the end it must be a good system.

optimization of SQL dynamic Query stitching string