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