Read Data from Database to JSON using JDBC

A Json array is an ordered collection of values that are enclosed in square brackets i.e. it begins with ‘[’ and ends with ‘]’. The values in the arrays are separated by ‘,’ (comma).

Sample JSON array

{
   "books": [ Java, JavaFX, Hbase, Cassandra, WebGL, JOGL]
}

The json-simple is a light weight library which is used to process JSON objects. Using this you can read or, write the contents of a JSON document using Java program.

JSON-Simple maven dependency

Following is the maven dependency for the JSON-simple library −

<dependencies>
   <dependency>
      <groupId>com.googlecode.json-simple</groupId>
      <artifactId>json-simple</artifactId>
      <version>1.1.1</version>
   </dependency>
</dependencies>

Paste this within the <dependencies> </dependencies> tag at the end of your pom.xml file. (before </project> tag)

Example

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 5 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

To read the contents of the above created MyPlayers table to a JSON file using JDBC −

Retrieve the contents of the MyPlayers table

• Register the Driver class of the desired database using the registerDriver() method of the DriverManager class or, the forName() method of the class named Class.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

• Create a connection object by passing the URL of the database, user-name and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class.

Connection mysqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");

• Create a Statement object using the createStatement() method of the connection interface.

Statement stmt = con.createStatement();

• Execute the SELECT query to retrieve the contents of the MyPlayers table into the ResultSet object, using the executeQuery() method.

String query = "Select * from MyPlayers";
ResultSet rs = stmt.executeQuery(query);

Create a JSON array and add the retrieved MyPlayers data

• Create a JSON documents (to represent the MyPlayers table) by Instantiating the JSONObject class of the json-simple library.

//Creating a JSONObject object
JSONObject jsonObject = new JSONObject();

• Create a JSON array to hold the records of the MyPlayers table, by instantiating the JSONArray class.

JSONArray array = new JSONArray();

• For each record in the MyPLayers table, create a JSON (again) object insert the contents of the record (obtained from the ResultSet object) into it using the put() method.

Finally, add the JSON object to the array created in the previous step.

while(rs.next()) {
   JSONObject record = new JSONObject();
   //Inserting key-value pairs into the json object
   record.put("ID", rs.getInt("ID"));
   record.put("First_Name", rs.getString("First_Name"));
   record.put("Last_Name", rs.getString("Last_Name"));
   record.put("Date_Of_Birth", rs.getDate("Date_Of_Birth"));
   record.put("Place_Of_Birth", rs.getString("Place_Of_Birth"));
   record.put("Country", rs.getString("Country"));
   array.add(record);
}

Write the JSON object to a file using FileReader

• After adding all the required records of the JSON array add it to the parent JSON object using the put() method

jsonObject.put("Players_data", array);

• Write the created JSON object into a file using the FileWriter class as −

FileWriter file = new FileWriter("E:/json_array_output.json");
file.write(jsonObject.toJSONString());
file.close();

Following JDBC program reads the contents of the MyPlayers table into a JSON file.

Example

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
public class DataBaseToJson {
   public static ResultSet RetrieveData() throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sample_database";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Retrieving the records
      ResultSet rs = stmt.executeQuery("Select * from MyPlayers");
      return rs;
   }
   public static void main(String args[]) throws Exception {
      //Creating a JSONObject object
      JSONObject jsonObject = new JSONObject();
      //Creating a json array
      JSONArray array = new JSONArray();
      ResultSet rs = RetrieveData();
      //Inserting ResutlSet data into the json object
      while(rs.next()) {
         JSONObject record = new JSONObject();
         //Inserting key-value pairs into the json object
         record.put("ID", rs.getInt("ID"));
         record.put("First_Name", rs.getString("First_Name"));
         record.put("Last_Name", rs.getString("Last_Name"));
         record.put("Date_Of_Birth", rs.getDate("Date_Of_Birth"));
         record.put("Place_Of_Birth", rs.getString("Place_Of_Birth"));
         record.put("Country", rs.getString("Country"));
         array.add(record);
      }
      jsonObject.put("Players_data", array);
      try {
         FileWriter file = new FileWriter("E:/output.json");
         file.write(jsonObject.toJSONString());
         file.close();
      } catch (IOException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
      System.out.println("JSON file created......);
   }
}

Output

JSON file created......

If you observe the output.json file you can see the read content as −

{
   "Players_data":
   [
      {
         "First_Name":"Shikhar",
         "Place_Of_Birth":"Delhi",
         "Last_Name":"Dhawan",
         "Country":"India",
         "ID":1,
         "Date_Of_Birth":1981-12-05
      },
      {
         "First_Name":"Jonathan",
         "Place_Of_Birth":"CapeTown",
         "Last_Name":"Trott",
         "Country":"SouthAfrica",
         "ID":2,
         "Date_Of_Birth":1981-04-22
      },
      {
         "First_Name":"Kumara",
         "Place_Of_Birth":"Matale",
         "Last_Name":"Sangakkara",
         "Country":"Srilanka",
         "ID":3,
         "Date_Of_Birth":1977-10-27
      },
      {
         "First_Name":"Virat",
         "Place_Of_Birth":"Mumbai",
         "Last_Name":"Kohli",
         "Country":"India",
         "ID":4,
         "Date_Of_Birth":1988-11-05
      },
      {
         "First_Name":"Rohit",
         "Place_Of_Birth":"Nagpur",
         "Last_Name":"Sharma",
         "Country":"India",
         "ID":5,
         "Date_Of_Birth":1987-04-30
      },
      {
         "First_Name":"Ravindra",
         "Place_Of_Birth":"Nagpur",
         "Last_Name":"Jadeja",
         "Country":"India",
         "ID":6,
         "Date_Of_Birth":1988-12-06
      },
      {
         "First_Name":"James",
         "Place_Of_Birth":"Burnely",
         "Last_Name":"Anderson",
         "Country":"England",
         "ID":7,
         "Date_Of_Birth":1982-06-30
      },
      {
         "First_Name":"Ryan",
         "Place_Of_Birth":"South Africa",
         "Last_Name":"McLaren",
         "Country":"India",
         "ID":8,
         "Date_Of_Birth":1983-02-09
      }
   ]
}