I lately revealed a instructional describing the other to be had how to retailer knowledge in the community in an Android app. Then again, as a result of article duration constraints, I may just now not effectively duvet developing and the use of an SQLite database for knowledge patience. On this instructional, I delve into how one can use an SQLite database in extra element.
SQLite is a relational database control device, very similar to Oracle, MySQL, PostgreSQL and SQL Server. It implements a number of the SQL same old, however in contrast to the four database engines discussed above, it isn’t a consumer-server database engine. Relatively, it’s embedded into the top software. What this implies is that you’ll be able to package a SQLite database with your software, and get get right of entry to to all of the energy of a relational database inside of your software. SQLite is local to each Android and iOS, and each and every app can create and use an SQLite database in the event that they so want. Actually, in Android, software contacts, and media are saved and referenced the use of SQLite databases. A thrilling tidbit of knowledge is that SQLite is probably the most used database engine in the arena, and fairly in all probability the so much extensively deployed device ever. To learn extra approximately SQLite databases, discuss with the SQLite internet web site.
Common readers of this collection will realize that we’ve lately began the use of the Android knowledge binding tactics for tutorials. For those who don’t seem to be accustomed to the idea that, take a look at my earlier article discussing Android knowledge binding. Additionally, we use recyclerview to show lists, as additionally mentioned in the past. You can also need to learn each in case you aren’t conversant in the ideas.
To allow knowledge binding in your app, you will have to upload the next to your app construct.gradle document
dataBinding.enabled = actual
Additionally, to make use of each recyclerview and cardview to show lists, you will have to come with the related libraries to the dependencies segment in your app construct.gradle record (24.2.1 used to be the present library model on the time of writing).
dependencies ... bring together 'com.android.fortify:layout:24.2.1' bring together 'com.android.beef up:cardview-v7:24.2.1'
After all, a few SQL revel in will probably be very useful, even though you’ll nonetheless be capable of practice the educational with out earlier revel in with SQL. Then again, to free up the whole chances of the use of an SQLite database, you will have to take a look at SQL syntax.
Pattern app description
For the pattern app, we’re going to create two tables, an Supplier desk and an Worker desk. The Worker desk will include a overseas key connection with the Agency desk, and we will be able to display the best way to insert, make a selection, replace and delete rows from tables. We will be able to additionally show how simple it may be to show pieces decided on from an SQLite database in a RecyclerView (listing) and in a Spinner.
We’ve were given a MainActivity, from which we will be able to navigate to both the EmployerActivity (for running at the Organisation desk), or the EmployeeActivity (for running at the Worker desk).
SQLite database garage categories
Garage categories confer with how stuff is saved inside the database. SQLite databases retailer values in one of five imaginable garage categories:
- NULL – For null worth.
- INTEGER – For integers containing up to 8 bytes (thats from byte to lengthy).
- REAL – Numbers with floating aspect.
- TEXT – Textual content strings, saved the use of the database encoding (UTF-8 or UTF-16).
- BLOB – Binary knowledge, saved precisely as enter.
With this in thoughts, shall we create a few tables.
Outline your tables
Because the SQLite database is native to your software, you’ll have to make sure that your app creates database tables and drops them as wanted. You must deal with your database thru code. Allow’s start through developing the Enterprise desk first, along side the EmployerActivity. It’s just right follow to summary your SQLite database advent good judgment into a elegance. This is helping with maintainability. We name our elegance SampleDBContract.
public ultimate elegance SampleDBContract personal SampleDBContract() public static elegance Enterprise implements BaseColumns public static ultimate String TABLE_NAME = "corporation"; public static ultimate String COLUMN_NAME = "identify"; public static ultimate String COLUMN_DESCRIPTION = "description"; public static ultimate String COLUMN_FOUNDED_DATE = "date"; public static ultimate String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_NAME + " TEXT, " + COLUMN_DESCRIPTION + " TEXT, " + COLUMN_FOUNDED_DATE + " INTEGER" + ")";
We outline a personal constructor for SampleDBContract in order that it gained’t be by chance instantiated, after which we create a elegance to constitute the Agency desk. Notice that Service provider elegance implements the BaseColumns interface. The BaseColumns interface supplies two very helpful columns to our desk. Those are the _ID column, on the way to be vehicle incremented each time a new row is inserted in the desk, and a _COUNT column, which can be utilized via ContentProviders to go back a rely of the choice of data back from a question. We don’t require the _COUNT column. The CREATE_TABLE string, compiles to the next SQL observation:
CREATE TABLE IF NOT EXISTS enterprise (_id INTEGER PRIMARY KEY AUTOINCREMENT, identify TEXT, description TEXT, date INTEGER)
To this point, we’ve got merely outlined the Company desk schema.
Create database with SQLiteOpenHelper
One of the simplest ways to regulate database advent and versioning, is to create a subclass of SQLiteOpenHelper. SQLiteOpenHelper will ease the control of your SQLite database really, through commencing databases while wanted, developing databases if they don’t exist in addition to upgrading or downgrading as essential. You most effective need to override the onCreate() and onUpgrade() strategies, to specify the movements you wish to have carried out to create and/or replace the database.
public elegance SampleDBSQLiteHelper extends SQLiteOpenHelper personal static ultimate int DATABASE_VERSION = 1; public static ultimate String DATABASE_NAME = "sample_database"; public SampleDBSQLiteHelper(Context context) tremendous(context, DATABASE_NAME, null, DATABASE_VERSION); @Override public void onCreate(SQLiteDatabase sqLiteDatabase) sqLiteDatabase.execSQL(SampleDBContract.Organization.CREATE_TABLE); @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + SampleDBContract.Service provider.TABLE_NAME); onCreate(sqLiteDatabase);
It’s that straightforward. The database model begins at 1, and we provide our SQLite database a identify (sample_database). The constructor merely calls the tremendous elegance’ constructor, with the database identify and model. In onCreate, we inform the SQLiteDatabase item to execute the Enterprise CREATE_TABLE SQL observation. In any case, onUpgrade, we drop the Agency desk and re create it. In your case, you can also want to run ALTER statements or no matter what complicated steps your app calls for to transport upload/cast off the important columns and tables.
Insert knowledge into your SQLite database
The Supplier desk has three primary columns, the identify, description and founded_date columns. Clicking the shop button calls the saveToDB() approach.
Inside of saveToDB(), we get a connection with a SQLiteDatabase item, the use of SQLiteOpenHelper’s getWritableDatabase() way. This technique will create the database, if it doesn’t but exist, or open it, if it has already been created. getWritableDatabase returns an SQLiteDatabase item, which permits learn/write get right of entry to.
personal void saveToDB() SQLiteDatabase database = new SampleDBSQLiteHelper(this).getWritableDatabase(); ContentValues values = new ContentValues(); values.placed(SampleDBContract.Service provider.COLUMN_NAME, binding.nameEditText.getText().toString()); values.placed(SampleDBContract.Agency.COLUMN_DESCRIPTION, binding.descEditText.getText().toString()); check out Calendar calendar = Calendar.getInstance(); calendar.setTime((new SimpleDateFormat("dd/MM/yyyy")).parse( binding.foundedEditText.getText().toString())); lengthy date = calendar.getTimeInMillis(); values.placed(SampleDBContract.Organization.COLUMN_FOUNDED_DATE, date); capture (Exception e) Log.e(TAG, "Errors", e); Toast.makeText(this, "Date is in the flawed layout", Toast.LENGTH_LONG).display(); go back; lengthy newRowId = database.insert(SampleDBContract.Corporation.TABLE_NAME, null, values); Toast.makeText(this, "The brand new Row Identity is " + newRowId, Toast.LENGTH_LONG).display();
There are four necessary issues to notice in the code snippet above:
- We get a SQLiteDatabase item that allows write get entry to to the database.
- Values to be saved in the database are positioned in a ContentValue item, with the column identify as the important thing.
- We placed Date in the ContentValue item as a lengthy, that allows you to translate to SQLite database garage elegance INTEGER. You’ll be able to in fact keep Date as a string should you please, however we use lengthy in order that we will be able to simply carry out more than and/or not up to comparisons whilst querying the database.
- Placing a row in the database the use of database.insert() means returns the row identity.
Make a selection knowledge from your SQLite database
Very similar to the getWritableDatabase() way above, we will be able to name the SQLiteOpenHelper item’s getReadableDatabase() to get an SQLiteDatabase item that can be utilized for learn get right of entry to to the database. It’s value bringing up that the SQLiteDatabase item back via getReadableDatabase() is the very same learn/write database again through getWritableDatabase(), with the exception of if there’s a constraint such because the document device containing the database being complete, that forces the database to open learn best.
The readFromDB approach goes to question the database, and go back all rows from the Corporation desk the place the Supplier identify fits phase or all the worth in the nameEditText, and the similar with the outline, and the place the corporate used to be based after the date enter in the Discovered Date EditText.
personal void readFromDB() String identify = binding.nameEditText.getText().toString(); String desc = binding.descEditText.getText().toString(); lengthy date = 0; check out Calendar calendar = Calendar.getInstance(); calendar.setTime((new SimpleDateFormat("dd/MM/yyyy")).parse( binding.foundedEditText.getText().toString())); date = calendar.getTimeInMillis(); seize (Exception e) SQLiteDatabase database = new SampleDBSQLiteHelper(this).getReadableDatabase(); String projection = SampleDBContract.Agency._ID, SampleDBContract.Organization.COLUMN_NAME, SampleDBContract.Organisation.COLUMN_DESCRIPTION, SampleDBContract.Enterprise.COLUMN_FOUNDED_DATE ; String variety = SampleDBContract.Corporation.COLUMN_NAME + " like ? and " + SampleDBContract.Enterprise.COLUMN_FOUNDED_DATE + " > ? and " + SampleDBContract.Supplier.COLUMN_DESCRIPTION + " like ?"; String selectionArgs = "%" + identify + "%", date + "", "%" + desc + "%"; Cursor cursor = database.question( SampleDBContract.Agency.TABLE_NAME, // The desk to question projection, // The columns to go back variety, // The columns for the WHERE clause selectionArgs, // The values for the WHERE clause null, // do not workforce the rows null, // do not clear out by way of row teams null // do not type ); Log.d(TAG, "The whole cursor rely is " + cursor.getCount()); binding.recycleView.setAdapter(new SampleRecyclerViewCursorAdapter(this, cursor));
Within the ultimate question means above, projection is a String array, representing the columns we need to fetch, variety is a String illustration of the SQL WHERE clause, formatted such that the ‘?’ personality might be changed by way of arguments in the selectionArgs String array. You’ll be able to additionally workforce, clear out and/or type the question effects, none of which we do right here. Placing knowledge into an SQLite database the use of the process above protects towards SQL injection.
Keep in mind of the thing again via the question. A Cursor item. Within the subsequent segment, we’re going to display how simple it’s to show the result of a Cursor the use of RecyclerView.
Show Cursor contents in a RecyclerView
A Cursor supplies random get right of entry to to the outcome set again via a database question. Because of this you’ll be able to get right of entry to values at any region in the cursor, similar to Java Lists and/or Arrays. With this data, we will be able to enforce a RecyclerView the use of a Cursor in just about the similar means we put in force RecyclerView the use of ArrayLists. Somewhat than calling Record.get(i), with a Cursor, you progress the Curosr to the specified place, the use of moveToPosition(). After shifting the Cursor to the specified place, you name the proper getXXX(int columnIndex) approach, the place XXX is both one of Blob, Double, Drift, Int, Lengthy, Brief or String.
Then again, as an alternative of bothering approximately the right kind column indices from our readFromDB() means above, we use the helpfully supplied getColumnIndexOrThrow() means, which fetches the index of the named column, or throws an Exception if the column identify doesn’t exist inside the Cursor.
public elegance SampleRecyclerViewCursorAdapter extends RecyclerView.Adapter<SampleRecyclerViewCursorAdapter.ViewHolder> Context mContext; Cursor mCursor; public SampleRecyclerViewCursorAdapter(Context context, Cursor cursor) mContext = context; mCursor = cursor; public static elegance ViewHolder extends RecyclerView.ViewHolder EmployerListItemBinding itemBinding; public ViewHolder(View itemView) tremendous(itemView); itemBinding = DataBindingUtil.bind(itemView); public void bindCursor(Cursor cursor) itemBinding.nameLabel.setText(cursor.getString( cursor.getColumnIndexOrThrow(SampleDBContract.Enterprise.COLUMN_NAME) )); itemBinding.descLabel.setText(cursor.getString( cursor.getColumnIndexOrThrow(SampleDBContract.Organization.COLUMN_DESCRIPTION) )); Calendar calendar = Calendar.getInstance(); calendar.setTimeInMillis(cursor.getLong( cursor.getColumnIndexOrThrow(SampleDBContract.Corporation.COLUMN_FOUNDED_DATE))); itemBinding.foundedLabel.setText(new SimpleDateFormat("dd/MM/yyyy").layout(calendar.getTime())); @Override public int getItemCount() go back mCursor.getCount(); @Override public void onBindViewHolder(ViewHolder holder, int place) mCursor.moveToPosition(place); holder.bindCursor(myCursor); @Override public ViewHolder onCreateViewHolder(ViewGroup figure, int viewType) View view = LayoutInflater.from(determine.getContext()).inflate( R.format.employer_list_item, figure, fake); ViewHolder viewHolder = new ViewHolder(view); go back viewHolder;
Defining overseas keys
To recap, to this point, we’ve created an Company desk, which we now have populated with rows of content material. We, now intend to create an Worker desk, which is said to the Agency desk during the Supplier _ID column.We outline an Worker elegance that extends BaseColumns, in the SampleDBContract elegance (very similar to how we outlined the Service provider elegance). Word that in the create desk String for Worker, we incorporated the String “FOREIGN KEY(employer_id) REFERENCES corporation(_id)”
public static elegance Worker implements BaseColumns public static ultimate String TABLE_NAME = "worker"; public static ultimate String COLUMN_FIRSTNAME = "firstname"; public static ultimate String COLUMN_LASTNAME = "lastname"; public static ultimate String COLUMN_DATE_OF_BIRTH = "date_of_birth"; public static ultimate String COLUMN_EMPLOYER_ID = "employer_id"; public static ultimate String COLUMN_JOB_DESCRIPTION = "job_description"; public static ultimate String COLUMN_EMPLOYED_DATE = "employed_date"; public static ultimate String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_FIRSTNAME + " TEXT, " + COLUMN_LASTNAME + " TEXT, " + COLUMN_DATE_OF_BIRTH + " INTEGER, " + COLUMN_EMPLOYER_ID + " INTEGER, " + COLUMN_JOB_DESCRIPTION + " TEXT, " + COLUMN_EMPLOYED_DATE + " INTEGER, " + "FOREIGN KEY(" + COLUMN_EMPLOYER_ID + ") REFERENCES " + Organization.TABLE_NAME + "(" + Organization._ID + ") " + ")";
Upgrading the SQLiteOpenHelper
When you have adopted the educational up so far, you’ll have constructed an Service provider desk and inserted values already. If you don’t amendment your database model, the brand new Worker desk is not created. Sadly, in case you amendment the model, keep in mind that the onUpgrade() means, as recently outlined, drops the Organisation desk. To protect by contrast, you’ll be able to remark out (or delete) the drop remark in onUpgrade(), and upload an execSQL() remark to create the Worker desk. Because the Worker desk references the Organization desk, you will have to create the Service provider desk first.
public elegance SampleDBSQLiteHelper extends SQLiteOpenHelper personal static ultimate int DATABASE_VERSION = 2; public static ultimate String DATABASE_NAME = "sample_database"; public SampleDBSQLiteHelper(Context context) tremendous(context, DATABASE_NAME, null, DATABASE_VERSION); @Override public void onCreate(SQLiteDatabase sqLiteDatabase) sqLiteDatabase.execSQL(SampleDBContract.Agency.CREATE_TABLE); sqLiteDatabase.execSQL(SampleDBContract.Worker.CREATE_TABLE); // We do not need to delete consumer knowledge. @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) if(oldVersion == 0 && newVersion == 2) sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + SampleDBContract.Worker.TABLE_NAME); else sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + SampleDBContract.Agency.TABLE_NAME); sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + SampleDBContract.Worker.TABLE_NAME); onCreate(sqLiteDatabase);
Show knowledge from an SQLite database question in a Spinner
To create an Worker, the consumer wishes to make a choice the corresponding Corporation. A great way to try this is to give a Spinner to the app consumer. Showing the contents of a Cursor in a Spinner is lovely simple.
First, we carry out a question as mentioned above, fetching simply the Enterprise identify and identity (queryCols). Subsequent, we create a SimpleCursorAdapter example passing it the Cursor, an array of columns to show (adapterCols), and an array of perspectives that the columns will have to be displayed in (adapterRowViews). Then, we set the Spinner’s Adapter to the SimpleCursorAdapter.
String queryCols = new String"_id", SampleDBContract.Corporation.COLUMN_NAME; SQLiteDatabase database = new SampleDBSQLiteHelper(this).getReadableDatabase(); Cursor cursor = database.question( SampleDBContract.Company.TABLE_NAME, // The desk to question queryCols, // The columns to go back null, // The columns for the WHERE clause null, // The values for the WHERE clause null, // do not team the rows null, // do not clear out by way of row teams null // do not type ); String adapterCols = new StringSampleDBContract.Organisation.COLUMN_NAME; int adapterRowViews = new intandroid.R.identity.text1; SimpleCursorAdapter cursorAdapter = new SimpleCursorAdapter( this, android.R.format.simple_spinner_item, cursor, adapterCols, adapterRowViews, 0); cursorAdapter.setDropDownViewResource(android.R.format.simple_spinner_dropdown_item); binding.employerSpinner.setAdapter(cursorAdapter);
Insert overseas key in database
Placing a row containing a overseas secret is utterly just like placing rows in tables with out overseas key constraints. The adaptation here’s that we get a connection with the chosen Cursor from the Spinner, after which get the worth of the Company _ID column.
Choose from SQLite database the use of JOIN
Sadly, we will be able to’t use the SQLiteDatabase’s question() option to question more than one tables. To question more than one tables, we will have to supply our personal SQL question. Within the pattern, the question is outlined in the SampleDBContract elegance.
public static ultimate String SELECT_EMPLOYEE_WITH_EMPLOYER = "SELECT * " + "FROM " + Worker.TABLE_NAME + " ee INNER JOIN " + Organization.TABLE_NAME + " er " + "ON ee." + Worker.COLUMN_EMPLOYER_ID + " = er." + Service provider._ID + " WHERE " + "ee." + Worker.COLUMN_FIRSTNAME + " like ? AND ee." + Worker.COLUMN_LASTNAME + " like ?";
Realize that we use the ‘?’ personality in the WHERE clause in so much the similar method as defined above for the question() way. To execute the supplied SQL, we’ll want to outline a selectionArgs String containing values so they can exchange the ‘?’s in our supplied SQL question.
personal void readFromDB() String firstname = binding.firstnameEditText.getText().toString(); String lastname = binding.lastnameEditText.getText().toString(); SQLiteDatabase database = new SampleDBSQLiteHelper(this).getReadableDatabase(); String selectionArgs = "%" + firstname + "%", "%" + lastname + "%"; Log.d(TAG, SampleDBContract.Worker.QUERY_WITH_EMPLOYER); Cursor cursor = database.rawQuery(SampleDBContract.Worker.QUERY_WITH_EMPLOYER, selectionArgs); Log.d(TAG, "The entire cursor rely is " + cursor.getCount()); binding.recycleView.setAdapter(new SampleJoinRecyclerViewCursorAdapter(this, cursor));
In spite of everything
As same old, all the supply code is to be had on github, to be used, reuse and misuse as you spot have compatibility. SQLite databases are very tough, and it’s superb that it’s to be had to all android apps, all the time. At the same time as it may well be an overkill for so much knowledge garage wishes, it’s an absolute godsend while you wish to have it.