DBMessageController.java
5.41 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
package com.shunzhi.parent.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.List;
/**
* Created by ToaHanDong on 2018/4/23.
*/
public class DBMessageController {
public static DBMessageController dbMessageController = null;
public static DBMessageController getInstance(Context context) {
if (null == dbMessageController) {
synchronized (DBMessageController.class) {
if (null == dbMessageController)
dbMessageController = new DBMessageController(context, false);
}
}
return dbMessageController;
}
private Context context;
private SQLiteDatabase database;
public static final String DATABASE = "message.db";
public static final String TABLE_MESSAGE = "message";//表名
public static final String TABLE_TEMP="temp";//备份的表
public static String TEMP_SQL= "alter table "+TABLE_MESSAGE+" rename to "+TABLE_TEMP;
public static String COPY_SQL="insert into "+TABLE_MESSAGE+" select * ,' ' from "+TABLE_TEMP;
public static String DROP_SQL="drop table "+TABLE_TEMP;
public static String sqlVersion2="create table if not exists " + DBMessageController.TABLE_MESSAGE + "(" +
DBMessageController.MESSAGE_ID + " integer primary key autoincrement," +
DBMessageController.MESSAGE_TITLE + " text unique," +
DBMessageController.MESSAGE_MSGID + " text," +
DBMessageController.MESSAGE_USERID + " text," +
DBMessageController.MESSAGE_TYPE + " integer," +
DBMessageController.MESSAGE_DATE+" text,"+
DBMessageController.MESSAGE_ISREAD + " text)";
public static final String MESSAGE_ID = "_id"; //主键
public static final String MESSAGE_TITLE = "title"; //内容
public static final String MESSAGE_USERID = "userid"; //用户的唯一标识
public static final String MESSAGE_TYPE = "type"; //消息类型
public static final String MESSAGE_ISREAD = "isread";//判断消息是否为已读
public static final String MESSAGE_MSGID = "msgid";//判断消息是否为已读
public static final String MESSAGE_DATE="date";//日期
static void createSongTable(SQLiteDatabase db) {
String sql = "create table if not exists " + DBMessageController.TABLE_MESSAGE + "(" +
DBMessageController.MESSAGE_ID + " integer primary key autoincrement," +
DBMessageController.MESSAGE_TITLE + " text unique," +
DBMessageController.MESSAGE_MSGID + " text," +
DBMessageController.MESSAGE_USERID + " text," +
DBMessageController.MESSAGE_TYPE + " integer," +
DBMessageController.MESSAGE_ISREAD + " text)";
db.execSQL(sql);
}
/**
* 在使用结束时应调用{@link #()}关闭数据库连接
*/
public DBMessageController(Context context, boolean writable) {
DBHelper helper = new DBHelper(context, DATABASE);
if (writable) {
this.database = helper.getWritableDatabase();
} else {
this.database = helper.getReadableDatabase();
}
this.context = context;
}
public void close() {
if (database.isOpen()) {
database.close();
}
}
public long addMessages(Messages messages) {
ContentValues contentValues = new ContentValues();
contentValues.put(DBMessageController.MESSAGE_TITLE, messages.title);
contentValues.put(DBMessageController.MESSAGE_TYPE, messages.type);
contentValues.put(DBMessageController.MESSAGE_USERID, messages.userid);
contentValues.put(DBMessageController.MESSAGE_ISREAD, messages.isRead);
contentValues.put(DBMessageController.MESSAGE_MSGID, messages.msgId);
contentValues.put(DBMessageController.MESSAGE_DATE,messages.date);
return database.insert(DBMessageController.TABLE_MESSAGE, null, contentValues);
}
public int getMessagesCounts(String type) {
String sql = "select * from " + TABLE_MESSAGE + "where type = ?";
Cursor cursor = database.rawQuery(sql, new String[]{type});
return cursor.getCount();
}
public int getMessagesCountsAllNoRead() {
String sql = "select * from " + TABLE_MESSAGE + " where isRead = ?";
Cursor cursor = database.rawQuery(sql, new String[]{"false"});
return cursor.getCount();
}
public void setMeaasgesRead() {
ContentValues contentValues = new ContentValues();
contentValues.put(MESSAGE_ISREAD, "true");
String whereClause="isRead = ?";
String[] whereArgs = new String[]{"false"};
database.update(TABLE_MESSAGE, contentValues, whereClause, whereArgs);
}
public List<Messages> getAllMessages() {
String sql = "select * from " + TABLE_MESSAGE;
Cursor cursor = database.rawQuery(sql, null);
List<Messages> messagesList = new ArrayList<>();
while (cursor.moveToNext()) {
Messages messages = new Messages();
messages.type = cursor.getInt(cursor.getColumnIndex(MESSAGE_TYPE));
messages.title = cursor.getString(cursor.getColumnIndex(MESSAGE_TITLE));
messages.date=cursor.getString(cursor.getColumnIndex(MESSAGE_DATE));
messagesList.add(messages);
}
return messagesList;
}
}