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 Datenbank in Android: Komplettes Code Beispiel

02.10.2018 (👁28582)

SQLite  Beispiel

 

Enthält den kompletten Code für eine SQLite Anwendung in Android Java. Das Code Beispiel erstellt zur Laufzeit eine SQLite Datenbank, fügt Datensätze an, ändert Datensätze, Löscht Datensätze und holt eine komplette Liste aus einer Tabelle ab.

Dabei werden die Daten in einer neuen Android 7 RecyclerView zur Darstellung gebracht und per Click-Event angezeigt.

 

 

 

 

Java Code, Android

Komplettes Code Beispiel in Java

 

Database_SQLite

Datei: app/java/project_package/Database_SQLite

In der Datei Database_SQLite steht aller Java Code, welche eine SQLite Datenbank zur Laufzeit erstellt, anbindet und Daten verwaltet.

1) Datenbank erstellen

Die Datenbank wird beim Init geprüft und notfalls werden die Tabellen erstellt. In diesem Fall wird eine Tabelle mit dem Namen: tbl_Notes erstellt.

        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) Datensatz anfügen

Ein Datensatz wird angefügt, indem man unter ContentValues werte zusammenstellt un diese dann mit db.Insert anfügt.

Als Ergebniss wird die neue ID des angefügten Datensatzes zurückgegeben.

        ContentValues values = new ContentValues();
        values.put(
"Title", sTitle);
        long newID = db.insert("tbl_Notes", null, values);

 

3) Datensatz aktualisieren

Ein einzelner Datensatz wird anhand der ID gefiltert und mit UPDATE Tabelle SET Feld=Wert gespeichtert.

        String sSQL = "UPDATE tbl_Notes " +
               
" SET [Title] = '" + sTitle + "'"    +
               
" WHERE IDNote=" + IDNote ;
       
db.execSQL(sSQL);

 

3) Datensatz löschen

Ein einzelner Datensatz wird ebenfalls mit DELETE von Tabelle gelöscht, wobei immer mit WHERE die einzelne ID definiert wird.

String sSQL = "DELETE FROM tbl_Notes " +
               
" WHERE " +
               
" IDNote=" + IDNote;
        
db.execSQL(sSQL);

 

 

4) Daten abrufen

Komplette Daten au seiner Tabelle werden mit SELECT ausgelesen und als CURSOR ausgegeben.

Ein Cursor kann dann zeilenweise durchlaufen werden und jeden einzelnen Datensatz abarbeiten.

        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() >--------
   
}

}


 

 

 

 

Datenmodell: DataModel_Note

App/java/project_package/DataModel_Note

 

Die Daten warden in diesem Beispiel als Daten-Notizen zusammengefasst.

Eine Notiz besteht aus einer laufenden ID-Nummer, einem Titel und Text und dem notierten Datum

 

Datenmodel: 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 >------------
}

 

 

 

 

MainActivity

Pfad: app/java/project_package/MainActivity

In der MainActivity werden die Button-Events abgearbeitet.

Hierbei sind die Actionen: Add, Save, Delete und Load (Liste) enthalten.

Add

Die btn_add Methode fügt einen Datensatz an die SQL Datenbank an

long newID=_db.add_Note(sTitle,"", new Date());

 

Save

Save speichert oder aktualisiert einen Datensatz in SQLite anhand der eindeutigen ID

_db.save_Note_byID(ID,sTitle);

 

Delete

Delete löscht einen Datensatz anhand einer ID

_db.delete_byID(ID);

Load
und Load holt von der SQLite Datenbank eine komplette Tabelle ab. Dabei stehen die Daten in einem 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 für RecyclerView

In Android Java werden Anzeige-Listen mit einem Adapter und einem Item-Layout geladen.

Der Adapter für das hier verwendete REcyclerView ist unter Adapter_for_RecylerView enthalten.

Beim Aufruf eines Adapter werden die Daten als Parameter geladen.

Der Adapter Code bindet zunächst ein Item-Layout in einen ViewHolder. Dann werden die Daten im ViewHolder dem einzelnen Zeilen-Element zugewiesen.

Zusätzlich wird noch ein Click-Event eingebettet, welcher beim Klick auf eine Zeile vom Aufrufenden Code in der MainActivity bearbeitet werden kann.

 

    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

Datei: app/res/layout/activity_main.xml

 

 

Mit ConstraintLayout und 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

Für die Liste RecyclerView muss ein Layout angelegt werden. Dieses befindet sich unter der row_layout.xml

Pfad: 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>

 

 

 

Betrifft:

SQL Lite, RecyclerView (Nachfolger von ListView),  Adapter Click Evente