Android SQLite Database Tutorial [CRUD Operations on 2 tables] – 4

29 December, 201717 January, 2018 Hasan Abdullahঅ্যান্ড্রয়েড অ্যাপ ডেভেলপমেন্ট, Android Local Storage

• Primary key of student table is _id
• registration_no field of student table must be unique
• Primary key of subject table is _id
• Foreign key of subject table is fk_registration_no
• fk_registration_no field of subject table is not unique
• subject_code field of subject table is not unique
• There is a unique constraint in subject table by fk_registration_no and subject_code field
• When a student is removed from student table, all subjects of that student must be removed from subject table
• When the registration number of a student will be updated in student table, fk_registration_no field of subject table must update for associated student
• If all student table is removed, then all subject will be removed
• If all subject is removed at a time, no need to remove any student from student table

মনে হতে পারে এই ছোট্ট কাজ করার জন্য এত ফিরিস্তি কেন দেয়া লাগছে? বা উপরের এত এত কাজগুলো সব ম্যানুয়াল্যি হ্যান্ডেল করতে হবে? উত্তর হচ্ছে “না”। উপরের প্রায় সবগুলো পয়েন্ট ডেটাবেজ বানানোর সময় জাস্ট একবার কোড লিখতে হবে। এরপর মনের সুখে শুধু CRUD operation চালানো যাবে।

Database Creation by Java Code

আপনি যেহেতু আগের পোস্টগুলো দেখেছেন তাই আর নতুন করে DatabaseHelper ক্লাস সম্পর্কে আলোচনা করছি না। সরাসরি এই ক্লাসের onCreate() মেথডে চলে যাব।

@Override
public void onCreate(SQLiteDatabase db) {

    // Create tables SQL execution
    String CREATE_STUDENT_TABLE = "CREATE TABLE " + Config.TABLE_STUDENT + "("
            + Config.COLUMN_STUDENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + Config.COLUMN_STUDENT_NAME + " TEXT NOT NULL, "
            + Config.COLUMN_STUDENT_REGISTRATION + " INTEGER NOT NULL UNIQUE, "
            + Config.COLUMN_STUDENT_PHONE + " TEXT, " //nullable
            + Config.COLUMN_STUDENT_EMAIL + " TEXT " //nullable
            + ")";

    String CREATE_SUBJECT_TABLE = "CREATE TABLE " + Config.TABLE_SUBJECT + "("
            + Config.COLUMN_SUBJECT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + Config.COLUMN_REGISTRATION_NUMBER + " INTEGER NOT NULL, "
            + Config.COLUMN_SUBJECT_NAME + " TEXT NOT NULL, "
            + Config.COLUMN_SUBJECT_CODE + " INTEGER NOT NULL, "
            + Config.COLUMN_SUBJECT_CREDIT + " REAL, " //nullable
            + "FOREIGN KEY (" + Config.COLUMN_REGISTRATION_NUMBER + ") REFERENCES " + Config.TABLE_STUDENT + "(" + Config.COLUMN_STUDENT_REGISTRATION + ") ON UPDATE CASCADE ON DELETE CASCADE, "
            + "CONSTRAINT " + Config.STUDENT_SUB_CONSTRAINT + " UNIQUE (" + Config.COLUMN_REGISTRATION_NUMBER + "," + Config.COLUMN_SUBJECT_CODE + ")"
            + ")";

    db.execSQL(CREATE_STUDENT_TABLE);
    db.execSQL(CREATE_SUBJECT_TABLE);
}

গত পর্বেই বলেছিলাম hardcode করে টেবিল/কলামের নাম না লিখে আলাদা একটা ভেরিয়েবল ইউজ করতে। এজন্য আমি Config.java নামের একটা ক্লাসে দরকারি স্ট্রিংগুলো লিখে রেখেছি।

Config.java ক্লাসের কোড এরকমঃ

public class Config {

    public static final String DATABASE_NAME = "student-db";

    //column names of student table
    public static final String TABLE_STUDENT = "student";
    public static final String COLUMN_STUDENT_ID = "_id";
    public static final String COLUMN_STUDENT_NAME = "name";
    public static final String COLUMN_STUDENT_REGISTRATION = "registration_no";
    public static final String COLUMN_STUDENT_PHONE = "phone";
    public static final String COLUMN_STUDENT_EMAIL = "email";

