android資料庫操作，android資料庫

android資料庫操作，android資料庫

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