使用Sqlite保存数据

来源：互联网发布：js触发a标签的href 编辑：程序博客网时间：2024/05/20 23:04

1. 定义bean类:

public class XGMessage {    public int msgType; //消息类型. 0--链接(有url--打开网页, 没有url--打开app); 1-报警    public String content; //消息内容    public String url; //消息url    public String receiveTime; //接收消息的时间    public int isRead;  //是否已读。0-未读，1-已读    public String mac; //设备的mac地址    public String username;    public String entryId;}

2. 定义契约类:

/** * 契约类是用于定义 URI、表格和列名称的常数的容器 * 通过实现 BaseColumns 接口，您的内部类可继承调用的主键字段_ID ，某些 Android 类（比如光标适配器） * 将需要内部类拥有该字段。 这并非必需项，但可帮助您的数据库与 Android 框架协调工作。 */public final class XGMessageContract {    // To prevent someone from accidentally instantiating the contract class,    // give it an empty constructor.    private XGMessageContract() {    }    /**     * BaseColumns接口包换_id,_count两个字段     */    public static abstract class XGMessageEntry implements BaseColumns {        public static final String TABLE_NAME="xg_message_list";        public static final String COLUMN_NAME_MSG_USERNAME="username";        public static final String COLUMN_NAME_MSG_ENTRY_ID ="entryId";        public static final String COLUMN_NAME_MSG_TYPE="msgType";        public static final String COLUMN_NAME_CONTENT="content";        public static final String COLUMN_NAME_URL="url";        public static final String COLUMN_NAME_RECEIVE_TIME="receiveTime";        public static final String COLUMN_NAME_MAC="mac";        public static final String COLUMN_NAME_IS_READ="isRead";        public static final String COLUMN_NAME_MSG_IS_DELETE="isDelete";        public static final String COLUMN_NAME_MSG_INTENT="intent";    }}

3. 编写常量类:

public interface Constants {    String DATABASE_NAME = "XGMessage.db";    int VERSION_CODE = 4;    String SQL_CREATE_MSG_ENTRIES =            "CREATE TABLE " + XGMessageEntry.TABLE_NAME + "("                    + XGMessageEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"                    + XGMessageEntry.COLUMN_NAME_MSG_ENTRY_ID + " TEXT NOT NULL,"                    + XGMessageEntry.COLUMN_NAME_MSG_USERNAME + " TEXT NOT NULL,"                    + XGMessageEntry.COLUMN_NAME_MSG_TYPE + " INTEGER DEFAULT -1,"                    + XGMessageEntry.COLUMN_NAME_CONTENT + " TEXT,"                    + XGMessageEntry.COLUMN_NAME_URL + " TEXT,"                    + XGMessageEntry.COLUMN_NAME_RECEIVE_TIME + " TEXT,"                    + XGMessageEntry.COLUMN_NAME_IS_READ + " INTEGER DEFAULT 0,"                    + XGMessageEntry.COLUMN_NAME_MAC + " TEXT DEFAULT 0"                    + ");";    String SQL_DELETE_MSG_ENTRIES =            "DROP TABLE IF EXISTS " + XGMessageEntry.TABLE_NAME;    String SQL_UPDATE_FIRST_MSG_ENTRIES =            "ALTER TABLE"+ XGMessageEntry.TABLE_NAME +" ADD "                    + XGMessageEntry.COLUMN_NAME_MSG_IS_DELETE+" INTEGER DEFAULT 1";    String SQL_UPDATE_SECOND_MSG_ENTRIES =            "ALTER TABLE"+ XGMessageEntry.TABLE_NAME +" ADD "                    + XGMessageEntry.COLUMN_NAME_MSG_IS_DELETE+" INTEGER DEFAULT 1,"                    + XGMessageEntry.COLUMN_NAME_MSG_INTENT+" TEXT NOT NULL";}

4. 使用SQL工具创建数据库:

public class XGMessageDBHelper extends SQLiteOpenHelper{    public XGMessageDBHelper(Context context) {        super(context, Constants.DATABASE_NAME, null, Constants.VERSION_CODE);    }    @Override    public void onCreate(SQLiteDatabase db) {        /*创建推送消息的表*/        db.execSQL(Constants.SQL_CREATE_MSG_ENTRIES);    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        // This database is only a cache for online data, so its upgrade policy is        // to simply to discard the data and start over        switch (oldVersion){            case 1:                db.execSQL(Constants.SQL_UPDATE_FIRST_MSG_ENTRIES);                break;            case 2:                db.execSQL(Constants.SQL_UPDATE_SECOND_MSG_ENTRIES);                break;            case 3:                db.execSQL(Constants.SQL_DELETE_MSG_ENTRIES);                onCreate(db);                break;        }    }    @Override    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {        onUpgrade(db, oldVersion, newVersion);    }}

5. 编写操作数据的Dao类:

public class XGMessageDao {    public static volatile XGMessageDao sMessageDao;    private XGMessageDBHelper mHelper;    public XGMessageDao(Context context) {        mHelper = new XGMessageDBHelper(context);    }//    public XGMessageDao getInstance(Context context) {//        if (sMessageDao == null) {//            synchronized (XGMessageDao.class) {//                if (sMessageDao == null) {//                    sMessageDao = new XGMessageDao(context);//                }//            }//        }//        return sMessageDao;//    }    /**     * 添加消息到数据库中     *     * @param msg 信息     * @return true--添加成功     */    public boolean insert(XGMessage msg) {        SQLiteDatabase db = mHelper.getWritableDatabase();        ContentValues values = new ContentValues();        values.put(COLUMN_NAME_MSG_USERNAME, msg.username);        values.put(COLUMN_NAME_MSG_USERNAME, msg.entryId);        values.put(COLUMN_NAME_MSG_TYPE, msg.msgType);        values.put(COLUMN_NAME_CONTENT, msg.content);        values.put(COLUMN_NAME_URL, msg.url);        values.put(COLUMN_NAME_RECEIVE_TIME, msg.receiveTime);        values.put(COLUMN_NAME_IS_READ, msg.isRead);        values.put(COLUMN_NAME_MAC, msg.mac);        /*第二个参数指定在 ContentValues 为空的情况下框架可在其中插入 NULL 的列的名称（如果您将其设置为 "null"，         那么框架将不会在没有值时插入行。）*/        long newRow = db.insert(TABLE_NAME, null, values);        db.close();        return newRow != -1;    }    /**     * 根据ID和用户名删除信息     *     * @param username 用户名     * @return true--删除成功     */    public boolean delete(String username) {        SQLiteDatabase db = mHelper.getWritableDatabase();        String whereClause= XGMessageEntry.COLUMN_NAME_MSG_USERNAME+ " LIKE ?" ;        String[] whereArgs= new String[]{username};        int delete = db.delete(                TABLE_NAME,                whereClause,    //约束删除行,不指定默认删除所有行                whereArgs); //对应数据        db.close();        return delete != 0;    }    /**     * 更新数据消息的是否阅读     *     * @param msg 消息     * @return true--更新成功     */    public boolean update(XGMessage msg) {        // New value for one column        SQLiteDatabase db = mHelper.getWritableDatabase();        db.beginTransaction();        int update=0;        try {            ContentValues values = new ContentValues();            values.put(XGMessageEntry.COLUMN_NAME_IS_READ,msg.isRead);            // Which row to update, based on the ID            String whereClause = XGMessageEntry.COLUMN_NAME_MSG_ENTRY_ID + " LIKE ?";            String[] whereArgs = new String[]{String.valueOf(msg.entryId)};            update = db.update(   //                    TABLE_NAME,  //更新的表名                    values,  //更新的值                    whereClause, //更新的行要求                    whereArgs);//更新行值要求            db.setTransactionSuccessful();        }catch (Exception e){            //处理异常        }finally {            db.close();            db.endTransaction();        }        return update != 0;    }    /**     * 根据用户名获取所有的推送消息     *     * @return     */    public List<XGMessage> getAllMessage(String username) {        SQLiteDatabase db = mHelper.getReadableDatabase();        // Define a projection that specifies which columns from the database        // you will actually use after this query, passing null get all columns        String[] projection = {                XGMessageEntry.COLUMN_NAME_MSG_ENTRY_ID,                XGMessageEntry.COLUMN_NAME_MSG_TYPE,                XGMessageEntry.COLUMN_NAME_MSG_USERNAME,                XGMessageEntry.COLUMN_NAME_CONTENT,                XGMessageEntry.COLUMN_NAME_URL,                XGMessageEntry.COLUMN_NAME_IS_READ,                XGMessageEntry.COLUMN_NAME_RECEIVE_TIME,                XGMessageEntry.COLUMN_NAME_MAC,        };        String selection = COLUMN_NAME_MSG_USERNAME+" LIKE ?";        String[] selectionArgs = new String[]{username};        String sortOrder = XGMessageEntry.COLUMN_NAME_RECEIVE_TIME + " DESC";        /*select * from xg_message_list where username=? order by receiveTime desc */        Cursor cursor = db.query(                XGMessageEntry.TABLE_NAME,  // 查询的表名                null,                       // 返回的列数据要求                selection,                  // 查询列要求                selectionArgs,              // 查询值要求                null,                       // 分组语句                null,                       // 分组过滤操作                sortOrder                   // 排序        );        List<XGMessage> list = new ArrayList<>();        if (cursor != null) {            while (cursor.moveToNext()) {                XGMessage msg = new XGMessage();                msg.msgType = cursor.getInt(cursor.getColumnIndex(XGMessageEntry.COLUMN_NAME_MSG_TYPE));                msg.msgType = cursor.getInt(cursor.getColumnIndex(XGMessageEntry.COLUMN_NAME_MSG_ENTRY_ID));                msg.content = cursor.getString(cursor.getColumnIndex(XGMessageEntry.COLUMN_NAME_CONTENT));                msg.url = cursor.getString(cursor.getColumnIndex(XGMessageEntry.COLUMN_NAME_URL));                msg.receiveTime = cursor.getString(cursor.getColumnIndex(XGMessageEntry.COLUMN_NAME_RECEIVE_TIME));                msg.isRead = cursor.getInt(cursor.getColumnIndex(XGMessageEntry.COLUMN_NAME_IS_READ));                msg.mac = cursor.getString(cursor.getColumnIndex(XGMessageEntry.COLUMN_NAME_MAC));                msg.mac = cursor.getString(cursor.getColumnIndex(XGMessageEntry.COLUMN_NAME_MSG_USERNAME));                list.add(msg);            }            cursor.close();        }        db.close();        return list;    }}

6. 测试类:

public class TestDataBase extends AndroidTestCase {    public static final String TAG=TestDataBase.class.getSimpleName();    public void testCreate(){    }    public void testInsert(){        XGMessageDao dao= new XGMessageDao(getContext());        XGMessage msg= new XGMessage();        msg.username="shu";        msg.content="hha";        msg.entryId="12333";        msg.msgType=1;        msg.mac="a23040df";        msg.isRead=1;        msg.url="http://www.baidu.com";        msg.receiveTime="12344454555";        dao.insert(msg);    }    public void testDelete(){        XGMessageDao dao= new XGMessageDao(getContext());        boolean isDelete = dao.delete("shu");    }    public void testUpdate(){        XGMessageDao dao= new XGMessageDao(getContext());        XGMessage msg= new XGMessage();        msg.entryId="12333";        msg.isRead=0;        dao.update(msg);    }    public void testQuery(){        XGMessageDao dao= new XGMessageDao(getContext());        List<XGMessage> msgs = dao.getAllMessage("shu");        for (int i = 0; i < msgs.size(); i++) {            XGMessage msg = msgs.get(i);            Log.d(TAG, "testQuery--->"+ msg.username+msg.content+"------");        }    }}

0 1