对android内置的sqlLite数据库进行增、删、改、查操作

1.创建数据库

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

public class DBOpenHelper extends SQLiteOpenHelper {

	public DBOpenHelper(Context context) {
		// 第一个参数表示context对象，第二个参数表示数据库的名称，第三个数表示游标对象，可以传入NULL，第四个参数表示数据库的版本号
		super(context, "kafei.db", null, 2);

	}

	/**
	 * onCreate方法是在数据库第一次被创建的时候调用
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("create table user(id integer primary key autoincrement,name varchar(20))");
	}

	/**
	 * 在数据库版本号发生改变的时候调用
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("alter table user add age integer");// 为user表增加一列
	}
}

测试：
        DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext());
        dbOpenHelper.getWritableDatabase();// 调用getWritableDatabase()方法就会自动创建数据库

2.利用SQLiteDatabase的execSQL方法手动编写SQL语句对数据库进行增删改查操作：

import java.util.ArrayList;
import java.util.List;

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

import cn.kafei.pojo.User;

public class UserService {
	private DBOpenHelper dbOpenHelper;

	public UserService(Context context) {
		this.dbOpenHelper = new DBOpenHelper(context);
	}

	/**
	 * 添加记录
	 * 
	 * @param user
	 */
	public void save(User user) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("insert into user(name,age)values(?,?)",
				new Object[] { user.getName(), user.getAge() });
	}

	/**
	 * 删除记录
	 * 
	 * @param id
	 *            记录ID
	 */
	public void delete(Integer id) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("delete from user where id=?", new Object[] { id });
	}

	/**
	 * 更新记录
	 * 
	 * @param user
	 */
	public void update(User user) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("update user set name=?,age=? where id=?", new Object[] {
				user.getName(), user.getAge(), user.getId() });
	}

	/**
	 * 查找记录
	 * 
	 * @param id
	 *            记录ID
	 */
	public User find(Integer id) {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from user where id=?",
				new String[] { id.toString() });
		if (cursor.moveToFirst()) {
			int userid = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			int age = cursor.getInt(cursor.getColumnIndex("age"));
			return new User(userid, name, age);
		}
		cursor.close();
		return null;
	}

	/**
	 * 分页获取记录
	 * 
	 * @param offset
	 *            跳过前面多少条记录
	 * @param maxResult
	 *            每页获取多少条记录
	 * @return
	 */
	public List<User> getScrollData(int offset, int maxResult) {
		List<User> users = new ArrayList<User>();
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(
				"select * from user order by id asc limit ?,?", new String[] {
						String.valueOf(offset), String.valueOf(maxResult) });
		while (cursor.moveToNext()) {
			int userid = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			int age = cursor.getInt(cursor.getColumnIndex("age"));
			users.add(new User(userid, name, age));
		}
		cursor.close();
		return users;
	}

	/**
	 * 获取记录总数
	 * 
	 * @return
	 */
	public long getCount() {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from user", null);
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		cursor.close();
		return result;
	}
}

3.利用SQLiteDatabase内置提供的insert、delete、update、find进行操作数据库

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import cn.kafei.pojo.User;

public class OtherService {
	private DBOpenHelper dbOpenHelper;

	public OtherService(Context context) {
		this.dbOpenHelper = new DBOpenHelper(context);
	}

