自动补全与sqlite联合的例子

从上一个例子（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_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",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,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);
            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,"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,"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");
    }
}

怎么样？是不是很简单？原文链接：https://www.f2er.com/sqlite/202283.html

自动补全与sqlite联合的例子

猜你在找的Sqlite相关文章