Golalta

Sep 06, 2019 SQLite FAQ: How do I create an autoincrement field in SQLite? You define a SQLite autoincrement field (also known in other databases as a serial, identity, or primary key field) with this syntax: id INTEGER PRIMARY KEY SQLite autoincrement field in a table. SQLite autoincrement FAQ: How do I get the autoincrement value from my last SQLite INSERT command? You can get the integer value of the primary key field from the last insert into an autoincrement field using a SQLite function named lastinsertrowid, as shown in the example below.

Saving data to a database is ideal for repeating or structured data,such as contact information. This page assumes that you arefamiliar with SQL databases in general and helps you get started withSQLite databases on Android. The APIs you'll need to use a databaseon Android are available in the android.database.sqlite package.

Caution: Although these APIs are powerful, they are fairly low-level and require a great deal of time and effort to use:

• There is no compile-time verification of raw SQL queries. As your data graph changes, you need to update the affected SQL queries manually. This process can be time consuming and error prone.
• You need to use lots of boilerplate code to convert between SQL queries and data objects.

For these reasons, we highly recommended using the Room Persistence Library as an abstraction layer for accessing information in your app's SQLite databases.

Define a schema and contract

One of the main principles of SQL databases is the schema: a formaldeclaration of how the database is organized. The schema is reflected in the SQLstatements that you use to create your database. You may find it helpful tocreate a companion class, known as a contract class, which explicitly specifiesthe layout of your schema in a systematic and self-documenting way.

A contract class is a container for constants that define names for URIs,tables, and columns. The contract class allows you to use the same constantsacross all the other classes in the same package. This lets you change a columnname in one place and have it propagate throughout your code.

A good way to organize a contract class is to put definitions that areglobal to your whole database in the root level of the class. Then create an innerclass for each table. Each inner class enumerates the corresponding table's columns.

Note: By implementing the BaseColumnsinterface, your inner class can inherit a primarykey field called _ID that some Android classes such as CursorAdapter expect it to have. It's not required, but this can help your databasework harmoniously with the Android framework.

For example, the following contract defines the table name and column names for asingle table representing an RSS feed:

Create a database using an SQL helper

Once you have defined how your database looks, you should implement methodsthat create and maintain the database and tables. Here are some typicalstatements that create and delete a table:

Just like files that you save on the device's internalstorage, Android stores your database in your app's private folder. Your data issecure, because by default this area is notaccessible to other apps or the user.

The SQLiteOpenHelper class contains a usefulset of APIs for managing your database.When you use this class to obtain references to your database, the systemperforms the potentiallylong-running operations of creating and updating the database only whenneeded and not during app startup. All you need to do is callgetWritableDatabase() orgetReadableDatabase().

Note: Because they can be long-running,be sure that you call getWritableDatabase() or getReadableDatabase() in a background thread,such as with AsyncTask or IntentService.

To use SQLiteOpenHelper, create a subclass thatoverrides the onCreate() andonUpgrade() callback methods. You mayalso want to implement theonDowngrade() oronOpen() methods,but they are not required.

For example, here's an implementation of SQLiteOpenHelper thatuses some of the commands shown above:

To access your database, instantiate your subclass ofSQLiteOpenHelper:

Put information into a database

Insert data into the database by passing a ContentValuesobject to the insert() method:

The first argument for insert()is simply the table name.

The second argument tells the framework what to do in the event that theContentValues is empty (i.e., you did notput any values).If you specify the name of a column, the framework inserts a row and setsthe value of that column to null. If you specify null, like in thiscode sample, the framework does not insert a row when there are no values.

The insert() methods returns the ID for thenewly created row, or it will return -1 if there was an error inserting the data. This can happenif you have a conflict with pre-existing data in the database.

Read information from a database

To read from a database, use the query() method, passing it your selection criteria and desired columns.The method combines elements of insert()and update(), except the column listdefines the data you want to fetch (the 'projection'), rather than the data to insert. The resultsof the query are returned to you in a Cursor object.

The third and fourth arguments (selection and selectionArgs) arecombined to create a WHERE clause. Because the arguments are provided separately from the selectionquery, they are escaped before being combined. This makes your selection statements immune to SQLinjection. For more detail about all arguments, see thequery() reference.

