Home > Articles

Working with Databases in Android

Android architect Adam Stroud presents the Android API for working with databases and explains how to get data from an app to a database and back again.

This chapter is from the book

This chapter is from the book

The previous chapter introduced the SQLiteOpenHelper and SQLiteDatabase classes and discussed how to create databases. Of course, this is only the first step as a database is not very useful until it contains data and allows software to run queries against that data. This chapter explains how that is done in Android by discussing which Android SDK classes can be used to manipulate a database as well as query a database.

Manipulating Data in Android

The Android SDK contains many classes to support database operations. Along with the classes to support create, read, update, and delete (CRUD) operations, the SDK contains classes to help generate the queries that read the database. Following are the classes introduced in this chapter and a summary of how they are used to work with databases in Android:

  • SQLiteDatabase: Represents a database in Android. It contains methods to perform standard database CRUD operations as well as control the SQLite database file used by an app.

  • Cursor: Holds the result set from a query on a database. An app can read the data from a cursor and display it to a user or perform business logic based on the data contained in the cursor.

  • ContentValues: A key/value store that inserts data into a row of a table. In most cases, the keys map to the column names of the table, and the values are the data to enter into the table.

  • CursorLoader: Part of the loader framework that handles cursor objects.

  • LoaderManager: Manages all loaders for an activity or fragment. The LoaderManager contains the API for initializing and resetting a loader that may be used by Android components.

Working with SQL is a vital part of working with databases in Android. In Chapter 2, “An Introduction to SQL,” we saw how SQL is used to both create and upgrade a database. SQL can also be used to read, update, and delete information from a database in Android. The Android SDK provides useful classes to assist in creating SQL statements, while also supporting the use of Java string processing to generate SQL statements.

Working with SQL in Android involves calling methods on an SQLiteDatabase object. This class contains methods for building SQL statements as well as convenience methods to make issuing SQL statements to the database easy.

In a typical database use case, inserting data into the database is the step that follows creating the database. This makes sense since a database is useful only after it contains data. The steps to create a database were covered in the previous chapter, so this discussion starts with inserting data into a database.

Inserting Rows into a Table

The SQLiteDatabase class contains multiple convenience methods that can be used to perform insert operations. In most cases, one of the following three methods is used to perform an insert operation:

  • long insert(String table, String nullColumnHack, ContentValues values)

  • long insertOrThrow(String table, String nullColumnHack, ContentValues values)

  • long insertWithOnConflict(String table, String nullColumnHack, ContentValues values, int conflictAlgorithm)

Notice that the parameter lists for all the variations of the insert methods contain (as the first three parameters) a String tableName, a String nullColumnHack, and ContentValues values. SQLiteDatabase.insertWithOnConflict() contains a fourth parameter which will be discussed soon. The common three parameters for the insert methods are

  • String table: Gives the name of the table on which to perform the insert operation. This name needs to be the same as the name given to the table when it was created.

  • String nullColumnHack: Specifies a column that will be set to null if the ContentValues argument contains no data.

  • ContentValues values: Contains the data that will be inserted into the table.

ContentValues is a maplike class that matches a value to a String key. It contains multiple overloaded put methods that enforce type safety. Here is a list of the put methods supported by ContentValues:

  • void put(String key, Byte value)

  • void put(String key, Integer value)

  • void put(String key, Float value)

  • void put(String key, Short value)

  • void put(String key, byte[] value)

  • void put(String key, String value)

  • void put(String key, Double value)

  • void put(String key, Long value)

  • void put(String key, Boolean value)

Each put method takes a String key and a typed value as parameters. When using ContentValues to insert data into a database, the key parameter must match the name of the column for the table that is targeted by the insert.

In addition to the overloaded put methods just listed, there is also a put(ContentValues other) method that can be used to add all the values from another ContentValues object, and a putNull(String key) method that adds a null value to a column of a table.

In a typical use case, a new instance of ContentValues is created and populated with all the values that should be inserted into the table. The ContentValues object is then passed to one of the insert methods from SQLiteDatabase. Listing 5.1 shows typical ContentValues usage.

Listing 5.1 Inserting Data with SQLiteDatabase.insert()

int id = 1;
String firstName = "Bob";
String lastName = "Smith";

ContentValues contentValues = new ContentValues();
contentValues.put("id", id);
contentValues.put("first_name", firstName);
contentValues.put("last_name", lastName);

SQLiteDatabase db = getDatabase();
db.insert("people", null, contentValues);

The code in Listing 5.1 passes a null for the value of the nullColumnHack to the SQLiteDatabase.insert() method. This is primarily because the code in Listing 5.1 “knows” what values were used to populate the values parameter and can ensure that there is at least one column represented in the ContentValues object. However, this is not always the case, and this is why the nullColumnHack parameter exists.