    //column names of subject table
    public static final String TABLE_SUBJECT = "subject";
    public static final String COLUMN_SUBJECT_ID = "_id";
    public static final String COLUMN_REGISTRATION_NUMBER = "fk_registration_no";
    public static final String COLUMN_SUBJECT_NAME = "name";
    public static final String COLUMN_SUBJECT_CODE = "subject_code";
    public static final String COLUMN_SUBJECT_CREDIT = "credit";
    public static final String STUDENT_SUB_CONSTRAINT = "student_sub_unique";

    //others for general purpose key-value pair data
    public static final String TITLE = "title";
    public static final String CREATE_STUDENT = "create_student";
    public static final String UPDATE_STUDENT = "update_student";
    public static final String CREATE_SUBJECT = "create_subject";
    public static final String UPDATE_SUBJECT = "update_subject";
    public static final String STUDENT_REGISTRATION = "student_registration";
}

ফিরে আসি DatabaseHelper.java ক্লাসের onCreate() মেথডে। আগের পর্বে একটা টেবিল create করেছিলাম তাই onCreate() এ শুধু CREATE_STUDENT_TABLE নামক (SQL) String বানানো হয়েছিল। এটা কিন্তু জাস্ট একটা স্ট্রিং। onCreate() এর শেষে এই স্ট্রিংটা execute করা হয়েছে। এখন যেহেতু আমরা ২ টা টেবিল বানাতে চাই অতএব ২ টা query string লাগবে। প্রথমটা গত পর্বের মতই। সেখানে নতুন কিছু নাই। কিন্তু সাবজেক্ট টেবিল বানানোর query string এ কিছু কারুকাজ (!) করতে হবে।

বারবার উপরে গিয়ে কোড খুঁজে বের করার পেইন নিতে চাচ্ছি না। তাই subject table create করার query string এর পার্টটা আলাদা করে নিচে দিচ্ছিঃ

String CREATE_SUBJECT_TABLE = "CREATE TABLE " + Config.TABLE_SUBJECT + "("
        + Config.COLUMN_SUBJECT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + Config.COLUMN_REGISTRATION_NUMBER + " INTEGER NOT NULL, "
        + Config.COLUMN_SUBJECT_NAME + " TEXT NOT NULL, "
        + Config.COLUMN_SUBJECT_CODE + " INTEGER NOT NULL, "
        + Config.COLUMN_SUBJECT_CREDIT + " REAL, " //nullable
        + "FOREIGN KEY (" + Config.COLUMN_REGISTRATION_NUMBER + ") REFERENCES " + Config.TABLE_STUDENT + "(" + Config.COLUMN_STUDENT_REGISTRATION + ") ON UPDATE CASCADE ON DELETE CASCADE, "
        + "CONSTRAINT " + Config.STUDENT_SUB_CONSTRAINT + " UNIQUE (" + Config.COLUMN_REGISTRATION_NUMBER + "," + Config.COLUMN_SUBJECT_CODE + ")"
        + ")";

টেবিল বানানোর জন্য যেই SQL instruction-গুলো দিতে হয় এখানেও সেগুলোই দেয়া হয়েছে। প্রথম ৫/৬ লাইন না বুঝার কিছু নাই। বলে দিচ্ছি কী কী কলাম আমাদের দরকার। শেষ ২-৩ লাইনে আমরা foreign key আর unique constraint অ্যাড করেছি।

Foreign key define করার syntax-টা হচ্ছেঃ

FOREIGN KEY <(যেই কলামকে এই টেবিলে ফরেইন কী হিসাবে ইউজ করব তার নাম)> REFERENCES <ফরেইন কী-টা যেই টেবিলের ইউনিক ফিল্ড তার নাম> <(ফরেইন কী ঐ টেবিলে যেই নামে আছে তা)>

Subject table এর ফরেইন কী ডিফাইন করার জন্য দরকার নিচের এই টেক্সটটুকুঃ

FOREIGN KEY (fk_registration_no) REFERENCES student(registration_no)

এই কমান্ডটুকু নিশ্চিত করবে যে “সাবজেক্ট টেবিলে এমন কোনো রেজিস্ট্রেশন নাম্বার এন্ট্রি হবে না যেই রেজিস্ট্রেশন নাম্বার স্টুডেন্ট টেবিলে নাই”। কিন্তু আমাদের আরো কিছু শর্ত ছিল। আমরা চাই স্টুডেন্ট ডিলেট হলে তার সাবজেক্টগুলো ডিলেট করতে। স্টুডেন্টের রেজিস্ট্রেশন নাম্বার আপডেট হলে সাবজেক্ট টেবিলেও ঐ স্টুডেন্টের রেজিস্ট্রেশন নাম্বারগুলো আপডেট করতে। এজন্য foreign key এর instruction এর পরে জুড়ে দিতে হবে নিচের লাইনটিঃ

