【玩转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; } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 二.编写一个操作数据库的管理工具类 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.getTime()); long num = sqliteDatabase.update(TABLE_NAME,contentValues,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,new String[]{String.valueOf(id)},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,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,HistoryDbColumn.CONTENT + "=?",new String[]{content},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; } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 三.搜索对话框的布局文件 <?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> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 四.编写功能代码 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; } } [url]http://mp.weixin.qq.com/s?__biz=MzIxNjY4MTgyNQ==&tempkey=5lh0a7qW[url]http://mp.weixin.qq.com/s/QN4ZGOuGcDeOuFWbC6aeFQ[/url][/url] [/url][/url]http://mp.weixin.qq.com/s?__biz=MzIxNjY4MTgyNQ==&tempkey=5lh0a7qWmqqNMpgubVo6zMpG%2Fpe8Jw7YRKEMqE4Fl7NbuIq%2BwOfYSQEB44oCwv0Cj6HxXRNmSOoNDrD9%2F8WoPnkLMW1%2BWcz9HtZ9ytp136nAgrF7V%2ByzG0jupCghFx5OUZWtmyyhsey0cEzFk5WsBQ%3D%3D&chksm=17841d0f20f394193a92974082356032a0f27f337c60ed8cf140a31dd22b005ac0b505b9cf97#rd[/url][/url] [/url][/url]http://mp.weixin.qq.com/s?__biz=MzIxNjY4MTgyNQ==&tempkey=5lh0a7qWhttp://mp.weixin.qq.com/s/QN4ZGOuGcDeOuFWbC6aeFQ[/url][/url] [/url][/url]http://mp.weixin.qq.com/s?__biz=MzIxNjY4MTgyNQ==&mid=2247483651&idx=1&sn=0ab6116dbc93d0c0e51c227e488d0234&chksm=97841d0da0f3941b216e255af8c4b247c8b378750dac9ec2663dd0427057070f0297e218bc52#rd[/url][/url] [/url][/url]http://mp.weixin.qq.com/s?__biz=MzIxNjY4MTgyNQ==&tempkey=5lh0a7qWmqqNMpgubVo6zMpG%2Fpe8Jw7YRKEMqE4Fl7NbuIq%2BwOfYSQEB44oCwv0Cj6HxXRNmSOoNDrD9%2F8WoPnkLMW1%2BWcz9HtZ9ytp136nAgrF7V%2ByzG0jupCghFx5OUZWtmyyhsey0cEzFk5WsBQ%3D%3D&chksm=17841d0f20f394193a92974082356032a0f27f337c60ed8cf140a31dd22b005ac0b505b9cf97#rd[/url][/url] /** * 点击搜索按钮新增一条记录 */ 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); }