SQL Lite - Computer Science and Engineering

bawltherapistSoftware and s/w Development

Dec 13, 2013 (3 years and 8 months ago)

95 views

CSCE
485
: MOBILE APPLICATIONS
DEVELOPMENT (USING ANDROID)


LECTURE
10

Mohamed
Shalan

CSCE
485

Topics

2


Dealing with Persistent Data


Android options


Internal Storage


External Storage


Shared Preferences


Structured data using
SQLite



Database


SQL


SQLite

& Android



Readings

3


Wei
-
Meng

Lee
Book: Ch
6


Android Documentation


Persistent Data


Most applications
require
you to save information
for
later use.


Depending on the requirements of your application,
you may need
to store
your data in a variety of
places
. Examples:


An application stores music/audio files in a specific
location so that other music players can find & access
them


An applications
may need to store
sensitive data
such
as encrypted username and password details
.

4

Storage Options


Shared preferences:


Shared
preferences are private data stored in
key value pairs.


Internal
storage:


Internal
storage is a location where you can
save files
on the device’s internal
storage (NAND Flash).


By
default, files stored in
internal storage
are private to your application, and
other applications
cannot access
them (neither can the user of the device).


When
the user
uninstalls the
application, the private files are removed.


External storage:


Every Android device supports shared external storage that you can use to store
files.


This can either be the removable storage such as a Secure Digital Card (SD
Card) or non
-
removable internal storage (NAND).

5

Storage Options


SQLite

database
:


Android
supports the full use of SQLite databases.


You
can create tables and perform SQL queries against the
tables accordingly
.


Local cache:


If you’d like to cache some data rather than store it persistently, the internal
data directory is where you should create the cache.


If you store data here and the system gets low on internal storage space,
Android may delete these files to reclaim space.


You should stay within a reasonable limit of space consumed of around
1
MB.


Network connection (Network/Remote Storage):


This
can be any remote data source
that you
have access to.


For
example, Flickr exposes an API that allows
you to
store images on its servers.
Your application could work with
Flickr to
store your images.

6

Permissions


Storing data anywhere on the device requires some
sort of permission from the user.


When users install applications from the Android
Market, the
application’s manifest
file is inspected for
required permissions that the application needs
to
operate
.


Anytime
your application needs access to sensitive
components
such as
external storage, access to the
Internet, phone device info, and so on,
the user
is
notified that the application would like to access these
components.


It is then
up to the user to decide whether she would
like to install the application.

7

Permissions


When you need to request permissions, you need to
add them to the
AndroidManifest.xml

file in your
project (can be done using the permission editor).


Internet:
android.permission.INTERNET


SD Card:
android.permission.WRITE_EXTERNAL_STORAGE



<manifest
xmlns:android
="
http://schemas.android.com/apk/res/android
"





package
="
com.android.app.myapp
" >





<uses
-
permission
android:name
="
android.permission.WRITE_EXTERNAL_STORAGE
"
/>





...

</manifest>

Internal Storage

9


To create and write a private file to the internal storage:


Call

openFileOutput
()

with the name of the file and the operating mode.


This returns a

FileOutputStream
.


Write to the file with

write()
.


Close the stream with

close().






To read a file from internal storage:


Call

openFileInput
()

and pass it the name of the file to read.


This returns a

FileInputStream
.


Read bytes from the file with

read()
.


Then close the stream with

close()
.


Better use try …. catch to handle file i/o exceptions


Import
java.io.FileInputStream

and
java.io.FileOutputStream


External Storage

10


Before you do any work with the external storage, you should always
call

getExternalStorageState
()

to check whether the media is
available.






To create your file use




Files read/write is similar to internal storage


Shared Preferences

11


Android provides the
SharedPreferences

object to help
you save simple application data.


For example, your application may have an option to allow users
to specify the font size of the text displayed in your application.


Using the
SharedPreferences

object, however, you save
the data you want through the use of key/value pairs


specify a key for the data you want to save, and then both
it and its value will be saved automatically to an XML file
for you.


You can use

SharedPreferences

to save any primitive data:
booleans
, floats,
ints
, longs, and strings.


