SQLite Datenbank

SQLite Datenbank Datentypen

Datentyp	Beschreibung
INTEGER	Ganzzahl (inklusive Integer, Long, Short)
REAL	Gleitkommazahl (inklusive Float, Double)
TEXT	Zeichenkette (Unicode-Text)
BLOB	Binärdaten (z.B. Bilder, Videos)
NULL	Nullwert (entspricht dem Java-Null oder SQL NULL)

Daten in DB einfügen

SQLiteDatabase db = null;
try {
    db = openOrCreateDatabase("PersonDB", MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS Persons (id INTEGER PRIMARY KEY, firstname TEXT, surname TEXT)");
    db.execSQL("INSERT OR REPLACE INTO Persons VALUES (1,'David', 'Höll')");
} catch (Exception e) {
    e.printStackTrace();
} finally {
    db.close();
}

Eigenschaftsklasse Person

public class Person {
    private int id;
    private String firstname;
    private String surname;

    public Person(int id, String firstname, String surname) {
        this.id = id;
        this.firstname = firstname;
        this.surname = surname;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }
}

Daten aus Datenbank lesen

SQLiteDatabase db = null;
// set the columns of the table
String[] columns = {"id", "firstname", "surname"};
// set the Arguments
String[] selectionArguments = {"1"};
ArrayList<Person> persons = new ArrayList<>();
try {
    db = openOrCreateDatabase("PersonDB", MODE_PRIVATE, null);

    Cursor cursor;
    // the arguments will replace the ?
    cursor = db.query("Persons", columns, "id = ?", selectionArguments, null, null, null);

    if (cursor != null && cursor.moveToFirst()) {
        do {
            int idIndex = cursor.getColumnIndexOrThrow("id");
            int firstnameIndex = cursor.getColumnIndexOrThrow("firstname");
            int lastnameIndex = cursor.getColumnIndexOrThrow("surname");

            // add the data to an ArrayList
            persons.add(new Person(cursor.getInt(idIndex), cursor.getString(firstnameIndex), cursor.getString(lastnameIndex)));
        } while (cursor.moveToNext());
    }
    cursor.close();

} catch (Exception e) {
    e.printStackTrace();
} finally {
    db.close();
}
// print the data as toast popup
for (Person person : persons) {
    Toast.makeText(getApplicationContext(),
            person.getId() + " " + person.getFirstname() + " " + person.getSurname(),
            Toast.LENGTH_LONG).show();
}