JSON文件存入MySQL数据库

目标:将不同格式的JSON文件存入MySQL数据库
涉及的点有:
1. java处理JSON对象,直接见源码。
2. java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x99\x8F\xE5\x8D…’ for column ‘text’ at row 1报错问题,报错原因:因为我没有对插入文本做任何处理,文本内有不同字节的utf8字符,我的处理方式就是过滤后再插入,因为特殊的字符其实也没什么用。

 1 public static String StringFilter(String str) throws PatternSyntaxException {
 2     // 清除掉所有特殊字符,只允许汉字字母数字和某些常见符号出现
 3     String regEx = "[^0-9a-zA-Z\u4e00-\u9fa5~!@#$%^&*()+=|{}':;',//[//].<>/?~!@#¥%……&*()——+|{}【】‘;:”“’。,、?]+";
 4     Pattern p = Pattern.compile(regEx);
 5     Matcher m = p.matcher(str);
 6     return m.replaceAll(" ").trim().replaceAll("s+", " ");
 7 }
 8 
 9 public static void insertMicroblogs(String uid, String mid, String time,String geo,String source,
10 String repost,String comment,String attitude,String text,int flag,Statement statement) throws Exception {
11     if (uid == null) return;
12     String sql = "insert into data2016.microblog values(\"" + mid+  "\",\"" + uid +  "\",\"" + time
13         +  "\",\"" + geo+  "\",\"" + source +  "\",\"" +repost +  "\",\"" + comment +  "\",\"" + attitude
14         +  "\",\"" + text+ "\"," + flag + ")";
15     String selectsql = "select count(*) from data2016.microblog where uid = \""+ uid+ "\" and mid=\"" + mid + "\"";
16 
17     try {
18         ResultSet rset = statement.executeQuery(selectsql);
19         int rowCount = 0; //记录查询结果记录数
20         if(rset.next()) { 
21             rowCount=rset.getInt(1); 
22         }
23         if(rowCount == 0){
24             statement.execute(sql);
25         }
26 
27     } catch (Exception e) {
28         pErrorUser.println("microblog:"+uid); 
29 //      System.out.println(sql);
30         e.printStackTrace();
31         return;
32     }
33 }
34 public static void readUserMicroblogs() throws Exception{
35     File file = new File("data/source/first/microblogs/microblogs12.txt");   
36     BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file));    
37     BufferedReader reader = new BufferedReader(new InputStreamReader(fis,"utf-8"),5*1024*1024);// 用5M的缓冲读取文本文件  
38 
39     String line = "";
40     int count = 0;
41     while((line = reader.readLine()) != null){
42         count++;
43         JSONObject blogObject = new JSONObject(line);
44         String uid = blogObject.getString("uid");
45         String microblogs = blogObject.getString("microblogs");
46         JSONArray microblogsArray = new JSONArray(microblogs);
47         int size = microblogsArray.length();
48 //      System.out.println("Size:" + size);
49         for (int i = 0; i < size; i++) {
50                       JSONObject jsonObj = microblogsArray.getJSONObject(i);
51               String mid=jsonObj.get("mid").toString();
52               String created_at=jsonObj.get("created_at").toString();
53               String geo=StringFilter(jsonObj.get("geo").toString().replaceAll("\"", " "));
54               String source=jsonObj.get("source").toString().replaceAll("\"", " ");
55               String reposts_count=jsonObj.get("reposts_count").toString();
56               String comments_count=jsonObj.get("comments_count").toString();
57               String attitudes_count=jsonObj.get("attitudes_count").toString();
58               String text;
59               int flag;
60               if(jsonObj.has("retweeted_status")){
61                   flag=1;
62                   String retweet = jsonObj.get("retweeted_status").toString();
63                   JSONObject weibo = new JSONObject(retweet);
64                   text=StringFilter(weibo.get("text").toString().replaceAll("\"", " "));
65               }else{
66                   flag=0;
67                   text=StringFilter(jsonObj.get("text").toString().replaceAll("\"", " "));
68               }
69               insertMicroblogs(uid,mid,created_at,geo,source,reposts_count,comments_count,attitudes_count,text,flag,statement);
70 
71             }
72 
73             if(count%50==0){
74                 System.out.print(count +"...");
75             }
76             if(count%1000==0){
77                 System.out.println();
78             }
79         }
80 }

 

 

如上代码是我对新浪微博数据文档的存入工作,因为文档较大,所以加入了缓存读取。遇到的其他问题基本都是特殊字符问题,其中插入文本中有双引号,原本处理方法是:

String source=jsonObj.get("source").toString().replaceAll("\"", "\\\" ");

但是不知道为嘛,转义字符没有成功加入,所以就直接将双引号替换为空格处理了。这一块内容原本不打算记,原以为是很顺利的事还是倒腾了一天,所以小记一下咯。

posted on 2016-05-17 19:43  开心咿呀  阅读(9289)  评论(0编辑  收藏  举报