This data will persist across user sessions (even if your
application is killed)

Shared Preferences

12


To use
SharedPreferences

import
android.content.SharedPreferences


Call
getSharedPreferences
()



Use this if you need multiple preferences files identified
by name, which you specify with the first parameter.


The information saved inside the
SharedPreferences

object is visible to all the activities within the same
application.


Call
getPreferences
()
with no name to keep them
private to the calling activity

Shared Preferences

13

SQLite


Serverless

SQL Database Engine


Source Code is Public Domain


Implements Most Features of SQL


Android API uses the built
-
in SQLite db.


Each database is private to the application.


In principle you could expose the data, if you expose the
application as a content provider.



14

Database

15


Data storage traditionally used individual,
unrelated files
, sometimes
called
flat files
.


In
the past,
each application
program in an organization used its
own file.


For
example,
In a university, each
department
might have
its own set
of files:


the
record office kept a
file about
the student information and their
grades,


the scheduling
office kept the name of the professors
and the
courses
they were teaching,


the
payroll
department kept
its own file about the whole staff and so
on.


Today, however
, all of these flat files can be combined in
a single
entity; the database for the whole university.

Database

16


A database is a collection of related, logically
coherent data used by the application programs in an
organization
.


Advantages of DB


Less
redundancy


Inconsistency avoidance


Efficiency


Data integrity


Security


Database


A database management system (DBMS) provides you with the
software tools you need to organize that data in a flexible
manner.


Tables comprise the fundamental building blocks of any
database.

17

Relational Database

18

Customer

HAS

Contract

1

m

ID

Name

ID

Amount

In the relational database management system

(RDBMS), the data is represented as a set of relations.

SQL


SQL: Structured Query Language


SQL is an ANSI (American National Standards
Institute) standard computer language for accessing
and manipulating database systems.


SQL is
declarative

rather than procedural language,
which
means that
users declare what they want
without having to write
a step
-
by
-
step procedure.


The SQL language was first implemented by the
Oracle Corporation in
1979

19

SQL

20


SQL statements
can be used
to retrieve and update data
in a database.


SQL query and update commands together form the Data
Manipulation Language (DML) part of SQL:


SELECT

-

extracts data from a database table


UPDATE

-

updates data in a database table


DELETE

-

deletes data from a database table


INSERT INTO

-

inserts new data into a database table


SQL Select


Syntax:


SELECT column FROM table


WHERE column operator value


Examples:


SELECT * FROM Persons


WHERE
FirstName
='
Tove
'




SELECT * FROM Persons


WHERE
FirstName

LIKE 'O%'




Operator

Description

=

Equal

<>

Not equal

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

BETWEEN

Between an inclusive range

LIKE

Search for a pattern

21

SQL DML


Insert


INSERT INTO
table_name

(column
1
, column
2
,...)


VALUES (value
1
, value
2
,....)


Update

UPDATE
table_name

SET
column_name

=
new_value

WHERE
column_name

=
some_value



Delete


DELETE FROM
table_name


WHERE
column_name

=
some_value




22

SQL DDL


The Data Definition Language (DDL) part of SQL
permits database tables to be created, altered or
deleted.


The most important DDL statements in SQL are:



CREATE TABLE

-

creates a new database table


ALTER TABLE
-

alters (changes) a database table


DROP TABLE

-

deletes a database table


CREATE INDEX

-

creates an index (search key)


DROP INDEX

-

deletes an index


23

SQL DDL


Create Table


CREATE
TABLE t
1
( t
1
key INTEGER PRIMARY
KEY,data

TEXT
,





num

double
NOT NULL
,






timeEnter

DATE )
;


SQLite supports the data types:


TEXT (similar to String in Java),


INTEGER (similar to long in Java) and


REAL (similar to double in Java).


All other types must be converted into on of these fields
before saving them in the database.


SQLite itself does not validate if the types written to the
columns are actually of the defined type, you can write an
integer into a string column.

24

Using SQLite


No special permission is required to use SQLite.


All databases, SQLite and others, are stored on the
device in
/data/data/
package_name
/databases
.


