1.当多条件查询时,有一个或两个条件不作为限定条件,则把该条件,condition1,赋值为其在数据库中对应的字段,即condition1。所以先做以下预处理。
if(pllh.length()>0){
pllh = pllh;
}else{
pllh = "pllh";
}
if(dh.length()>0){
dh = dh;
}else{
dh = "dh";
}
if(cz.length()>0){
cz = cz;
}else{
cz = "cz";
}
if(zl.length()>0){
zl = zl;
}else{
zl = "zl";
}
if(hangstr.length()>0){
hangstr = hangstr;
}else{
hangstr = "hang";
}
if(liestr.length()>0){
liestr = liestr;
}else{
liestr = "lie";
}
2.构建sql语句
QString str = QString("INSERT INTO temcity SELECT * FROM city WHERE pllh like '% %1 %' AND dh like '% %2 %' AND cz like '% %3 %' AND zl like '% %4 %' AND datetime BETWEEN '%5' AND '%6' AND hang = '%7' AND lie = '%8'" )
.arg(pllh).arg(dh).arg(cz).arg(zl).arg(startTime).arg(endTime).arg(hangstr).arg(liestr);
其中 ‘% %3 %’ 是为了之后构造模糊查询的语句做准备。
模糊查询的语法是:SELECT * FROM city WHERE pllh like ‘%condition%’,这样包含condition的pllh字段都会返回。
多条件查询+模糊查询的语法是:SELECT * FROM city WHERE pllh like ‘%condition1%’ AND dh like ‘%condition2%’
3.构造完成后对 str 中不符合sql语法的部分进行替换
str.replace("'% pllh %'","pllh");
str.replace("'% dh %'","dh");
str.replace("'% cz %'","cz");
str.replace("'% zl %'","zl");
str.replace("'hang'","hang");
str.replace("'lie'","lie");
str.replace(" %'","%'");
str.replace("'% ","'%");
bool success = querysql.exec(str);
这样就实现了多态、多条件、模糊查询。