【Android 应用】数据库实例---包含String转时间戳、查询升序

本文主要是介绍【Android 应用】数据库实例---包含String转时间戳、查询升序，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

写一个数据库实例，方便以后参考。

推荐一篇sql文章：https://cloud.tencent.com/developer/article/1394216

1、数据库创建类

AdCacheFileDB .java

package com.seecool.apmservice.ad;import android.content.Context;import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;public class AdCacheFileDB extends SQLiteOpenHelper {private static final String DBFilename = "AdCacheRecord.db";public static final String CREATE_FILE_PAGE = "create table AdCacheRecord("+ "id integer primary key autoincrement,"+ "endtime integer,"+ "url varchar(64))";public AdCacheFileDB(Context context) {super(context, DBFilename, null, 1);}@Overridepublic void onCreate(SQLiteDatabase db) {//初始化数据库的表结构 d integer primary key autoincrement,db.execSQL(CREATE_FILE_PAGE);}@Overridepublic void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {}
}

2、数据库封装类

AdCacheFileDao.java

package com.seecool.apmservice.ad;import android.util.Log;import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;import java.util.ArrayList;
import java.util.List;public class AdCacheFileDao{private static final String TAG = "AdCacheFileDao";private AdCacheFileDB helper;public AdCacheFileDao(Context context){helper = new AdCacheFileDB(context);}public long add(long endtime,String url){SQLiteDatabase db = helper.getWritableDatabase();ContentValues values = new ContentValues();values.put("endtime",endtime);values.put("url",url);long num = db.insert("AdCacheRecord",null,values);db.close();return num;}public boolean findByUrl(String url){SQLiteDatabase db = helper.getWritableDatabase();Cursor cursor = db.rawQuery("select * from AdCacheRecord where url=?",new String[]{url});boolean result = cursor.moveToNext();cursor.close();db.close();return result;}public int update(String url,long endtime){SQLiteDatabase db = helper.getWritableDatabase();ContentValues values = new ContentValues();values.put("endtime",endtime);int num = db.update("AdCacheRecord",values,"url=?", new String[]{url});db.close();return num;}public int delete(String url){SQLiteDatabase db = helper.getWritableDatabase();int num = db.delete("AdCacheRecord","url=?",new String[]{url});db.close();return num;}/*** 找出所有信息* */public List<CacheFileInfo> findAll(){SQLiteDatabase db = helper.getWritableDatabase();List<CacheFileInfo> list = new ArrayList<CacheFileInfo>();Cursor cursor = db.query("AdCacheRecord",null,null,null,null,null,null);while(cursor.moveToNext()){int id = cursor.getInt(cursor.getColumnIndex("id"));long endtime = cursor.getLong(cursor.getColumnIndex("endtime"));String url = cursor.getString(cursor.getColumnIndex("url"));CacheFileInfo fileInfo = new CacheFileInfo(id,endtime,url);list.add(fileInfo);}cursor.close();db.close();return list;}/*** 按时间的排序找出所有信息* */public List<CacheFileInfo> findAllForEndtimeSort(){SQLiteDatabase db = helper.getWritableDatabase();List<CacheFileInfo> list = new ArrayList<CacheFileInfo>();Cursor cursor = db.query("AdCacheRecord",null,null,null,null,null,"endtime asc");while(cursor.moveToNext()){int id = cursor.getInt(cursor.getColumnIndex("id"));long endtime = cursor.getLong(cursor.getColumnIndex("endtime"));String url = cursor.getString(cursor.getColumnIndex("url"));CacheFileInfo fileInfo = new CacheFileInfo(id,endtime,url);list.add(fileInfo);}cursor.close();db.close();return list;}public CacheFileInfo findById(int id){SQLiteDatabase db = helper.getWritableDatabase();Cursor cursor = db.query("AdCacheRecord",null,"id=?",new String[]{id+""},null,null,null);CacheFileInfo user = new CacheFileInfo();while(cursor.moveToNext()){int uid = cursor.getInt(cursor.getColumnIndex("id"));long endtime = cursor.getInt(cursor.getColumnIndex("endtime"));String url = cursor.getString(cursor.getColumnIndex("url"));user.setId(uid);user.setEndtime(endtime);user.setUrl(url);break;}cursor.close();db.close();return user;}public static class CacheFileInfo {private int id;private long endtime;private String url;public CacheFileInfo(){ }public CacheFileInfo(int id, long endtime, String url) {this.id = id;this.endtime = endtime;this.url = url;}public int getId() {return id;}public void setId(int id) {this.id = id;}public long getEndtime() {return endtime;}public void setEndtime(long endtime) {this.endtime = endtime;}public String getUrl() {return url;}public void setUrl(String url) {this.url = url;}}
}

3、数据库使用类

AdCacheFileManager.java

package com.seecool.apmservice.ad;import android.util.Log;import android.content.Context;
import java.util.List;
import java.util.Date;
import java.text.SimpleDateFormat;import java.io.File;
import android.content.Context;
import com.seecool.apmservice.APMApplication;public class AdCacheFileManager{private static final String TAG = "AdCacheFileManager";private static AdCacheFileManager mAdCacheFileManager = null;private static AdCacheFileDao mAdCacheFileDao = null;private static Context mContext = null;private static final String DISK_CACHE_PATH = "/web_image_cache/";public static synchronized AdCacheFileManager getInstance(){if(mAdCacheFileManager == null){mAdCacheFileManager = new AdCacheFileManager();}return mAdCacheFileManager;}private AdCacheFileManager(){mContext = APMApplication.getAppContext();mAdCacheFileDao = new AdCacheFileDao(mContext);}public long addOrUpdate(String endtime, String url){if(mAdCacheFileDao == null){mAdCacheFileDao = new AdCacheFileDao(mContext);}SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");Date date = null;try {date = simpleDateFormat.parse(endtime);Log.d(TAG, "Endtime :"+ date.getTime() + " url: " + url);if(mAdCacheFileDao.findByUrl(url)){return mAdCacheFileDao.update(url, date.getTime());}return mAdCacheFileDao.add(date.getTime(), url);} catch (Exception e) {e.printStackTrace();return -1;}}private String getFilePath() {String diskCachePath;Context appContext = APMApplication.getAppContext();diskCachePath = appContext.getCacheDir().getAbsolutePath() + DISK_CACHE_PATH;return diskCachePath;}public void clean(){///:TODO 时间如何保证对了long nowtime = (new Date()).getTime();if(mAdCacheFileDao == null){mAdCacheFileDao = new AdCacheFileDao(mContext);}List<AdCacheFileDao.CacheFileInfo> fileinfo = mAdCacheFileDao.findAll();for(int i = 0;i<= fileinfo.size();i++){AdCacheFileDao.CacheFileInfo info = new AdCacheFileDao.CacheFileInfo();if(info.getEndtime() > nowtime){continue;}mAdCacheFileDao.delete(info.getUrl());//String url = "http://192.168.15.165/rem/material/bg71QRktM9in/2020/07/03/UEsHxIMYsym8.jpg";remove(info.getUrl());//WebImage.removeFromCache(url);}return;}private String getCacheKey(String url) {if(url == null){throw new RuntimeException("Null url passed in");} else {return url.replaceAll("[.:/,%?&=]", "+").replaceAll("[+]+", "+");}}public void remove(String url) {if(url == null){return;}// Remove from file cacheFile f = new File(getFilePath(), getCacheKey(url));if(f.exists() && f.isFile()) {f.delete();}}
}