项目开发第五天

今天完善项目的数据库操作：

定义三个数据库，分别表示收入、支出和添加类别。

package net.hnjdzy.tinyaccount.db;

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

import net.hnjdzy.tinyaccount.entity.AccountCategory;
import net.hnjdzy.tinyaccount.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);
        //因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, mFactory);
        //所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
        db = helper.getWritableDatabase();
    }

    //收入类型
    public List<AccountCategory> getIncomeType(){
        ArrayList<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<AccountItem>();
        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();    //结束事务
        }
    }
}

package net.hnjdzy.tinyaccount.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import net.hnjdzy.tinyaccount.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 arg0, int arg1, int arg2) {
        // TODO Auto-generated method stub

    }

}