dbstorejdbc.java

网页采集系统 ================= 安装配置 ------- 1 程序我就不说了 2 配置文件 applicationContext.xml 里面有详细的注释 3 已经

package com.laozizhu.search.store;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import com.laozizhu.search.Item;
import com.laozizhu.search.ItemBase;
import com.laozizhu.search.SearchResult;
import com.laozizhu.search.SearchResultBase;
import com.laozizhu.search.Store;
import com.laozizhu.search.util.DBUtil;

/**
 * 数据库存储的实现。<br>
 * 在数据库里面保存数据，可以设定数据源和表格名字。
 * 
 * @author 老紫竹(laozizhu.com)
 */
public class DBStoreJDBC implements Store {
  private static final String FIELD_DEFAULT_NO_ID = "url,author,title,datetimeCreate,body";

  private static final String FIELD_DEFAULT = "id," + FIELD_DEFAULT_NO_ID;

  public void deleteByUrl(String url) {
    if (url == null) {
      return;
    }
    Connection con = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    try {
      con = getDataSource().getConnection();
      stat = con.prepareStatement("delete from " + getTableName() + " where url=?");
      stat.setString(1, url);
      stat.executeUpdate();
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      DBUtil.close(con, stat, rs);
    }
  }

  public void optimize() {
    // 暂时没有实质性优化可做，不过可以重建一下索引啥的
  }

  public boolean save(Item item) {
    if (item == null || item.getAuthor() == null || item.getTitle() == null || item.getBody() == null
        || item.getDatetimeCreate() == null) {
      return false;
    }
    deleteByUrl(item.getUrl());
    Connection con = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    try {
      con = getDataSource().getConnection();
      stat = con.prepareStatement("insert into " + getTableName() + "(" + FIELD_DEFAULT_NO_ID + ")values(?,?,?,?,?)");
      stat.setString(1, item.getUrl());
      stat.setString(2, item.getAuthor());
      stat.setString(3, item.getTitle());
      stat.setString(4, item.getDatetimeCreate());
      stat.setString(5, item.getBody());
      stat.executeUpdate();
      return true;
    } catch (Exception ex) {
      ex.printStackTrace();
      return false;
    } finally {
      DBUtil.close(con, stat, rs);
    }
  }

  // TODO: 未实现汇总数
  public SearchResult seach(String keyword, int begin, int number) {
    if (keyword == null) {
      return null;
    }
    Connection con = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    SearchResult sr = new SearchResultBase();
    try {
      con = getDataSource().getConnection();
      stat = con.prepareStatement("select " + FIELD_DEFAULT + " from " + getTableName()
          + " where title like ? or body like ? limit ?,?");
      stat.setString(1, "%" + keyword + "%");
      stat.setString(2, "%" + keyword + "%");
      stat.setInt(3, begin);
      stat.setInt(4, begin);
      rs = stat.executeQuery();
      List<Item> list = new ArrayList<Item>();
      if (rs.next()) {
        Item o = new ItemBase();
        o.setId(rs.getInt("id"));
        o.setUrl(rs.getString("url"));
        o.setAuthor(rs.getString("author"));
        o.setTitle(rs.getString("title"));
        o.setDatetimeCreate(rs.getString("datetimeCreate"));
        o.setBody(rs.getString("body"));
        list.add(o);
      }
      sr.setReturnList(list);
      return sr;
    } catch (Exception ex) {
      ex.printStackTrace();
      return null;
    } finally {
      DBUtil.close(con, stat, rs);
    }
  }

  public Item searchByUrl(String url) {
    if (url == null) {
      return null;
    }
    Connection con = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    try {
      con = getDataSource().getConnection();
      stat = con.prepareStatement("select  " + FIELD_DEFAULT + " from " + getTableName() + " where url=?");
      stat.setString(1, url);
      rs = stat.executeQuery();
      if (rs.next()) {
        Item o = new ItemBase();
        o.setId(rs.getInt("id"));
        o.setUrl(url);
        o.setAuthor(rs.getString("author"));
        o.setTitle(rs.getString("title"));
        o.setDatetimeCreate(rs.getString("datetimeCreate"));
        o.setBody(rs.getString("body"));
        return o;
      }
      return null;
    } catch (Exception ex) {
      ex.printStackTrace();
      return null;
    } finally {
      DBUtil.close(con, stat, rs);
    }
  }

  public Item searchById(int id) {
    if (id < 0) {
      return null;
    }
    Connection con = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    try {
      con = getDataSource().getConnection();
      stat = con.prepareStatement("select  " + FIELD_DEFAULT + " from " + getTableName() + " where id=?");
      stat.setInt(1, id);
      rs = stat.executeQuery();
      Item o = null;
      if (rs.next()) {
        o = new ItemBase();
        o.setId(id);
        o.setUrl(rs.getString("url"));
        o.setAuthor(rs.getString("author"));
        o.setTitle(rs.getString("title"));
        o.setDatetimeCreate(rs.getString("datetimeCreate"));
        o.setBody(rs.getString("body"));
      }
      return o;
    } catch (Exception ex) {
      ex.printStackTrace();
      return null;
    } finally {
      DBUtil.close(con, stat, rs);
    }
  }

  /**
   * 数据库源
   */
  private DataSource dataSource;

  public DataSource getDataSource() {
    return dataSource;
  }

  public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
  }

  /**
   * 表格的名字
   */
  private String tableName = "LzzSearch";

  public String getTableName() {
    return tableName;
  }

  public void setTableName(String tableName) {
    this.tableName = tableName;
  }

  public void deleteById(int id) {
    // TODO Auto-generated method stub
    
  }

  public List<Item> findAll(int begin, int number) {
    // TODO Auto-generated method stub
    return null;
  }
}