ON UPDATE CASCADE ON DELETE CASCADE

এই কমান্ডটুকুই আপডেট-ডিলেটের শর্তগুলো পূরণ করবে! মজা না? আমাদেরকে ম্যানুয়াল্যি প্রতিবার ডেটা আপডেট-ডিলেটে আর চেক করা লাগছে না। আপনি যদি স্টুডেন্ট ডিলেট করলে সাবজেক্ট ডিলেট করতে না চান তাহলে ON DELETE CASCADE অংশটুকু বাদ দিবেন। যদি রেজিস্ট্রেশন নাম্বার আপডেট হলে সাবজেক্ট টেবিলেও তা আপডেট না করতে চান তাহলে ON UPDATE CASCADE অংশটুকু বাদ দিবেন। আমাদের যেহেতু উভয় শর্তই পূরণ করতে হবে তাই দুইটা clause-ই রেখেছি।

আচ্ছা! স্টুডেন্টের রেজিস্ট্রেশন নাম্বার আপডেট হলে সাবজেক্ট টেবিলেও ঐ রেজিস্ট্রেশন নাম্বার আপডেট করা প্রয়োজন কেন? একটু চিন্তা করে দেখেন! কোনো স্টুডেন্টের রেজিস্ট্রেশন নাম্বার 1200. এই নাম্বারের against এ কয়েকটা সাবজেক্ট এন্ট্রি দেয়া হয়েছে সাবজেক্ট টেবিলে। আপনি স্টুডেন্ট টেবিলে এই স্টুডেন্টের রেজিস্ট্রেশন নাম্বার আপডেট করে 1300 করলেন। কিন্তু সাবজেক্ট টেবিলে 1200 এর জায়গায় 1300 করলেন না। তাহলে ঘটনা কী ঘটবে? আপনি ঐ স্টুডেন্টের নতুন রেজিস্ট্রেশন নাম্বার দিয়ে সাবজেক্ট টেবিলে সাবজেক্ট খুঁজবেন, কিন্তু দেখবেন এই স্টুডেন্টের কোনো সাবজেক্টই নাই। থাকবে কিভাবে? সাবজেক্ট আছে 1200 রোল এর, কিন্তু আপনি সার্চ করছেন 1300 দিয়ে। তাই ON UPDATE CASCADE টা জরুরি। আর ON DELETE CASCADE কেন দরকার? মেমরির অপচয় রোধ করার জন্য! যেই স্টুডেন্টই ডেটাবেজে নাই সেই স্টুডেন্টের সাবজেক্টের ইনফরমেশন রেখে লাভ কী হবে? অহেতুক স্পেস দখল করে রাখবে না?

আমাদের আর একটা শর্ত পূরণ করা বাকি আছে! তা হচ্ছে একই স্টুডেন্ট একটা সাবজেক্ট ২ বার নিতে পারবে না। শুধু fk_registration_no কে unique বলে দেয়া যাচ্ছে না। যদি বলি তবে একটা স্টুডেন্ট একটার বেশি সাবজেক্টই নিতে পারবে না। কারণ তখন সাবজেক্ট টেবিলে fk_registration_no হিসাবে কোনো একটা সংখ্যাকে একবারই এন্ট্রি দেয়া যাবে। একই ভাবে subject_code-কেও unique বলতে পারছি না। তাহলে একটা সাবজেক্ট একজন স্টুডেন্টই পড়তে পারবে। তাহলে যেটা করতে হবে তা হচ্ছে স্টুডেন্টের রেজিস্ট্রেশন নাম্বার আর সাবজেক্ট কোড মিলিয়ে unique constraint বলে দিতে হবে। তাহলে সাবজেক্ট টেবিলে একটার বেশি row তে একই সাথে fk_registration_no ও subject_code same হবে না। আর এই প্যাচানো কাজটা এক লাইনের কমান্ড দিয়ে করা হয়েছে CREATE_SUBJECT_TABLE স্ট্রিংয়ের শেষ লাইনেঃ

