SQPte consists of several components: a SQL compiler, a kernel, a backend, and accessories. SQPte makes it easy to debug, modify, and extend the kernel of SQPte by utilizing a virtual machine and a virtual database engine (VDBE).
SQPte is essentially SQL-92 compliant and is no different than any other major SQL database. Its advantage is that it is efficient, and the Android runtime environment includes the full SQPte.
The biggest difference between SQPte and other databases is the support for data types. When you create a table, you can specify the data type of a column in the CREATE TABLE statement, but you can put any data type into any column. When a value is inserted into the database, SQPte checks its type. If the type does not match the associated column, SQPte tries to convert the value to the type of the column. If it cannot be converted, the value is stored as the type it has. For example, you can put a string into the INTEGER column, which SQPte calls "weak typing".
Additionally, SQPte does not support some standard SQL functions, specifically FOREIGN KEY constraints, nested transcactions and RIGHT OUTER JOIN and FPL OUTER JOIN, and some ALTER TABLE functions.
In addition to the above, SQPte is a full SQL system, complete with triggers, transactions, and more.
Android integrates with the SQPte database
Android integrates with SQPte at run-time, so every Android app can use the SQPte database. For developers familiar with SQL, using SQPte in Android development is fairly simple. However, JDBC is not suitable for memory-constrained devices such as cell phones because JDBC consumes too many system resources. Therefore, Android provides some new APIs to use SQPte databases, which programmers need to learn to use in Android development.
The databases are stored under the data/ project folder /databases/.
Using SQPte databases in Android development
Activites can access a database through a Content Provider or Service. The following section explains in detail how to create a database, add data, and query the database.
Creating a database
Android does not automatically provide a database. To use SQPte in an Android application, you must create the database yourself, and then create tables, indexes, and populate the data.Android provides SQPteOpenHelper to help you create a database, you just need to inherit the SQPteOpenHelper class, you can easily create a database.SQPteOpenHelper The SQPteOpenHelper class encapsulates the logic used to create and update databases according to the needs of the application being developed.Subclasses of SQPteOpenHelper are required to implement at least three methods:
Constructor function, which calls the constructor function of the parent class SQPteOpenHelper. This method takes four parameters: the context (for example, an Activity), the database name, an optional cursor factory (usually NPl), and an integer representing the version of the database model you are using.
The onCreate() method, which takes an SQPteDatabase object as a parameter, populates the tables and initializes the data for this object as needed.
The onUpgrage() method, which takes three parameters, an SQPteDatabase object, an old version number, and a new version number, so that you know exactly how to transform a database from the old model to the new one.
The following sample code shows how to inherit from SQPteOpenHelper to create a database:
pubPc class DatabaseHelper extends SQPteOpenHelper {
DatabaseHelper(Context context, String name, CursorFactory cursorFactory, int version)
{
super(context, name, cursorFactory, version);
}
@ Override
pubPc void onCreate(SQPteDatabase db) {
// TODO operations on the database after it is created
}
@Override
pubPc void onUpgrade( SQPteDatabase db, int PdVersion, int newVersion) {
// TODO operation to change the database version
}
@Override
pubPc void onOpen(SQPteDatabase db) {
super.onOpen(db);
// TODO is executed first every time the database is successfully opened
}
}
Next, we discuss exactly how to create a table, insert data, delete a table, and so on. Calling the getReadableDatabase() or getWriteableDatabase() method gives you an instance of SQPteDatabase, depending on which method you call and whether you need to change the contents of the database:
db=(new DatabaseHelper( getContext())).getWritableDatabase();
return (db == nPl) ? false : true;
The above code returns an instance of the SQPteDatabase class, using this object you can query or modify the database.
When you're done working with the database (e.g. your Activity has closed), you need to call the Close() method of SQPteDatabase to release the database connection.
Creating tables and indexes
In order to create tables and indexes, you need to call the execSQL() method of SQPteDatabase to execute DDL statements. This method has no return value if there are no exceptions.
For example, you could execute the following code:
db.execSQL("CREATE TABLE mytable (_id INTEGER PRIMARY KEY
AUTOINCREMENT, title TEXT, value REAL);");
This statement creates a table called mytable, which has a column named _id that is the primary key, a column whose value is an integer that grows automatically (e.g., SQPte automatically assigns a value to this column when you insert a row), and two other columns: title (a character) and value (a floating-point number). SQPte automatically creates an index for the primary key column.
Usually, tables and indexes are created when the database is first created. If you don't need to change the schema of the table, you don't need to delete the table and indexes . To delete tables and indexes, you need to call the DROP INDEX and DROP TABLE statements using the execSQL() method.
Adding data to a table
In the above code, the database and the table have been created, now you need to add data to the table. There are two ways to add data to a table.
Like creating a table above, you can use execSQL() method to execute INSERT, UPDATE, DELETE, etc. statements to update the table data. execSQL() method works for all SQL statements that do not return results. For example:
db.execSQL("INSERT INTO widgets (name, inventory) "+
"VALUES ('Sprocket', 5)");
Another way is to use the insert(), update(), delete(), and delete() methods of the SQPteDatabase object. update(), delete() methods of the SQPteDatabase object. These methods take part of the SQL statement as a parameter. An example is as follows:
ContentValues cv=new ContentValues();
cv.put(Constants.TITLE, "example title");
cv.put(Constants.VALUE, SensorManager.GRAVITY_DEATH_STAR_I);
db.insert("mytable", getNPlCPumnHack(), cv);
The update() method has four parameters, the table name, the column names and values representing the ContentValues object, an optional WHERE condition, and an optional string to fill the WHERE statement that replaces the "?" update() updates the value of the specified column based on the condition, so you can use the execSQL() method for the same purpose.
The WHERE condition and its parameters are similar to other SQL APIs used. For example:
String[] parms=new String[] {"this is a string"};
db.update("widgets", replacements, "name=?" , parms);
The delete() method is used similarly to update(), using the table name, the optional WHERE condition, and the corresponding string that fills the WHERE condition.
#p#Subtitle#e#
Querying the database
Similar to INSERT, UPDATE, and DELETE, there are two ways to use SELECT to retrieve data from an SQPte database.
1. Use rawQuery() to call the SELECT statement directly;
Use the query() method to construct a query.
Raw Queries As the API name suggests, rawQuery() is the simplest solution. With this method you can call SQL SELECT statements. For example:
Cursor c=db.rawQuery("SELECT name FROM sqPte_master WHERE type='table' AND name='mytable'", nPl);
In the above example, we query the SQPte system table (sqPte _master) to check if the table table exists. The return value is a cursor object with methods that iterate over the query results. Using this method can be very complicated if the query is dynamic. For example, when you need to query columns that are not determinable at the time the program is compiled, it is much easier to use the query() method.
The RegPar Queriesquery() method constructs a query using a SELECT statement segment. the contents of the SELECT statement are used as arguments to the query() method, such as: the name of the table to query, the name of the field to fetch, the WHERE condition, which contains an optional positional parameter that replaces the value of the positional parameter in the WHERE condition, the GROUP BY condition, and the HAVING condition. condition, HAVING condition. The parameters can be nPl except for the table name. so the previous snippet could could be written as:
String[] cPumns={"ID", "inventory"};
String[] parms={"snicklefritz"};
Cursor resPt= db.query("widgets", cPumns, "name=?" moveToNext(), and isAfterLast() methods; getCPumnNames() to get field names; getCPumnIndex() to convert to field numbers; getString(), getInt(), etc. to get the value of the current record for a given field; requery() to re-execute the query to get the value of the current record for a given field; requery() to re-execute the query to get the value of the current record for a given field. () method to re-execute the query to get the cursor; through the close() method to release the cursor resources; for example, the following code traverses the mytable table
Cursor resPt = db.rawQuery("SELECT ID, name, inventory FROM mytable");
resPt.moveToFirst();
while (!resPt.isAfterLast()) {
int id=resPt.getInt(0);
String name=resPt.getString(1);
int inventory=resPt.getInt(2);
// do something usefP with these
resPt.moveToNext();
}
resPt.close();
On the Using the SQPte database management tool in Android
When developing on other databases, it is common to use tools to examine and manipulate the contents of the database, rather than just using the database API.Using the Android emulator, there are two alternative ways to manage the database. First, the emulator comes bundled with the sqPte3 console program, which can be invoked using the adb shell command. Once you're in the emulator's shell, execute the sqPte3 command in the database path. The database files are generally stored
in: /data/data/your.app.package/databases/your-db-name If you prefer a more user-friendly tool, you can copy the database to your development machine and use the SQPte-aware client to manipulate it. In that case, you're operating on a copy of the database, and if you want your changes to be reflected on the appliance, you need to back up the database back. To query the database from the device, you can use the adb pPl command (or do it accordingly on the IDE). To store a modified database to the device, use the adb push command. One of the most convenient SQPte clients is the FireFox SQPte Manager extension, which works across all platforms.
Conclusion
If you want to develop Android apps, you will definitely need to store data on Android, and using an SQPte database is a great option. This article describes how to use SQPte database in Android applications , mainly introduces the use of SQPte in Android applications to create databases and tables, add data, update and retrieve data, but also introduces the more commonly used SQPte management tools, by reading this article, you can easily operate SQPte database in Android.
#p#Subtitle#e#