SQLite Library

scacchicgardenΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 7 μήνες)

185 εμφανίσεις

SQLite

Supported by BlackBerry OS 5.0

Using SQLite

http://cmer.cis.uoguelph.ca

2

Overview


SQLite Library


SQLite Database


Database Security Options


A Statement Life Cycle


Transactions


Remove Databases


Vacuum Command


Best Parctice


http://cmer.cis.uoguelph.ca

3

SQLite Library


It is a relational database library.


Has a small footprint. Hence, it is good for
small devices.


BlackBerry devices that run BlackBerry Device
Software version 5.0 or later supports SQLite
library.


SQLite library has been introduced in
BlackBerry Java Development Environment
Version 5 and later.


http://cmer.cis.uoguelph.ca

4

SQLite Library (Cont.)


The package that provides classes and
interfaces to work with SQLite is
“net.rim.device.api.database”.


DB locations can be :


SD Card: /SDCard/


Device memory: /store/home/user


System memory: /system/


Default location:
/SDCard/databases/<application_name>



http://cmer.cis.uoguelph.ca

5

SQLite Database


SQLite database is stored in a single file on a
SD Card.


The default location for a SQLite database is
/SDCard/databases/<application_name>


You can store the database file in the device
memory by specifying the file system path


It can be encrypted or plain text.

http://cmer.cis.uoguelph.ca

6

How to Create a Plain
Database


Import
net.rim.device.api.database
that include
classes to work with SQLite.


Create a URI representing the database file


dbURI =
URI.create(“file:///SDCard/Databases/myApplication/”
+ “MyDatabase.db”);


Invoke the Create() method of the
DatabaseFactory class to create a new database


Database d = DatabaseFactory.create(dbURI);



http://cmer.cis.uoguelph.ca

7

How to Create a Plain
Database


-
or



Invoke openorCreate() to create a new
database or open an existing one.



Database db = DatabaseFactory.openOrCreate(uri);



You can also create an encrypted database by
specifying security options in Create() or
openOrCreate() method.



http://cmer.cis.uoguelph.ca

8

How to Create a Plain
Database (Cont.)


DatabaseFactory:


Creates new or open existing instances of a device
database


Databases can be created temporary (in
-
memory)
or persistent depending on the URI path


URI path can be one of the DB locations presented
in the previous slides.



Example: create a database in device memory:


URI path: /store/home/user


Create a database in system memory:


URI path:


/system/



http://cmer.cis.uoguelph.ca

9

Example
-

Open or Create
a Database


public void createPlainDatabaseFile() throws Exception {


// The database is created in SD card


String dbLocation = "/SDCard/databases/myApplication/";


//if you want to create the database in flash memory use the following URI


// dbLocation = "/store/home/user/";



//Create a URI path file for the database


URI uri = URI.create(dbLocation + “myDB.db”);


// Invoke the create() or openOrCreate() method


Database db = DatabaseFactory.openOrCreate(uri, new
DatabaseSecurityOptions(false))


}

http://cmer.cis.uoguelph.ca

10

Encrypted Databases


A database can be encrypted by specifying the
security options in Creat() or openOrCreate()
method:


DatabaseFactory.create(URI fileURI,
DatabaseSecurityOptions securityOptions);


DatabaseFactory.openOrCreate(URI fileURI,
DatabaseSecurityOptions securityOptions);






http://cmer.cis.uoguelph.ca

11

Encrypted Databases
(Cont.)


When a database is encrypted, it is linked to
the device in which it is created. So, it does
not support portability between databases.


In order to transfer the encrypted database to
the other device, you have to decrypt it first.


An encrypted database should be closed as
soon as possible, otherwise, it is susceptible
to “cold boot attack”.

http://cmer.cis.uoguelph.ca

12

DatabaseSecurity
Options


DatabaseSecurityOptions defines the
security options for a database.


There are three choices available:


Not encrypted, accessible from any application


Encrypted, accessible from any application


Encrypted and protected, accessible only from
applications that are signed with code signed key

http://cmer.cis.uoguelph.ca

13

DatabaseSecurity
Options (Cont.)


If two applications are signed with the same
key, then both applications have access to the
database.


Methods:


Public DatabaseSecurityOptions(boolean

encrypted)


If encrypted is true, it means that the database is encrypted and not
portable


Public DatabaseSecurityOptions(CodeSigningKey

key)


