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'}
 ]
}
 
Advertisements

2 thoughts on “Read from JSON file and persist into MySQL

  1. interior decorating December 24, 2012 at 3:32 pm Reply

    Wow, awesome weblog format! How lengthy have you been running a blog for? you make running a blog glance easy. The full glance of your web site is wonderful, well as} the content!

  2. kiran June 18, 2013 at 2:34 pm Reply

    nice one…love this one….can you post “reading columns from .json fole and print it into browser”……thanks in advance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: