Android之sqlite数据库的简单使用

第一种方法：手写sql脚本

新建一个DbOpenHelper类，继承SQLiteOpenHelper

package com.example.android_sqlite_db;import android.content.Context;import android.database.DatabaseErrorHandler;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.database.sqlite.SQLiteDatabase.CursorFactory;public class DbOpenHelper extends SQLiteOpenHelper {private static String name="mydb.db";//数据库的名称private static int version=2;//数据库的版本号，//注意：当version的值比之前的值大时会去执行onUpgrade方法，而不是再执行onCreate方法了public DbOpenHelper(Context context) {super(context, name, null, version);// TODO Auto-generated constructor stub}//当数据库创建的时候，是第一次被执行的，完成对数据库的表的创建@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubString sql="create table person(id integer primary key autoincrement,name varchar(64),address varchar(64))";db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubString sql="alter table person add sex varchar(8)";db.execSQL(sql);}}

新建一个DbRepository接口：

package com.example.android_sqlite_db.repository;import java.util.List;import java.util.Map;public interface DbRepository {public boolean insertPerson(String[] parm);public boolean deletePerson(String[] parm);public boolean updatePerson(String[] parm);public Map<String,String> selectOnePerson(String[] parm);public List<Map<String,String>> selectListPerson(String[] parm);}

新建一个PersonService 类，实现DbRepository接口

package com.example.android_sqlite_db.service;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.android_sqlite_db.DbOpenHelper;import com.example.android_sqlite_db.repository.DbRepository;public class PersonService implements DbRepository {private Context context;public PersonService(Context context){this.context=context;}@Overridepublic boolean insertPerson(String[] bindArgs) {// TODO Auto-generated method stubDbOpenHelper openHelper=new DbOpenHelper(context);SQLiteDatabase sqliteDatabase=null;try {sqliteDatabase=openHelper.getWritableDatabase();String sql="insert into person(name,address,sex) values(?,?,?)";sqliteDatabase.execSQL(sql, bindArgs);} catch (Exception e) {// TODO: handle exception}finally{if (sqliteDatabase!=null) {sqliteDatabase.close();}}return true;}@Overridepublic boolean deletePerson(String[] parm) {// TODO Auto-generated method stubSQLiteDatabase sqliteDatabase=null;DbOpenHelper openHelper=new DbOpenHelper(context);try {sqliteDatabase=openHelper.getWritableDatabase();String sql="delete from person where id=?";sqliteDatabase.execSQL(sql, parm);} catch (Exception e) {// TODO: handle exception}finally{if (sqliteDatabase!=null) {sqliteDatabase.close();}}return true;}@Overridepublic boolean updatePerson(String[] bindArgs) {// TODO Auto-generated method stubDbOpenHelper openHelper=new DbOpenHelper(context);SQLiteDatabase sqliteDatabase=null;try {sqliteDatabase=openHelper.getWritableDatabase();String sql="update  person set name=?,address=?,sex=? where id=?";sqliteDatabase.execSQL(sql, bindArgs);} catch (Exception e) {// TODO: handle exception}finally{if (sqliteDatabase!=null) {sqliteDatabase.close();}}return true;}@Overridepublic Map<String,String> selectOnePerson(String[] selectionArgs) {// TODO Auto-generated method stubSQLiteDatabase sqliteDatabase=null;Map<String,String> map=new HashMap<String,String>();DbOpenHelper openHelper=new DbOpenHelper(context);try {sqliteDatabase=openHelper.getWritableDatabase();String sql="select * from person where id=?";Cursor cursor=sqliteDatabase.rawQuery(sql, selectionArgs);String[]columnName=cursor.getColumnNames();while (cursor.moveToNext()) {for (int i = 0; i < columnName.length; i++) {String columnNames=columnName[i];int columnIndex=cursor.getColumnIndex(columnNames);String columnValue=cursor.getString(columnIndex);if (columnValue==null) {columnValue="";}map.put(columnNames, columnValue);}}} catch (Exception e) {// TODO: handle exception}finally{if (sqliteDatabase!=null) {sqliteDatabase.close();}}return map;}@Overridepublic List<Map<String,String>> selectListPerson(String[] selectionArgs) {// TODO Auto-generated method stubSQLiteDatabase sqliteDatabase=null;List<Map<String,String>> listMap=new ArrayList<Map<String,String>>();DbOpenHelper openHelper=new DbOpenHelper(context);try {sqliteDatabase=openHelper.getWritableDatabase();String sql="select * from person";Cursor cursor=sqliteDatabase.rawQuery(sql, selectionArgs);String[]columnName=cursor.getColumnNames();while(cursor.moveToNext()){Map<String,String> map=new HashMap<String, String>();for (int i = 0; i < columnName.length; i++) {String columnNames=columnName[i];int columnIndex=cursor.getColumnIndex(columnNames);String columnValue=cursor.getString(columnIndex);if (columnValue==null) {columnValue="";}map.put(columnNames, columnValue);}listMap.add(map);}} catch (Exception e) {// TODO: handle exception}finally{if (sqliteDatabase!=null) {sqliteDatabase.close();}}return listMap;}}

myTest测试类继承AndroidTestCase

package com.example.android_sqlite_db.test;import java.util.List;import java.util.Map;import com.example.android_sqlite_db.DbOpenHelper;import com.example.android_sqlite_db.service.PersonService;import android.test.AndroidTestCase;import android.util.Log;public class MyTest extends AndroidTestCase {public MyTest(){}public void create(){DbOpenHelper db=new DbOpenHelper(getContext());db.getWritableDatabase();}public void insertPerson(){PersonService service=new PersonService(getContext());boolean flg=service.insertPerson(new String[]{"张三","广西省","男"});Log.i("MyTest", flg+"");}public void deletePerson(){PersonService service=new PersonService(getContext());boolean flg=service.deletePerson(new String[]{"1"});Log.i("MyTest", flg+"");}public void updatePerson(){PersonService service=new PersonService(getContext());boolean flg=service.updatePerson(new String[]{"李四","广州市","不祥","2"});Log.i("MyTest", flg+"");}public void getOnePerson(){PersonService service=new PersonService(getContext());Map<String,String> map=service.selectOnePerson(new String[]{"2"});Log.i("MyTest", map.toString());}public void getListPerson(){PersonService service=new PersonService(getContext());List<Map<String,String>> map=service.selectListPerson(null);Log.i("MyTest", map.toString());}}

最后在AndroidManifest.xml清单文件里加上AndroidTestCase的单元测试配置：

<instrumentation android:targetPackage="com.example.android_sqlite_db" android:name="android.test.InstrumentationTestRunner"></instrumentation><pre name="code" class="html"><application>    <uses-library android:name="android.test.runner"/></application>

第二种方法：使用Android API封装好的方法

新建一个DbRepository2接口

package com.example.android_sqlite_db.repository;import java.util.List;import java.util.Map;import android.content.ContentValues;public interface DbRepository2 {public long insertPerson(ContentValues values);public long deletePerson(String[] parm);public long updatePerson(ContentValues values,String[] whereArgs);public Map<String,String> selectOnePerson(String[] parm);public List<Map<String,String>> selectListPerson(String[] parm);}

新建一个PersonService2类实现DbRepository2接口

package com.example.android_sqlite_db.service;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import com.example.android_sqlite_db.DbOpenHelper;import com.example.android_sqlite_db.repository.DbRepository;import com.example.android_sqlite_db.repository.DbRepository2;public class PersonService2 implements DbRepository2{private DbOpenHelper openHelper=null;private Context context;public PersonService2(Context context){this.context=context;}@Overridepublic long insertPerson(ContentValues values) {// TODO Auto-generated method stublong id=0;try {openHelper=new DbOpenHelper(context);SQLiteDatabase sqliteDatabase=openHelper.getWritableDatabase();id=sqliteDatabase.insert("person", null, values);} catch (Exception e) {// TODO: handle exception}finally{if (openHelper!=null) {openHelper.close();}}return id;}@Overridepublic long deletePerson(String[] whereArgs) {long id=0;try {openHelper=new DbOpenHelper(context);SQLiteDatabase sqliteDatabase=openHelper.getWritableDatabase();id=sqliteDatabase.delete("person", "id=?", whereArgs);} catch (Exception e) {// TODO: handle exception}finally{if (openHelper!=null) {openHelper.close();}}return id;}@Overridepublic long updatePerson(ContentValues values,String[] whereArgs) {// TODO Auto-generated method stublong id=0;try {openHelper=new DbOpenHelper(context);SQLiteDatabase sqliteDatabase=openHelper.getWritableDatabase();id=sqliteDatabase.update("person", values, "id=?", whereArgs);} catch (Exception e) {// TODO: handle exception}finally{if (openHelper!=null) {openHelper.close();}}return id;}@Overridepublic Map<String, String> selectOnePerson(String[] selectionArgs) {Map<String, String> map=new HashMap<String, String>();try {openHelper=new DbOpenHelper(context);SQLiteDatabase sqliteDatabase=openHelper.getWritableDatabase();String[] columns=new String[]{"id","name","address","sex"};Cursor cursor=sqliteDatabase.query(false, "person", columns, "id=?", selectionArgs, null, null, null, null);String[] columnNameArr=cursor.getColumnNames();while(cursor.moveToNext()){for (int i = 0; i < columnNameArr.length; i++) {String columnName=columnNameArr[i];int columnIndex=cursor.getColumnIndex(columnName);String columnValue=cursor.getString(columnIndex);map.put(columnName, columnValue);}}} catch (Exception e) {// TODO: handle exception}finally{if (openHelper!=null) {openHelper.close();}}return map;}@Overridepublic List<Map<String, String>> selectListPerson(String[] selectionArgs) {List<Map<String, String>> listMap=new ArrayList<Map<String,String>>();try {openHelper=new DbOpenHelper(context);SQLiteDatabase sqliteDatabase=openHelper.getWritableDatabase();String[] columns=new String[]{"id","name","address","sex"};Cursor cursor=sqliteDatabase.query(false, "person", columns, null, selectionArgs, null, null, null, null);String[] columnNameArr=cursor.getColumnNames();while(cursor.moveToNext()){Map<String, String> map=new HashMap<String, String>();for (int i = 0; i < columnNameArr.length; i++) {String columnName=columnNameArr[i];int columnIndex=cursor.getColumnIndex(columnName);String columnValue=cursor.getString(columnIndex);map.put(columnName, columnValue);}listMap.add(map);}} catch (Exception e) {// TODO: handle exception}finally{if (openHelper!=null) {openHelper.close();}}return listMap;}}

新建一个测试类：

package com.example.android_sqlite_db.test;import java.util.List;import java.util.Map;import android.content.ContentValues;import android.test.AndroidTestCase;import android.util.Log;import com.example.android_sqlite_db.service.PersonService2;public class MyTest2 extends AndroidTestCase{public void addPerson(){PersonService2 service=new PersonService2(getContext());ContentValues values=new ContentValues();values.put("name", "王五");values.put("address", "阳江市");values.put("sex", "男");long id=service.insertPerson(values);Log.i("MyTest2", id+"");}public void updatePerson(){PersonService2 service=new PersonService2(getContext());ContentValues values=new ContentValues();values.put("name", "小李四");values.put("address", "茂名市");values.put("sex", "男");long id=service.updatePerson(values, new String[]{"2"});Log.i("MyTest2", id+"");}public void deletePerson(){PersonService2 service=new PersonService2(getContext());long id=service.deletePerson(new String[]{"3"});Log.i("MyTest2", id+"");}public void selectOnePerson(){PersonService2 service=new PersonService2(getContext());Map<String, String> map=service.selectOnePerson(new String[]{"4"});Log.i("MyTest2", map.toString());}public void selectListPerson(){PersonService2 service=new PersonService2(getContext());List<Map<String, String>> listMap=service.selectListPerson(null);Log.i("MyTest2", listMap.toString());}}