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

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

บทความนี้สาธิตการใช้ IN Operator ใน SQLite เพื่อกรองข้อมูลที่ตรงกับ รายการค่าที่กำหนดหลายค่า ทำให้ Query สั้นกว่าและอ่านง่ายกว่าใช้ OR หลายตัว

ขั้นตอนที่ 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="กรองพนักงานแผนก IT, HR, Finance (IN Operator)"
            android:textSize="16sp"
            android:textStyle="bold"
            android:layout_marginBottom="16dp" />

        <EditText
            android:id="@+id/etName"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="ชื่อ"
            android:layout_marginBottom="8dp" />

        <EditText
            android:id="@+id/etSalary"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="เงินเดือน"
            android:inputType="numberDecimal"
            android:layout_marginBottom="8dp" />

        <EditText
            android:id="@+id/etDepartment"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:hint="แผนก (IT, HR, Finance, Marketing)"
            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/btnSave"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:layout_marginEnd="4dp"
                android:text="บันทึก" />

            <Button
                android:id="@+id/btnFilterIN"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:layout_marginStart="4dp"
                android:layout_marginEnd="4dp"
                android:text="กรอง IT, HR, Finance" />

            <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.sqliteindemo;

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 = "CompanyDB";
    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_SALARY = "salary";
    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_SALARY + " REAL NOT NULL, " +
                COL_DEPARTMENT + " TEXT" +
                ")";
        db.execSQL(sql);
    }

    @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, double salary, String department) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(COL_NAME, name);
        cv.put(COL_SALARY, salary);
        cv.put(COL_DEPARTMENT, department);
        return db.insert(TABLE_EMPLOYEES, null, cv) != -1;
    }

    // ⭐ IN Query กับ Dynamic Parameters
    public List<Employee> getEmployeesInDepartments(List<String> departments) {
        List<Employee> list = new ArrayList<>();
        if (departments == null || departments.isEmpty()) return list;

        SQLiteDatabase db = getReadableDatabase();

        // สร้าง Placeholders: ?, ?, ?
        StringBuilder placeholders = new StringBuilder();
        for (int i = 0; i < departments.size(); i++) {
            if (i > 0) placeholders.append(",");
            placeholders.append("?");
        }

        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " WHERE " + COL_DEPARTMENT + " IN (" + placeholders + ")" +
                " ORDER BY " + COL_DEPARTMENT + ", " + COL_NAME;

        String[] args = departments.toArray(new String[0]);
        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.getDouble(cursor.getColumnIndexOrThrow(COL_SALARY)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_DEPARTMENT))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    // IN ร่วมกับ AND (เงินเดือนมากกว่า 20000 และอยู่ในแผนกที่กำหนด)
    public List<Employee> getHighEarnersInDepartments(List<String> departments, double minSalary) {
        List<Employee> list = new ArrayList<>();
        if (departments == null || departments.isEmpty()) return list;

        SQLiteDatabase db = getReadableDatabase();
        StringBuilder placeholders = new StringBuilder();
        for (int i = 0; i < departments.size(); i++) {
            if (i > 0) placeholders.append(",");
            placeholders.append("?");
        }

        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " WHERE " + COL_DEPARTMENT + " IN (" + placeholders + ")" +
                " AND " + COL_SALARY + " > ?" +
                " ORDER BY " + COL_SALARY + " DESC";

        String[] args = new String[departments.size() + 1];
        for (int i = 0; i < departments.size(); i++) {
            args[i] = departments.get(i);
        }
        args[departments.size()] = String.valueOf(minSalary);

        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.getDouble(cursor.getColumnIndexOrThrow(COL_SALARY)),
                        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.getDouble(cursor.getColumnIndexOrThrow(COL_SALARY)),
                        cursor.getString(cursor.getColumnIndexOrThrow(COL_DEPARTMENT))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }
}

Model: Employee.java

package com.example.sqliteindemo;

public class Employee {
    public int id;
    public String name;
    public double salary;
    public String department;

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

    @Override
    public String toString() {
        return String.format("%s - %,.0f บาท (%s)", name, salary, department);
    }
}

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

package com.example.sqliteindemo;

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.Arrays;
import java.util.List;

public class MainActivity extends AppCompatActivity {

    private DatabaseHelper dbHelper;
    private EditText etName, etSalary, etDepartment;
    private Button btnSave, btnFilterIN, btnShowAll;
    private RecyclerView rvEmployees;
    private EmployeeAdapter adapter;

