转载请注明出处：http://blog.csdn.net/linglongxin24/article/details/53366564
本文出自【DylanAndroid的博客】

• 【玩转SQLite系列】文章目录
  
  • 【玩转SQLite系列】(一)初识SQLite,重拾sql语句
  • 【玩转SQLite系列】(二)SQLite创建和打开数据库的三种方式
  • 【玩转SQLite系列】(三)通过sql语句操作SQLite数据库
  • 【玩转SQLite系列】(四)通过Android提供的API操作SQLite数据库
  • 【玩转SQLite系列】(五)SQLite数据库优化
  • 【玩转SQLite系列】(六)SQLite数据库应用案例实现历史搜索记录
  • 【玩转SQLite系列】(七)SQLite数据库轻量级ORM操作数据库工具类
    

【玩转SQLite系列】(六)SQLite数据库应用案例实现历史搜索记录

前面通过一系列的文章讲述了SQLite的各种使用场景，那么我们用一个实际的案例去实现一个搜索历史记录的功能。
这里面用到了以下内容：

【Android自定义View实战】之自定义超简单SearchView搜索框

Android宽度全屏的Dialog和DialogFragment用法

Java泛型应用之打造Android万能ViewHolder-超简洁写法

Java泛型应用之打造Android中ListView和GridView万能适配器【CommonAdapter】–超简洁写法

不了解的可以去学习一下。

一.编写一个历史搜索记录实例对象

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 DATABASE_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", DATABASE_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) {
        ContentValues 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) {
        ContentValues 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="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:minHeight="250dp"
    android:orientation="vertical">

    <cn.bluemobi.dylan.sqlite.SearchView
        android: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>

    <ListView
        android:id="@+id/lv"
        android:layout_width="match_parent"
        android:layout_height="match_parent"></ListView>

    <TextView
        android: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 cn.bluemobi.dylan.sqlite.adapter.CommonAdapter;
import cn.bluemobi.dylan.sqlite.adapter.CommonViewHolder;

/**
 * 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);
    }

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        getSupportActionBar().setTitle("SQLite应用案例实现搜索记录");
        setContentView(R.layout.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(R.layout.dialog_search);
        dialog.getWindow().setGravity(Gravity.TOP);
        dialog.setCanceledOnTouchOutside(true);
        dialog.setCancelable(true);
        WindowManager.LayoutParams lp = dialog.getWindow().getAttributes();
        lp.width = WindowManager.LayoutParams.MATCH_PARENT;
        lp.height = WindowManager.LayoutParams.WRAP_CONTENT;
        dialog.getWindow().setAttributes(lp);
        lv = (ListView) dialog.findViewById(R.id.lv);
        tv = (TextView) dialog.findViewById(R.id.tv);
        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, R.layout.item_for_search) {
            @Override
            protected void convertView(CommonViewHolder commonViewHolder, History history) {
                TextView tv = commonViewHolder.get(R.id.textView);
                tv.setText(history.getContent());
            }
        };
        lv.setAdapter(commonAdapter);
        notifyAdapter();
    }

    @Override
    public 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);
    }
}

五.GitHub