Android停的SQLite数据库的相关操作及AndroidTestCase测试
Android下的SQLite数据库的相关操作及AndroidTestCase测试
一:创建数据库
package com.itcode.mysqlite; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; /** * 继承SQLiteOpenHelper,创建数据库 * @author Administrator * */ public class MySQLiteOpenHelper extends SQLiteOpenHelper{ //一:声明构造函数 /** * @param context 当前应用的环境,用来确定数据库目录 * @param name 数据库文件的名字 * @param factory 游标工厂,用来创建结果集对象 * @param version 数据库版本 从1开始 */ public MySQLiteOpenHelper(Context context) { super(context, "itcode.db", null, 1); } //二:重写onCreate方法 数据库被创建时调用 public void onCreate(SQLiteDatabase db) { System.out.println("MySQLiteOpenHelper-->onCreate 被调用!"); //1.创建数据库 db.execSQL("create table account(_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),balance INTEGER)"); // db.execSQL("CREATE TABLE account(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))"); // 执行一条SQL语句 } //三:重写onUpgreade方法,数据库时调用 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // System.out.println("MySQLiteOpenHelper-->onUpgrade 被调用"); // db.execSQL("alter table account ADD balance integer"); // db.execSQL("ALTER TABLE account ADD balance INTEGER"); } }
二:对数据库进行CRUD操作
package com.itcode.mysqlite;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
public class AccountDao {
//一:创建AccountDao时依据当前context创建数据库
private MySQLiteOpenHelper mySQLiteOpenHelper;
public AccountDao(Context context){
mySQLiteOpenHelper = new MySQLiteOpenHelper(context);
}
//二:增:传进来一个Account,将此对象加入数据库
public int add(Account a){
//1.获取相应的(可写的)数据库
SQLiteDatabase writableDatabase = mySQLiteOpenHelper.getWritableDatabase();
//2.执行语句
writableDatabase.execSQL("insert into account(name,balance) values(?,?)", new Object[]{a.getName(),a.getBalance()});
//3.获取插入的数据集:
Cursor rawQuery = writableDatabase.rawQuery("select _id from account order by _id desc limit 1", null);
rawQuery.moveToNext();
int id = rawQuery.getInt(rawQuery.getColumnIndex("_id"));
//4.关闭数据库
return id;
}
//三:删:根据id删除数据库中相应的数据项
public void delete(int id){
//1.获取相应的(可写的)数据库
SQLiteDatabase writableDatabase = mySQLiteOpenHelper.getWritableDatabase();
//2.执行删除语句
writableDatabase.execSQL("Delete from account where _id = ?",new Object[]{id});
//3.关闭数据库
writableDatabase.close();
}
//四:改:传进来一个Account,根据Account的对象的id修改name 和balance
public void update(Account a){
//1.获取相应的(可写的)数据库
SQLiteDatabase writableDatabase = mySQLiteOpenHelper.getWritableDatabase();
//2.执行更新语句
writableDatabase.execSQL("update account set name=?,balance = ? where _id = ?",new Object[]{a.getName(),a.getBalance(),a.getId()});
//3.关闭数据库
writableDatabase.close();
}
//五:查:根据id查询数据库
public Account query(int id){
//1.获取相应的(可读的)数据库
SQLiteDatabase readableDatabase = mySQLiteOpenHelper.getReadableDatabase();
//2.执行更新语句
Cursor rawQuery = readableDatabase.rawQuery("select * from account where _id=?", new String[]{String.valueOf(id)});
//3.将查询到的数据赋值给Account对象
Account a = null;
while(rawQuery.moveToNext()){
Integer balance = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("balance")));
String name = rawQuery.getString(rawQuery.getColumnIndex("name"));
a = new Account(id,name,balance);
}
//3.关闭结果集与数据库
rawQuery.close();
readableDatabase.close();
return a;
}
//六:查询所有,将查询到的所有数据放在list集合中返回
public List<Account> queryAll(){
List<Account> list = new ArrayList<Account>();
Account a = null;
//1.获取相应的(可读的)数据库
SQLiteDatabase readableDatabase = mySQLiteOpenHelper.getReadableDatabase();
Cursor rawQuery = readableDatabase.rawQuery("Select * from account",null);
while(rawQuery.moveToNext()){
Integer id = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("_id")));
String name = rawQuery.getString(rawQuery.getColumnIndex("name"));
Integer balance = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("balance")));
a = new Account(id,name,balance);
list.add(a);
}
//3.关闭资源
rawQuery.close();
readableDatabase.close();
return list;
}
//七:分页查询:传入要查询第几页,每页显示几条数据,将查询到的数据放在list集合中返回
public List<Account> queryPage(int pageNum,int pageSize){
List<Account> list = new ArrayList<Account>();
Account a = null;
String index = String.valueOf((pageNum-1)*pageSize);
String count = String.valueOf(pageSize);
//1.获取相应的(可读的)数据库
SQLiteDatabase readableDatabase = mySQLiteOpenHelper.getReadableDatabase();
//2.执行相应的查询语句
Cursor rawQuery = readableDatabase.rawQuery("select * from account limit ?,?", new String[]{index,count});
while(rawQuery.moveToNext()){
Integer id = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("_id")));
String name = rawQuery.getString(rawQuery.getColumnIndex("name"));
Integer balance = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("balance")));
a = new Account(id,name,balance);
list.add(a);
}
//3.关闭相应的资源
rawQuery.close();
readableDatabase.close();
return list;
}
//八:查询总记录条数
public int queryCount(){
//1.获取相应的(可读的)数据库
SQLiteDatabase readableDatabase = mySQLiteOpenHelper.getReadableDatabase();
//2.执行相应的语句
Cursor rawQuery = readableDatabase.rawQuery("select count(*) from account", null);
rawQuery.moveToNext();//不加此句可否?不能去掉!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
int count = rawQuery.getInt(0);
//3.关闭资源
rawQuery.close();
readableDatabase.close();
return count;
}
//九:事物管理 从一个Account对象向另一个Account对象汇款,传入参数,fromid,toId,moneySize
public void remit(int fromId,int toId,int moneySize){
SQLiteDatabase writableDatabase = null;
try {
//1.获取相应的(可写的)数据库
writableDatabase = mySQLiteOpenHelper.getWritableDatabase();
//2.开启事物
writableDatabase.beginTransaction();
//3.执行相应的语句
writableDatabase.execSQL("update account set balance=balance-? where _id = ?", new Object[]{moneySize,fromId});
writableDatabase.execSQL("update account set balance=balance+? where _id = ?",new Object[]{moneySize,toId});
//4.设置成功标记
writableDatabase.setTransactionSuccessful();
//5.关闭资源
writableDatabase.close();
} catch (Exception e) {
//6.结束事务,会把最后一次成功标记之前的事务提交
writableDatabase.endTransaction();
e.printStackTrace();
}
}
}
三:对CRUD的方法的测试:
package com.itcode.mysqlite; import java.util.List; import java.util.Random; import android.database.sqlite.SQLiteDatabase; import android.test.AndroidTestCase; public class SQLiteTest extends AndroidTestCase{ //1.测试创建数据库的方法 public void testCreateDB(){ //1.创建数据库 MySQLiteOpenHelper helper = new MySQLiteOpenHelper(getContext()); //2.得到相应的数据库 SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("INSERT INTO account(_id,name, balance) VALUES(1,'fuck', 10000)"); } //2.测试添加数据的方法 public void testAdd(){ AccountDao dao = new AccountDao(getContext());// Context android.test.AndroidTestCase.getContext() for(int i=1;i<100;i++){ dao.add(new Account("Test" + i,new Random().nextInt(10000))); } } //3.测试删除数据的方法 public void testDelete(){ AccountDao dao = new AccountDao(getContext()); for(int i=100;i<200;i++){ dao.delete(i); } } //4.测试修改数据的方法 public void testUpdate(){ AccountDao dao = new AccountDao(getContext()); Account a = new Account(1,"fuck you!!!~",999); dao.update(a); } //5.测试查询数据的方法,根据id查询数据库 public void testQuery(){ AccountDao dao = new AccountDao(getContext()); System.out.println(dao.query(1)); System.out.println(dao.query(3)); System.out.println(dao.query(4)); } //6.测试查询所有的方法 public void testQueryAll(){ AccountDao dao = new AccountDao(getContext()); List<Account> list =null; list = dao.queryAll(); for (Account account : list) { System.out.println(account); } } //7.测试分布查询的方法 public void testQueryPage(){ AccountDao dao = new AccountDao(getContext()); List<Account> list = null; list = dao.queryPage(2, 10); for (Account account : list) { System.out.println(account); } } //8.测试查询总记录条数的方法 public void testQueryCount(){ AccountDao dao = new AccountDao(getContext()); int count = dao.queryCount(); System.out.println(count); } }