How to parse a JSON document into a database

java, json, mysql, parsing

Solution

Java interface for accessing databases is Java Database Connectivity (JDBC). Using JDBC you can create a connection to the database, issue database queries and updates and receive the results. try the following code

  private Connection connect = null;
  PreparedStatement preparedStatement = null;

 public int save() throws Exception {
   int status = 0;  
    try {
      // Load the MySQL driver, each DB has its own driver
      Class.forName("com.mysql.jdbc.Driver");

      // DB connection setup 
      connect = DriverManager.getConnection("jdbc:mysql://dbhost/database?" + "user=sqluser&password=sqluserpw");

      // PreparedStatements 
      preparedStatement = connect
          .prepareStatement("insert into  Table_Name values (?, ?, ?, ? )");

      Object obj = parser.parse(new FileReader("c.\\itemize.json"));
      JSONObject jsonObject = (JSONObject) obj;

      String pr = (String) jsonObject.get("Pr");
      // Parameters start with 1
      preparedStatement.setString(1, pr);

      String n = (String) itemize.get("n");
      preparedStatement.setString(2, n);

      String yst = (String) jsonObject.get("yst");
      preparedStatement.setString(3, yst);

      String wh = (String) itemize.get("wh");
      preparedStatement.setString(4, wh);

      status = preparedStatement.executeUpdate();

    } catch (Exception e) {
      throw e;
    } finally {
      try {
          if (connect != null) {
             connect.close();
           }

         } catch (Exception e) {

         }
    }
    return status;
  }

Problem

I have a JSON document with information on a product, and I want to parse the JSON document and put it into a database. An Example JSON document: ``` { "itemize": { "pr": "2583", "n": "Chocolate donut", "yst": "A beautiful, premium chocolate donut" "wh": 2.99 } ``` Here is the code I have so far: ``` import java.io.FileNotFoundException; import java.io.FileReader; import java.io.IOException; import java.util.Iterator; import org.json.simple.JSONArray; import org.json.simple.JSONObject; import org.json.simple.parser.JSONParser; import org.json.simple.parser.ParseException; public class q1 { public static void addProduct() { JSONParser parser=new JSONParser(); try{ Object obj = parser.parse(new FileReader("c.\\itemize.json")); JSONObject jsonObject = (JSONObject) obj; String pr = (String) jsonObject.get("Pr"); //Put pr into database String n = (String) jsonObject.get("n"); //Put n into database String yst = (String) jsonObject.get("yst"); //Put yst into database String wh = (String) jsonObject.get("wh"); //Put wh into database } } } ``` The database is in MySQL and has all these columns already. I just need to replace the commented lines in the java code with lines that will put the string into the database. This is what the database looks like: ``` Pr VARCHAR(30) NOT NULL, n VARCHAR(30) NOT NULL, yst VARCHAR(30) NOT NULL, wh VARCHAR(30) NOT NULL, Primary Key (Product_ID)); ```

Original source