DDM 2021-12-14

package com.joythis.android.simplestsqlite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import java.util.ArrayList;

public class MyContactsDB extends SQLiteOpenHelper {

    public final static String DB_NAME = "my_contacts.DB";
    //increases in DB_VERSION should cause "onUpgrade"
    public final static int DB_VERSION = 2;//onUpgrade

    public final static String TABLE_CONTACTS = "tContacts";
    public final static String COL_ID = "_id";
    public final static String COL_NAME = "cName";
    public final static String COL_PHONE = "cPhone";
    //public final static String COL_EMAIL = "cEmail";

    /*
    create table if not exists tContacts(
        _id INTEGER PRIMARY KEY NOT NULL,
        cName TEXT NOT NULL,
        cPhone TEXT NOT NULL,
        //cEmail TEXT NOT NULL
    );
     */
    public final static String CREATE_TABLE_CONTACTS =
        "create table if not exists "+TABLE_CONTACTS+"(\n"+
        COL_ID+" INTEGER PRIMARY KEY NOT NULL,\n"+
        COL_NAME+" TEXT NOT NULL,\n"+
        COL_PHONE+" TEXT NOT NULL\n);";

    public final static String DROP_TABLE_CONTACTS =
        "drop table if exists "+TABLE_CONTACTS+";";

    public MyContactsDB(
        @Nullable Context context, //context
        @Nullable String name, //DB_NAME
        @Nullable SQLiteDatabase.CursorFactory factory, //
        int version //DB_VERSION
    ) {
        super(context, name, factory, version);
    }//MyContactDB

    public MyContactsDB(
        @Nullable Context context //context
    ){
        super(
            context,
            DB_NAME,
            null,//Cursor are the result of select statement
            DB_VERSION
        );
    }//MyContact


    @Override
    public void onCreate(SQLiteDatabase db) {
        //automatically called by the framework
        //when the need to work with still non-existing
        //tables
        installDB(db);
    }//onCreate

    void installDB(SQLiteDatabase pDB){
        if (pDB!=null){
            try {
                pDB.execSQL(CREATE_TABLE_CONTACTS);
            }
            catch (Exception e){
                Log.e(
                    "@MyContactDB",
                    e.toString()
                );
            }
        }
    }//installDB

    @Override
    public void onUpgrade(
        SQLiteDatabase db,
        int oldVersion, //1
        int newVersion //2
    )
    {
        //automatically called when
        //the database changes
        //that is signaled by the database "version"
        if (newVersion>oldVersion){
            try{
                //destroy
                db.execSQL(
                    DROP_TABLE_CONTACTS
                );
                //reconstruct
                installDB(db);
            }//try
            catch (Exception e){
                Log.e(
                   "@MyContactsDB",
                   e.toString()
                );
            }//catch
        }//if
    }//onUpgrade

    public final int DOES_NOT_EXIST = -1;
    /*
    receives a MyContact
    returns the _id >= 1 in TABLE_CONTACTS
    where that MyContact already exists
    or
    -1, if it does not exist
     */
    public long getIdForContact(MyContact pC){
        SQLiteDatabase db = this.getReadableDatabase();
        if (db!=null){
            //select * from tContacts where (cName='Rita' and cPhone='123')
            String strQ = String.format(
                "select %s from %s where (%s='%s' and %s='%s')",
                //COL_NAME+","+COL_PHONE
                "*",
                TABLE_CONTACTS,
                COL_NAME,
                pC.mName,
                COL_PHONE,
                pC.mPhone
            );
            Cursor cursorResults =
                db.rawQuery(
                    strQ,
                    null
                );

            if (cursorResults!=null){
                int iHowMany = cursorResults.getCount();
                if (iHowMany>0){
                    //a contact with that name and phone
                    //already exists
                    cursorResults.moveToFirst();
                    int indexOfIdColumn =
                        cursorResults.getColumnIndex(
                            "_id"
                        );
                    long idOfAlreadyExistingContact =
                        cursorResults.getLong(
                            //0
                            indexOfIdColumn
                        );
                    return idOfAlreadyExistingContact;
                }//if
            }
        }
        return DOES_NOT_EXIST;
    }//getIdForContact

    long insertNewContact(MyContact pC){
        long iCurrentIdForContact =
            getIdForContact(pC);

        boolean bWillBeNew =
            iCurrentIdForContact == DOES_NOT_EXIST;

        if (bWillBeNew){
            SQLiteDatabase db =
                this.getWritableDatabase();
            if (db!=null){
                ContentValues cvs = new ContentValues();
                cvs.put(COL_NAME, pC.mName);
                cvs.put(COL_PHONE, pC.mPhone);

                long lWhereInserted =
                    db.insert(
                        TABLE_CONTACTS,
                        null,//we have no columns allowing null
                        cvs
                    );

                return lWhereInserted;
            }
        }

        return iCurrentIdForContact;
    }//insertNewContact

    public ArrayList<MyContact> selectByQuery(
        String pQ
    ){
        ArrayList<MyContact> alRet = new ArrayList<>();

        SQLiteDatabase db = getReadableDatabase();
        if (db!=null){
            try{
                Cursor results =
                    db.rawQuery(
                        pQ,
                        null
                    );
                if (results!=null && results.getCount()>0){
                    results.moveToFirst();
                    while(!results.isAfterLast()){
                        //read the fields of the present record from the database
                        String strName, strPhone;
                        int idxColName, idxColNumber;
                        idxColName =
                            results.getColumnIndex(COL_NAME);
                        idxColNumber =
                            results.getColumnIndex(COL_PHONE);

                        strName = results.getString(idxColName);
                        strPhone = results.getString(idxColNumber);
                        //database reading ends here

                        //accessory ops related to the method's return value
                        MyContact c = new MyContact(strName, strPhone);
                        alRet.add(c);

                        results.moveToNext();
                    }//while
                    return alRet;
                }//if
            }
            catch (Exception e){
                Log.e(
                    "@MyContactsDB",
                    e.toString()
                );
            }
        }//if
        return alRet;//via this exit, this will be an empty list
    }//selectByQuery

