玩转SQLite系列SQLite数据库应用案例实现历史搜索记录一.编写一个历史搜索记录实例对象package cn.bluemobi.dylan.sqlite;import java.util.Date;/*** 搜索记录的操作对象* Created by Administrator on 2016-11-20.*/public class History {/*** id 主键，自增*/private int id;/*** 搜索的内容*/private String content;/*** 搜索的时间*/private String time;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getContent() {return content;}public void setContent(String content) {this.content = content;}public String getTime() {return time;}public void setTime(String time) {this.time = time;}}二.编写一个操作数据库的管理工具类package cn.bluemobi.dylan.sqlite;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Environment;import java.io.File;import java.util.ArrayList;import java.util.List;/*** 数据库操作管理类* Created by Administrator on 2016-11-19.*/public class DBManager {private static volatile DBManager dbManager;private SQLiteDatabase sqLiteDatabase;private DBManager() {openDataBase();createTable();}public static DBManager getDBManager() {if (dbManager == null) {synchronized (DBManager.class) {if (dbManager == null) {dbManager = new DBManager();}}}return dbManager;}/*** 数据库名称*/private final String DA TABASE_NAME = "info.db";/*** 表名*/private final String TABLE_NAME = "history";/*** 表格所包含的字段*/private class HistoryDbColumn {/*** 字段一id*/public static final String ID = "id";/*** 字段二内容*/public static final String CONTENT = "name";/*** 字段三时间*/public static final String TIME = "time";}/*** 1.创建或打开数据库连接**/private void openDataBase() {File dataBaseFile = new File(Environment.getExternalStorageDirectory() + "/sqlite", DA TABASE_NAME);if (!dataBaseFile.getParentFile().exists()) {dataBaseFile.mkdirs();}sqLiteDatabase = SQLiteDatabase.openOrCreateDatabase(dataBaseFile, null);}/***** 2.创建表*/private void createTable() {String sql = "CREATE TABLE " +"IF NOT EXISTS " +TABLE_NAME + "(" +HistoryDbColumn.ID + " Integer PRIMARY KEY AUTOINCREMENT," +HistoryDbColumn.CONTENT + " varchar," +HistoryDbColumn.TIME + " datetime)";sqLiteDatabase.execSQL(sql);}/*** 插入一条数据** @param history* @return*/public long insert(History history) {ContentV alues contentValues = new ContentValues();contentValues.put(HistoryDbColumn.CONTENT, history.getContent());contentValues.put(HistoryDbColumn.TIME, history.getTime());long num = sqLiteDatabase.insert(TABLE_NAME, null, contentValues);return num;}/*** 根据id删除一条数据** @param id* @return*/public long delete(int id) {long num = sqLiteDatabase.delete(TABLE_NAME, HistoryDbColumn.ID + "=?", new String[]{String.valueOf(id)});return num;}/*** 根据id修改一条数据** @param id* @return*/public long update(History history, int id) {ContentV alues contentValues = new ContentValues();contentValues.put(HistoryDbColumn.CONTENT, history.getContent());contentValues.put(HistoryDbColumn.TIME, history.getTime());long num = sqLiteDatabase.update(TABLE_NAME, contentValues, HistoryDbColumn.ID + "=?", new String[]{String.valueOf(id)});return num;}/*** 根据id查询一条数据** @param id* @return*/public History qurey(int id) {History history = null;Cursor cursor = sqLiteDatabase.query(TABLE_NAME, null, HistoryDbColumn.ID + "=?", new String[]{String.valueOf(id)}, null, null, null);if (cursor != null) {if (cursor.moveToNext()) {history = new History();history.setId(cursor.getInt(cursor.getColumnIndex(HistoryDbColumn.ID))); history.setContent(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.CONTENT)));history.setTime(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.TIME)));}}return history;}/*** 根据id查询一条数据* 倒序** @return*/public List<History> queryAll() {List<History> historys = new ArrayList<>();Cursor cursor = sqLiteDatabase.query(TABLE_NAME, null, null, null, null, null, HistoryDbColumn.TIME + " desc");if (cursor != null) {while (cursor.moveToNext()) {History history = new History();history.setId(cursor.getInt(cursor.getColumnIndex(HistoryDbColumn.ID)));history.setContent(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.CONTENT)));history.setTime(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.TIME)));historys.add(history);}}return historys;}/*** 根据内容查询一条数据** @return*/public History queryByContent(String content) {History history = null;Cursor cursor = sqLiteDatabase.query(TABLE_NAME, null, HistoryDbColumn.CONTENT + "=?", new String[]{content}, null, null, null);if (cursor != null) {if (cursor.moveToNext()) {history = new History();history.setId(cursor.getInt(cursor.getColumnIndex(HistoryDbColumn.ID))); history.setContent(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.CONTENT)));history.setTime(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.TIME)));}}return history;}}三.搜索对话框的布局文件<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="/apk/res/android"android:layout_width="match_parent"android:layout_height="match_parent"android:minHeight="250dp"android:orientation="vertical"><cn.bluemobi.dylan.sqlite.SearchViewandroid:id="@+id/sv"android:padding="10dp"android:background="@color/colorPrimaryDark"android:layout_width="match_parent"android:layout_height="wrap_content"></cn.bluemobi.dylan.sqlite.SearchView><ListViewandroid:id="@+id/lv"android:layout_width="match_parent"android:layout_height="match_parent"></ListView><TextViewandroid:id="@+id/tv"android:layout_gravity="center"android:gravity="center"android:layout_weight="1"android:layout_width="match_parent"android:layout_height="match_parent"android:text="暂无搜索记录" /></LinearLayout>四.编写功能代码package cn.bluemobi.dylan.sqlite;import android.app.Dialog;import android.os.Bundle;import android.support.annotation.Nullable;import android.support.v7.app.AppCompatActivity;import android.util.Log;import android.view.Gravity;import android.view.View;import android.view.WindowManager;import android.widget.Button;import android.widget.EditText;import android.widget.ListView;import android.widget.TextView;import android.widget.Toast;import java.util.Date;import java.util.List;import monAdapter;import monViewHolder;/*** SQLite应用案例实现搜索记录* Created by Administrator on 2016-11-20.*/public class SearchActivity extends AppCompatActivity implements View.OnClickListener { private EditText et;private ListView lv;private TextView tv;private Dialog dialog;private SearchView sv;private Button bt;private List<History> histories;private CommonAdapter<History> commonAdapter;private final int MAX_ITME = 5;private void assignViews() {et = (EditText) findViewById(R.id.et);}@Overrideprotected void onCreate(@Nullable Bundle savedInstanceState) {super.onCreate(savedInstanceState);getSupportActionBar().setTitle("SQLite应用案例实现搜索记录");setContentView(yout.ac_search);assignViews();intiDialog();addListener();initData();}/*** 添加按钮监听*/private void addListener() {et.setOnClickListener(this);}/**** 初始化搜索对话框*/private void intiDialog() {dialog = new Dialog(this, R.style.Dialog_FullScreen);dialog.setContentView(yout.dialog_search);dialog.getWindow().setGravity(Gravity.TOP);dialog.setCanceledOnTouchOutside(true);dialog.setCancelable(true);youtParams lp = dialog.getWindow().getAttributes();lp.width = youtParams.MATCH_PARENT;lp.height = youtParams.WRAP_CONTENT;dialog.getWindow()ttributes(lp);lv = (ListView) dialog.findViewById(R.id.lv);tv = (TextView) dialog.findViewById();sv = (SearchView) dialog.findViewById(R.id.sv);bt = (Button) dialog.findViewById(R.id.bt);bt.setOnClickListener(this);lv.setEmptyView(tv);}/*** 初始化数据*/private void initData() {commonAdapter = new CommonAdapter<History>(this, histories, yout.item_for_search) {@Overrideprotected void convertView(CommonViewHolder commonViewHolder, History history) {TextView tv = commonViewHolder.get(R.id.textView);tv.setText(history.getContent());}};lv.setAdapter(commonAdapter);notifyAdapter();}@Overridepublic void onClick(View v) {switch (v.getId()) {case R.id.et:if (!dialog.isShowing()) {dialog.show();}break;case R.id.bt:addHistory();break;}}/*** 点击搜索按钮新增一条记录*/private void addHistory() {String inputText = sv.getInputText();if (inputText.isEmpty()) {Toast.makeText(this, "请输入内容进行搜索", Toast.LENGTH_SHORT).show();return;}/**1.先判断数据库当中有没有这条历史记录，如果有则修改其搜索的时间即可*/ History history = DBManager.getDBManager().queryByContent(inputText);if (history != null) {history.setTime(new Date().toString());DBManager.getDBManager().update(history, history.getId());} else {/**2.判断搜索记录是否达到限值,达到极限则删除一条数据**/if (histories != null && histories.size() == MAX_ITME) {DBManager.getDBManager().delete(histories.get(histories.size() - 1).getId());}/**3.插入一条数据**/history = new History();history.setContent(sv.getInputText());history.setTime(new Date().toString());long num = DBManager.getDBManager().insert(history);if (num != -1) {Log.d(Contacts.TAG, "插入成功");} else {Log.d(Contacts.TAG, "插入失败");}}notifyAdapter();}/*** 更新数据库当中的数据*/private void notifyAdapter() {histories = DBManager.getDBManager().queryAll();commonAdapter.notifyDataSetChanged(histories);}}。