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;

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

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

您可能感兴趣的文章:Android SQLite数据库增删改查操作的使用详解Android SQLite数据库增删改查操作的案例分析Android使用SQLite数据库的简单实例android创建数据库(SQLite)保存图片示例android通过jxl读excel存入sqlite3数据库Android中的SQL查询语句LIKE绑定参数问题解决办法（sqlite数据库）Android中操作SQLite数据库快速入门教程Android操作存放在assets文件夹下SQLite数据库的方法Android操作SQLite数据库(增、删、改、查、分页等)及ListView显示数据的方法详解Android创建和使用数据库SQLIte