java - Android 日记应用 - 搜索 SQLite 数据库并在 ListView 中显示结果

Question

我在 Android 中有一个日记应用程序，我希望能够根据用户在 EditText 字段中键入的内容在 SQLite 数据库中搜索条目。问题是我必须使用旧版本，这使得使用 RecyclerView 变得困难（我认为）。我的设置如下：

一个 SQLite 数据库

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

public class SecondDataBaseHelper extends SQLiteOpenHelper {

    private static SecondDataBaseHelper secondDataBaseHelper;

    SQLiteDatabase db = this.getWritableDatabase();

    public final static String DATABASE_NM ="RECORD_OF_USER";
    public final static String TABLE_NM ="DATA_OF_USER";
    public final static String COL_1 = "ID";
    public final static String COL_2 = "SUBJECT";
    public final static String COL_3 = "ENTRY";
    public final static String COL_4 = "DATE";
    public final static String COL_5 = "LATITUDE";
    public final static String COL_6 = "LONGITUDE";


    public SecondDataBaseHelper(Context context) {
        super(context, DATABASE_NM, null, 1);
    }

    public static SecondDataBaseHelper databaseInstance(Context context) {
        if (secondDataBaseHelper == null) {
            secondDataBaseHelper = new SecondDataBaseHelper(context);
        }

        return secondDataBaseHelper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NM +" (ID TEXT, SUBJECT TEXT, ENTRY TEXT, DATE TEXT, LATITUDE TEXT, LONGITUDE TEXT)");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void addEntryToDatabase(Diary entry) {
        ContentValues contentValues = new ContentValues();

        contentValues.put(COL_1, entry.getId());
        contentValues.put(COL_2, entry.getSubject());
        contentValues.put(COL_3, entry.getContent());
        contentValues.put(COL_4, entry.getDate());
        contentValues.put(COL_5, entry.getLatitude());
        contentValues.put(COL_6, entry.getLongitude());

        db.insert(TABLE_NM, null, contentValues);
    }

    public void populateDiaryListArray() {
        SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();

        try (Cursor result = sqLiteDatabase.rawQuery("SELECT * FROM " + TABLE_NM, null)) {
            if (result.getCount() != 0) {
                while (result.moveToNext()) {
                    String id = result.getString(1);
                    String subject = result.getString(2);
                    String content = result.getString(3);
                    String date = result.getString(4);
                    String latitude = result.getString(5);
                    String longitude = result.getString(6);

                    Diary diary = new Diary(id, subject, content, date, latitude, longitude);
                    Diary.diaryArrayList.add(diary);
                }
            }
        }
    }


    public void updateDiaryInDB(Diary entry) {
        SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();

        contentValues.put(COL_1, entry.getId());
        contentValues.put(COL_2, entry.getSubject());
        contentValues.put(COL_3, entry.getContent());
        contentValues.put(COL_4, entry.getDate());
        contentValues.put(COL_5, entry.getLatitude());
        contentValues.put(COL_6, entry.getLongitude());

        sqLiteDatabase.update(TABLE_NM, contentValues, COL_1 + " ?= ", new String[]{String.valueOf(entry.getId())});
    }

    public void deleteEntry(String id) {
        db.delete(TABLE_NM, COL_1 + "=" + id, null);
    }

}

./日记适配器

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.TextView;

import androidx.annotation.NonNull;
import androidx.annotation.Nullable;

import org.w3c.dom.Text;

import java.util.List;

public class DiaryAdapter extends ArrayAdapter<Diary>{
    public DiaryAdapter(Context context, List<Diary> entries) {
        super(context, 0, entries);
    }

    @NonNull
    @Override
    public View getView(int position, @Nullable View convertView, @NonNull ViewGroup parent) {
        Diary diary = getItem(position);

        if (convertView == null) {
            convertView = LayoutInflater.from(getContext()).inflate(R.layout.note_cell, parent, false);
        }

        TextView entryID = convertView.findViewById(R.id.entryID);
        TextView entrySubject = convertView.findViewById(R.id.entrySubject);

        entryID.setText(diary.getId());
        entrySubject.setText(diary.getSubject());

        return convertView;
    }
}

日记类本身

