Android 数据存储和IO——SQLite(三)

该实例是用户将自己不熟悉的单词添加到数据库中，当用户需要查询某个单词或是解释时，在相应的文本框中输入相应的关键词，就会出来相应的结果。

本实例主要是用来学习，只是实现了简单的添加和模糊查询，提供一条思路。（很多Bolg上都有类似的例子，如有雷同，纯属巧合）

上代码：

1. 布局文件的主界面:dict_sqlite.xml

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent"> <EditText android:id="@+id/word" android:layout_width="fill_parent" android:layout_height="wrap_content" android:focusable="true" android:hint="生词" /> <EditText android:id="@+id/detail" android:layout_width="fill_parent" android:layout_height="wrap_content" android:hint="生词解释" /> <Button android:id="@+id/insert" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="添加生词" /> <EditText android:id="@+id/key" android:layout_width="fill_parent" android:layout_height="wrap_content" /> <Button android:id="@+id/search" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="查找 " /> </LinearLayout>

2.查询结果的ListView的布局文件：popup.xml

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent"> <ListView android:id="@+id/show_dict" android:layout_width="fill_parent" android:layout_height="fill_parent" /> </LinearLayout>

3. 查询结果中ListView中适配器的布局文件：line.xml

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent"> <TextView android:id="@+id/textView_Result_Dict" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="找到的单词" /> <EditText android:id="@+id/word_result_dict" android:layout_width="fill_parent" android:layout_height="wrap_content" /> <TextView android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="解释" /> <EditText android:id="@+id/detail_result_dict" android:layout_width="fill_parent" android:layout_height="wrap_content" /> </LinearLayout>

4. 主程序的代码：

(1.) 自定义的SQLiteOpenHelper的子类来管理数据库的连接：MyDatabaseHelper.java:

package com.infy.configuration; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteDatabase.CursorFactory; public class MyDatabaseHelper extends SQLiteOpenHelper{ final String CREATE_TABLE_SQL="create table dict(_id integer primary key autoincrement,word,detail)"; public MyDatabaseHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); // TODO Auto-generated constructor stub } @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub //第一个使用数据库时自动建表 db.execSQL(CREATE_TABLE_SQL); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } }

(2.) 主界面的代码：Dict.java:

package com.infy.configuration; import java.util.ArrayList; import java.util.HashMap; import java.util.Map; import android.app.Activity; import android.content.Intent; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.os.Bundle; import android.view.View; import android.view.View.OnClickListener; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; public class Dict extends Activity{ MyDatabaseHelper dbHelper; Button insert= null; Button search = null; @Override protected void onCreate(Bundle savedInstanceState) { // TODO Auto-generated method stub super.onCreate(savedInstanceState); setContentView(R.layout.dict_sqlite); //创建MyDatabaseHelper对象，指定数据库版本为1，此处使用相对路径即可数据库文件会自动保存在程序的数据文件夹的database目录下 dbHelper = new MyDatabaseHelper(this, "my_Dict.db3", null, 1); insert = (Button)findViewById(R.id.insert); search = (Button)findViewById(R.id.search); final EditText word = (EditText)findViewById(R.id.word); final EditText detail =(EditText)findViewById(R.id.detail); final EditText key=(EditText)findViewById(R.id.key); //插入生词的操作 insert.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub //获取用户的输入 String word_user= word.getText().toString(); String detail_word=detail.getText().toString(); //插入生词记录 insertData(dbHelper.getReadableDatabase(),word_user,detail_word); //提示信息 Toast.makeText(Dict.this, "添加生词成功！", Toast.LENGTH_LONG).show(); } }); //收索生词的记录 search.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub //获取用户的输入 String key_user = key.getText().toString(); //执行查询操作 Cursor cursor = dbHelper.getReadableDatabase().rawQuery("select *from dict where word like ? or detail like ?", new String[]{ "%" +key_user +"%" ,"%" +key +"%"}); //创建一个Bundle对象 Bundle data = new Bundle(); data.putSerializable("data", converCursorToList(cursor)); //创建一个Intent Intent intent = new Intent(Dict.this,ResultActivity.class); intent.putExtras(data); startActivity(intent); } }); } //把cursor转化成list protected ArrayList<Map<String, String> > converCursorToList(Cursor cursor){ ArrayList<Map<String, String>> result = new ArrayList<Map<String,String>>(); //遍历Cursor结果集 while(cursor.moveToNext()){ //将结果集集中地数据存入Arraylist中 Map<String,String> map = new HashMap<String, String>(); map.put("word", cursor.getString(1)); map.put("detail", cursor.getString(2)); result.add(map); } return result; } private void insertData(SQLiteDatabase db,String word,String detail){ //执行插入语句 db.execSQL("insert into dict values(null,?,?)",new String[]{word,detail}); } @Override protected void onDestroy() { // TODO Auto-generated method stub super.onDestroy(); //退出程序时关闭MyDatabaseHelper里的SQLiterDatabse if(dbHelper != null){ dbHelper.close(); } } }

(3.)查询结果的代码：ResultActivity.java

package com.infy.configuration; import java.util.List; import java.util.Map; import android.app.Activity; import android.content.Intent; import android.os.Bundle; import android.widget.ListView; import android.widget.SimpleAdapter; public class ResultActivity extends Activity { @Override protected void onCreate(Bundle savedInstanceState) { // TODO Auto-generated method stub super.onCreate(savedInstanceState); setContentView(R.layout.popup); ListView listView = (ListView)findViewById(R.id.show_dict); Intent intent = getIntent(); //获取该Intent中的数据 Bundle data = intent.getExtras(); //从Bundle数据包中获取数据 @SuppressWarnings("unchecked") List<Map<String, String>> list = (List<Map<String,String>>)data.getSerializable("data"); //将List封装成SimpleAdapter SimpleAdapter adapter = new SimpleAdapter(ResultActivity.this,list,R.layout.line,new String[]{"word","detail"},new int[]{R.id.word_result_dict,R.id.detail_result_dict}); //填充ListView listView.setAdapter(adapter); } }

上面的ResultActivity设为了对话框风格的Activitiy.以对话框的方式来显示查询结果。

清单文件AndroidManifest.xml为：

<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.infy.configuration" android:versionCode="1" android:versionName="1.0"> <application android:icon="@drawable/icon" android:label="@string/app_name"> <activity android:name=".Dict" android:label="@string/app_name"> <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> <activity android:name=".ResultActivity" android:theme="@android:style/Theme.Dialog"/> </application> <uses-sdk android:minSdkVersion="8" /> <uses-permission android:name ="android.permission.INTERENT"/> </manifest>

结果图：

(1.)添加生词的界面：

(2.))查询结果的界面：在查找按钮上面的文本框中输入P字母，点击查找按钮将出现如下查询结果的界面：