To look at a row in the cursor, use one of the Cursor movemethods, which you must always call before you begin reading values. Since the cursor starts atposition -1, calling moveToNext() places the 'read position' on thefirst entry in the results and returns whether or not the cursor is already past the last entry inthe result set. For each row, you can read a column's value by calling one of theCursor get methods, such as getString() or getLong(). For each of the get methods,you must pass the index position of the column you desire, which you can get by callinggetColumnIndex() orgetColumnIndexOrThrow(). When finishediterating through results, call close() on the cursorto release its resources.For example, the following shows how to get all the item IDs stored in a cursorand add them to a list:

Delete information from a database

To delete rows from a table, you need to provide selection criteria thatidentify the rows to the delete() method. Themechanism works the same as the selection arguments to thequery() method. It divides theselection specification into a selection clause and selection arguments. Theclause defines the columns to look at, and also allows you to combine columntests. The arguments are values to test against that are bound into the clause.Because the result isn't handled the same as a regular SQL statement, it isimmune to SQL injection.

The return value for the delete() methodindicates the number of rows that were deleted from the database.

Update a database

When you need to modify a subset of your database values, use theupdate() method.

Updating the table combines the ContentValues syntax ofinsert() with the WHERE syntaxof delete().

The return value of the update() method isthe number of rows affected in the database.

Persisting database connection

Since getWritableDatabase()and getReadableDatabase() areexpensive to call when the database is closed, you should leave your database connectionopen for as long as you possibly need to access it. Typically, it is optimal to close the databasein the onDestroy() of the calling Activity.

Debug your database

The Android SDK includes a sqlite3 shell tool that allows you to browsetable contents, run SQL commands, and perform other useful functions on SQLitedatabases. For more information, see how to how to issue shell commands.

• Android Basics

• Android - User Interface

• Android Advanced Concepts

• Android Useful Examples

• Android Useful Resources

• Selected Reading

SQLite is a opensource SQL database that stores data to a text file on a device. Android comes in with built in SQLite database implementation.

SQLite supports all the relational database features. In order to access this database, you don't need to establish any kind of connections for it like JDBC,ODBC e.t.c

Database - Package

The main package is android.database.sqlite that contains the classes to manage your own databases

Database - Creation

In order to create a database you just need to call this method openOrCreateDatabase with your database name and mode as a parameter. It returns an instance of SQLite database which you have to receive in your own object.Its syntax is given below

Apart from this , there are other functions available in the database package , that does this job. They are listed below

Database - Insertion

we can create table or insert data into table using execSQL method defined in SQLiteDatabase class. Its syntax is given below

This will insert some values into our table in our database. Another method that also does the same job but take some additional parameter is given below

Sqlite Database In Android Studio

Database - Fetching

We can retrieve anything from database using an object of the Cursor class. We will call a method of this class called rawQuery and it will return a resultset with the cursor pointing to the table. We can move the cursor forward and retrieve the data.

There are other functions available in the Cursor class that allows us to effectively retrieve the data. That includes

Database - Helper class

For managing all the operations related to the database , an helper class has been given and is called SQLiteOpenHelper. It automatically manages the creation and update of the database. Its syntax is given below

Example

Here is an example demonstrating the use of SQLite Database. It creates a basic contacts applications that allows insertion, deletion and modification of contacts.

To experiment with this example, you need to run this on an actual device on which camera is supported.

Following is the content of the modified MainActivity.java.

Following is the modified content of display contact activity DisplayContact.java

Following is the content of Database class DBHelper.java

Following is the content of the res/layout/activity_main.xml

Following is the content of the res/layout/activity_display_contact.xml

Following is the content of the res/value/string.xml

Following is the content of the res/menu/main_menu.xml

Following is the content of the res/menu/display_contact.xml

This is the defualt AndroidManifest.xml of this project

Let's try to run your application. I assume you have connected your actual Android Mobile device with your computer. To run the app from Android studio , open one of your project's activity files and click Run icon from the tool bar. Before starting your application,Android studio will display following window to select an option where you want to run your Android application.

Select your mobile device as an option and then check your mobile device which will display following screen −

Now open your optional menu, it will show as below image: Optional menu appears different places on different versions

Click on the add button of the menu screen to add a new contact. It will display the following screen −

It will display the following fields. Please enter the required information and click on save contact. It will bring you back to main screen.

Use Sqlite In Android Studio

Now our contact sai has been added.In order to see that where is your database is created. Open your android studio, connect your mobile. Go tools/android/android device monitor. Now browse the file explorer tab. Now browse this folder /data/data/<your.package.name>/databases<database-name>.