import java.util.ArrayList;
import java.util.Date;

public class Diary {
    private String id;
    private String subject;
    private String content;
    private String date;
    private String latitude;
    private String longitude;

    public static ArrayList<Diary> diaryArrayList = new ArrayList<>();
    public static String DIARY_EDIT_EXTRA = "diaryEdit";

    public Diary(String id, String subject, String content, String date, String latitude, String longitude) {
        this.id = id;
        this.subject = subject;
        this.content = content;
        this.date = date;
        this.latitude = latitude;
        this.longitude = longitude;
    }

    public static Diary getDiaryForID(int passedDiaryID) {
        for (Diary diary : diaryArrayList) {
            if (Integer.parseInt((diary.getId())) == passedDiaryID) {
                return diary;
            }
        }

        return null;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getSubject() {
        return subject;
    }

    public void setSubject(String subject) {
        this.subject = subject;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getLatitude() {
        return latitude;
    }

    public void setLatitude(String latitude) {
        this.latitude = latitude;
    }

    public String getLongitude() {
        return longitude;
    }

    public void setLongitude(String longitude) {
        this.longitude = longitude;
    }

}

和主应用程序页面本身，./SearchEntries

import java.util.ArrayList;

public class SearchEntries extends AppCompatActivity {


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


    }
}

我的 XML 页面设置如下

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".SearchEntries">

    <androidx.appcompat.widget.Toolbar
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:minHeight="?actionBarSize"
        android:theme="?actionBarTheme"
        android:background="@color/darkGray">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Search"
            android:textSize="35sp"
            android:paddingRight="25dp"
            android:textColor="@color/white"
            android:textStyle="bold"
            android:layout_gravity="center"/>

    </androidx.appcompat.widget.Toolbar>

    <EditText
        android:id="@+id/searchTextField"
        android:layout_width="300dp"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:layout_marginTop="30dp"
        android:hint="Enter text here"
        android:maxLines="1"
        android:textSize="25sp" />

    <ListView
        android:id="@+id/diarySearchResults"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_marginTop="8dp"
        android:dividerHeight="3dp"
        android:divider="@color/darkGray"/>

</LinearLayout>

基本上，我想在 XML 文件中的 searchTextField 中输入文本，并将其显示在 ./diarySearchResults ListView 字段中，其中每个条目仅由适配器的 ID/主题组合组成。

标签： javaandroidsqlite

Answer 1

如果你想从 SQLite 中搜索的关键字中获取数据列表，那么这里是简单的查询。

我已经用你的模型类和 DatabaseHelper 制作了一个方法来简化它。只需在其中传递所需的关键字并完成工作。

private void searchDiaryData(String diaryId, String diarySubject){

    List<Diary> searchResults = new ArrayList<>();

    Cursor cursor = database.rawQuery(" SELECT * FROM " + TABLE_NM +
            " WHERE " + SecondDataBaseHelper.COL_1 + " LIKE '" + diaryId + "%' OR "
            + SecondDataBaseHelper.COL_2 + " LIKE '" + diarySubject + "%'", null);

    if (cursor != null) {
        if (cursor.moveToFirst()) {
            do {
                String _id = cursor.getString(cursor.getColumnIndex(SecondDataBaseHelper.COL_1));
                String _subject = cursor.getString(cursor.getColumnIndex(SecondDataBaseHelper.COL_2));
                String _content = cursor.getString(cursor.getColumnIndex(SecondDataBaseHelper.COL_3));
                String _date = cursor.getString(cursor.getColumnIndex(SecondDataBaseHelper.COL_4));
                String _latitude = cursor.getString(cursor.getColumnIndex(SecondDataBaseHelper.COL_5));
                String _longitude = cursor.getString(cursor.getColumnIndex(SecondDataBaseHelper.COL_6));

                Diary diaryModel = new Diary(
                        _id,
                        _subject,
                        _content,
                        _date,
                        _latitude,
                        _longitude
                );

                searchResults.add(diaryModel);
            } while (cursor.moveToNext());
        }

        diaryAdapter = new DiaryAdapter(this, searchResults);
        diarySearchResults.setAdapter(diaryAdapter);
        cursor.close();
    }
}