Last Updated:

Working with SQlite database in Android Studio

SQLite is the most suitable database for storing information on the Android system. It is recommended for all developers who create programs for the mobile operating system from Google.

But not everyone understands how to interact with it at all in order to eventually get a fast application with fast data processing from the database. So, you need to read this material on creating, adding a database, and so on.

Creating and filling the database with information

The most interesting thing in the work-creation of the database is the addition of basic information to the starter version. You will have to code a lot here, however, in the future you will have to write a little, since only small edits will be required over time. Here's something that, for example, can be used to populate (users may have it completely differently):

<string-array name="catlist">
<item>Barsik</item>
<item>Murzik</item>
<item>Taska</item>
<item>Ryzhik</item>
</string-array>

After that, you can start creating a "wrapper" for the database. Here's what you should get in the process:

package en.alexanderklimov.catbase;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.resources;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

private final Context fContext;
private static final String DATABASE_NAME = "cat_database.db";
public static final String TABLE_NAME = "cattable";

DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
fContext = context;
}

@Override
public void onCreate(SQLiteDatabasedb) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE" + TABLE_NAME + "("
+ "_id INTEGER PRIMARY KEY," + "title TEXT" + ");");

// Add records to the table
ContentValues ​​values ​​= new ContentValues();
// Get an array of strings and p resources
Resources res = fContext.getResources();
String[] cattable_records = res.getStringArray(R.array.catlist_v1);
// loop through the array and insert records into the table
int length = cattable_records.length;
for (inti = 0; i&lt; length; i++) {
values. put("title", cattable_records[i]);
db.insert(TABLE_NAME, null, values);
}
}

@Override
public void onUpgrade(SQLiteDatabasedb, intoldVersion, intnewVersion) {
// TODO Auto-generated method stub
Log.w("TestBase", "Upgrading database from version" + oldVersion
+ " to " + newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
onCreate(db);
}
}

However, that's not all. In the onCreate() method, you create and populate the table with data from the resources. And then - the end of the work, you need to connect the class to the basis of all the activity of the SQLite database. The code will look like this:

setContentView(R.layout.activity_test);

// Initialize our wrapper class
DatabaseHelperdbh = new DatabaseHelper(this);

We need the database to write and read
SQLiteDatabasesqdb = dbh.getWritableDatabase();

close connections to the sqdb.close() database
;
dbh.close();

All. This is where the database is ready. However, there is another way with complex filling. It already takes a little more effort.

First, you need to create a special XML file in which there will be a different structure. Its content will be different from the example above. Here's what it would look like:

<?xml version="1.0" encoding="utf-8"?>
<cats>
<record title="Barsik" color="black" />
<record title="Murzik" color="white" />
<record title="Red" color="red" />
<record title="Vaska" color="gray" />
</cats>

Here, too, the color of the cat's coat is added. If desired, the user can supplement his database with attributes. But for example, this option is suitable. Next, you need to modify the already ready-made "wrapper" from the first option. Now it will acquire this appearance:

package en.alexanderklimov.catbase;

import java.io.IOException;

import org.xmlpull.v1.XmlPullParser;
import org.xmlpull.v1.XmlPullParserException;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.resources;
import android.content.res.XmlResourceParser;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

private final Context fContext;
private static final String DATABASE_NAME = "colorcat_database.db";
public static final String TABLE_NAME = "cattable";

DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
fContext = context;
}

@Override
public void onCreate(SQLiteDatabasedb) {
// TODO Auto-generated method stub
db.execSQL("CREATE TABLE" + TABLE_NAME + "("
+ "_id INTEGER PRIMARY KEY, " + "title TEXT, " + "color TEXT"
+ ");");

Add records to the
Table ContentValues ​​values ​​= new ContentValues();

Get the file from
Resources resources res = fContext.getResources();

// Open xml file
XmlResourceParser _xml = res.getXml(R.xml.cats_records);
try {
// Look for the end of the document
inteventType = _xml.getEventType();
while (eventType != XmlPullParser.END_DOCUMENT) {
// Looking for record tags
if ((eventType == XmlPullParser.START_TAG)
&& (_xml.getName().equals("record"))) {
// The Record tag is found, now we get its attributes and
// insert into table
String title = _xml.getAttributeValue(0);
String color = _xml.getAttributeValue(1);
values. put("title", title);
values ​​put("color", color);
db.insert(TABLE_NAME, null, values);
}
eventType = _xml.next();
}
}
// catch errors
catch (XmlPullParserException e) {
Log.e("Test", e.getMessage(), e);
} catch (IOException e) {
Log.e("Test", e.getMessage(), e);

} finally {
// Close the xml file
_xml.close();
}

}

@Override
public void onUpgrade(SQLiteDatabasedb, intoldVersion, intnewVersion) {
// TODO Auto-generated method stub
Log.w("TestBase", "Upgrading database from version" + oldVersion
+ " to " + newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);
onCreate(db);
}
}

And now, when the user opens his table with data, he will have this result of his efforts when working with SQLite:

_idtitlecolor
1BarsikBlack
2MurzykWhite
3CamelinaRed
4Vas'kaGrey

Changing data

Over time, you may need to change the data. In view of this, you will have to apply a small line of code with certain parameters. It will not cause bewilderment, as it is very simple. This is what the new command that the user will enter when requesting a data update will look like:

UPDATE "Table" SET Field = Value

It is worth directly understanding how what is implemented. In simple terms, UPDATE itself is a request to update the data. And therefore, it is the main component in the line of code. But set is needed in order to list the fields, as well as the values needed for application, separated by commas after it. Here's what might happen:

UPDATE goods SET price = 150 WHERE num = 2

And all this is done with a table that looks like this:

Atitleprice
1Kettle300
2Cup100
3Spoon25
4Plate100

Consequently, now the price will change for one of the goods. This can be done with any line and any block of information. The main thing is that there are no errors and the upper lines denoting the data are not edited. Then everything will be normal and no revisions of the resulting code will be required.

But there may also be a scenario when you need to replace data in several blocks. Therefore, it is necessary to resort to another option for writing code. And it will look like this (you can say, this is a modified version of the standard replacement command):

UPDATE goods SET price = price / 2 WHERE price >= 100

Multiple fields in a row may also require data refresh. It is done, again, by a slightly modified command. It looks like this:

UPDATE goods SET title = «утюг», price = 300 WHERE num = 2

At this point, with the change of data, the theme is over. Only the deletion of information from the table remains to be parsed.

Deletion of data

Deleting data from a SQL data table is very easy. To do this, the DELETE statement is used, and the command itself is read as:

DELETE FROM "table name"
WHERE "condition"

This is used for partial removal. However, there is an option when it is possible to erase all information from the database. Then the command is as follows:

DELETE FROM "table_name"

This is how it is possible to get rid of unnecessary data. And after reading this material, the user will understand how to manipulate the database. Here is all the basics to work with SQLite in AndroidStudio.