玩转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
/
publicListqueryAll(){
Listhistorys=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;
}
}
三.搜索对话框的布局文件
android:layout_width="match_parent"
android:layout_height="match_parent"
android:minHeight="250dp"
android:orientation="vertical">
android:id="@+id/sv"
android:padding="10dp"
android:background="@color/colorPrimaryDark"
android:layout_width="match_parent"
android:layout_height="wrap_content">
android:id="@+id/lv"
android:layout_width="match_parent"
android:layout_height="match_parent">
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="暂无搜索记录"/>
四.编写功能代码
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;
privateListhistories;
privateCommonAdaptercommonAdapter;
privatefinalintMAX_ITME=5;
privatevoidassignViews(){
et=(EditText)findViewById(R.id.et);
}
@Override
protectedvoidonCreate(@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().setAwww.shanxiwang.netttributes(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(this,histories,R.layout.item_for_search){
@Override
protectedvoidconvertView(CommonViewHoldercommonViewHolder,Historyhistory){
TextViewtv=commonViewHolder.get(R.id.textView);
tv.setText(history.getContent());
}
};
lv.setAdapter(commonAdapter);
notifyAdapter();
}
@Override
publicvoidonClick(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).show();
return;
}
/1.先判断数据库当中有没有这条历史记录，如果有则修改其搜索的时间即可/
Historyhistory=DBManager.getDBManager().queryByContent(inputText);
if(history!=null){
history.setTime(newDate().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=newHistory();
history.setContent(sv.getInputText());
history.setTime(newDate().toString());
longnum=DBManager.getDBManager().insert(history);
if(num!=-1){
Log.d(Contacts.TAG,"插入成功");
}else{
Log.d(Contacts.TAG,"插入失败");
}
}
notifyAdapter();
}
/
更新数据库当中的数据
/
privatevoidnotifyAdapter(){
histories=DBManager.getDBManager().queryAll();
commonAdapter.notifyDataSetChanged(histories);
}
}
|
|
