sqlite 版本升级及数据库操作类（二）

数据库操作工具类（增删改查）

/**
 * 保存数据到数据库
 */
public void savaShowData(String ctype, String title, String source) {
    synchronized (mySqlitehelper) {
        if (!writableDatabase.isOpen()) {
            writableDatabase = mySqlitehelper.getWritableDatabase();
        }
        writableDatabase.beginTransaction();
        try {
            ContentValues contentValues = new ContentValues();
            contentValues.put("ctype", ctype);
            contentValues.put("title", title);
            contentValues.put("source", source);
            writableDatabase.insert("NewsShow", null, contentValues);
            writableDatabase.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            writableDatabase.endTransaction();
            writableDatabase.close();
        }
    }
}

/**
 * 获取数据库缓存的前30条数据
 * @return
 */
public List getshowDate() {
    synchronized (mySqlitehelper) {
        if (!writableDatabase.isOpen()) {
            writableDatabase = mySqlitehelper.getWritableDatabase();
        }
        Cursor cursor = writableDatabase.query("NewsShow", null, null, null, null, null, null, null);
        Cursor queryCursor = null;
        try {
            if (cursor.getCount() > 0) {
                int limitNum;
                if (cursor.getCount() <= 30) limitNum = cursor.getCount();
                else limitNum = cursor.getCount() - (cursor.getCount() - 30);
                //select * from 表名 limit 0,10;
                String sql = "select * from NewsShow limit " + 0 + "," + limitNum;
                queryCursor = writableDatabase.rawQuery(sql, null);
                showList = new ArrayList<News>();
                while (queryCursor.moveToNext()) {
                    String ctype = queryCursor.getString(queryCursor.getColumnIndex("ctype"));
                    String title = queryCursor.getString(queryCursor.getColumnIndex("title"));
                    String source = queryCursor.getString(queryCursor.getColumnIndex("source"));
                    showList.add(new News(ctype, title, source, source_type, type, detail_a, listImages, data_id, false, isRead));
                }
                return showList;
            } else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != queryCursor)
                queryCursor.close();
            cursor.close();
        }
    }
    return showList;
}
/**
 * 删除数据库指定数据 (_id)
 * @param number
 */
public void deleteData(int number) {
    synchronized (mySqlitehelper) {
        if (!writableDatabase.isOpen()) {
            writableDatabase = mySqlitehelper.getWritableDatabase();
        }
        try {
            writableDatabase.delete("NewsShow", "_id=?", new String[]{String.valueOf(number)});
            writableDatabase.close();
        } catch (Exception e) {
            e.printStackTrace();
            LogUtils.e(TAG, "delete fail" + e);
        } finally {
            writableDatabase.close();
        }
    }
}
/**
 * 清空数据库并重置id
 */
public void deleteShowData() {
    synchronized (mySqlitehelper) {
        if (!writableDatabase.isOpen()) {
            writableDatabase = mySqlitehelper.getWritableDatabase();
        }
        try {
            String sql = "DELETE FROM " + "NewsShow" + ";";
            writableDatabase.execSQL(sql);
            String sqlRevert = "update sqlite_sequence set seq=0 where name='" + "NewsShow" + "'";
            writableDatabase.execSQL(sqlRevert);
            writableDatabase.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            writableDatabase.close();
        }
    }
}
/**
 *  查询数据库是否已存在某条数据
 */
public String queryDate(String detail_a) {
    String url = null;
    synchronized (mySqlitehelper) {
        if (!writableDatabase.isOpen()) {
            writableDatabase = mySqlitehelper.getWritableDatabase();
        }
        String sql = "select detail_a from NewsShow where detail_a='" + detail_a + "'";
        Cursor cursor = writableDatabase.rawQuery(sql, null);
        try {
            while (cursor.moveToNext()) {
                url = cursor.getString(cursor.getColumnIndex("detail_a"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            LogUtils.e(TAG, " query savaSqlite is null:" + e);
        } finally {
            cursor.close();
            writableDatabase.close();
        }
    }
    return url;
}
/**
 * 更新数据库表中某条数据
 */
public void updataHasRead(String detialUrl) {
    if (!detialUrl.equals(queryDate(detialUrl))) {//先查询 再去更新已读
        return;
    }
    synchronized (mySqlitehelper) {
        if (!writableDatabase.isOpen()) {
            writableDatabase = mySqlitehelper.getWritableDatabase();
        }
        try {
            ContentValues values = new ContentValues();
            values.put("isRead", "Y");
            int isRead = writableDatabase.update("NewsShow", values, "detail_a=?", new String[]{detialUrl});
        } catch (Exception e) {
            e.printStackTrace();
            LogUtils.e(TAG, "updataHasRead :" + e);
        } finally {
            writableDatabase.close();
        }
    }
}