Android SQLite database, androidsqlite

Android SQLite database, androidsqlite

After a while, I insisted on not talking about it. It means that the computer version of UC browser has a bunch of bugs. Although some small functions are very user-friendly.

As the default database in the system, SQLite is sufficient for storing some data normally. There are also corresponding APIs for convenient operations. The official recommendation is to use the class and SQLiteOpenHelper to manage the database. Use the fields in the table as the member attributes of the class, and implement BaseColumns for the table structure class. This interface has two fields: _ COUNT and _ ID. _ COUNT indicates that the system counts all rows. _ ID is the unique id of each row. Here I want to design a table that stores the positioning information each time. The corresponding class is LocinfoTable. java.

　　

1 package com. example. sqlitetest. db; 2 3 import android. provider. baseColumns; 4 5 public final class Locinfo {6 private static final String SQL _DELETE_ENTRIES = "DROP TABLE IF EXISTS" 7 + LocinfoTable. TABLE_NAME; 8 9 public static abstract class LocinfoTable implements BaseColumns {10 // location information table, basic information of a location 11 public static final String TABLE_NAME = "locinfo "; 12 // The number of times the record was located 13 public static final String COLUMN_NAME_LOC_COUNT = "LOC_COUNT"; 14 // start position longitude, that is, the longitude of the start point 15 public static final String column_name_start_long1_= "start_long1_"; 16 // the start point latitude, that is, the latitude of the start point 17 public static final String COLUMN_NAME_START_LATITUDE =" START_LATITUDE "; 18 // longitude of the end position, that is, 19 public static final String column_name_end_long1_= "end_long1_"; 20 // latitude of the end position, that is, the latitude of the start point 21 public static final String COLUMN_NAME_END_LATITUDE = "END_LATITUDE"; 22 // the start time 23 public static final String COLUMN_NAME_START_TIME = "START_TIME "; 24 // end time 25 public static final String COLUMN_NAME_END_TIME = "END_TIME"; 26 // The DISTANCE from the start point to the end point 27 public static final String COLUMN_NAME_DISTANCE = "DISTANCE "; 28 // positioning type 29 public static final String COLUMN_NAME_LOCTYPE = "LOCTYPE"; 30 // REMARKS 31 public static final String COLUMN_NAME_REMARKS = "REMARKS"; 32} 33}

With the structure of the database table, the next step is to manage the database. According to the official recommendation, SQLiteOpenHelper should be inherited and its constructor should be rewritten. SQLiteOpenHelper mainly has two functions that must be implemented after inheritance. They are onCreate and onUpgrade. The onCreat function is called when the database is not created. This function is called when the database is created. The onUpgrade function is called when the database version is upgraded. Common Database upgrades include modifying the table structure. The following constructor must be added to inherit from SQLiteOpenHelper:

　　

public LocinfoDBHelper(Context context, String name, CursorFactory factory,            int version) {        super(context, name, factory, version);    }

At first glance, it seems that there are a lot of functions, but we really don't have to worry about creating a database, so we can write another constructor to reload it. The context environment is required, and the database name is required. Therefore, you can reload a constructor with only two parameters.

1 public LocinfoDBHelper(Context context, String dbname) 

Let's take a look at the SQLiteOpenhelper methods to facilitate database operations.

Function Name	Function Description
Public synchronized void close ()	Close any opened database
Public String getDatabaseName ()	Obtain the name of the currently opened database
Public SQLiteDatabase getReadableDatabase ()	Obtain a readable database instance. If the database instance to be retrieved does not exist, a database is created.
Public SQLiteDatabase getWritableDatabase ()	Obtain a writable database instance, same as above. It is created if it does not exist.
Public abstract void onCreate (SQLiteDatabase db)	It is called when a database is created for the first time. The table creation statement is usually put here.
Public void onDowngrade (SQLiteDatabase db, int oldVersion, int newVersion)	Database Version downgrade is called at a time, which is usually rarely used.
Public abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)	Database Version Upgrade, such as when updating the table structure.

 

After reading the above functions, I will probably understand them. SQLiteOpenHelper only provides a class for database management. The final operation of the database is performed through the SQLiteDataBase returned by its getWritableDatabase.

So let's take a look at this SQLiteDataBase class. For databases, the most basic thing is to add, delete, modify, and query DML statements, so let's focus on how these APIs are used.

1. Insert function:

Public longInsert(String table, String nullColumnHack, ContentValues values) Added in API level 1 Convenience method for inserting a row into the database.ParametersTable // the table name the table to insert the row intonullColumnHack // usually null optional; may be null. SQL doesn' t allow inserting a completely empty row without naming at least one column name. if your provided values is empty, no column names are known and an empty row can't be inserted. if not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty. values // the field to be inserted and its value this map contains the initial column values for the row. the keys shoshould be the column names and the values the column valuesReturnsThe row ID of the newly inserted row, or-1 if an error occurred

2. Update operations

Public intUpdate(String table, ContentValues values, String whereClause, String [] whereArgs) Added in API level 1 Convenience method for updating rows in the database.ParametersTable// The table name the table to update inValues// Map the fields to be updated and their attributes. a map from column names to new column values. null is a valid value that will be translated to NULL.WhereClause// Where clause the optional WHERE clause to apply when updating. Passing null will update all rows.WhereArgs// Wehere condition, used to replace "? "You may include? S in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.ReturnsThe number of rows affected

3. query operations

　　

Public CursorQuery(String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit) Added in API level 1 Query the given table, returning a Cursor over the result set.ParametersTable // table nameThe table name to compile the query against.Columns // name of the column to be queriedA list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn' t going to be used.Selection // query clause, which is equivalent to the field name in whereA filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.SelectionArgs // query ConditionYou may include? S in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.GroupBy // sorting FieldA filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.Having //A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself ). passing null will cause all row groups to be pinned ded, and is required when row grouping is not being used.OrderBy // sort FieldsHow to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.Limit // returnLimits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.Returns // The returned value is a cursor object. The cursor is used to operate the returned records row by row.A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details.

4. delete a function

　　

Public intDelete(String table, String whereClause, String [] whereArgs) Added in API level 1 Convenience method for deleting rows in the database.ParametersTable // table nameThe table to delete fromWhereClause // fields in the where clauseThe optional WHERE clause to apply when deleting. Passing null will delete all rows.WhereArgs // where clause ConditionYou may include? S in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.ReturnsThe number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.

After the above functions, we can see that the API is to split each segment of the original SQL statement into a parameter, which can basically meet some simple queries. However, if complicated statements are to be processed, an original method is provided, that is, the function for directly executing SQL statements (this is also limited, however, there are usually no complicated SQL statements on mobile phones. After all, mobile phones are not servers ):

Public voidExecSQL(String SQL) Added in API level 1 Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data. // only the SQL statement It has no means to return any data (such as the number of affected rows) that does not require data to be returned is executed ). instead, you're encouraged to use insert (String, String, ContentValues), update (String, ContentValues, String, String []), et al, when possible. when using enableWriteAheadLogging (), journal_mode is automatically managed by this class. so, do not set journal_mode using "PRAGMA journal_mode '" statement if your app is using enableWriteAheadLogging () Parameterssql the SQL statement to be executed. multiple statements separated by semicolons are not supported. throwsSQLException // SQL exception if the SQL string is invalid

This section describes how to use these functions. Next, let's take a look at some of the problems I encountered during learning:

If there are too many fields in the database, it is easy to write incorrect field names or field types. In this case, you need to delete the table and create a new table. At this time, the onUpgrade function will be triggered, and the database version will be upgraded, if we continue to use version = 1 as a parameter to obtain instances of the help class, and then use this instance to obtain database objects for database operations, an error will be reported:

android.database.sqlite.SQLiteException: Can't downgrade database from version 2 to 1
So we need to upgrade the version to 2. however, manual upgrade is not suitable. It is not difficult to update each update. Therefore, I want to remember this version through sharedpreferences and retrieve it whenever necessary, if the database is updated, update the version in sharedpreferences in the onUpgrade function.

Extended knowledge:

SQLite Basic Data Type

1. NULL: NULL.
2. INTEGER: A signed INTEGER, depending on the size of the number to be saved.
3. REAL: floating point number, which is stored as an 8-byte IEEE floating point number.
4. TEXT: String TEXT.
5. BLOB: binary object.

How to view the sqlite database in the Android simulator:

1. Enter the simulator adb shell or specify the device name in cmd.

　　

2. Go to the application directory: cd/data/application package name

　　

3. You can see the connected folders. The databases is what we need, go in, and then sqlite3 database_name

　　

4. Now you can perform some common operations. For example:. schema, select

　　

Without knowing it, it would take a long time to write a blog. Reading others' blogs and writing them on their own are two different things. Let's continue to spur ourselves. If you have any questions, you can discuss them together.