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