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

心已赠人 2022-06-16 08:36 684阅读 0赞

## 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中特殊数据存储介绍][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中日期和时间函数介绍][SQLite]。

项目效果图如下：

![这里写图片描述][SouthEast]

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

**项目代码Github上**：[https://github.com/13767004362/SQLitePractice][https_github.com_13767004362_SQLitePractice]

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

[SQLite]: http://blog.csdn.net/hexingen/article/details/71577318
[SouthEast]: /images/20220616/e0672f39e5c54abcbf763711c151bae5.png
[https_github.com_13767004362_SQLitePractice]: https://github.com/13767004362/SQLitePractice