Android Studio SQLite Database Example
Example demonstrating the use of SQLite Database.
It creates a basic operations of SQLite database operations.
SQLiteDatabase is a class that allowed us to performCreate, Retrieve , Update, and Delete data (CRUD) operation. In this tutorial we will show you how to useSQLiteDatabase to perform CRUD operation in Android.
Steps of Create Android Studio 2.1.1 with SQLite Database
You will use Android studio to create an Android application under a package com.example.root.mysqliteapplication1. While creating this project, make sure you Target SDK and Compile With at the latest version of Android SDK to use higher levels of APIs.
Modify src/MainActivity.java file to get references of all the XML components.
Create new class MyDBHandler.java that will manage the database work and Products.java
Tool Used
1. Android Studio 2.1.1
To Do
In this tutorial we will going to create an app that allow to Database operation (Create, Retrieve , Update, and Delete data (CRUD)), product record.
Example
This example shows a very simple example which is to just store important data like product id and productname using SQLite Database in the android studio.
Screenshot
MainActivity.java.
package com.example.root.mysqliteapplication1;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.util.Log;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.EditText;
import android.widget.TextView;
public class MainActivity extends AppCompatActivity {
// Declare references
EditText userInput;
TextView recordsTextView;
MyDBHandler dbHandler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
userInput = (EditText) findViewById(R.id.buckysInput);
recordsTextView = (TextView) findViewById(R.id.buckysText);
/* Can pass nulls because of the constants in the helper.
* the 1 means version 1 so don't run update.
*/
dbHandler = new MyDBHandler(this, null, null, 1);
printDatabase();
}
//Print the database results
public void printDatabase(){
String dbString = dbHandler.databaseToString();
recordsTextView.setText(dbString);
userInput.setText("");
}
//add your elements onclick methods.
//Add a product to the database
public void addButtonClicked(View view){
// dbHandler.add needs an object parameter.
Products product = new Products(userInput.getText().toString());
dbHandler.addProduct(product);
printDatabase();
}
//Delete items
public void deleteButtonClicked(View view){
// dbHandler delete needs string to find in the db
String inputText = userInput.getText().toString();
dbHandler.deleteProduct(inputText);
printDatabase();
}
}
activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<android.support.design.widget.CoordinatorLayout 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"
android:fitsSystemWindows="true"
tools:context="com.example.root.mysqliteapplication1.MainActivity">
<android.support.design.widget.AppBarLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:theme="@style/AppTheme.AppBarOverlay">
<android.support.v7.widget.Toolbar
android:id="@+id/toolbar"
android:layout_width="match_parent"
android:layout_height="?attr/actionBarSize"
android:background="?attr/colorPrimary"
app:popupTheme="@style/AppTheme.PopupOverlay" />
</android.support.design.widget.AppBarLayout>
<include layout="@layout/content_main" />
<android.support.design.widget.FloatingActionButton
android:id="@+id/fab"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="bottom|end"
android:layout_margin="@dimen/fab_margin"
android:src="@android:drawable/ic_dialog_email" />
</android.support.design.widget.CoordinatorLayout>
content_main.xml
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout 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"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
app:layout_behavior="@string/appbar_scrolling_view_behavior"
tools:context="com.example.root.mysqliteapplication1.MainActivity"
tools:showIn="@layout/activity_main">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Basic SQLite Database Operations"
android:textStyle="bold"
android:id="@+id/txtHeading"
android:fontFeatureSettings=" "
android:background="#f2bcbc" />
<EditText
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/buckysInput"
android:width="300dp"
android:layout_below="@+id/txtHeading"
android:layout_marginTop="15dp"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:hint="Product Name" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceLarge"
android:text="Large Text"
android:id="@+id/buckysText"
android:layout_below="@+id/txthedingproduct"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true"
android:layout_alignRight="@+id/deleteButton"
android:layout_alignEnd="@+id/deleteButton" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="ADD"
android:id="@+id/addButton"
android:onClick="addButtonClicked"
android:layout_below="@+id/buckysInput"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="DELETE"
android:id="@+id/deleteButton"
android:onClick="deleteButtonClicked"
android:layout_below="@+id/buckysInput"
android:layout_toRightOf="@+id/txtHeading"
android:layout_toEndOf="@+id/txtHeading" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Products List"
android:id="@+id/txthedingproduct"
android:fontFeatureSettings=" "
android:background="#f2bcbc"
android:layout_below="@+id/addButton"
android:layout_marginTop="15dp"
android:textStyle="bold"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />
</RelativeLayout>
Products.java
package com.example.root.mysqliteapplication1;
public class Products {
private int _id;
private String _productname;
//Added this empty constructor in lesson 50 in case we ever want to create the object and assign it later.
public Products(){
}
public Products(String productName) {
this._productname = productName;
}
public int get_id() {
return _id;
}
public void set_id(int _id) {
this._id = _id;
}
public String get_productname() {
return _productname;
}
public void set_productname(String _productname) {
this._productname = _productname;
}
}
MyDBHandler.java
package com.example.root.mysqliteapplication1;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.Cursor;
import android.content.Context;
import android.content.ContentValues;
public class MyDBHandler extends SQLiteOpenHelper{
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "productDB.db";
public static final String TABLE_PRODUCTS = "products";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_PRODUCTNAME = "productname";
//We need to pass database information along to superclass
public MyDBHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String query = "CREATE TABLE " + TABLE_PRODUCTS + "(" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_PRODUCTNAME + " TEXT " +
");";
db.execSQL(query);
}
//Lesson 51
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRODUCTS);
onCreate(db);
}
//Add a new row to the database
public void addProduct(Products product){
ContentValues values = new ContentValues();
values.put(COLUMN_PRODUCTNAME, product.get_productname());
SQLiteDatabase db = getWritableDatabase();
db.insert(TABLE_PRODUCTS, null, values);
db.close();
}
//Delete a product from the database
public void deleteProduct(String productName){
SQLiteDatabase db = getWritableDatabase();
db.execSQL("DELETE FROM " + TABLE_PRODUCTS + " WHERE " + COLUMN_PRODUCTNAME + "=\"" + productName + "\";");
}
// this is goint in record_TextView in the Main activity.
public String databaseToString(){
String dbString = "";
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_PRODUCTS + " WHERE 1";// why not leave out the WHERE clause?
//Cursor points to a location in your results
Cursor recordSet = db.rawQuery(query, null);
//Move to the first row in your results
recordSet.moveToFirst();
//Position after the last row means the end of the results
while (!recordSet.isAfterLast()) {
// null could happen if we used our empty constructor
if (recordSet.getString(recordSet.getColumnIndex("productname")) != null) {
dbString += recordSet.getString(recordSet.getColumnIndex("productname"));
dbString += "\n";
}
recordSet.moveToNext();
}
db.close();
return dbString;
}
}
