Hello Friends, Recently I was developing an Android application in which I was required to use SQLite Database. So, I developed a separate test application by which you can learn SQLite Database Handling in Android. Beside SQLite you can also learn how to use Spinner Control, TableView and TableRow in your Android application. You can also learn using TableRow programmatically as well as using in xml design. There is four activity in this test application, one for adding record, one for updating and deleting record, one for viewing all records and a main activity which provides navigation to these activities.
So, Here is the code
activity_main.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
<?xml version="1.0" encoding="utf-8"?> <android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" tools:context="com.zatackcoder.databasehandler.MainActivity"> <LinearLayout android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" android:gravity="center"> <Button android:id="@+id/addRecordB" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Add Record" /> <Button android:id="@+id/updateDeleteRecordB" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Update/Delete Record" /> <Button android:id="@+id/viewRecordsB" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="View Records" /> </LinearLayout> </android.support.constraint.ConstraintLayout> |
activity_record_add.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
<?xml version="1.0" encoding="utf-8"?> <android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" tools:context="com.zatackcoder.databasehandler.RecordAddActivity"> <TextView android:id="@+id/textView" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Add Record" android:textSize="18sp" android:textStyle="bold" app:layout_constraintBottom_toTopOf="@+id/nameET" app:layout_constraintEnd_toEndOf="parent" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="parent" app:layout_constraintTop_toTopOf="parent" /> <EditText android:id="@+id/nameET" android:layout_width="wrap_content" android:layout_height="wrap_content" android:ems="10" android:hint="@string/name" android:inputType="textPersonName" app:layout_constraintBottom_toTopOf="@+id/phoneET" app:layout_constraintEnd_toEndOf="parent" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="parent" app:layout_constraintTop_toBottomOf="@+id/textView" tools:ignore="MissingConstraints" /> <EditText android:id="@+id/phoneET" android:layout_width="wrap_content" android:layout_height="wrap_content" android:ems="10" android:hint="@string/phone" android:inputType="phone" app:layout_constraintBottom_toTopOf="@+id/emailET" app:layout_constraintEnd_toEndOf="parent" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="parent" app:layout_constraintTop_toBottomOf="@+id/nameET" tools:ignore="MissingConstraints" /> <EditText android:id="@+id/emailET" android:layout_width="wrap_content" android:layout_height="wrap_content" android:ems="10" android:hint="@string/email" android:inputType="textEmailAddress" app:layout_constraintBottom_toTopOf="@+id/updateB" app:layout_constraintEnd_toEndOf="parent" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="parent" app:layout_constraintTop_toBottomOf="@+id/phoneET" tools:ignore="MissingConstraints" /> <Button android:id="@+id/updateB" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="@string/save" app:layout_constraintBottom_toBottomOf="parent" app:layout_constraintEnd_toEndOf="parent" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="parent" app:layout_constraintTop_toBottomOf="@+id/emailET" tools:ignore="MissingConstraints" /> </android.support.constraint.ConstraintLayout> |
activity_record_update_delete.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
<?xml version="1.0" encoding="utf-8"?> <android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" tools:context="com.zatackcoder.databasehandler.RecordUpdateDeleteActivity" tools:layout_editor_absoluteY="81dp"> <Spinner android:id="@+id/idS" android:layout_width="220dp" android:layout_height="50dp" app:layout_constraintBottom_toTopOf="@+id/nameET" app:layout_constraintEnd_toEndOf="@+id/nameET" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="@+id/nameET" app:layout_constraintTop_toTopOf="parent" android:padding="5dp"/> <EditText android:id="@+id/nameET" android:layout_width="wrap_content" android:layout_height="wrap_content" android:ems="10" android:hint="@string/name" android:inputType="textPersonName" app:layout_constraintBottom_toTopOf="@+id/phoneET" app:layout_constraintEnd_toEndOf="@+id/phoneET" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="@+id/phoneET" app:layout_constraintTop_toBottomOf="@+id/idS" tools:ignore="MissingConstraints" /> <EditText android:id="@+id/phoneET" android:layout_width="wrap_content" android:layout_height="wrap_content" android:ems="10" android:hint="@string/phone" android:inputType="phone" app:layout_constraintBottom_toTopOf="@+id/emailET" app:layout_constraintEnd_toEndOf="@+id/emailET" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="@+id/emailET" app:layout_constraintTop_toBottomOf="@+id/nameET" tools:ignore="MissingConstraints" /> <EditText android:id="@+id/emailET" android:layout_width="wrap_content" android:layout_height="wrap_content" android:ems="10" android:hint="@string/email" android:inputType="textEmailAddress" app:layout_constraintBottom_toTopOf="@+id/linearLayout" app:layout_constraintEnd_toEndOf="@+id/linearLayout" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="@+id/linearLayout" app:layout_constraintTop_toBottomOf="@+id/phoneET" tools:ignore="MissingConstraints" /> <LinearLayout android:id="@+id/linearLayout" android:layout_width="wrap_content" android:layout_height="wrap_content" android:gravity="center" android:orientation="horizontal" app:layout_constraintBottom_toBottomOf="parent" app:layout_constraintEnd_toEndOf="parent" app:layout_constraintHorizontal_bias="0.5" app:layout_constraintStart_toStartOf="parent" app:layout_constraintTop_toBottomOf="@+id/emailET" tools:ignore="MissingConstraints"> <Button android:id="@+id/updateB" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:background="@android:color/holo_green_dark" android:text="Update" android:textColor="@android:color/white" /> <Button android:id="@+id/deleteB" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_margin="5dp" android:background="@android:color/holo_red_dark" android:text="Delete" android:textColor="@android:color/white" /> </LinearLayout> </android.support.constraint.ConstraintLayout> |
activity_records_view.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
<?xml version="1.0" encoding="utf-8"?> <android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" tools:context="com.zatackcoder.databasehandler.RecordsViewActivity"> <TableLayout android:id="@+id/recordsTL" android:layout_width="368dp" android:layout_height="495dp" tools:layout_editor_absoluteX="8dp" tools:layout_editor_absoluteY="8dp" tools:ignore="MissingConstraints"> <TableRow android:layout_width="match_parent" android:layout_height="match_parent" > <TextView android:id="@+id/id" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="#" android:textStyle="bold" android:padding="5dp"/> <TextView android:id="@+id/textView5" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Name" android:textStyle="bold" android:layout_weight="1" android:padding="5dp"/> <TextView android:id="@+id/textView6" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Phone" android:textStyle="bold" android:layout_weight="1" android:padding="5dp"/> <TextView android:id="@+id/textView7" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Email" android:textStyle="bold" android:layout_weight="1" android:padding="5dp"/> </TableRow> </TableLayout> </android.support.constraint.ConstraintLayout> |
MainActivity.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
package com.zatackcoder.databasehandler; import android.content.Intent; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.view.View; import android.widget.Button; public class MainActivity extends AppCompatActivity { Button addRecordB; Button updateDeleteRecordB; Button viewRecordsB; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); addRecordB = (Button) findViewById(R.id.addRecordB); updateDeleteRecordB = (Button) findViewById(R.id.updateDeleteRecordB); viewRecordsB = (Button) findViewById(R.id.viewRecordsB); addRecordB.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { startActivity(new Intent(getApplicationContext(), RecordAddActivity.class)); } }); updateDeleteRecordB.setOnClickListener(new View.OnClickListener(){ @Override public void onClick(View view) { startActivity(new Intent(getApplicationContext(), RecordUpdateDeleteActivity.class)); } }); viewRecordsB.setOnClickListener(new View.OnClickListener(){ @Override public void onClick(View view) { startActivity(new Intent(getApplicationContext(), RecordsViewActivity.class)); } }); } } |
Record.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
package com.zatackcoder.databasehandler; /** * Created by rajesh kumar sahanee on 17/12/17. */ public class Record { long id; String name; String phone; String email; public Record(long id) { this.id = id; } public Record(Long id, String name, String phone, String email) { this.id = id; this.name = name; this.phone = phone; this.email = email; } public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } /* * Overriding to show name and phone in Spinner UI * */ @Override public String toString() { return name + " (" + phone + ")"; } } |
SQLiteHelper.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
package com.zatackcoder.databasehandler; 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; /** * Created by rajesh kumar sahanee on 18/11/17. */ public class SQLiteHelper extends SQLiteOpenHelper { private static final int DATABASE_VERSION = 1; public static final String DATABASE_NAME = "databasehandler.db"; public static final String RECORD_TABLE = "records"; public static final String C_ID = "id"; public static final String C_NAME = "name"; public static final String C_PHONE = "phone"; public static final String C_EMAIL = "source"; public SQLiteHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("create table " + RECORD_TABLE + " ( " + C_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + C_NAME + " VARCHAR, " + C_PHONE + " VARCHAR, " + C_EMAIL + " VARCHAR" + ");"); } @Override public void onUpgrade(SQLiteDatabase db, int i, int i1) { db.execSQL("DROP TABLE IF EXISTS " + RECORD_TABLE); onCreate(db); } public void addRecord(Record record) { SQLiteDatabase db = this.getReadableDatabase(); ContentValues contentValues = new ContentValues(); //contentValues.put(C_ID, record.getId()); contentValues.put(C_NAME, record.getName()); contentValues.put(C_PHONE, record.getPhone()); contentValues.put(C_EMAIL, record.getEmail()); db.insert(RECORD_TABLE, null, contentValues); db.close(); } public void updateRecord(Record record) { SQLiteDatabase db = this.getReadableDatabase(); ContentValues contentValues = new ContentValues(); contentValues.put(C_NAME, record.getName()); contentValues.put(C_PHONE, record.getPhone()); contentValues.put(C_EMAIL, record.getEmail()); db.update(RECORD_TABLE, contentValues, C_ID + " = ?", new String[]{String.valueOf(record.getId())}); db.close(); } public void deleteRecord(Record record) { SQLiteDatabase db = this.getReadableDatabase(); db.delete(RECORD_TABLE, C_ID + " = ?", new String[]{String.valueOf(record.getId())}); db.close(); } public Record getRecord(long id) { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query(RECORD_TABLE, null, C_ID + " = ?", new String[]{String.valueOf(id)}, null, null, null); cursor.moveToNext(); Record record = new Record(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3)); cursor.close(); db.close(); return record; } public ArrayList<Record> getAllRecords() { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query(RECORD_TABLE, null, null, null, null, null, null); ArrayList<Record> records = new ArrayList<Record>(); Record record; if (cursor.getCount() > 0) { for (int i = 0; i < cursor.getCount(); i++) { cursor.moveToNext(); record = new Record(cursor.getLong(0)); record.setName(cursor.getString(1)); record.setPhone(cursor.getString(2)); record.setEmail(cursor.getString(3)); records.add(record); } } cursor.close(); db.close(); return records; } } |
RecordAddActivity.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
package com.zatackcoder.databasehandler; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; public class RecordAddActivity extends AppCompatActivity { EditText nameET; EditText phoneET; EditText emailET; Button saveB; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_record_add); setTitle("Add Record"); final SQLiteHelper sqLiteHelper = new SQLiteHelper(this); nameET = (EditText)findViewById(R.id.nameET); phoneET = (EditText)findViewById(R.id.phoneET); emailET = (EditText)findViewById(R.id.emailET); saveB = (Button) findViewById(R.id.updateB); saveB.setOnClickListener(new View.OnClickListener(){ @Override public void onClick(View view) { if(nameET.getText().toString().isEmpty() || phoneET.getText().toString().isEmpty()) { Toast.makeText(getApplicationContext(), "Name and Phone cannot be blank", Toast.LENGTH_LONG).show(); } else { sqLiteHelper.addRecord(new Record(0L, nameET.getText().toString(), phoneET.getText().toString(), emailET.getText().toString())); Toast.makeText(getApplicationContext(), "Record Added Successfully", Toast.LENGTH_LONG).show(); nameET.setText(""); phoneET.setText(""); emailET.setText(""); } } }); } } |
RecordUpdateDeleteActivity.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
package com.zatackcoder.databasehandler; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.AdapterView; import android.widget.ArrayAdapter; import android.widget.Button; import android.widget.EditText; import android.widget.Spinner; import android.widget.Toast; import java.util.ArrayList; public class RecordUpdateDeleteActivity extends AppCompatActivity { SQLiteHelper sqLiteHelper; ArrayList<Record> records; Spinner idS; EditText nameET; EditText phoneET; EditText emailET; Button updateB; Button deleteB; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_record_update_delete); setTitle("Update/Delete Record"); idS = (Spinner) findViewById(R.id.idS); nameET = (EditText) findViewById(R.id.nameET); phoneET = (EditText) findViewById(R.id.phoneET); emailET = (EditText) findViewById(R.id.emailET); updateB = (Button) findViewById(R.id.updateB); deleteB = (Button) findViewById(R.id.deleteB); sqLiteHelper = new SQLiteHelper(this); records = sqLiteHelper.getAllRecords(); final ArrayAdapter<Record> ids = new ArrayAdapter<>(this, android.R.layout.simple_spinner_item, records); idS.setAdapter(ids); idS.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() { @Override public void onItemSelected(AdapterView<?> adapterView, View view, int i, long l) { Record record = (Record) idS.getSelectedItem(); nameET.setText(record.getName()); phoneET.setText(record.getPhone()); emailET.setText(record.getEmail()); } @Override public void onNothingSelected(AdapterView<?> adapterView) { } }); updateB.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { if(nameET.getText().toString().isEmpty() || phoneET.getText().toString().isEmpty()) { Toast.makeText(getApplicationContext(), "Name and Phone cannot be blank", Toast.LENGTH_LONG).show(); return; } Record record = (Record) idS.getSelectedItem(); record.setName(nameET.getText().toString()); record.setPhone(phoneET.getText().toString()); record.setEmail(emailET.getText().toString()); sqLiteHelper.updateRecord(record); Toast.makeText(getApplicationContext(), "Record Updated Successfully", Toast.LENGTH_LONG).show(); } }); deleteB.setOnClickListener(new View.OnClickListener(){ @Override public void onClick(View view) { Record record = (Record) idS.getSelectedItem(); sqLiteHelper.deleteRecord(record); Toast.makeText(getApplicationContext(), "Record Deleted Successfully", Toast.LENGTH_LONG).show(); } }); } } |
RecordsViewActivity.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
package com.zatackcoder.databasehandler; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.view.ViewGroup; import android.widget.TableLayout; import android.widget.TableRow; import android.widget.TextView; import java.util.ArrayList; public class RecordsViewActivity extends AppCompatActivity { TableLayout recordsTL; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_records_view); setTitle("Records"); recordsTL = (TableLayout) findViewById(R.id.recordsTL); SQLiteHelper sqLiteHelper = new SQLiteHelper(this); ArrayList<Record> records = sqLiteHelper.getAllRecords(); for (Record record : records) { TableRow tr = new TableRow(this); TableRow.LayoutParams params = new TableRow.LayoutParams(ViewGroup.LayoutParams.WRAP_CONTENT, ViewGroup.LayoutParams.WRAP_CONTENT, 1.0f); TextView idTV = new TextView(this); idTV.setText(String.valueOf(record.getId())); idTV.setPadding(5, 5, 5, 5); TextView nameTV = new TextView(this); nameTV.setText(record.getName()); nameTV.setPadding(5, 5, 5, 5); nameTV.setLayoutParams(params); TextView phoneTV = new TextView(this); phoneTV.setText(record.getPhone()); phoneTV.setPadding(5, 5, 5, 5); phoneTV.setLayoutParams(params); TextView emailTV = new TextView(this); emailTV.setText(record.getEmail()); emailTV.setPadding(5, 5, 5, 5); emailTV.setLayoutParams(params); tr.addView(idTV, 0); tr.addView(nameTV, 1); tr.addView(phoneTV, 2); tr.addView(emailTV, 3); recordsTL.addView(tr); } } } |
Screenshots
AndroidStudio Project Download
SQLite Database Handling in Android
1 file(s) 16.57 MB
Thanks for stopping by please share if you like it
Comments