Java系列：执行SQL语句，返回分页数据

执行SQL语句，实现分页效果

public class SensorMonitor {
    private static String USERNAMR = "system";
    private static String PASSWORD = "orcl";
    private static String DRVIER = "oracle.jdbc.OracleDriver";
    private static String URL = "jdbc:oracle:thin:@192.168.1.101:1521:orcl";

    public String getData(String jsonData){
        try {
            String whereStr = getWhereStr(jsonData);
            JSONObject obj = new JSONObject(jsonData);
            int page = obj.getInt("page");
            String sql =
                    "select  id,sensorType,position,substation,value,status from cddy" +
                            ( (whereStr.equals(""))? "" :  " where " + whereStr );
            return queryWithPage(sql, page);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

private String getWhereStr1(String jsonData) {
        String ret="";
        try {
            JSONObject obj = new JSONObject(jsonData);
            String id = obj.getString("id");
            String substation = obj.getString("substation");
            String subport = obj.getString("subport");
            String area = obj.getString("area");
            String dataType = obj.getString("dataType");
            String sensorType = obj.getString("sensorType");
            String swichDescribe = obj.getString("swichDescribe");
            String alarmStatus = obj.getString("alarmStatus");
            String resetStatus = obj.getString("resetStatus");
            if (! id.trim().equals("")) {
                ret += String.format(" id = '%s' ", id);
            }
            if (! substation.trim().equals("")) {
                if (!ret.equals("")) ret += " and ";
                ret += String.format(" substation = '%s' ", substation);
            }
            if (! subport.trim().equals("")) {
                if (!ret.equals("")) ret += " and ";
                ret += String.format(" subport = '%s' ", subport);
            }
            if (! area.trim().equals("")) {
                if (!ret.equals("")) ret += " and ";
                ret += String.format(" area = '%s' ", area);
            }
            if (! dataType.trim().equals("")) {
                if (!ret.equals("")) ret += " and ";
                ret += String.format(" dataType = '%s' ", dataType);
            }
            if (! sensorType.trim().equals("")) {
                if (!ret.equals("")) ret += " and ";
                ret += String.format(" sensorType = '%s' ", sensorType);
            }
            if (! swichDescribe.trim().equals("")) {
                if (!ret.equals("")) ret += " and ";
                ret += String.format(" swichDescribe = '%s' ", swichDescribe);
            }
            if (! alarmStatus.trim().equals("")) {
                if (!ret.equals("")) ret += " and ";
                ret += String.format(" alarmStatus = '%s' ", alarmStatus);
            }
            if (! resetStatus.trim().equals("")) {
                if (!ret.equals("")) ret += " and ";
                ret += String.format(" resetStatus = '%s' ", resetStatus);
            }
        } catch (JSONException e) {
            e.printStackTrace();
        }
        return ret;
    }

private String queryWithPage(String sql, int page) {
        Connection  conn        = null;
        Statement   stat        = null;
        StringBuffer    buff = new StringBuffer();
        try {
            conn    =   this.getConnection();
            stat    =   conn.createStatement();
        }
        catch (Exception e) {
            e.printStackTrace();
            return "[]";
        }
        try {
            ResultSet rows      = stat.executeQuery(sql);
            ResultSetMetaData rsmd = rows.getMetaData();
            int colCount        = rsmd.getColumnCount();
            int recordTotal = 0;
            int recordFrom = 0;
            int recordTo = 0;
            boolean first = true;
            buff.append("[");
            while (rows.next()) {
                recordTotal ++;
                if ((recordTotal > (page - 1) * 10) && (recordTotal <= page * 10)) {
                    if (recordFrom == 0) {
                        recordFrom = recordTotal;
                        recordTo = recordTotal;
                    }
                    else
                        recordTo ++;
                }
                //只获取当前页码的数据list
                if (recordTotal < recordFrom) continue;
                if (recordTotal > recordTo) continue;

                String rowStr   = "";
                for (int i = 1; i <= colCount; i ++ ) {
                    if (i>1) rowStr += ",";
                    String tempValue    =   rows.getString(i);
                    tempValue           =   tempValue.replace("\"","\\\"");
                    tempValue           =   tempValue.replace("\n","\\n");
                    tempValue           =   tempValue.replace("\t","\\t");
                    tempValue           =   tempValue.replace(".0","");
                    rowStr              +=  String.format("%s", tempValue);
                }
                rowStr = String.format("\"%s\"", rowStr);
                if (first) first = false;
                else buff.append(",");
                buff.append(rowStr);
            }
            buff.append("]");
            rows.close();
            int pageTotal = (recordTotal % 10 == 0)? recordTotal / 10: (recordTotal / 10) + 1;
            if (page > pageTotal) page = pageTotal;
            return String.format(
                    "{\"pageTotal\":%d, \"pageCurrent\":%d, \"recordTotal\":%d, \n" +
                            "\"recordFrom\":%d, \"recordTo\":%d, \"data\":\n" +
                            "%s}",
                    pageTotal, page, recordTotal, recordFrom, recordTo, buff.toString()
            );
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (stat != null) stat.close();
            if (conn != null) conn.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return
                "{\"pageTotal\":0, \"pageCurrent\":0, \"recordTotal\":0, \n" +
                        "\"recordFrom\":0, \"recordTo\":0, data:[]}";
    }

 private Connection getConnection() {
        Connection connection  = null;
        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;
    }
}