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

Query SQLite ด้วย WHERE ... GLOB Operator

GLOB เป็น Operator ใน SQLite สำหรับ Pattern Matching คล้าย LIKE แต่ Case-Sensitive และใช้ Wildcard แบบ Unix Shell (* = 0 หรือหลายตัวอักษร, ? = 1 ตัวอักษร, [range] = ช่วงตัวอักษร)

ขั้นตอนที่ 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="ค้นหาด้วย GLOB (Case-Sensitive, Unix Wildcards)"
            android:textSize="16sp"
            android:textStyle="bold"
            android:layout_marginBottom="16dp" />

        <EditText
            android:id="@+id/etSearch"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="Pattern: สม*, สม?, สม[ช-ศ]าย"
            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="ค้นหา GLOB" />

            <Button
                android:id="@+id/btnShowAll"
                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.sqliteglobdemo;

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_CODE = "employee_code"; // รหัสพนักงาน เช่น EMP001

    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_CODE + " TEXT NOT NULL" +
                ")";
        db.execSQL(sql);

        insertSampleData(db);
    }

    private void insertSampleData(SQLiteDatabase db) {
        // ข้อมูลทดสอบ Case-Sensitive และ Code Pattern
        String[][] data = {
            {"Somchai", "EMP001"}, {"somchai", "EMP002"}, {"SomChai", "EMP003"},
            {"Somying", "EMP010"}, {"Somying", "EMP011"}, {"Somsak", "EMP020"},
            {"SomSak", "EMP021"}, {"Wichai", "EMP100"}, {"WICHAI", "EMP101"},
            {"Admin001", "ADM001"}, {"admin002", "ADM002"}, {"User_1", "USR001"},
            {"User_2", "USR002"}, {"Test-User", "TST001"}, {"Test.User", "TST002"}
        };

        for (String[] row : data) {
            ContentValues cv = new ContentValues();
            cv.put(COL_NAME, row[0]);
            cv.put(COL_CODE, row[1]);
            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);
    }

    // ⭐ GLOB Query
    public List<Employee> searchByNameGlob(String pattern) {
        List<Employee> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " WHERE " + COL_NAME + " GLOB ?" +
                " 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_CODE))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    // GLOB บน Code (Pattern matching ขั้นสูง)
    public List<Employee> searchByCodeGlob(String pattern) {
        List<Employee> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " WHERE " + COL_CODE + " GLOB ?" +
                " ORDER BY " + COL_CODE + " 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_CODE))
                ));
            } 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_CODE))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }
}

Model: Employee.java

package com.example.sqliteglobdemo;

public class Employee {
    public int id;
    public String name;
    public String code;

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

    @Override
    public String toString() {
        return name + " (" + code + ")";
    }
}

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

package com.example.sqliteglobdemo;

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, btnShowAll;
    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);
        btnShowAll = findViewById(R.id.btnShowAll);
        rvEmployees = findViewById(R.id.rvEmployees);
        rvEmployees.setLayoutManager(new LinearLayoutManager(this));

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

        loadAllEmployees();

        btnSearch.setOnClickListener(v -> searchGlob());
        btnShowAll.setOnClickListener(v -> loadAllEmployees());
    }

    private void searchGlob() {
        String pattern = etSearch.getText().toString().trim();
        if (pattern.isEmpty()) {
            Toast.makeText(this, "กรุณากรอก Pattern", Toast.LENGTH_SHORT).show();
            return;
        }

        // Demo: ค้นหาทั้ง Name และ Code
        List<Employee> byName = dbHelper.searchByNameGlob(pattern);
        List<Employee> byCode = dbHelper.searchByCodeGlob(pattern);
        
        // รวมผลลัพธ์ (Simple merge)
        List<Employee> combined = new ArrayList<>(byName);
        for (Employee e : byCode) {
            if (!combined.contains(e)) combined.add(e);
        }

        adapter.setData(combined);
        Toast.makeText(this, "GLOB: พบ " + combined.size() + " รายการ", Toast.LENGTH_SHORT).show();
    }

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

EmployeeAdapter.java

package com.example.sqliteglobdemo;

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.tvCode.setText("Code: " + emp.code);
    }

    @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, tvCode;
        ViewHolder(View itemView) {
            super(itemView);
            tvName = itemView.findViewById(android.R.id.text1);
            tvCode = itemView.findViewById(android.R.id.text2);
        }
    }
}

GLOB Wildcard Patterns

ตัวอย่าง Pattern ที่มีประโยชน์

-- รหัสพนักงานขึ้นต้น EMP ตามด้วย 3 หลัก
WHERE code GLOB 'EMP[0-9][0-9][0-9]'

-- รหัส Admin หรือ User
WHERE code GLOB '[AU]?????'  -- A หรือ U ตามด้วย 5 ตัวอักษร

-- มีอักขระพิเศษ - หรือ _
WHERE code GLOB '*[-_]*'

-- ชื่อขึ้นต้นพิมพ์ใหญ่ ตามด้วยพิมพ์เล็ก
WHERE name GLOB '[A-Z][a-z]*'

-- ไม่ขึ้นต้นด้วยตัวเลข
WHERE name GLOB '[^0-9]*'

GLOB vs LIKE

เมื่อไหร่ควรใช้ GLOB

• ต้องการ Case-Sensitive Search (เช่น รหัสสินค้า, Serial Number)
• Pattern Matching ซับซ้อน (Character Classes/Ranges)
• Validation Format (เช่น Email pattern, Phone format)
• Legacy Unix-style patterns

สรุป

GLOB ใน SQLite ให้ Pattern Matching แบบ Case-Sensitive ใช้ Wildcard แบบ Unix Shell (*, ?, []) เหมาะสำหรับการ Validate Format, Search Code/ID ที่ Case-Sensitive และ Pattern ที่ซับซ้อนกว่า LIKE