对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方法是在数据库第一次被创建的时候调用 */@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL("create table user(id integer primary key autoincrement,name varchar(20))");}/** * 在数据库版本号发生改变的时候调用 */@Overridepublic 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 getScrollData(int offset, int maxResult) {List users = new ArrayList();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 getScrollData(int offset, int maxResult) {List users = new ArrayList();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 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();        }    }

更多相关文章

随机推荐