发布于 2016-03-01 21:53:34 | 164 次阅读 | 评论: 0 | 来源: 网友投递
SQLite轻量级关系型数据库
SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度比他们都快。
使用示例：
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();
}
}