问题描述

我正在研究存在 Sqlite 数据库的应用程序。我为使用列表在 sqlite 数据库上实现搜索所做的工作，我已经实现了类似的查询并且我知道了；searchview 不是从 sqlite 搜索数据...

这是我的 Sqlite 文件....

    public List<GetSetClientsDetail> SearchClientsData() {

    String[] columns = {
            fname,
            route,
            cnic,
            lname,
            contact
    };

    String sortOrder = fname + " ASC";

    List<GetSetClientsDetail> clientlist = new ArrayList<GetSetClientsDetail>();

    SQLiteDatabase db = this.getReadableDatabase();


    Cursor cursor = db.query(
            table_poducts_records,
            new String[] { fname, route, cnic, lname, contact},
            fname + " LIKE '%" + fname + "%'",
            null, null, null, null, null);


    if (cursor.moveToFirst()) {
        do {
            GetSetClientsDetail clientsDetail = new GetSetClientsDetail();

            clientsDetail.setFNAME(cursor.getString(cursor.getColumnIndex(fname)));
            clientsDetail.setROUTE(cursor.getString(cursor.getColumnIndex(route)));
            clientsDetail.setCNIC(cursor.getString(cursor.getColumnIndex(cnic)));
            clientsDetail.setLNAME(cursor.getString(cursor.getColumnIndex(lname)));
            clientsDetail.setCONTACT(cursor.getString(cursor.getColumnIndex(contact)));
            clientlist.add(clientsDetail);

        } while (cursor.moveToNext());
    }
    cursor.close();
    db.close();

    return clientlist;
}

这是我实现搜索的 viewpage 类搜索..

public class Clients extends Fragment {
RecyclerView recyclerViewClients;
Button btnAll;
SearchView searchViewclient;
ClientRecyclerAdapter clientRecyclerAdapter;
List<GetSetClientsDetail> listclients;
DatabaseHelper databaseHelper;

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
    View view =  inflater.inflate(R.layout.clients, container, false);

    btnAll=view.findViewById(R.id.searchallclients);
    recyclerViewClients=view.findViewById(R.id.recyclerviewallclients);
    searchViewclient=view.findViewById(R.id.searchclient);
    listclients = new ArrayList<>();

    clientRecyclerAdapter = new ClientRecyclerAdapter(listclients,recyclerViewClients);
    recyclerViewClients.setItemAnimator(new DefaultItemAnimator());
    recyclerViewClients.setItemAnimator(new DefaultItemAnimator()); //multi copy paste!
    recyclerViewClients.setLayoutManager(new LinearLayoutManager(getContext()));
    recyclerViewClients.setHasFixedSize(true);
    recyclerViewClients.setAdapter(clientRecyclerAdapter);

    databaseHelper = new DatabaseHelper(getActivity());

    searchViewclient.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            SearchSQliteClientData();
        }
    });

    btnAll.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            getClientFromSqlite();
        }
    });

    return view;
}

@SuppressLint("StaticFieldLeak")
private void SearchSQliteClientData() {
    new AsyncTask<Void, Void, Void>() {
        @Override
        protected Void doInBackground(Void... params) {
            listclients.clear();
            listclients.addAll(databaseHelper.SearchClientsData());
            return null;
        }

        @Override
        protected void onPostExecute(Void aVoid) {
            super.onPostExecute(aVoid);
            clientRecyclerAdapter.notifyDataSetChanged();
        }
    }.execute();
}


@SuppressLint("StaticFieldLeak")
private void getClientFromSqlite() {
    new AsyncTask<Void, Void, Void>() {
        @Override
        protected Void doInBackground(Void... params) {
            listclients.clear();
            listclients.addAll(databaseHelper.getAllClientsData());
            return null;
        }

        @Override
        protected void onPostExecute(Void aVoid) {
            super.onPostExecute(aVoid);
            clientRecyclerAdapter.notifyDataSetChanged();
        }
    }.execute();
}

}

我想做的是当我在搜索视图上按 A 时；它带来了与 a 相关的所有数据，或者如果我按下一个字母它会将这些数据带到我已实施的列表中。

标签： androidsqliteandroid-sqliteandroid-search

解决方案

举个例子

改变public List<GetSetClientsDetail> SearchClientsData()

至

public List<GetSetClientsDetail> SearchClientsData(String mysearchstring)

这允许将搜索参数从调用者传递给方法

然后改变： -

Cursor cursor = db.query(
            table_poducts_records,
            new String[] { fname, route, cnic, lname, contact},
            fname + " LIKE '%" + fname + "%'",
            null, null, null, null, null);

至

String[] whereargs = new String[]{"%" + mysearchstring + "%"}; //<<<<<<<<<< ADDED
Cursor cursor = db.query(
            table_poducts_records,
            new String[] { fname, route, cnic, lname, contact},
            fname + " LIKE ?", //<<<<<<<<<< CHANGED
            whereargs, //<<<<<<<<<< ADDED
            null, null, null); //<<<<<<<<<< 3 nulls instead of 4 (as whereargs has replaced first null)

这使用传递给 LIKE 子句中的方法的搜索参数来防止 SQL 注入

作为测试尝试：-

 listclients.addAll(databaseHelper.SearchClientsData("A")); //<<<<<<<<<< look for all with A anywhere in the fname column

