안드로이드 db 검색기능 구현중인데 SearchView에 Input을 해도 검색결과가 나오지 않습니다.


DatabaseHelper.java

package com.example.cracking.dbtenc;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper{

    private static final String TAG = DatabaseHelper.class.getSimpleName();

    // database configuration
    // if you want the onUpgrade to run then change the database_version
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "dbtenc.db";

    // table configuration
    private static final String TABLE_NAME = "user_table";         // Table name
    public static final String USER_TABLE_COLUMN_ID = "_id";     // a column named "_id" is required for cursor
    public static final String USER_TABLE_COLUMN_NAME = "user_name";
    public static final String USER_TABLE_COLUMN_TEL = "user_tel";
    public static final String SEARCH_KEY = "searchData";

    private DatabaseOpenHelper openHelper;
    private SQLiteDatabase database;

    // this is a wrapper class. that means, from outside world, anyone will communicate with PersonDatabaseHelper,
    // but under the hood actually DatabaseOpenHelper class will perform database CRUD operations
    public DatabaseHelper(Context aContext) {

        openHelper = new DatabaseOpenHelper(aContext);
        database = openHelper.getWritableDatabase();
    }

    public void insertData (String aUserName, String aUserTel) {

        // we are using ContentValues to avoid sql format errors

        ContentValues contentValues = new ContentValues();
        String searchValue = aUserName+" "+aUserTel;

        contentValues.put(USER_TABLE_COLUMN_NAME, aUserName);
        contentValues.put(USER_TABLE_COLUMN_TEL, aUserTel);
        contentValues.put(SEARCH_KEY,searchValue);
        database.insert(TABLE_NAME, null, contentValues);
    }

    public void delete(String name){
        try {
            database = openHelper.getWritableDatabase();
            database.delete(TABLE_NAME, USER_TABLE_COLUMN_NAME + "=?", new String[]{name});
            Log.i("helper","deleted");
        }catch (Exception e){
            Log.i("helper","faild");
        }
    }

    public Cursor getAllData () {

        String buildSQL = "SELECT * FROM " + TABLE_NAME+" ORDER BY "+USER_TABLE_COLUMN_NAME+" ASC";

        Log.d(TAG, "getAllData SQL: " + buildSQL);

        return database.rawQuery(buildSQL, null);
    }

    public void update_byID(int id, String v1, String v2){
        ContentValues contentValues = new ContentValues();
        String searchValue =v1+" "+v2;

        contentValues.put(USER_TABLE_COLUMN_NAME,v1);
        contentValues.put(USER_TABLE_COLUMN_TEL,v2);
        contentValues.put(SEARCH_KEY,searchValue);
        database.update(TABLE_NAME,contentValues,USER_TABLE_COLUMN_ID+"="+id,null);
    }

    //이쪽이 문제입니다.
    public Cursor searchUser(String inputText) throws SQLException{
        Log.w(TAG,inputText);
        String query = "SELECT _id as "+USER_TABLE_COLUMN_ID+","+USER_TABLE_COLUMN_NAME+","+USER_TABLE_COLUMN_TEL+" from "+TABLE_NAME+" where "+SEARCH_KEY+" LIKE '"+inputText+"';";
        Log.w(TAG,query);
        Cursor mCursor = database.rawQuery(query,null);

        if(mCursor!=null){
            mCursor.moveToFirst();
        }
        return mCursor;
    }


    // this DatabaseOpenHelper class will actually be used to perform database related operation

    private class DatabaseOpenHelper extends SQLiteOpenHelper {

        public DatabaseOpenHelper(Context aContext) {
            super(aContext, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase sqLiteDatabase) {
            // Create your tables here

            String buildSQL = "CREATE TABLE " + TABLE_NAME + "( " + USER_TABLE_COLUMN_ID + " INTEGER PRIMARY KEY, " +
                    USER_TABLE_COLUMN_NAME + " TEXT, " + USER_TABLE_COLUMN_TEL + " TEXT, "+SEARCH_KEY+")";

            sqLiteDatabase.execSQL(buildSQL);

        }

        @Override
        public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
            // Database schema upgrade code goes here

            String buildSQL = "DROP TABLE IF EXISTS " + TABLE_NAME;

            sqLiteDatabase.execSQL(buildSQL);       // drop previous table

            onCreate(sqLiteDatabase);               // create the table from the beginning
        }
    }
}

DBAdapter.java

package com.example.cracking.dbtenc;

