วิธีกรองข้อมูล SQLite ด้วย WHERE และ LIKE ใน Android

Query SQLite ด้วย WHERE ... LIKE สำหรับ Pattern Matching

บทความนี้สาธิตการใช้ LIKE Operator ใน SQLite เพื่อค้นหาข้อมูลแบบ Pattern Matching (Wildcard Search) เช่น ค้นหาชื่อขึ้นต้นด้วย "สม" หรือมีคำว่า "ชาย" อยู่ในชื่อ

ขั้นตอนที่ 1: สร้างโปรเจกต์ใหม่

Android Studio → File → New Project → Empty Activity

ขั้นตอนที่ 2: Layout - activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 
    xmlns:android="https://schemas.android.com/apk/res/android"
    xmlns:app="https://schemas.android.com/apk/res-auto"
    xmlns:tools="https://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <LinearLayout
        android:layout_width="0dp"
        android:layout_height="0dp"
        android:orientation="vertical"
        android:padding="16dp"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintEnd_toEndOf="parent">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="ค้นหาพนักงานด้วย LIKE Pattern"
            android:textSize="18sp"
            android:textStyle="bold"
            android:layout_marginBottom="16dp" />

        <EditText
            android:id="@+id/etSearch"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="ค้นหาชื่อ (ใช้ % และ _)"
            android:layout_marginBottom="16dp" />

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:layout_marginBottom="16dp">

            <Button
                android:id="@+id/btnSearch"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:layout_marginEnd="4dp"
                android:text="ค้นหา" />

            <Button
                android:id="@+id/btnClear"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:layout_marginStart="4dp"
                android:text="แสดงทั้งหมด" />

        </LinearLayout>

        <androidx.recyclerview.widget.RecyclerView
            android:id="@+id/rvEmployees"
            android:layout_width="match_parent"
            android:layout_height="0dp"
            android:layout_weight="1" />

    </LinearLayout>

</androidx.constraintlayout.widget.ConstraintLayout>

ขั้นตอนที่ 3: DatabaseHelper.java

