SQLite is Android's version of SQL. SQL - Structured Query Language - is a commonly used query langauge to store,access and edit data dynamically. SQLite is basically an entire SQL defined using Classes and methods defined in Android SDK. Developers can make use of these classes and methods to perform query functions on the data stored in their apps. In this app, we will be making two buttons - one for adding text records and another for views those text records. these twxt records are getting stroed in a database handled through SQLite.

Steps to develop this can be summarized below -

• In your launcher activity(here HomeActivity.class) make two buttons - Add Records and View Records.
• Add Records button takes the user to the AddRecordsActivity.class
• In the AddRecordsActivity.class we have EditTexts to receive inputs from the user and a button to save.
• We prepare a class named here as DBHelper, which provides us instance for DataBase to store/retrieve data from DataBase.
• When the user clicks on Save Button in AddRecordsActivity.class, insert_mybooks() method gets called from Database helper class which saves the data in database.
• When the user clicks on ViewRecords button in HomeActivity.class user is taken to ListingActivity.class
• ListingActivity.class uses a cursor object to traverse through the Database
• Previous and Next Buttons are used to take the user to and fro.

Used Concepts & Used Version: (Version of Android API)

• compileSdkVersion 25
• buildToolsVersion "25.0.0"

Now, let’s begin the development...

1. Make a new project

• Name the launcher activity as HomeActivity.class

• Add two buttons - Add Records and View Records.

• The two buttons take the user to different activities for adding records and listing records.

package in.sisoft.sqliteudemy; import android.content.Intent; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; public class HomeActivity extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_home); } @Override protected void onStart() { super.onStart(); DBHelper db1 = new DBHelper(this); } public void add_records(View view) { Intent i = new Intent(this,AddRecordsActivity.class); startActivity(i); } public void ListRecords(View v){ Intent i = new Intent(this,ListingActivity.class); startActivity(i); } }

2. HomeActivity.class layout

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:id="@+id/activity_home" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="in.sisoft.sqliteudemy.HomeActivity"> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Add Records" android:id="@+id/btn1" android:onClick="add_records"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="View Records" android:id="@+id/btn2" android:layout_below="@id/btn1" android:onClick="ListRecords"/> </RelativeLayout>

3. AddRecordsActivity.class

package in.sisoft.sqliteudemy; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.TextView; import android.widget.Toast; public class AddRecordsActivity extends AppCompatActivity { DBHelper dbh ; protected String author; protected String bookTitle; private String Status; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); } @Override protected void onStart() { super.onStart(); dbh = new DBHelper(this); } public void insert_mybooks(View v) { author = ((TextView) findViewById(R.id.editText2)).getText().toString(); Status = ((TextView) findViewById(R.id.editText3)).getText().toString(); bookTitle=((TextView) findViewById(R.id.editText1)).getText().toString(); dbh.insert_mybooks(bookTitle,author,Status); Toast.makeText(this, "Book Added", Toast.LENGTH_SHORT).show(); } }

When the user clicks on Submit button, inesert_mybooks(View v) gets called which then calls dbh.insert_mybooks() method. This method is present in DB Helper class(mentioned later) which finally saves the entries in the database.

4. Layout for AddRecordsActivity.class

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="match_parent" android:layout_height="match_parent"> <EditText android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/editText1" android:hint="Enter Book Name" /> <EditText android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/editText2" android:hint="Author"/> <EditText android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/editText3" android:hint="Reading Status"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Submit" android:onClick="insert_mybooks" android:id="@+id/button2"/> </LinearLayout>

5. DBhelper.class

package in.sisoft.sqliteudemy; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DBHelper extends SQLiteOpenHelper { public DBHelper(Context context) { super(context, "mydb", null, 1); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE `mybooks` ( `_id` INTEGER PRIMARY KEY AUTOINCREMENT, `book_title` TEXT, `book_author` TEXT, `status` TEXT )"); db.execSQL("insert into mybooks(book_title, book_author, status) values('Book1','Author1','Reading')"); db.execSQL("insert into mybooks(book_title, book_author, status) values('Book2','Author2','Reading')"); db.execSQL("insert into mybooks(book_title, book_author, status) values('Book3','Author3','Reading')"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } void insert_mybooks(String title, String author, String st) { SQLiteDatabase db = getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put("book_title", title); cv.put("book_author", author); cv.put("status", st); db.insert("mybooks", null, cv); db.close() ; } Cursor listRecord() { SQLiteDatabase db = getReadableDatabase(); Cursor c1; c1 = db.rawQuery("select _id, book_title, book_author, status from mybooks", null); return c1 ; } }

a) Inside DBHelper.class we create a constructor for creating instances of the database

b) OnCreate method ensures that whenever the database is prepared it saves a few initial values in it which can be used to display. We have entered 3 values here which user can view when they launch the app.

c) All the statements used in SQLite are same as SQL. You just need to know the right method and class to execute those statements. Like in this case, you can see that we are using execSQL() method which contains same SQL statements that you would use in SQL queries otherwise.

d)Here we have also defined insert_mybooks(String title, String author, String st) method that finally saves the data in database. ContentValues is a class that helps us to submit data in one go inside the database.

e) We use getReadableDatabase() and getWritableDatabse() to read and write data respectively.

f) There is another method listRecord() which provides a cursor object that can traverse through the database.

6. ListingActivity.class

package in.sisoft.sqliteudemy; import android.content.Intent; import android.database.Cursor; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.TextView; import android.widget.Toast; public class ListingActivity extends AppCompatActivity { DBHelper dblist; Cursor c12; TextView tv1 ; String value; int curs; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_listing); tv1 = (TextView) findViewById(R.id.textView2); } @Override protected void onStart() { super.onStart(); dblist = new DBHelper(this); c12 = dblist.listRecord(); curs = c12.getPosition(); if (c12.moveToFirst()) { value = c12.getString(1)+":"+c12.getString(2)+":"+c12.getString(3); } tv1.setText(value); } public void nxt(View v){ if (!c12.isLast()) { c12.moveToNext(); value = c12.getString(1) + ":" + c12.getString(2) + ":" + c12.getString(3); tv1.setText(value);} else { Toast.makeText(this, "Ät Last Record", Toast.LENGTH_SHORT).show();}} public void prev(View v) { if (!c12.isFirst()) { c12.moveToPrevious(); value = c12.getString(1) + ":" + c12.getString(2) + ":" + c12.getString(3); tv1.setText(value); } else { Toast.makeText(this, "Ät First Record", Toast.LENGTH_SHORT).show(); } } public void list_books(View v) { tv1.setText(value); } }

a)In the above activity, we initiate a cursor object during onStart() and move the cursor to the first position in the records.

b) Based upon what button user clicks on - previous/next - Cursor movements are decided.

7. Layout for ListingActivity.class

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="New Text" android:id="@+id/textView2" android:layout_margin="20dp"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Next" android:onClick="nxt" android:layout_margin="20dp"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Previous" android:onClick="prev" android:layout_margin="20dp"/> /> </LinearLayout>

Output when the App is run -

1. User is presented with two options to Add Record and View Records.

2. When user clicks on Add Records, they are taken to the AddRecordsActivity.class to add records through EditTexts

3. If the user click on View Records button , they are directed to another screen where they can view records through previous/next buttons.

4. In case the user reaches at the end of the list, they are informed through a Toast message.