Android之SQLite数据库创建和更新,以及增删改差操作 事务

SQLite数据库
1.SQLite数据库的特点
       手机自带的数据库, 不区分数据类型(除了主键), 语法和MySQL相同, 每个库是一个文件
2.创建库
     定义类继承SQLiteOpenHelper, 定义构造函数, 显式调用父类构造函数, 传入4个参数
     重写onCreate()和onUpgrade()方法
     调用getWritableDatabase()(加锁)或者getReadableDatabase()(不加锁)方法都可以创建数据库
     数据库文件不存在时, 会创建数据库文件, 并且执行onCreate()方法
     数据库文件存在, 并且版本没有改变时, 不执行任何方法
     数据库文件存在, 版本提升, 执行onUpgrade()方法
3.增删改查
     增删改都可以使用SQLiteDatabase.execSQL()方法执行SQL语句完成

     查询方法需要使用SQLiteDatabase.rawQuery()方法进行查询, 得到一个Cursor, 再调用moveToNext()和             getString()getInt()等方法获取数据

数据的增删改差以及分页操作(通过实现SQLiteDatabase的子类获取数据库连接后进行增删改差)

SQL实现

import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class PersonDao {private DBOpenHelper helper;public PersonDao(Context context) {helper = new DBOpenHelper(context);}public void savePerson(Person person) {SQLiteDatabase db = helper.getWritableDatabase();// 获取数据库链接,参数是应用环境ContextObject[] obj = new Object[] { person.getName(), person.getBalance() };db.execSQL("INSERT INTO person(name,balance) VALUES(?,?)", obj);// 执行插入的sql语句,第一个参数是sql语句,第二个参数是占位符的具体参数//db.close();  }public void deletePerson(int id) {SQLiteDatabase db = helper.getWritableDatabase();db.execSQL("DELETE FROM person WHERE id=?", new Object[] { id });//db.close();  如果操作数据库都不关闭连接,那么这次拿到的连接是上一次没关闭的连接(效率稍高,存在于单个用户操作数据库的情况下)}public void updatePerson(Person person) {SQLiteDatabase db = helper.getWritableDatabase();db.execSQL("UPDATE person SET name=?,balance=? WHERE id=?", new Object[] { person.getName(),person.getBalance(),person.getId() });//db.close();}public Person queryPerson(int id) {SQLiteDatabase db = helper.getReadableDatabase();//获取一个可读的数据库链接Cursor c = db.rawQuery("SELECT name,balance FROM person WHERE id=?", new String[]{String.valueOf(id)});//执行sql查询操作,获取游标Person person = null;if(c.moveToNext()){//判断游标是否有下一条记录,如果有的话,将游标向后移动一位//c.getString(0);  获取0号索引上的数据String name = c.getString(c.getColumnIndex("name"));//根据列名得出索引,再用c.getString(索引)获取值int balance = c.getInt(c.getColumnIndex("balance"));person = new Person(id, name, balance);}c.close();db.close();return person;}public List<Person> queryAll(){SQLiteDatabase db = helper.getReadableDatabase();Cursor c = db.rawQuery("SELECT * FROM person", null);List<Person> persons = new ArrayList<Person>();Person person = null;while(c.moveToNext()){String name = c.getString(c.getColumnIndex("name"));int balance = c.getInt(c.getColumnIndex("balance"));int id = c.getInt(c.getColumnIndex("id"));person = new Person(id, name, balance);persons.add(person);}return persons;}public int queryCount(){SQLiteDatabase db = helper.getReadableDatabase();Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);c.moveToNext();return c.getInt(0);}/** * 分页查询 * @param pageNum 当前页码 * @param pageSize 每页多少条 * @return */public List<Person> queryPage(int pageNum,int pageSize){String offset = (pageNum-1)*pageSize+"";//从第几条开始SQLiteDatabase db = helper.getReadableDatabase();Cursor c = db.rawQuery("SELECT * FROM person LIMIT ?,?", new String[]{offset,pageSize+""});List<Person> persons = new ArrayList<Person>();Person person = null;while(c.moveToNext()){String name = c.getString(c.getColumnIndex("name"));int balance = c.getInt(c.getColumnIndex("balance"));int id = c.getInt(c.getColumnIndex("id"));person = new Person(id, name, balance);persons.add(person);}return persons;}//事务public void remit(int from,int to,int amount){SQLiteDatabase db = helper.getWritableDatabase();try {db.beginTransaction();//开始事务(如果不写endTransaction,也会结束事务,不过要等到超时之后才会结束)db.execSQL("UPDATE person SET balance=balance-? WHERE id=?",new Object[]{amount,from});db.execSQL("UPDATE person SET balance=balance+? WHERE id=?",new Object[]{amount,to});db.setTransactionSuccessful();//设置成功点,在事务结束时,成功点之前操作会被提交/** * 成功点可以有多个 * db.execSQL("");//成功提交 * db.execSQL("");//成功提交 * db.setTransactionSuccessful(); * db.execSQL("");//这句话将被回滚 * System.out.println(1/0); * db.execSQL("");//这句话没执行 * db.setTransactionSuccessful(); */} finally{db.endTransaction();//结束事务,将成功点之前的操作提交db.close();}}}

内容提供者方式实现

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;public class PersonDaoNew {private DBOpenHelper helper;public PersonDaoNew(Context context) {helper = new DBOpenHelper(context);}public void savePerson(Person person) {SQLiteDatabase db = helper.getWritableDatabase();// 获取数据库链接,参数是应用环境ContextContentValues values = new ContentValues();//类似于一个map集合,键是String,用来存放列名,值是Object,用来存入插入的数据values.put("name", person.getName());values.put("balance", person.getBalance());db.insert("person", "id", values);//此方法会返回插入数据的id值/** * 第二个参数 * ContentValues values = new ContentValues(); * db.insert("person", null, values); * 或者 * db.insert("person", null, null); * 如果想往表中插入null数据时 * 因为如果不指定第二个参数,db.insert("person", null, null); * 最后执行的sql语句是  INSERT INTO person(null)  VALUES(NULL); * 所以,如果想要正确执行这条插入null数据的sql语句,那么第二个参数应该任意指定个表中的列名,就算指定主键id也行(此时如果id自增的话照样自增,不会为null) * db.insert("person", "id", null); */db.close();}public void deletePerson(int id) {SQLiteDatabase db = helper.getWritableDatabase();db.delete("person", "id=?", new String[]{id+""});}public void updatePerson(Person person) {SQLiteDatabase db = helper.getWritableDatabase();ContentValues values = new ContentValues();values.put("name", person.getName());values.put("balance", person.getBalance());db.update("person", values, "id=?", new String[]{person.getId()+""});}public Person queryPerson(int id) {SQLiteDatabase db = helper.getReadableDatabase();//获取一个可读的数据库链接Cursor c = db.query("person", new String[]{"name","balance"}, "id=?", new String[]{id+""}, null, null, null);Person person = null;if(c.moveToNext()){String name = c.getString(c.getColumnIndex("name"));int balance = c.getInt(c.getColumnIndex("balance"));person = new Person(id,name,balance);}return person;}public List<Person> queryAll(){SQLiteDatabase db = helper.getReadableDatabase();Cursor c = db.query("person", null, null, null, null, null, "id DESC");List<Person> persons = new ArrayList<Person>();Person person = null;while(c.moveToNext()){String name = c.getString(c.getColumnIndex("name"));int balance = c.getInt(c.getColumnIndex("balance"));int id = c.getInt(c.getColumnIndex("id"));person = new Person(id, name, balance);persons.add(person);}return persons;}public int queryCount(){SQLiteDatabase db = helper.getReadableDatabase();Cursor c = db.query("person", new String[]{"COUNT(*)"}, null, null, null, null, null);c.moveToNext();return c.getInt(0);}/** * 分页查询 * @param pageNum 当前页码 * @param pageSize 每页多少条 * @return */public List<Person> queryPage(int pageNum,int pageSize){String offset = (pageNum-1)*pageSize+"";//从第几条开始SQLiteDatabase db = helper.getReadableDatabase();Cursor c = db.query("person", null, null, null, null, null, null,offset+","+pageSize);List<Person> persons = new ArrayList<Person>();Person person = null;while(c.moveToNext()){String name = c.getString(c.getColumnIndex("name"));int balance = c.getInt(c.getColumnIndex("balance"));int id = c.getInt(c.getColumnIndex("id"));person = new Person(id, name, balance);persons.add(person);}return persons;}public void remit(int from,int to,int amount){SQLiteDatabase db = helper.getWritableDatabase();try {db.beginTransaction();//开始事务(如果不写endTransaction,也会结束事务,不过要等到超时之后才会结束)/* * values1.put("balance", "balance-"+amount); * 这样设置值是错误的,因为第二个参数程序会认为它是个字符串,将字符串设置到int类型上是0   * 正确做法是先将balance查询出来后,进行运算操作后再设置到第二个参数上去 * 所以这种情况下,不推荐用这种方式进行数据库操作,而推荐sql */ContentValues values1 = new ContentValues();values1.put("balance", "balance-"+amount);db.update("person", values1, "id=?", new String[]{from+""});ContentValues values2 = new ContentValues();values2.put("balance", "balance+"+amount);db.update("person", values2, "id=?", new String[]{to+""});db.setTransactionSuccessful();//设置成功点,在事务结束时,成功点之前操作会被提交} finally{db.endTransaction();//结束事务,将成功点之前的操作提交db.close();}}}

数据库的创建和更新在这个类中操作(实现SQLiteOpenHelper接口中的  onCreate和onUpgrade方法来进行数据库的新建和修改操作)

import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** *SQLiteOpenHelper这个类用来管理数据库的创建和控制数据库版本 */public class DBOpenHelper extends SQLiteOpenHelper {public DBOpenHelper(Context context){//创建对象的时候,需要传入上下文环境     数据库文件是放在当前应用包下的sqlite文件夹下(这个位置信息也是封装在Context中的)/* * 父类没有无参构造函数,子类必须显示调用有参的构造函数 * 参数一:上下文环境,用来确定数据库文件存储的目录 * 参数二:数据库文件的名字 * 参数三:生成游标的工厂,如果为null,就表示使用默认的 * 参数四:数据库的版本,从1开始 */super(context,"mydb.db",null,2);}//创建@Overridepublic void onCreate(SQLiteDatabase db) {String sql = "CREATE TABLE person(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20))";db.execSQL(sql);//执行sql语句}//修改数据库  注意修改的时候版本号必须大于上一次操作数据库的版本号@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {String sql = "ALTER TABLE person ADD balance INTEGER";db.execSQL(sql);//执行sql语句}}

测试类

import java.util.List;import java.util.Random;import android.test.AndroidTestCase;public class DBTest extends AndroidTestCase {public void testCreateDatabase() {DBOpenHelper helper = new DBOpenHelper(getContext());helper.getWritableDatabase();/* * 获取可写的数据库链接 * 数据库文件不存在时,会创建数据库文件,并且执行onCreate方法 * 数据库文件存在时,并且版本没有改变时,不执行任何方法 * 数据库文件存在时,并且版本提升,执行onUpgrade方法 */}public void testSave(){PersonDao dao = new PersonDao(getContext());for(int i=0;i<100;i++){dao.savePerson(new Person("李四",new Random().nextInt(10000)));}}public void testDelete(){PersonDao dao = new PersonDao(getContext());dao.deletePerson(1);}public void testUpdate(){PersonDao dao = new PersonDao(getContext());dao.updatePerson(new Person(2,"皮卡丘",100));}public void testQuery(){PersonDao dao = new PersonDao(getContext());Person person = dao.queryPerson(2);System.out.println(person);}public void queryAll(){PersonDao dao = new PersonDao(getContext());List<Person> persons = dao.queryAll();for (Person person : persons) {System.out.println(person);}}public void queryCount(){PersonDao dao = new PersonDao(getContext());System.out.println(dao.queryCount());}public void testQueryPage(){PersonDao dao = new PersonDao(getContext());List<Person> persons = dao.queryPage(2, 20);for (Person person : persons) {System.out.println(person);}}public void testTransaction(){PersonDao dao = new PersonDao(getContext());dao.remit(3, 2, 100);}}

domain

public class Person {private Integer id;private String name;private Integer balance;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Integer getBalance() {return balance;}public void setBalance(Integer balance) {this.balance = balance;}public Person(Integer id, String name, Integer balance) {this.id = id;this.name = name;this.balance = balance;}public Person(String name, Integer balance) {this.name = name;this.balance = balance;}public Person() {}@Overridepublic String toString() {return "Person [id=" + id + ", name=" + name + ", balance=" + balance+ "]";}}