“CONSTRAINT ” + Config.STUDENT_SUB_CONSTRAINT + ” UNIQUE (” + Config.COLUMN_REGISTRATION_NUMBER + “,” + Config.COLUMN_SUBJECT_CODE + “)”

এটার জেনারেল ফরমেট হচ্ছেঃ

CONSTRAINT <ইচ্ছা মত একটা নাম> UNIQUE (<প্রথম কলাম, দ্বিতীয় কলাম>)

কোডে আসলে এক্সিকিউট হচ্ছে নিচের কমান্ডটাঃ

CONSTRAINT student_sub_unique UNIQUE (fk_registration_no, subject_code)

উল্লেখ্য constraint এর ইচ্ছা মত একটা নাম (যেমন আমি ব্যবহার করেছি student_sub_unique) দেয়া mandatory নয়, তবে recommended.

ডেটাবেজ যেন ফরেইন কী এর constraint অনুযায়ী ডেটা আপডেট ডিলেট করতে পারে সে জন্য এটাকে enable করতে হবে। এটা enable করার জন্য DatabaseHelper.java ক্লাসে নিচের মেথডটি override করে foreign key constraint enable করতে হবেঃ

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);

    //enable foreign key constraints like ON UPDATE CASCADE, ON DELETE CASCADE
    db.execSQL("PRAGMA foreign_keys=ON;");
}

আপনার অ্যাপের minimum SDK version যদি API 16 বা এর উপরে হয় তাহলে আপনি নিচের কোডটা ইউজ করতে পারেনঃ

@Override
public void onConfigure(SQLiteDatabase db){
    db.setForeignKeyConstraintsEnabled(true);
}

CRUD Operation

গত পর্বে স্টুডেন্টের ডেটার create, read, update, delete সবগুলো অপারেশন দেখানো হয়েছিল। সাবজেক্টের ডেটার ক্ষেত্রেও কাজগুলো একই রকম। তাই আর ভেঙে ভেঙে ব্যাখ্যা করছি না। শুধু subject টেবিলের মেথডগুলো এখানে উল্লেখ করছিঃ

public class DatabaseQueryClass {

    private Context context;

    public DatabaseQueryClass(Context context){
        this.context = context;
        Logger.addLogAdapter(new AndroidLogAdapter());
    }
    /*
       All methods of student CRUD goes here
    */
    
    // subjects
    public long insertSubject(Subject subject, long registrationNo){
        long rowId = -1;
        DatabaseHelper databaseHelper = DatabaseHelper.getInstance(context);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(Config.COLUMN_SUBJECT_NAME, subject.getName());
        contentValues.put(Config.COLUMN_SUBJECT_CODE, subject.getCode());
        contentValues.put(Config.COLUMN_SUBJECT_CREDIT, subject.getCredit());
        contentValues.put(Config.COLUMN_REGISTRATION_NUMBER, registrationNo);

        try {
            rowId = sqLiteDatabase.insertOrThrow(Config.TABLE_SUBJECT, null, contentValues);
        } catch (SQLiteException e){
            Logger.d(e);
            Toast.makeText(context, e.getMessage(), Toast.LENGTH_LONG).show();
        } finally {
            sqLiteDatabase.close();
        }

        return rowId;
    }

    public Subject getSubjectById(long subjectId){
        DatabaseHelper databaseHelper = DatabaseHelper.getInstance(context);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getReadableDatabase();

        Subject subject = null;

        Cursor cursor = null;
        try{
            cursor = sqLiteDatabase.query(Config.TABLE_SUBJECT, null,
                    Config.COLUMN_SUBJECT_ID + " = ? ", new String[] {String.valueOf(subjectId)},
                    null, null, null);

            if(cursor!=null && cursor.moveToFirst()){
                String subjectName = cursor.getString(cursor.getColumnIndex(Config.COLUMN_SUBJECT_NAME));
                int subjectCode = cursor.getInt(cursor.getColumnIndex(Config.COLUMN_SUBJECT_CODE));
                double subjectCredit = cursor.getDouble(cursor.getColumnIndex(Config.COLUMN_SUBJECT_CREDIT));

                subject = new Subject(subjectId, subjectName, subjectCode, subjectCredit);
            }
        } catch (SQLiteException e){
            Toast.makeText(context, e.getMessage(), Toast.LENGTH_LONG).show();
        } finally {
            if(cursor!=null)
                cursor.close();
            sqLiteDatabase.close();
        }

        return subject;
    }

