package cn.hackcoder.beautyreader.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Created by hackcoder on 15-1-25.
 */
public class DataBaseHelper extends SQLiteOpenHelper {
  private static final String dbName = "sample.db";
  private static int dbVersion = 1;

  public DataBaseHelper(Context context) {
    super(context,dbName,null,dbVersion);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
    Log.d("===========","數(shù)據(jù)庫初始化");
    //建表
    String sql = "create table if not exists tb_article(id integer primary key autoincrement,title varchar(50),content TEXT,url varchar(50),page integer)";
    db.execSQL(sql);
  }

  /**
   *
   * @param db
   * @param oldVersion
   * @param newVersion
   */
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

  }
}

package cn.hackcoder.beautyreader.service;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

import cn.hackcoder.beautyreader.db.DataBaseHelper;
import cn.hackcoder.beautyreader.model.Article;

/**
 * Created by hackcoder on 15-1-25.
 */
public class ArticleService {

  private DataBaseHelper dataBaseHelper;
  private SQLiteDatabase readableDatabase;
  private SQLiteDatabase writableDatabase;

  public ArticleService(Context context) {
    dataBaseHelper = new DataBaseHelper(context);
  }

  public void add(Article article) {
    String sql = "insert into tb_article(id,title,content,url,page) values(?,?,?,?,?)";
    getReadableDatabase().execSQL(sql, new Object[]{null, article.getTitle(), article.getContent(), article.getUrl(), article.getPage()});
  }

  public void delete(int id) {
    String sql = "delete from tb_article where id =?";
    getReadableDatabase().execSQL(sql, new Object[]{id});
  }

  public void deleteAll() {
    String sql = "delete from tb_article";
    getReadableDatabase().execSQL(sql,null);
  }
  public void update(Article article) {
    String sql = "update tb_article set title=?,content=?,url=?,page = ? where id =?";
    getReadableDatabase().execSQL(sql, new Object[]{article.getTitle(), article.getContent(), article.getUrl(), article.getPage(), article.getId()});
  }

  public void updateContentOfUrl(String url,String content){
    String sql = "update tb_article set content=? where url =?";
    getReadableDatabase().execSQL(sql, new Object[]{content,url});
  }
  public Article find(int id) {
    Article article = new Article();
    String sql = "select id,title,content,url,page from tb_article where id = ?";
    Cursor cursor = getReadableDatabase().rawQuery(sql, new String[]{String.valueOf(id)});
    if (cursor.moveToNext()) {
      article.setId(id);
      article.setTitle(cursor.getString(cursor.getColumnIndex("title")));
      article.setContent(cursor.getString(cursor.getColumnIndex("content")));
      article.setUrl(cursor.getString(cursor.getColumnIndex("url")));
      article.setPage(cursor.getInt(cursor.getColumnIndex("page")));
      cursor.close();
      return article;
    }
    cursor.close();
    return null;
  }

  public List<Article> findByUrl(String url) {
    List<Article> articles = new ArrayList<Article>();
    String sql = "select id,title,content,url,page from tb_article where url = ?";
    Cursor cursor = getReadableDatabase().rawQuery(sql, new String[]{url});
    while (cursor.moveToNext()) {
      Article article = new Article();
      article.setId(cursor.getInt(cursor.getColumnIndex("id")));
      article.setTitle(cursor.getString(cursor.getColumnIndex("title")));
      article.setContent(cursor.getString(cursor.getColumnIndex("content")));
      article.setUrl(cursor.getString(cursor.getColumnIndex("url")));
      article.setPage(cursor.getInt(cursor.getColumnIndex("page")));
      articles.add(article);
    }
    cursor.close();
    return articles;
  }
  public int getCountOfPage(int page){
    String sql = "select count(*) from tb_article where page = ?";
    Cursor cursor = getReadableDatabase().rawQuery(sql, new String[]{String.valueOf(page)});
    cursor.moveToFirst();
    int count = cursor.getInt(0);
    cursor.close();
    return count;
  }

  public List<Article> getArticlesOfPage(int curPage){
    List<Article> articles = new ArrayList<Article>();
    String sql = "select id,title,content,url,page from tb_article where page = ?";
    Cursor cursor = getReadableDatabase().rawQuery(sql,new String[]{String.valueOf(curPage)});
    while(cursor.moveToNext()){
      Article article = new Article();
      article.setId(cursor.getInt(cursor.getColumnIndex("id")));
      article.setTitle(cursor.getString(cursor.getColumnIndex("title")));
      article.setContent(cursor.getString(cursor.getColumnIndex("content")));
      article.setUrl(cursor.getString(cursor.getColumnIndex("url")));
      article.setPage(cursor.getInt(cursor.getColumnIndex("page")));
      articles.add(article);
    }
    cursor.close();
    return articles;
  }
  public int countOfSum() {
    String sql = "select count(*) from tb_article";
    Cursor cursor = getReadableDatabase().rawQuery(sql, null);
    cursor.moveToFirst();
    int count = cursor.getInt(0);
    cursor.close();
    return count;
  }

  public List<Article> getArticles(int start, int pageSize) {
    List<Article> articles = new ArrayList<Article>();
    String sql = "select id,title,content,url,page from tb_article limit ?,?";
    Cursor cursor = getReadableDatabase().rawQuery(sql,new String[]{String.valueOf(start),String.valueOf(pageSize)});
    while(cursor.moveToNext()){
      Article article = new Article();
      article.setId(cursor.getInt(cursor.getColumnIndex("id")));
      article.setTitle(cursor.getString(cursor.getColumnIndex("title")));
      article.setContent(cursor.getString(cursor.getColumnIndex("content")));
      article.setUrl(cursor.getString(cursor.getColumnIndex("url")));
      article.setPage(cursor.getInt(cursor.getColumnIndex("page")));
      articles.add(article);
    }
    cursor.close();
    return articles;
  }

  public void closeDB() {
    if (readableDatabase != null && readableDatabase.isOpen()) {
      readableDatabase.close();
    }
    if (writableDatabase != null && writableDatabase.isOpen()) {
      writableDatabase.close();
    }
  }

  public SQLiteDatabase getReadableDatabase() {
    return dataBaseHelper.getReadableDatabase();
  }

  public SQLiteDatabase getWritableDatabase() {
    return dataBaseHelper.getWritableDatabase();
  }
}