SqlLite

Pour récupérer la base de données d’une application existante :

Using Android Studio 3.0 or later version it is possible to pull database (also shared preference, cache directory and others) if application runs in debug mode on non-rooted device.

To pull database using android studio follow these steps.

  1. Click View > Tool Windows > Device File Explorer.
  2. Expand /data/data/[package-name] nodes.

Steps followed in Android Studio 3.0

Manipulation d’une base de données :

Utilisez SQLite Data Browser http://sqlitebrowser.org/

Pour les manipulations : http://zetcode.com/db/sqlite/datamanipulation/

Exemples : 

select * from nameday where mois=3 and jour=2 and pays= »FR »

update nameday SET  prenoms= »Charles Le Bon » where mois=3 and jour=2 and pays= »FR »

Utilisation d’une base de données déjà créée :
1) Modify your database schema/structure to make it compatible with Android OS :
Change primary key fields of all tables to “_id“.
2) Create a table “android_metadata(…)” and insert one row :
CREATE TABLE “android_metadata” (“locale” TEXT DEFAULT ‘en_US’) and INSERT INTO “android_metadata” VALUES (‘en_US’)
3) Copy your database file to … yourapp/assets/

In order to create assets folder, let Android Studio do it for you.

  1. In Android Studio (1.0 & above), right-click on the enter image description here folder and navigate to the Assets Folder.

enter image description here

  1. On the next screen just click Finish

4) Read this file from MyDatabaseHelper and write it to application’s database directory

Make your DataBaseHelper class look like this:

 public class UtilDb extends SQLiteOpenHelper  
 {  
   //The Android's default system path of your application database.  
   private static String DB_PATH = "/data/data/<>/databases/";  
   private static String DB_NAME = "DBAndroCity.db";  
   private SQLiteDatabase myDataBase;  
   private final Context myContext;  
   /**  
    * Constructor  
    * Takes and keeps a reference of the passed context in order to access to the application assets and resources.  
    * @param context  
    */  
   public UtilDb(Context context) {  
     super(context, DB_NAME, null, 1);  
     this.myContext = context;  
   }  
   /**  
    * Creates a empty database on the system and rewrites it with your own database.  
    * */  
   public void createDataBase() throws IOException{  
     boolean dbExist = checkDataBase();  
     if(dbExist){  
       //do nothing - database already exist  
     }else{  
       //By calling this method and empty database will be created into the default system path  
       //of your application so we are gonna be able to overwrite that database with our database.  
       this.getReadableDatabase();  
       try {  
         copyDataBase();  
       } catch (IOException e) {  
         throw new Error("Error copying database");  
       }  
     }  
   }  
   /**  
    * Check if the database already exist to avoid re-copying the file each time you open the application.  
    * @return true if it exists, false if it doesn't  
    */  
   private boolean checkDataBase(){  
     SQLiteDatabase checkDB = null;  
     try{  
       String myPath = DB_PATH + DB_NAME;  
       checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);  
     }catch(SQLiteException e){  
       //database does't exist yet.  
     }  
     if(checkDB != null){  
       checkDB.close();  
     }  
     return checkDB != null ? true : false;  
   }  
   /**  
    * Copies your database from your local assets-folder to the just created empty database in the  
    * system folder, from where it can be accessed and handled.  
    * This is done by transfering bytestream.  
    * */  
   private void copyDataBase() throws IOException{  
     //Open your local db as the input stream  
     InputStream myInput = myContext.getAssets().open(DB_NAME);  
     // Path to the just created empty db  
     String outFileName = DB_PATH + DB_NAME;  
     //Open the empty db as the output stream  
     OutputStream myOutput = new FileOutputStream(outFileName);  
     //transfer bytes from the inputfile to the outputfile  
     byte[] buffer = new byte[1024];  
     int length;  
     while ((length = myInput.read(buffer))>0){  
       myOutput.write(buffer, 0, length);  
     }  
     //Close the streams  
     myOutput.flush();  
     myOutput.close();  
     myInput.close();  
   }  
   public void openDataBase() throws SQLException {  
     //Open the database  
     String myPath = DB_PATH + DB_NAME;  
     myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);  
   }  
   @Override  
   public synchronized void close() {  
     if(myDataBase != null)  
       myDataBase.close();  
     super.close();  
   }  
   @Override  
   public void onCreate(SQLiteDatabase db) {  
   }  
   @Override  
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
   }  
   // Add your public helper methods to access and get content from the database.  
   // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy  
   // to you to create adapters for your views.  
 }  

