Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

SQLite Database in Android : Complede Code Example

02.10.2018 (πŸ‘507)

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

_db.delete_byID(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