工具类：数据库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 stub
System.out.println("数据库全路径为 ==="+DB);
sQLiteDatabase=getWritableDatabase();
}
// 它是创建时候的回调
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
Log.i("onCreate", "执行了");
db.execSQL("CREATE TABLE IF NOT EXISTS contact(unit,_id integer PRIMARY KEY AUTOINCREMENT,oid,mobile,name,job,phone,headword,allword);");
}
// 这个是版本更新的回调
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
Log.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);
}
// 这是一个插入的方法，封装了内部访问数据库的insert
public long insert(String table, String nullColumnHack, ContentValues values) {
// insert方法第一个参数是表名，第二个参数是防止空行所提供的列名，第三个参数是要插入的字段对应的键值对
// ContentValues它是一种键值对，可以直接new出来，就像hashmap
// 返回这一行的行号
return sQLiteDatabase.insert(table, nullColumnHack, values);
}
// 这是一个删除的方法，封装了内部访问数据库的delete
public long delete(String table, String whereClause, String[] whereArgs) {
// 第一个参数是表名，第二个参数是条件，第三个参数是替换条件中的占位符
// 返回值是此次操作影响了多少行
return sQLiteDatabase.delete(table, whereClause, whereArgs);
}
// 这是一个更新的方法，封装了内部访问数据库的update
public 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;
@Override
protected 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();
}
@Override
public 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() {
@Override
public 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();
}
@Override
public void beforeTextChanged(CharSequence s, int start, int count,
int after) {
// TODO Auto-generated method stub
}
@Override
public void onTextChanged(CharSequence s, int start, int before, int count) {
// TODO Auto-generated method stub
String 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();
}
@Override
public void afterTextChanged(Editable s) {
// TODO Auto-generated method stub
}
}本人原创，转载请注明出处。
352
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
