[原]自动补全与sqlite联合的例子
lincyang发表于 2012-01-07 17:59:45
从上一个例子（Android自动补全教程）可以看到自动补全是很简单的，
今天再深入一点，ArrayAdapter提供的字符串从数据库中查询，并且使用MultiAutoCompleteTextView控件。
此控件和AutoCompleteTextView的最大区别是可以补全多个词,看名字就能知道，呵呵。
效果如下，每个词中间用逗号分割。
首先
布局和上一个例子相同。
创建一个名为list_item.xml的XML文件并把它保存在res/layout/文件夹下。编辑文件像下面这样：
<?xml version="1.0" encoding="utf-8"?>
<TextView xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:padding="10dp"
android:textSize="16sp"
android:textColor="#000">
</TextView>
这个文件定义了一个简单的TextView来显示提示列表的每一项。
打开 res/layout/main.xml文件加入如下内容：
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<TextView
android:id="@+id/tv"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/hello" />
<MultiAutoCompleteTextView
android:id="@+id/mactv"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
/>
</LinearLayout>下面就来设计我的数据库，名字为person，要建一个person表，有两个字段：name和gender。
新建一个SQLiteHelper类，继承自SQLiteOpenHelper：
package com.linc.autosqlite.dao;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* 实现对表的创建、更新、变更列名操作
* @author lincyang
*
*/
public class SQLiteHelper extends SQLiteOpenHelper {
public static final String DB_NAME = "person";
public static final int DB_VERSION = 1;
protected static Context ctx;
//
//构造函数一：传context
//
public SQLiteHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
ctx = context;
}
//
//构造函数二
//
public SQLiteHelper() {
super(ctx,DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table person(name varchar(20) not null , " +
"gender varchar(10) not null );";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
protected void closeCursor(Cursor cursor) {
if (cursor != null) {
cursor.close();
}
}
}
这样，构造的时候就为你创建数据库了，在onCreate的时候，数据库的表也建好了。我又创建一个新类AutoSqliteDAO来专门负责处理数据库相关逻辑和事务：
package com.linc.autosqlite.dao;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.linc.autosqlite.meta.Person;
public class AutoSqliteDAO extends SQLiteHelper {
public AutoSqliteDAO(Context context)
{
super(context);
}
/**
* 用contentValues来插入数据
* 这是Android独有的办法，如果你没有sql经验直接学习Android，
* 那么这种方法是适合你的
* @param name
* @param gender
*/
public void insertPerson(String name,String gender)
{
SQLiteDatabase db = getReadableDatabase();
ContentValues cv = new ContentValues();
cv.put("name", name);
cv.put("gender",gender);
db.insert("person",null,cv);
db.close();
}
/**
* 用执行sql语句来插入数据，注意占位符的用法
* 其实这两种办法都不错，如果你善于用sql语句，说明你有这方面的经验，就用这种方法吧
* @param name
* @param gender
*/
public void insertPerson2(String name,String gender)
{
SQLiteDatabase db = getReadableDatabase();
String sql = "INSERT INTO person(name,gender) VALUES (?,?)";
db.execSQL(sql,new Object[]{name,gender});
db.close();
}
public void deletePerson(String name)
{
SQLiteDatabase db = getReadableDatabase();
String whereClause = "name=?";//删除的条件
String[] whereArgs = {name};//删除的条件参数
db.delete("user",whereClause,whereArgs);
db.close();
}
public void deletePerson2(String name)
{
SQLiteDatabase db = getReadableDatabase();
String sql = "DELETE FROM person WHERE name = ?";
db.execSQL(sql,new Object[]{name});//执行SQL语句
db.close();
}
public void updatePerson(String name,String gender)
{
SQLiteDatabase db = getReadableDatabase();
ContentValues cv = new ContentValues();
cv.put("gender",gender);
String whereClause = "name=?";
String[] whereArgs = {name};
db.update("user",cv,whereClause,whereArgs);
db.close();
}
public void updatePerson2(String name,String gender)
{
SQLiteDatabase db = getReadableDatabase();
String sql = "UPDATE [person] SET gender = ? WHERE name = ?";
db.execSQL(sql,new Object[]{gender,name});
db.close();
}
/**
* query的参数很多，下面我列出的应该很容易看懂：
* table：第一个是表名
* columns：第二个是要查询的列名，一个数组。
* selection：第三个是条件，用？占位
* selectionArgs：第四个是条件参数，数组
* groupBy：第五个是分组
* having：第六个是having，筛选满足条件的组
* orderBy：第七个是按递增或递减顺序排序
* limit:第八个是限制返回记录的区域
* @param name
* @return
*/
public ArrayList<Person> queryPerson(String name)
{
Cursor cursor = null;
ArrayList<Person> personList = new ArrayList<Person>();
try {
SQLiteDatabase db = getReadableDatabase();
cursor = db.query("person",new String[]{"name","gender"},
"name = ?",new String[]{name},null,null,null,null);
Person person;
while (cursor.moveToNext()) {
String personName = cursor.getString(0);
String personGender = cursor.getString(1);
person = new Person(personName,personGender);
personList.add(person);
}
db.close();
}
catch(Exception ex)
{
Log.i("linc", "queryPerson------ex is " +ex.getMessage());
}
finally {
closeCursor(cursor);
}
return personList;
}
public ArrayList<String> queryPerson()
{
Cursor cursor = null;
ArrayList<String> nameList = new ArrayList<String>();
String sql = "SELECT * FROM person ";
try {
SQLiteDatabase db = getReadableDatabase();
cursor = db.rawQuery(sql,null);
while (cursor.moveToNext()) {
String personName = cursor.getString(0);
nameList.add(personName);
}
db.close();
}
catch(Exception ex)
{
Log.i("linc", "queryPerson------ex is " +ex.getMessage());
}
finally {
closeCursor(cursor);
}
return nameList;
}
public ArrayList<Person> queryPerson2(String name)
{
Cursor cursor = null;
ArrayList<Person> personList = new ArrayList<Person>();
String sql = "SELECT * FROM person WHERE name = ?";
try {
SQLiteDatabase db = getReadableDatabase();
cursor = db.rawQuery(sql, new String[]{name});
Person person;
while (cursor.moveToNext()) {
String personName = cursor.getString(0);
String personGender = cursor.getString(1);
person = new Person(personName,personGender);
personList.add(person);
}
db.close();
}
catch(Exception ex)
{
Log.i("linc", "queryPerson2------ex is " +ex.getMessage());
}
finally {
closeCursor(cursor);
}
return personList;
}
public ArrayList<Person> queryPerson3(String name)
{
Cursor cursor = null;
ArrayList<Person> personList = new ArrayList<Person>();
String sql = "SELECT * FROM person WHERE name LIKE ?";
try {
SQLiteDatabase db = getReadableDatabase();
cursor = db.rawQuery(sql, new String[]{name});
Person person;
while (cursor.moveToNext()) {
String personName = cursor.getString(0);
String personGender = cursor.getString(1);
person = new Person(personName,personGender);
personList.add(person);
}
db.close();
}
catch(Exception ex)
{
Log.i("linc", "queryPerson3------ex is " +ex.getMessage());
}
finally {
closeCursor(cursor);
}
return personList;
}
}
最后，在Activity开始时我插入了几条数据，那么在文本框中输入时就会自动补全了，就像上图一样。
Activity代码如下：
package com.linc.autosqlite;
import java.util.ArrayList;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.ArrayAdapter;
import android.widget.MultiAutoCompleteTextView;
import com.linc.autosqlite.dao.AutoSqliteDAO;
public class AutoSqliteTestActivity extends Activity {
private MultiAutoCompleteTextView mactv;
AutoSqliteDAO dao;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
mactv = (MultiAutoCompleteTextView)findViewById(R.id.mactv);
dao = new AutoSqliteDAO(this);
init();
ArrayList<String> nameList = dao.queryPerson();
Log.i("linc", "list count is "+nameList.size());
ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
android.R.layout.simple_dropdown_item_1line, nameList);
mactv.setAdapter(adapter);
mactv.setThreshold(1);//从第一个字符开始补全，可选
mactv.setTokenizer(new MultiAutoCompleteTextView.CommaTokenizer());//必须设置的分隔符
}
private void init()
{
dao.insertPerson("张飞", "male");
dao.insertPerson("刘备", "male");
dao.insertPerson("孙尚香", "female");
dao.insertPerson("关羽", "male");
dao.insertPerson("liubei", "male");
dao.insertPerson("dongzhuo", "male");
dao.insertPerson("yuanshao", "male");
}
}怎么样？是不是很简单？