    public long updateSubjectInfo(Subject subject){

        long rowCount = 0;
        DatabaseHelper databaseHelper = DatabaseHelper.getInstance(context);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put(Config.COLUMN_SUBJECT_NAME, subject.getName());
        contentValues.put(Config.COLUMN_SUBJECT_CODE, subject.getCode());
        contentValues.put(Config.COLUMN_SUBJECT_CREDIT, subject.getCredit());

        try {
            rowCount = sqLiteDatabase.update(Config.TABLE_SUBJECT, contentValues,
                    Config.COLUMN_SUBJECT_ID + " = ? ",
                    new String[] {String.valueOf(subject.getId())});
        } catch (SQLiteException e){
            Logger.d("Exception: " + e.getMessage());
            Toast.makeText(context, e.getMessage(), Toast.LENGTH_LONG).show();
        } finally {
            sqLiteDatabase.close();
        }

        return rowCount;
    }

    public List<Subject> getAllSubjectsByRegNo(long registrationNo){
        DatabaseHelper databaseHelper = DatabaseHelper.getInstance(context);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getReadableDatabase();

        List<Subject> subjectList = new ArrayList<>();
        Cursor cursor = null;
        try{
            cursor = sqLiteDatabase.query(Config.TABLE_SUBJECT,
                    new String[] {Config.COLUMN_SUBJECT_ID, Config.COLUMN_SUBJECT_NAME, Config.COLUMN_SUBJECT_CODE, Config.COLUMN_SUBJECT_CREDIT},
                    Config.COLUMN_REGISTRATION_NUMBER + " = ? ",
                    new String[] {String.valueOf(registrationNo)},
                    null, null, null);

            if(cursor!=null && cursor.moveToFirst()){
                subjectList = new ArrayList<>();
                do {
                    int id = cursor.getInt(cursor.getColumnIndex(Config.COLUMN_SUBJECT_ID));
                    String subjectName = cursor.getString(cursor.getColumnIndex(Config.COLUMN_SUBJECT_NAME));
                    int subjectCode = cursor.getInt(cursor.getColumnIndex(Config.COLUMN_SUBJECT_CODE));
                    double subjectCredit = cursor.getDouble(cursor.getColumnIndex(Config.COLUMN_SUBJECT_CREDIT));

                    subjectList.add(new Subject(id, subjectName, subjectCode, subjectCredit));
                } while (cursor.moveToNext());
            }
        } catch (SQLiteException e){
            Toast.makeText(context, e.getMessage(), Toast.LENGTH_LONG).show();
        } finally {
            if(cursor!=null)
                cursor.close();
            sqLiteDatabase.close();
        }

        return subjectList;
    }

    public boolean deleteSubjectById(long subjectId) {
        DatabaseHelper databaseHelper = DatabaseHelper.getInstance(context);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();

        int row = sqLiteDatabase.delete(Config.TABLE_SUBJECT,
                Config.COLUMN_SUBJECT_ID + " = ? ", new String[]{String.valueOf(subjectId)});

        return row > 0;
    }

    public boolean deleteAllSubjectsByRegNum(long registrationNum) {
        DatabaseHelper databaseHelper = DatabaseHelper.getInstance(context);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();

        int row = sqLiteDatabase.delete(Config.TABLE_SUBJECT,
                Config.COLUMN_REGISTRATION_NUMBER + " = ? ", new String[]{String.valueOf(registrationNum)});

        return row > 0;
    }

    public long getNumberOfSubject(){
        long count = -1;
        DatabaseHelper databaseHelper = DatabaseHelper.getInstance(context);
        SQLiteDatabase sqLiteDatabase = databaseHelper.getWritableDatabase();

        try {
            count = DatabaseUtils.queryNumEntries(sqLiteDatabase, Config.TABLE_SUBJECT);
        } catch (SQLiteException e){
            Logger.d("Exception: " + e.getMessage());
            Toast.makeText(context, e.getMessage(), Toast.LENGTH_LONG).show();
        } finally {
            sqLiteDatabase.close();
        }

        return count;
    }

}

প্রোজেক্টটির সম্পূর্ণ সোর্সকোড পাওয়া যাবে আমার গিটহাব রিপোজিটরিতে  Multiple (two) tables CRUD Operation শিরোনামে।

আপনার মতামত, সমালোচনা, পরামর্শ একান্ত কাম্য। ধন্যবাদ।