Read from JSON file and persist into MySQL

Here is a simple example where we are reading contents of a JSON file and inserting into MySQL database. The code is dynamic so it can handle any number of columns no matter what the column name is. All you have to do is match the database column names with JSON content.

For this demo, we are using following jar files.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121

import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Iterator; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import net.sf.json.JSONSerializer; import org.apache.commons.io.IOUtils; public class MyJson {  private static String tableName = "jsontest";  public static void main(String[] args) {   try {    ClassLoader cl = MyJson.class.getClassLoader();    InputStream is = cl.getResourceAsStream("test.json");    String str = IOUtils.toString(is);    JSONObject jsonObject = (JSONObject) JSONSerializer.toJSON(str);    JSONArray jsonArr = jsonObject.getJSONArray("profiles");    JSONObject obj = null;    JSONArray nameArr = null;    JSONArray valArr = null;        for (int i = 0; i < jsonArr.size(); i++) {     obj = jsonArr.getJSONObject(i);     nameArr = obj.names();     valArr = obj.toJSONArray(nameArr);     saveRecord(nameArr, valArr);    }   } catch (Exception e) {    e.printStackTrace();   }  }  private static void saveRecord(JSONArray nameArray, JSONArray valArray) {   Connection conn = getConnection();   StringBuffer sb = new StringBuffer("insert into " + tableName + "(");   int size = nameArray.size();   int count = 0;   Iterator<Object> iterator = nameArray.iterator();      while (iterator.hasNext()) {    if (count < (size - 1))     sb.append(iterator.next() + ",");    else     sb.append(iterator.next() + ")");    count++;   }   sb.append(" values(");     for (int i = 0; i < size; i++) {      if (i < (size - 1))     sb.append("?,");    else     sb.append("?)");     }   System.out.println(sb.toString());   try {    PreparedStatement pstmt = conn.prepareStatement(sb.toString());    bindVariables(valArray, pstmt);    pstmt.executeUpdate();   } catch (SQLException e) {    e.printStackTrace();   }  }  private static void bindVariables(JSONArray valArray,    PreparedStatement pstmt) throws SQLException {   Iterator<Object> iterator = valArray.iterator();   int cnt = 0;   while (iterator.hasNext()) {    Object obj = iterator.next();    if (obj instanceof String) {     pstmt.setString(++cnt, (String) obj);    } else if (obj instanceof Integer) {     pstmt.setLong(++cnt, (Integer) obj);    } else if (obj instanceof Long) {     pstmt.setLong(++cnt, (Long) obj);    } else if (obj instanceof Double) {     pstmt.setDouble(++cnt, (Double) obj);    }   }  }  private static Connection getConnection() {   Connection con = null;   String url = "jdbc:mysql://localhost:3306/";   String db =  "user";   String driver = "com.mysql.jdbc.Driver";   String user = "root";   String pass = "hello";   try {    Class.forName(driver);    con = DriverManager.getConnection(url + db, user, pass);   } catch (ClassNotFoundException e) {    e.printStackTrace();   } catch (SQLException e) {    e.printStackTrace();   }   return con;  } }

And this the content of json file(test.json) we have.

?

1 2 3 4 5 6 7

{'profiles': [   {'name':'John', 'age': 44, 'phone':'203-203-2030'},   {'name':'Alex','age':31, 'phone':'203-203-2030'},   {'name':'Amy', 'age': 24, 'phone':'203-203-2030'},   {'name':'Melissa','age':21, 'phone':'203-203-2030'}  ] }