文章标题

来源：互联网发布：破壁机骗局知乎编辑：程序博客网时间：2024/06/07 19:45

Android 学习之sqlite学习笔记（一）

我就以创建一个Message数据库作为例子来总结

创建数据库

//创建message.db数据库SQLiteDatabase mDb = context.openOrCreateDatabase("message", Context.MODE_PRIVATE, null);

根据user_id 创建表，表中有五个字段，分别是message, type,user_id,portrait,readed,date

    //消息内容    private static final String COL_MESSAGE = "message";    // 1：from ; 0:to    private static final String COL_IS_COMING = "type";    //用户id    private static final String COL_USER_ID = "user_id";    //用户头像    private static final String COL_ICON = "portrait";    // 1:readed ; 0 unreaded ;已读或者未读    private static final String COL_READED = "readed";    //消息时间    private static final String COL_DATE = "date";    /**     * 根据user_id创建表，如果表不存在就创建，存在就不创建     * @param userId     */    private void createTable(String userId)    {        mDb.execSQL("CREATE table IF NOT EXISTS _" + userId                + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " //                + COL_USER_ID + " TEXT, " //                + COL_ICON + " text, "//                + COL_IS_COMING + " integer ,"//                + COL_MESSAGE + " text , " //                + COL_DATE + " text , "//                + COL_READED + " integer); ");//    }        //根据user_id创建表//      mDb.execSQL("CREATE table _" + userId//                  + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " ////                  + COL_USER_ID + " TEXT, " ////                  + COL_ICON + " text, "////                  + COL_IS_COMING + " integer ,"////                  + COL_MESSAGE + " text , " ////                  + COL_DATE + " text , "////                  + COL_READED + " integer); ");//

删除表

    /**     * 根据表名删除表     * @param userId     */    public void deleteTable(String userId){        mDb.execSQL("DROP TABLE _" + userId + ";");//    }

添加数据（增）

    /**     * 为每个用户根据其userId创建一张消息表，并向表中添加数据     * @param userId     * @param chatMessage  消息实体类（五个属性）     */    public void add(String userId, ChatMsgEntity chatMessage)    {        createTable(userId);        int isComing = chatMessage.getType();        int readed = chatMessage.isReaded() ? 1 : 0;        mDb.execSQL(                "insert into _" + userId + " (" + COL_USER_ID + "," + COL_ICON                + "," + COL_IS_COMING + "," + COL_MESSAGE + ","                + COL_DATE + "," +  COL_READED                 + ") values(?,?,?,?,?,?)",                new Object[] { chatMessage.getUserId(), chatMessage.getPortrait(),                        isComing, chatMessage.getContent(),                        chatMessage.getDateStr(),readed});    }

通过userId获取未读消息个数

    /**     * 通过userId获取未读消息个数     * @param userId     * @return     */    private int getUnreadedMsgsCountByUserId(String userId)    {        createTable(userId);        String sql = "select count(*) as count from _" + userId + " where "                + COL_IS_COMING + " = 1 and " + COL_READED + " = 0";        Cursor c = mDb.rawQuery(sql, null);        int count = 0;        if (c.moveToNext())            count = c.getInt(c.getColumnIndex("count"));        c.close();        return count;    }

0 0