	/**
	 * 添加记录
	 * 
	 * @param user
	 */
	public void save(User user) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", user.getName());
		values.put("age", user.getAge());
		// 第一个参数表的名称,
		// 第二个参数:空置字段，假如指定name为空值字段,则组拼的SQL语句为insert into user(name)
		// values(null),最终目的是为了保证SQL语句的正常执行
		// 在第三个参数为null或者为空集合的时候使用
		// 第三个参数:表示要添加的值，
		db.insert("user", null, values);
		// db.execSQL("insert into user(name,age)values(?,?)",new
		// Object[]{user.getName(),user.getAge()});
	}

	/**
	 * 删除记录
	 * 
	 * @param id
	 *            记录ID
	 */
	public void delete(Integer id) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.delete("user", "id=?", new String[] { id.toString() });
		// db.execSQL("delete from user where id=?",new Object[]{id});
	}

	/**
	 * 更新记录
	 * 
	 * @param user
	 */
	public void update(User user) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", user.getName());
		values.put("age", user.getAge());
		// 第二个参数表示要更新的值
		db.update("user", values, "id=?", new String[] { user.getId()
				.toString() });
		// db.execSQL("update user set name=?,age=? where id=?",new
		// Object[]{user.getName(),user.getAge(),user.getId()});
	}

	/**
	 * 查找记录
	 * 
	 * @param id
	 *            记录ID
	 */
	public User find(Integer id) {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		/**
		 * 第一个参数：要查询的表 第二个参数：要查询的列,如果要查询所有的列，就传入Null 第三个参数:查询条件 第四个参数：查询条件的值
		 * 第五个参数：分组条件 第六个参数：having 第七个参数：排序
		 */
		Cursor cursor = db.query("user", new String[] { "id", "name", "age" },
				"id=?", new String[] { id.toString() }, null, null, null);

		// Cursor cursor=db.rawQuery("select * from user where id=?", new
		// String[]{id.toString()});
		if (cursor.moveToFirst()) {
			int userid = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			int age = cursor.getInt(cursor.getColumnIndex("age"));
			return new User(userid, name, age);
		}
		cursor.close();
		return null;
	}

	/**
	 * 分页获取记录
	 * 
	 * @param offset
	 *            跳过前面多少条记录
	 * @param maxResult
	 *            每页获取多少条记录
	 * @return
	 */
	public List<User> getScrollData(int offset, int maxResult) {
		List<User> users = new ArrayList<User>();
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.query("user", null, null, null, null, null,
				"id asc", offset + "," + maxResult);

		// Cursor
		// cursor=db.rawQuery("select * from user order by id asc limit ?,?",
		// new String[]{String.valueOf(offset),String.valueOf(maxResult)});
		while (cursor.moveToNext()) {
			int userid = cursor.getInt(cursor.getColumnIndex("id"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			int age = cursor.getInt(cursor.getColumnIndex("age"));
			users.add(new User(userid, name, age));
		}
		cursor.close();
		return users;
	}

	/**
	 * 获取记录总数
	 * 
	 * @return
	 */
	public long getCount() {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.query("user", new String[] { "count(*)" }, null,
				null, null, null, null);
		// Cursor cursor=db.rawQuery("select count(*) from user", null);
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		cursor.close();
		return result;
	}
}

测试

import java.util.List;

import cn.kafei.pojo.User;
import cn.kafei.service.DBOpenHelper;
import cn.kafei.service.OtherService;
import android.test.AndroidTestCase;
import android.util.Log;

public class OtherServiceTest extends AndroidTestCase {

	private static final String TAG="UserServiceTest";
	public void testCreateDB() throws Exception {
		DBOpenHelper dbOpenHelper = new DBOpenHelper(getContext());
		dbOpenHelper.getWritableDatabase();// 调用getWritableDatabase()方法就会自动创建数据库
	}

	public void testSave() throws Exception {
		OtherService service=new OtherService(getContext());
//		for (int i = 0; i <20; i++) {
			service.save(new User("lht", 28));
//		}
	}

	public void testDelete() throws Exception {
		OtherService service=new OtherService(getContext());
		service.delete(1);
	}

	public void testUpdate() throws Exception {
		OtherService service=new OtherService(getContext());
		User user=new User();
		user.setAge(100);
		user.setId(21);
		service.update(user);
	}

	public void testFind() throws Exception {
		OtherService service=new OtherService(getContext());
		User user=service.find(1);
		Log.i(TAG,user.toString());
	}

	public void testScrollData() throws Exception {
		OtherService service=new OtherService(getContext());
		//第一个参数表示页数，第二个参数表示每页显示的记录数
		List<User> users=service.getScrollData(5, 5);
		for (User user : users) {
			Log.i(TAG,user.toString());
		}
	}

	public void testCount() throws Exception {
		OtherService service=new OtherService(getContext());
		Log.i(TAG, String.valueOf(service.getCount()));
	}
}

4.SQLLite中的事务
  

 public void updateMoney(){
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        db.beginTransaction();//开启事务
        try{
        db.execSQL("update user set amount=amount-10 where id=21");
        db.execSQL("update user set amount=amount+10 where id=22");
        db.setTransactionSuccessful();//设置事务的标志为true
        }finally{
        //结束事务,有两种情况，一种是commit,一种是rollback,事务的提交或回滚是由事务的标志决定的,默认情况下，事务的标志为false，如果事务的标志为true则会提交否则就会回滚
        db.endTransaction();
        }
    }