nowindxdw
11/2/2017 - 3:07 AM
Android SQLite
Android SQLite数据库操作代码类分享
投稿：junjie 字体：[ 增加 减小] 类型：转载
这篇文章主要介绍了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();
}
}