一些关于历史搜索记录所需要的操作
当需要把历史搜索记录保存到本地时，如果只是简单的保存你可以使用SharedPreferences，不过操作起来就不简单了，下面是关于使用数据库实现历史搜索记录，你可能需要的一些方法
//模糊查询所有数据 queryData(""); //获取总个数 getCount(); //删除id小于10的值 deleteLessToId("10"); //获取前10条数据 getTopTenData(); //获取所有数据 getAllData(); //获取第4行数据的id getThePositionId(4); /** * 查询数据库中的总条数. * @return */ public long getCount(){ SQLiteDatabase db = helper.getReadableDatabase(); Cursor cursor =db.rawQuery("select count(*) from records", null); cursor.moveToFirst(); long reslut=cursor.getLong(0); return reslut; } /** * 插入数据 */ private void insertData(String name) { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("insert into records(name) values('" + name + "')"); db.close(); } /** * 模糊查询数据 */ private void queryData(String tempName) { //desc：代表查询结果倒序排 Cursor cursor = helper.getReadableDatabase().rawQuery( "select id as _id,name from records where name like '%" + tempName + "%' order by id desc ", null); // 创建adapter适配器对象 adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_1, cursor, new String[]{"name"}, new int[]{android.R.id.text1}, CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER); // 设置适配器 lvRecord.setAdapter(adapter); } /** * 检查数据库中是否已经有该条记录,有就删除 */ private void deleteData(String tempName) { Cursor cursor = helper.getReadableDatabase().rawQuery( "select id as _id,name from records where name =?", new String[]{tempName}); //判断是否存在该条数据,存在就删除 if(cursor!=null &&cursor.moveToNext()){ LogUtil.e("已经存在了" + cursor.getString(1)); SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("delete from records where name='" + tempName + "'"); db.close(); } } /** * 清空所有数据 */ private void deleteAllData() { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("delete from records"); db.close(); } /** * 获取前10条数据 */ public void getTopTenData(){ //按照id降序排序后, 再查询前10条 10代表偏移量 Cursor cursor = helper.getWritableDatabase().rawQuery("select * from records order by id desc limit 0,10", null); if(cursor != null){ while(cursor.moveToNext()){ int id=cursor.getColumnIndex("id"); int name = cursor.getColumnIndex("name");//获得下标 LogUtil.e("id=" + cursor.getString(id)); LogUtil.e("name=" + cursor.getString(name)); } cursor.close(); } } /** * 获取所有数据 */ public void getAllData(){ SQLiteDatabase db = helper.getReadableDatabase(); //ASC 升序 DESC 降序 Cursor cursor = db.query("records",null, null, null, null, null, "id DESC");//查询所有数据，按照id 降序排序 while(cursor.moveToNext()){ int id=cursor.getColumnIndex("id"); int name = cursor.getColumnIndex("name"); LogUtil.e("id=" + cursor.getString(id)); LogUtil.e("name=" + cursor.getString(name)); } } /** * 升序排好后, 获取索引为position的值 * @param position 从0开始 */ public String getThePositionId(int position){ SQLiteDatabase db = helper.getReadableDatabase(); //ASC 升序 DESC 降序(默认) Cursor cursor = db.query("records",null, null, null, null, null, "id DESC");//查询所有数据，按照id 降序排序 if(cursor!=null && cursor.moveToPosition(position)){ int id=cursor.getColumnIndex("id"); int name = cursor.getColumnIndex("name"); //LogUtil.e("id=" + cursor.getString(id)); //LogUtil.e("name=" + cursor.getString(name)); return cursor.getString(id); }else{ //LogUtil.e("移动失败,数据量不够"); return null; } } /** * @param id 删除小于该id的数据 */ public void deleteLessToId(String id){ SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("delete from records where id<" + id); db.close(); } /** * 删除指定数据 * @param name 需要删除的内容 */ public void deleteDataToName(String name){ SQLiteDatabase db = helper.getWritableDatabase(); db.delete("records", "name=?", new String[]{name});//删除name为"name"的数据 db.close(); } }
下面我使用的代码如下：
import android.content.Intent; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.text.Editable; import android.text.TextUtils; import android.text.TextWatcher; import android.view.View; import android.widget.AdapterView; import android.widget.CursorAdapter; import android.widget.EditText; import android.widget.ImageView; import android.widget.ListView; import android.widget.SimpleCursorAdapter; import android.widget.TextView; import com.shch.nursingworkers.R; import com.shch.nursingworkers.activity.BaseActivity; import com.shch.nursingworkers.utils.ToastUtils; import com.shch.nursingworkers.utils.data.RecordSQLiteOpenHelper; import com.shch.nursingworkers.utils.shape.ShapeUtil; import com.umeng.analytics.MobclickAgent; import butterknife.BindView; /** * 用户搜索界面 */ public class UserSearchActivity extends BaseActivity { @BindView(R.id.iv_back) ImageView ivBack; @BindView(R.id.et_search) EditText etSearch; @BindView(R.id.tv_search) TextView tvSearch; @BindView(R.id.lv_record) ListView lvRecord; private RecordSQLiteOpenHelper helper; private SimpleCursorAdapter adapter; @Override protected void onResume() { super.onResume(); MobclickAgent.onPageStart(this.getClass().getName()); //统计页面 MobclickAgent.onResume(this); //统计时长 } @Override protected void onPause() { super.onPause(); MobclickAgent.onPageEnd(this.getClass().getName()); MobclickAgent.onPause(this); } @Override public int initResourceLayout() { return R.layout.activity_user_search; } @Override public void initView() { ShapeUtil.roundedCorner(this,"#ffffffff","#ffffffff",20,etSearch); findViewById(R.id.iv_back).setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { finish(); } }); //搜索按钮 findViewById(R.id.tv_search).setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { if (!TextUtils.isEmpty(etSearch.getText().toString())) { String data = etSearch.getText().toString(); deleteData(data);//先删除相同的记录 insertData(data);//再添加 deleteOtherData(9);//只保留10条最新数据，删除其他的 final Intent intent = getIntent(); intent.putExtra("search",etSearch.getText().toString()); setResult(RESULT_OK,intent); finish(); }else{ ToastUtils.showToast("请输入搜索内容"); } } }); //编辑框 etSearch.addTextChangedListener(new TextWatcher() { @Override public void beforeTextChanged(CharSequence s, int start, int count, int after) { } @Override public void onTextChanged(CharSequence s, int start, int before, int count) { if (!TextUtils.isEmpty(etSearch.getText().toString())) { String tempName = etSearch.getText().toString(); // 根据tempName去模糊查询数据库中有没有数据 queryData(tempName); }else{ queryData(""); } } @Override public void afterTextChanged(Editable s) { } }); } @Override public void initData() { helper = new RecordSQLiteOpenHelper(this); lvRecord.setOnItemClickListener(new AdapterView.OnItemClickListener() { @Override public void onItemClick(AdapterView<?> parent, View view, int position, long id) { //获取点击的历史搜索内容 final Intent intent = getIntent(); TextView textView = (TextView) view.findViewById(android.R.id.text1); String name = textView.getText().toString(); intent.putExtra("search", name); setResult(RESULT_OK,intent); finish(); } }); //模糊查询所有数据 queryData(""); } /** * 插入数据 */ private void insertData(String name) { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("insert into records(name) values('" + name + "')"); db.close(); } /** * 模糊查询数据 * 传入空字符串时，可查询所有 */ private void queryData(String tempName) { //desc：代表查询结果倒序排 Cursor cursor = helper.getReadableDatabase().rawQuery( "select id as _id,name from records where name like '%" + tempName + "%' order by id desc ", null); // 创建adapter适配器对象 adapter = new SimpleCursorAdapter(this, android.R.layout.simple_list_item_1, cursor, new String[]{"name"}, new int[]{android.R.id.text1}, CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER); // 设置适配器 lvRecord.setAdapter(adapter); } /** * 检查数据库中是否已经有该条记录,有就删除 */ private void deleteData(String tempName) { Cursor cursor = helper.getReadableDatabase().rawQuery( "select id as _id,name from records where name =?", new String[]{tempName}); //判断是否存在该条数据,存在就删除 if(cursor!=null &&cursor.moveToNext()){ deleteDataOfName(tempName); } } /** * 清空所有数据 */ private void deleteAllData() { SQLiteDatabase db = helper.getWritableDatabase(); db.execSQL("delete from records"); db.close(); } /** * 升序排好后, 获取索引为position的值 然后删除后面的 只保留前面10条数据 * @param position 从0开始 */ public void deleteOtherData(int position){ SQLiteDatabase db = helper.getReadableDatabase(); //ASC 升序 DESC 降序(默认) Cursor cursor = db.query("records",null, null, null, null, null, "id DESC");//查询所有数据，按照id 降序排序 if(cursor!=null && cursor.moveToPosition(position)){ int id=cursor.getColumnIndex("id");//先获取所在列数 int name = cursor.getColumnIndex("name"); //LogUtil.e("id=" + cursor.getString(id) + ",name=" + cursor.getString(name)); db.execSQL("delete from records where id<" + cursor.getString(id) );//删除小于该id的值 db.close(); ToastUtils.showToast("执行了删除"); } } /** * 删除指定数据 * @param name 需要删除的内容 */ public void deleteDataOfName(String name){ SQLiteDatabase db = helper.getWritableDatabase(); db.delete("records", "name=?", new String[]{name});//删除name为"name"的数据 db.close(); } }
数据库帮助类：
import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * 用户搜素历史记录 */ public class RecordSQLiteOpenHelper extends SQLiteOpenHelper { /** * 数据库名 */ private static String name = "user_history_search.db"; /** * 版本号 */ private static Integer version = 1; public RecordSQLiteOpenHelper(Context context) { super(context, name, null, version); } @Override public void onCreate(SQLiteDatabase db) { //表名：records 字段: name 文本类型 与自增的 id db.execSQL("create table records(id integer primary key autoincrement,name text)"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }
效果如下：
如果适配器想自定义：可参考将数据库内容显示到listView-游标适配器
