欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-08-30 07:59:13
写一个数据库实例,方便以后参考。1、数据库创建类AdCacheFileDB .javapackage com.seecool.apmservice.ad;import android.content.Context;import android.database.sqlite.SQLiteOpenHelper;import android.database.sqlite.SQLiteDatabase;public class AdCacheFileDB extends SQL....

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

推荐一篇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();
        }
    }
}

结果

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

 

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