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+"------");
}
}
} 版权声明：本文为showdy原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。