A SQLite album for Android app developers

SQLite offers a able way to abundance and adapt assiduous abstracts for your apps. This is a acute and in-demand accomplishment all developers should know!

Sometimes, you charge to abundance added circuitous abstracts in your app than aloof simple key/value pairs adored with a argument book or Shared Preferences. Databases are ideal for autumn circuitous abstracts structures and are decidedly ill-fitted to autumn records, area anniversary block of abstracts stored uses the aforementioned fields, formatted in the aforementioned manner. This works like a table or an Excel spreadsheet, and, like Excel, it allows for abundant added activating abetment and analytic alignment of data. It’s acknowledgment to databases that abounding machine-learning and big abstracts applications are possible. Databases additionally accomplish accustomed accoutrement like Facebook possible. As a aftereffect it’s a accomplishment in high demand.

This is why programmers will eventually charge to apprentice to use databases. That way, your abstracts will be organized and you’ll accept no adversity retrieving passwords, user abstracts or whatever added advice you need. And this also happens to be a abundant way to abundance abstracts on an Android accessory as well. To do all this, we’ll be application SQLite.

SQL databases are relational databases area abstracts is stored in tables. The Structured Concern Accent (SQL) is the allegorical accent acclimated to concern those databases so that you can add, abolish and adapt data. For added on SQL itself, analysis out this article. SQLite is an accomplishing of a relational database, accurately aimed for anchored scenarios. It’s ideal for the brand of an Android app. The easiest way to brainstorm a relational database is to anticipate of it as a alternation of tables.

What’s air-conditioned is SQLite doesn’t crave a committed relational database administration arrangement (RDBMS)— it is acclimated anon from your code, rather than via a server or alien resource. Your abstracts is adored into a book locally on your device, authoritative it a able and decidedly accessible way to abundance assiduous abstracts on Android. SQLite is open-source, accessible to use, portable, and abominable cross-compatible.

There’s no charge to install annihilation added if you appetite to alpha application SQLite in Android Studio. Android provides the classes which you can use to handle your database. Android developers can use the SQLiteOpenHelper to use SQL commands. That’s what we’ll be attractive at in this post.

In the abutting few sections, you’ll apprentice actualize a table this way and in the process, you’ll hopefully alpha to feel adequate with SQLite, SQL, and databases in general.

Start a new abandoned Android Studio project. Now actualize a new chic by right-clicking the amalgamation on the larboard and allotment New > Java Class. I’ve alleged abundance ‘Database’. We appetite to extend SQLiteOpenHelper chic and so admission that as the superclass. To recap: this agency we’re inheriting methods from that class, so our new chic can act aloof like it.

Right now, your cipher will be accent red because you charge to apparatus the affiliated methods and add the constructor.

The accomplished commodity should attending like so:

The aboriginal affair to do is to abridge our constructor. Add these variables:

With that done, amend your architect like so:

Break it bottomward and you can see that we’re calling our database ‘MyDatabase.db’. Now, whenever we accomplish a new Database article from this class, the architect will body that database for us.

Now we’re accessible to alpha clearing it with some data! This abstracts takes the anatomy of a table and hopefully you’ll see why this is useful. What affectionate of affair ability we use a database for in the absolute world? Well, how about CRM – chump accord management? This is what big companies use to accumulate clue of their customers’ details. It’s how they apperceive to alarm us with appropriate offers in which we may be interested. It’s how your annual cable consistently knows back it’s time for a face-lifting – that ability be a acceptable archetype to use.

In added words, we’re application our admiral for evil.

To that end, we’re activity to charge some added variables so that we can body our table and alpha clearing it with data. Logically, that ability attending article like this:

Now the publishers who we’re architecture our app for will be able to concern back a assertive use is due for a face-lifting and calmly grab their phone cardinal to accord them a buzz.

Imagine aggravating to do this after SQL; you’d be affected to actualize assorted argument files with altered names for anniversary user, or one argument book with an base so you apperceive which band to retrieve advice from different text files. Again you’d accept to annul and alter anniversary admission manually with no way to analysis back things got out of sync. Searching for advice by name would be a nightmare. You ability end up application your own fabricated shorthand. It would get actual messy, actual fast.

While it ability be accessible to abstain application tables with a little creativity— all this can be a little alarming at first— it is an invaluable accomplishment to apprentice in the continued run and will absolutely accomplish your activity a lot easier. It’s additionally appealing abundant appropriate if you anytime accept dreams of acceptable a ‘full stack’ developer or creating web apps.

