android sqlite 类使用,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 findByUrl(String url) {

List articles = new ArrayList();

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 getArticlesOfPage(int curPage){

List articles = new ArrayList();

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 getArticles(int start, int pageSize) {

List articles = new ArrayList();

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

}

}