public DatabaseSecurityOptions(int

signerId)



http://cmer.cis.uoguelph.ca

14

Example
-

Encrypted
Database


It opens or creates an encrypted database file. It
is not portable between devices.


public void createEncryptedDatabaseFile() throws Exception {


// The database is created in SD card


String dbLocation = "/SDCard/databases/myApplication/";


//Create a URI path file for the database


URI uri = URI.create(dbLocation + “myDB.db”);


//Specify security options to encrypt the database


DatabaseSecurityOptions dso = new DatabaseSecurityOptions(true);


// Invoke the create() or openOrCreate() method


Database db = DatabaseFactory.openOrCreate(uri, dso);


}


http://cmer.cis.uoguelph.ca

15

Database Class


Database class allows to create, delete and
execute SQL statements


In order to create a SQL statement, invoke
createStatement() method of Database class.


Statement createStatement(String sqlStatement)


Creates a Statement in this database.



The argument can contain multiple SQL
Statements delimited by ‘;’


http://cmer.cis.uoguelph.ca

16

A statement lifecycle


It represents a SQL statement.


The lifecycle of a statement is:


Create a statement


Prepare statement


Bind


Query Execution or


Update Execution



http://cmer.cis.uoguelph.ca

17

Example
-

Create a Table

Public void createTable() throws Exception{


String dbLocation = "/SDCard/databases/myApplication/";


//Create a URI path file for the database


URI uri = URI.create(dbLocation + “myDB.db”);


Database db = DatabaseFactory.open(uri);


//Create a statement


Statement st = db.createStatement( “CREATE TABLE ‘Student’ ( ”
+ “ ‘Name’ TEXT,” + “ ‘ID’ INTEGER )”);


st.prepare();


st.execute();

}


http://cmer.cis.uoguelph.ca

18

Bind() Method


If the SQL statement has parameters, you
have to invoke bind() method to bind the
parameter to a value.


Statement.bind() has two arguments: the first
argument is the parameter number and the
second parameter is the value to bound to it.


The occurance of ? in a statement means
parameters are numbered sequentially.


http://cmer.cis.uoguelph.ca

19

Bind() Method (Cont.)


The occurance ?NN in a SQL statement means
that each parameter is numbered as integer
NN.


Example:

Statement s = “SELECT * FROM Employee WHERE
salary < ?2 AND salary > ?10”;


Means that:


bind(2, 80000);

// means salary <80000


bind(10, 50000);

// means salary >50000

http://cmer.cis.uoguelph.ca

20

Example
-
Bind() method

Public void insertDataIntoTable() throws Exception{


String dbLocation =
"/SDCard/databases/myApplication/";


URI uri = URI.create(dbLocation + “myDB.db”);


Database db = DatabaseFactory.open(uri);


Statement st = db.createStatement( “
INSERT INTO Employee
VALUES(?, ?, ?)”);


st.prepare();


st.bind(1, employeeID);


st.bind(2, name);


st.bind(3, salary);


st.execute();

}

http://cmer.cis.uoguelph.ca

21

Query Execution


Query execution is done using getCursor()
method.


getCursor() executes only SELECT queries and
returns cursor with selected rows .


The returned cursor is positioned before the
first row. Hence, to get the current row, invoke
Cursor.first() or Cursor.next() method.


The method’s signature: Cursor getCursor()




http://cmer.cis.uoguelph.ca

22

Cursor Class


Cursor provides read
-
only access to results
returned by getCursor() method


It is an iterator. It provides one
-
way forward
-
only navigation method.


Methods:


getRow(): returns current row with values


next(): move the cursor to the next row


prev(): move cursor to the previous row


isEmpty(): determines whether cursor has rows


close(): closes cursor object

http://cmer.cis.uoguelph.ca

23

Example
-

Cursor Class

Public void selectDataTable() throws Exception{


URI uri = URI.create("
/SDCard/databases/myApplication/"
+ “
myDB.db
”);


Database db = DatabaseFactory.open(uri);


Statement st = db.createStatement( “SELECT * FROM Employee
”);


st.prepare();


Cursor cursor = st.getCursor();


while(cursor.next()) {



row = cursor.getRow();



id = row.getInteger(0);


name = row.getString(1);



salary = tow.getInteger(2);


}


st.close();


cursor.close();

}

http://cmer.cis.uoguelph.ca

24

Update Execution


To update execution, invoke
execute()
method


