玩轉(zhuǎn)SQLite系列SQLite數(shù)據(jù)庫應(yīng)用案例實(shí)現(xiàn)歷史搜索記錄

玩轉(zhuǎn)SQLite系列SQLite數(shù)據(jù)庫應(yīng)用案例實(shí)現(xiàn)歷史搜索記錄一.編寫一個(gè)歷史搜索記錄實(shí)例對(duì)象packagecn.bluemobi.dylan.sqlite;importjava.util.Date;/***搜索記錄的操作對(duì)象*CreatedbyAdministratoron2016-11-20.*/publicclassHistory{/***id主鍵，自增*/privateintid;/***搜索的內(nèi)容*/privateStringcontent;/***搜索的時(shí)間*/privateStringtime;publicintgetId(){returnid;}publicvoidsetId(intid){this.id=id;}publicStringgetContent(){returncontent;}publicvoidsetContent(Stringcontent){this.content=content;}publicStringgetTime(){returntime;}publicvoidsetTime(Stringtime){this.time=time;}}二.編寫一個(gè)操作數(shù)據(jù)庫的管理工具類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;/***數(shù)據(jù)庫操作管理類*CreatedbyAdministratoron2016-11-19.*/publicclassDBManager{privatestaticvolatileDBManagerdbManager;privateSQLiteDatabasesqLiteDatabase;privateDBManager(){openDataBase();createTable();}publicstaticDBManagergetDBManager(){if(dbManager==null){synchronized(DBManager.class){if(dbManager==null){dbManager=newDBManager();}}}returndbManager;}/***數(shù)據(jù)庫名稱*/privatefinalStringDATABASE_NAME="info.db";/***表名*/privatefinalStringTABLE_NAME="history";/***表格所包含的字段*/privateclassHistoryDbColumn{/***字段一id*/publicstaticfinalStringID="id";/***字段二內(nèi)容*/publicstaticfinalStringCONTENT="name";/***字段三時(shí)間*/publicstaticfinalStringTIME="time";}/***1.創(chuàng)建或打開數(shù)據(jù)庫連接**/privatevoidopenDataBase(){FiledataBaseFile=newFile(Environment.getExternalStorageDirectory()+"/sqlite",DATABASE_NAME);if(!dataBaseFile.getParentFile().exists()){dataBaseFile.mkdirs();}sqLiteDatabase=SQLiteDatabase.openOrCreateDatabase(dataBaseFile,null);}/*****2.創(chuàng)建表*/privatevoidcreateTable(){Stringsql="CREATETABLE"+"IFNOTEXISTS"+TABLE_NAME+"("+HistoryDbColumn.ID+"IntegerPRIMARYKEYAUTOINCREMENT,"+HistoryDbColumn.CONTENT+"varchar,"+HistoryDbColumn.TIME+"datetime)";sqLiteDatabase.execSQL(sql);}/***插入一條數(shù)據(jù)**@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;}/***根據(jù)id刪除一條數(shù)據(jù)**@paramid*@return*/publiclongdelete(intid){longnum=sqLiteDatabase.delete(TABLE_NAME,HistoryDbColumn.ID+"=?",newString[]{String.valueOf(id)});returnnum;}/***根據(jù)id修改一條數(shù)據(jù)**@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;}/***根據(jù)id查詢一條數(shù)據(jù)**@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;}/***根據(jù)id查詢一條數(shù)據(jù)*倒序**@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;}/***根據(jù)內(nèi)容查詢一條數(shù)據(jù)**@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;}}三.搜索對(duì)話框的布局文件<?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應(yīng)用案例實(shí)現(xiàn)搜索記錄*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應(yīng)用案例實(shí)現(xiàn)搜索記錄");setContentView(R.layout.ac_search);assignViews();intiDialog();addListener();initData();}/***添加按鈕監(jiān)聽*/privatevoidaddListener(){et.setOnClickListener(this);}/****初始化搜索對(duì)話框*/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);}/***初始化數(shù)據(jù)*/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;}}/***點(diǎn)擊搜索按鈕新增一條記錄*/privatevoidaddHistory(){StringinputText=sv.getInputText();if(inputText.isEmpty()){Toast.makeText(this,"請輸入內(nèi)容進(jìn)行搜索",Toast.LENGTH_SHORT