Android中对数据库进行CRUD操作

一、首先新建Adnroid项目 然后就是新建 数据库文件

创建DBSQLiteOpenHelper类 并让它继承 SQLiteOpenHelper

 

public class DBSQLiteOpenHelper extends SQLiteOpenHelper {

	// 数据库的名称
	private static final String name = "CSDN";

	// 数据库的版本
	private static final int version = 2;

	public DBSQLiteOpenHelper(Context context) {
		super(context, name, null, version);

		Log.v("DBSQLiteOpenHelper", "构造器.....");
	}

	// 当数据 库第一次创建的时候 执行的方法
	@Override
	public void onCreate(SQLiteDatabase db) {
		// execSQL来挨靠 sql语句
		db.execSQL("create table person(personid integer primary key autoincrement,name varchar(20),age integer)");

		Log.v("DBSQLiteOpenHelper", "onCreate...创建执行第一次。");
	}

	// 当版本发生变化则执行此方法
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL("alter table person add account integer");

		Log.v("DBSQLiteOpenHelper", "每次更新时都执行.....");
	}

}

-----------------------------------------------------------------------------------------------------

------接下来就是对数据库进行操作了----------------------------------------------------------------------

新建 domain包 中Person 类 其中字段如下：

public class Person {

private Integer id;
private String name;
private Integer age;
private Integer account;

生成 空构造器，带参数的构造器，和set/get 方法

为了方便测试 生成String

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

接下来就是实现PersonDao接口和PersonDaoImpl类了。

public interface PersonDao {
	
	//插入
	public boolean insert(ContentValues values);
	//更新
	public boolean update(ContentValues values,Integer id);
	//删除
	public boolean delete(Integer id);
	//查找所有
	public List<Person> findAll();
	//获取当前页信息
	public List<Person> getNowPageInfo(String[] selectionArgs,String order,String limit);

	//条件查找
	public List<Person> findByName( String[] selectionArgs);
	
	public Cursor findAlls();
	
	public Cursor findById(Integer id);

	public Cursor findByNames(String name);
}
 

 

--------------------------------------------------------------------------------------------------------

-------------------------------

 

public class PersonDaoImpl implements PersonDao {

	private DBSQLiteOpenHelper dbsqLiteOpenHelper;

	public PersonDaoImpl(Context context) {
		super();
		dbsqLiteOpenHelper = new DBSQLiteOpenHelper(context);
	}

	public boolean insert(ContentValues values) {
		SQLiteDatabase db = dbsqLiteOpenHelper.getWritableDatabase();
		boolean flag = false;

		if (db.isOpen()) {
			// 执行插入操作
			long l = db.insert("person", null, values);

			if (l > 0) {
				flag = true;
			}
			db.close();
		}
		return flag;
	}

	@Override
	public boolean update(ContentValues values, Integer id) {
		SQLiteDatabase db = dbsqLiteOpenHelper.getWritableDatabase();
		boolean flag = false;
		if (db.isOpen()) {
			int l = db.update("person", values, "id=?",
					new String[] { id + "" });
			if (l > 0) {
				flag = true;
			}
			db.close();
		}
		return flag;
	}

	@Override
	public boolean delete(Integer id) {
		SQLiteDatabase db = dbsqLiteOpenHelper.getWritableDatabase();
		boolean flag = false;
		if (db.isOpen()) {
			int l = db.delete("person", "id=?", new String[] { id + "" });

			if (l > 0) {
				flag = true;
			}
			db.close();
		}
		return flag;
	}

	@Override
	public List<Person> findAll() {
		SQLiteDatabase db = dbsqLiteOpenHelper.getWritableDatabase();
		List<Person> persons = new ArrayList<Person>();

		if (db.isOpen()) {
			Cursor cursor = db.query("person", new String[] { "id", "name",
					"phone" }, null, null, null, null, null);

			while (cursor.moveToNext()) {
				// 创建person对象
				Person person = new Person();
				// 设置person的属性
				person.setId(cursor.getInt(cursor.getColumnIndex("id")));
				person.setName(cursor.getString(cursor.getColumnIndex("name")));
				person.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
				// 添加到集合众
				persons.add(person);
			}
		}

		return persons;
	}

	@Override
	public List<Person> getNowPageInfo(String[] selectionArgs, String order,
			String limit) {
		SQLiteDatabase db = dbsqLiteOpenHelper.getWritableDatabase();
		List<Person> persons = new ArrayList<Person>();

		if (db.isOpen()) {
			Cursor cursor = db.query("person", new String[] { "id", "name",
					"phone" }, "name like ?", selectionArgs, null, null, order,
					limit);

			while (cursor.moveToNext()) {
				// 创建person对象
				Person person = new Person();
				// 设置person的属性
				person.setId(cursor.getInt(cursor.getColumnIndex("id")));
				person.setName(cursor.getString(cursor.getColumnIndex("name")));
				person.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
				// 添加到集合众
				persons.add(person);
			}

		}

		return persons;
	}

