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
		
	}

	//当数据库创建的时候，是第一次被执行的，完成对数据库的表的创建
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql="create table person(id integer primary key autoincrement,name varchar(64),address varchar(64))";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		String 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;
	}

	@Override
	public boolean insertPerson(String[] bindArgs) {
		// TODO Auto-generated method stub
		DbOpenHelper 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;
	}

	@Override
	public boolean deletePerson(String[] parm) {
		// TODO Auto-generated method stub
		SQLiteDatabase 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;
	}

	@Override
	public boolean updatePerson(String[] bindArgs) {
		// TODO Auto-generated method stub
		DbOpenHelper 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;
	}

	@Override
	public Map<String,String> selectOnePerson(String[] selectionArgs) {
		// TODO Auto-generated method stub
		SQLiteDatabase 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;
	}

	@Override
	public List<Map<String,String>> selectListPerson(String[] selectionArgs) {
		// TODO Auto-generated method stub
		SQLiteDatabase 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;
	}
	@Override
	public long insertPerson(ContentValues values) {
		// TODO Auto-generated method stub
		long 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;
	}
	@Override
	public 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;
	}
	@Override
	public long updatePerson(ContentValues values,String[] whereArgs) {

		// TODO Auto-generated method stub
		long 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;
	
	}
	@Override
	public 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;
	}
	@Override
	public 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());
	}
}