数据库教程:MySQL、oracle分页原生查询

  //根据表名 关键字 进行分页查询   @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;      }