WebCrawler Java小爬爬 从入门到放弃 第六章

上一章 博主细化了写入本地的类，建立了目录形式，其中用到了FamousBookLocalInfo书籍本地存储信息类。
说是本地存储信息，其实是将本地存储的书籍信息概要写到数据库里，然后通过这个类进行相关数据库的操作。
由于我们在爬的过程中经常访问数据库，前面我们也用外观模式写了一个数据库链接类，用PreparedStatement优化操作速度。这里我们用饿汉式单例模式写这个FamousBookLocalInfo类。
关于注释，因为这几天很忙，博主之前就是用的英文注释，且注释英语较为简单
所以没有改成中文。
Code：

import java.sql.*;
import DB_Link_info.*;
/**
 * 
 * Query famous book local information
 * @author Mr.Dragon
 * @Time 2016-9-20 
 * @version   1.0
 * 
 */
public class FamousBookLocalInfo { 
    static{
        System.out.println("Trying to connect the database.");
    }
    private static FamousBookLocalInfo instance = new FamousBookLocalInfo();
    private static Connection con = DB_Link.getConnection();
    private static PreparedStatement psta = null;   
    public static synchronized FamousBookLocalInfo getInstance()              
    {
        if(instance==null)
            try {
                instance=new FamousBookLocalInfo();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                O.p("Famous book local information has a "
                        + "initialization failure");
            }   
        return instance;
    }

    public static boolean authorIsExisted(String author){ 
        //Judge whether author's name has existed in database
        String sql = "select author from bookInfo where author = ?";
        try {
            psta = con.prepareStatement(sql);
            psta.setString(1,author);
            ResultSet rs = psta.executeQuery();
            if(!rs.next()){
                O.p("There is no infomation of "+author+" in database");
                return false;
            }
            psta.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block  
            O.p("Query failed"+e);
        }
        return true;
    }


    public static void insertBookInfo(String book,String author){
        //write book's information into database
        String sql ="insert into bookInfo(bookname,author)values(?,?)";
        try {
            if(null == getStatus(book)){
                psta = con.prepareStatement(sql);
                psta.setString(1,book);
                psta.setString(2,author);
                psta.executeUpdate();
                psta.close();
            }       
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            O.p(book+" has a saved error"+e);
        }
    }

    /*--------It's not as useful as function getStatus()------------
    public static boolean bookIsExisted(String bookname){  
        //whether book's name has existed in database
        String sql = "select bookname from bookInfo where bookname = ?";
        try {
            psta = con.prepareStatement(sql);
            psta.setString(1,bookname);
            ResultSet rs = psta.executeQuery();
            if(0 == rs.getRow()){
                System.out.println("There is no infomation of "+bookname
                        +" in database");
                return false;
            }
            psta.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return true;
    }*/
    public static void updateWordsnumAndStatus(int num){
        /**
         * update words' number and book's status in database
         * @return nothing
         * @error SQLException
         * 
         */
        String sql = "update bookInfo set wordsnum += ?";
        try {
            psta = con.prepareStatement(sql);
            psta.setInt(1,num);
            psta.executeUpdate();
            psta.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static String getStatus(String bookname){
        String status = null;
        String sql = "select status from bookInfo where bookname = ?";
        try {
            psta = con.prepareStatement(sql);
            psta.setString(1,bookname);
            ResultSet rs = psta.executeQuery();
            if(rs.next()){
                status = rs.getString("status");
            }
            psta.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            O.p(bookname+" status gained failed"+e);
        }
        return status;
    }
    public static void setStatusEnd(String bookname){
        String sql = "update bookInfo set status='end' where bookname = ?";
        try {
            psta = con.prepareStatement(sql);
            psta.setString(1,bookname);
            psta.executeUpdate();
            psta.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            O.p(bookname+" status gained failed"+e);
        }
    }
}

关于bookIsExisted()这个方法，本来是这样想的：
if 作者存在{
if 书籍存在{
append方式写入书籍信息
}
else 书籍开头 写入书名、作者等信息
}
else 建立某作者文件夹
最后思量，在数据库建表时，我们分析的是近代名著，不用书籍名+作者名联合做主键 一个书名做主键就够了 所以加一个status（书籍状态信息）完美解决
关于数据库建表：

CREATE TABLE bookInfo(
bookname NVARCHAR(20),
author NVARCHAR(15),
wordsnum INT default 0,
status VARCHAR(15) default 'new',
PRIMARY KEY (bookname),
CHECK (status IN('downloading','end','new')) 
);

if null=status{
书籍不存在；
写入书籍信息；
跟新status=new；
}
if new=status{
**.txt 开头写入书籍简要信息
}
append方式写入书籍信息；

明天再写一章应该就结束了我们的小爬爬之旅了。
大家十一快乐！