Android记账本开发（数据库开发） _数据库

天下之事常成于困约，而败于奢靡。这篇文章主要讲述Android记账本开发：数据库开发相关的知识，希望能为你提供帮助。
今天做了一些关于数据库的内容。
根据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
【Android记账本开发（数据库开发）】

文章图片

支出种类表：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(); //结束事务 } } }

完成增删改查等基本操作。