To explain nullColumnHack, consider the case where a ContentValues object that is inserted into a table contains no key/value pairs. This would amount to attempting to perform an insert operation without specifying any columns to insert data into. Such an insert statement is illegal in SQL because an insert statement must specify at least one column to insert data into. The nullColumnHack parameter can be used to guard against the “empty ContentValues” use case by specifying the name of a column that should be set to null in the case that the ContentValues object contains no data. Like the keys in the ContentValues instance, the string value for nullColumnHack must match the name of a column in the table that is targeted by the insert statement.

Listing 5.2 contains a usage of the nullColumnHack parameter. After the code in Listing 5.2 is run, column last_name will contain a value of null.

Listing 5.2 Specifying Null Columns with nullColumnHack

ContentValues contentValues = new ContentValues();
SQLiteDatabase db = getDatabase();
db.insert("people", "last_name", contentValues);

All three insert methods of SQLiteDatabase return a long. The value returned by the methods is the row ID of the inserted row, or a value of –1 if there was an error performing the insert.

Both Listings 5.1 and 5.2 used the simplest insert method to put a row into a table of the database, SQLiteDatabase.insert(). This method attempts to perform the insert and returns –1 if there is an error. The other two insert methods can be used to handle error cases differently.

SQLiteDatabase.insertOrThrow() is similar to SQLiteDatabase.insert(). However, it throws an SQLException if there was an error inserting the row. SQLiteDatabase.insertOrThrow() takes the same parameter list and has the same return type as SQLiteDatabase.insert(). It takes a String as the table parameter, a String as the nullColumnHack parameter, and a ContentValues object as the values parameter.

SQLiteDatabase.insertWithConflict(String table, String nullColumnHack, ContentValues values, int conflictAlgorithm) operates a little differently from the other two insert methods. It supports conflict resolution during the insert operation. Insertion conflicts occur when an attempt is made to insert a row into a table that would produce duplicates in a column that has the UNIQUE constraint applied to it, or duplicate data for the primary key. For example, consider the database table represented by Table 5.1.

Table 5.1 Example Database Table

first_name

last_name

id*

Bob

Smith

1

Ralph

Taylor

2

Sabrina

Anderson

3

Elizabeth

Hoffman

4

Abigail

Elder

5

In Table 5.1, the id column is the primary key and must hold a unique value for all rows across the entire table. Therefore, an attempt to insert a row containing an id of 1 would be an illegal operation in SQL because it would cause a UNIQUE constraint violation.

In this scenario, the two previous insert methods would indicate the error by either returning a value of -1 (SQLiteDatabase.insert()) or throwing an exception (SQLiteDatabase.insertOrThrow()). However, SQLiteDatabase.insertWithOnConflict() takes a fourth int parameter that can be used to tell the method how to handle the insertion conflict. The conflict resolution algorithms are defined as constants in SQLiteDatabase and can be one of the following:

  • SQLiteDatabase.CONFLICT_ROLLBACK: Aborts the current insert statement. If the insert was part of a transaction, any previous statements are also undone and the value of SQLiteDatabase.CONFLICT_FAIL is returned by the insertWithOnConflict() method.

  • SQLiteDatabase.CONFLICT_ABORT: Aborts the current statement. If the statement was part of a transaction, all previous statements are left untouched.

  • SQLiteDatabase.CONFLICT_FAIL: Similar to SQLiteDatabase.CONFLICT_ABORT. In addition to aborting the current statement, this flag causes the method to return SQLITE_CONSTRAINT as a return code.

  • SQLiteDatabase.CONFLICT_IGNORE: Skips the current statement and all other statements in the transaction are processed. When using this flag, no error value is returned.

  • SQLiteDatabase.CONFLICT_REPLACE: Removes conflicting rows currently in the table, and the new row is inserted. An error will not be returned when using this flag.

  • SQLiteDatabase.NONE: No conflict resolution is applied.

Updating Rows in a Table

Once data has been inserted into a database, it often needs to be updated. Like the three insert methods discussed previously, SQLiteDatabase has a couple of update methods that can be used to perform update operations on tables in a database:

  • int update(String table, ContentValues values, String whereClause, String[] whereArgs)

  • int updateWithOnConflict(String table, ContentValues values, String whereClause, String[] whereArgs, int conflictAlgorithm)

Much like the insert methods, both update methods take the same first four parameters, and updateWithOnConflict() takes a fifth parameter to define how a conflict should be resolved.

The common parameters for the update methods are

  • String table: Defines the name of the table on which to perform the update. As with the insert statements, this string needs to match the name of a table in the database schema.

  • ContentValues values: Contains the key/value pairs that map the columns and values to be updated by the update statement.

  • String whereClause: Defines the WHERE clause of an UPDATE SQL statement. This string can contain the “?” character that will be replaced by the values in the whereArgs parameter.

  • String[] whereArgs: Provides the variable substitutions for the whereClause argument.

