MsgDBUtils(创建数据库，表，增，改，查)

package com.wmys.doctor.xmpp;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class MsgDBUtils {

    public static void insertConsult(Context context, JSONObject json) {
        SQLiteDatabase db = context.openOrCreateDatabase("msg.db", Context.MODE_PRIVATE, null);
        try {
            // String id = json.getString("id");
            // String jid = json.getString("jid");
            // String nickName = json.getString("nickname");
            // String avatar = json.getString("avatar");
            // String patient = json.getString("patient");
            // String disease = json.getString("disease");
            // String newCount = json.getString("new_msg_count");
            // String msgIds = "";
            // String status = "";
            // String lastMsg = "";

            ContentValues cv = new ContentValues();
            cv.put("id", "1");
            cv.put("jid", "jid");
            cv.put("nick_name", "nick");
            cv.put("avatar", "avatar");
            cv.put("patient", "patient");
            cv.put("disease", "disease");
            cv.put("new_counts", "newCount");
            // cv.put("msgIds", msgIds);
            cv.put("status", "0");
            cv.put("last_msg", "xxxxxxxxxxxxxxxxxxxxxxx");
            // 插入ContentValues中的数据
            db.insert("consult", null, cv);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
    }

    public static void insertMsg(Context context, String json, String id) {
        SQLiteDatabase db = context.openOrCreateDatabase("msg.db", Context.MODE_PRIVATE, null);
        ContentValues cv = new ContentValues();
        cv.put("json", json);
        cv.put("consult_id", id);
        cv.put("date", new Date()+"");
        // 插入ContentValues中的数据
        db.insert("message", null, cv);
        updataMsg(db, id);
        setLastMsg(db, json, id);
        db.close();
    }

    private static void updataMsg(SQLiteDatabase db, String where) {
        Cursor cursor = db.query("consult", new String[] { "new_counts", "msg_ids" }, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            int newCounts = cursor.getInt(cursor.getColumnIndexOrThrow("new_counts"));
            // String msgIds =
            // cursor.getString(cursor.getColumnIndexOrThrow("msg_ids"));
            ContentValues cv = new ContentValues();
            cv.put("new_counts", newCounts + 1);
            // cv.put("msg_ids", msgIds + "~" + where);
            db.update("consult", cv, "id=?", new String[] { where });
        }

    }

    public static List<String> getMessage(Context context, String id) {
        List<String> list = new ArrayList<String>();
        SQLiteDatabase db = context.openOrCreateDatabase("msg.db", Context.MODE_PRIVATE, null);
        Cursor cursor = db.query("message", new String[] { "json,date" }, "consult_id=?", new String[] { id }, null, null,
                "date desc");
        int s = cursor.getCount();

        while (cursor.moveToNext()) {
            
            list.add(cursor.getString(cursor.getColumnIndexOrThrow("json")));
        }

        clearMsg(db, id);

        return list;
    }

    private static void clearMsg(SQLiteDatabase db, String where) {
        ContentValues cv = new ContentValues();
        cv.put("new_counts", 0);

        db.update("consult", cv, "id=?", new String[] { where });
        db.close();
    }

    public static void setLastMsg(SQLiteDatabase db, String json, String where) {
        ContentValues cv = new ContentValues();
        cv.put("last_msg", json);
        // 插入ContentValues中的数据
        db.update("consult", cv, "id=?", new String[] { where });
    }

    public static String getLastMsg(Context context, String id) {
        String message = "";
        SQLiteDatabase db = context.openOrCreateDatabase("msg.db", Context.MODE_PRIVATE, null);
        Cursor cursor = db.query("consult", new String[] { "last_msg" }, "id=?", new String[] { id }, null, null, null);
        if (cursor.moveToFirst()) {
            message = cursor.getString(cursor.getColumnIndexOrThrow("last_msg"));
        }
        db.close();
        return message;
    }
    
    public static void createConsultTable(SQLiteDatabase db) {
        String tab_field = "id INTEGER PRIMARY KEY," + " jid VARCHAR(30)," + "patient  VARCHAR(10),"
                + "nick_name VARCHAR(10)," + "avatar VARCHAR(10)," + "disease  VARCHAR(20)," + "new_counts INTEGER,"
                + "msg_ids TEXT," + "status INTEGER," + "last_msg TEXT";
        db.execSQL("DROP TABLE IF EXISTS consult");
        db.execSQL("CREATE TABLE consult (" + tab_field + " )");

    }

    public static void createMessageTable(SQLiteDatabase db) {
        String tab_field = "id INTEGER," + "json TEXT,"
                + "consult_id INTEGER,date DATE";
        db.execSQL("DROP TABLE IF EXISTS message");
        db.execSQL("CREATE TABLE message (" + tab_field + ")");

    }

    public static void closeMsgDB(SQLiteDatabase db) {
        db.close();
    }

}

 

public static void isHaveTable(Context context) {
        // db.execSQL("SELECT name FROM sqlite_master WHERE type='table' order
        // by name");
        SQLiteDatabase db = context.openOrCreateDatabase("msg.db", Context.MODE_PRIVATE, null);
        Cursor cursor = db.query("sqlite_master", new String[] { "name" }, "type=?", new String[] { "table" }, null,
                null, null);
        int s = cursor.getCount();
        if (s == 1) {
            createConsultTable(db);
            createMessageTable(db);
        }
        closeMsgDB(db);
    }
    
    public static void delTable(Context context){
        SQLiteDatabase db = context.openOrCreateDatabase("msg.db", Context.MODE_PRIVATE, null);
        db.execSQL("DROP TABLE IF EXISTS message");
        db.execSQL("DROP TABLE IF EXISTS consult");
        closeMsgDB(db);
    }

public static void delMessage(Context context, String id) {
        int maxLength = 0;
        SQLiteDatabase db = context.openOrCreateDatabase("msg.db", Context.MODE_PRIVATE, null);
        // db.delete("person", "age < ?", new String[]{"35"});
        Cursor cursor = db.query("message", new String[] { "json,date" }, "consult_id=?", new String[] { id }, null,
                null, null);
        int s = cursor.getCount();
        if (s >1) {
            while (cursor.moveToNext() && maxLength < 1) {
                maxLength++;
                String date = cursor.getString(cursor.getColumnIndexOrThrow("date"));
                db.delete("message", "date=?", new String[] { date });
            }
            db.close();

        }

    }