今天做了一些关于数据库的内容。
根据app功能，需要进行数据存储的无非三个方面：用户的个人信息、该用户的收入情况和支出情况，下面进行具体论述。
首先是用户的个人信息，初步设计账号密码功能，后续可能添加头像等。
然后是收入情况，可以先设计一个收入类型的列表，记录所有的收入类别，例如工资，奖金等，目的是为之后用户选择添加收入时提供添加选项。
然后是个人的收入记录，列表项有种类Category，金额Money，备注remark，日期date等。
支出状况类似。
首先定义DatabaseHelper.java类建立数据库。
package com.example.thorineaccount.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import com.example.thorineaccount.R; import java.text.SimpleDateFormat; import java.util.Date; public class DatabaseHelper extends SQLiteOpenHelper { private static final String DATABASE_NAME = "account.db"; private static final int DATABASE_VERSION = 1; public DatabaseHelper(Context context) {//修改构造方法 super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) {//启动程序时第一次创建，若程序已安装，需要先卸载 // 建表 //收入类别 String sql = "CREATE table accountincometype (id integer primary key autoincrement,category text,icon integer)"; db.execSQL(sql); //收入明细表(id,类别，金额，备注，日期时间) sql = "CREATE table accountincome (id integer primary key autoincrement,category text,"+ "money double,remark text,date text)"; db.execSQL(sql); //支出类别 sql = "CREATE table accountoutlaytype (id integer primary key autoincrement,category text,icon integer)"; db.execSQL(sql); //支出明细表(id,类别，金额，备注，日期时间) sql = "CREATE table accountoutlay (id integer primary key autoincrement,category text,"+ "money double,remark text,date text)"; db.execSQL(sql); //初始化的数据 initData(db); } //自动增长的列表，不需要给值；某个字段不想给值，不出现在表名后的列表中 private void initData(SQLiteDatabase db) { //收入类别 String sql = String.format("insert into accountincometype(category,icon) values(‘工资‘,%d)", R.drawable.fund_icon); db.execSQL(sql); sql = String.format("insert into accountincometype(category,icon) values(‘奖金‘,%d)", R.drawable.insurance_icon); db.execSQL(sql); sql = String.format("insert into accountincometype(category,icon) values(‘兼职收入‘,%d)", R.drawable.baby_icon); db.execSQL(sql); //支出类别 sql = String.format("insert into accountoutlaytype(category,icon) values(‘交通‘,%d)", R.drawable.traffic_icon); db.execSQL(sql); sql = String.format("insert into accountoutlaytype(category,icon) values(‘食物‘,%d)", R.drawable.breakfast_icon); db.execSQL(sql); sql = String.format("insert into accountoutlaytype(category,icon) values(‘图书‘,%d)", R.drawable.book_icon); db.execSQL(sql); sql = String.format("insert into accountoutlaytype(category,icon) values(‘电影‘,%d)", R.drawable.film_icon); db.execSQL(sql); sql = String.format("insert into accountoutlaytype(category,icon) values(‘房租‘,%d)", R.drawable.housing_loan_icon); db.execSQL(sql); sql = String.format("insert into accountoutlaytype(category,icon) values(‘运动‘,%d)", R.drawable.sport_icon); db.execSQL(sql); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String currentDate = sdf.format(new Date()); //收入明细 sql = "insert into accountincome(category,money,date) values(‘工资‘,10000,‘"+currentDate+"‘)"; db.execSQL(sql); sql = "insert into accountincome(category,money,date) values(‘奖金‘,1000,‘"+currentDate+"‘)"; db.execSQL(sql); //支出明细 sql = "insert into accountoutlay(category,money,date) values(‘交通‘,100,‘"+currentDate+"‘)"; db.execSQL(sql); sql = "insert into accountoutlay(category,money,date) values(‘食物‘,200,‘"+currentDate+"‘)"; db.execSQL(sql); sql = "insert into accountoutlay(category,money,date) values(‘图书‘,150,‘"+currentDate+"‘)"; db.execSQL(sql); sql = "insert into accountoutlay(category,money,date) values(‘电影‘,100,‘"+currentDate+"‘)"; db.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }
收入数据表：accountincome
收入种类表：accountincometype
支出数据表：accountoutlay
支出种类表：accountoutlaytype
即完成数据表的基本创建。
编写AccountDao.java 文件
package com.example.thorineaccount.db; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import com.example.thorineaccount.entity.AccountCategory; import com.example.thorineaccount.entity.AccountItem; import java.util.ArrayList; import java.util.List; public class AccountDao { private DatabaseHelper helper; private SQLiteDatabase db; public AccountDao(Context context){ //创建数据库 helper = new DatabaseHelper(context); db = helper.getWritableDatabase(); } //收入类型 public List<AccountCategory> getIncomeType(){ List<AccountCategory> result = new ArrayList<AccountCategory>(); String sql = "select id,category,icon from AccountIncomeType"; Cursor cursor = db.rawQuery(sql, null); while (cursor.moveToNext()){ int id = cursor.getInt(cursor.getColumnIndex("id")); String category = cursor.getString(cursor.getColumnIndex("category")); int icon = cursor.getInt(cursor.getColumnIndex("icon")); AccountCategory c = new AccountCategory(id,category,icon); result.add(c); } cursor.close(); return result; } //支出类型 public List<AccountCategory> getOutlayType(){ ArrayList<AccountCategory> result = new ArrayList<AccountCategory>(); String sql = "select id,category,icon from AccountOutlayType"; Cursor cursor = db.rawQuery(sql, null); while (cursor.moveToNext()){ int id = cursor.getInt(cursor.getColumnIndex("id")); String category = cursor.getString(cursor.getColumnIndex("category")); int icon = cursor.getInt(cursor.getColumnIndex("icon")); AccountCategory c = new AccountCategory(id,category,icon); result.add(c); } cursor.close(); return result; } public List<AccountItem> getIncomeList(){ ArrayList<AccountItem> result = new ArrayList<>(); Cursor cursor = db.query("AccountIncome",null,null,null,null,null,null); while (cursor.moveToNext()){//依次读取，将每次读取的对象加入集合中 AccountItem item = new AccountItem(); item.setId(cursor.getInt(cursor.getColumnIndex("id"))); item.setCategory(cursor.getString(cursor.getColumnIndex("category"))); item.setMoney(cursor.getDouble(cursor.getColumnIndex("money"))); item.setDate(cursor.getString(cursor.getColumnIndex("date"))); item.setRemark(cursor.getString(cursor.getColumnIndex("remark"))); result.add(item); } cursor.close(); return result; } //支出类型 public List<AccountItem> getOutlayList(){ ArrayList<AccountItem> result = new ArrayList<AccountItem>(); String sql = "select id,category,money,remark,date from AccountOutlay"; Cursor cursor = db.rawQuery(sql, null); while (cursor.moveToNext()){ AccountItem item = new AccountItem(); item.setId(cursor.getInt(cursor.getColumnIndex("id"))); item.setCategory(cursor.getString(cursor.getColumnIndex("category"))); item.setMoney(cursor.getDouble(cursor.getColumnIndex("money"))); item.setDate(cursor.getString(cursor.getColumnIndex("date"))); item.setRemark(cursor.getString(cursor.getColumnIndex("remark"))); result.add(item); } cursor.close(); return result; } public void addIncome(AccountItem item){ db.beginTransaction();//开启事务 try{ db.execSQL("INSERT INTO AccountIncome(id,category,money,date,remark) VALUES(null,?,?,?,?)",new Object[]{item.getCategory(),item.getMoney(),item.getDate(), item.getRemark()}); db.setTransactionSuccessful(); }finally { db.endTransaction();//结束 } } //添加支出 public void addOutlay(AccountItem item) { db.beginTransaction(); try { db.execSQL("INSERT INTO AccountOutlay(id,category,money,date,remark) VALUES(null,?,?,?,?)", new Object[]{item.getCategory(), item.getMoney(),item.getDate(),item.getRemark()}); db.setTransactionSuccessful(); } finally { db.endTransaction(); } } //删除收入 public void deleteIncome(long id) { String sql = "delete from AccountIncome where id="+id; db.beginTransaction(); try { db.execSQL(sql); db.setTransactionSuccessful(); } finally { db.endTransaction(); } } //添加收入类型 public void addIncomeCategory(String category,int icon) { db.beginTransaction(); try { db.execSQL("INSERT INTO AccountIncomeType(id,category,icon) VALUES(null,?,?)", new Object[]{category,icon}); db.setTransactionSuccessful(); } finally { db.endTransaction(); } } //添加支出3类型 public void addOutlayCategory(String category,int icon) { db.beginTransaction(); //开始事务 try { db.execSQL("INSERT INTO AccountOutlayType(id,category,icon) VALUES(null,?,?)", new Object[]{category,icon}); db.setTransactionSuccessful(); //设置事务成功完成 } finally { db.endTransaction(); //结束事务 } } }
完成增删改查等基本操作。