1、SQlite是什么?
SQLite 是进程内的数据库引擎,不存在数据库的客户端和服务器,使用SQLite只需要带上它的一个动态库,就可以享用它的全部功能。
2、SQLiteOpenHelper的使用
我们要使用SQlite,首先要得到一个SQliteDatabase对象,那么怎么得到它呢?
首先,我们要得到一个SQliteOpenHelper对象,然后用这个SQliteOpenHelper对象 的getReadableDatabase()或getWritableDatabase()方法获得可读或可写的数据库,从而得到一个SQLiteDatebase对象。然后再用这个SQLiteDatebase对象对数据库进行操作就可以了。我们来看一下代码:
//DatabaseHelper作为一个访问SQLite的助手类,提供两个方面的功能,
//第一,getReadableDatabase(),getWritableDatabase()可以获得SQLiteDatabse对象,通过该对象可以对数据库进行操作
//第二,提供了onCreate()和onUpgrade()两个回调函数,允许我们在创建和升级数据库时,进行自己的操作
采用MVC模式:即service:服务层; Dao:数据访问层; 操作层。
1.PersonService2.java 定义接口
- public interface PersonService2 {
- public boolean addPerson(ContentValues values);
- public boolean deletePerson(String whereClause, String[] whereArgs);
- public boolean updatePerson(ContentValues values, String whereClause, String[] whereArgs);
- public Map<String,String> viewPerson(String selection, String[] selectionArgs);
- public List<Map<String,String>> listPersonMaps(String selection, String[] selectionArgs);
- }
- public class PersonDao2 implements PersonService2 {
- private DbOpenHelper helper = null;
- public PersonDao2(Context context) {
- helper = new DbOpenHelper(context);
- }
- public boolean addPerson(ContentValues values) {
- boolean flag = false;
- SQLiteDatabase database = null;
- long id = -1;
- try {
- database = helper.getWritableDatabase();
- id = database.insert("person", null, values);
- flag = (id != -1 ? true : false);
- } catch (Exception e) {
- } finally {
- if (database != null) {
- database.close();
- }
- }
- return flag;
- }
- public boolean deletePerson(String whereClause, String[] whereArgs) {
- boolean flag = false;
- SQLiteDatabase database = null;
- int count = 0;
- try {
- database = helper.getWritableDatabase();
- count = database.delete("person", whereClause, whereArgs);
- flag = (count > 0 ? true : false);
- } catch (Exception e) {
- } finally {
- if (database != null) {
- database.close();
- }
- }
- return flag;
- }
- public boolean updatePerson(ContentValues values, String whereClause,
- String[] whereArgs) {
- boolean flag = false;
- SQLiteDatabase database = null;
- int count = 0;// 影响数据库的行数
- try {
- database = helper.getWritableDatabase();
- count = database.update("person", values, whereClause, whereArgs);
- flag = (count > 0 ? true : false);
- } catch (Exception e) {
- } finally {
- if (database != null) {
- database.close();
- }
- }
- return flag;
- }
- //应用中通过id查询,所以只得到单条记录,即cursor.moveToNext()就为null了。
- public Map<String, String> viewPerson(String selection,String[] selectionArgs) {
- // select 返回的列的名称(投影查询) from
- SQLiteDatabase database = null;
- Cursor cursor = null;
- Map<String, String> map = new HashMap<String, String>();
- try {
- database = helper.getReadableDatabase();
- cursor = database.query(true, "person", null, selection,
- selectionArgs, null, null, null, null);
- int cols_len = cursor.getColumnCount();
- while (cursor.moveToNext()) {
- for (int i = 0; i < cols_len; i++) {//循环,把这一行每一列的值都取出来
- String cols_name = cursor.getColumnName(i);
- String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
- if (cols_value == null) {
- cols_value = "";
- }
- map.put(cols_name, cols_value);
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- if (database != null) {
- database.close();
- }
- }
- return map;
- }
- public List<Map<String, String>> listPersonMaps(String selection,String[] selectionArgs) {
- List<Map<String, String>> list = new ArrayList<Map<String, String>>();
- SQLiteDatabase database = null;
- Cursor cursor = null;
- try {
- database = helper.getReadableDatabase();
- cursor = database.query(false, "person", null, selection,
- selectionArgs, null, null, null, null);
- int cols_len = cursor.getColumnCount();
- while (cursor.moveToNext()) {
- Map<String, String> map = new HashMap<String, String>();
- for (int i = 0; i < cols_len; i++) {
- String cols_name = cursor.getColumnName(i);
- String cols_value = cursor.getString(cursor.getColumnIndex(cols_name));
- if (cols_value == null) {
- cols_value = "";
- }
- map.put(cols_name, cols_value);
- }
- list.add(map);
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- if (database != null) {
- database.close();
- }
- }
- return list;
- }
- }
4. 应用层
老罗的视频中是用一个测试设备来测试,我们在真机上测试不采用这种方法,我在一个Actvity中实现对SQLite的操作。源码如下:
- public class MainActivity extends Activity {
- private PersonDao personService = null;
- private final static String TAG = "SQLite";
- private ListView listView;
- private Button button1, button2, button3, button4, button5;
- @Override
- protected void onCreate(Bundle savedInstanceState) {
- super.onCreate(savedInstanceState);
- setContentView(R.layout.activity_main);
- personService = new PersonDao(this);
- listView = (ListView) this.findViewById(R.id.listView1);
- /........找到button.........../
- button1.setOnClickListener(new OnClickListener() {
- public void onClick(View v) {
- addPerson(); //添加人员
- }
- });
- button2.setOnClickListener(new OnClickListener() {
- public void onClick(View v) {
- deletePerson(" id = ? ", new String[] { "2" });//删除id为2的记录
- }
- });
- button3.setOnClickListener(new OnClickListener() {
- public void onClick(View v) {
- updatePerson(" id = ? ", new String[] { "3" });//更新人员
- }
- });
- button4.setOnClickListener(new OnClickListener() {
- public void onClick(View v) {
- viewPerson(" id = ? ", new String[] { "3" });//通过id,查看单条记录
- }
- });
- button5.setOnClickListener(new OnClickListener() {
- public void onClick(View v) {
- listPerson(null, null);//不限制条件,就可查询全部记录
- }
- });
- }
- public void addPerson() {
- ContentValues values = new ContentValues();// 类似map的属性
- values.put("name", "杰克");
- values.put("address", "江西");
- values.put("sex", "男");
- boolean flag = personService.addPerson(values);
- Log.i(TAG, "addPerson--->>" + flag);
- }
- public void deletePerson(String whereClause, String[] whereArgs) {
- // delete from person where id = ?
- // 不包含 where关键字
- boolean flag = personService.deletePerson(whereClause, whereArgs);
- Log.i(TAG, "deletePerson--->>" + flag);
- }
- public void updatePerson(String whereClause, String[] whereArgs) {
- ContentValues values = new ContentValues();// 类似map的属性
- values.put("name", "杰克");
- values.put("address", "纽约");
- values.put("sex", "女");
- boolean flag = personService.updatePerson(values, whereClause,whereArgs);
- Log.i(TAG, "updatePerson--->>" + flag);
- }
- public void viewPerson(String selection, String[] selectionArgs) {
- Map<String, String> map = personService.viewPerson(selection,
- selectionArgs);
- Log.i(TAG, "viewPerson--->>" + map.toString());
- }
- public void listPerson(String selection, String[] selectionArgs) {
- List<Map<String, String>> list = personService.listPersonMaps(
- selection, selectionArgs);
- Log.i(TAG, "listPerson--->>" + list.toString());
- query(list);
- }
- public void query(List<Map<String, String>> list) {
- // 需要显示多行,用SimpleAdapter.
- SimpleAdapter adapter = new SimpleAdapter(this, list,
- R.layout.simple_list_item, new String[] { "name", "address",
- "sex" }, new int[] { R.id.textView1, R.id.textView2,R.id.textView3 });
- listView.setAdapter(adapter);
- }
- }
person表中有4个字段:id,name,address,sex. 在SimpleAdapter 的new String[]参数中写入这4个字段的某些值,就会在listView中显示相应的数据,这个SimpleAdapter 会自动为我们从list列表中找出这些数据,相当智能是吧,呵呵。
效果图: