通讯录-数据库SQLite的存储 数据库语言的模糊查询

工具类：数据库SQLite的创建及增删改查方法的规范MySQLiteOpenHelper

public class MySQLiteOpenHelper extends SQLiteOpenHelper {//数据库版本private static final int VERSION = 1;private SQLiteDatabase sQLiteDatabase = null;// 它是数据库名private static final String DB_NAME = "contact";// 它是数据库的路径private static final String DB_PATH = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS).getAbsolutePath();// 它是数据库全路径名public  static final String DB = DB_PATH + File.separator + DB_NAME;// 这是封装在内部的，访问数据库的对象public MySQLiteOpenHelper(Context context) {super(context, DB, null, 2);// TODO Auto-generated constructor stubSystem.out.println("数据库全路径为 ==="+DB);sQLiteDatabase=getWritableDatabase();}// 它是创建时候的回调@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubLog.i("onCreate", "执行了");db.execSQL("CREATE TABLE IF NOT EXISTS contact(unit,_id integer PRIMARY KEY AUTOINCREMENT,oid,mobile,name,job,phone,headword,allword);");}// 这个是版本更新的回调@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubLog.i("onUpgrade", "执行了");if(newVersion>oldVersion){db.execSQL("DROP TABLE IF EXISTS contact;");db.execSQL("CREATE TABLE IF NOT EXISTS contact1(_id integer PRIMARY KEY AUTOINCREMENT,unit,phone,oid,name,job,mobile);");}}// 这是查询数据库的方法们，也就是各种重载public Cursor selectCursor(String table, String[] columns,String selection, String[] selectionArgs, String groupBy,String having, String orderBy) {return sQLiteDatabase.query(table, columns, selection, selectionArgs,groupBy, having, orderBy);}public Cursor selectCursor(String sql) {return sQLiteDatabase.rawQuery(sql, new String[] {});}public Cursor selectCursor(String sql, String[] selectionArgs) {return sQLiteDatabase.rawQuery(sql, selectionArgs);}// 这是一个插入的方法，封装了内部访问数据库的insertpublic long insert(String table, String nullColumnHack, ContentValues values) {// insert方法第一个参数是表名，第二个参数是防止空行所提供的列名，第三个参数是要插入的字段对应的键值对// ContentValues它是一种键值对，可以直接new出来，就像hashmap// 返回这一行的行号return sQLiteDatabase.insert(table, nullColumnHack, values);}// 这是一个删除的方法，封装了内部访问数据库的deletepublic long delete(String table, String whereClause, String[] whereArgs) {// 第一个参数是表名，第二个参数是条件，第三个参数是替换条件中的占位符// 返回值是此次操作影响了多少行return sQLiteDatabase.delete(table, whereClause, whereArgs);}// 这是一个更新的方法，封装了内部访问数据库的updatepublic long update(String table, ContentValues values, String whereClause,String[] whereArgs) {// 第一个参数是表名，第二个参数表示要更新的列，用键值对的方式来表达，第三个参数是条件，第四个参数是替换条件中的占位符// 返回值是此次操作影响了多少行return sQLiteDatabase.update(table, values, whereClause, whereArgs);}// 封装一个执行任意语句的方法public void execSQL(String sql) {sQLiteDatabase.execSQL(sql);}// 封装一个执行任意语句的方法的重载public void execSQL(String sql, Object[] bindArgs) {// 第一个参数是任意sql语句，第二个参数是替换占位符，为了兼容数据类型，这里使用了Object数组sQLiteDatabase.execSQL(sql, bindArgs);}// 封装一个cursor转换成list的方法public List<Map<String, Object>> cursorToList(Cursor cursor) {if (cursor == null) {return null;}List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();// 获取操作cursor之前的下标int position = cursor.getPosition();cursor.moveToPosition(-1);for (; cursor.moveToNext();) {Map<String, Object> map = new HashMap<String, Object>();// cursor.getColumnCount()方法可以获取到一共有多少个列for (int i = 0; i < cursor.getColumnCount(); i++) {// cursor.getColumnName(i)可以获取到第i列的列名map.put(cursor.getColumnName(i), cursor.getString(i));}list.add(map);}// 把cursor恢复到之前的状态cursor.moveToPosition(position);return list;}// 查询list的方法们，各种重载就像cursor的查询一样。public List<Map<String, Object>> selectList(String table, String[] columns,String selection, String[] selectionArgs, String groupBy,String having, String orderBy) {// 调用之前的查询方法Cursor cursor = selectCursor(table, columns, selection, selectionArgs,groupBy, having, orderBy);// 返回list使用之前的转换方法return cursorToList(cursor);}public List<Map<String, Object>> selectList(String sql,String[] selectionArgs) {Cursor cursor = selectCursor(sql, selectionArgs);return cursorToList(cursor);}public List<Map<String, Object>> selectList(String sql) {Cursor cursor = selectCursor(sql);return cursorToList(cursor);}public void destroy() {if (sQLiteDatabase != null) {sQLiteDatabase.close();}}} 

数据库的模糊查询：

public class SearchActivity extends Activity implements OnItemClickListener,TextWatcher {private ListView listView;private ContactAdapter contactAdapter;private ArrayList<Contacts> contacts;//联系人列表private ArrayList<Person> persons;//打电话用的列表private List<Map<String, Object>> list = null;//从数据库中获取的list集合private ArrayList<Contacts> list1 = null;//需要加载的数据private MySQLiteOpenHelper mySQLiteOpenHelper=null;private EditText editText=null;private Button bt_cancle;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);requestWindowFeature(Window.FEATURE_NO_TITLE);setContentView(R.layout.search_activity);listView = (ListView) findViewById(R.id.lv_contacts_chaxun_item);editText = (EditText) findViewById(R.id.et_Search);bt_cancle=(Button) findViewById(R.id.bt_search_cancle);contactAdapter = new ContactAdapter(SearchActivity.this, contacts);listView.setAdapter(contactAdapter);listView.setOnItemClickListener(this);editText.addTextChangedListener(this);mySQLiteOpenHelper = new MySQLiteOpenHelper(SearchActivity.this);list1 = new ArrayList<Contacts>();list = mySQLiteOpenHelper.selectList("Select * from contact");LoadData();}//点击取消按钮public void Cancle (View view){finish();}@Overridepublic void onItemClick(AdapterView<?> parent, View view,final int position, long id) {String str1 = persons.get(position).getPhoto().toString();String str2 = persons.get(position).getMobile().toString();String[] sq1 = str1.split(",");String[] sq2 = str2.split(",");String[] sq3 = new String[sq1.length + sq2.length];for (int i = 0; i < sq1.length; i++) {sq3[i] = sq1[i];}for (int j = 0; j < sq2.length; j++) {sq3[sq1.length + j] = sq2[j];}String[] sq4 = new String[sq3.length];int i = 0;int k = 0;for (String s : sq3) {if (!s.equals("null")) {sq4[i] = s;i++;}if (s.equals("null")) {k = 1;}}final String[] phoneNum = new String[sq4.length - k];for (int m = 0; m < sq4.length - k; m++) {phoneNum[m] = sq4[m];}new AlertDialog.Builder(this).setTitle("拨号列表").setItems(phoneNum, new DialogInterface.OnClickListener() {@Overridepublic void onClick(DialogInterface dialog, int which) {// Toast.makeText(this,// "您要播的电话是："+persons.get(which).getPhoto().toString(),// Toast.LENGTH_LONG).show();Intent intent = new Intent();intent.setAction("android.intent.action.CALL");intent.addCategory("android.intent.category.DEFAULT");intent.setData(Uri.parse("tel:" + phoneNum[which]));startActivity(intent);}}).show();}//从数据库加载所有的数据private void LoadData(){contacts = new ArrayList<Contacts>();persons = new ArrayList<Person>();list1.clear();for (int i = 0; i < list.size(); i++) {Contacts c = new Contacts();Person p = new Person();c.setName(list.get(i).get("name").toString());c.setJob(list.get(i).get("job").toString());c.setNumber(list.get(i).get("mobile").toString());c.setPhoto(list.get(i).get("phone").toString());p.setMobile(list.get(i).get("mobile").toString());p.setPhoto(list.get(i).get("phone").toString());list1.add(c);persons.add(p);}contactAdapter.setData(list1);contactAdapter.notifyDataSetChanged();}@Overridepublic void beforeTextChanged(CharSequence s, int start, int count,int after) {// TODO Auto-generated method stub}@Overridepublic void onTextChanged(CharSequence s, int start, int before, int count) {// TODO Auto-generated method stubString str = editText.getText().toString().toUpperCase();//判断字符串的长度 分别在数据库里检索if(str.length()>1){list.clear();list =mySQLiteOpenHelper.selectList("Select * from contact where (headword like'%"+str+"%')or (phone like'%"+str+"%') or (mobile like'%"+str+"%') or( name like'%"+str+"%') or (allword like'%"+str+"%') ");}else if (str.length()==1) {list.clear();list =mySQLiteOpenHelper.selectList("Select * from contact where (headword like'%"+str+"%')or( name like'%"+str+"%')");}else if(str.length()==0){list.clear();list = mySQLiteOpenHelper.selectList("Select * from contact");}LoadData();}@Overridepublic void afterTextChanged(Editable s) {// TODO Auto-generated method stub}}

本人原创，转载请注明出处。