Android SQLite数据库操作代码类分享
使用示例：
packagecn.hackcoder.beautyreader.db;
importandroid.content.Context;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.database.sqlite.SQLiteOpenHelper;
importandroid.util.Log;
/**
*Createdbyhackcoderon15-1-25.
*/
publicclassDataBaseHelperextendsSQLiteOpenHelper{
privatestaticfinalStringdbName="sample.db";
privatestaticintdbVersion=1;
publicDataBaseHelper(Contextcontext){
super(context,dbName,null,dbVersion);
}
@Override
publicvoidonCreate(SQLiteDatabasedb){
Log.d("===========","数据库初始化");
//建表
Stringsql="createtableifnotexiststb_article(idintegerprimarykeyautoincrement,titlevarchar(50),contentTEXT,urlvarchar(50),pageinteger)";
db.execSQL(sql);
}
/**
*
*@paramdb
*@paramoldVersion
*@paramnewVersion
*/
@Override
publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){
}
}
类源码：
packagecn.hackcoder.beautyreader.service;
importandroid.content.Context;
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
importjava.util.ArrayList;
importjava.util.List;
importcn.hackcoder.beautyreader.db.DataBaseHelper;
importcn.hackcoder.beautyreader.model.Article;
/**
*Createdbyhackcoderon15-1-25.
*/
publicclassArticleService{
privateDataBaseHelperdataBaseHelper;
privateSQLiteDatabasereadableDatabase;
privateSQLiteDatabasewritableDatabase;
publicArticleService(Contextcontext){
dataBaseHelper=newDataBaseHelper(context);
}
publicvoidadd(Articlearticle){
Stringsql="insertintotb_article(id,title,content,url,page)values(?,?,?,?,?)";
getReadableDatabase().execSQL(sql,newObject[]{null,article.getTitle(),article.getContent(),article.getUrl(),article.getPage()});
}
publicvoiddelete(intid){
Stringsql="deletefromtb_articlewhereid=?";
getReadableDatabase().execSQL(sql,newObject[]{id});
}
publicvoiddeleteAll(){
Stringsql="deletefromtb_article";
getReadableDatabase().execSQL(sql,null);
}
publicvoidupdate(Articlearticle){
Stringsql="updatetb_articlesettitle=?,content=?,url=?,page=?whereid=?";
getReadableDatabase().execSQL(sql,newObject[]{article.getTitle(),article.getContent(),article.getUrl(),article.getPage(),article.getId()});
}
publicvoidupdateContentOfUrl(Stringurl,Stringcontent){
Stringsql="updatetb_articlesetcontent=?whereurl=?";
getReadableDatabase().execSQL(sql,newObject[]{content,url});
}
publicArticlefind(intid){
Articlearticle=newArticle();
Stringsql="selectid,title,content,url,pagefromtb_articlewhereid=?";
Cursorcursor=getReadableDatabase().rawQuery(sql,newString[]{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();
returnarticle;
}
cursor.close();
returnnull;
}
publicList<Article>findByUrl(Stringurl){
List<Article>articles=newArrayList<Article>();
Stringsql="selectid,title,content,url,pagefromtb_articlewhereurl=?";
Cursorcursor=getReadableDatabase().rawQuery(sql,newString[]{url});
while(cursor.moveToNext()){
Articlearticle=newArticle();
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();
returnarticles;
}
publicintgetCountOfPage(intpage){
Stringsql="selectcount(*)fromtb_articlewherepage=?";
Cursorcursor=getReadableDatabase().rawQuery(sql,newString[]{String.valueOf(page)});
cursor.moveToFirst();
intcount=cursor.getInt(0);
cursor.close();
returncount;
}
publicList<Article>getArticlesOfPage(intcurPage){
List<Article>articles=newArrayList<Article>();
Stringsql="selectid,title,content,url,pagefromtb_articlewherepage=?";
Cursorcursor=getReadableDatabase().rawQuery(sql,newString[]{String.valueOf(curPage)});
while(cursor.moveToNext()){
Articlearticle=newArticle();
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();
returnarticles;
}
publicintcountOfSum(){
Stringsql="selectcount(*)fromtb_article";
Cursorcursor=getReadableDatabase().rawQuery(sql,null);
cursor.moveToFirst();
intcount=cursor.getInt(0);
cursor.close();
returncount;
}
publicList<Article>getArticles(intstart,intpageSize){
List<Article>articles=newArrayList<Article>();
Stringsql="selectid,title,content,url,pagefromtb_articlelimit?,?";
Cursorcursor=getReadableDatabase().rawQuery(sql,newString[]{String.valueOf(start),String.valueOf(pageSize)});
while(cursor.moveToNext()){
Articlearticle=newArticle();
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();
returnarticles;
}
publicvoidcloseDB(){
if(readableDatabase!=null&&readableDatabase.isOpen()){
readableDatabase.close();
}
if(writableDatabase!=null&&writableDatabase.isOpen()){
writableDatabase.close();
}
}
publicSQLiteDatabasegetReadableDatabase(){
returndataBaseHelper.getReadableDatabase();
}
publicSQLiteDatabasegetWritableDatabase(){
returndataBaseHelper.getWritableDatabase();
}
}