Android学习---SQLite数据库的增删改查和事务(transaction)调用

上一篇文章中介绍了手工拼写sql语句进行数据库的CRUD操作,本文将介绍调用sqlite内置的方法实现CRUD操作,其实质也是通过拼写sql语句.

首先,创建一个新的android项目:

其次,查看代码实现增删查改:

1.创建DB工具类

MyDBHelper.java(创建数据库的操作)

package com.amos.android_db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Created by amosli on 14-6-12. */ public class MyDBHelper extends SQLiteOpenHelper{ /** * * @param context */ public MyDBHelper(Context context) { super(context, "sqlitedb", null, 1); } /** * 数据库第一次创建的时候调用此方法 * @param db */ @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }

2.配置测试环境

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.amos.android_db" android:versionCode="1" android:versionName="1.0"> <instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="com.amos.android_db"/> <uses-sdk android:minSdkVersion="7"/> <application android:label="@string/app_name"> <uses-library android:name="android.test.runner"/> <activity android:name="MyActivity" android:label="@string/app_name"> <intent-filter> <action android:name="android.intent.action.MAIN"/> <category android:name="android.intent.category.LAUNCHER"/> </intent-filter> </activity> </application> </manifest>

 

3.PersonDao.java(实现增删查改的方法)

package com.amos.android_db.dao; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.util.Log; import com.amos.android_db.MyDBHelper; import java.util.ArrayList; import java.util.List; /** * Created by amosli on 14-6-12. */ public class PersonDao { private Context context; MyDBHelper dbHelper; public PersonDao(Context context) { this.context = context; dbHelper = new MyDBHelper(context); } /** * 添加一条记录 */ public void add(String name, int age) { SQLiteDatabase db = dbHelper.getWritableDatabase(); if (db.isOpen()) { ContentValues values = new ContentValues(); values.put("age", age); values.put("name", name); //不允许插入一个空值,如果contentvalue,一般第二个参 db.insert("person", null, values);//通过组拼完成的添加的操作 } db.close(); } }

１)测试add方法:

package com.amos.android_db.test; import android.test.AndroidTestCase; import com.amos.android_db.dao.PersonDao; /** * Created by amosli on 14-6-13. */ public class TestPersonDao extends AndroidTestCase{ public void testAdd() throws Exception{ PersonDao personDao = new PersonDao(this.getContext()); personDao.add("amosli",10); personDao.add("amosli",10); for(int i=0;i<10;i++){ personDao.add("amos"+i,10+i); } } }

 

查看结果:

打开新创建的数据库sqlitedb

./adb shell #cd /data/data/com.amos.android_db/databases # ls sqlitedb # sqlite3 sqlitedb SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .database seq name file --- --------------- ---------------------------------------------------------- 0 main /data/data/com.amos.android_db/databases/sqlitedb sqlite> .table android_metadata person

查看写入的值:

sqlite> select * from person; 1|amosli|10 2|amosli|10 3|amos0|10 4|amos1|11 5|amos2|12 6|amos3|13 7|amos4|14 8|amos5|15 9|amos6|16 10|amos7|17 11|amos8|18 12|amos9|19

２)删除数据

delete方法,主要是调用了SQLiteDatabase的delete方法.其实质上也是在拼sql语句.

public void delete(String name) { SQLiteDatabase db = dbHelper.getWritableDatabase(); if (db.isOpen()) { db.delete("person", "name=?", new String[]{name}); db.close(); } }

测试delete方法:

public void testDelete() throws Exception{ PersonDao personDao = new PersonDao(this.getContext()); personDao.delete("amosli"); }

 

查看结果:

sqlite> select * from person; 3|amos0|10 4|amos1|11 5|amos2|12 6|amos3|13 7|amos4|14 8|amos5|15 9|amos6|16 10|amos7|17 11|amos8|18 12|amos9|19

3)更新数据

public void update(String name, String newname, int newage) { SQLiteDatabase db = dbHelper.getWritableDatabase(); if (db.isOpen()) { ContentValues contentValues = new ContentValues(); contentValues.put("name", newname); contentValues.put("age", newage); db.update("person", contentValues, "name=?", new String[]{name}); db.close(); } }

