欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL、oracle分页原生查询

程序员文章站 2022-03-26 09:47:49
//根据表名 关键字 进行分页查询 @override public jsonarray doget(string tablename, string keywo...
//根据表名 关键字 进行分页查询
 @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;
    }