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;
    }