测试方法:

public void testUpdate() throws Exception{ PersonDao personDao = new PersonDao(this.getContext()); personDao.update("amos0","0amos",35); }

查看结果:

sqlite> select * from person; 3|0amos|35 4|amos1|11 5|amos2|12 6|amos3|13 7|amos4|14 8|amos5|15 9|amos6|16 10|amos7|17 11|amos8|18 12|amos9|19

4)查找数据

public boolean find(String name) { boolean status_result = false; SQLiteDatabase db = dbHelper.getReadableDatabase(); // public android.database.Cursor query( // String table, // String[] columns, // String selection, // String[] selectionArgs, // String groupBy, // String having, // String orderBy) if (db.isOpen()) { Cursor cursor = db.query("person", null, "name=?", new String[]{name}, null, null, null); if (cursor.moveToFirst()) { status_result = true; } cursor.close(); db.close(); } return status_result; }

测试方法:

public void testFind() throws Exception{ PersonDao personDao = new PersonDao(this.getContext()); assertEquals(true,personDao.find("amos1")); }

 

5)查找所有数据

public List<Person> findAll(){ List<Person> persons = null; SQLiteDatabase db = dbHelper.getReadableDatabase(); if(db.isOpen()){ persons = new ArrayList<Person>(); Cursor cursor = db.query("person", null, null, null, null, null, null); while(cursor.moveToNext()){ Person person = new Person(); person.setName(cursor.getString(cursor.getColumnIndex("name"))); person.setAge(cursor.getInt(cursor.getColumnIndex("age"))); persons.add(person); } cursor.close(); db.close(); } return persons; }

测试方法:

public void testFindAll() throws Exception{ PersonDao personDao = new PersonDao(getContext()); List<Person> personList = personDao.findAll(); for(Person person:personList){ Log.d("person:",person.toString()); } }

输出结果:

 

  

4.扩展--SQLite中的事务

这里以amos1向amos2转钱200元为例:

1),amos1账户初始1000元,amos2账户初始0元.

2),从amos1中减去200元,amos2中加上200元,这两个步骤要么同时成功,要么同时失败,不能一方成功,另一主失败,这就是事务.

代码实现:

package com.amos.android_db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Created by amosli on 14-6-12. */ public class MyDBHelper extends SQLiteOpenHelper{ public MyDBHelper(Context context) { super(context, "sqlitedb", null, 2); } /** * 数据库第一次创建的时候调用此方法 * @param db */ @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table if not exists person (personid integer primary key autoincrement ,name varchar(30) ,age integer(3) )"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("alter table person add account integer null"); } }

在初始化时更新表的结构,添加account一列,用来表示账户余额.

在PersonDao中添加如下方法:

public void transferMoney() { SQLiteDatabase db = dbHelper.getWritableDatabase(); if(db.isOpen()){ try{ db.beginTransaction(); //给amos1账户里设置1000元,amost account=0; db.execSQL("update person set account=? where name = ?",new Object[]{1000,"amos1"}); db.execSQL("update person set account=? where name = ?",new Object[]{0,"amos2"}); //从amos1账户里扣除200元 db.execSQL("update person set account=account-? where name = ?",new Object[]{200,"amos1"}); //把amos1的钱转给amos2 db.execSQL("update person set account=account+? where name=?",new Object[]{200,"amos2"}); }catch(Exception e){ e.printStackTrace(); }finally{ //显示的设置数据事务是否成功 db.setTransactionSuccessful(); db.endTransaction(); db.close(); } } }

和hibernate里的事务调用很类似,这里先beginTransaction,然后要注意的是setTransactionSuccessful和endTransaction.

测试方法:

public void testTransaction() throws Exception{ PersonDao personDao = new PersonDao(getContext()); personDao.transferMoney(); }

测试结果:

sqlite> select * from person; 3|0amos|35| 4|amos1|11|800 5|amos2|12|200 6|amos3|13| 7|amos4|14| 8|amos5|15| 9|amos6|16| 10|amos7|17| 11|amos8|18| 12|amos9|19|

 

 

本文源码:https://github.com/amosli/android_basic/tree/android_db