上一篇文章中介绍了手工拼写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
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# lssqlitedb# sqlite3 sqlitedbSQLite version 3.6.22Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite> .databaseseq name file --- --------------- ----------------------------------------------------------0 main /data/data/com.amos.android_db/databases/sqlitedb sqlite> .tableandroid_metadata person
查看写入的值:
sqlite> select * from person;1|amosli|102|amosli|103|amos0|104|amos1|115|amos2|126|amos3|137|amos4|148|amos5|159|amos6|1610|amos7|1711|amos8|1812|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|104|amos1|115|amos2|126|amos3|137|amos4|148|amos5|159|amos6|1610|amos7|1711|amos8|1812|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|354|amos1|115|amos2|126|amos3|137|amos4|148|amos5|159|amos6|1610|amos7|1711|amos8|1812|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 ListfindAll(){ List persons = null; SQLiteDatabase db = dbHelper.getReadableDatabase(); if(db.isOpen()){ persons = new ArrayList (); 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 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|8005|amos2|12|2006|amos3|13|7|amos4|14|8|amos5|15|9|amos6|16|10|amos7|17|11|amos8|18|12|amos9|19|
本文源码: