Transcript SQLite

Cosc 5/4730
Android and Blackberry
SQLite
SQLite
• For the sql language syntax , please see SQlite
documentation
– http://www.sqlite.org
• Both Android and Blackberry implement the
SQLite package
– But very differently.
SQLite
BLACKBERRY
Blackberry SQLite
• net.rim.device.api.database.*
– accessible only by signed applications
• For the purposes of the class, you don’t need a signing
keys. But if you plan to use an on the phone.
» Category: Signed: This element is only accessible by
signed applications. If you intend to use this element,
please visit http://www.blackberry.com/go/codesigning
to obtain a set of code signing keys. Code signing is only
required for applications running on BlackBerry
smartphones
– development on BlackBerry Smartphone
Simulators can occur without code signing.
SQLite Database files
• Each SQLite® database is stored in a single file.
– If you only specify the database name as the
parameter value to DatabaseFactory.create() , the
database file is created on the SD card of the
device.
– The default location for the database file is
/SDCard/databases/<application_name>/ . The
name of the application that creates the database
is included in the path to avoid name collisions.
Opening/create a DB
• Uses DatabaseFactory
– open() //opens db, throws exception if fails
– create() //creates db, but if fails throws exception
– examples:
Database d;
//using default location on the SDcard
d = DatabaseFactory.create("myDB.db");
d = DatabaseFactory.open("myDB.db");
Opening/create a DB
• Throws the following exceptions
• DatabasePathException
– Bad path or SDCard not there.
• OS 5 Storm and Bold and 6+ can use internal
• ControlledAccessException
– on access restriction
• DatabaseIOException
– Can’t open it generally.
Statement interface
1. Creation: Statement =
Database.createStatement(String)
2. Preparation: via Statement.prepare()
3. Bind: of any formal parameters in the statement
to actuals via Statement.bind(int, int) and
associated methods, starting from 1 for the first
formal.
4. Query Execution: via Statement.getCursor(),
which returns a Cursor
5. Update Execution: via Statement.execute()
Statements and cursor
• Use the statement, we then use SQLite sql
statements to work on the database.
• For select statements, it returns a cursor
• From the cursors, we get Row object for each
row of data selected.
example
• Create Db and add a data
d = DatabaseFactory.create("myDB.db");
Statement st;
st = d.createStatement( "CREATE TABLE 'HighScore'
('Name' TEXT, 'Score' INTEGER )" );
st.prepare();
st.execute();
st.close();
//add some data
st = d.createStatement("INSERT INTO HighScore(Name,Score) VALUES
('Jim',3012)");
st.prepare();
st.execute();
st.close();
Example (2)
• Read back the data
st = d.createStatement("SELECT Name,Score FROM
HighScore");
st.prepare();
net.rim.device.api.database.Cursor c =
st.getCursor();
Row r;
while(c.next()) {
r = c.getRow();
System.out.println("Row "+i+ "is
"+r.getString(0)+" "+r.getInteger(1));
}
c.close();
Where 0 is Name and 1 is Score column.
st.close()
Closing the database
• Don’t forget to close the database when you
are done.
try {
d.close(); //where d is the database.
} catch (DatabaseIOException e) {
//for some reason the db can’t be closed.
}
finally
• Don’t forgot to close everything
– Cursor, statements and database
– It releases all the resources that they are holding.
• Some more code samples on-line :
– http://docs.blackberry.com/en/developers/deliver
ables/8682/Create_a_SQLite_database_761831_1
1.jsp
A Note
• There is a RecordStore that can be used
instead of SQLite
– works on the rest of the phones
– Also, not a signed API either.
SQLite
ANDROID
SQLite and Android
• The similarity ends at the name between
blackberry and android.
– It SQLite, but handled very differently.
• Package
– android.database.sqlite
• Normally the SQLite is implemented in a
separate class from the Activity class.
SQL language
• Android sqlite uses sql, but the methods may break up
the statement
– example String WhereClause and String[] WhereArgs
• sql and android query examples
– select Name, Score from HighScore where name=‘jim’;
• db.query(true, DATABASE_TABLE, new String[] {KEY_NAME,
KEY_SCORE, }, KEY_NAME + "=\'" + VarName+"\'",, null, null, null,
null, null);
– NOTE the single quotes, which are required for any Text (android term),
varchar, or string fields in the table
– Select Name,Score from HighScore;
• db.query(true, DATABASE_TABLE, new String[] {KEY_NAME,
KEY_SCORE}, null, null, null, null, null, null);
– The red null so there is no WHERE CLAUSE.
DB class
• First you need to create
a class to handle the
database.
– In my case,
DBAdapter. I'm also
using a number of
constants to simplify
typing errors
• let the compiler
find my typing
errors.
public class DBAdapter {
public static final String KEY_NAME = "Name";
public static final String KEY_SCORE = "Score";
private static final String TAG = "DBAdapter";
private static final String DATABASE_NAME = "myDB";
private static final String DATABASE_TABLE = "HighScore";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_CREATE =
"CREATE TABLE HighScore (Name TEXT, Score INTEGER );";
private final Context context;
private DatabaseHelper DBHelper;
private SQLiteDatabase db;
public DBAdapter(Context ctx) {
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}
Extend SQLiteOpenHelper
• A helper class to manage database creation and
version management.
– You create a subclass implementing
onCreate(SQLiteDatabase),
onUpgrade(SQLiteDatabase, int, int) and optionally
onOpen(SQLiteDatabase)
– This class takes care of opening the database if it
exists, creating it if it does not, and upgrading it as
necessary.
– Transactions are used to make sure the database is
always in a sensible state.
DB class (2)
• subclass in DBAdapter
private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
//only called when the database is created!
db.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
onCreate(db);
}
}
Now our stuff
• Add a few classes in DBAdapter to do the heavy lifting.
// ---opens the database--public DBAdapter open() throws SQLException {
db = DBHelper.getWritableDatabase();
return this;
}
// ---closes the database--public void close() {
DBHelper.close();
db.close();
}
SQLiteDatabase methods
• Some of the common methods
– void execSQL(String sql)
• Execute a single SQL statement that is not a query.
– long insert(String table, String nullColumnHack, ContentValues
values)
• method for inserting a row into the database.
– long insertOrThrow(String table, String nullColumnHack,
ContentValues values)
• method for inserting a row into the database or throws a
SQLException
– Cursor query(String table, String[] columns, String selection,
String[] selectionArgs, String groupBy, String having, String
orderBy)
• Query the given table, returning a Cursor over the result set.
• There are several query methods that can be used.
SQLiteDatabase methods (2)
– int delete(String table, String whereClause, String[]
whereArgs)
• method for deleting rows in the database.
– public Cursor rawQuery (String sql, String[]
selectionArgs)
• Runs the provided SQL and returns a Cursor over the result
set.
• Sql is the SQL query. The SQL string must not be ; terminated
• selectionArgs You may include ?s in where clause in the
query, which will be replaced by the values from
selectionArgs. The values will be bound as Strings.
– Maybe null.
Now our stuff (2)
// ---insert into the database--public long insertName(String name, Integer value) {
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_NAME, name);
initialValues.put(KEY_SCORE, value);
return db.insert(DATABASE_TABLE, null, initialValues);
}
// ---retrieves all rows--public Cursor getNames() {
Cursor c = db.query(DATABASE_TABLE, new String[] {KEY_NAME, KEY_SCORE},
null, null, null, null, KEY_NAME);
if (c != null ) //make sure db is not empty!
c.moveToFirst();
return c;
}
Now our stuff (3)
public Cursor getRow(long rowId) throws SQLException {
Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] { KEY_ROWID, KEY_NAME, KEY_IP,
KEY_OWNER }, KEY_ROWID + "="+ rowId, null, null, null, null, null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
// ---updates a row--public boolean updateRow(String name, int score) {
ContentValues args = new ContentValues();
args.put(KEY_SCORE, score);
//returns true if one or more updates happened, otherwise false.
return db.update(DATABASE_TABLE, args, KEY_NAME + "= \'" + name+"\'", null) > 0;
}
Now our stuff (4)
// ---deletes a particular row--public boolean deleteRow(long rowId) {
return db.delete(DATABASE_TABLE, KEY_ROWID + "=" +
rowId, null) > 0;
}
//Delete all rows with name
public boolean deleteName(String name) {
return db.delete(DATABASE_TABLE, KEY_NAME + "= \'"
+ name+"\'", null) > 0;
}
The Activity class.
• create an activity class and add
DBAdatper db = new DBAdapter(this);
• Access and use the DB.
db.open();
id = db.insertName("Jim", 3012);
• note if (id == -1) then it failed to insert
//whatever else you want to do.
db.close();
Cursor notes
• The queries all return Cursor, so you need to
pull the information out of it.
– In my example, there are 2 columns in the db (and
both are requested)
Cursor c = db.getNames();
String str = “Name: " + c.getString(0); //name
str += " Score:" + c.getInt(1); //score
Cursor notes (2)
• The Score column is actually an integer, so I could use .getInt(1) as
well.
• Cursor has a number of useful methods
• double getDouble(int columnIndex), float getFloat(int
columnIndex), int getInt(int columnIndex), long getLong(int
columnIndex), short getShort(int columnIndex), String getString(int
columnIndex)
• String[] getColumnNames()
– Returns a string array holding the names of all of the columns in the
result set in the order in which they were listed in the result.
• int getCount()
– Returns the numbers of rows in the cursor.
• int getColumnCount()
– Return total number of columns
Example with Cursor
public void delRowbyName(String name) {
db.open();
Cursor c = db.getNames();
for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
//0 name, 1 score
label1.setText(c.getString(0) + " " + c.getInt(1));
}
db.close();
}
Debugging a "stopped unexpectedly"
• bring up the ddms
– ddms.bat or eclipse
ddms
– There will you give a way
to debug your app,
including what caused
– Check the log for Errors
(in red)
debugging the database
• The system stores databases in the
/data/data/package_name/databases folder by default.
• In a command line using the adb (Android Debug Bridge found in the android sdk tools library) you can access the
databases on a running emulator like below
– adb -s emulator-5554 shell
• assuming 5554 is your emulator
– sqlite3 /data/data/package_name/databases/database_name
• After this you can type normal SQL commands to test the
content. For example:
– SELECT * FROM table_name;
• The output format is pretty ugly, but workable .
References
• Creating an using Databases with android
– http://www.devx.com/wireless/Article/40842
• Android Tutorials
– http://www.helloandroid.com/tutorials
– http://www.helloandroid.com/ Some good general
help
• Android developer site
– http://developer.android.com/intl/zhCN/reference/android/database/sqlite/packagesummary.html
Q&A