根据SQL语句查询出的结果集,将其封装为json
例如:
1.select id,username,password,sex from user where id=11
{id:1,username:'jibs',password:'ssdfsdfsfwe',sex:'男'}
/**
* 通过sql查询结果封装成json
* <br />
* <p>Example:</p>
* <code>String sql = "select id,username,password,sex from user where id=12";</code>
* @param sql 查询SQL
* @return {"id":"1","username":"jibs","password":"ssdfsdfsfwe","sex":"男"}
*/
代码:
package com.dawnsun.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
public class TestSql2Json {
public static void main(String args[]) throws JSONException{
Connection conn = null;
Statement stat = null;
String url = "jdbc:mysql://localhost:3306/databasename";
String sql ="select * from student";
ResultSet rs = null;
JSONArray array = null;
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url,"username","password");
stat = conn.createStatement();
rs = stat.executeQuery(sql);
// json数组
array = new JSONArray();
// 获取列数
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
// 遍历ResultSet中的每条数据
while (rs.next()) {
JSONObject jsonObj = new JSONObject();
// 遍历每一列
for (int i = 1; i <= columnCount; i++) {
String columnName =metaData.getColumnLabel(i);
String value = rs.getString(columnName);
jsonObj.put(columnName, value);
}
array.put(jsonObj);
}
//通过key获取每个对应value
for(int j=0;j<array.length();j++){
for(int i=1;i<columnCount;i++){
String columnName =metaData.getColumnLabel(i);
JSONObject resultObj = array.optJSONObject(j);
//获取数据项
String value = resultObj.getString(columnName);
logger.info(value);
}
}
//测试json数据的条数
// int ii = array.length();
rs.close();
} catch (Exception e) {
logger.error(e.getMessage());
}
return array.toString();
}