package com.example.sqlitelikedemo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "EmployeeDB";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_EMPLOYEES = "Employees";
    private static final String COL_ID = "id";
    private static final String COL_NAME = "name";
    private static final String COL_POSITION = "position";
    private static final String COL_DEPARTMENT = "department";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + TABLE_EMPLOYEES + " (" +
                COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COL_NAME + " TEXT NOT NULL, " +
                COL_POSITION + " TEXT, " +
                COL_DEPARTMENT + " TEXT" +
                ")";
        db.execSQL(sql);

        // Seed Data
        insertSampleData(db);
    }

    private void insertSampleData(SQLiteDatabase db) {
        String[] names = {"สมชาย ใจดี", "สมหญิง รักงาน", "สมศักดิ์ มั่นคง", "สมปอง เฉลียวฉลาด", 
                          "วิชัย มานะ", "วิไล ยิ้มสนุก", "อนันต์ อุดม", "บุญมี ศรีสุข"};
        String[] positions = {"Manager", "Developer", "Designer", "Tester", "Developer", "HR", "Manager", "Developer"};
        String[] depts = {"IT", "IT", "Design", "QA", "IT", "HR", "Management", "IT"};

        for (int i = 0; i < names.length; i++) {
            ContentValues cv = new ContentValues();
            cv.put(COL_NAME, names[i]);
            cv.put(COL_POSITION, positions[i]);
            cv.put(COL_DEPARTMENT, depts[i]);
            db.insert(TABLE_EMPLOYEES, null, cv);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMPLOYEES);
        onCreate(db);
    }

    public boolean insertEmployee(String name, String position, String department) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(COL_NAME, name);
        cv.put(COL_POSITION, position);
        cv.put(COL_DEPARTMENT, department);
        return db.insert(TABLE_EMPLOYEES, null, cv) != -1;
    }

    // ⭐ LIKE Query กับ Parameterized Query
    public List<Employee> searchByName(String pattern) {
        List<Employee> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        // Pattern: ผู้ใช้กรอก "สม" -> เราเพิ่ม "%" ให้อัตโนมัติ
        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " WHERE " + COL_NAME + " LIKE ?" +
                " ORDER BY " + COL_NAME + " ASC";

        Cursor cursor = db.rawQuery(query, new String[]{pattern});
        if (cursor.moveToFirst()) {
            do {
                list.add(new Employee(
                        cursor.getInt(cursor.getColumnIndexOrThrow(COL_ID)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_NAME)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_POSITION)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_DEPARTMENT))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    // ค้นหาในหลายคอลัมน์ (OR)
    public List<Employee> searchMultipleColumns(String keyword) {
        List<Employee> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String pattern = "%" + keyword + "%";
        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " WHERE " + COL_NAME + " LIKE ?" +
                " OR " + COL_POSITION + " LIKE ?" +
                " OR " + COL_DEPARTMENT + " LIKE ?" +
                " ORDER BY " + COL_NAME + " ASC";

        String[] args = {pattern, pattern, pattern};
        Cursor cursor = db.rawQuery(query, args);

        if (cursor.moveToFirst()) {
            do {
                list.add(new Employee(
                        cursor.getInt(cursor.getColumnIndexOrThrow(COL_ID)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_NAME)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_POSITION)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_DEPARTMENT))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    public List<Employee> getAllEmployees() {
        List<Employee> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_EMPLOYEES, null);
        if (cursor.moveToFirst()) {
            do {
                list.add(new Employee(
                        cursor.getInt(cursor.getColumnIndexOrThrow(COL_ID)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_NAME)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_POSITION)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_DEPARTMENT))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }
}

Model: Employee.java

package com.example.sqlitelikedemo;

public class Employee {
    public int id;
    public String name;
    public String position;
    public String department;

    public Employee(int id, String name, String position, String department) {
        this.id = id;
        this.name = name;
        this.position = position;
        this.department = department;
    }

    @Override
    public String toString() {
        return name + " (" + position + ", " + department + ")";
    }
}

ขั้นตอนที่ 4: MainActivity.java

package com.example.sqlitelikedemo;

import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;
import java.util.List;

public class MainActivity extends AppCompatActivity {

    private DatabaseHelper dbHelper;
    private EditText etSearch;
    private Button btnSearch, btnClear;
    private RecyclerView rvEmployees;
    private EmployeeAdapter adapter;

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

        dbHelper = new DatabaseHelper(this);

        etSearch = findViewById(R.id.etSearch);
        btnSearch = findViewById(R.id.btnSearch);
        btnClear = findViewById(R.id.btnClear);
        rvEmployees = findViewById(R.id.rvEmployees);
        rvEmployees.setLayoutManager(new LinearLayoutManager(this));

        adapter = new EmployeeAdapter();
        rvEmployees.setAdapter(adapter);

        loadAllEmployees();

        btnSearch.setOnClickListener(v -> searchEmployees());
        btnClear.setOnClickListener(v -> {
            etSearch.setText("");
            loadAllEmployees();
        });
    }

    private void searchEmployees() {
        String keyword = etSearch.getText().toString().trim();
        if (keyword.isEmpty()) {
            Toast.makeText(this, "กรุณากรอกคำค้นหา", Toast.LENGTH_SHORT).show();
            return;
        }

        // Auto-add wildcards ถ้าผู้ใช้ไม่ใส่
        String pattern = keyword.contains("%") || keyword.contains("_") ? keyword : "%" + keyword + "%";

        List<Employee> results = dbHelper.searchByName(pattern);
        adapter.setData(results);
        Toast.makeText(this, "พบ " + results.size() + " รายการ", Toast.LENGTH_SHORT).show();
    }

    private void loadAllEmployees() {
        adapter.setData(dbHelper.getAllEmployees());
    }
}

EmployeeAdapter.java

package com.example.sqlitelikedemo;

import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;
import androidx.annotation.NonNull;
import androidx.recyclerview.widget.RecyclerView;
import java.util.ArrayList;
import java.util.List;

public class EmployeeAdapter extends RecyclerView.Adapter<EmployeeAdapter.ViewHolder> {

    private final List<Employee> items = new ArrayList<>();

    @NonNull
    @Override
    public ViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
        View view = LayoutInflater.from(parent.getContext())
                .inflate(android.R.layout.simple_list_item_2, parent, false);
        return new ViewHolder(view);
    }

    @Override
    public void onBindViewHolder(@NonNull ViewHolder holder, int position) {
        Employee emp = items.get(position);
        holder.tvName.setText(emp.name);
        holder.tvDetail.setText(emp.position + " | " + emp.department);
    }

    @Override
    public int getItemCount() {
        return items.size();
    }

    public void setData(List<Employee> newData) {
        items.clear();
        items.addAll(newData);
        notifyDataSetChanged();
    }

    static class ViewHolder extends RecyclerView.ViewHolder {
        TextView tvName, tvDetail;
        ViewHolder(View itemView) {
            super(itemView);
            tvName = itemView.findViewById(android.R.id.text1);
            tvDetail = itemView.findViewById(android.R.id.text2);
        }
    }
}

Wildcard Patterns ใน LIKE

Case Sensitivity

• Default: LIKE ไม่สนใจ Case (Case-insensitive) สำหรับ ASCII
• Case-sensitive: ใช้ PRAGMA case_sensitive_like = ON; หรือ COLLATE BINARY
• Unicode/Thai: SQLite LIKE ทำงานกับ Unicode ได้แต่ Case-folding อาจไม่ครบ

GLOB vs LIKE

-- GLOB: Case-sensitive, ใช้ * และ ? แทน % และ _
WHERE name GLOB 'สม*'      -- ขึ้นต้นด้วย "สม" (Case-sensitive)
WHERE name GLOB '*[چ-ي]*'  -- มีตัวอักษรอราบิก (Pattern matching ขั้นสูง)

Performance Tips

1. Index: CREATE INDEX idx_name ON Employees(name) ช่วย LIKE 'prefix%' (ไม่ช่วย '%suffix' หรือ '%middle%')
2. FTS (Full Text Search): สำหรับค้นหาข้อความจริง ใช้ CREATE VIRTUAL TABLE ... USING FTS5
3. Avoid Leading %: LIKE '%text' ต้อง Full Table Scan

สรุป

LIKE ใน SQLite ใช้ Wildcard % (0 หรือหลายตัวอักษร) และ _ (1 ตัวอักษร) ร่วมกับ Parameterized Query ปลอดภัยจาก SQL Injection เหมาะสำหรับ Search, Autocomplete, Filter แบบ Text-based