Json object from database in java

Question

Can someone help me create a JSON object from a database?

Here is what JSON 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 can get the selected type, name, demo from the database.

I do not know how to start JSON encoding.

Answer 1

If you want to extract data from the database and create a JSON object yourself, follow these steps:

 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 ); 

jObjDeviceList now contains all the data.

Answer 2

Using jOOQ, you can create a similar JSON list from your database:

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

The JSON string will look like this:

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

This is not exactly what you requested in the question, but maybe you do not depend on the exact JSON format that you proposed? Alternatively, you can subsequently convert this JSON object.

More details here:

http://sourceforge.net/apps/trac/jooq/wiki/Manual/ADVANCED/Export

Answer 3

If you have 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 

Done with (Note: classpath has a DOS delimiter):

 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:

• json-lib-2.4-jdk15.jar
• commons-lang-2.6.jar
• commons-logging-1.1.1.jar
• commons-collections-3.2.1.jar
• commons-beanutils-1.8.0.jar
• ezmorph-1.0.6.jar

Answer 4

 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); } }