我要评论
//根据表名 关键字 进行分页查询
@override
public jsonarray doget(string tablename, string keyword,int pagenum,int pagesize) {
resultset result = null;
preparedstatement pre = null;
try {
//根据数据库类型查询表的所有列名
string datatype = con.getmetadata().getdatabaseproductname();
string columnsql = "";
switch (datatype) {
case "mysql":
columnsql = "select column_name from information_schema.columns where table_name ='"+tablename+"'";
break;
case "oracle":
columnsql = "select column_name from all_tab_columns where table_name ='"+tablename+"'";
default:
break;
}
pre = con.preparestatement(columnsql);
result = pre.executequery();
//拼接sql
stringbuilder sql = new stringbuilder("select * from "+tablename+" t where ");
resultsetmetadata md = result.getmetadata();// 得到结果集(rs)的结构信息，比如字段数、字段名等
int columncount = md.getcolumncount(); // 返回此 resultset 对象中的列数
while (result.next()) {
for(int i=1;i<=columncount;i++){
sql.append(sql.append("t."+result.getobject(i))+" like '%"+keyword+"%' or ");
}
}
sql = sql.delete(sql.length()-3, sql.length());//去掉sql末尾的or
if("mysql".equals(datatype)){//mysql分页语句拼接
sql = sql.append("limit "+pagenum+","+pagesize+"");
}
if("oracle".equals(datatype)){//oracle分页语句拼接
string sqlstr = "select * from (select a.*, rownum rn from ("+sql+") a where rownum <="+pagenum*pagesize+" ) where rn >="+(pagenum-1)*pagesize+"";
sql = sql.replace(0,sql.length(), sqlstr);
}
pre = con.preparestatement(sql.tostring());
result = pre.executequery();
} catch (exception e) {
e.printstacktrace();
}
return resultsettolist(pre, result);
}
/**
* 将resultset结果集转成jsonarray
*
* @param pre
* @param rs
* @return
* @author
*/
public jsonarray resultsettolist(preparedstatement pre, resultset result) {
if (result == null)
return null;
jsonarray array = new jsonarray();
try {
resultsetmetadata md = result.getmetadata();// 得到结果集(rs)的结构信息，比如字段数、字段名等
int columncount = md.getcolumncount(); // 返回此 resultset 对象中的列数
jsonobject json = null;
while (result.next()) {
json = new jsonobject();
for (int i = 1; i <= columncount; i++) {
if(!"".equals(result.getobject(i))){
if(result.getobject(i) instanceof date){
json.put(md.getcolumnname(i), sdf.format(result.getobject(i)));
}else{
json.put(md.getcolumnname(i), result.getobject(i));
}
}else{
json.put(md.getcolumnname(i), "");
}
}
array.put(json);
}
} catch (sqlexception | jsonexception e) {
e.printstacktrace();
} finally {
close(result);
close(pre);
close(con);
}
//"返回json数据：" + array);
return array;
}
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论