IT技术互动交流平台

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

来源:IT165收集  发布日期:2016-11-28 21:01:34

出处: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  https://github.com/linglongxin24/SQLite

Tag标签: 案例   数据库   历史  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规