SQLite数据库
|
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：当数据库需要被更新的时候执行，例如删除久表，创建新表。 方法名 返回类型 描述 备注 4绑定数据到listview等组件 在许多时候需要将数据库表中的数据显示在ListView、Gallery等组件中。虽然可以直接使用Adapter对象处理，但工作量很大。为此，Android SDK提供了一个专用于数据绑定的Adapter类：public SimpleCursorAdapter(Context context,int layout,Cursor c,String[] from,int[] to);
5实例 RssDB类 public class RssDB{ 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 UpdateAllRss(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; } } （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |