Words on SQLite3 : most of the SQL-92 standard for SQL. partial support for triggers does not implement referential integrity constraints uses a relaxed data typing model. Not assigning a type => variant type.( possible to insert a string into numeric column ) invisible field called ROWID in every table for indexation and identification. outer joins are not supported create a SQLite database? - Prototype :public static SQLiteDatabase.openDatabase ( String path, SQLiteDatabase.CursorFactory factory, int flags ) -Signification Arguments : 1.path=database file to open and/or create. 2.factory=null for default(factory class that is instantiate a cursor when query is called) 3. flags= access mode(OPEN_READWRITE | OPEN_READONLY | CREATE_IF_NECESSARY) RQs: 1-to share use Content Providers or external SD resident DB to shared DB between projects: 2-if DB in SD =><uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" /> opening/creating a SQLITE database in your local Android’s data space: SQLiteDatabase db = this.openOrCreateDatabase("myfriendsDB", MODE_PRIVATE, null); càd : path="/data/data/<CURRENT_namespace>/databases/" 2ième Arg=Mode:MODE_PRIVATE, MODE_WORLD_READABLE,MODE_WORLD_WRITEABLE and MODE_APPEND Executing SQL commands on the Database 1)Action queries (manipulating Tables,Users,environement..)
Types:text, varchar, integer, float, numeric, date, time, timestamp, blob, boolean, -db.execSQL=>for :(insert, delete, update, create, drop, alter, etc db.execSQL( "insert into tblAMIGO(name, phone) values ('AAA', '555' );" );
2)Retrieval queries (Select) 2.a.Raw queries: EXEMPLE:select LastName, cellPhone from ClientTable where state = ‘Ohio’ order by LastName select city, count(*) as TotalClients from ClientTable group by city Using RawQuery Cursor c1 = db.rawQuery("select count(*) as Total from tblAMIGO",null); Using Parametized RawQuery : String mySQL = "select count(*) as Total " + " from tblAmigo " + " where recID > ? " + " and name = ? "; String[] args = { "1", "BBB" }; Cursor c1 = db.rawQuery(mySQL, args);
2.b.Simple queries: -Simple queries can only retrieve data from a single table.
signature of Simple Query Method | ****************Example************************ | **********Example2************* |
query( String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy ) | Cursor c = db.query( "EmployeeTable", columns, "sex = ? And superSsn = ? " , conditionArgs, "Dno", "Count(*) > 2", "AVG Desc " ); | String [] columns = {"recID", "name", "phone"}; Cursor c1 = db.query ( "tblAMIGO", columns, "recID > 2 and length(name) >= 3 and name like 'B%' ", null, null, null, "recID" ); int theTotal = c1.getCount(); |
Full Example :
Query | signification | Query Method |
select name, count(*) as TotalSubGroup from tblAMIGO where recID > 3 group by name having count(*) <= 4; | how many groups of friends whose recID > 3 have the same name. In addition, we want to see ‘name’ groups having no more than four people each. | String [] selectColumns = {"name", "count(*) as TotalSubGroup"}; String whereCondition = "recID > ?"; String [] whereConditionArgs = {"3"}; String groupBy = "name"; String having = "count(*) <= 4"; String orderBy = "name"; Cursor myCur = db.query ( "tblAMIGO", selectColumns, whereCondition, whereConditionArgs, groupBy, having, orderBy ); |
create a SQLite database?
- Prototype :public static SQLiteDatabase.openDatabase ( String path, SQLiteDatabase.CursorFactory factory, int flags ) -Signification Arguments : 1.path=database file to open and/or create. 2.factory=null for default(factory class that is instantiate a cursor when query is called) 3. flags= access mode(OPEN_READWRITE | OPEN_READONLY | CREATE_IF_NECESSARY) ….....................
ACTIONS QUERY : Cursors provide READ_ONLY access to records. -cursor.updateInt(. EXAMPLE OF UPDATE REQEST : db.execSQL( "update tblAMIGO set name = (name || 'XXX') where phone >= '001' "); SIGNIFICATION=>ajouter au nom d'une personne le suffix XXX si cette personne damet comme phone >=001. ==>Note The symbol || is the SQL concatenate operator
DATABASE VISIBILITY -Share DB : 1)one way =external SDCard. 2)other way=content provider.
SQLITE COMMAND LINE : COMMANDE*********** Functionnality -sqlite3 nwDB.db create or open DB. ===>adb shell to tap in the emulator’s internal memory -sqlite> .tables ===> names of tables sqlite> select * from tblAMIGO ; ===> select sqlite> .exit ===> sortir
Exemple : $E:\Android> adb shell $# sqlite3 /data/data/matos.sql1/databases/myfriendsDB
Handling Database SQLite Code Source Example :
A)Bean For Input/OutPut With DB Slite:
public class SQLiteOperationsSample {
SQLiteDatabase db;
// Create the dataBase
public void openDatabase(ContextWrapper context) {
try {
db = SQLiteDatabase
.openDatabase(
"data/data/slm.Abdennour.android.formation.sqldemo3/myfriendsDB",
// "sdcard/myfriendsDB",
null, SQLiteDatabase.CREATE_IF_NECESSARY);
Toast.makeText(context, "DB was opened!", 1).show();
} catch (SQLiteException e) {
Toast.makeText(context, e.getMessage(), 1).show();
}
}
// Create Table & Insert into Table & Transaction Handling
public void insertSomeDbData(ContextWrapper context) {
// create table: tblAmigo
db.beginTransaction();
try {
db.execSQL("create table tblAMIGO ("
+ " recID integer PRIMARY KEY autoincrement, "
+ " name text, " + " phone text ); ");
// commit your changes
db.setTransactionSuccessful();
Toast.makeText(context, "Table was created", 1).show();
} catch (SQLException e1) {
Toast.makeText(context, e1.getMessage(), 1).show();
} finally {
// finish transaction processing
db.endTransaction();
}
// populate table: tblAmigo
db.beginTransaction();
try {
// insert rows
db.execSQL("insert into tblAMIGO(name, phone) "
+ " values ('AAA', '555' );");
db.execSQL("insert into tblAMIGO(name, phone) "
+ " values ('BBB', '777' );");
db.execSQL("insert into tblAMIGO(name, phone) "
+ " values ('CCC', '999' );");
// commit your changes
db.setTransactionSuccessful();
Toast.makeText(context, " 3 records were inserted", 1).show();
} catch (SQLiteException e2) {
// report problem
Toast.makeText(context, e2.getMessage(), 1).show();
} finally {
db.endTransaction();
}
}
public void useRawQuery1(ContextWrapper context) {
try {
// hard-coded SQL-select command with no arguments
String mySQL = "select count(*) as Total from tblAMIGO";
Cursor c1 = db.rawQuery(mySQL, null);
int index = c1.getColumnIndex("Total");
// advance to the next record (first rec. if necessary)
c1.moveToNext();
int theTotal = c1.getInt(index);
Toast.makeText(context, "Total1: " + theTotal, 1).show();
} catch (Exception e) {
Toast.makeText(context, e.getMessage(), 1).show();
}
}
public void useRawQuery2(ContextWrapper ctx) {
try {
// ? arguments provided for automatic replacement
String mySQL = " select count(*) as Total " + " from tblAmigo "
+ " where recID > ? " + " and name = ? ";
String[] args = { "1", "BBB" };
Cursor c1 = db.rawQuery(mySQL, args);
int index = c1.getColumnIndex("Total");
// advance to the next record (first rec. if necessary)
c1.moveToNext();
int theTotal = c1.getInt(index);
Toast.makeText(ctx, "Total2: " + theTotal, 1).show();
} catch (Exception e) {
Toast.makeText(ctx, e.getMessage(), 1).show();
}
}
public void useRawQuery3(ContextWrapper ctx) {
try {
// arguments injected by manual string concatenation
String[] args = { "1", "BBB" };
String mySQL = " select count(*) as Total " + " from tblAmigo "
+ " where recID > " + args[0] + " and name = '"
+ args[1] + "'";
Cursor c1 = db.rawQuery(mySQL, null);
int index = c1.getColumnIndex("Total");
// advance to the next record (first rec. if necessary)
c1.moveToNext();
int theTotal = c1.getInt(index);
Toast.makeText(ctx, "Total3: " + theTotal, 1).show();
} catch (Exception e) {
Toast.makeText(ctx, e.getMessage(), 1).show();
}
}
public void useSimpleQuery1(ContextWrapper ctx) {
try {
// simple (implicit) query on one table
String[] columns = { "recID", "name", "phone" };
Cursor c1 = db.query("tblAMIGO", columns,
"recID > 2 and length(name) >= 3 and name like 'B%' ",
null, null, null, "recID");
int theTotal = c1.getCount();
Toast.makeText(ctx, "Total4: " + theTotal, 1).show();
} catch (Exception e) {
Toast.makeText(ctx, e.getMessage(), 1).show();
}
}
public void useSimpleQuery2(ContextWrapper ctx) {
try {
// nontrivial 'simple query' on one table
String[] selectColumns = { "name", "count(*) as TotalSubGroup" };
String whereCondition = "recID >= ?";
String[] whereConditionArgs = { "1" };
String groupBy = "name";
String having = "count(*) <= 4";
String orderBy = "name";
Cursor c = db.query("tblAMIGO", selectColumns, whereCondition,
whereConditionArgs, groupBy, having, orderBy);
int theTotal = c.getCount();
Toast.makeText(ctx, "Total5: " + theTotal, 1).show();
} catch (Exception e) {
Toast.makeText(ctx, e.getMessage(), 1).show();
}
}
public void useCursor1(ContextWrapper ctx, TextView txtMsg) {
try {
txtMsg.append("\n");
// obtain a list of <recId, name, phone> from DB
String[] columns = { "recID", "name", "phone" };
Cursor c = db.query("tblAMIGO", columns, null, null, null, null,
"recID");
int theTotal = c.getCount();
Toast.makeText(ctx, "Total6: " + theTotal, 1).show();
int idCol = c.getColumnIndex("recID");
int nameCol = c.getColumnIndex("name");
int phoneCol = c.getColumnIndex("phone");
while (c.moveToNext()) {
columns[0] = Integer.toString((c.getInt(idCol)));
columns[1] = ctx.getString(nameCol);
columns[2] = ctx.getString(phoneCol);
txtMsg.append(columns[0] + " " + columns[1] + " " + columns[2]
+ "\n");
}
} catch (Exception e) {
Toast.makeText(ctx, e.getMessage(), 1).show();
}
}
public void updateDB(ContextWrapper ctx, TextView txMsg) {
// action query using execSQL
String theValue;
try {
theValue = "222";
db.execSQL(" update tblAMIGO " + " set name = (name || 'XXX') "
+ " where phone >= '" + theValue + "' ");
useCursor1(ctx, txMsg);
} catch (Exception e) {
Toast.makeText(ctx, "updateDB " + e.getMessage(), 1).show();
}
useCursor1(ctx, txMsg);
}
public void dropTable(ContextWrapper ctx) {
// (clean start) action query to drop table
try {
db.execSQL(" drop table tblAmigo; ");
Toast.makeText(ctx, "Table dropped", 1).show();
} catch (Exception e) {
Toast.makeText(ctx, "dropTable()\n" + e.getMessage(), 1).show();
}
}
public void useInsertMethod(ContextWrapper ctx, TextView txtMsg) {
ContentValues initialValues = new ContentValues();
initialValues.put("name", "ABC");
initialValues.put("phone", "101");
int rowPosition = (int) db.insert("tblAMIGO", null, initialValues);
txtMsg.append("\nrec added at: " + rowPosition);
initialValues.put("name", "DEF");
initialValues.put("phone", "202");
rowPosition = (int) db.insert("tblAMIGO", null, initialValues);
txtMsg.append("\nrec added at: " + rowPosition);
initialValues.clear();
rowPosition = (int) db.insert("tblAMIGO", null, initialValues);
txtMsg.append("\nrec added at: " + rowPosition);
rowPosition = (int) db.insert("tblAMIGO", "name", initialValues);
txtMsg.append("\nrec added at: " + rowPosition);
useCursor1(ctx, txtMsg);
}
public void useUpdateMethod(ContextWrapper ctx, TextView txtM) {
// using the update method to change name of selected friend
String[] whereArgs = { "2", "7" };
ContentValues updValues = new ContentValues();
updValues.put("name", "Maria");
int recAffected = db.update("tblAMIGO", updValues,
"recID > ? and recID < ?", whereArgs);
Toast.makeText(ctx, "Total7: " + recAffected, 1).show();
useCursor1(ctx, txtM);
}
public void useDeleteMethod(ContextWrapper ctx, TextView txtM) {
// using the delete method to remove a group of friends
// whose id# is between 2 and 7
String[] whereArgs = { "2", "7" };
int recAffected = db.delete("tblAMIGO", "recID > ? and recID < ?",
whereArgs);
Toast.makeText(ctx, "Total8: " + recAffected, 1).show();
useCursor1(ctx, txtM);
}// useDeleteMethod
}
|
B)Activity Manager of DB SQlite File:
public class SQLManager extends Activity {
SQLiteDatabase db;
TextView txtMsg;
SQLiteOperationsSample SQLiteMthdSample;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
SQLiteMthdSample = new SQLiteOperationsSample();
setContentView(R.layout.main);
txtMsg = (TextView) findViewById(R.id.txtMsg);
try {
SQLiteMthdSample.openDatabase(this); // open (create if needed)
// database
SQLiteMthdSample.dropTable(this);
// if needed drop table tblAmigos
SQLiteMthdSample.insertSomeDbData(this); // create-populate
// tblAmigos
SQLiteMthdSample.useRawQuery1(this); // fixed SQL with no arguments
SQLiteMthdSample.useRawQuery2(this); // parameter substitution
SQLiteMthdSample.useRawQuery3(this); // manual string concatenation
SQLiteMthdSample.useSimpleQuery1(this); // simple query
SQLiteMthdSample.useSimpleQuery2(this); // nontrivial 'simple query'
SQLiteMthdSample.useCursor1(this, txtMsg); // retrieve rows from a
// table
SQLiteMthdSample.updateDB(this, txtMsg); // use execSQL to update
SQLiteMthdSample.useInsertMethod(this, txtMsg); // use insert method
SQLiteMthdSample.useUpdateMethod(this, txtMsg);// use update method
SQLiteMthdSample.useDeleteMethod(this, txtMsg); // use delete method
db.close();// make sure to release the DB
Toast.makeText(this, "All done!", 1).show();
} catch (Exception e) {
Toast.makeText(this, e.getMessage(), 1).show();
}
}
}
|
|
Summary Of Content Provider(Full Example) :(17)
Content Provider:=used to store and retrieve data and thus make them accessible to all applications. (sert à stocker et récupérer des données et ainsi les rendre accessibles à toutes les applications.)
- contentProvider is composed of : 1)Uri 2)Methods (Insert, Update, Delete, Query)
/TODO
Content Provider Manager Code Source Full Example :
A)Manifest XML File:
<application android:icon="@drawable/ic_launcher" android:label="@string/app_name">
<activity android:label="@string/app_name" android:name="MainActivity"> <intent-filter> <action android:name="android.intent.action.MAIN"/> <category android:name="android.intent.category.LAUNCHER"/> </intent-filter> </activity> <provider android:authorities="com.androidbook.provider.BookProvider.bookprovider" android:name=".BookProvider"/> </application>
B)Bean:
|
public class SharedInformation {
public SharedInformation() {
}
public static final class Cours implements BaseColumns {
private Cours() {
}
public static final String COURS_ID = "COURS";
public static final String COURS_NAME = "COURS_NAME";
public static final String COURS_DESC = "COURS_DESC";
}
}
|
C)Avtivity Test:
public class ContentProviderExempleActivity extends Activity {
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
insertRecords();
displayContentProvider();
}
private void displayContentProvider() {
String columns[] = new String[] { Cours.COURS_ID, Cours.COURS_NAME, Cours.COURS_DESC };
Uri mContacts = TutosAndroidProvider.CONTENT_URI;
Cursor cur = managedQuery(mContacts, columns, null, null, null);
Toast.makeText(ContentProviderExempleActivity.this, cur.getCount() + "",
Toast.LENGTH_LONG).show();
if (cur.moveToFirst()) {
String name = null;
do {
name = cur.getString(cur.getColumnIndex(Cours.COURS_ID)) + " " +
cur.getString(cur.getColumnIndex(Cours.COURS_NAME)) + " " +
cur.getString(cur.getColumnIndex(Cours.COURS_DESC));
Toast.makeText(this, name + " ", Toast.LENGTH_LONG).show();
} while (cur.moveToNext());
}
}
private void insertRecords() {
ContentValues contact = new ContentValues();
contact.put(Cours.COURS_NAME, "Android");
contact.put(Cours.COURS_DESC, "Introduction à la programmation sous Android");
getContentResolver().insert(TutosAndroidProvider.CONTENT_URI, contact);
contact.clear();
contact.put(Cours.COURS_NAME, "Java");
contact.put(Cours.COURS_DESC, "Introduction à la programmation Java");
getContentResolver().insert(TutosAndroidProvider.CONTENT_URI, contact);
contact.clear();
contact.put(Cours.COURS_NAME, "Iphone");
contact.put(Cours.COURS_DESC, "Introduction à l'objectif C");
getContentResolver().insert(TutosAndroidProvider.CONTENT_URI, contact);
}
}
|
D)Content Provider: |
public class TutosAndroidProvider extends ContentProvider {
// URI de notre content provider, elle sera utilisé pour accéder au
// ContentProvider
public static final Uri CONTENT_URI = Uri
.parse("content://com.tutos.android.content.provider.tutosandroidprovider");
// Nom de notre base de données
public static final String CONTENT_PROVIDER_DB_NAME = "tutosandroid.db";
// Version de notre base de données
public static final int CONTENT_PROVIDER_DB_VERSION = 1;
// Nom de la table de notre base
public static final String CONTENT_PROVIDER_TABLE_NAME = "cours";
// Le Mime de notre content provider, la premiére partie est toujours
// identique
public static final String CONTENT_PROVIDER_MIME = "vnd.android.cursor.item/vnd.tutos.android.content.provider.cours";
// Notre DatabaseHelper
private static class DatabaseHelper extends SQLiteOpenHelper {
// Création à partir du Context, du Nom de la table et du numéro de
// version
DatabaseHelper(Context context) {
super(context, TutosAndroidProvider.CONTENT_PROVIDER_DB_NAME, null,
TutosAndroidProvider.CONTENT_PROVIDER_DB_VERSION);
}
// Création des tables
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE "
+ TutosAndroidProvider.CONTENT_PROVIDER_TABLE_NAME + " ("
+ Cours.COURS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ Cours.COURS_NAME + " VARCHAR(255)," + Cours.COURS_DESC
+ " VARCHAR(255)" + ");");
}
// Cette méthode sert à gérer la montée de version de notre base
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "
+ TutosAndroidProvider.CONTENT_PROVIDER_TABLE_NAME);
onCreate(db);
}
}
private DatabaseHelper dbHelper;
/**
* @see android.content.ContentProvider#delete(Uri,String,String[])
*/
@Override
public int delete(Uri uri, String selection, String[] selectionArgs) {
// TODO Put your code here
return 0;
}
/**
* le type mime de notre ContentProvider
*
* @see android.content.ContentProvider#getType(Uri)
*/
@Override
public String getType(Uri uri) {
return TutosAndroidProvider.CONTENT_PROVIDER_MIME;
}
/**
* sert à rajouter une valeur à notre ContentProvider.
*
* @see android.content.ContentProvider#insert(Uri,ContentValues)
*/
@Override
public Uri insert(Uri uri, ContentValues values) {
// Create and/or open a database that will be used for reading and
// writing.
// =>récupérer une instance de la base de données en mode ecriture
SQLiteDatabase db = dbHelper.getWritableDatabase();
try {
// db.insertOrThrow=>inserting a row into the database.
// retourne l’id de l’insertion dans la base et -1 en cas d’échec de
// l’insertion.
long id = db.insertOrThrow(CONTENT_PROVIDER_TABLE_NAME, null,
values);
if (id == -1) {
throw new RuntimeException(String.format(
"%s : Failed to insert [%s] for unknown reasons.",
"TutosAndroidProvider", values, uri));
} else {
// Appends the given ID to the end of the path.
return ContentUris.withAppendedId(uri, id);
}
} finally {
// fermer la connexion à la BDD qq résultat
db.close();
}
}
/**
* initialiser notre DatabaseHelper
*
* @see android.content.ContentProvider#onCreate()
*/
@Override
public boolean onCreate() {
dbHelper = new DatabaseHelper(getContext());
return true;
}
/**
* id de notre Uri
*/
private long getId(Uri uri) {
String lastPathSegment = uri.getLastPathSegment();
if (lastPathSegment != null) {
try {
return Long.parseLong(lastPathSegment);
} catch (NumberFormatException e) {
Log.e("TutosAndroidProvider", "Number Format Exception : " + e);
}
}
return -1;
}
/**
* @see android.content.ContentProvider#query(Uri,String[],String,String[],String)
*/
@Override
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
long id = getId(uri);
SQLiteDatabase db = dbHelper.getReadableDatabase();
if (id < 0) {
return db
.query(TutosAndroidProvider.CONTENT_PROVIDER_TABLE_NAME,
projection, selection, selectionArgs, null, null,
sortOrder);
} else {
return db.query(TutosAndroidProvider.CONTENT_PROVIDER_TABLE_NAME,
projection, Cours.COURS_ID + "=" + id, null, null, null,
null);
}
}
/**
* @see android.content.ContentProvider#update(Uri,ContentValues,String,String[])
*/
@Override
public int update(Uri uri, ContentValues values, String selection,
String[] selectionArgs) {
long id = getId(uri);
SQLiteDatabase db = dbHelper.getWritableDatabase();
try {
if (id < 0)
// mettre à jour l’élement par sa valeur.
return db.update(
TutosAndroidProvider.CONTENT_PROVIDER_TABLE_NAME,
values, selection, selectionArgs);
else
// met à jour l’élément
return db.update(
TutosAndroidProvider.CONTENT_PROVIDER_TABLE_NAME,
values, Cours.COURS_ID + "=" + id, null);
} finally {
db.close();
}
}
}
|
Content Provider (Retreive Contacts) :
ContentResolver cr = getContentResolver(); Cursor cur = cr.query( ContactsContract.Contacts.CONTENT_URI, null, null, null, null); if (cur.getCount() > 0) { while (cur.moveToNext()) { String id = cur.getString(cur.getColumnIndex(ContactsContract.Contacts._ID)); String name = cur.getString(cur.getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME)); .... } } |
|
No comments:
Post a Comment