一、概要
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\"}");
}
}三、输出数组样式
随时随地看视频
