안드로이드 db 검색기능 구현중인데 SearchView에 Input을 해도 검색결과가 나오지 않습니다.
조회수 3470회
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);
}
}
}
-
(•́ ✖ •̀)
알 수 없는 사용자
1 답변
-
코드를 꼼꼼하게 훑어보지는 못했습니다만 일단은
searchUser
함수 내에서 like 검색 시 %를 사용하지 않아 의도한대로 검색 결과가 나오지 않는 것 같습니다. inputText로 시작하는 문자열을 검색할 경우 query 부분의 마지막을" LIKE '" + inputText + "%';"
으로 수정해보세요.%는 inputText 앞뒤로 붙일 수 있으니 의도에 따라 적절히 추가하시면 됩니다.
-
(•́ ✖ •̀)
알 수 없는 사용자
-
댓글 입력