Android SQLite database: Using SQLite database in android application, we can save number of temporary records locally the user’s device. We can create different table with specific column types and store the required data. One useful usage of SQLite database can be creating OFFLINE database for the application when the internet connectivity isn’t available. We can then later sync the data once the connection is available and delete the records from SQLite database tables. In this post we will see a simple example of creating a SQLite database and adding data.
The database will contain a table for storing Product information, this stored data will be displayed on a Android ListView control. DOWNLOAD SOURCE CODE
Create a layout file in your project with name products.xml and edit it as below:
res > layout > products.xml:
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical"> <TableLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:padding="5dp"> <TableRow android:layout_width="match_parent" android:layout_height="wrap_content" android:weightSum="5"> <TextView android:layout_height="wrap_content" android:layout_weight="1" android:text="Product Name" /> <EditText android:id="@+id/txtProductName" android:layout_height="wrap_content" android:layout_weight="4" /> </TableRow> <TableRow android:layout_width="match_parent" android:layout_height="wrap_content" android:weightSum="5"> <TextView android:layout_height="wrap_content" android:layout_weight="1" android:text="Product Category" /> <EditText android:id="@+id/txtProductCategory" android:layout_height="wrap_content" android:layout_weight="4" /> </TableRow> <TableRow android:layout_width="match_parent" android:layout_height="wrap_content" android:weightSum="5"> <TextView android:layout_height="wrap_content" android:layout_weight="1" android:text="Price" /> <EditText android:id="@+id/txtProductPrice" android:inputType="numberDecimal" android:layout_height="wrap_content" android:layout_weight="4" /> </TableRow> </TableLayout> <Button android:id="@+id/btnSave" android:layout_height="50dp" android:layout_width="wrap_content" android:layout_margin="5dp" android:layout_gravity="center" android:onClick="saveProduct" android:text="SAVE" /> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="horizontal" android:padding="4dp" android:background="@color/colorPrimary" android:layout_columnSpan="1" android:weightSum="8"> <TextView android:layout_width="0dp" android:layout_height="wrap_content" android:text="ID" android:textColor="@color/colorWhite" android:layout_weight="1"/> <TextView android:layout_width="0dp" android:layout_height="wrap_content" android:text="NAME" android:textColor="@color/colorWhite" android:layout_weight="3"/> <TextView android:layout_width="0dp" android:layout_height="wrap_content" android:text="CATEGORY" android:textColor="@color/colorWhite" android:layout_weight="3"/> <TextView android:layout_width="0dp" android:layout_height="wrap_content" android:text="PRICE" android:textColor="@color/colorWhite" android:layout_weight="1"/> </LinearLayout> <ListView android:id="@+id/lstProducts" android:padding="2dp" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_alignParentLeft="true"> </ListView> </LinearLayout>
This will be our main layout file. From this screen user will be able to add data and save it into SQLite database.
res > layout > lst_template.xml:
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="horizontal" android:padding="4dp" android:layout_columnSpan="1" android:weightSum="8"> <TextView android:layout_width="0dp" android:layout_height="wrap_content" android:id="@+id/lblId" android:layout_weight="1"/> <TextView android:layout_width="0dp" android:layout_height="wrap_content" android:id="@+id/lblName" android:layout_weight="3"/> <TextView android:layout_width="0dp" android:layout_height="wrap_content" android:id="@+id/lblCategory" android:layout_weight="3"/> <TextView android:layout_width="0dp" android:layout_height="wrap_content" android:id="@+id/lblPrice" android:layout_weight="1"/> </LinearLayout>
This template will be used to display the products data stored in the SQLite database.
Now create a class file with name DBController.java and edit it as below:
DBController.java:
package com.example.sqliteexport;
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;
import java.util.HashMap;
public class DBController extends SQLiteOpenHelper {
private static final String tablename = "tblProducts"; // tablename
private static final String product = "product"; // column name
private static final String id = "ID"; // auto generated ID column
private static final String category = "category"; // column name
private static final String price = "price";
private static final String databasename = "dbProducts"; // Dtabasename
private static final int versioncode = 1; //versioncode of the database
public DBController(Context context) {
super(context, databasename, null, versioncode);
}
@Override
public void onCreate(SQLiteDatabase database) {
String query;
query = "CREATE TABLE IF NOT EXISTS " + tablename + "(" + id + " integer primary key, "
+ product + " text, " + category + " text, " + price + " text )";
database.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase database, int version_old,
int current_version) {
String query;
query = "DROP TABLE IF EXISTS " + tablename;
database.execSQL(query);
onCreate(database);
}
public ArrayList<HashMap<String, String>> getProducts() {
ArrayList<HashMap<String, String>> productList = new ArrayList<HashMap<String, String>>();
SQLiteDatabase database = this.getWritableDatabase();
Cursor cursor = database.rawQuery("SELECT * FROM " + tablename, null);
if (cursor.moveToFirst()) {
do {
HashMap<String, String> map = new HashMap<String, String>();
map.put("id", cursor.getString(0));
map.put("product", cursor.getString(1));
map.put("category", cursor.getString(2));
map.put("price", cursor.getString(3));
productList.add(map);
} while (cursor.moveToNext());
}
cursor.close();
database.close();
// return contact list
return productList;
}
public boolean addProduct(String productName, String productCategory, String productPrice) {
try {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(product, productName);
cv.put(category, productCategory);
cv.put(price, productPrice);
db.insert(tablename, null, cv);
db.close();
return true;
} catch (Exception ex) {
return false;
}
}
}
This is our database class. Using this, we will create database and its table. The onCreate method will create the table with the specified table name if it is not created. The getProducts() method will return all the product records present in the table in ArrayList. The addProduct() function adds new records in the table. Now create a class file with name Products.java and edit it as below:
Products.java:
package com.example.sqliteexport;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;
import java.util.HashMap;
import java.util.List;
public class Products extends AppCompatActivity {
Button btnSave;
EditText txtProductName, txtProductCategory, txtProductPrice;
DBController dbController;
ListView lstProducts;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.products);
btnSave = findViewById(R.id.btnSave);
txtProductCategory = findViewById(R.id.txtProductCategory);
txtProductName = findViewById(R.id.txtProductName);
txtProductPrice = findViewById(R.id.txtProductPrice);
lstProducts = findViewById(R.id.lstProducts);
clear();
dbController = new DBController(getApplicationContext());
setProducts();
}
public void clear() {
txtProductName.setText("");
txtProductCategory.setText("");
txtProductPrice.setText("");
}
public void saveProduct(View v) {
try {
if (dbController == null)
dbController = new DBController(getApplicationContext());
if (TextUtils.isEmpty(txtProductCategory.getText().toString()) ||
TextUtils.isEmpty(txtProductName.getText().toString()) ||
TextUtils.isEmpty(txtProductPrice.getText().toString())) {
Toast.makeText(Products.this, "Please enter product name, its category & price to save", Toast.LENGTH_SHORT).show();
return;
}
boolean result = dbController.addProduct(txtProductName.getText().toString(), txtProductCategory.getText().toString(), txtProductPrice.getText().toString());
if (result) {
clear();
Toast.makeText(Products.this, "Product saved successfully", Toast.LENGTH_SHORT).show();
setProducts();
} else
Toast.makeText(Products.this, "Cannot save product", Toast.LENGTH_SHORT).show();
} catch (Exception ex) {
Toast.makeText(Products.this, ex.getMessage(), Toast.LENGTH_SHORT).show();
}
}
public void setProducts() {
try {
if(dbController == null)
dbController = new DBController(getApplicationContext());
List<HashMap<String, String>> data = dbController.getProducts();
if (data.size() != 0) {
SimpleAdapter adapter = new SimpleAdapter(
Products.this, data, R.layout.lst_template,
new String[]{"id", "product", "category", "price"}, new int[]{
R.id.lblId,R.id.lblName,
R.id.lblCategory, R.id.lblPrice});
lstProducts.setAdapter(adapter);
}
} catch (Exception ex) {
Toast.makeText(Products.this, ex.getMessage().toString(), Toast.LENGTH_LONG).show();
}
}
}
The saveProduct() function is being called when the Save button is pressed. It will pass the data entered by the user to DBController method, which stores the data into SQLite database table. The setProducts() function calls the getProducts() method of the DBController class and fills the data into an Adapter and sets it to ListView.