To body this table, we charge to use execSQL. This lets us allocution to our database and assassinate any SQL command that doesn’t acknowledgment data. So it’s absolute for architecture our table to activate with. We’re activity to use this in the onCreate() method, which will be alleged appropriate abroad back our article is created.

What’s accident actuality is we’re talking to our database and cogent it to actualize a new table with a specific table name, which we’ve authentic in our string.

If we breach the blow of that continued animal cord down, it absolutely contains a cardinal of easy-to-understand SQL commands:

SQLite will additionally add addition cavalcade around alleged rowid, which acts as a affectionate of base for retrieving annal and increases incrementally in amount with anniversary new entry. The aboriginal almanac will accept the rowid ‘0’, the additional will be ‘1’, and so on. We don’t charge to add this ourselves but we can accredit to it whenever we want. If we capital to change the name of a column, we would manually actualize one with the variable INTEGER PRIMARY KEY . That way, we could about-face our ‘rowid’ into ‘subscriber_id’ or article similar.

The blow of the columns are added straightforward. These are activity to accommodate characters (VARCHAR) and they will anniversary be called by the variables we created earlier. Here is a acceptable resource area you can see the SQL syntax on its own for this command and abounding others.

The added method, onUpgrade, is appropriate for back the database adaptation is changed. This will bead or add tables to advancement to the new action version. Aloof abide it and don’t anguish about it:

DROP TABLE is acclimated to annul the absolute data. Actuality we’re deleting the table if it already exists afore rebuilding it. See the antecedent cavalcade for more.

If all that’s in place, you’ve congenital your aboriginal database. Able-bodied done!

In future, if we to accredit to a database that was already created, again we would use getReadableDatabase() or getWriteableDatabase() to accessible the database accessible for reading-from or writing-to.

To admit new abstracts as a row, artlessly use db.insert(String table, Cord nullColumnHack, ContentValues). But what are ContentValues? This is a chic acclimated by Android that can abundance ethics to be bound by the ContentResolver.

If we actualize a ContentValues article and ample it with our data, we can canyon that to our database for assimilation. It looks like this:

Another advantage would be to use database.execSQL() and ascribe the abstracts manually:

This does the exact aforementioned thing. Remember to consistently abutting the database back you’re accomplished with it. You weren’t brought up in a barn, were you?

Of course, to absolutely use this database properly, we would apparently appetite to abide our columns application objects. We could use the afterward chic to add new subscribers to our list:

Then we could calmly body as abounding new subscribers as we admired and booty the variables from there. Better yet, we can additionally retrieve abstracts from our database this way to body new objects.

For instance, we ability use article like the afterward to apprehend through a account of audience and again abide an arrangement account application those objects. This uses a ‘cursor’, which you’ll apprentice about in the abutting section.

We’ve accounting an abominable lot of cipher so far after testing anything, which consistently gets me a little bit itchy.

Problem is, there’s not abundant to see actuality at the moment. To analysis if this is working, we charge to concern and acknowledgment some of the abstracts we’ve inserted. To do that we charge to use a cursor. Cursors acquiesce the abetment of accomplished after-effects sets and let us action our rows sequentially. This is accessible if you anytime appetite to accomplish some affectionate of algorithm on a row-by-row basis. You’ll see what I mean.

First, we charge to actualize our cursor, which we will do with query. Which looks like this:

We could again use this to actualize an ArrayList or cull out alone $.25 of data.

By creating a little adjustment like this:

Then we could admission that from our MainActivity.java and appearance it on a TextView, like so:

I had to actualize a TextView with the ID ‘TextView’. This should affectation the name ‘Adam’ on the awning seeing as the cursor has been confused to the aboriginal admission and is avaricious a cord from position 1 – which is area we put the name (ID is 0).

If we were application this for real, we would apparently use a “for” bend and use that to grab abstracts from every entry. For example:

Likewise, we ability apprehend our database this way and again use those strings to body altar for anniversary subscriber.

Other advantageous things we can do accommodate afterlight rows with database.update and deleting annal with database.delete. With a bit of organization, you can alpha administration your abstracts in a analytic and automatic address and accessible up lots of opportunities for able apps in the future.

Programmers will eventually charge to apprentice to use databases

SQL is appealing abundant appropriate if you anytime accept dreams of acceptable a ‘full assemblage developer' or creating web apps.