	@Override
	public List<Person> findByName(String[] selectionArgs) {
		SQLiteDatabase db = dbsqLiteOpenHelper.getWritableDatabase();
		List<Person> persons = new ArrayList<Person>();

		if (db.isOpen()) {
			// 执行查询
			Cursor cursor = db.query("person", new String[] { "id", "name",
					"phone" }, "name like ?", selectionArgs, null, null,
					"id desc");

			while (cursor.moveToNext()) {
				// 创建person对象
				Person person = new Person();
				// 设置person的属性
				person.setId(cursor.getInt(cursor.getColumnIndex("id")));
				person.setName(cursor.getString(cursor.getColumnIndex("name")));
				person.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
				// 添加到集合众
				persons.add(person);
			}
		}

		return persons;
	}

	@Override
	public Cursor findAlls() {
		SQLiteDatabase db = dbsqLiteOpenHelper.getReadableDatabase();

		if (db.isOpen()) {
			// 执行查询
			Cursor c = db.rawQuery("select id as _id,name,phone from person",
					null);

			return c;
		}

		return null;
	}

	@Override
	public Cursor findById(Integer id) {
		SQLiteDatabase db = dbsqLiteOpenHelper.getReadableDatabase();

		if (db.isOpen()) {
			// 执行查询
			Cursor c = db.rawQuery(
					"select id,name,phone from person where id=?",
					new String[] { id + "" });
			return c;
		}
		return null;
	}

	@Override
	public Cursor findByNames(String name) {
		SQLiteDatabase db = dbsqLiteOpenHelper.getReadableDatabase();

		if (db.isOpen()) {
			// 执行查询
			Cursor c = db.rawQuery(
					"select id,name,phone from person where name like ?",
					new String[] { name });
			return c;
		}
		return null;
	}

}

 

------------------------功能写好后，接下来就是对此方法 进行测试 了-----------------------------------------------------

第一步：首先在AndroidManifest.xml中加入下面红色代码:

<instrumentation
android:name="android.test.InstrumentationTestRunner"
android:targetPackage="com.example.lession05_db" >
</instrumentation>

<uses-library android:name="android.test.runner" />

第二步：就是写测试方法DBTest 继承AndroidTestCase

public class DBPersonTest extends AndroidTestCase {

	private static final String TAG = "DBPersonTest";

	// 创建Dao对象
	//private PersonDao personDao = new PersonDaoImpl();

	
	public void createDB() {
		DBSQLiteOpenHelper db = new DBSQLiteOpenHelper(this.getContext());
		@SuppressWarnings("unused")
		SQLiteDatabase sdb = db.getWritableDatabase();
	}

	public void insert() {
		PersonDao personDao = new PersonDaoImpl(getContext());

		for (int i = 1; i < 100; i++) {
			
			// 参数
			ContentValues values = new ContentValues();
			// 创建person实体对象
			Person person = new Person(null, "chrp" + i, "1503198558" + i);
			// 调用put方法存值
			values.put("name", person.getName());
			values.put("phone", person.getPhone());
			// 执行插入
			personDao.insert(values);
		}
	}

	public void update() {
		PersonDao personDao = new PersonDaoImpl(getContext());

		// 参数
		ContentValues values = new ContentValues();

		// 创建person实体对象
		Person person = new Person(1, "xxxrxxx", "15031985581");

		// 调用put方法存值
		values.put("name", person.getName());
		values.put("phone", person.getPhone());
		// 执行插入
		personDao.update(values, person.getId());
	}

	public void delete() {
		PersonDao personDao = new PersonDaoImpl(getContext());

		// 创建person实体对象
		Person person = new Person(1, "xxxhxxx", "15031985581");

		// 执行插入
		personDao.delete(person.getId());
	}

	public void findAll() {
		PersonDao personDao = new PersonDaoImpl(getContext());

		List<Person> persons = personDao.findAll();

		for (Person p : persons) {
			Log.v(TAG, p.toString());
		}
	}

	public void findByName() {
		PersonDao personDao = new PersonDaoImpl(getContext());

		List<Person> persons = personDao
				.findByName(new String[] { "%p1%" });

		for (Person p : persons) {
			Log.v(TAG, p.toString());
		}
	}

	public void getNowPageInfo() {
		PersonDao personDao = new PersonDaoImpl(getContext());

		List<Person> persons = personDao.getNowPageInfo(
				new String[] { "%p1%" }, "id desc", "0,5");

		for (Person p : persons) {
			Log.v(TAG, p.toString());
		}
	}

}