//创建数据库表
public class DatabaseTable {
private final String SQL_CREATE = "create table IF NOT EXISTS todiItem (id integer primary key autoincrement, "
+ "msgid integer not null, " +"userid integer not null, " + "msgcontent text default '');";
private final String TABLE_NAME = "DatabaseTest";
private final String ID = "id";
private final String MSGID = "msgid"; //消息ID
private final String USERID = "userid"; //消息ID
private final String MSGCONTENT = "msgcontent"; //文本消息内容
private final String[] CULUMNS = new String[] { ID, MSGID, USERID, MSGCONTENT};
public String[] getCulumns(){
return CULUMNS;
}
public String getSqlCrate(){
return SQL_CREATE;
}
public String getTableName(){
return TABLE_NAME;
}
public String getId(){
return ID;
}
public String getMsgId(){
return MSGID;
}
public String getUserId(){
return USERID;
}
public String getMsgContent(){
return MSGCONTENT;
}
}
//创建类数据
public class DatabaseMsg {
public int id; //数据库中ID值
public int msgid; //消息ID
public int userid; //用户ID
public String msgcontent; //文本消息内容
public DatabaseMsg(){
id = 0;
msgid = 0; //消息ID
userid = 0; //用户ID
msgcontent = ""; //文本消息内容
}
}
//对数据库进行操作
public class DatabaseCtrl {
public static final String DATABASE_NAME = "test_db";
private static final int DATABASE_VERSION = 1;
private DatabaseHelper dbHelper;
public SQLiteDatabase db;
private static DatabaseCtrl dataAdater;
private static DatabaseTable databaseTabel;
/*
* 数据库单例模式
* */
public static DatabaseCtrl getInstance(){
if (dataAdater == null){
dataAdater = new DatabaseCtrl();
}
if (databaseTabel == null){
databaseTabel = new DatabaseTable();
}
return dataAdater;
}
/*
* 创建并打开数据库
* */
public int createAndOpenDbAdapter(Context context) {
if (dataAdater == null)
return -1;
dataAdater.open(context);
return 0;
}
/*
* 关闭数据库
* */
public void closeDbAdapter() {
if (dataAdater != null) {
dataAdater.close();
}
}
/*
* 打开数据库
* */
private void open(Context context) throws SQLException {
synchronized (DATABASE_NAME) {
if (dbHelper == null) {
dbHelper = new DatabaseHelper(context);
}
if (db == null) {
db = dbHelper.getWritableDatabase();
}
}
}
/*
* 关闭数据库
* */
private void close() {
if (dbHelper != null) {
dbHelper.close();
}
if (db != null) {
db.close();
}
dbHelper = null;
db = null;
}
/*
* 插入一条聊天记录
* */
public synchronized long insertMessageResponseItem(DatabaseMsg msgItem) {
ContentValues values = new ContentValues();
values.put(databaseTabel.getMsgId(), msgItem.msgid);
values.put(databaseTabel.getUserId(), msgItem.userid);
values.put(databaseTabel.getMsgContent(), msgItem.msgcontent);
return db.insert(databaseTabel.getTableName(), null, values);
}
/*
* 获取单条聊天记录
* */
public List<HashMap<String, String>> getItemMsg(String strId){
List<HashMap<String, String>> listData = new ArrayList<HashMap<String, String>>();
String selection = databaseTabel.getId() + "=?";
String[] args = new String[] { strId };
Cursor cursor = db.query(databaseTabel.getTableName(), databaseTabel.getCulumns(), selection, args, null, null, null);
if (cursor != null) {
cursor.moveToFirst();
justCursor(cursor, listData);
cursor.close();
}
return listData;
}
/*
* 获取所有记录
* */
public List<HashMap<String, String>> getAllItemsMsg() {
String selection = null;// MessageCenterTable.URL + " is not null";
Cursor cursor = db.query(databaseTabel.getTableName(), databaseTabel.getCulumns(), selection, null, null, null, null);
List<HashMap<String, String>> listData = new ArrayList<HashMap<String, String>>();
if (cursor != null) {
cursor.moveToFirst();
justCursor(cursor, listData);
cursor.close();
}
return listData;
}
/*
* 获取某一类记录
* */
public List<HashMap<String, String>> getUserRecord(String strId){
List<HashMap<String, String>> listData = new ArrayList<HashMap<String, String>>();
String selection = databaseTabel.getUserId() + "=?";
String[] args = new String[] { strId };
Cursor cursor = db.query(databaseTabel.getTableName(), databaseTabel.getCulumns(), selection, args, null, null, null);
if (cursor != null) {
cursor.moveToFirst();
justCursor(cursor, listData);
cursor.close();
}
return listData;
}
/*
* 删除单个记录
* */
public boolean deleteOneItem(String id) {
boolean result = db.delete(databaseTabel.getTableName(), databaseTabel.getId() + "=" + "?", new String[] { id }) > 0;
return result;
}
/*
* 删除某一类中的一条消息
* */
public boolean deleteItem(int srcType, String id){
String selection = "datasrc = ? and id = ?";
String[] args = new String[2];
args[0] = Integer.toString(srcType);
args[1] = id;
return db.delete(databaseTabel.getTableName(), selection, args) > 0;
}
/*
* 删除某一类消息
* */
public boolean deleteItem(int srcType){
String strType = Integer.toString(srcType);
return db.delete(databaseTabel.getTableName(), databaseTabel.getDataSrc() + "=" + "?", new String[] { strType }) > 0;
}
/*
* 获取一类信息
* */
public List<HashMap<String, String>> getDataSource(int userId) {
List<HashMap<String, String>> listData = new ArrayList<HashMap<String, String>>();
String selection = databaseTabel.userId+ "=" + "?";
String[] args = new String[] { Integer.toString(userId) };
Cursor cursor = db.query(databaseTabel.getTableName(), null, selection, args, null, null,null);
if (cursor != null) {
cursor.moveToFirst();
justCursor(cursor, listData);
cursor.close();
}
return listData;
}
/*
* 数据更新
* */
public int UpdateSendState(String id, int sendState) {
String selection="";
selection = databaseTabel.getId() + "=" + "?";
String[] args = new String[] { id };
ContentValues cValues = new ContentValues();
cValues.put(databaseTabel.getSendState(), Integer.toString(sendState));
int cursor = db.update(databaseTabel.getTableName(), cValues, selection, args);
return cursor;
}
/*
* 查看所有数据
*/
public void justCursor(Cursor cursor, List<HashMap<String, String>> listData) {
for (int i = 0; i < cursor.getCount(); i++) {
HashMap<String, String> hsMap = new HashMap<String, String>();
hsMap.put(databaseTabel.getId(), cursor.getString(cursor.getColumnIndex(databaseTabel.getId())));
hsMap.put(databaseTabel.getMsgId(), cursor.getString(cursor.getColumnIndex(databaseTabel.getMsgId())));
hsMap.put(databaseTabel.getUserId(), cursor.getString(cursor.getColumnIndex(databaseTabel.getUserId())));
hsMap.put(databaseTabel.getMsgContent(), cursor.getString(cursor.getColumnIndex(databaseTabel.getMsgContent())));
listData.add(hsMap);
cursor.moveToNext();
}
}
public void createTables() {
Log.i("DbAdapter", "Create DB");
db.execSQL(databaseTabel.getTableName());
}
public class DatabaseHelper extends SQLiteOpenHelper {
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.i("DatabaseHelper", "Create DB");
db.execSQL(databaseTabel.getSqlCrate());
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i("DatabaseHelper", "onUpgrade");
db.execSQL("DROP TABLE IF EXISTS " + databaseTabel.getTableName());
onCreate(db);
}
}
}