Now you can create a new instance of this DataBaseHelper class and call the createDataBase() and openDataBase() methods. Remember to change the « YOUR_PACKAGE » to your application package namespace (i.e: com.examplename.myapp) in the DB_PATH string.

Dans le code de l’application :

  UtilDb db = new UtilDb(this);  
     db = new UtilDb(this);  
     try {  
       db.createDataBase();  
     } catch (IOException ioe) {  
       throw new Error("Unable to create database");  
     }  
     try {  
       db.openDataBase();  
     }catch(SQLException sqle){  
       throw sqle;  
     }  
     //-- Test Retrieve all run  
     String sAllRun = "";  
     Cursor c = db.getAllRun();  
     if (c != null) {  
       if (c.moveToFirst()) {  
         do {  
           sAllRun = sAllRun + " ("+c.getInt(0) +") De " + c.getString(2) +  
               " à " + c.getString(3)+ "\n";  
           Toast.makeText(this, sAllRun, Toast.LENGTH_LONG).show();  
         } while (c.moveToNext());  
       }  
     }  
     db.close();  

Avec la fonction « getAllRun() » :

   //---retrieves all run---  
   public Cursor getAllRun() {  
     return db.query(DATABASE_TABLE_Run, new String[]{KEY_ROWID_RUN,  
             KEY_RUN_NAME,  
             KEY_CITY_FIRST,  
             KEY_CITY_LAST,  
             KEY_CITY_nMin,  
             KEY_CITY_nHour,  
             KEY_CITY_nDay,  
             KEY_CITY_nMonth  
         }, null, null, null,  
         null, null);  
   }  

 

Références :

http://zaman91.wordpress.com/2010/09/22/android-how-to-use-own-sqlite-database/

http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

Ou création via SQL : http://www.michenux.net/android-database-sqlite-creation-upgrade-245.html

Suppression de ligne :

   public static int deleteContact(SQLiteDatabase db, String sNom) {  
     //on delete la ligne concernée  
     //retourne 1 si ok 0 sinon  
     //db.delete("tablename","id=? and name=?",new String[]{"1","jack"});  
     //delete from tablename where id='1' and name ='jack'"  
     int nRes;  
     nRes=db.delete("contact", "nom=?", new String[]{sNom});  
     Log.d("delete"," "+nRes);  
     return nRes;  
   }  

Création de la base avec une table :

 public class DbHelper extends SQLiteOpenHelper {  
   private static final String DATABASE_NAME = "dbnumtel.db";  
   private static final int DATABASE_VERSION = 1;  
   public DbHelper(Context context) {  
     super(context, DATABASE_NAME, null, DATABASE_VERSION);  
   }  
   @Override  
   public void onCreate(SQLiteDatabase db) {  
     db.execSQL("CREATE TABLE IF NOT EXISTS contact ( " +  
         "_id INTEGER PRIMARY KEY AUTOINCREMENT," +  
         "nom TEXT," +  
         "numtel TEXT," +  
         "normtel TEXT," +  
         "logtel INTEGER," +  
         "idico INTEGER)");  
   }  
 }  

Opérations diverses :

  public static long CompteEnr(SQLiteDatabase db) {  
     Log.d("Base", "Nb enr=" + String.valueOf(DatabaseUtils.queryNumEntries(db, "contact")));  
     return DatabaseUtils.queryNumEntries(db, "contact");  
   }  
   public static void onDelete(SQLiteDatabase db) {  
     db.execSQL("DELETE FROM contact");  
   }  
   public static boolean onRecherche(SQLiteDatabase db, String sNum) {  
     Cursor c = db.query("contact", null,  
         "numtel = '" + sNum + "'", null, null, null, null);  
     //si aucun élément n'a été retourné dans la requête, on renvoie faux  
     if (c.getCount() == 0) {  
       c.close();  
       return false;  
     } else {  
       c.close();  
       return true;  
     }  
   }  

