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

玩转SQLite系列SQLite数据库应用案例实现历史搜索记录一.编写一个历史搜索记录实例对象packagecn.bluemobi.dylan.sqlite;importjava.util.Date;/***搜索记录的操作对象*CreatedbyAdministratoron2016-11-20.*/publicclassHistory{/***id主键，自增*/privateintid;/***搜索的内容*/privateStringcontent;/***搜索的时间*/privateStringtime;publicintgetId(){returnid;}publicvoidsetId(intid){this.id=id;}publicStringgetContent(){returncontent;}publicvoidsetContent(Stringcontent){this.content=content;}publicStringgetTime(){returntime;}publicvoidsetTime(Stringtime){this.time=time;}}二.编写一个操作数据库的管理工具类packagecn.bluemobi.dylan.sqlite;importandroid.content.ContentValues;importandroid.database.Cursor;importandroid.database.sqlite.SQLiteDatabase;importandroid.os.Environment;importjava.io.File;importjava.util.ArrayList;importjava.util.List;/***数据库操作管理类*CreatedbyAdministratoron2016-11-19.*/publicclassDBManager{privatestaticvolatileDBManagerdbManager;privateSQLiteDatabasesqLiteDatabase;privateDBManager(){openDataBase();createTable();}publicstaticDBManagergetDBManager(){if(dbManager==null){synchronized(DBManager.class){if(dbManager==null){dbManager=newDBManager();}}}returndbManager;}/***数据库名称*/privatefinalStringDATABASE_NAME="info.db";/***表名*/privatefinalStringTABLE_NAME="history";/***表格所包含的字段*/privateclassHistoryDbColumn{/***字段一id*/publicstaticfinalStringID="id";/***字段二内容*/publicstaticfinalStringCONTENT="name";/***字段三时间*/publicstaticfinalStringTIME="time";}/***1.创建或打开数据库连接**/privatevoidopenDataBase(){FiledataBaseFile=newFile(Environment.getExternalStorageDirectory()+"/sqlite",DATABASE_NAME);if(!dataBaseFile.getParentFile().exists()){dataBaseFile.mkdirs();}sqLiteDatabase=SQLiteDatabase.openOrCreateDatabase(dataBaseFile,null);}/*****2.创建表*/privatevoidcreateTable(){Stringsql="CREATETABLE"+"IFNOTEXISTS"+TABLE_NAME+"("+HistoryDbColumn.ID+"IntegerPRIMARYKEYAUTOINCREMENT,"+HistoryDbColumn.CONTENT+"varchar,"+HistoryDbColumn.TIME+"datetime)";sqLiteDatabase.execSQL(sql);}/***插入一条数据**@paramhistory*@return*/publiclonginsert(Historyhistory){ContentValuescontentValues=newContentValues();contentValues.put(HistoryDbColumn.CONTENT,history.getContent());contentValues.put(HistoryDbColumn.TIME,history.getTime());longnum=sqLiteDatabase.insert(TABLE_NAME,null,contentValues);returnnum;}/***根据id删除一条数据**@paramid*@return*/publiclongdelete(intid){longnum=sqLiteDatabase.delete(TABLE_NAME,HistoryDbColumn.ID+"=?",newString[]{String.valueOf(id)});returnnum;}/***根据id修改一条数据**@paramid*@return*/publiclongupdate(Historyhistory,intid){ContentValuescontentValues=newContentValues();contentValues.put(HistoryDbColumn.CONTENT,history.getContent());contentValues.put(HistoryDbColumn.TIME,history.getTime());longnum=sqLiteDatabase.update(TABLE_NAME,contentValues,HistoryDbColumn.ID+"=?",newString[]{String.valueOf(id)});returnnum;}/***根据id查询一条数据**@paramid*@return*/publicHistoryqurey(intid){Historyhistory=null;Cursorcursor=sqLiteDatabase.query(TABLE_NAME,null,HistoryDbColumn.ID+"=?",newString[]{String.valueOf(id)},null,null,null);if(cursor!=null){if(cursor.moveToNext()){history=newHistory();history.setId(cursor.getInt(cursor.getColumnIndex(HistoryDbColumn.ID)));history.setContent(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.CONTENT)));history.setTime(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.TIME)));}}returnhistory;}/***根据id查询一条数据*倒序**@return*/publicList<History>queryAll(){List<History>historys=newArrayList<>();Cursorcursor=sqLiteDatabase.query(TABLE_NAME,null,null,null,null,null,HistoryDbColumn.TIME+"desc");if(cursor!=null){while(cursor.moveToNext()){Historyhistory=newHistory();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);}}returnhistorys;}/***根据内容查询一条数据**@return*/publicHistoryqueryByContent(Stringcontent){Historyhistory=null;Cursorcursor=sqLiteDatabase.query(TABLE_NAME,null,HistoryDbColumn.CONTENT+"=?",newString[]{content},null,null,null);if(cursor!=null){if(cursor.moveToNext()){history=newHistory();history.setId(cursor.getInt(cursor.getColumnIndex(HistoryDbColumn.ID)));history.setContent(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.CONTENT)));history.setTime(cursor.getString(cursor.getColumnIndex(HistoryDbColumn.TIME)));}}returnhistory;}}三.搜索对话框的布局文件<?xmlversion="1.0"encoding="utf-8"?><LinearLayoutxmlns: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>四.编写功能代码packagecn.bluemobi.dylan.sqlite;importandroid.app.Dialog;importandroid.os.Bundle;importandroid.support.annotation.Nullable;importandroid.support.v7.app.AppCompatActivity;importandroid.util.Log;importandroid.view.Gravity;importandroid.view.View;importandroid.view.WindowManager;importandroid.widget.Button;importandroid.widget.EditText;importandroid.widget.ListView;importandroid.widget.TextView;importandroid.widget.Toast;importjava.util.Date;importjava.util.List;importcn.bluemobi.dylan.sqlite.adapter.CommonAdapter;importcn.bluemobi.dylan.sqlite.adapter.CommonViewHolder;/***SQLite应用案例实现搜索记录*CreatedbyAdministratoron2016-11-20.*/publicclassSearchActivityextendsAppCompatActivityimplementsView.OnClickListener{privateEditTextet;privateListViewlv;privateTextViewtv;privateDialogdialog;privateSearchViewsv;privateButtonbt;privateList<History>histories;privateCommonAdapter<History>commonAdapter;privatefinalintMAX_ITME=5;privatevoidassignViews(){et=(EditText)findViewById(R.id.et);}@OverrideprotectedvoidonCreate(@NullableBundlesavedInstanceState){super.onCreate(savedInstanceState);getSupportActionBar().setTitle("SQLite应用案例实现搜索记录");setContentView(R.layout.ac_search);assignViews();intiDialog();addListener();initData();}/***添加按钮监听*/privatevoidaddListener(){et.setOnClickListener(this);}/****初始化搜索对话框*/privatevoidintiDialog(){dialog=newDialog(this,R.style.Dialog_FullScreen);dialog.setContentView(R.layout.dialog_search);dialog.getWindow().setGravity(Gravity.TOP);dialog.setCanceledOnTouchOutside(true);dialog.setCancelable(true);WindowManager.LayoutParamslp=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);}/***初始化数据*/privatevoidinitData(){commonAdapter=newCommonAdapter<History>(this,histories,R.layout.item_for_search){@OverrideprotectedvoidconvertView(CommonViewHoldercommonViewHolder,Historyhistory){TextViewtv=commonViewHolder.get(R.id.textView);tv.setText(history.getContent());}};lv.setAdapter(commonAdapter);notifyAdapter();}@OverridepublicvoidonClick(Viewv){switch(v.getId()){caseR.id.et:if(!dialog.isShowing()){dialog.show();}break;caseR.id.bt:addHistory();break;}}/***点击搜索按钮新增一条记录*/privatevoidaddHistory(){StringinputText=sv.getInputText();if(inputText.isEmpty()){Toast.makeText(this,"请输入内容进行搜索",Toast.LENGTH_SHORT