SQLite数据库

概述1SQLiteDatabase SQLiteDatabase提供了如下静态方法打开数据库 static  SQLiteDatabase openDatabase(String path,SQLiteDatabase.CursorFactory factory,int flags);打开path文件代表的数据库 static  SQLiteDatabase openOrCreateDatabase(

1sqliteDatabase

sqliteDatabase提供了如下静态方法打开数据库

static sqliteDatabase openDatabase(String path,sqliteDatabase.CursorFactory factory,int flags);打开path文件代表的数据库

static sqliteDatabase openorCreateDatabase(file file,sqliteDatabase.CursorFactory factory);打开或创建数据库

static sqliteDatabase openorCreateDatabase(String path,sqliteDatabase.CursorFactory factory);打开或创建数据库

创建数据库对象后就可以使用以下方法对数据库进行 *** 作

execsql(String sql,Object[] bindArgs);执行带占位符的SQL语句

execsql(String sql);执行SQL语句

insert(String table,String nullColumnHack,ContentValues values);像执行表插入数据

update(String table,ContentValues values,String whereClause,String[] whereArgs);更新数据

Cursor query(String table,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy,String limit);对执行数据库进行查询

Cursor query(boolean distinct,String table,String limit);对执行数据库进行查询,，其中第一个参数控制是否去除重复

rawquery(String sql,String[] selectionArgs);执行带占位符的SQL查询

beginTransaction();开始事物

endTrasation();结束事物

2Cursor

Cursor类似于JDBC的ResultSet,Cursor同样提供了如下方法来移动查询指针:

move(int offset);将记录指针向上或者向下移动指定的行数

boolean movetoFirst();移动到第一行，移动成功返回true

boolean movetoLast();移动到最后一行，移动成功返回true

boolean movetoNext();移动到下一行

boolean movetoposition(int postion);移动到指定的位置

boolean movetoPrevIoUs();移动到上一行

3sqliteOpenHelper类

sqliteOpenHelper是一个抽象类，来管理数据库的创建和版本的管理。要使用它必须实现它的onCreate(sqliteDatabase)，onUpgrade(sqliteDatabase,int,int)方法

onCreate：当数据库第一次被建立的时候被执行，例如创建表,初始化数据等。

onUpgrade：当数据库需要被更新的时候执行，例如删除久表，创建新表。

方法名 返回类型 描述 备注
sqliteDatabase synchronized getReadableDatabase() 创建或打开一个数据库 可以通过这两个方法返回的sqliteDatabase对象对数据库进行一系列的 *** 作，如新建一个表，插入一条数据等
sqliteDatabasesynchronizedgetWritableDatabase() 创建或打开一个可以读写的数据库
voIDabstractonCreate(sqliteDatabase db) 第一次创建的时候调用
voIDonopen(sqliteDatabase db) 打开数据库
voIDabstractonUpgrade(sqliteDatabase db,int oldVersion,int newVersion)升级数据库
voIDsynchronizedclose()关闭所有打开的数据库对象

4绑定数据到ListvIEw等组件

在许多时候需要将数据库表中的数据显示在ListVIEw、gallery等组件中。虽然可以直接使用Adapter对象处理，但工作量很大。为此，AndroID SDK提供了一个专用于数据绑定的Adapter类：public SimpleCursorAdapter(Context context,int layout,Cursor c,String[] from,int[] to);
SimpleCursorAdapter与SimpleAdapter用法相近。只是将List对象换成了Cursor对象。而且SimpleCursorAdapter类构造方法的第四个参数from表示Cursor对象中的字段，而SimpleAdapter类构造方法的第四个参数from表示Map对象中的key。除此之外，这两个Adapter类在使用方法完全相同。
例如： SimpleCursorAdapter simpleCursorAdapter = new SimpleCursorAdapter (this,androID.R.layout.simple_expandable_List_item_1,cursor,new String[]{"name"},new int[]{androID.R.ID.text1});

5实例

RSSDB类

public class RSSDB{
private final static String DB_name = "RSSDB5.db3";
// private final static int DATABASE_VERSION = 1;
private sqliteDatabase db=null;
//数据库文件目标存放路径为系统默认位置
public static final String DB_SYstemPATH= "/data/data/com.zte.RSS/databases/";
//如果你想把数据库文件存放在SD卡的位置
public static final String DB_Sdpath = androID.os.Environment.getExternalStorageDirectory().getabsolutePath()
+ "/xinyue/drivertest/packfiles/";
private String DB_PATH="";
public RSSDB(Context context) {
// Todo auto-generated constructor stub

checkDataBase();
Log.v("DB_PATH",DB_PATH);


}

/*
* 返回数据库文件的路径
*/
public String getDbPath(){
return DB_PATH;
}


/*
* 获取数据库文件的大小
*/
public long getDbSize(){
return new file(DB_PATH+DB_name).length();
}


/*
* 返回一个数据库
*/
public sqliteDatabase getDatabase() {

return db;

}

/*
* 创建数据库
*/
public synchronized voID createDataBase() throws Exception{
try {
file dir = new file(DB_PATH);
if(!dir.exists()){
dir.mkdirs();
}
file dbf = new file(DB_PATH + DB_name);
if(dbf.exists()){
dbf.delete();
}
db=sqliteDatabase.openorCreateDatabase(dbf,null);
}
catch (sqliteException e) {
Log.v("create",e.getMessage());
throw new Exception("");


}
catch (Exception e) {
Log.v("create",e.getMessage());
throw new Exception("");

}

}


/*
* 动态管理数据库文件，根据sd卡是否有用来设置数据库文件的路径。
*
* 假如sd能用，则把数据文件保存到sd卡，
* 此时在sd卡未找到此数据库文件下，如果在系统默认保存数据库文件的地址找到了数据库文件，则把此文件复制到sd卡，否则创建数据文件，并初始化；
*
* 如果sd不能用，则数据文应保存在系统默认位置，如果此位置无数据库文件，则创建并初始化
*/
private synchronized boolean checkDataBase(){
//得到 sdk状态
String sdState = androID.os.Environment.getExternalStorageState();
//表示sd卡已经挂载,并且拥有读写权限
if(sdState.equals(androID.os.Environment.MEDIA_MOUNTED))
{
DB_PATH=DB_Sdpath;
}else
{
DB_PATH=DB_SYstemPATH;
}
String myPath = DB_PATH + DB_name;
Log.v("checkDataBase-DB_PATH",DB_PATH);
//sqliteDatabase checkDB=null;
try{
db = sqliteDatabase.openDatabase(myPath,null,sqliteDatabase.OPEN_READWRITE);
}catch(sqliteException e){
//database does't exist yet.
}
if(db != null){
return true;
}else{

//假如数据库文件应该保持到sd卡，则检查系统默认位置是否有数据库文件，有则把数据库文件移动到sd卡
if(DB_PATH.equals(DB_Sdpath)){
myPath = DB_SYstemPATH + DB_name;
try{
db = sqliteDatabase.openDatabase(myPath,sqliteDatabase.OPEN_READWRITE);
}catch(sqliteException e){
//database does't exist yet.
}
if(db!=null){
db.close();
db=null;
try
{
Log.v("移动-DB_PATH",DB_PATH);
//将数据库文件移动到sd卡
copyDataBase();
}
catch(IOException e)
{
//移动失败，则设置数据库文件在系统默认的数据库文件位置
DB_PATH=DB_SYstemPATH;

return true;
}
myPath = DB_PATH + DB_name;
try{
db = sqliteDatabase.openDatabase(myPath,sqliteDatabase.OPEN_READWRITE);
}catch(sqliteException e){
return false;
}
//删除系统默认位置的数据库文件
}
else
{
try
{
//假如系统的默认位置也没有数据库文件，则创建数据库
createDataBase();

}
catch(Exception e){
return false;
}
//数据库的初始化
onCreate();

}
}else{
try
{
createDataBase();

}
catch(Exception e){
return false;
}
onCreate();
}
}

return true;
}

/**
* 把系统默认位置的数据库文件拷贝到sd卡
*
*/
private synchronized voID copyDataBase() throws IOException{
String intfilename = DB_SYstemPATH + DB_name;
inputStream myinput = new fileinputStream(intfilename);

String outfilename = DB_Sdpath + DB_name;


OutputStream myOutput = new fileOutputStream(outfilename);

byte[] buffer = new byte[1024];
int length;
while ((length = myinput.read(buffer))>0){
myOutput.write(buffer,length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myinput.close();
}



/*
* 数据库文件的初始化，创建表，视图，并插入基本数据
*/
public voID onCreate() {
db.beginTransaction();
try
{
//创建RSS源的类型表
String sql = "create table FeedsType(Title TEXT PRIMARY KEY, Image TEXT)";
db.execsql(sql);

//RSS源
sql="create table Feeds(" +
"Address TEXT PRIMARY KEY," + //xml的地址
"FeedsTypeRowID Interger," + //对应FeedsType的rowID
"Title TEXT NOT NulL," + //频道名称
"Generator TEXT," + //生成该频道的程序名
"Description TEXT," + //频道的描述
"copyright TEXT," + //频道内容的版权说明
"link TEXT," + //频道的URL
"Image TEXT,"+ //频道的图标
"PubDate Interger," + //最近发布的时间
"IsSubscribe Interger)";
db.execsql(sql);

//具体的每条信息
sql="create table RSSItem(" +
"FeedsRowID Interger," + //相应的Feeds rowID
"Title TEXT," + //文章名
"link TEXT," + //文章的地址
"Author TEXT," + //文章的作者
"Description TEXT," + //文章的内容
"PubDate Interger," + //发布时间
"Isstar Interger," + //是否是星标项目
"IsRead Interger" + //是否已读
")";
db.execsql(sql);

//为RSSitem表创建索引
sql="CREATE INDEX time_index ON RSSItem(PubDate desc)";
db.execsql(sql);

//创建视图
sql="create vIEw FeedsVIEw " +
" as" +
" select " +
"Feeds.ROWID," +
"Feeds.FeedsTypeRowID," +
"Feeds.Address," +
"Feeds.Title," +
"Feeds.Generator," +
"Feeds.PubDate," +
"Feeds.Image," +
"Feeds.IsSubscribe," +
"Feeds.copyright," +
"Feeds.link," +
"FeedsType.Title as FeedsTypeTitle " +
" from Feeds,FeedsType" +
" where Feeds.FeedsTypeRowID=FeedsType.ROWID";
db.execsql(sql);
sql="create vIEw RSSItemVIEw " +
" as" +
" select " +
"RSSItem.ROWID," +
"RSSItem.Title," +
"RSSItem.link," +
"RSSItem.Author," +
"RSSItem.Description," +
"RSSItem.PubDate," +
"RSSItem.Isstar," +
"RSSItem.IsRead," +
"FeedsVIEw.Title as FeedsTitle," +
"RSSItem.FeedsRowID," +
"FeedsVIEw.Image as FeedsImage," +
"FeedsVIEw.FeedsTypeTitle," +
"FeedsVIEw.FeedsTypeRowID " +
" from RSSItem,FeedsVIEw" +
" where RSSItem.FeedsRowID=FeedsVIEw.ROWID";
db.execsql(sql);

//插入初始化的数据
sql="insert into Feedstype(Title,ROWID) select '默认',0" +
" UNION ALL SELECT '实事新闻',2" +
" UNION ALL SELECT '财经资讯',3" +
" UNION ALL SELECT '娱乐明星',4" +
" UNION ALL SELECT '星座时尚',5 "+
" UNION ALL SELECT '体育新闻',6 "+
" UNION ALL SELECT 'IT数码',1 ";
db.execsql(sql);

//插入初始化的数据
sql="insert into Feeds" +
"(address,Image,FeedsTypeRowID,Title,Generator,Description,copyright,link,IsSubscribe)" +

" SELECT " +
"'http://RSS.sina.com.cn/news/marquee/ddt.xml'," +
"'img/logo/9.png'," +
"2," +
"'新闻要闻-新浪新闻'," +
"'WWW.SINA.COM.CN'," +
"'新闻中心-新闻要闻'," +
"'copyright 1996 -2012 SINA Inc. All Rights Reserved'," +
"'http://news.sina.com.cn'," +
"0 "+

" UNION ALL SELECT" +
" 'http://cn.engadget.com/RSS.xml'," +
"'img/logo/6.png'," +
"1," +
"'Engadget 中国版'," +
"'CN.ENGADGET.COM'," +
"'© 2012 Aol Inc. All rights Reserved. Privacy Policy '," +
"'http://cn.engadget.com',"+
"1 "+

" UNION ALL SELECT" +
" 'http://RSS.news.sohu.com/RSS/pfocus.xml'," +
"'img/logo/8.png'," +
"2," +
"'搜狐焦点图新闻'," +
"'WWW.soHU.COM'," +
"'即时报道国内外时政大事，解读环球焦点事件'," +
"'copyright © 2012 Sohu.com Inc. All Rights Reserved'," +
"'http://news.sohu.com/s2005/jiaodiantu.sHTML',"+
"1 "+

" UNION ALL SELECT" +
" 'http://astro.women.sohu.com/RSS/xingzuoxinwen.xml'," +
"5," +
"'星座频道'," +
"'最IN的星闻，融汇星座、测试、血型、生肖、命理、塔罗、解梦、紫微等东西方星相占卜资讯，最专业、互动、时尚的星迷乐园'," +
"'http://astro.women.sohu.com/',"+
"1 "+

" UNION ALL SELECT" +
" 'http://RSS.sina.com.cn/tech/rollnews.xml'," +
"1," +
"'科技要闻-新浪科技'," +
"'copyright 1996 - 2012SINA Inc. All Rights Reserved'," +
"'http://tech.sina.com.cn/roll.sHTML',"+
"0 "+

" UNION ALL SELECT" +
" 'http://RSS.sina.com.cn/roll/finance/hot_roll.xml'," +
"'img/logo/9.png'," +
"3," +
"'财经要闻汇总-新浪财经'," +
"'WWW.SINA.COM.CN'," +
"'财经要闻汇总'," +
"'copyright 1996 - 2012SINA Inc. All Rights Reserved'," +
"'http://finance.sina.com.cn/',"+
"0 "+

" UNION ALL SELECT" +
" 'http://RSS.sina.com.cn/ent/hot_roll.xml'," +
"4," +
"'娱乐要闻汇总-新浪娱乐'," +
"'娱乐要闻汇总'," +
"'http://ent.sina.com.cn/',"+
"1 "+

" UNION ALL SELECT" +
" 'http://RSS.sina.com.cn/news/allnews/astro.xml'," +
"'焦点新闻-新浪星座'," +
"'星座焦点新闻'," +
"'http://astro.sina.com.cn',"+
"0 "+



/* " UNION ALL SELECT" +
" 'http://cng.dili360.com/RSS/RSS.xml'," +
"'img/logo/3.png'," +
"0," +
"'中国国家地理'," +
"'WWW.DIli360.COM'," +
"'《中国国家地理》杂志频道是中国国家地理网最重要的频道之一，包括以下栏目：大讲堂、俱乐部出行、绿色出行、摄影大赛、徒步队等栏目'," +
"'copyright © 2001-2012 Chinese National Geography Press. All rights reserved. '," +
"'http://cng.dili360.com',"+
"1 "+*/

" UNION ALL SELECT" +
" 'http://news.ifeng.com/RSS/index.xml'," +
"'img/logo/4.png'," +
"'资讯频道_凤凰网'," +
"'WWW.IFENG.COM'," +
"'copyright © 2011 Phoenix New Media limited All Rights Reserved. '," +
"'http://news.ifeng.com/',"+
"1 "+

" UNION ALL SELECT" +
" 'http://RSS.news.sohu.com/RSS/it.xml'," +
"'IT频道'," +
"'搜狐网IT新闻'," +
"'http://it.sohu.com/',"+
"0 "+

" UNION ALL SELECT" +
" 'http://RSS.news.sohu.com/RSS/business.xml'," +
"'财经热点新闻'," +
"'搜狐网财经新闻'," +
"'http://business.sohu.com/caijingredian/index.sHTML',"+
"0 "+

" UNION ALL SELECT" +
" 'http://RSS.news.sohu.com/RSS/sports.xml'," +
"6," +
"'体育频道热点新闻'," +
"'搜狐网新闻中心体育新闻'," +
"'http://sports.sohu.com/hotnews/index.sHTML',"+
"1 "+

" UNION ALL SELECT" +
" 'http://RSS.news.sohu.com/RSS/yule.xml'," +
"'八卦新闻'," +
"'娱乐新闻'," +
"'http://yule.sohu.com/s2006/3040/s246373576/index.sHTML',"+
"1 ";



/* " UNION ALL SELECT" +
" 'http://cnbeta.Feedsportal.com/c/34306/f/624776/index.RSS'," +
"'img/logo/2.png'," +
"'cnBeta.COM_中文业界资讯站'," +
"'WWW.CNBETA.COM'," +
"'cnBeta.COM - 简明IT新闻,网友媒体与言论平台'," +
"'©2003-2011 cnBeta 传媒组织 '," +
"'http://www.cnbeta.com/',"+
"1 "*/

db.execsql(sql);


db.setTransactionSuccessful();
}
finally
{
db.endTransaction();
}
Log.v("初始化数据库成功","初始化数据库成功");
}


/*
* 关闭数据库文件
*/
public voID close(){
if(db!=null){
db.close();
//db=null;
}
}

}

RSSDataControl 类

public class RSSDataControl { //每次最多获取的数据的数目 private static final int limit=100; //数据库文件的最大容量，以bytes为单位 private static final long maxRSSDBSize=5*1024*1024; private RSSDB RSSdb; SharedPreferences preferences; SharedPreferences.Editor editor; public RSSDataControl(RSSDB RSSdb) { this.RSSdb=RSSdb; } /* * 根据查询条件获取数目 */ public int GetCount(String tablename,String wheresql) { int count=0; String sql="select count(ROWID) from "+tablename; if(!wheresql.equals("")) { sql=sql+" where "+wheresql; } //Log.v("sql",sql); Cursor cursor=RSSdb.getDatabase().rawquery(sql,null); if(cursor.movetoNext()) { count=cursor.getInt(0); } cursor.close(); return count; } /* * 添加一个item * */ public boolean AddRSSItem(ModelRSSItem model) { ContentValues values=new ContentValues(); values.put("FeedsRowID",model.FeedsRowID); values.put("Title",model.Title); values.put("link",model.link); values.put("Author",model.Author); values.put("Description",model.Description); values.put("PubDate",model.PubDate.getTime()); values.put("Isstar",model.Isstar); values.put("IsRead",model.IsRead); //添加一条数据 return RSSdb.getDatabase().insert("RSSItem",values)>0; } /* * 添加一个item * */ public int AddFeeds(String Title,String address,int FeedstyperowID) { if(GetCount("Feeds","address='"+address+"'")>0){ return -1; } ContentValues values=new ContentValues(); values.put("Title",Title); values.put("Address",address); values.put("FeedsTypeRowID",FeedstyperowID); values.put("Image","img/logo/12.png"); values.put("IsSubscribe",0); String sql="insert into Feeds (address,IsSubscribe) values ('"+address+"','img/logo/12.png',"+FeedstyperowID+",'"+Title+"',0)"; //添加一条数据 RSSdb.getDatabase().execsql(sql); return 1; } /* * 获取一个订阅源的信息 */ public JsONObject GetFeeds(int rowID) { String sql="select ROWID,Address,PubDate,IsSubscribe,FeedsTypeTitle from FeedsvIEw where ROWID="+rowID; Cursor cursor=RSSdb.getDatabase().rawquery(sql,null); if(cursor.movetoNext()) { JsONObject Feed=new JsONObject(); try { Feed.put("ROWID",rowID); Feed.put("Address",cursor.getString(2)); Feed.put("Title",cursor.getString(3)); Feed.put("FeedsTypeRowID",cursor.getInt(1)); Feed.put("IsSubscribe",cursor.getInt(7)); Feed.put("Image",cursor.getString(6)); Feed.put("FeedsTypeTitle",cursor.getString(10)); Feed.put("PubDate",cursor.getString(5)); Feed.put("Generator",cursor.getString(4)); Feed.put("copyright",cursor.getString(8)); Feed.put("link",cursor.getString(9)); } catch (JsONException e) { // Todo auto-generated catch block e.printstacktrace(); } return Feed; } return null; } /* * 更新订阅源的信息 */ public boolean Updatafeeds(JsONObject Feed) { ContentValues values=new ContentValues(); int resultNum=0; try { values.put("FeedsTypeRowID",Feed.getInt("FeedsTypeRowID")); //values.put("Address",Feed.getString("Address")); values.put("Title",Feed.getString("Title")); values.put("Generator",Feed.getString("Generator")); //values.put("PubDate",Feed.getString("PubDate")); //values.put("Image",Feed.getString("Image")); values.put("IsSubscribe",Feed.getInt("IsSubscribe")); values.put("copyright",Feed.getString("copyright")); values.put("link",Feed.getString("link")); resultNum = RSSdb.getDatabase().update("Feeds",values,"ROWID=?",new String[]{Feed.getInt("ROWID")+""}); } catch (JsONException e) { // Todo auto-generated catch block Log.v("更新失败",e.getMessage()); } return resultNum>0; } /* * 更新订阅源的信息 */ public boolean Updatafeeds(int rowID,Date pubDate) { ContentValues values=new ContentValues(); values.put("pubDate",pubDate.getTime()); int resultNum=RSSdb.getDatabase().update("Feeds",new String[]{rowID+""}); return resultNum>0; } /* * 设置RSSitem已读 */ public boolean SetSomeRSSItemRead(String wheresql,int isRead){ ContentValues values=new ContentValues(); values.put("IsRead",isRead); int resultNum=RSSdb.getDatabase().update("RSSItem",wheresql,null); return resultNum>0; } /* * 设置RSSitem是否已读 */ public boolean SetRSSItemRead(int rowID,new String[]{rowID+""}); RSSdb.close(); return resultNum>0; } /* * 设置RSSitem是否为星标项目 */ public boolean SetRSSItemStar(int rowID,int isstar){ ContentValues values=new ContentValues(); values.put("Isstar",isstar); int resultNum=RSSdb.getDatabase().update("RSSItem",new String[]{rowID+""}); RSSdb.close(); return resultNum>0; } /* * 获取所有的订阅源类型名称跟此类型未读项目的数目 */ public JsONArray GetRSSItemList(String wheresql,String orderby) { JsONArray RSSItemList=new JsONArray(); try { String sql="SELECT "+ "Title," + "link," + "Author," + "Description," + "PubDate," + "Isstar," + "IsRead," + "FeedsTitle," + "FeedsRowID," + "FeedsImage," + "FeedsTypeTitle," + "FeedsTypeRowID," + "ROWID " + " from RSSItemVIEw "; if(!wheresql.trim().equals(null)&&!wheresql.trim().equals("")) { sql=sql+" where "+wheresql; } if(!orderby.trim().equals(null)&&!orderby.trim().equals("")) { sql+=" ORDER BY "+orderby; } sql+=" limit 0,"+limit; Log.v("sql",sql); //item的索引，表示是第几个item int index=0; //所有项目的数目 Cursor cursor=RSSdb.getDatabase().rawquery(sql,null); while(cursor.movetoNext()) { JsONObject RSSItem=new JsONObject(); try { Log.v("Title",cursor.getString(0)); RSSItem.put("Index",index); RSSItem.put("ROWID",cursor.getInt(12)); index++; RSSItem.put("Title",cursor.getString(0)); RSSItem.put("Description",cursor.getString(3)); RSSItem.put("Isstar",cursor.getInt(5)); RSSItem.put("IsRead",cursor.getInt(6)); RSSItem.put("PubDate",dateFormat(cursor.getLong(4))); RSSItem.put("link",cursor.getString(1)); RSSItem.put("FeedsTitle",cursor.getString(7)); RSSItem.put("FeedsRowID",cursor.getInt(8)); RSSItem.put("FeedsImage",cursor.getString(9)); RSSItem.put("FeedsTypeTitle",cursor.getString(10)); RSSItem.put("FeedsTypeRowID",cursor.getInt(11)); RSSItem.put("Author",cursor.getString(2)); } catch(Exception ex) { Log.d("DirectoryListPlugin","Got JsON Exception " + ex.getMessage()); } RSSItemList.put(RSSItem); } cursor.close(); } catch(Exception ex) { Log.v("错误",ex.getMessage()); } return RSSItemList; } /* * 获取所有的订阅源类型名称跟此类型未读项目的数目 */ public JsONArray GetFeedsTypetoNumList() { JsONArray FeedsTypeList=new JsONArray(); try { //所有项目的数目 Cursor cursor1=RSSdb.getDatabase().rawquery("SELECT count(*) as count FROM RSSItem where isread=0",null); if(cursor1.movetoNext()) { JsONObject Feeds=new JsONObject(); try { Feeds.put("Count",cursor1.getInt(0)); //数据库所有RSSitem项目的数目 Feeds.put("RSSItemCount",GetCount("RSSitem","")); Feeds.put("Title","所有项目"); FeedsTypeList.put(Feeds); } catch(Exception ex) { Log.d("DirectoryListPlugin","Got JsON Exception " + ex.getMessage()); } } else { JsONObject Feeds=new JsONObject(); try { Feeds.put("Count",0); //数据库所有RSSitem项目的数目 Feeds.put("RSSItemCount","Got JsON Exception " + ex.getMessage()); } } cursor1.close(); //星标项目的数目 Cursor cursor2=RSSdb.getDatabase().rawquery(" SELECT count(*) as Count FROM RSSItem where isstar=1",null); if(cursor2.movetoNext()) { JsONObject Feeds=new JsONObject(); try { Feeds.put("Count",cursor2.getInt(0)); Feeds.put("Title","星标项目"); FeedsTypeList.put(Feeds); } catch(Exception ex) { Log.d("DirectoryListPlugin",0); Feeds.put("Title","Got JsON Exception " + ex.getMessage()); } } cursor2.close(); //获取所有的订阅源类型名称跟此类型未读项目的数目 Cursor cursor3=RSSdb.getDatabase().rawquery("select ROWID,Title from FeedsType ",null); while(cursor3.movetoNext()) { JsONObject Feeds=new JsONObject(); try { Feeds.put("ROWID",cursor3.getInt(0)); Feeds.put("Title",cursor3.getString(1)); Cursor cursor4=RSSdb.getDatabase().rawquery("SELECT count(*) as Count FROM RSSItemVIEw" + " where isread=0 and FeedsTypeRowID="+cursor3.getInt(0),null); if(cursor4.movetoNext()) { Feeds.put("Count",cursor4.getInt(0)); } else { Log.v("获取NUM失败","aaaaa"); continue; } cursor4.close(); FeedsTypeList.put(Feeds); } catch(Exception ex) { Log.d("DirectoryListPlugin","Got JsON Exception " + ex.getMessage()); } } cursor3.close(); RSSdb.close(); } catch(Exception ex) { Log.v("错误",ex.getMessage()); } return FeedsTypeList; } /* * 获取所有的订阅源名称跟未读未读的项目的数目 */ public JsONArray GetFeedsList(String wheresql) { JsONArray FeedsList=new JsONArray(); /**********************所有项目***************************/ JsONObject Feeds1=new JsONObject(); try { //count为未读项目的数目 Feeds1.put("Count",GetCount("RSSItem","IsRead=0")); //被订阅源的数目 Feeds1.put("FeedsCount",GetCount("Feeds","IsSubscribe=1")); //数据库所有RSSitem项目的数目 Feeds1.put("RSSItemCount","")); Feeds1.put("Title","所有项目"); FeedsList.put(Feeds1); } catch(Exception ex) { Log.d("DirectoryListPlugin","Got JsON Exception " + ex.getMessage()); return null; } String sql="select ROWID,pubDate,FeedsTypeRowID from Feeds "; if(!wheresql.equals("")) { sql=sql+" where "+wheresql; } Cursor cursor2=RSSdb.getDatabase().rawquery(sql,null); while(cursor2.movetoNext()) { JsONObject Feeds=new JsONObject(); try { Feeds.put("ROWID",cursor2.getString(1)); Feeds.put("IsSubscribe",cursor2.getString(4)); Feeds.put("FeedsTypeRowID",cursor2.getInt(5)); Feeds.put("Image",cursor2.getString(3)); //count为未读项目的数目 Feeds.put("Count"," (FeedsRowID="+cursor2.getInt(0)+" and isread=0) ")); Feeds.put("PubDate",dateFormat(cursor2.getLong(2))); } catch(Exception ex) { try { Feeds.put("PubDate",""); Feeds.put("Count",0); } catch (JsONException e) { Log.v("DirectoryListPluginPubDate","Got JsON Exception " + ex.getMessage()); } Log.v("DirectoryListPlugin","Got JsON Exception " + ex.getMessage()); } FeedsList.put(Feeds); } cursor2.close(); RSSdb.close(); return FeedsList; } /* * 更新某个订阅源 * maxCount:每个订阅源最多更新的项目数 */ public boolean UpdateRSS(ModelFeeds model,int maxCount) { Log.v("Address",model.Address); //设置此项目的最近更新时间 model.PubDate=new Date(); Cursor cursor1=null; try { cursor1=RSSdb.getDatabase().rawquery("SELECT PubDate,Title FROM RSSItem where FeedsRowID="+model.RowID+" order by pubdate desc limit 0,1",null); } catch(Exception e) { } Date lastBuildDate=new Date(0); String lasttitle=""; if(cursor1!=null&&cursor1.movetoNext()){ lastBuildDate=new Date(cursor1.getLong(0)); lastTitle=cursor1.getString(1); } if(cursor1!=null){ cursor1.close(); } Log.v("lastBuildDate",lastBuildDate.toGMTString()); List<ModelRSSItem> ListModelRSSItem=new ArrayList<ModelRSSItem>(); int FeedsRowID=model.RowID; URL url=null; try { url = new URL(model.Address); } catch (MalformedURLException ex) { Log.v("构建Url失败：",ex.getMessage()); return false; } //构建XmlPullParserFactory XmlPullParserFactory pullParserFactory=null; try { pullParserFactory = XmlPullParserFactory.newInstance(); } catch (XmlPullParserException ex) { Log.v("构建XmlPullParserFactory失败：",ex.getMessage()); return false; } //获取XmlPullParser的实例 XmlPullParser xmlPullParser=null; try { xmlPullParser = pullParserFactory.newPullParser(); } catch (XmlPullParserException ex) { Log.v("获取XmlPullParser的实例失败 ：",ex.getMessage()); return false; } //设置输入流 xml文件装载器 try { xmlPullParser.setinput(url.openConnection().getinputStream(),"UTF-8"); } catch (XmlPullParserException ex) { Log.v("设置输入流 xml文件装载器 失败：",ex.getMessage()); return false; } catch (IOException ex) { Log.v("设置输入流 xml文件装载器 失败：",ex.getMessage()); return false; } /** * pull读到xml后 返回数字 * 读取到xml的声明返回数字0 START_document; 读取到xml的结束返回数字1 END_document ; 读取到xml的开始标签返回数字2 START_TAG 读取到xml的结束标签返回数字3 END_TAG 读取到xml的文本返回数字4 TEXT */ int eventType=0; try { eventType=xmlPullParser.getEventType(); } catch (XmlPullParserException ex) { Log.d("获取失败xmlPullParserEventType：",ex.getMessage()); return false; } /* * 获取订阅源的所有项目item */ //标志已经添加了count个项目 int count=1; //是否已经获取获取订阅源的最新发布时间 boolean flags=false; ModelRSSItem modelRSSItem=null; while(eventType != XmlPullParser.END_document&&count<=maxCount){ try { String nodename=xmlPullParser.getname(); switch (eventType) { case XmlPullParser.START_document: break; case XmlPullParser.START_TAG: if("item".equals(nodename)){ modelRSSItem=new ModelRSSItem(); modelRSSItem.IsRead=0; modelRSSItem.Isstar=0; modelRSSItem.FeedsRowID=FeedsRowID; } if("Title".equals(nodename)&& modelRSSItem!=null){ modelRSSItem.Title=xmlPullParser.nextText(); //Log.v("标题",modelRSSItem.Title); if(lastTitle.equals(modelRSSItem.Title)) { Log.v("标题相同",modelRSSItem.Title); count=maxCount+1; } // Log.v("Title2",modelRSSItem.Title); } if("link".equals(nodename)&& modelRSSItem!=null){ modelRSSItem.link=xmlPullParser.nextText(); } if("author".equals(nodename)&& modelRSSItem!=null){ modelRSSItem.Author=xmlPullParser.nextText(); } if("pubDate".equals(nodename)){ String pubDateStr=xmlPullParser.nextText(); //Log.v("pubDateStr",pubDateStr); Date date=getDate(pubDateStr); //Log.v("pubDateStr2",date.getTime()+""); /** * 假如是订阅源的发布时间 */ /* if(!flags&&modelRSSItem==null) { flags=true; model.PubDate=date; //获取订阅源的最新发布时间 }*/ if(modelRSSItem!=null) { //假如此项目的时间跟上次更新保存的时间相同，则代表此项目上次已经被更新了 if(lastBuildDate.compareto(date)>=0){ // Log.v("时间相同",pubDateStr); //设定count结束循环 count=maxCount+1; }else { modelRSSItem.PubDate=date; // Log.v("pubDateStr",pubDateStr); } } } if("description".equals(nodename)&& modelRSSItem!=null){ modelRSSItem.Description=xmlPullParser.nextText(); //Log.v("Description",modelRSSItem.Description); } break; case XmlPullParser.END_TAG: if("item".equals(nodename)&& modelRSSItem!=null){ if(count<=maxCount) { ListModelRSSItem.add(modelRSSItem); count++; } } break; default: break; } }catch (XmlPullParserException ex) { Log.d("获取订阅源的信息失败：",ex.getMessage()); return false; } catch (IOException ex) { Log.d("获取订阅源的信息失败2：",ex.getMessage()); return false; } try { eventType = xmlPullParser.next(); } catch (XmlPullParserException e) { // Todo auto-generated catch block e.printstacktrace(); } catch (IOException e) { // Todo auto-generated catch block e.printstacktrace(); } } RSSdb.getDatabase().beginTransaction(); Updatafeeds(model.RowID,model.PubDate); for(ModelRSSItem modelRSSItem2:ListModelRSSItem) { AddRSSItem(modelRSSItem2); } RSSdb.getDatabase().setTransactionSuccessful(); RSSdb.getDatabase().endTransaction(); return true; } /* * 更新所有订阅源 */ public synchronized boolean Upda@R_404_6614@lRSS(int FeedsMaxItemCount) { //更新前先管理数据库文件的大小 autoRSSDBSize(); List<ModelFeeds> ListFeeds = new ArrayList<ModelFeeds>(); //获取所有的订阅源 Cursor cursor=RSSdb.getDatabase().rawquery("select ROWID,Address from Feeds where IsSubscribe=1",null); while(cursor.movetoNext()) { ModelFeeds modelFeeds=new ModelFeeds(); modelFeeds.Address=cursor.getString(cursor.getColumnIndex("Address")); modelFeeds.RowID=cursor.getInt(0); ListFeeds.add(modelFeeds); } cursor.close(); //更新所有订阅源 for(ModelFeeds model:ListFeeds) { UpdateRSS(model,FeedsMaxItemCount); } return true; } /* * 自动设置数据文件大小 * 如果数据库文件大小接近5m时，删除500条非星标项目数据 * 如图手机容量不够时也删除500条非星标项目数据 * 这样保证数据文不超过5m，或者在手机容量不足的情况下，能正常更新数据，并保证数据文件不会变大 */ private voID autoRSSDBSize(){ //获取目前数据库文件的大小 long dbSize=RSSdb.getDbSize(); Log.v("dbSize",dbSize+""); //假如数据库文件大小接近maxRSSDBSize if(dbSize>=maxRSSDBSize-1024*100) { //从RSSItem表中删除500条非星标数据 Log.v("delete","删除500条数据"); deleteRSSItem(500); return; } //获取数据库文件的保存路径 String dbpath=RSSdb.getDbPath(); //数据库保存存储器的可用容量 long availableCapacity=0; //假如数据库文件保存到sd卡 if(dbpath.equals(RSSDB.DB_Sdpath)) { availableCapacity=getSDCardAvailableCapacity(); } else { availableCapacity=getSystemAvailableCapacity(); } //假如剩余容量少与2m if(availableCapacity<2*1024*1024) { //从RSSItem表中删除500条非星标数据 deleteRSSItem(500); } } /* *根据文章的发布时间 删除数据库非星标项目的数据 *删除的是那些最早更新的非星标数据 * */ private voID deleteRSSItem(int count){ String sql="select PubDate FROM RSSItem where isstar=0 order by PubDate ASC limit 1 offset "+count; long pubDate=0; Cursor cursor=RSSdb.getDatabase().rawquery(sql,null); while(cursor.movetoNext()) { pubDate=cursor.getLong(0); } cursor.close(); Log.v("deleteRSSItem",pubDate+"_PubDate"); if(pubDate>0) { sql="delete FROM RSSItem where (isstar=0) and (pubDate<="+pubDate+")"; RSSdb.getDatabase().execsql(sql); } } /* * 获取sd卡的可用容量 */ private long getSDCardAvailableCapacity() { String state = Environment.getExternalStorageState(); if(Environment.MEDIA_MOUNTED.equals(state)) { file sdcardDir = Environment.getExternalStorageDirectory(); StatFs sf = new StatFs(sdcardDir.getPath()); long blockSize = sf.getBlockSize(); long availCount = sf.getAvailableBlocks(); return availCount*blockSize; } //当无法获取sd的可用容量时，返回一个足够大的数字 return 5*1024*1024; } /* * 获取系统内存可用容量 */ private long getSystemAvailableCapacity() { file root = Environment.getRootDirectory(); StatFs sf = new StatFs(root.getPath()); long blockSize = sf.getBlockSize(); long availCount = sf.getAvailableBlocks(); return availCount*blockSize; } /* * 根据long型数据获取时间描述 */ private String dateFormat(long time) { try { Date date=new Date(time); Calendar calendar = Calendar.getInstance(); //将date转换成日历 calendar.setTime(date); //获取现在的时间 Calendar calendarNow = Calendar.getInstance(); int NowDay=calendarNow.get(Calendar.DAY_OF_YEAR); int day=calendar.get(Calendar.DAY_OF_YEAR); if(NowDay==day) { return "今天 "+calendar.get(Calendar.HOUR_OF_DAY)+":"+calendar.get(Calendar.MINUTE); } if((NowDay-day)==1) { return "昨天 "+calendar.get(Calendar.HOUR_OF_DAY)+":"+calendar.get(Calendar.MINUTE); } if((NowDay-day)==2) { return "前天 "+calendar.get(Calendar.HOUR_OF_DAY)+":"+calendar.get(Calendar.MINUTE); } else { return calendar.get(Calendar.YEAR)+"-"+calendar.get(Calendar.MONTH)+"-"+calendar.get(Calendar.DAY_OF_YEAR); } } catch(Exception ex){ return ""; } } /* * 根据字符串获取时间,如果获取失败则返回现在的时间 * */ private Date getDate(String datestr) { datestr=datestr.trim(); Date date=new Date(); try { date=new Date(datestr); } catch(Exception ex){ SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); try { date=sdf.parse(datestr); } catch (ParseException e) { sdf= new SimpleDateFormat("yyyy.MM.dd G 'at' hh:mm:ss z"); try { date=sdf.parse(datestr); } catch (ParseException e1) { // Todo auto-generated catch block sdf= new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss.SSSSSSz"); try { date=sdf.parse(datestr); } catch (ParseException e2) { // Todo auto-generated catch block date=new Date(); } } } } return date; } }

总结

以上是内存溢出为你收集整理的SQLite数据库全部内容，希望文章能够帮你解决SQLite数据库所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。