Android开发中SQLite存储时间和按日期函数查询

Android开发中SQLite使用时间和日期函数

---

在Android开发中，在消息模块中，消息做数据库缓存处理，排序查询或指定时间查询需要用时间和日期函数。

本篇简括：

• 存储时间字段
• 查询结果按时间进行排序
• 指定时间范围查询，按年或者按月
• DAO设计模式操作数据库
• RxJava异步执行数据库操作

存储日期：

思路分析：

• 后台数据源：通常在数据库(例如：MySQL)中存储时间的类型是timeMap，或者Date。

• 网络数据源：若是将date或者timemap直接放到json中，json或传递过来数据类型是long。 或者将一定格式时间字符串传递添加到json,传递过来。

• Android客户端数据库：将网络数据源中时间转成一定格式（例如，YYYY-MM-DD或者YYYY-MM-DD HH:MM:SS.SSS）String字符串，然后用Text格式存储到SQLite中。

备注：按Integer或者Real格式来存储，适用于哪种的时间数据，请阅读上篇SQLite中特殊数据存储介绍。

接下来，按着思路编写相关代码。

首先，创建BaseColumns 的实现类，用于存放数据库中需要的常量：

public class Data implements BaseColumns {
    /**
     * 数据库信息
     */
    public static final String SQLITE_NAME="sqlitePrcactice.db";
    public static final int SQLITE_VERSON=1;
    /**
     * 信息表，及其字段
     */
    public static final String TABLE_NAME="message";
    public static final String COLUMN_CONTENT="content";
    public static final String COLUMN_DATE="date";
    /**
     * 时间字段的格式
     */
    public static final String DATE_FORMAT="YYYY-MM-DD";
    /**
     * 时间字段的降序，采用date函数比较
     */
    public static final String ORDER_BY="date("+COLUMN_DATE+") desc";
}

接下来，编写数据库的代码：

public class DataHelper extends SQLiteOpenHelper {
    //创建表的SQL语句
    public static final String CREATE_MESSAGE="create table "+
            Data.TABLE_NAME+"("+
            Data._ID+" integer primary key autoincrement,"+
            Data.COLUMN_CONTENT+" text,"+
            //这里日期用text格式存储
            Data.COLUMN_DATE+" text"
            +")";

    public DataHelper(Context context) {
        super(context, Data.SQLITE_NAME, null, Data.SQLITE_VERSON);
    }

    @Override

    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_MESSAGE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

最后是插入带有日期的信息到SQLite数据库中：

这里,采用DAO设计模式来操作数据库。

1. 创建表对应实体类类，表中字段对应着类中属性。
2. 创建DAO接口层
3. 创建DAO实现类，具体编写数据库的增删查改代码。

DAO实现类中往数据库插入消息的代码：

  @Override
 public void insert(Message message) {
        SQLiteDatabase sqLiteDatabase = null;
        try {
            sqLiteDatabase = this.dataHelper.getWritableDatabase();
            sqLiteDatabase.insert(Data.TABLE_NAME,null,ValuesTransform.transformContentValues(message));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//释放资源
            if (sqLiteDatabase != null) {
                sqLiteDatabase.close();
            }
        }
    }

各种类对象转换的工具类：

public class ValuesTransform {
    /**
     * 从Cursor生成Message对象
     * @param cursor
     * @return
     */
    public static Message transformMessage(Cursor cursor){
        Message message=new Message();
        message.setId(cursor.getInt(cursor.getColumnIndex(Data._ID)));
        message.setContent(cursor.getString(cursor.getColumnIndex(Data.COLUMN_CONTENT)));
        message.setDate(cursor.getString(cursor.getColumnIndex(Data.COLUMN_DATE)));
        return  message;
    }

    /**
     * 从Message生成ContentValues
     * @param message
     * @return
     */
    public static ContentValues transformContentValues(Message message){
        ContentValues contentValues=new ContentValues();
        contentValues.put(Data.COLUMN_CONTENT,message.getContent());
        contentValues.put(Data.COLUMN_DATE,message.getDate());
        return  contentValues;
    }
}

数据库按时间或者日期查询：

2.1 按照日期大小顺序:

日期字段存储的格式为YYYY-MM-DD，故采用date()函数进行排序,格式为date(时间字段名)。

public class Data implements BaseColumns {
    ..........

    /**
     * 时间字段的降序，采用date函数比较
     */
    public static final String ORDER_BY="date("+COLUMN_DATE+") desc";
}

DAO实现类中查询数据库进行排序的代码

sqLiteDatabase.query(Data.TABLE_NAME, null, selection, selectionArgs, null, null, Data.ORDER_BY);

2.2 指定时间条件查询：

使用format （’%Y-%m-%d’）来查询对应的年月日。这里，查询5月的所有数据，因为月份的数据格式为MM, 不能使用5，使用05. 采用strftime()函数根据指定格式来查询。

select * from mytable where strftime('%m', datatime) = '05'

根据上面SQL语句开始，编写查询条件和查询参数的代码：

    /**
     * 创建查询条件
     * @return
     */
 public String createSelectAction(){
        StringBuffer stringBuffer=new StringBuffer();
        stringBuffer.append("strftime('%m',");
        stringBuffer.append(Data.COLUMN_DATE);
        stringBuffer.append(")=?");
        return  stringBuffer.toString();
 }
 //调用DAO实现类中的查询方法
 queryAction(createSelectAction(),new String[]{"05"});

DAO实现类中的条件查询代码：

    @Override
    public List<Message> queryAction(String selection,
                                     String[] selectionArgs) {
        SQLiteDatabase sqLiteDatabase = null;
        Cursor cursor = null;
        List<Message> list = null;
        try {
            sqLiteDatabase = this.dataHelper.getReadableDatabase();
            cursor = sqLiteDatabase.query(Data.TABLE_NAME, null, selection, selectionArgs, null, null, Data.ORDER_BY);
            list=new ArrayList<>();
            if (cursor != null && cursor.moveToFirst()) {//循环加载
                do {
                    list.add(ValuesTransform.transformMessage(cursor));
                }while (cursor.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {//释放资源
            if (cursor != null) {
                cursor.close();
            }
            if (sqLiteDatabase != null) {
                sqLiteDatabase.close();
            }
        }
        return list;
    }

根据需求来，指定其他条件的查询。例如，查询2017年5月10日的SQL:

select * from mytable where strftime('%Y-%m-%d', datatime) = '2017-05-10'

更多关于时间和日期函数的详情，请阅读SQLite中日期和时间函数介绍。

项目效果图如下：

DAO设计模式，RxJava的使用(1.x和2.x版本不同，代码也不同)，省略不提。

项目代码Github上：https://github.com/13767004362/SQLitePractice

下篇介绍，android原生方式（ContentProvider+SQLite+CursorLoader），实现数据库的观察者模式，实时刷新UI。