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();  }}