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字符，我的处理方式就是过滤后再插入，因为特殊的字符其实也没什么用。

public static String StringFilter(String str) throws PatternSyntaxException {
    // 清除掉所有特殊字符,只允许汉字字母数字和某些常见符号出现
    String regEx = "[^0-9a-zA-Z\u4e00-\u9fa5~!@#$%^&*()+=|{}':;',//[//].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]+";
    Pattern p = Pattern.compile(regEx);
    Matcher m = p.matcher(str);
    return m.replaceAll(" ").trim().replaceAll("s+", " ");
}

public static void insertMicroblogs(String uid, String mid, String time,String geo,String source,
String repost,String comment,String attitude,String text,int flag,Statement statement) throws Exception {
    if (uid == null) return;
    String sql = "insert into data2016.microblog values(\"" + mid+  "\",\"" + uid +  "\",\"" + time
        +  "\",\"" + geo+  "\",\"" + source +  "\",\"" +repost +  "\",\"" + comment +  "\",\"" + attitude
        +  "\",\"" + text+ "\"," + flag + ")";
    String selectsql = "select count(*) from data2016.microblog where uid = \""+ uid+ "\" and mid=\"" + mid + "\"";

    try {
        ResultSet rset = statement.executeQuery(selectsql);
        int rowCount = 0; //记录查询结果记录数
        if(rset.next()) { 
            rowCount=rset.getInt(1); 
        }
        if(rowCount == 0){
            statement.execute(sql);
        }

    } catch (Exception e) {
        pErrorUser.println("microblog:"+uid); 
//      System.out.println(sql);
        e.printStackTrace();
        return;
    }
}
public static void readUserMicroblogs() throws Exception{
    File file = new File("data/source/first/microblogs/microblogs12.txt");   
    BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file));    
    BufferedReader reader = new BufferedReader(new InputStreamReader(fis,"utf-8"),5*1024*1024);// 用5M的缓冲读取文本文件  

    String line = "";
    int count = 0;
    while((line = reader.readLine()) != null){
        count++;
        JSONObject blogObject = new JSONObject(line);
        String uid = blogObject.getString("uid");
        String microblogs = blogObject.getString("microblogs");
        JSONArray microblogsArray = new JSONArray(microblogs);
        int size = microblogsArray.length();
//      System.out.println("Size:" + size);
        for (int i = 0; i < size; i++) {
                      JSONObject jsonObj = microblogsArray.getJSONObject(i);
              String mid=jsonObj.get("mid").toString();
              String created_at=jsonObj.get("created_at").toString();
              String geo=StringFilter(jsonObj.get("geo").toString().replaceAll("\"", " "));
              String source=jsonObj.get("source").toString().replaceAll("\"", " ");
              String reposts_count=jsonObj.get("reposts_count").toString();
              String comments_count=jsonObj.get("comments_count").toString();
              String attitudes_count=jsonObj.get("attitudes_count").toString();
              String text;
              int flag;
              if(jsonObj.has("retweeted_status")){
                  flag=1;
                  String retweet = jsonObj.get("retweeted_status").toString();
                  JSONObject weibo = new JSONObject(retweet);
                  text=StringFilter(weibo.get("text").toString().replaceAll("\"", " "));
              }else{
                  flag=0;
                  text=StringFilter(jsonObj.get("text").toString().replaceAll("\"", " "));
              }
              insertMicroblogs(uid,mid,created_at,geo,source,reposts_count,comments_count,attitudes_count,text,flag,statement);

            }

            if(count%50==0){
                System.out.print(count +"...");
            }
            if(count%1000==0){
                System.out.println();
            }
        }
}

 

 

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

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

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