Statement.execute() executes an update
statement. It does not return a result set.


If the query statement is NOT SELECT type,
invoke this method. You can use for any other
SQL statement like INSERT, DELETE, CREATE,
UPDATE and so on.


http://cmer.cis.uoguelph.ca

25

Example


Execute()
method

Public void deleteTable() throws Exception{


String dbLocation = "/SDCard/databases/myApplication/";


URI uri = URI.create(dbLocation + “myDB.db”);


Database db = DatabaseFactory.open(uri);


Statement st = db.createStatement( “DELETE Employee”);


st.prepare();


st.execute();

}


http://cmer.cis.uoguelph.ca

26

Reset() method


Statement. reset() method resets the statement
to its state after prepare() method.


It also clears all bindings.


After invoking reset() method, execute() must
be called to het a new cursor.


So, if several SQL statements are run, invoke
reset() method after each execute() mthod to
clear bindings and clear the statement state.


http://cmer.cis.uoguelph.ca

27

Example
-

reset() Method

Public void insertDataTable() throws Exception{


URI uri = URI.create("/SDCard/databases/myApplication/" + “myDB.db”);


Database db = DatabaseFactory.open(uri);


Statement st = db.createStatement(“
INSERT INTO Employee VALUES(?, ?)
”);


st.prepare();


while(count <10) {



st.bind(1,count);



st.bind(2, employee[count]);



st.execute();



st.reset();



count++;


}


st.close();

}


http://cmer.cis.uoguelph.ca

28

Transactions


In order to execute multiple statements in one
transaction, two methods should be called:


beginTransaction():


Starts a new transaction. So, all of the following statement
executions can be committed or rolled back.


commitTransaction()


Commits current transaction starting with the
beginTransaction(). Before committing the transaction, all
the cursors started within this transaction should be
closed otherwise, the transaction is failed.


Nested transactions are not supported.

http://cmer.cis.uoguelph.ca

29

Example
-

Transaction

public void transactionStatement() {


URI uri = URI.create("/SDCard/databases/myApplication/" + “myDB.db”);


Database db = DatabaseFactory.open(uri);


db.beginTransaction();


Statement st = db.createStatement(
“INSERT INTO Employee VALUES(1, ‘Bob’
)
”);


st.prepare();


st.execute();


st.reset();


Statement st = db.createStatement(
“INSERT INTO Employee VALUES(2, ‘John’
)
”);


st.prepare();


st.execute();


db.commitTransaction();

}





http://cmer.cis.uoguelph.ca

30

Delete a Database


Invoke DatabaseFactory.delete() to remove an
exisiting database from the device.


The method’s signature:

public static void delete( URI fileURI)


Example:




URI uri = URI.create("/SDCard/databases/myApplication/" + “myDB.db”);


//Remove the database from the device


DatabaseFactory.delete(uri);



http://cmer.cis.uoguelph.ca

31

Vacuum Command


Database is stored as a file. Some operations
such as dropping a table, inserting or deleting
data, cause the file to be fragmented.


Vacuum command is used to defragment and
reduce the size of the database file.


To defragment the file, Vacuum command
copies all pieces of the file into the memory
and creates a new database file.

http://cmer.cis.uoguelph.ca

32

Vacuum Command
(Cont.)


Because Vacuum copies all information related
to the database into the memory, it needs
enough memory.


If enough memory is not available, the Vacuum
command is failed.

http://cmer.cis.uoguelph.ca

33

Best Practices


In order to have a better performance when
developing a database application, consider
the following:


Use local variables


Use primitive types instead of classes


Use static variables instead of Strings


Use efficient algorithms


Write efficient loops


Avoid Java.util.Enumeration



http://cmer.cis.uoguelph.ca

34

Best Practices (Cont.)


Make classes
final



Using final keyword means that it would never be
extended.


Use
int

instead of
long


long is a 64
-
bit integer


Avoid unnecessary field initialization


Explicitly initialize local variables


Use temporary tables as much as possible


It takes less recourses.


http://cmer.cis.uoguelph.ca

35

Best Practices (Cont.)


Store data as little as possible


The search is faster.


Avoid subqueries


Because the result is stored in a temporary file


Use static inner class


Reduce the number of references


http://cmer.cis.uoguelph.ca

36

References

1.
BlackBerry Java Application SQLite Version. 5.0 (Development Guide)


Available online at http://www.blackberry.com/developers