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

    @Override
    public void onCreate(SQLiteDatabase db) {
        //初始化数据库的表结构 d integer primary key autoincrement,
        db.execSQL(CREATE_FILE_PAGE);
    }
    
    @Override
    public 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 cache
        File f = new File(getFilePath(), getCacheKey(url));
        if(f.exists() && f.isFile()) {
            f.delete();
        }
    }
}

结果

 

本文地址：https://blog.csdn.net/twk121109281/article/details/107181682