java 每日习题（十）数据库数据转成json格式

题目：把从数据中读取到的数据转成json格式

1. 读取数据到ResultSet,

2. 把每一行数据转成一个JSONObject

3. 把所有单行的JSONObject存到一个list里

4. 把list转成JSONObject

{
    "data": [
        {
            "timeStamp": "1447836374319", 
            "elapsed": "169"
        }, 
        {
            "timeStamp": "1447836374319", 
            "elapsed": "169"
        }, 
        {
            "timeStamp": "1447836375346", 
            "elapsed": "498"
        }, 
        {
            "timeStamp": "1447836375346", 
            "elapsed": "498"
        }, 
        {
            "timeStamp": "1447836376346", 
            "elapsed": "250"
        }
    ]
}

 

 

package everyDay;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;

import org.junit.Test;

public class DataToJson {

    @Test
    public void getData() {
        try {
            String url = "jdbc:mysql://10.255.255.148:3306/testresults";
            String user = "user";
            String password = "passwd";
            Connection conn = DriverManager.getConnection(url, user, password);

            Statement stmt = conn.createStatement();
            String sql = "select timeStamp, elapsed from cpuInfo order by timeStamp limit 5";
            ResultSet resultset = stmt.executeQuery(sql);

            List<JSONObject> list = new ArrayList<JSONObject>();

            while (resultset.next()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("timeStamp", resultset.getString(1));
                jsonObject.put("elapsed", resultset.getString(2));

                list.add(jsonObject);
            }
            JSONObject jsonList = new JSONObject();
            for (JSONObject obj : list) {

                System.out.println("jsonObject：" + obj);//输出单条数据

            }

            jsonList.put("data", list);
            System.out.println(jsonList);//输出转成json格式的list

        } catch (Exception e) {
            // TODO: handle exception
        }

    }

}