java分页sql语句_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;

}

}