    // แผนกที่ต้องการกรอง
    private final List<String> targetDepartments = Arrays.asList("IT", "HR", "Finance");

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

        dbHelper = new DatabaseHelper(this);

        etName = findViewById(R.id.etName);
        etSalary = findViewById(R.id.etSalary);
        etDepartment = findViewById(R.id.etDepartment);
        btnSave = findViewById(R.id.btnSave);
        btnFilterIN = findViewById(R.id.btnFilterIN);
        btnShowAll = findViewById(R.id.btnShowAll);
        rvEmployees = findViewById(R.id.rvEmployees);
        rvEmployees.setLayoutManager(new LinearLayoutManager(this));

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

        loadAllEmployees();

        btnSave.setOnClickListener(v -> saveEmployee());
        btnFilterIN.setOnClickListener(v -> filterByDepartments());
        btnShowAll.setOnClickListener(v -> loadAllEmployees());
    }

    private void saveEmployee() {
        String name = etName.getText().toString().trim();
        String salaryStr = etSalary.getText().toString().trim();
        String dept = etDepartment.getText().toString().trim();

        if (name.isEmpty() || salaryStr.isEmpty() || dept.isEmpty()) {
            Toast.makeText(this, "กรอกข้อมูลไม่ครบ", Toast.LENGTH_SHORT).show();
            return;
        }

        double salary = Double.parseDouble(salaryStr);
        if (dbHelper.insertEmployee(name, salary, dept)) {
            Toast.makeText(this, "บันทึกสำเร็จ", Toast.LENGTH_SHORT).show();
            etName.setText("");
            etSalary.setText("");
            etDepartment.setText("");
            loadAllEmployees();
        } else {
            Toast.makeText(this, "บันทึกล้มเหลว", Toast.LENGTH_SHORT).show();
        }
    }

    private void filterByDepartments() {
        List<Employee> filtered = dbHelper.getEmployeesInDepartments(targetDepartments);
        adapter.setData(filtered);
        Toast.makeText(this, "พบ " + filtered.size() + " รายในแผนก: " + targetDepartments, Toast.LENGTH_SHORT).show();
    }

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

EmployeeAdapter.java

package com.example.sqliteindemo;

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(String.format("%,.0f บาท | %s", emp.salary, 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);
        }
    }
}

รายละเอียด IN Operator

1. Syntax พื้นฐาน

WHERE column IN (value1, value2, value3, ...)

-- เทียบเท่า:
WHERE column = value1 OR column = value2 OR column = value3 ...

2. ใช้กับ Subquery

-- หาพนักงานที่อยู่ในแผนกที่มีงบประมาณ > 1M
SELECT * FROM Employees 
WHERE department IN (SELECT name FROM Departments WHERE budget > 1000000)

3. NOT IN

WHERE department NOT IN ('IT', 'HR', 'Finance')
-- แสดงแผนกอื่นๆ ที่ไมใช่ 3 แผนกนี้

4. IN กับ NULL

-- IN (..., NULL) จะไม่ match NULL values
-- ต้องใช้: WHERE department IN ('IT', 'HR') OR department IS NULL

Dynamic IN Clause - Best Practice

// Helper Method สำหรับสร้าง Placeholders
public static String createPlaceholders(int count) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < count; i++) {
        if (i > 0) sb.append(",");
        sb.append("?");
    }
    return sb.toString();
}

// ใช้งาน
String placeholders = createPlaceholders(departments.size());
String query = "SELECT * FROM Employees WHERE department IN (" + placeholders + ")";
String[] args = departments.toArray(new String[0]);
Cursor cursor = db.rawQuery(query, args);

Performance: IN vs OR vs EXISTS

⚠️ ข้อจำกัด SQLite

• SQLITE_MAX_VARIABLE_NUMBER: Default 999 (compile-time) → IN clause ไม่ควรเกิน 999 ค่า
• หากต้องการมากกว่า: ใช้ Temporary Table หรือ JOIN แทน

สรุป

WHERE ... IN เป็นวิธีที่สะอาดและมีประสิทธิภาพสำหรับการกรองหลายค่า ใน Android ควรสร้าง Placeholders แบบ Dynamic และใช้ Parameterized Query เพื่อความปลอดภัยและประสิทธิภาพ