Android开发-数据库代码编写
数据库代码主要是查找
package com.example.Utils.database; import android.annotation.SuppressLint; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import com.example.Utils.fragment.FloatUtils; import java.util.ArrayList; import java.util.List; /* * 负责管理数据库的类 * 主要对于表当中的内容进行操作,增删改查 * */ public class DBManager { private static SQLiteDatabase db; /* 初始化数据库对象*/ public static void initDB(Context context){ DBOpenHelper helper = new DBOpenHelper(context); //得到帮助类对象 db = helper.getWritableDatabase(); //得到数据库对象 } /** * 读取数据库当中的数据,写入内存集合里 * kind :表示收入或者支出 * */ public static List<TypeBean> getTypeList(int kind){ List<TypeBean>list = new ArrayList<>(); //读取typetb表当中的数据 String sql = "select * from typetb where kind = "+kind; Cursor cursor = db.rawQuery(sql, null); // 循环读取游标内容,存储到对象当中 while (cursor.moveToNext()) { @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename")); @SuppressLint("Range") int imageId = cursor.getInt(cursor.getColumnIndex("imageId")); @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId")); @SuppressLint("Range") int kind1 = cursor.getInt(cursor.getColumnIndex("kind")); @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id")); TypeBean typeBean = new TypeBean(id, typename, imageId, sImageId, kind); list.add(typeBean); } return list; } /* * 向记账表当中插入一条元素 * */ public static void insertItemToAccounttb(AccountBean bean){ ContentValues values = new ContentValues(); values.put("typename",bean.getTypename()); values.put("sImageId",bean.getsImageId()); values.put("beizhu",bean.getBeizhu()); values.put("money",bean.getMoney()); values.put("time",bean.getTime()); values.put("year",bean.getYear()); values.put("month",bean.getMonth()); values.put("day",bean.getDay()); values.put("kind",bean.getKind()); db.insert("accounttb",null,values); } public static List<AccountBean>getAccountListOneDayFromAccounttb(int year,int month,int day){ List<AccountBean>list = new ArrayList<>(); String sql = "select * from accounttb where year=? and month=? and day=? order by id desc"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + ""}); //遍历符合要求的每一行数据 while (cursor.moveToNext()) { @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id")); @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename")); @SuppressLint("Range") String beizhu = cursor.getString(cursor.getColumnIndex("beizhu")); @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex("time")); @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId")); @SuppressLint("Range") int kind = cursor.getInt(cursor.getColumnIndex("kind")); @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("money")); AccountBean accountBean = new AccountBean(id, typename, sImageId, beizhu, money, time, year, month, day, kind); list.add(accountBean); } return list; } public static List<AccountBean>getAccountListOneMonthFromAccounttb(int year,int month){ List<AccountBean>list = new ArrayList<>(); String sql = "select * from accounttb where year=? and month=? order by id desc"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + ""}); //遍历符合要求的每一行数据 while (cursor.moveToNext()) { @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id")); @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename")); @SuppressLint("Range") String beizhu = cursor.getString(cursor.getColumnIndex("beizhu")); @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex("time")); @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId")); @SuppressLint("Range") int kind = cursor.getInt(cursor.getColumnIndex("kind")); @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("money")); @SuppressLint("Range") int day = cursor.getInt(cursor.getColumnIndex("day")); AccountBean accountBean = new AccountBean(id, typename, sImageId, beizhu, money, time, year, month, day, kind); list.add(accountBean); } return list; } public static float getSumMoneyOneDay(int year,int month,int day,int kind){ float total = 0.0f; String sql = "select sum(money) from accounttb where year=? and month=? and day=? and kind=?"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + "", kind + ""}); // 遍历 if (cursor.moveToFirst()) { @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("sum(money)")); total = money; } return total; } public static float getSumMoneyOneMonth(int year,int month,int kind){ float total = 0.0f; String sql = "select sum(money) from accounttb where year=? and month=? and kind=?"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""}); // 遍历 if (cursor.moveToFirst()) { @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("sum(money)")); total = money; } return total; } public static int getCountItemOneMonth(int year,int month,int kind){ int total = 0; String sql = "select count(money) from accounttb where year=? and month=? and kind=?"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""}); if (cursor.moveToFirst()) { @SuppressLint("Range") int count = cursor.getInt(cursor.getColumnIndex("count(money)")); total = count; } return total; } public static float getSumMoneyOneYear(int year,int kind){ float total = 0.0f; String sql = "select sum(money) from accounttb where year=? and kind=?"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", kind + ""}); // 遍历 if (cursor.moveToFirst()) { @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("sum(money)")); total = money; } return total; } public static int deleteItemFromAccounttbById(int id){ int i = db.delete("accounttb", "id=?", new String[]{id + ""}); return i; } public static List<AccountBean>getAccountListByRemarkFromAccounttb(String beizhu){ List<AccountBean>list = new ArrayList<>(); String sql = "select * from accounttb where beizhu like '%"+beizhu+"%'"; Cursor cursor = db.rawQuery(sql, null); while (cursor.moveToNext()) { @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id")); @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename")); @SuppressLint("Range") String bz = cursor.getString(cursor.getColumnIndex("beizhu")); @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex("time")); @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId")); @SuppressLint("Range") int kind = cursor.getInt(cursor.getColumnIndex("kind")); @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("money")); @SuppressLint("Range") int year = cursor.getInt(cursor.getColumnIndex("year")); @SuppressLint("Range") int month = cursor.getInt(cursor.getColumnIndex("month")); @SuppressLint("Range") int day = cursor.getInt(cursor.getColumnIndex("day")); AccountBean accountBean = new AccountBean(id, typename, sImageId, bz, money, time, year, month, day, kind); list.add(accountBean); } return list; } public static List<Integer>getYearListFromAccounttb(){ List<Integer>list = new ArrayList<>(); String sql = "select distinct(year) from accounttb order by year asc"; Cursor cursor = db.rawQuery(sql, null); while (cursor.moveToNext()) { @SuppressLint("Range") int year = cursor.getInt(cursor.getColumnIndex("year")); list.add(year); } return list; } public static void deleteAllAccount(){ String sql = "delete from accounttb"; db.execSQL(sql); } public static List<ChartItemBean>getChartListFromAccounttb(int year, int month, int kind){ List<ChartItemBean>list = new ArrayList<>(); float sumMoneyOneMonth = getSumMoneyOneMonth(year, month, kind); //求出支出或者收入总钱数 String sql = "select typename,sImageId,sum(money)as total from accounttb where year=? and month=? and kind=? group by typename " + "order by total desc"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""}); while (cursor.moveToNext()) { @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId")); @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename")); @SuppressLint("Range") float total = cursor.getFloat(cursor.getColumnIndex("total")); //计算所占百分比 total /sumMonth float ratio = FloatUtils.div(total,sumMoneyOneMonth); ChartItemBean bean = new ChartItemBean(sImageId, typename, ratio, total); list.add(bean); } return list; } public static float getMaxMoneyOneDayInMonth(int year,int month,int kind){ String sql = "select sum(money) from accounttb where year=? and month=? and kind=? group by day order by sum(money) desc"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""}); if (cursor.moveToFirst()) { @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("sum(money)")); return money; } return 0; } public static List<BarChartItemBean>getSumMoneyOneDayInMonth(int year, int month, int kind){ String sql = "select day,sum(money) from accounttb where year=? and month=? and kind=? group by day"; Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""}); List<BarChartItemBean>list = new ArrayList<>(); while (cursor.moveToNext()) { @SuppressLint("Range") int day = cursor.getInt(cursor.getColumnIndex("day")); @SuppressLint("Range") float smoney = cursor.getFloat(cursor.getColumnIndex("sum(money)")); BarChartItemBean itemBean = new BarChartItemBean(year, month, day, smoney); list.add(itemBean); } return list; } }