To
work with an SQLite database, you must
communicate with SQLite
through classes
in the
android.database

package.


You can either code “low
-
level” SQL or use the
database API


Create a class that extends
SQLiteOpenHelper
, and
put all database functionality in that class


SQLiteOpenHelper


A helper class is used to manage database creation.


To create and upgrade a database in your Android application you
usually subclass "
SQLiteOpenHelper
".


In this class you need to override the methods:


onCreate
()
to create the database and


onUpgrade
()
to upgrade the database in case of changes in the
database schema.


Both methods receive an "
SQLiteDatabase
" object.


SQLiteOpenHelper

provides the methods
getReadableDatabase
()
and
getWriteableDatabase
()
to get access to an
"
SQLiteDatabase
" object which allows database access either in
read or write mode.


For the primary key of the database you should always use the
identifier "_id" as some of Android functions rely on this standard.

27

Dealing with Data


Given
that you have a database and one or more
tables, you probably want to put
some data
in them.
You have two major approaches for doing this.


Use
execSQL
()
,
it works
for any SQL that does not return
results, so it
can handle
INSERT , UPDATE , DELETE , and so
on just
fine.


Use
the
insert()
,
update()
, and
delete()
methods
on
the
SQLiteDatabase

object.


These
are “builder” sorts of methods, in
that they
break down the
SQL statements into discrete chunks, then
take those
chunks as
parameters.

28

29

30

public Cursor query (

boolean

distinct,

String table,

String[] columns,

String selection,

String[]
selectionArgs
,

String
groupBy
, String having,
String
orderBy
, String limit)

Using Raw SQL

31


Insert

public

void

insertPerson
(String name,
int

age) {


String stmt =


"insert into names (name, age) values ('"

+ name +
"',"
+ age +
")"
;


db.execSQL
(stmt);

}


Update

public

void

updatePerson
(String name,
int

age) {


String stmt =



"update names set age='"

+ age +
“’ where name='"

+ name +
"')"
;


db.execSQL
(stmt);

}



Queries

32


/* Select using the raw SQL */

public

Cursor
selectPerson
(String name) {


String
stmt

=


"select _id, name, age from names





where name='"

+ name +
"')"
;


return
db
.rawQuery
(
stmt
);

}



/* Select using query method */

public

Cursor
selectPerson
(String name) {


String[] columns = {
KEY_ROWID
,
KEY_NAME
,
KEY_AGE
};


String[]
selectionArgs

= { name };



return
db
.query
(
TABLE_NAME
,
/
/ table to select from



columns,



/
/ fields (columns) to select



KEY_NAME

+
"=?"
,

/
/ WHERE clause



selectionArgs
,


/
/ arguments to WHERE clause



null
,




/
/ GROUP BY clause



null
,




/
/ HAVING clause



null




/
/ ORDER BY clause



);


}

Cursor


In computer science, a database
cursor

is a control
structure that enables traversal over the records in a
database


Database cursor is a concept used in many database systems.


No
matter how you execute the query, you get a Cursor
object back
.


With the cursor
, you can do the following:


Find
out how many rows are in the result
set:
getCount
()


Iterate
over the
rows:
moveToFirst
()
,
and
moveToNext
()


Get column value:
getInt
(),
getString
(),
getFload
(),



Re
-
execute
the query that created the
cursor:
requery
()


Release
the cursor’s
resources:
close
()

33

Using
SQLite

-

Summary

34


Create a class based on
SQLiteOpenHelper

class to handle
database creation and upgrade (e.g.,
myDBHelper
).


It is a good idea to add to the class constants for the table name
and the table columns.


Create a new class to handle all the DB operations


In this class, create an object of type
myDBHelper

and call the
method
getWritableDatabase

to obtain a reference to
SQLiteDatabase

object, then


Use the method
execSQL

to execute SQL UPDATE, INSERT or DELETE
statements (RAW SQL) or


Use the builder methods:
insert
()

,
update()
, and
delete()


Use the method
query()
to retrieve records from the database;
method parameters match the SQL statement parameters; it returns a
cursor object