วิธีจัดเรียง 2 คอลัมน์ใน SQLite Android (ORDER BY หลายคอลัมน์)

ORDER BY หลายคอลัมน์ใน SQLite Android

บทความนี้สาธิตการใช้ ORDER BY หลายคอลัมน์ ใน SQLite เพื่อจัดเรียงข้อมูลตามลำดับชั้น เช่น เรียงตามชื่อก่อน แล้วค่อยเรียงตามเงินเดือน (Secondary Sort)

ขั้นตอนที่ 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="จัดการพนักงาน (Multi-column ORDER BY)"
            android:textSize="18sp"
            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/btnRefresh"
                android:layout_width="0dp"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:layout_marginStart="4dp"
                android:layout_marginEnd="4dp"
                android:text="รีเฟรช (ORDER BY name, salary)" />

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

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_CREATED = "created_at";

    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_CREATED + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
                ")";
        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;
    }

    public boolean updateEmployee(int id, String name, double salary) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(COL_NAME, name);
        cv.put(COL_SALARY, salary);
        return db.update(TABLE_EMPLOYEES, cv, COL_ID + "=?", new String[]{String.valueOf(id)}) > 0;
    }

    // ⭐ จุดสำคัญ: ORDER BY name ASC, salary DESC
    public List<Employee> getAllEmployeesOrdered() {
        List<Employee> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        
        // เรียงชื่อ A-Z ก่อน, ถ้าชื่อเหมือนกัน เรียงเงินเดือน มากไปน้อย
        String query = "SELECT * FROM " + TABLE_EMPLOYEES + 
                " ORDER BY " + COL_NAME + " ASC, " + COL_SALARY + " DESC";
        
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                Employee emp = 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_CREATED))
                );
                list.add(emp);
            } 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_CREATED))
                ));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return list;
    }
}

Model Class: Employee.java

package com.example.sqliteorderbydemo;

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

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

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

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

package com.example.sqliteorderbydemo;

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, btnRefresh, btnUpdate;
    private RecyclerView rvEmployees;
    private EmployeeAdapter adapter;
    private int selectedId = -1;

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

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

        loadOrderedEmployees();

        btnSave.setOnClickListener(v -> saveEmployee());
        btnRefresh.setOnClickListener(v -> loadOrderedEmployees());
        btnUpdate.setOnClickListener(v -> updateEmployee());

        rvEmployees.addOnItemTouchListener(new RecyclerItemClickListener(this, (view, position) -> {
            Employee emp = adapter.getItem(position);
            etName.setText(emp.name);
            etSalary.setText(String.valueOf(emp.salary));
            selectedId = emp.id;
            btnSave.setEnabled(false);
            btnUpdate.setEnabled(true);
        }));
    }

    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();
            clearForm();
            loadOrderedEmployees();
        } else {
            Toast.makeText(this, "บันทึกล้มเหลว", Toast.LENGTH_SHORT).show();
        }
    }

    private void updateEmployee() {
        if (selectedId == -1) return;
        String name = etName.getText().toString().trim();
        String salaryStr = etSalary.getText().toString().trim();

        if (name.isEmpty() || salaryStr.isEmpty()) return;

        double salary = Double.parseDouble(salaryStr);
        if (dbHelper.updateEmployee(selectedId, name, salary)) {
            Toast.makeText(this, "อัปเดตสำเร็จ", Toast.LENGTH_SHORT).show();
            clearForm();
            loadOrderedEmployees();
        }
    }

    private void loadOrderedEmployees() {
        adapter.setData(dbHelper.getAllEmployeesOrdered());
    }

    private void clearForm() {
        etName.setText("");
        etSalary.setText("");
        selectedId = -1;
        btnSave.setEnabled(true);
        btnUpdate.setEnabled(false);
    }
}

EmployeeAdapter.java

package com.example.sqliteorderbydemo;

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();
    }

    public Employee getItem(int position) {
        return items.get(position);
    }

    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);
        }
    }
}

RecyclerItemClickListener.java (Helper)

package com.example.sqliteorderbydemo;

import android.content.Context;
import android.view.GestureDetector;
import android.view.MotionEvent;
import android.view.View;
import androidx.recyclerview.widget.RecyclerView;

public class RecyclerItemClickListener implements RecyclerView.OnItemTouchListener {
    interface OnItemClickListener { void onItemClick(View view, int position); }
    
    private final OnItemClickListener listener;
    private final GestureDetector gestureDetector;

    public RecyclerItemClickListener(Context context, OnItemClickListener listener) {
        this.listener = listener;
        gestureDetector = new GestureDetector(context, new GestureDetector.SimpleOnGestureListener() {
            @Override
            public boolean onSingleTapUp(MotionEvent e) { return true; }
        });
    }

    @Override
    public boolean onInterceptTouchEvent(@NonNull RecyclerView rv, @NonNull MotionEvent e) {
        View child = rv.findChildViewUnder(e.getX(), e.getY());
        if (child != null && listener != null && gestureDetector.onTouchEvent(e)) {
            listener.onItemClick(child, rv.getChildAdapterPosition(child));
            return true;
        }
        return false;
    }

    @Override public void onTouchEvent(@NonNull RecyclerView rv, @NonNull MotionEvent e) {}
    @Override public void onRequestDisallowInterceptTouchEvent(boolean disallowIntercept) {}
}

คำอธิบาย ORDER BY หลายคอลัมน์

-- ไวยากรณ์
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], column3 [ASC|DESC]...

-- ตัวอย่าง
ORDER BY name ASC, salary DESC
-- หมายถึง: เรียงชื่อ A-Z ก่อน (Primary), ถ้าชื่อซ้ำกัน เรียงเงินเดือน มากไปน้อย (Secondary)

ORDER BY department ASC, hire_date DESC, salary ASC
-- เรียงแผนก, แล้ววันเข้างานล่าสุดก่อน, แล้วเงินเดือนน้อยไปมาก

Index สำหรับ ORDER BY (Performance)

-- สร้าง Index รองรับ ORDER BY name, salary
CREATE INDEX idx_emp_name_salary ON Employees(name ASC, salary DESC);

-- ดู Query Plan
EXPLAIN QUERY PLAN SELECT * FROM Employees ORDER BY name ASC, salary DESC;
-- ควรเห็น "USE INDEX idx_emp_name_salary"

สรุป

การใช้ ORDER BY column1, column2 ใน SQLite ทำได้โดยการคั่นคอลัมน์ด้วย Comma (,) ระบบจะเรียงตามคอลัมน์แรกก่อน แล้วค่อยเรียงคอลัมน์ถัดไปเมื่อคอลัมน์แรกมีค่าเท่ากัน ควรสร้าง Composite Index เพื่อประสิทธิภาพ