Oracle多条件模糊查询分页
因为涉及到开了数据的分页和模糊查询,所以一开始便想到了用EasyUI来做,可是随即发现并没有想象的那么简单,首先之前在学校的时候使用的数据库一直是SQL Server但现在到公司之后使用的数据库是Oracle,它在分页语句上面和之前sql的是有不同的,后来又涉及到多条件的模糊查询,只能拼接sql语句。
public static string strJson1;
public void ProcessRequest(HttpContext context)
{
//int state = int.Parse(context.Request["state"]);
//获取显示行数
int rows = int.Parse(context.Request["rows"]);
//获取当前页面页数
int page = int.Parse(context.Request["page"]);
//显示最小记录
int sm = (page - 1) * rows + 1;
//显示最大记录
int big = page * rows;
//判断页面是否第一次加载
var state = context.Request["state"];
var a = context.Request["a"];
if (a != null)
{
string name = context.Request["name"];
string zc = context.Request["zc"];
string szly = context.Request["szly"];
//模糊查询语句
string sql2 = "select * from (select a.*,rownum rn from (select * from TestBigData ";
string sql4 = "where 1=1";
//查询该条件下所有信息总数
string sql3="select count(id) from (select a.*,rownum rn from (select * from TestBigData ";
string sql7=") a )";
string sql5 = ") a ) where rn between " + sm + " and " + big + "";
if (name != "")
{
sql4 += "and name like '%" + name + "%'";
}
if (zc != "")
{
sql4 += "and zc like '%" + zc + "%'";
}
if (szly != "")
{
sql4+= "and szly like '%" + szly + "%'";
}
string sql6 = sql2 + sql4 + sql5;
DataSet set1 = DBHelper.GetDataSetBySql(sql6);
int res1 = int.Parse(DBHelper.GetExecuteScalar(sql3 + sql4 + sql7).ToString());
strJson1 = "{\"total\":" + res1 + ",\"rows\":" + JsonConvert.SerializeObject(set1.Tables[0]) + "}";
context.Response.Write(strJson1);
}
//页面第一次打开,显示所有信息
else
{
//if (state ==1)
//{
string sql1 = "select count(id) from TestBigData";
int res = int.Parse(DBHelper.GetExecuteScalar(sql1).ToString());
string sql = "select * from (select a.*,rownum rn from (select * from TestBigData) a ) where rn between " + sm + " and " + big + "";
DataSet set = DBHelper.GetDataSetBySql(sql);
string strJson = "{\"total\":" + res + ",\"rows\":" + JsonConvert.SerializeObject(set.Tables[0]) + "}";
context.Response.Write(strJson);
}
//}