Json object from database in java

Question

Can anyone help me how to create a JSON Object from the database?

This is what the JSON output should look like:

{“devicelist”:{
    “device”: [
    {“id”: “01”, “type”: “CAM”, “name”: “Livingroom”}
    {“id”: “15”, “type”: “CAM”, “name”: “Kitchen”}
]
}}

This is my code:

 if (reg!=null)
 {

     try
                  {
                     con = ds.getConnection();
                     Statement select = con.createStatement();
                    ResultSet result=select.executeQuery("Select type,name,demo from register_device");  
                      while (result.next())
                      {
                         String  type_json=result.getString("type");
                         String name_json=result.getString("name");
                         String id_json=result.getString("demo");
                         JSONArray arrayObj=new JSONArray();

                      }
                  }
                  catch(Exception e)
                  {

                  }
      }

I am able to get the selected type,name,demo from the database.

I don't know how to start the JSON coding.

Which database tables column (mentioned above) stores the json values? — Buhake Sindi, Jul 20 '11 at 09:08
Hello, I have same requirement and I will be very very thankful if you could tell me is there any simple and elegant way to convert SQL to JSON using Java standard library (Java.net.*) or anything that is not third party jars. Also, "JSONARRAY" ...is this third party? I will be very very thankful if anyone could reply me — user3384231, Feb 17 '17 at 15:52

score 10 · Accepted Answer · edited Jun 21 '13 at 08:50

If you want to extract the data from the DB and construct the JSON Object yourself, you can do:

JsonArray jArray = new JsonArray();
while (result.next())
{
    String  type_json=result.getString("type");
    String name_json=result.getString("name");
    String id_json=result.getString("demo");
    JsonObject jObj = new JsonObject();
    jobj.put("id", id_json);
    jobj.put("type", type_json);
    jobj.put("name", name_json);
    jArray.put(jObj);
}

JsonObject jObjDevice = new JsonObject();
jObjDevice.put("device", jArray);
JsonObject jObjDeviceList = new JsonObject();
jObjDevice.put("devicelist", jObjDevice );

now jObjDeviceList contains all the data.

last statement should be `jObjDeviceList.put("devicelist", jObjDevice );` I guess — YoussefDir, Jun 11 '20 at 20:05

Lukas Eder · Answer 2 · 2020-11-17T15:55:39.073

With jOOQ, you could produce a similar JSON list from your database:

String json = create.select(TYPE, NAME, DEMO)
                    .from(REGISTER_DEVICE)
                    .fetch()
                    .formatJSON();

The JSON String would look like this (configurable):

{fields:["TYPE","NAME","DEMO"],
 records:[["01","CAM","Livingroom"],["15","CAM","Kitchen"]]}

See more here. Alternatively, you can use your RDBMS's native SQL/JSON capabilities to create arbitrarily nested JSON documents.

(Disclaimer: I work for the company behind jOOQ)

score 0 · Answer 3 · answered Jul 20 '11 at 10:01

If you have a Device objects, json-lib can serialize the object using get() methods as JSON.

import java.util.*;
import net.sf.json.*;

public class JsonEncode {
    public static void main(String[] args) throws Exception {
        Device d1 = new Device("01", "CAM", "LivingRoom");
        Device d2 = new Device("15", "CAM", "Kitchen");

        List<Device> devices = new ArrayList<Device>(Arrays.asList(d1, d2));

        JSONArray serializedDevices = JSONArray.fromObject(devices);
        JSONObject jsonDevices = new JSONObject();
        jsonDevices.put("devices", serializedDevices);

        JSONObject json = new JSONObject();
        json.put("deviceList", jsonDevices);
        System.out.println(json);
    }

    public static class Device {
        Device(String id, String type, String name) {
            this.id = id;
            this.type = type;
            this.name = name;
        }
        private String id;
        public String getId() { return id; }
        private String type;
        public String getType() { return type; }
        private String name;
        public String getName() { return name; }
    }
}

Saved as: JsonEncode.java

Compiled with:

javac -cp json-lib-2.4-jdk15.jar JsonEncode.java

Executed with (Note: classpath has DOS separator):

java -cp .;json-lib-2.4-jdk15.jar;commons-lang-2.6.jar;commons-logging-1.1.1.jar;commons-collections-3.2.1.jar;ezmorph-1.0.6.jar;commons-beanutils-1.8.0.jar JsonEncode

Dependencies:

score -1 · Answer 4 · answered Jul 11 '17 at 06:07

package com.idal.cib;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnector {

    static Connection conn1 = null;

    public static Connection getDbConnection(String driver, String url,
            String username, String password) {
        // TODO Auto-generated constructor stub
        try {

            Class.forName(driver);

            conn1 = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn1;
    }

}


package com.idal.cib;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

public class DBJsonConverter {

    static ArrayList<String> data = new ArrayList<String>();
    static Connection conn = null;
    static PreparedStatement ps = null;
    static ResultSet rs = null;
    static String path = "";
    static String driver="";
    static String url="";
    static String username="";
    static String password="";
    static String query="";

    @SuppressWarnings({ "unchecked" })
    public static void dataLoad(String path) {
        JSONObject obj1 = new JSONObject();
        JSONArray jsonArray = new JSONArray();
        conn = DatabaseConnector.getDbConnection(driver, url, username,
                password);
        try {
            ps = conn.prepareStatement(query);
            rs = ps.executeQuery();
            ArrayList<String> columnNames = new ArrayList<String>();
            if (rs != null) {
                ResultSetMetaData columns = rs.getMetaData();
                int i = 0;
                while (i < columns.getColumnCount()) {
                    i++;
                    columnNames.add(columns.getColumnName(i));
                }
                while (rs.next()) {
                    JSONObject obj = new JSONObject();
                    for (i = 0; i < columnNames.size(); i++) {
                        data.add(rs.getString(columnNames.get(i)));
                        {
                            for (int j = 0; j < data.size(); j++) {
                                if (data.get(j) != null) {
                                    obj.put(columnNames.get(i), data.get(j));
                                }else {
                                    obj.put(columnNames.get(i), "");
                                }
                            }
                        }
                    }

                    jsonArray.add(obj);
                    obj1.put("header", jsonArray);
                    FileWriter file = new FileWriter(path);
                    file.write(obj1.toJSONString());
                    file.flush();
                    file.close();
                }
                ps.close();
            } else {
                JSONObject obj2 = new JSONObject();
                obj2.put(null, null);
                jsonArray.add(obj2);
                obj1.put("header", jsonArray);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    rs.close();
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    @SuppressWarnings("static-access")
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        driver = "oracle.jdbc.driver.OracleDriver";
        url = "jdbc:oracle:thin:@10.11.34.134:1521:COREMUAT";
        username = "oasisusr";
        password = "p#g_ay0w";
        path = "D:\\VF_Polaris\\968670\\category_list1.json";
        query = "select * from temp_employee";

        DatabaseConnector dc = new DatabaseConnector();
        dc.getDbConnection(driver,url,username,password);
        DBJsonConverter formatter = new DBJsonConverter();
        formatter.dataLoad(path);

    }

}

Pl add some explanation to your answer. – Sahil Mittal Jul 11 '17 at 06:27 — Sahil Mittal, Jul 11 '17 at 06:27

Json object from database in java

4 Answers4

Linked