工作示例

以下是基于问题中的代码的工作示例，但为方便起见进行了简化。

核心变化有两点，因为上面的代码在数据库助手DatabaseHelper.java中： -

那就是 SearchClientData 方法是：-

public List<GetSetClientsDetail> SearchClientsData(String mysearchstring) {

    String[] columns = {
            fname, route, cnic, lname, contact
    };

    String sortOrder = fname + " ASC";
    List<GetSetClientsDetail> clientlist = new ArrayList<GetSetClientsDetail>();
    SQLiteDatabase db = this.getReadableDatabase();

    String[] whereargs = new String[]{"%" + mysearchstring + "%"}; //<<<<<<<<<< ADDED
    Cursor cursor = db.query(
            table_poducts_records,
            new String[]{fname, route, cnic, lname, contact},
            fname + " LIKE ?",
            whereargs,
            null, null, sortOrder, null
    );
    if (cursor.moveToFirst()) {
        do {
            GetSetClientsDetail clientsDetail = new GetSetClientsDetail();

            clientsDetail.setFNAME(cursor.getString(cursor.getColumnIndex(fname)));
            clientsDetail.setROUTE(cursor.getString(cursor.getColumnIndex(route)));
            clientsDetail.setCNIC(cursor.getString(cursor.getColumnIndex(cnic)));
            clientsDetail.setLNAME(cursor.getString(cursor.getColumnIndex(lname)));
            clientsDetail.setCONTACT(cursor.getString(cursor.getColumnIndex(contact)));
            clientlist.add(clientsDetail);
        } while (cursor.moveToNext());
    }
    cursor.close();
    return clientlist;
}

public List<GetSetClientsDetail> getAllClientsData() {
    return SearchClientsData("");
}

请注意免费赠品 getAllClientsData，它只使用传递“”的 SearchClientData 方法，它将选择所有行。

另一个核心变化是不再依赖 SearchView 的OnClickListener ，因为其他监听器窃取了焦点能力，这很可能会被调用。

SearchView 的setOnQueryTextListener已被使用。这允许将文本传递给 SearchClientsData。

为方便起见，此示例使用 ArrayAdapter 和常用的 Simple_List_Item1 布局，并在主线程和活动上完成工作。

使用的活动代码是：-

public class MainActivity extends AppCompatActivity {

    ListView listviewClients;
    Button btnAll;
    ArrayAdapter mSCA;
    SearchView searchViewclient;
    List<GetSetClientsDetail> listclients;
    DatabaseHelper databaseHelper;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        btnAll=this.findViewById(R.id.searchallclients);
        listviewClients=this.findViewById(R.id.clients);
        searchViewclient=this.findViewById(R.id.searchclient);
        databaseHelper = new DatabaseHelper(this);
        addSomeData();
        manageListView("");

        searchViewclient.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                manageListView("zzz");
            }
        });
        searchViewclient.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
            @Override
            public boolean onQueryTextSubmit(String query) {
                return false;
            }

            @Override
            public boolean onQueryTextChange(String newText) {
                manageListView(newText);
                return false;
            }
        });

        btnAll.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                manageListView("");
            }
        });
    }

    private void manageListView(String searchArgument) {
        listclients =  databaseHelper.SearchClientsData(searchArgument);
        if (mSCA == null) {
            mSCA = new ArrayAdapter(this,android.R.layout.simple_list_item_1,listclients);
            listviewClients.setAdapter(mSCA);
        } else {
            mSCA.clear();
            mSCA.addAll(listclients);
            mSCA.notifyDataSetChanged();
        }
    }

    private void addSomeData() {
        databaseHelper.add("Albert","001","123456789","Smith","someone");
        databaseHelper.add("Freda","001","123456789","Jones","someone");
        databaseHelper.add("Mike","002","0987654321","Allenby","him");
    }



    /*
    private void SearchSQliteClientData() {
        new AsyncTask<Void, Void, Void>() {
            @Override
            protected Void doInBackground(Void... params) {
                listclients.clear();
                listclients.addAll(databaseHelper.SearchClientsData());
                return null;
            }

            @Override
            protected void onPostExecute(Void aVoid) {
                super.onPostExecute(aVoid);
                clientRecyclerAdapter.notifyDataSetChanged();
            }
        }.execute();
    }


    private void getClientFromSqlite() {
        new AsyncTask<Void, Void, Void>() {
            @Override
            protected Void doInBackground(Void... params) {
                listclients.clear();
                listclients.addAll(databaseHelper.getAllClientsData());
                return null;
            }

            @Override
            protected void onPostExecute(Void aVoid) {
                super.onPostExecute(aVoid);
                clientRecyclerAdapter.notifyDataSetChanged();
            }
        }.execute();
    }

    */
}

addSomeData 方法按照它的说法添加了一些测试数据。注释掉的代码是原始但未使用的代码。

结果

第一次运行时（不是真正的第一次，而是经过多次运行但新开始，因此有大量数据）：-

键入a或A 并且所有 Mike 的消失

依此类推，列表会根据在搜索字段中输入的文本立即进行调整。单击全部按钮重新显示全部。

android - Sqlite 数据库上的搜索视图不起作用

问题描述

解决方案

工作示例

结果

推荐阅读