import android.content.Context;
import android.database.Cursor;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.CursorAdapter;
import android.widget.TextView;

public class DBAdapter extends CursorAdapter {

    public DBAdapter(Context context, Cursor c) {
        super(context, c);
    }

    @Override
    public View newView(Context context, Cursor cursor, ViewGroup parent) {
        // when the view will be created for first time,
        // we need to tell the adapters, how each item will look
        LayoutInflater inflater = LayoutInflater.from(parent.getContext());
        View retView = inflater.inflate(R.layout.list_item, parent, false);

        return retView;
    }

    @Override
    public void bindView(View view, Context context, Cursor cursor) {
        // here we are setting our data
        // that means, take the data from the cursor and put it in views

        TextView textViewUserName = (TextView) view.findViewById(R.id.tv_user_name);
        textViewUserName.setText(cursor.getString(cursor.getColumnIndex(cursor.getColumnName(1))));

        TextView textViewUserTel = (TextView) view.findViewById(R.id.tv_user_tel);
        textViewUserTel.setText(cursor.getString(cursor.getColumnIndex(cursor.getColumnName(2))));
    }


}


MainActivity.java

package com.example.cracking.dbtenc;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.os.Handler;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.PopupMenu;
import android.widget.SearchView;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity implements SearchView.OnQueryTextListener,SearchView.OnCloseListener{

    private EditText editUserName;
    private EditText editUserTel;
    public TextView tvUserName;
    public TextView tvUserTel;
    private DBAdapter customAdapter;
    private DatabaseHelper databaseHelper;
    private static final int ENTER_DATA_REQUEST_CODE = 1;
    private ListView listView;
    public String latestUserName;
    public String latestUserTel;
    public String selectedUserName;
    public String selectedUserTel;
    public SearchView searchUser;

    private static final String TAG = MainActivity.class.getSimpleName();

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        searchUser = (SearchView)findViewById(R.id.search_User);
        searchUser.setIconifiedByDefault(false);
        searchUser.setOnQueryTextListener(this);
        searchUser.setOnCloseListener(this);

        databaseHelper = new DatabaseHelper(this);

        tvUserName = (TextView)findViewById(R.id.tv_user_name);
        tvUserTel = (TextView)findViewById(R.id.tv_user_tel);

        listView = (ListView) findViewById(R.id.list_data);
        listView.setOnItemClickListener(new OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {

                Cursor cursor = (Cursor) parent.getItemAtPosition(position);
                cursor.moveToLast();

                latestUserName = cursor.getString(cursor.getColumnIndexOrThrow(databaseHelper.USER_TABLE_COLUMN_NAME));
                latestUserTel = cursor.getString(cursor.getColumnIndexOrThrow(databaseHelper.USER_TABLE_COLUMN_TEL));
                Toast.makeText(MainActivity.this, "Recently, Added User\n UserName : "+latestUserName+"\nUserTel : "+latestUserTel, Toast.LENGTH_SHORT).show();

                Log.d(TAG, "clicked on item: " + id);
            }
        });

        listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
            @Override
            public boolean onItemLongClick(AdapterView<?> parent, final View view, final int position, final long id) {

                Cursor cursor = (Cursor) parent.getItemAtPosition(position);

                selectedUserName = cursor.getString(cursor.getColumnIndexOrThrow(databaseHelper.USER_TABLE_COLUMN_NAME));
                selectedUserTel = cursor.getString(cursor.getColumnIndexOrThrow(databaseHelper.USER_TABLE_COLUMN_TEL));

                final int item_id = cursor.getInt(cursor.getColumnIndex(databaseHelper.USER_TABLE_COLUMN_ID));

                PopupMenu popup = new PopupMenu(MainActivity.this, view);
                getMenuInflater().inflate(R.menu.menu_listview,popup.getMenu());

                popup.setOnMenuItemClickListener(new PopupMenu.OnMenuItemClickListener() {
                    @Override
                    public boolean onMenuItemClick(MenuItem item) {
                        switch (item.getItemId()){
                            case R.id.modify:

                                LayoutInflater inflater =LayoutInflater.from(MainActivity.this);
                                View layout = inflater.inflate(R.layout.edit_user_info,null);

                                editUserName = (EditText)layout.findViewById(R.id.edit_user_name);
                                editUserTel = (EditText)layout.findViewById(R.id.edit_user_tel);

                                AlertDialog.Builder editAlert = new AlertDialog.Builder(MainActivity.this);
                                editAlert.setTitle("EditUser");
                                editAlert.setView(layout);


                                editAlert.setPositiveButton("Modify", new DialogInterface.OnClickListener() {
                                    @Override
                                    public void onClick(DialogInterface dialog, int which) {

                                        String userName = editUserName.getText().toString();
                                        String userTel = editUserTel.getText().toString();
                                        databaseHelper.update_byID(item_id,userName,userTel);

                                        databaseHelper.getAllData();

                                        customAdapter = new DBAdapter(MainActivity.this, databaseHelper.getAllData());
                                        listView.setAdapter(customAdapter);
                                    }
                                });
                                editAlert.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
                                    @Override
                                    public void onClick(DialogInterface dialog, int which) {
                                        return;
                                    }
                                });
                                AlertDialog ad = editAlert.create();
                                ad.show();
                                break;
                            case R.id.delete:

                                AlertDialog.Builder deleteAlert = new AlertDialog.Builder(MainActivity.this);
                                deleteAlert.setMessage("해당 유저가 삭제됩니다.\n삭제하시겠습니까??\nUser name : "+selectedUserName).setCancelable(false).setNegativeButton("Confirm", new DialogInterface.OnClickListener() {
                                    @Override
                                    public void onClick(DialogInterface dialog, int which) {
                                        databaseHelper.delete(selectedUserName);
                                        databaseHelper.getAllData();

                                        customAdapter = new DBAdapter(MainActivity.this, databaseHelper.getAllData());
                                        listView.setAdapter(customAdapter);
                                    }
                                }).setPositiveButton("Cancel", new DialogInterface.OnClickListener() {
                                    @Override
                                    public void onClick(DialogInterface dialog, int which) {
                                        return;
                                    }
                                });
                                AlertDialog alert = deleteAlert.create();
                                alert.show();
                                break;
                        }
                        return false;
                    }
                });
                popup.show();
                return false;
            }
        });

        new Handler().post(new Runnable() {
            @Override
            public void run() {
                customAdapter = new DBAdapter(MainActivity.this, databaseHelper.getAllData());
                listView.setAdapter(customAdapter);
            }
        });
    }

    public void onClickEnterData(View btnAdd) {

        startActivityForResult(new Intent(this, EnterDataActivity.class), ENTER_DATA_REQUEST_CODE);

    }

    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {

        super.onActivityResult(requestCode, resultCode, data);

        if (requestCode == ENTER_DATA_REQUEST_CODE && resultCode == RESULT_OK) {

            databaseHelper.insertData(data.getExtras().getString("tag_user_name"), data.getExtras().getString("tag_user_pin"));

            customAdapter.changeCursor(databaseHelper.getAllData());
        }
    }

    @Override
    public boolean onClose() {
        showResults("");
        return false;
    }

    @Override
    public boolean onQueryTextSubmit(String query) {
        showResults(query + "*");
        return false;
    }

    @Override
    public boolean onQueryTextChange(String newText) {
        showResults(newText + "*");
        return false;
    }

    //이쪽도 문제입니다.
    public void showResults(String query){

        Cursor cursor = databaseHelper.searchUser((query != null ? query.toString() : "@@@@"));

        if(cursor == null){

        }else{
            String[] from = new String[]{
                    DatabaseHelper.USER_TABLE_COLUMN_NAME,
                    DatabaseHelper.USER_TABLE_COLUMN_TEL
            };

            int[] to = new int[]{
                    R.id.tv_user_name,R.id.tv_user_tel
            };

            customAdapter = new DBAdapter(MainActivity.this, databaseHelper.getAllData());
            listView.setAdapter(customAdapter);
        }
    }


}


  • 2016년 06월 04일에 작성됨

조회수 615


1 답변


좋아요
1
싫어요
채택취소하기

코드를 꼼꼼하게 훑어보지는 못했습니다만 일단은 searchUser 함수 내에서 like 검색 시 %를 사용하지 않아 의도한대로 검색 결과가 나오지 않는 것 같습니다. inputText로 시작하는 문자열을 검색할 경우 query 부분의 마지막을 " LIKE '" + inputText + "%';"으로 수정해보세요.

%는 inputText 앞뒤로 붙일 수 있으니 의도에 따라 적절히 추가하시면 됩니다.


로그인이 필요한 기능입니다.

Hashcode는 개발자들을 위한 무료 QnA사이트 입니다. 작성한 답변에 다른 개발자들이 댓글을 작성하거나 좋아요/싫어요를 할 수 있기 때문에 계정을 필요로 합니다.
► 로그인
► 계정만들기
Close