home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> Android

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

Query SQLite ด้วย WHERE ... BETWEEN ... AND

บทความนี้สาธิตการใช้ BETWEEN ... AND Operator ใน SQLite เพื่อกรองข้อมูลในช่วง (Range Query) ร่วมกับ AND เพื่อเพิ่มเงื่อนไขอื่นๆ

ขั้นตอนที่ 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="กรองพนักงานเงินเดือน 10,000 - 50,000 (BETWEEN AND)"
            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="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/btnFilter"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:layout_marginStart="4dp"
                android:text="กรอง 10K-50K" />

        </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.sqlitebetweendemo;

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 = "SalaryDB";
    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";

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

    // ⭐ BETWEEN ... AND Query
    public List<Employee> getEmployeesInSalaryRange(double minSalary, double maxSalary) {
        List<Employee> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        // BETWEEN เป็น Inclusive (รวมค่าขอบเขต)
        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " WHERE " + COL_SALARY + " BETWEEN ? AND ?" +
                " ORDER BY " + COL_SALARY + " ASC";

        String[] args = {String.valueOf(minSalary), String.valueOf(maxSalary)};
        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))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    // BETWEEN ร่วมกับ AND (หลายเงื่อนไข)
    public List<Employee> getEmployeesInRangeAndNamePattern(double minSalary, double maxSalary, String namePattern) {
        List<Employee> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " WHERE " + COL_SALARY + " BETWEEN ? AND ?" +
                " AND " + COL_NAME + " LIKE ?" +
                " ORDER BY " + COL_SALARY + " DESC";

        String[] args = {String.valueOf(minSalary), String.valueOf(maxSalary), namePattern};
        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))
                ));
            } 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))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }
}

Model: Employee.java

package com.example.sqlitebetweendemo;

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

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

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

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

package com.example.sqlitebetweendemo;

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 etName, etSalary;
    private Button btnSave, btnFilter;
    private RecyclerView rvEmployees;
    private EmployeeAdapter adapter;

    // ช่วงเงินเดือนที่ต้องการกรอง
    private static final double MIN_SALARY = 10000;
    private static final double MAX_SALARY = 50000;

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

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

        loadAllEmployees();

        btnSave.setOnClickListener(v -> saveEmployee());
        btnFilter.setOnClickListener(v -> filterEmployees());
    }

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

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

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

    private void filterEmployees() {
        List<Employee> filtered = dbHelper.getEmployeesInSalaryRange(MIN_SALARY, MAX_SALARY);
        adapter.setData(filtered);
        Toast.makeText(this, String.format("พบ %d ราย (เงินเดือน %.0f - %.0f)", 
                filtered.size(), MIN_SALARY, MAX_SALARY), Toast.LENGTH_SHORT).show();
    }

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

EmployeeAdapter.java

package com.example.sqlitebetweendemo;

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.tvSalary.setText(String.format("เงินเดือน: %,.0f บาท (ID: %d)", emp.salary, emp.id));
    }

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

รายละเอียด BETWEEN ... AND

1. Inclusive Range (รวมค่าขอบเขต)

salary BETWEEN 10000 AND 50000
-- เทียบเท่า: salary >= 10000 AND salary <= 50000
-- รวมทั้ง 10000 และ 50000

2. ร่วมกับ AND (Multiple Conditions)

WHERE salary BETWEEN 10000 AND 50000 
  AND name LIKE 'สม%'
  AND department = 'IT'

3. Date Range (วันที่)

-- SQLite Date Format: 'YYYY-MM-DD'
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

-- หรือใช้ date()
WHERE date(created_at) BETWEEN date('now', '-30 days') AND date('now')

4. NOT BETWEEN (นอกช่วง)

WHERE salary NOT BETWEEN 10000 AND 50000
-- salary < 10000 OR salary > 50000

⚠️ ข้อควรระวัง

  1. NULL Values: BETWEEN จะคืน FALSE ถ้าคอลัมน์เป็น NULL → ใช้ WHERE salary IS NOT NULL AND salary BETWEEN ? AND ?
  2. Data Type: ให้แน่ใจว่า Column Type และ Parameter Type ตรงกัน (REAL กับ Double, TEXT กับ String)
  3. Performance: เพิ่ม Index บนคอลัมน์ที่ใช้ BETWEEN บ่อย: CREATE INDEX idx_salary ON Employees(salary)

สรุป

BETWEEN ... AND ทำให้ Query อ่านง่ายกว่า = AND = และ SQLite Optimize ให้เหมือนกัน ควรใช้ Parameterized Query (?) เพื่อป้องกัน SQL Injection และจัดการ NULL Values ให้ถูกต้อง