Listing 5.3 shows an example of the SQLiteDatabase.update() call.

Listing 5.3 Example Update Call

String firstName = "Robert";

ContentValues contentValues = new ContentValues();
contentValues.put("first_name", firstName);

SQLiteDatabase db = getDatabase();
db.update("people", contentValues, "id = ?", new String[] {"1"});

Listing 5.3 updates the first name of the person that has an id of 1. The code first creates and populates a ContentValues object to hold the values that will be updated. It then makes the call to SQLiteDatabase.update() to issue the statement to the database. The rows are selected for the update() method using the whereClause and whereArgs parameters, which are in bold in Listing 5.3. The “?” in the whereClause parameter of the update() method serves as a placeholder for the statement. The whereArgs parameter, containing an array of strings, holds the value(s) that will replace the placeholder(s) when the statement is sent to the database. Since Listing 5.3 contains only a single placeholder, the string array only needs to be of size 1. When multiple placeholders are used, they will be replaced in order using the values from the string array. Passing null values for the whereClause and whereArgs parameters will cause the update statement to be run against every row in the table.

Table 5.2 shows the result of running the code in Listing 5.3 on Table 5.1. The changes to the row with id 1 are in bold.

Table 5.2 person Table after Call to update()

first_name

last_name

id*

Robert

Smith

1

Ralph

Taylor

2

Sabrina

Anderson

3

Elizabeth

Hoffman

4

Abigail

Elder

5

The basic whereClause in Listing 5.3 matches the value of a single column. When using either update method, any legal SQL whereClause can be used to build the statement.

Both update methods in SQLiteDatabase return an integer that represents the number of rows that were affected by the update statement.

Replacing Rows in a Table

In addition to insert and update operations, SQLiteDatabase supports the SQL replace operation with the SQLiteDatabase.replace() methods. In SQLite, a replace operation is an alias for INSERT OR REPLACE. It inserts the row if it does not already exist in a table, or updates the row if it already exists.

There are two versions of the replace() method in SQLiteDatabase: SQLiteDatabase.replace() and SQLiteDatabase.replaceOrThrow(). Both methods have the same parameter list:

  • String table: The name of the table on which to perform the operation

  • String nullColumnHack: The name of a column to set a null value in case of an empty ContentValues object

  • ContcentValues initialValues: The values to insert into the table

Both replace() methods return a long indicating the row ID of the new row, or a value of -1 if an error occurs. In addition, replaceOrThrow() can also throw an exception in the case of an error.

Listing 5.4 shows an example of the SQLiteDatabase.replace() call.

Listing 5.4 Example Replace Call

String firstName = "Bob";

ContentValues contentValues = new ContentValues();
contentValues.put("first_name", firstName);
contentValues.put("id", 1);

SQLiteDatabase db = getDatabase();
db.replace("people", null, contentValues);

Table 5.3 shows the state of the people table after running the SQLiteDatabase.replace() call in Listing 5.4. Notice that the last_name attribute for the first row is now blank. This is because there was a conflict when processing the SQLiteDatabase.replace() method. The ContentValues object passed to SQLiteDatabase.replace() specified a value of 1 for the id attribute. The conflict arises because the id attribute is the primary key for the table, and there is already a row that contains an id of 1. To resolve the conflict, the SQLiteDatabase.replace() method removes the conflicting row and inserts a new row containing the values specified in the ContentValues object. Because the ContentValues object passed to SQLiteDatabase.replace()contains values for only the first_name and id attributes, only those attributes are populated in the new row.

Table 5.3 person Table after replace() Call

first_name

last_name

id*

Bob

 

1

Ralph

Taylor

2

Sabrina

Anderson

3

Elizabeth

Hoffman

4

Abigail

Elder

5

Deleting Rows from a Table

Unlike the update and insert operations, SQLiteDatabase has only a single method for deleting rows: SQLiteDatabase.delete(String table, String whereClause, String[] whereArgs). The delete() method’s signature is similar to the signature of the update() method. It takes three parameters representing the name of the table from which to delete rows, the whereClause, and a string array of whereArgs. The processing of the whereClause and the whereArgs for the delete() method matches the whereClause processing for the update() method. The whereClause parameter contains question marks as placeholders, and the whereArgs parameter contains the values for the placeholders. Listing 5.5 shows a delete() method example.

Listing 5.5 Example Delete Method

SQLiteDatabase db = getDatabase();
db.delete("people", "id = ?", new String[] {"1"});

The results of running the code in Listing 5.5 are shown in Table 5.4, where there is no longer a row with an id of 1.

Table 5.4 Row Deleted from the Table

first_name

last_name

id*

Ralph

Taylor

2

Sabrina

Anderson

3

Elizabeth

Hoffman

4

Abigail

Elder

5

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020