Using SQLlite in your Android app

So, as part of my Android Developer Course by Rob Percival (which is excellent, and I highly recommend every computer/Android geek take), he showed us how to make use of SQLlite in our Android app. In the demonstration and challenge, he was using static inputs, like this:
// And add some information to that table.
firstDatabaseEver.execSQL(“INSERT INTO events (name, year) VALUES (‘Birth’, 1985)”);

Which is great. He was just showing us how SQLlite worked. With that said, in a really useful app, I figured I would need a way to take a user’s input and put it into the table, so I fiddled around a bit, and here is what I came up with.

MainActivity.java:
/* Copyright 2017 by AlaskaLinuxUser (https://thealaskalinuxuser.wordpress.com)
*
* Licensed under the Apache License, Version 2.0 (the “License”);
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an “AS IS” BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.alaskalinuxuser.sqllitedemo;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends AppCompatActivity {

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

// Let’s make some variables to use later.
String nameVar = “Joined the Navy”;
int yearVar = 2004;

try {

// First, make a database, open or create will open if exist, create if not.
SQLiteDatabase firstDatabaseEver = this.openOrCreateDatabase(“events”, MODE_PRIVATE, null);

// Now, let’s ceate a table.
firstDatabaseEver.execSQL(“CREATE TABLE IF NOT EXISTS events (name VARCHAR, year INT(4))”);

// And add some information to that table.
firstDatabaseEver.execSQL(“INSERT INTO events (name, year) VALUES (‘Birth’, 1985)”);

// And a second one for fun, this time, we will use our variable we created, you can see
// how this would be useful for an app, so you could have the user’s input dumped into a
// table.
firstDatabaseEver.execSQL(“INSERT INTO events (name, year) VALUES (‘” + nameVar + “‘, ” + yearVar + “)”);

// Now we need a cursor to retreive the data with a query.
Cursor myCursor = firstDatabaseEver.rawQuery(“SELECT * FROM events”, null);

// Now that we have the data, we need a way to index them, or grab the parts we need.
int nameIndex = myCursor.getColumnIndex(“name”);
int yearIndex = myCursor.getColumnIndex(“year”);

// Let’s start from the top of the table, by moving to the first position.
myCursor.moveToFirst();

// And if it is not null, let’s use it.
while (myCursor != null){

// Logging for posterity….
Log.i(“WJH”, myCursor.getString(nameIndex));
Log.i(“WJH”, Integer.toString(myCursor.getInt(yearIndex)));

// And move on to the next result in the table.
myCursor.moveToNext();
}
} catch (Exception e) {

e.printStackTrace();

}
}
}

Note that I don’t actually have user’s input into the table here, but I did use variables to input the data into the table, like so:

// Let’s make some variables to use later.
String nameVar = “Joined the Navy”;
int yearVar = 2004;

// And a second one for fun, this time, we will use our variable we created, you can see
// how this would be useful for an app, so you could have the user’s input dumped into a
// table.
firstDatabaseEver.execSQL(“INSERT INTO events (name, year) VALUES (‘” + nameVar + “‘, ” + yearVar + “)”);

As we can see, the input into the table of “events” is based on the output of the variables “nameVar and yearVar, which I previously defined. It is really easy to see that we could use something like getText from an editText field and input those variables into the tables. Which I think is the best use of it.

Linux – keep it simple.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s