为了账号安全,请及时绑定邮箱和手机立即绑定

Java系列:java实现分页功能

标签:
Java

一、概要
1.解析json数据,返回数组
2.拼接SQL语句并执行遍历
3.定义list并分情况添加数据
4.获取json{共几页,当前页码,共几条,起始条数,终止条数,数据list}
二、代码实例

package JsonToWhere;
import org.json.JSONException;
import org.json.JSONObject;
import java.sql.*;
import java.util.*;

public class JsonToSql {
    private static String USERNAMR = "mine";
    private static String PASSWORD = "mine";
    private static String DRVIER = "oracle.jdbc.OracleDriver";
    private static String URL = "jdbc:oracle:thin:@192.168.100.57:1521:orcl";

    Connection connection = getConnection();
    PreparedStatement pstm = null;
    ResultSet rs = null;
    PreparedStatement pstm1 = null;
    ResultSet rs1 = null;

    //获取json{共几页,当前页码,共几条,起始条数,终止条数,数据list}
    public String query(String jsonData) {
        String[] sqlStr = getSqlStr(jsonData);
        String table = sqlStr[0];
        String fields = sqlStr[1];
        String where = sqlStr[2];
        String order = sqlStr[3];
        String pageNum = sqlStr[4];
        String dataJson = null;

        //拼接SQL语句
        String sql = String.format(
                "select  %s  from %s where %s order by %s",
                fields, table, (where.equals("")) ? "" : where, order
        );

        //定义list并添加数据
        String[] strArray = fields.split(",");
        List<String> list = new ArrayList<String>();
        try {
            int totalRecords = 0;
            //执行SQL语句 并遍历
            pstm = connection.prepareStatement(sql);
            rs = pstm.executeQuery();

            if (pageNum.equals("0")) pageNum = "1";//当输入的页码为0时,视为1
            int pageNumber = Integer.parseInt(pageNum);
            int recordStart = (pageNumber - 1) * 10 + 1;
            int recordEnd = pageNumber * 10;
            int totalPages = 0;
            while (rs.next()) {
                totalRecords++;
                //只获取当前页码的数据list
                if (totalRecords < recordStart) continue;
                if (totalRecords > recordEnd) continue;

                List<String> list1 = new ArrayList<String>();
                for (int i = 0; i < strArray.length; i++) {
                    String string = rs.getString(strArray[i]);
                    list1.add(string);
                }
                String list2 = '\n' + list1.toString().replace("[", "\"").replace("]", "\"");
                list.add(list2);

            }
            //获取总页码
            totalPages = (totalRecords % 10 == 0) ? (totalRecords / 10) : ((totalRecords / 10) + 1);

            if (recordEnd > totalRecords) recordEnd = totalRecords;
            //超出页码范围时获取list
            if (pageNumber > totalPages) {
                pageNumber = totalPages;
                recordStart = (pageNumber - 1) * 10 + 1;
                int totalRecords_out= 0;
                pstm1 = connection.prepareStatement(sql);
                rs1 = pstm1.executeQuery();
                int recordStart_out = (totalPages - 1) * 10 + 1;
                int recordEnd_out = totalPages * 10;
                while (rs1.next()) {
                    totalRecords_out++;
                    if (totalRecords_out < recordStart_out) continue;
                    if (totalRecords_out > recordEnd_out) continue;
                    List<String> list1 = new ArrayList<String>();
                    for (int i = 0; i < strArray.length; i++) {
                        String string = rs1.getString(strArray[i]);
                        list1.add(string);
                    }
                    String list2 = '\n' + list1.toString().replace("[", "\"").replace("]", "\"");
                    list.add(list2);
                }
            }

            dataJson = String.format(
                    "{\"pageCount\": %d, " +
                            "\"pageNumber\": %d, " +
                            "\"recordCount\": %d, " +
                            "\"recordFrom\": %d, " +
                            "\"recordTo\": %d, " +
                            "\"data\": %s" +
                            "}",
                    totalPages,//共几页
                    pageNumber,//当前页码
                    totalRecords,//共几条
                    recordStart,//起始条数
                    recordEnd,//终止条数
                    list//数据list
            );
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstm != null) {
                try {
                    pstm.close();
                    pstm = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        System.out.println(dataJson);
        return dataJson;
    }

    //解析json数据,返回数组
    private String[] getSqlStr(String jsonData){
        String ret = "";
        String table="";
        String fields="";
        String order="";
        String page="";
        String[] arry = new String[5];
        try {
            JSONObject obj = new JSONObject(jsonData);
            Iterator keys = obj.keys();
            while (keys.hasNext()) {
                String key = String.valueOf(keys.next());
                String val = obj.getString(key);
                if (key.equals("filter")){
                    JSONObject obj1=obj.getJSONObject("filter");
                    Iterator keys1 = obj1.keys();
                    while (keys1.hasNext()) {
                        String key1 = String.valueOf(keys1.next());
                        String val1 = obj1.getString(key1);
                        if(!val1.equals("")){
                            if (!ret.equals("")){
                                ret += " and ";
                            }
                            ret += String.format(key1+"='%s'",val1);
                        }
                    }
                }else if(key.equals("table")){
                    table = val;
                }else if(key.equals("fields")){
                    fields=val;
                }else if(key.equals("order")){
                    order=val;
                }else if(key.equals("page")){
                    page=val;
                }
            }
            arry[0] = table;
            arry[1] = fields;
            arry[2] = ret;
            arry[3] = order;
            arry[4] = page;
        }catch (Exception e){
            e.printStackTrace();
        }
        return arry;
    }

    public Connection getConnection(){
        try {
            Class.forName(DRVIER);
            connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
            System.out.println("成功连接数据库");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("class not find !", e);
        } catch (SQLException e) {
            throw new RuntimeException("get connection error!", e);
        }
        return connection;
    }

    public static void main(String args[]) throws JSONException {
        JsonToSql jts = new JsonToSql();
        jts.query("{\"table\":\"electr_data\",\"fields\":\"equid,equname,areaname\"," +
                "\"filter\":{\"substation\": \"\",\"equid\": \"1\",\"equname\": \"\"," +
                "\"i_a\": \"1\",\"u_a\": \"\",\"w_a\": \"3\",\"areaname\": \"煤矿\"}," +
                "\"order\":\"equid\",\"page\":\"10\"}");
    }
}

三、输出数组样式
图片描述

点击查看更多内容
3人点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消