    public ArrayList<MyContact> selectAllByDescId(){
        String q = String.format(
        "select %s from %s order by %s DESC",
        "*",
        TABLE_CONTACTS,
        COL_ID
        );
        return selectByQuery (q);
    }//selectAllByDescId

    public ArrayList<MyContact> selectAllByAscId(){
        String q = String.format(
            "select %s from %s order by %s ASC",
            //"*",
            COL_ID+","+COL_NAME+","+COL_PHONE,
            TABLE_CONTACTS,
            COL_ID
        );
        return selectByQuery (q);
    }//selectAllByAscId

    /*
    selects every record whose name starts with "art"
    select * from tContacts where cName like 'art%'

    selects every record whose name ends with "art"
    select * from tContacts where cName like '%art'

    selects every record whose name containing "art"
    select * from tContacts where cName like '%art%'
     */
    public ArrayList<MyContact> selectAllWithNameLike(
        String pName
    ){
        String q = String.format(
        "select %s from %s where (%s like '%%%s%%') order by %s DESC",
        "*",
        TABLE_CONTACTS,
        COL_NAME,
        pName,
        COL_ID
        );
        return selectByQuery (q);
    }//selectAllWithNameLike

}//MyContactDB

****


package com.joythis.android.simplestsqlite;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Context;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Spinner;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {
    MyContactsDB mDB;

    AmUtil mUtil;

    Context mContext;
    EditText mEtName, mEtPhone;
    Spinner mSpnActionOptions;
    Button mBtnAction;

    ListView mLv;
    ArrayList<MyContact> mAl;
    ArrayAdapter<MyContact> mAd;

    View.OnClickListener mClickHandler = new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            switch(v.getId()){
                case R.id.idBtnAction:
                    performSelectedAction();
                    break;
            }
        }
    };

    void performSelectedAction(){
        switch(mSpnActionOptions.getSelectedItemPosition()){
            case 0:
                //insert
                actionInsert();
                break;
            case 1:
                //select all by id desc
                actionSelectAll();
                break;
            case 2:
                //select like name
                actionSelectLikeName();
                break;
        }//switch
    }//performSelectedAction

    void actionSelectLikeName(){
        String strName = mEtName.getText().toString().trim();
        if (strName!=null && strName.length()>0) {
            ArrayList<MyContact> res =
                    mDB.selectAllWithNameLike(strName);
            displayResults(res);
        }
        else{
            mUtil.fb("Name should not be empty!");
        }
    }

    void actionInsert(){
        String strName, strPhone;
        strName = mEtName.getText().toString();
        strPhone = mEtPhone.getText().toString();

        MyContact c = new MyContact(strName, strPhone);
        long idForExistingContactOrMinus1 =
            mDB.getIdForContact(c);

        if (idForExistingContactOrMinus1== mDB.DOES_NOT_EXIST){
            mUtil.fb("New contact about to the inserted.");
            long idxWhereInserted = mDB.insertNewContact(c);
            String strWhereInserted = String.format(
                "New MyContact created @%d",idxWhereInserted
            );
            mUtil.fb(strWhereInserted);

            /*
            to have the ListView displaying all the MyContacts
            including this last one, just inserted
             */
            actionSelectAll();
        }
        else{
            mUtil.fb("Contact already exists. Nothing done.");
        }
    }//actionInsert

    /*
    1)
    must read the existing MyContacts from the database
    via mDB
    2)
    display the existing MyContact in the ListView (mLv)
     */
    void actionSelectAll(){
        //1
        ArrayList<MyContact> alRet =
            mDB.selectAllByDescId();

        if (alRet!=null && alRet.size()>0){
            displayResults(alRet);
        }
        else{
            mUtil.fb("No records in database.");
        }
    }//actionSelectAll

    void displayResults(ArrayList<MyContact> pAl){
        if (pAl!=null && pAl.size()>0){
            this.mAl.clear(); //keep the data mem address
            for (MyContact c : pAl){
                mAl.add(c);
            }//for
            mAd.notifyDataSetChanged();
        }//if
    }//displayResults

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

        init();
    }//onCreate

    void init(){
        this.mDB = new MyContactsDB(this);

        mUtil = new AmUtil(this);

        mContext = MainActivity.this;
        mEtName = findViewById(R.id.idEtName);
        mEtPhone = findViewById(R.id.idEtNumber);
        mSpnActionOptions = findViewById(R.id.idSpnActionOptions);
        mBtnAction = findViewById(R.id.idBtnAction);

        mLv = findViewById(R.id.idLv);
        mAl = new ArrayList<>();
        mAd = new ArrayAdapter<MyContact>(
            mContext, //from the Activity
            android.R.layout.simple_list_item_1, //layout for each element that the data source provides
            mAl //data source
        );

        //behaviors
        mBtnAction.setOnClickListener(mClickHandler);
        mLv.setAdapter(mAd);

        actionSelectAll();
    }//init
}