《老罗Android》学习之SQLite

1、SQlite是什么？
SQLite 是进程内的数据库引擎，不存在数据库的客户端和服务器，使用SQLite只需要带上它的一个动态库，就可以享用它的全部功能。
2、SQLiteOpenHelper的使用
我们要使用SQlite，首先要得到一个SQliteDatabase对象，那么怎么得到它呢？

首先，我们要得到一个SQliteOpenHelper对象，然后用这个SQliteOpenHelper对象的getReadableDatabase()或getWritableDatabase()方法获得可读或可写的数据库，从而得到一个SQLiteDatebase对象。然后再用这个SQLiteDatebase对象对数据库进行操作就可以了。我们来看一下代码：

//DatabaseHelper作为一个访问SQLite的助手类，提供两个方面的功能，
//第一，getReadableDatabase(),getWritableDatabase()可以获得SQLiteDatabse对象，通过该对象可以对数据库进行操作
//第二，提供了onCreate()和onUpgrade()两个回调函数，允许我们在创建和升级数据库时，进行自己的操作

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[] whereArgs) {
		boolean flag = false;
		SQLiteDatabase database = null;
		int count = 0;// 影响数据库的行数
		try {
			database = helper.getWritableDatabase();
			count = database.update("person",values,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[] 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",selection,selectionArgs,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[] 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,null);
		int cols_len = cursor.getColumnCount();
		while (cursor.moveToNext()) {
			Map<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("sex","女");
		boolean flag = personService.updatePerson(values,"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>> list = personService.listPersonMaps(
				selection,"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列表中找出这些数据，相当智能是吧，呵呵。

效果图：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!