Android数据库在实战中是非常实用的，这里记录一下基本使用。
1、DbOpenHelper
/**
* 通过继承SqliteOpenHelper来创建一个数据库
*
*/
public class DbOpenHelper extends SQLiteOpenHelper {
private static String DATABASE_NAME = "ecash.db";
private static int DATABASE_VERSION = 1;
/**
* (Context context, String name, CursorFactory factory,int version)
*/
public DbOpenHelper(Context context) {
/**
* @param context 上下文对象
* @param name 数据库名称 secb.db
* @param factory 游标工厂
* @param version 数据库版本
*/
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/**
* 数据库第一次被使用时创建数据库
*
* @param db 操作数据库的
*/
public void onCreate(SQLiteDatabase db) {
//执行有更新行为的sql语句
db.execSQL("CREATE Table balance (id integer primary key autoincrement, order_id varchar(50), amount varchar(20), trade_status varchar(20), flag integer, name varchar(50), " +
"trade_time timestamp, merchant_no varchar(50), terminal_no varchar(50), batch_no varchar(20))");
}
/**
* 数据库版本发生改变时才会被调用,数据库在升级时才会被调用;
*
* @param db 操作数据库
* @param oldVersion 旧版本
* @param newVersion 新版本
*/
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("drop table if exists balance");
onCreate(db);
}
}
2、ECashService
public class ECashService {
private DbOpenHelper dbOpenHelper;
public ECashService(Context context) {
dbOpenHelper = new DbOpenHelper(context);
}
/**
* 添加ECash
*
* @param eCash
*/
public long addECash(ECash eCash) {
// 对读和写操作的方法
// 如果当我们二次调用这个数据库方法,他们调用的是同一个数据库对象,在这里的方法创建的数据调用对象是用的同一个对象
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("order_id", eCash.getOrder_id());
values.put("amount", eCash.getAmount());
values.put("trade_status", eCash.getTrade_status());
values.put("flag", eCash.getFlag());
values.put("name", eCash.getName());
values.put("trade_time", eCash.getTrade_time());
values.put("merchant_no", eCash.getMerchant_no());
values.put("terminal_no", eCash.getTerminal_no());
values.put("batch_no", eCash.getBatch_no());
long result = db.insert("balance", null, values);
db.close();
return result;
// db.execSQL("insert into balance(order_id,amount,trade_status,flag,name,trade_time,merchant_no,terminal_no,batch_no) values(?,?,?,?,?,?,?,?,?)", new Object[]
// {eCash.getOrder_id(), eCash.getAmount(), eCash.getTrade_status(), eCash.getFlag(), eCash.getName(), eCash.getTrade_time(), eCash.getMerchant_no(),
// eCash.getTerminal_no(), eCash.getBatch_no()});
}
/**
* 修改ECash
*
* @param eCash
*/
public long modifyECash(ECash eCash) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("flag", eCash.getFlag());
long result = db.update("balance", values, "order_id=?", new String[]{eCash.getOrder_id()});
db.close();
return result;
// db.execSQL("update balance set flag=? where order_id=?", new Object[]
// {eCash.getFlag(), eCash.getOrder_id()});
}
/**
* 删除ECash
*/
public void deleteECash(String order_id) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from balance where order_id=?", new Object[]
{order_id});
}
/**
* 根据ECash的Id查询ECash对象
*
* @return ECash
*/
public ECash findECash(String order) {
// 只对读的操作的方法
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
// Cursor游标的位置,默认是0,所有在操作时一定要先cursor.moveToFirst()一下,定位到第一条记录
Cursor cursor = db.query("balance", new String[]
{"order_id", "amount", "trade_status", "flag", "name", "trade_time", "merchant_no", "terminal_no", "batch_no"}, "order_id = ?", new String[]
{order}, null, null, null);
if (cursor.moveToFirst()) {
String order_id = cursor.getString(cursor.getColumnIndex("order_id"));
String amount = cursor.getString(cursor.getColumnIndex("amount"));
String trade_status = cursor.getString(cursor.getColumnIndex("trade_status"));
int flag = cursor.getInt(cursor.getColumnIndex("flag"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String trade_time = cursor.getString(cursor.getColumnIndex("trade_time"));
String merchant_no = cursor.getString(cursor.getColumnIndex("merchant_no"));
String terminal_no = cursor.getString(cursor.getColumnIndex("terminal_no"));
String batch_no = cursor.getString(cursor.getColumnIndex("batch_no"));
return new ECash(order_id, amount, flag, trade_status, name, trade_time, merchant_no, terminal_no, batch_no);
}
db.close();
cursor.close();
return null;
}
/**
* 返回ECash对象的集合
*
* @return List<ECash>
*/
public List<ECash> findPersonList() {
List<ECash> persons = new ArrayList<>();
// 只对读的操作的方法
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.query("balance", null, null, null, null, null, null, null);
while (cursor.moveToNext()) {
String order_id = cursor.getString(cursor.getColumnIndex("order_id"));
String amount = cursor.getString(cursor.getColumnIndex("amount"));
String trade_status = cursor.getString(cursor.getColumnIndex("trade_status"));
int flag = cursor.getInt(cursor.getColumnIndex("flag"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String trade_time = cursor.getString(cursor.getColumnIndex("trade_time"));
String merchant_no = cursor.getString(cursor.getColumnIndex("merchant_no"));
String terminal_no = cursor.getString(cursor.getColumnIndex("terminal_no"));
String batch_no = cursor.getString(cursor.getColumnIndex("batch_no"));
persons.add(new ECash(order_id, amount, flag, trade_status, name, trade_time, merchant_no, terminal_no, batch_no));
}
cursor.close();
return persons;
}
/**
* 返回ECash的记录总个数
*
* @return
*/
public Long getCount() {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(0) from balance ", null);
// 这里必定有一条记录.所有不用判断,直接移到第一条.
cursor.moveToFirst();
// 这里只有一个字段时候 返回
cursor.close();
return cursor.getLong(0);
}
}
3、具体使用
public class ECashActivity extends IppsActivity {
private DbOpenHelper db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_ecash);
db = new DbOpenHelper(this);
// 第一次调用该方法会调用数据库
db.getWritableDatabase();
if(saveECash()>0){
//成功
}
}
/**
* 添加ECash
*/
public long saveECash(String amount) {
ECashService db = new ECashService(this);
eCash = new ECash();
eCash.setOrder_id(new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()));
eCash.setAmount(amount);
eCash.setFlag(0); //0表示未撤销
eCash.setTrade_status("未结");
eCash.setTrade_time(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
eCash.setMerchant_no((String) IppsApplication.map.get("mchntNo"));
eCash.setTerminal_no((String) IppsApplication.map.get("phone"));
eCash.setBatch_no(getBeachNo());
return db.addECash(eCash);
}
}