If we breach the cord down, it absolutely contains a cardinal of easy-to-understand SQL commands

you’ve created a accomplished apple of opportunities for your programming career

Introducing SQLite

Creating your aboriginal database

Creating tables

Inserting data

Retrieving abstracts and application cursors

Closing comments

package com.androidauthority.sqliteexample;
 
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public chic Database extends SQLiteOpenHelper {
    accessible Database(Context context, Cord name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context,name,factory, version);
    }
 
    @Override
    public abandoned onCreate(SQLiteDatabase db) {
    }
 
    @Override
    public abandoned onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
    }
     
}

public changeless final int DATABASE_VERSION = 1;
public changeless final Cord DATABASE_NAME = "MyDatabase.db";

public Database(Context context) {
     super(context,DATABASE_NAME,null, DATABASE_VERSION);
}

public changeless final Cord TABLE_NAME = "SUBSCRIBERS";
public changeless final Cord COLUMN_NAME = "NAME";
public changeless final Cord COLUMN_MAGAZINE_TITLE = "MAGAZINE_TITLE";
public changeless final Cord COLUMN_RENEWAL_DATE= "RENEWAL_DATE";
public changeless final Cord COLUMN_PHONE = "PHONE_NUMBER";

See Also: hack line

@Override
public abandoned onCreate(SQLiteDatabase db) {
    db.execSQL("create table "   TABLE_NAME   " ( "   COLUMN_NAME   " VARCHAR, " 
                  COLUMN_MAGAZINE_TITLE   " VARCHAR, "   COLUMN_RENEWAL_DATE   " VARCHAR, " 
                  COLUMN_PHONE   " VARCHAR);");
}

create table   TABLE_NAME(
      COLUMN_NAME   VARCHAR,
      COLUMN_MAGAZINE_TITLE   VARCHAR, 
      COLUMN_RENEWAL_DATE   VARCHAR, 
      COLUMN_PHONE   VARCHAR)

@Override
public abandoned onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
     db.execSQL("DROP TABLE IF EXISTS "   TABLE_NAME);
     onCreate(db);
}

contentValues.put(COLUMN_NAME, "Adam");
contentValues.put(COLUMN_MAGAZINE_TITLE, "Women's World");
contentValues.put(COLUMN_RENEWAL_DATE, "11/11/2018");
contentValues.put(COLUMN_PHONE, "00011102");
 
db.insert(TABLE_NAME, null, contentValues);
db.close();

db.execSQL("INSERT INTO "   TABLE_NAME   "("   COLUMN_NAME   "," 
             COLUMN_MAGAZINE_TITLE   ","   COLUMN_RENEWAL_DATE   "," 
             COLUMN_PHONE   ") VALUES('Adam','Women's World','11/11/2018','00011102')");
db.close();

public chic SubscriberModel {
     clandestine Cord ID, name, magazine, renewal, phone;
 
     accessible Cord getID() {
         acknowledgment ID;
     }
     accessible Cord getName() {
         acknowledgment name;
     }
     accessible Cord getRenewal() {
         acknowledgment renewal;
     }
     accessible Cord getMagazine() {
         acknowledgment magazine;
     }
     accessible Cord getPhone() {
         acknowledgment phone;
     }
     accessible abandoned setName(String name) {
         this.name = name;
     }
     accessible abandoned setMagazine(String magazine) {
         this.magazine = magazine;
     }
     accessible abandoned setRenewal(String renewal) {
         this.renewal = renewal;
     }
     accessible abandoned setPhone(String phone) {
         this.phone = phone;
     }
}

public ArrayList getAllRecords() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
    ArrayList subs = new ArrayList<>();
    Subscribers subscribers;
    if (cursor.getCount() > 0) {
        for (int i = 0; i < cursor.getCount(); i  ) {
            cursor.moveToNext();
            subscribers = new Subscribers();
            subscribers.setName(cursor.getString(1));
            subscribers.setMagazine(cursor.getString(2));
            subs.add(subscribers);
        }
    }
    cursor.close();
    db.close();
    acknowledgment subs;
}

Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

public Cord returnName() {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
    cursor.moveToFirst();
    acknowledgment cursor.getString(1);
}

Database database = new Database(this);
TextView textView = (TextView)findViewById(R.id.TextView);
textView.setText(database.returnName());

for (int i = 0; i < cursor.getCount(); i  ) {
     cursor.moveToNext();
     //Get advantageous abstracts such as names of bodies who charge to renew here
}

Optional