Android下的SQLite数据库的相关操作及AndroidTestCase测试

时间：2022-04-21 15:28:20

一：创建数据库

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);

	}

}

相关文章