Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to read/retrieve 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
}
]
}
Updated on: 30-Jul-2019
9K+ Views
Advertisements