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.
And this the content of json file(test.json) we have.
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 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; } } |
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' } ] }
|
No comments:
Post a Comment