Android SQLite数据库操作代码类分享

使用示例：

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("===========","数据库初始化");
    //建表
    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();
  }
}