2018/6/1 山东大学学习资源聚合平台工作

写了外围审核系统的数据库内容导入：

package DataProcess;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import Model.Knowledge;

public class ReadData{  
	private String url = "jdbc:mysql://localhost:3306/platform?useUnicode=true&characterEncoding=UTF-8";
	private String driver = "com.mysql.jdbc.Driver"; 
	private String userName = "root";
	private String password = "scx199702";
	
	static String filePathIn = "F://Ruby//articles_cleaned.sql";
    
    public  void init(){
    	try {
			execute(filePathIn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    
    /*
    * 读取sql文件,获取sql语句
    * 返回所有sql语句的list集合
    * */
    private List<String> loadSql(String sqlFile) throws Exception {
    	List<String> sqlList = new ArrayList<String>();
		/*
		* 读取文件的内容并写道StringBuffer中去
		* */
    	InputStream sqlFileIn = new FileInputStream(sqlFile);
    	InputStreamReader reader = new InputStreamReader(sqlFileIn,"UTF-8"); //最后的"GBK"根据文件属性而定，如果不行，改成"UTF-8"试试
        BufferedReader br = new BufferedReader(reader);
	    StringBuffer sqlSb = new StringBuffer();
	    byte[] buff = new byte[sqlFileIn.available()];
	    String line;
	    while((line = br.readLine()) != null) {
	         sqlSb.append(line);
	    }
	      /*
	      * windows下换行是/r/n，Linux下是/n，
	         * 此处需要根据导出的sql文件进行具体的处理，我在处理的时候
	         * 也遇到了很多的问题，如果我这个不行可以在网上找找别的解析方法
	        * */
         String sqlArr[] = sqlSb.toString().split(";\r\n");
         for(int i = 0; i<sqlArr.length; i++) {
            String sql = sqlArr[i].replaceAll("--.*", "").trim();
            if(!"".equals(sql)) {
                 sqlList.add(sql);
            }
         }
            return sqlList;
             
    }
         
         /*
          * 传入文件执行sql语句
          * 
          * */
     private void execute(String sqlFile) throws SQLException {
         Statement stmt = null;
         List<String> sqlList = new ArrayList<String>();
         Connection conn = getConnection();
         try {
        	 sqlList = loadSql(sqlFile);
             conn.setAutoCommit(false);
             stmt = conn.createStatement();
             for (String sql : sqlList) {
                 System.out.println(sql);
                 stmt.addBatch(sql);
             }
             int[] rows = stmt.executeBatch();
             System.out.println("Row count:" + Arrays.toString(rows));
             conn.commit();
             System.out.println("数据更新成功");
         } catch (Exception e) {
             e.printStackTrace();
             conn.rollback();
         }finally{
             stmt.close();
             conn.close();
        }
     }
    public ArrayList<Knowledge> getAllKnowledge(){
    	ArrayList<Knowledge> re = new ArrayList<Knowledge>();
    	Connection con = getConnection();
    	String sql = "SELECT * FROM articles_cleaned";
    	Statement stmt;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
	        while(rs.next()){
	            //Retrieve by column name
	            String article_type  = rs.getString("article_type");
	            String created_time  = rs.getString("created_time");
	            String nick_name  = rs.getString("nick_name");
	            String article_title  = rs.getString("article_title");
	            String article_link  = rs.getString("article_link");
	            String user_link  = rs.getString("user_link");
	            String view_number  = rs.getString("view_number");
	            String spider_time  = rs.getString("spider_time");
	            String article_content  = rs.getString("article_content");
	            Knowledge k = new Knowledge(article_type,created_time,nick_name,article_title,article_link,user_link,view_number,spider_time,article_content);
	            re.add(k);
	         }
	         rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        return re;
    }
         /*
         * 获取sql连接
         * */
    private Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, password);
            if(!conn.isClosed()) {
                System.out.println("数据库连接成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}  

利用sql文件初始化数据库，并对应到内存中去，Knowledge如下：

package Model;

public class Knowledge {
	public String article_type;
	public String created_time;
	public String nick_name;
	public String article_link;
	public String article_title;
	public String user_link;
	public String view_number;
	public String spider_time;
	public String article_content;
	public Knowledge(){
		
	}
	public Knowledge(String article_type, String created_time, String nick_name, String article_title,String article_link, String user_link,
			String view_number, String spider_time, String article_content) {
		this.article_type = article_type;
		this.created_time = created_time;
		this.nick_name = nick_name;
		this.article_title = article_title;
		this.article_link = article_link;
		this.user_link = user_link;
		this.view_number = view_number;
		this.spider_time = spider_time;
		this.article_content = article_content;
	}
}

然后进行敏感词过滤，可以看到如下结果：