Android初级教程十一——SQLite

zhufeng518

　　Android采用标准的SQLite数据库，常用的类如下：
　　1、SQLiteOpenHelper
　　2、SQLiteDataBase
　　3、SQLiteCursor
　　4、ContentValues
　　

　　一般存放路径： /data/data/com.microjobsinc.mjandroid/databases/MicroJobs.
　　可以用adb pull 命令从模拟器上取得数据库到本机上，通过SQLite3.exe运行相关命令。
　　创建数据库
　　

　　MicroJobsDatabase.java:
String[] sql =mContext.getString(R.string.MicroJobsDatabase_onCreate).split("\n");
　　

　　strings.xml:
<string name="MicroJobsDatabase_onCreate">"
CREATE TABLE jobs (_id INTEGER PRIMARY KEY AUTOINCREMENT, employer_id INTEGER,
title TEXT, description TEXT, start_time INTEGER, end_time INTEGER,
status INTEGER);
CREATE TABLE employers( _id INTEGER, employer_name TEXT, ...
CREATE TABLE workers( _id INTEGER PRIMARY KEY AUTOINCREMENT, ...
CREATE TABLE status( _id INTEGER PRIMARY KEY AUTOINCREMENT, ...
INSERT INTO status (_id , status) VALUES (NULL, 'Filled');
INSERT INTO status (_id , status) VALUES (NULL, 'Applied For');
INSERT INTO status (_id , status) VALUES (NULL, 'Open');
...
　　"</string>
　　  
onUpdate
　　<string name="MicroJobsDatabase_onUpgrade">
　　"DROP TABLE IF EXISTS jobs
DROP TABLE IF EXISTS employers
　　DROP TABLE IF EXISTS status"
　　</string>   

package com.microjobsinc.mjandroid;
import ...
/**
* Provides access to the MicroJobs database. Since this is not a Content Provider,
* no other applications will have access to the database.
*/
public class MicroJobsDatabase extends SQLiteOpenHelper {
/** The name of the database file on the file system */
private static final String DATABASE_NAME = "MicroJobs";
/** The version of the database that this class understands. */
private static final int DATABASE_VERSION = 1;
/** Keep track of context so that we can load SQL from string resources */
private final Context mContext;
/** Constructor */
public MicroJobsDatabase(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.mContext = context;
}
/** Called when it is time to create the database */
@Override
public void onCreate(SQLiteDatabase db) {
String[] sql =
mContext.getString(R.string.MicroJobsDatabase_onCreate).split("\n");
db.beginTransaction();
try {
// Create tables and test data
execMultipleSQL(db, sql);
db.setTransactionSuccessful();
} catch (SQLException e) {
Log.e("Error creating tables and debug data", e.toString());
Databases  |  105      throw e;
} finally {
db.endTransaction();
}
}
/** Called when the database must be upgraded */
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(MicroJobs.LOG_TAG, "Upgrading database from version " + oldVersion +
" to " +
newVersion + ", which will destroy all old data");
String[] sql =
mContext.getString(R.string.MicroJobsDatabase_onUpgrade).split("\n");
db.beginTransaction();
try {
execMultipleSQL(db, sql);
db.setTransactionSuccessful();
} catch (SQLException e) {
Log.e("Error upgrading tables and debug data", e.toString());
throw e;
} finally {
db.endTransaction();
}
// This is cheating. In the real world, you'll need to add columns, not
rebuild from scratch.
onCreate(db);
}
/**
* Execute all of the SQL statements in the String[] array
* @param db The database on which to execute the statements
* @param sql An array of SQL statements to execute
*/
private void execMultipleSQL(SQLiteDatabase db, String[] sql){
for( String s : sql )
if (s.trim().length()>0)
db.execSQL(s);
}
}

public class MicroJobsDatabase extends SQLiteOpenHelper {
/*
* @param sortBy the sort criteria
*/
public JobsCursor getJobs(JobsCursor.SortBy sortBy) {
String sql = JobsCursor.QUERY + sortBy.toString();
SQLiteDatabase d = getReadableDatabase();
JobsCursor c = (JobsCursor) d.rawQueryWithFactory(
new JobsCursor.Factory(),
sql,
null,
null);
c.moveToFirst();
return c;
}
...
public static class JobsCursor extends SQLiteCursor{
public static enum SortBy{
title,
employer_name
}
private static final String QUERY =
"SELECT jobs._id, title, employer_name, latitude, longitude, status "+
"FROM jobs, employers "+
"WHERE jobs.employer_id = employers._id "+
"ORDER BY ";
private JobsCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
String editTable, SQLiteQuery query) {
super(db, driver, editTable, query);
}
private static class Factory implements SQLiteDatabase.CursorFactory{
@Override
public Cursor newCursor(SQLiteDatabase db,
SQLiteCursorDriver driver, String editTable,
SQLiteQuery query) {
return new JobsCursor(db, driver, editTable, query);
}
}
public long getColJobsId()
{return getLong(getColumnIndexOrThrow("jobs._id"));}
public String getColTitle()
{return getString(getColumnIndexOrThrow("title"));}
public String
getColEmployerName()
{return getString(getColumnIndexOrThrow("employer_name"));}
public long getColLatitude()
{return getLong(getColumnIndexOrThrow("latitude"));}
public long getColLongitude()
{return getLong(getColumnIndexOrThrow("longitude"));}
public long getColStatus(){return getLong(getColumnIndexOrThrow("status"));}
}
Here are some of the highlights of the code:
Function that fashions a query based on the user’s requested sort column (the
sortBy parameter) and returns results as a cursor.
108  |  Chapter 8: Persistent Data Storage: SQLite Databases and Content ProvidersCreates the query string. Most of the string is static (the QUERY variable), but this line
tacks on the sort column. Even though QUERY is private, it is still available to the
enclosing class. This is because the getJobs method and the the JobsCursor class are
both within the MicroJobsDatabase class, which makes JobsCursor’s private data
members available to the getJobs method.
To get the text for the sort column, we just run toString on the enumerated value
passed by the user. The enumeration is defined at item 8. We could have defined an
associative array, which would give us more flexibility in naming variables, but this
solution is simpler. Additionally, the names of the columns pop up quite nicely in
Eclipse’s autocompletion.
Retrieves a handle to the database.
Creates the JobsCursor cursor using the SQLiteDatabase object’s rawQueryWith
Factory method. This method lets us pass a factory method that Android will use
to create the exact type of cursor we need. If we had used the simpler rawQuery
method, we would get back a generic Cursor that lacked the special features of
JobsCursor.
As a convenience to the caller, moves to the first row in the result. This way, the
cursor is returned ready to use. A common mistake is forgetting the moveToFirst call
and then pulling your hair out trying to figure out why the Cursor object is throwing
exceptions.
The cursor is the return value.
Class that creates the cursor returned by getJobs.
Simple way to provide alternate sort criteria: store the names of columns in an
enum. This variable is used in item 2.
Constructor for the customized cursor. The final argument is the query passed by
the caller.
Factory class to create the cursor, embedded in the JobsCursor class.
Creates the cursor from the query passed by the caller.
Returns the cursor to the enclosing JobsCursor class.
Convenience functions that extract particular columns from the row under the cur-
sor. For instance, getColTitle returns the value of the title column in the row
currently referenced by the cursor. This separates the database implementation from
the calling code and makes that code easier to read.
A sample use of the database follows. The code gets a cursor, sorted by title, through
a call to getJobs. It then iterates through the jobs.
MicroJobsDatabase db = new MicroJobsDatabase(this);
JobsCursor cursor = db.getJobs(JobsCursor.SortBy.title);
Databases  |  109for( int rowNum=0; rowNum<cursor.getCount(); rowNum++){
cursor.moveToPosition(rowNum);
doSomethingWith(cursor.getColTitle());
}
Here are some of the highlights of the code:
Creates a MicroJobsDatabase object. The argument, this, represents the context, as
discussed previously.
Creates the JobsCursor cursor, referring to the SortBy enumeration discussed earlier.
Uses generic Cursor methods to iterate through the cursor.
Still within the loop, invokes one of the custom accessor methods provided by
JobsCursor to “do something” chosen by the user with the value of each row’s title
column.
Modifying the Database
Android Cursors are great when you want to read data from the database, but the
Cursors API does not provide methods for creating, updating, or deleting data. The
SQLiteDatabase class provides two basic interfaces that you can use for both reading
and writing:
·  A set of four methods called simply insert, query, update, and delete
·  A more general execSQL method that takes any SQL statement and runs it against
the database
We recommend using the first method when your operations fit its capabilities. We’ll
show you both ways using the MJAndroid operations.
Inserting data into the database
The SQL INSERT statement is used whenever you want to insert data into an SQL
database. The INSERT statement maps to the “create” operation of the CRUD
methodology.
In the MJAndroid application, the user can add jobs to the list by clicking on the Add
Job menu item when looking at the Jobs list. The user can then fill out a form to input
the employer, job title, and description. After the user clicks on the Add Job button on
the form, the following line of code is executed:
db.addJob(employer.id, txtTitle.getText().toString(),
txtDescription.getText().toString());
This code calls the addJob function, passing in the employer ID, the job title, and the
job description. The addJob function does the actual work of writing the job out to the
database.
Example 8-1 shows you how to use the insert method.
110  |  Chapter 8: Persistent Data Storage: SQLite Databases and Content ProvidersExample 8-1. Using the insert method
/**
* Add a new job to the database. The job will have a status of open.
* @param employer_id  The employer offering the job
* @param title     The job title
* @param description  The job description
*/
public void addJob(long employer_id, String title, String description){
ContentValues map = new ContentValues();
map.put("employer_id", employer_id);
map.put("title", title);
map.put("description", description);
try{
getWritableDatabase().insert("jobs", null, map);
} catch (SQLException e) {
Log.e("Error writing new job", e.toString());
}
}
Here are some of the highlights of the code in Example 8-1:
The ContentValues object is a map of column names to column values. Internally,
it’s implemented as a HashMap<String,Object>. However, unlike a simple HashMap,
ContentValues is strongly typed. You can specify the data type of each value stored
in a ContentValues container. When trying to pull values back out, ContentValues
will automatically convert values to the requested type if possible.
The second parameter to the insert method is nullColumnHack. It’s used only when
the third parameter, the map, is null and therefore the row would otherwise be
completely empty.
Example 8-2 shows you how to use the execSQL method.
Example 8-2. Using the execSQL method
/**
* Add a new job to the database. The job will have a status of open.
* @param employer_id  The employer offering the job
* @param title     The job title
* @param description  The job description
*/
public void addJob(long employer_id, String title, String description){
String sql =
"INSERT INTO jobs (_id, employer_id, title, description, start_time, end_time,
status) " +
"VALUES (     NULL, ?,     ?,   ?,     0,     0,    3)";
Object[] bindArgs = new Object[]{employer_id, title, description};
try{
getWritableDatabase().execSQL(sql, bindArgs);
} catch (SQLException e) {
Log.e("Error writing new job", e.toString());
}
}
Databases  |  111Here are some of the highlights of the code in Example 8-2:
First, we build a SQL string template named sql that contains bindable parameters
that will be filled in with user data. The bindable parameters are marked by a ques-
tion mark in the string. Next, we build an object array named bindArgs that contains
one object per element in our SQL template. There are three question marks in the
template, and therefore there must be three elements in the object array.
Executes the SQL command by passing the SQL template string and the bind argu-
ments to execSQL. Using a SQL template and bind arguments is much preferred over
building up the SQL statement, complete with parameters, into a String or
StringBuilder. By using a template with parameters, you protect your application
from SQL injection attacks. These attacks occur when a malicious user enters in-
formation into a form that is deliberately meant to modify the database in a way that
was not intended by the developer. This is normally done by ending the current SQL
command prematurely, using SQL syntax characters, and then adding new SQL
commands directly in the form field. The template-plus-parameters approach also
protects you from more run-of-the-mill errors, such as invalid characters in the
parameters.
Updating data already in the database
The MicroJobs application enables the user to edit a job by clicking on the job in the
Jobs list and choosing the Edit Job menu item. The user can then modify the strings
for employer, job title, and description in the editJob form. After the user clicks on the
Update button on the form, the following line of code is executed:
db.editJob((long)job_id, employer.id, txtTitle.getText().toString(),
txtDescription.getText().toString());
This code calls the editJob method, passing the job ID and the three items the user can
change: employer ID, job title, and job description. The editJob method does the actual
work of modifying the job in the database.
Example 8-3 shows you how to use the update method.
Example 8-3. Using the update method
/**
* Update a job in the database.
* @param job_id     The job id of the existing job
* @param employer_id  The employer offering the job
* @param title     The job title
* @param description  The job description
*/
public void editJob(long job_id, long employer_id, String title, String description) {
ContentValues map = new ContentValues();
map.put("employer_id", employer_id);
map.put("title", title);
map.put("description", description);
String[] whereArgs = new String[]{Long.toString(job_id)};
112  |  Chapter 8: Persistent Data Storage: SQLite Databases and Content Providers  try{
getWritableDatabase().update("jobs", map, "_id=?", whereArgs);
} catch (SQLException e) {
Log.e("Error writing new job", e.toString());
}
}
Here are some of the highlights of the code in Example 8-3:
The first parameter to update is the name of the table to manipulate. The second is
the map of column names to new values. The third is a small snippet of SQL; in this
case, it’s a SQL template with one parameter. The parameter is marked with a ques-
tion mark, and is filled out with the contents of the fourth argument.
Example 8-4 shows you how to use the execSQL method.
Example 8-4. Using the execSQL method
/**
* Update a job in the database.
* @param job_id     The job id of the existing job
* @param employer_id  The employer offering the job
* @param title     The job title
* @param description  The job description
*/
public void editJob(long job_id, long employer_id, String title, String description) {
String sql =
"UPDATE jobs " +
"SET employer_id = ?, "+
" title = ?, "+
" description = ? "+
"WHERE _id = ? ";
Object[] bindArgs = new Object[]{employer_id, title, description, job_id};
try{
getWritableDatabase().execSQL(sql, bindArgs);
} catch (SQLException e) {
Log.e("Error writing new job", e.toString());
}
}
For the application in Example 8-4, we show the simplest possible function. This makes
it easy to understand in a book, but is not enough for a real application. In a real
application, you would want to check input strings for invalid characters, verify that
the job exists before trying to update it, verify that the employer_id value is valid before
using it, do a better job of catching errors, etc. You would also probably authenticate
the user for any application that is shared by multiple people.
Deleting data in the database
The MicroJobs application enables the user to delete a job as well as create and change
it. From the main application interface, the user clicks on the List Jobs button to get a
list of jobs, and then clicks on a particular job to see the job detail. At this level, the
Databases  |  113user can click on the “Delete this job” menu item to delete the job. The application
asks the user if he really wants to delete the job. When the user hits the “Delete” button
in response, the following line of code in the MicroJobsDetail.java file is executed:
db.deleteJob(job_id);
This code calls the deleteJob method of the MicroJobsDatabase class, passing it the job
ID to delete. The code is similar to the functions we’ve already seen and lacks the same
real-world features.
Example 8-5 shows you how to use the delete method.
Example 8-5. Using the delete method
/**
* Delete a job from the database.
* @param job_id    The job id of the job to delete
*/
public void deleteJob(long job_id) {
String[] whereArgs = new String[]{Long.toString(job_id)};
try{
getWritableDatabase().delete("jobs", "_id=?", whereArgs);
} catch (SQLException e) {
Log.e("Error deleteing job", e.toString());
}
}
Example 8-6 shows you how to use the execSQL method.
Example 8-6. Using the execSQL method
/**
* Delete a job from the database.
* @param job_id    The job id of the job to delete
*/
public void deleteJob(long job_id) {
String sql = String.format(
"DELETE FROM jobs " +
"WHERE _id = '%d' ",
job_id);
try{
getWritableDatabase().execSQL(sql);
} catch (SQLException e) {
Log.e("Error deleteing job", e.toString());
}
}