Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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 insert/store JSON array into a database 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 with in 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 create a JSON document (players_data.json) with an array of documents where, each document in the array represents a record in the MyPlayers table created above as shown below −
{
"players_data": [
{
"ID": "1",
"First_Name": "Shikhar",
"Last_Name": "Dhawan",
"Date_Of_Birth": "1981-12-05",
"Place_Of_Birth":"Delhi",
"Country": "India"
},
{
"ID": "2",
"First_Name": "Jonathan",
"Last_Name": "Trott",
"Date_Of_Birth": "1981-04-22",
"Place_Of_Birth":"CapeTown",
"Country": "SouthAfrica"
},
{
"ID": "3",
"First_Name": "Kumara",
"Last_Name": "Sangakkara",
"Date_Of_Birth": "1977-10-27",
"Place_Of_Birth":"Matale",
"Country": "Srilanka"
},
{
"ID": "4",
"First_Name": "Virat",
"Last_Name": "Kohli",
"Date_Of_Birth": "1988-11-05",
"Place_Of_Birth":"Mumbai",
"Country": "India"
},
{
"ID": "5",
"First_Name": "Rohit",
"Last_Name": "Sharma",
"Date_Of_Birth": "1987-04-30",
"Place_Of_Birth":"Nagpur",
"Country": "India"
},
{
"ID": "6",
"First_Name": "Ravindra",
"Last_Name": "Jadeja",
"Date_Of_Birth": "1988-12-06",
"Place_Of_Birth":"Nagpur",
"Country": "India"
},
{
"ID": "7",
"First_Name": "James",
"Last_Name": "Anderson",
"Date_Of_Birth": "1982-06-30",
"Place_Of_Birth":"Burnely",
"Country": "England"
},
{
"ID": "8",
"First_Name": "Ryan",
"Last_Name": "McLaren",
"Date_Of_Birth": "1983-02-09",
"Place_Of_Birth":"South Africa",
"Country": "India"
}
]
}
To write the contents of the a JSON array to a database using JDBC −
Obtain connection to a database
- 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");
Read the contents of a JSON file
- Instantiate the JSONParser class of the json-simple library.
JSONParser jsonParser = new JSONParser();
- Parse the contents of the obtained object using the parse() method.
//Parsing the contents of the JSON file
JSONObject jsonObject = (JSONObject) jsonParser(new FileReader("E:/players_data.json"));
- Retrieve the json array using the get() method into the JSONArray object.
JSONArray jsonArray = (JSONArray) jsonObject.get("contact");
Insert read JSON contents to MyPlayers table
- Create a PreparedStatement object to insert values into the table MyPLayers.
PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyPlayers values (?, ?, ?, ?, ?, ? )");
- For each record in the JSON array retrieve the key-value pairs using the get method and set value to the appropriate bind variable of the PreparedStatement using the setXXX() methods.
JSONObject record = (JSONObject) object;
int id = Integer.parseInt((String) record.get("ID"));
String first_name = (String) record.get("First_Name");
pstmt.setInt(1, id);
pstmt.setString(2, first_name);
Following JDBC program inserts the contents of the players_data.json file into the MyPlayers table.
Example
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;
public class JsonToDatabase {
public static Connection ConnectToDB() throws Exception {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
return con;
}
public static void main(String args[]) {
//Creating a JSONParser object
JSONParser jsonParser = new JSONParser();
try {
//Parsing the contents of the JSON file
JSONObject jsonObject = (JSONObject) jsonParser.parse(new FileReader("E:/players_data.json"));
//Retrieving the array
JSONArray jsonArray = (JSONArray) jsonObject.get("players_data");
Connection con = ConnectToDB();
//Insert a row into the MyPlayers table
PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyPlayers values (?, ?, ?, ?, ?, ? )");
for(Object object : jsonArray) {
JSONObject record = (JSONObject) object;
int id = Integer.parseInt((String) record.get("ID"));
String first_name = (String) record.get("First_Name");
String last_name = (String) record.get("Last_Name");
String date = (String) record.get("Date_Of_Birth");
long date_of_birth = Date.valueOf(date).getTime();
String place_of_birth = (String) record.get("Place_Of_Birth");
String country = (String) record.get("Country");
pstmt.setInt(1, id);
pstmt.setString(2, first_name);
pstmt.setString(3, last_name);
pstmt.setDate(4, new Date(date_of_birth));
pstmt.setString(5, place_of_birth);
pstmt.setString(6, country);
pstmt.executeUpdate();
}
System.out.println("Records inserted.....");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Output
Connection established...... Records inserted......
If you verify the contents of the MyPlayers table using the SELECT statement you can see the inserted records as −
mysql> select * from MyPlayers; +----+------------+------------+---------------+----------------+-------------+ | ID | First_Name | Last_Name | Date_Of_Birth | Place_Of_Birth | Country | +----+------------+------------+---------------+----------------+-------------+ | 1 | Shikhar | Dhawan | 1981-12-05 | Delhi | India | | 2 | Jonathan | Trott | 1981-04-22 | CapeTown | SouthAfrica | | 3 | Kumara | Sangakkara | 1977-10-27 | Matale | Srilanka | | 4 | Virat | Kohli | 1988-11-05 | Mumbai | India | | 5 | Rohit | Sharma | 1987-04-30 | Nagpur | India | | 6 | Ravindra | Jadeja | 1988-12-06 | Nagpur | India | | 7 | James | Anderson | 1982-06-30 | Burnely | England | | 8 | Ryan | McLaren | 1983-02-09 | South Africa | India | +----+------------+------------+---------------+----------------+-------------+ 8 rows in set (0.00 sec
Updated on: 2019-07-30T22:30:26+05:30
10K+ Views
Advertisements