SQLite
example
Contains
the complete code for a SQLite application in Android Java. The code example
creates a SQLite database at runtime, appends records, modifies records,
deletes records, and retrieves a complete list from a table.
The data
will be displayed in a new Android 7 RecyclerView and displayed via a click
event.
Java code,
Android
Complete
code example in Java
Database_SQLite
File: app /
java / project_package / Database_SQLite
In the file
Database_SQLite is all Java code, which creates a SQLite database at runtime,
binds and manages data.
1) Create a
database
The
database is checked at init and, if necessary, the tables are created. In this
case, a table called tbl_Notes is created.
String createTable = "CREATE TABLE
tbl_Notes " +
"( IDNote INTEGER PRIMARY KEY AUTOINCREMENT
" +
" , Title TEXT
" + //*Note of type TEXT
" , Text TEXT
" + //*Note of type TEXT
" , dtDay
DATETIME " +
" , dtEdit
DATETIME DEFAULT CURRENT_TIMESTAMP " +
")"
;
db.execSQL(createTable);
|
2) Add data
record
A record is
added by putting together values ββunder ContentValues ββand appending them
with db.Insert.
As a result,
the new ID of the attached record is returned.
ContentValues values = new ContentValues();
values.put("Title", sTitle);
long newID = db.insert("tbl_Notes", null, values);
|
3) Update
record
A single
record is filtered by ID and stored with UPDATE table SET field = value.
String sSQL = "UPDATE tbl_Notes
" +
" SET [Title] =
'" +
sTitle + "'" +
" WHERE
IDNote=" + IDNote ;
db.execSQL(sSQL);
|
3) Delete
record
A single
record is also deleted from table with DELETE, always using WHERE to define the
single ID.
String
sSQL = "DELETE
FROM tbl_Notes " +
" WHERE " +
" IDNote=" + IDNote;
db.execSQL(sSQL);
|
4) Retrieve
data
Complete
data in its table are read out with SELECT and output as CURSOR.
A cursor
can then be traversed line by line and process each individual record.
String sSQL = "SELECT * FROM
tbl_Notes";
Cursor data = db.rawQuery(sSQL,
null);
return data;
|
package com.codedocu.demo06_database01;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.text.SimpleDateFormat;
import java.util.Date;
/*
Created by Raimund.Popp@CodeDocu.com*/
public
class Database_SQLite
extends
SQLiteOpenHelper
{
private static final String DatabaseName = "CalendarCloud.db";
public DataModel_Note Note;
//----------<
Database >--------
public Database_SQLite(Context
context) {
super(context, DatabaseName, null, 1);
}
@Override
public void onCreate(SQLiteDatabase
db) {
//--------<
onCreate() >--------
//*Create Tables
String createTable = "CREATE TABLE
tbl_Notes " +
"( IDNote INTEGER PRIMARY KEY AUTOINCREMENT
" +
" , Title TEXT
" + //*Note of type TEXT
" , Text TEXT
" + //*Note of type TEXT
" , dtDay
DATETIME " +
" , dtEdit
DATETIME DEFAULT CURRENT_TIMESTAMP " +
")"
;
db.execSQL(createTable);
//--------</
onCreate() >--------
}
@Override
public void onUpgrade(SQLiteDatabase
db, int
i,
int
i1)
{
//*run only if database
structure changes
}
//----------</
Database >--------
//================< Region:
Insert,Update,Delete >================
public long add_Note(String sTitle, String sText, Date
dtNoteDay) {
//--------<
add_Note() >--------
//*add a recordset to the
tbl_Notes Table
//< getDB >
SQLiteDatabase db = this.getWritableDatabase();
//</ getDB >
//< set_Values >
SimpleDateFormat
fmtDate = new SimpleDateFormat("YYYYMMdd");
String sDate_Note =
fmtDate.format(dtNoteDay);
String sDate_Now =
fmtDate.format(new Date());
ContentValues values = new ContentValues();
values.put("Title", sTitle);
//values.put("Text",
sText);
//values.put("dtDay",
sDate_Note);
//values.put("dtEdit", sDate_Now);
//</ set_Values >
//< add >
long newID = db.insert("tbl_Notes", null, values);
//</ add >
//< out >
if (newID == -1)
{
//*SQLite Error on
Insert: -1
return -1;
}
else
{
//*return value is new
ID
return newID;
}
//</ out >
//--------</ add_Note()
>--------
}
public void save_Note_byID(Integer IDNote,
String sTitle){
//--------<
update_Note_byID() >--------
SQLiteDatabase db = this.getWritableDatabase();
String sSQL = "UPDATE tbl_Notes
" +
" SET [Title] =
'" +
sTitle + "'" +
" WHERE
IDNote=" + IDNote ;
//< run >
db.execSQL(sSQL);
//</ run >
//--------</
update_Note_byID() >--------
}
public void delete_byID(int IDNote){
//--------< delete_byID()
>--------
SQLiteDatabase db = this.getWritableDatabase();
//< SQL >
String sSQL = "DELETE FROM
tbl_Notes " +
" WHERE " +
" IDNote=" + IDNote;
//</ SQL >
//< run >
db.execSQL(sSQL);
//</ run >
//--------</ delete_byID()
>--------
}
public Cursor get_Table(){
//--------<
get_Table() >--------
//
SQLiteDatabase db = this.getWritableDatabase();
String sSQL = "SELECT * FROM
tbl_Notes";
Cursor data = db.rawQuery(sSQL,
null);
return data;
//--------</
get_Table() >--------
}
//================</
Region: Insert,Update,Delete >================
public Cursor
get_Datarows_by_Criteria(String sWhere){
//--------<
get_Data_by_Filter() >--------
//
SQLiteDatabase db = this.getWritableDatabase();
String sSQL = "SELECT * FROM
tbl_Notes WHERE " + sWhere ;
Cursor data = db.rawQuery(sSQL,
null);
return data;
//--------</
get_Data_by_Filter() >--------
}
}
|
Data model: DataModel_Note
App / java
/ project_package / DataModel_Note
The data is
summarized as data notes in this example.
A note
consists of a running ID number, a title and text, and the noted date
Data Model:
Note
package com.codedocu.demo06_database01;
import
java.util.Date;
public
class DataModel_Note
{
//----------<
DataModel: Note >------------
//< inner >
public Long IDNote;
public String Title;
public String Text;
public Date dtNote;
public Date dtEdit;
//</ inner >
//----------< DataModel: Note
>------------
}
|
Main Activity
Path: app /
java / project_package / MainActivity
The button
events are processed in MainActivity.
Here are the
actions: Add, Save, Delete and Load (List) included.
Add
The btn_add
method appends a record to the SQL database
long newID=_db.add_Note(sTitle,"", new Date());
|
Save
Save saves
or updates a record in SQLite based on the unique ID
_db.save_Note_byID(ID,sTitle);
|
Delete
Delete
deletes a record based on an ID
Load and
Load retrieves a complete table from the SQLite database. The data is in
a cursor
Cursor cursor =_db.get_Table();
|
package com.codedocu.demo06_database01;
import
android.database.Cursor;
import
android.support.v7.app.AppCompatActivity;
import
android.os.Bundle;
import
android.support.v7.widget.LinearLayoutManager;
import
android.support.v7.widget.RecyclerView;
import
android.view.View;
import
android.widget.EditText;
import
android.widget.TextView;
import
android.widget.Toast;
import
java.util.ArrayList;
import
java.util.Date;
public
class MainActivity
extends
AppCompatActivity
{
Database_SQLite _db; //=new Database(this);
EditText ctlTitle;
@Override
protected void onCreate(Bundle
savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//< init >
_db=new Database_SQLite(this);
ctlTitle=findViewById(R.id.editTitle);
//</ init >
}
//=============<
Region: Events >===========
public void btnAdd_Clicked(View view) {
//------------<
btnAdd_Clicked() >------------
//< add Title >
//*add recordset with title on
database
String sTitle=ctlTitle.getText().toString();
long newID=_db.add_Note(sTitle,"", new Date());
//</ add Title >
//< show newID >
String sNewID=String.valueOf(newID);
TextView
lblID=findViewById(R.id.IDNote);
lblID.setText(sNewID);
//</ show newID >
//------------</
btnAdd_Clicked() >------------
}
public void btnSave_Clicked(View view) {
//------------<
btnSave_Clicked() >------------
//*save recordset by ID
//< get fields >
TextView
lblID=findViewById(R.id.IDNote);
String
sID=lblID.getText().toString();
Integer ID=Integer.parseInt(sID);
EditText
edit=findViewById(R.id.editTitle);
String
sTitle=edit.getText().toString();
//</ get fields >
//< save Datase >
_db.save_Note_byID(ID,sTitle);
//</ save Dataset
>
//------------</
btnSave_Clicked() >------------
}
public void btnDelete_Clicked(View view) {
//------------<
btnDelete_Clicked() >------------
//*save recordset by ID
//< get fields >
TextView
lblID=findViewById(R.id.IDNote);
String
sID=lblID.getText().toString();
Integer ID=Integer.parseInt(sID);
EditText
edit=findViewById(R.id.editTitle);
String
sTitle=edit.getText().toString();
//</ get fields >
//< save Datase >
_db.delete_byID(ID);
//</ save Dataset
>
//< change elements >
lblID.setText("-1");
edit.setText("");
//</ change elements
>
//------------</
btnDelete_Clicked() >------------
}
public void btnLoad_Clicked(View view) {
//------------<
btnLoad_Clicked() >------------
RecyclerView
list_with_notes=findViewById(R.id.List_Notes);
//ArrayList<String>
arrayList = new ArrayList<String>();
ArrayList<DataModel_Note>
data_with_Notes = new ArrayList<DataModel_Note>();
//< get Table >
Cursor cursor =_db.get_Table();
//</ get Table >
//----< load List-View
>----
int colID=cursor.getColumnIndex("IDNote");
int colTitle=cursor.getColumnIndex("Title");
while (cursor.moveToNext())
{
//< get Data from
data_cursor >
String sID =
cursor.getString(colID);
String sTitle =
cursor.getString(colTitle);
//</ get Data from
data_cursor >
//< create data as
dataclass >
DataModel_Note note=new DataModel_Note();
note.Title=sTitle;
note.IDNote=Long.parseLong(sID);
//</ create data as
dataclass >
//-< add Data_Entries to
List >-
data_with_Notes.add(note);
//-</ add
Data_Entries to List >-
}
//< show results
>
list_with_notes.setLayoutManager(new LinearLayoutManager(this));
RecyclerView.Adapter adapter = new Adapter_for_RecylerView(data_with_Notes
);
list_with_notes.setAdapter(adapter);
//</ show results
>
//----</ load List-View
>----
//--< onClick listener
>--
((Adapter_for_RecylerView)
adapter).setOnItemClickListener(new Adapter_for_RecylerView.OnItemClickListener()
{
@Override
public void onItemClick(View view,
int
position)
{
//------<
onItemClick() >--------
//< get Row_Fields
>
String
sPosition=String.valueOf(position);
TextView
item_lblID=view.findViewById(R.id.lblID);
TextView
item_lblTitle=view.findViewById(R.id.lblTitle);
String
sID=item_lblID.getText().toString();
String
sTitle=item_lblTitle.getText().toString();
//</ get Row_Fields
>
//-< set
active_elements >
TextView
lblIDNote=findViewById(R.id.IDNote);
lblIDNote.setText(sID);
EditText
edit=findViewById(R.id.editTitle);
edit.setText(sTitle);
//-</ set
active_elements >
//< show >
Toast.makeText(MainActivity.this, "position=" + position + " Title=" + sTitle + " ID=" + sID, Toast.LENGTH_SHORT).show();
//</ show >
//------<
onItemClick() >--------
}
});
//--</ onClick
listener >--
//------------</
btnLoad_Clicked() >------------
}
//=============</
Region: Events >===========
}
|
Adapter for RecyclerView
In Android
Java, ad lists are loaded with an adapter and an item layout.
The adapter
for the REcyclerView used here is contained in Adapter_for_RecylerView.
When an
adapter is called, the data is loaded as a parameter.
The adapter
code first binds an item layout to a ViewHolder. Then the data in the
ViewHolder is assigned to the single row element.
In
addition, another click event is embedded, which can be edited by clicking on a
line from the calling code in the MainActivity.
package com.codedocu.demo06_database01;
import
android.content.Context;
import
android.support.annotation.NonNull;
import
android.support.v7.widget.RecyclerView;
import
android.view.LayoutInflater;
import
android.view.View;
import
android.view.ViewGroup;
import
android.widget.TextView;
import
java.util.List;
public
class Adapter_for_RecylerView
extends RecyclerView.Adapter<Adapter_for_RecylerView.MyViewHolder>
{
//internal data
private List<DataModel_Note>
_data;
//--< init_create
>--
// get Data on Init
public Adapter_for_RecylerView(List<DataModel_Note>
data) {
_data = data;
}
//--</ init_create
>--
//--< Add_Click_Listener >--
// Define listener member variable
private OnItemClickListener listener;
// Define the listener
interface
public interface OnItemClickListener {
void onItemClick(View
itemView, int position);
}
// Define the method
that allows the parent activity or fragment to define the listener
public void setOnItemClickListener(OnItemClickListener
listener) {
this.listener = listener;
}
//--<
Add_Click_Listener >--
//----< bind and load data
>----
@Override
//*load Data into Row
public void onBindViewHolder(@NonNull Adapter_for_RecylerView.MyViewHolder
holder, final
int position)
{
//--------<
onBindViewHolder() >--------
//*Get Data and load it in Item
output-viewfield
//< get Data >
// Get the data model based on
position
DataModel_Note note= _data.get(position);
//</ get Data >
//< show Data >
// Set item views based on your
views and data model
TextView lblID =
holder._lblID;
lblID.setText(note.IDNote.toString());
TextView lblTitle = holder._lblTitle;
lblTitle.setText(note.Title);
//</ show Data >
//--------</
onBindViewHolder() >--------
}
//----</ bind and
load data >----
@Override
//*used by
layoutmanager
public int getItemCount() {
return _data.size();
}
//private final
View.OnClickListener onRow_Click = new ();
//========< internal_Viewholder
with row_layout >========
//holds row as layout at runtime
public class MyViewHolder extends RecyclerView.ViewHolder
implements
View.OnClickListener
{
//--------< class:
ViewHolder() >--------
//bind row-layout to internal
field
public TextView _lblID;
public TextView _lblTitle;
//-< create
Viewholder-of-Row >-
public MyViewHolder(final View itemView) {
super(itemView);
//--< Row_Items
>--
_lblID=itemView.findViewById(R.id.lblID);
_lblTitle=itemView.findViewById(R.id.lblTitle);
//--< Row_Items
>--
//--< add_click_Listener
>--
itemView.setOnClickListener(new View.OnClickListener()
{
@Override
public void onClick(View v) {
// Triggers click
upwards to the adapter on click
if (listener != null) {
int position =
getAdapterPosition();
if (position !=
RecyclerView.NO_POSITION) {
listener.onItemClick(itemView, position);
}
}
}
});
//--</
add_click_Listener >--
}
@Override
public void onClick(View v) {
}
//-</ create
Viewholder-of-Row >-
//--------</ class:
ViewHolder() >--------
}
@NonNull
@Override
public MyViewHolder
onCreateViewHolder (@NonNull ViewGroup parent, int viewType) {
//--------<
onCreateViewHolder() >--------
//*load layout from /res/layout
to internal viewholder{}-class
//-< get Resource-Layout
>-
Context context =
parent.getContext();
LayoutInflater inflater =
LayoutInflater.from(context);
// load row layout
View rowLayout =
inflater.inflate(R.layout.row_layout, parent, false);
//-</ get
Resource-Layout >-
//< load_internal Viewholder
>
// Return a new holder instance
MyViewHolder
viewHolder = new MyViewHolder(rowLayout);
return viewHolder;
//</ load_internal
Viewholder >
//--------</
onCreateViewHolder() >--------
}
//========</
internal_Viewholder with row_layout >========
}
|
Activity_main.xml
File: app /
res / layout / activity_main.xml
With
ConstraintLayout and RecyclerView
<?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=".MainActivity">
<Button
android:id="@+id/btnDelete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginStart="36dp"
android:layout_marginTop="228dp"
android:text="Delete"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
android:onClick="btnDelete_Clicked"
/>
<Button
android:id="@+id/btnSave"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginStart="40dp"
android:layout_marginTop="96dp"
android:onClick="btnSave_Clicked"
android:text="Save"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<Button
android:id="@+id/btnLoad"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginStart="36dp"
android:layout_marginTop="156dp"
android:text="Load"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
android:onClick="btnLoad_Clicked"
/>
<Button
android:id="@+id/btnAdd"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginStart="36dp"
android:layout_marginTop="36dp"
android:text="Add"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent"
android:onClick="btnAdd_Clicked"
/>
<EditText
android:id="@+id/editTitle"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginTop="36dp"
android:layout_marginEnd="28dp"
android:ems="10"
android:inputType="textPersonName"
android:text="Name"
app:layout_constraintEnd_toEndOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<TextView
android:id="@+id/IDNote"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginStart="140dp"
android:layout_marginTop="16dp"
android:text="-1"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<android.support.v7.widget.RecyclerView
android:id="@+id/List_Notes"
android:layout_width="240dp"
android:layout_height="413dp"
android:layout_marginStart="140dp"
android:layout_marginTop="84dp"
android:clickable="true"
android:longClickable="true"
android:scrollbars="horizontal|vertical"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
</android.support.constraint.ConstraintLayout>
|
Row_layout.xml
For the
list RecyclerView a layout has to be created. This is located under the
row_layout.xml
Path: app /
res / layout / row_layout.xml
<?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="wrap_content">
<TextView
android:id="@+id/lblID"
android:layout_width="32dp"
android:layout_height="18dp"
android:layout_marginTop="16dp"
android:text="ID"
android:textAlignment="viewStart"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
<TextView
android:id="@+id/lblTitle"
android:layout_width="334dp"
android:layout_height="32dp"
android:layout_marginStart="44dp"
android:layout_marginTop="8dp"
android:text="Title"
android:textSize="24sp"
app:layout_constraintStart_toStartOf="parent"
app:layout_constraintTop_toTopOf="parent" />
</android.support.constraint.ConstraintLayout>
|
Subject:
SQL Lite,
RecyclerView (successor to ListView), Adapter Click Evente