Pour ajouter la base de données (cf ce post) :

 import java.io.File;  
 import java.io.FileOutputStream;  
 import java.io.IOException;  
 import java.io.InputStream;  
 import java.io.OutputStream;  
 import android.content.Context;  
 import android.database.Cursor;  
 import android.database.SQLException;  
 import android.database.sqlite.SQLiteDatabase;  
 import android.database.sqlite.SQLiteOpenHelper;  
 public class DataBaseHelperClass extends SQLiteOpenHelper{  
  //The Android's default system path of your application database.  
 private static String DB_PATH = "/data/data/package_name/databases/";  
 // Data Base Name.  
 private static final String DATABASE_NAME = "DBName.sqlite";  
 // Data Base Version.  
 private static final int DATABASE_VERSION = 1;  
 // Table Names of Data Base.  
 static final String TABLE_Name = "tableName";  
 public Context context;  
 static SQLiteDatabase sqliteDataBase;  
 /**  
  * Constructor  
  * Takes and keeps a reference of the passed context in order to access to the application assets and resources.  
  * @param context  
  * Parameters of super() are  1. Context  
  *               2. Data Base Name.  
  *               3. Cursor Factory.  
  *               4. Data Base Version.  
  */  
 public DataBaseHelperClass(Context context) {      
   super(context, DATABASE_NAME, null ,DATABASE_VERSION);  
   this.context = context;  
 }  
 /**  
  * Creates a empty database on the system and rewrites it with your own database.  
  * By calling this method and empty database will be created into the default system path  
  * of your application so we are gonna be able to overwrite that database with our database.  
  * */  
 public void createDataBase() throws IOException{  
   //check if the database exists  
   boolean databaseExist = checkDataBase();  
   if(databaseExist){  
     // Do Nothing.  
   }else{  
     this.getWritableDatabase();       
     copyDataBase();   
   }// end if else dbExist  
 } // end createDataBase().  
 /**  
  * Check if the database already exist to avoid re-copying the file each time you open the application.  
  * @return true if it exists, false if it doesn't  
  */  
 public boolean checkDataBase(){  
   File databaseFile = new File(DB_PATH + DATABASE_NAME);  
   return databaseFile.exists();      
 }  
 /**  
  * Copies your database from your local assets-folder to the just created empty database in the  
  * system folder, from where it can be accessed and handled.  
  * This is done by transferring byte stream.  
  * */  
 private void copyDataBase() throws IOException{   
   //Open your local db as the input stream  
   InputStream myInput = context.getAssets().open(DATABASE_NAME);   
   // Path to the just created empty db  
   String outFileName = DB_PATH + DATABASE_NAME;   
   //Open the empty db as the output stream  
   OutputStream myOutput = new FileOutputStream(outFileName);   
   //transfer bytes from the input file to the output file  
   byte[] buffer = new byte[1024];  
   int length;  
   while ((length = myInput.read(buffer))>0){  
     myOutput.write(buffer, 0, length);  
   }  
   //Close the streams  
   myOutput.flush();  
   myOutput.close();  
   myInput.close();   
 }  
 /**  
  * This method opens the data base connection.  
  * First it create the path up till data base of the device.  
  * Then create connection with data base.  
  */  
 public void openDataBase() throws SQLException{     
   //Open the database  
   String myPath = DB_PATH + DATABASE_NAME;  
   sqliteDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);   
 }  
 /**  
  * This Method is used to close the data base connection.  
  */  
 @Override  
 public synchronized void close() {   
   if(sqliteDataBase != null)  
     sqliteDataBase.close();   
   super.close();   
 }  
 /**  
 * Apply your methods and class to fetch data using raw or queries on data base using   
 * following demo example code as:  
 */  
 public String getUserNameFromDB(){  
   String query = "select User_First_Name From "+TABLE_USER_DETAILS;  
   Cursor cursor = sqliteDataBase.rawQuery(query, null);  
   String userName = null;  
   if(cursor.getCount()>0){  
     if(cursor.moveToFirst()){  
   do{  
         userName = cursor.getString(0);  
       }while (cursor.moveToNext());  
     }  
   }  
   return userName;  
 }  
 @Override  
 public void onCreate(SQLiteDatabase db) {  
   // No need to write the create table query.  
   // As we are using Pre built data base.  
   // Which is ReadOnly.  
 }  
 @Override  
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
   // No need to write the update table query.  
   // As we are using Pre built data base.  
   // Which is ReadOnly.  
   // We should not update it as requirements of application.  
 }    
 }  
Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion /  Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Articles récents
Commentaires récents
fatima sur Bienvenue !
AdminDroid sur Bienvenue !
fatima sur Bienvenue !
Archives
Catégories
%d blogueurs aiment cette page :