jyiosapp/android/app/src/main/java/com/topsoft/jianyu/SQLite/DbBase.java

package com.topsoft.jianyu.SQLite;


import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import org.json.JSONArray;
import org.json.JSONObject;
import java.util.List;


import static android.content.Context.MODE_PRIVATE;

/**
 * Created by wangkaiyue on 2018/1/26.
 */

public class DbBase {

    private final String DB_NAME="jy_db";
    private final String MASSAHE_TABLE_NAME="jy_message";
    static SharedPreferences share=null;


    private static DbBase dbBase;
    private static Context context;

    private final String LOG_TAG = "DbBase";

    public DbBase(Context context){
        this.context=context;
    }
    public static DbBase getInstance(Context ctx){
        context=ctx;
        if (dbBase==null){
            dbBase=new DbBase(ctx);
        }
        share=context.getSharedPreferences("jy", MODE_PRIVATE);
        return dbBase;
    }

    //插入一条信息
    public boolean insertMessage(JyMessage message){
        boolean result = true;
        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getWritableDatabase();
        try{

            db.beginTransaction();
            ContentValues cv= message.getMsaagesContentValues();

            if(db.insert(MASSAHE_TABLE_NAME, null, cv)<0){
                result=false;
            }
            if (result) {
                db.setTransactionSuccessful();
            }
        }catch (Exception e) {
            Log.e(LOG_TAG,e.getMessage());
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    //取单条通知消息
    public JSONObject findOneMessage(String id){
        JSONObject msg=new JSONObject();
        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getReadableDatabase();
        try{
            Cursor cursor = db.query(MASSAHE_TABLE_NAME, new String[]{"id","title","content","link","category","openid","readed","comeintime","descript","extend"}, "id=?", new String[]{id}, null, null, null);
            while(cursor.moveToNext()){
                msg.put("id",cursor.getInt(cursor.getColumnIndex("id"))+"");
                msg.put("title",cursor.getString(cursor.getColumnIndex("title")));
                msg.put("content",cursor.getString(cursor.getColumnIndex("content")));
                msg.put("link",cursor.getString(cursor.getColumnIndex("link")));
                msg.put("category",cursor.getString(cursor.getColumnIndex("category")));
                msg.put("readed",cursor.getInt(cursor.getColumnIndex("readed"))+"");
                msg.put("comeintime",cursor.getInt(cursor.getColumnIndex("comeintime"))+"");
                msg.put("openid",cursor.getString(cursor.getColumnIndex("openid")));
                msg.put("descript",cursor.getString(cursor.getColumnIndex("descript")));
                msg.put("extend",cursor.getString(cursor.getColumnIndex("extend")));
            }
        }catch (Exception e){
            Log.e(LOG_TAG,e.getMessage());
            return null;
        }finally {
            if (null != db) {
                db.close();
            }
        }
        return msg;
    }

    //更新消息状态
    public boolean updateMessageState(Integer id,boolean state) {
        boolean result = true;
        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getWritableDatabase();
        try {
            db.beginTransaction();
            ContentValues cv = new ContentValues();
            if(state){
                cv.put("readed", 1);
            }else{
                cv.put("readed", 0);
            }
            String whereClause = "id=?";
            String[] whereArgs = {id.toString()};
            //参数1 是要更新的表名
            //参数2 是一个ContentValeus对象
            //参数3 是where子句
            if(db.update(MASSAHE_TABLE_NAME, cv, whereClause, whereArgs)<0){
                result=false;
            }
            if (result) {
                db.setTransactionSuccessful();
            }
        } catch (Exception e) {
            Log.e(LOG_TAG,e.getMessage());
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    //根据消息的推送时间改变为已读信息
    public boolean updateMessageState(String pushTime) {
        if("".equals(pushTime)){
            return false;
        }
        boolean result = true;
        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getWritableDatabase();
        try {
            db.beginTransaction();
            ContentValues cv = new ContentValues();
            cv.put("readed", 1);

            String whereClause = "pushTime=?";
            String[] whereArgs = {pushTime};
            //参数1 是要更新的表名
            //参数2 是一个ContentValeus对象
            //参数3 是where子句
            if(db.update(MASSAHE_TABLE_NAME, cv, whereClause, whereArgs)<0){
                result=false;
            }
            if (result) {
                db.setTransactionSuccessful();
            }
        } catch (Exception e) {
            Log.e(LOG_TAG,e.getMessage());
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    //根据推送时间获取信息id
    public Integer getIDbypushTime(String pushTime){
        Integer id=-1;
        if("".equals(pushTime)){
            return id;
        }

        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getReadableDatabase();
        try{
            Cursor cursor=db.query(MASSAHE_TABLE_NAME, new String[]{"id"}, "pushTime=?", new String[]{pushTime}, null, null, null);
            if(cursor.moveToNext()){
                id=cursor.getInt(cursor.getColumnIndex("id"));
            }
            return id;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (null != db) {
                db.close();
            }
        }
        return id;
    }

    //删除一条信息
    public boolean deleteMessageById(Integer id){
        boolean result = true;
        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getReadableDatabase();
        try {
            db.beginTransaction();
            String whereClauses = "id=?";
            String [] whereArgs = {id.toString()};
            if(db.delete(MASSAHE_TABLE_NAME, whereClauses, whereArgs)<0){
                result = false;
            }
            if (result) {
                db.setTransactionSuccessful();
            }
        } catch (Exception e) {
            Log.e(LOG_TAG,e.getMessage());
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    //批量插入
    public boolean insertMessageList(List<JyMessage> list){
        boolean result = true;
        if (null == list || list.size() <= 0) {
            return true;
        }
        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getWritableDatabase();
        try {
            String openid = share.getString("openid", "");
            if("".equals(openid)){
                return false;   //openid为空
            }
            db.beginTransaction();
            for (JyMessage msg : list) {
                ContentValues values = msg.getMsaagesContentValues();
                values.put("openid",openid);
                if (db.insert(MASSAHE_TABLE_NAME, null, values) < 0) {
                    result = false;
                    break;
                }
            }
            if (result) {
                db.setTransactionSuccessful();
            }
        } catch (Exception e) {
            Log.e(LOG_TAG,e.getMessage());
            return false;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    //按通知接收时间倒排，取前100条（或者做分页处理）。
    public JSONArray getMessageList(String openid){
        JSONArray list=new JSONArray();
        JSONObject msg=null;
        DBHelper dbHelper = new DBHelper(context,"jy_db",null,1);
        SQLiteDatabase db =dbHelper.getWritableDatabase();
        try{
            Cursor cursor=null;
            if("".equals(openid)){
                cursor  = db.query("jy_message", new String[]{"id","title","content","link","category","openid","readed","comeintime","descript","extend"}, "openid=?", new String[]{""}, null, null, "comeintime desc");
            }else{
                cursor  = db.query("jy_message", new String[]{"id","title","content","link","category","openid","readed","comeintime","descript","extend"}, "openid=? or openid=?", new String[]{openid,""}, null, null, "comeintime desc");
            }
            int count=0;
            while(cursor.moveToNext()&&count<100){
                count++;
                msg=new JSONObject();
                msg.put("id",cursor.getInt(cursor.getColumnIndex("id"))+"");
                msg.put("title",cursor.getString(cursor.getColumnIndex("title")));
                msg.put("content",cursor.getString(cursor.getColumnIndex("content")));
                msg.put("link",cursor.getString(cursor.getColumnIndex("link")));
                msg.put("category",cursor.getString(cursor.getColumnIndex("category")));
                msg.put("openid",cursor.getString(cursor.getColumnIndex("openid")));
                msg.put("readed",cursor.getInt(cursor.getColumnIndex("readed"))+"");
                msg.put("comeintime",cursor.getInt(cursor.getColumnIndex("comeintime"))+"");
                msg.put("descript",cursor.getString(cursor.getColumnIndex("descript")));
                msg.put("extend",cursor.getString(cursor.getColumnIndex("extend")));
                list.put(msg);
            }
            return  list;
        }catch (Exception e){
            Log.e(LOG_TAG,e.getMessage());
        }finally{
            try {
                if (null != db) {
                    db.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    //获取未读消息数量
    public int getUnReadCount(String openid){
        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getReadableDatabase();
        try{
            //参数1：表名
            //参数2：要想显示的列
            //参数3：where子句
            //参数4：where子句对应的条件值
            //参数5：分组方式
            //参数6：having条件
            //参数7：排序方式
            Cursor cursor =null;
             if("".equals(openid)){
                 cursor=db.query(MASSAHE_TABLE_NAME, new String[]{"id","title","content","link","category","openid","readed","comeintime","descript","extend"}, "openid=? and readed=?", new String[]{"","0"}, null, null, null);
             }else{
                 cursor=db.query(MASSAHE_TABLE_NAME, new String[]{"id","title","content","link","category","openid","readed","comeintime","descript","extend"}, "(openid=? or openid=?)and readed=?", new String[]{openid,"","0"}, null, null, null);
             }
            return cursor.getCount();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (null != db) {
                db.close();
            }
        }
        return 0;
    }

    //所有信息设置为已读
    public int updateAllMegReaded(String openid){
        int num=0;
        DBHelper dbHelper = new DBHelper(context,DB_NAME,null,1);
        SQLiteDatabase db =dbHelper.getWritableDatabase();
        try {
            db.beginTransaction();
            ContentValues cv = new ContentValues();
            cv.put("readed", 1);

            if("".equals(openid)){
                num=db.update(MASSAHE_TABLE_NAME, cv, "openid=?", new String[]{""});
            }else{
                num=db.update(MASSAHE_TABLE_NAME, cv, "openid=? or openid=?", new String[]{openid,""});
            }
            if (num>0) {
                db.setTransactionSuccessful();
            }
        } catch (Exception e) {
            Log.e(LOG_TAG,e.getMessage());
            return num;
        } finally {
            try {
                if (null != db) {
                    db.endTransaction();
                    db.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return num;
    }
}