mardi 3 mai 2016

Android SQLite function to select multiple tables with inner join

I have 2 tables: "utilisateurs(id, pseudo, indice_calorique, age, genre, taille, taux_masse_grasse, id_frequence_activite)" "frequence_activite(id, libelle)"

And I wan't to display all rows of utilisateurs table but instead of display the id I wan't to retrieve the title(libelle) in frequence_activite table.

So I did a function to retrieve data of a table with a references, so I tried to get the title of the references table, so I did this:

public List<Utilisateurs> getAllUtilisateursFreqAct() {
    Utilisateurs utilisateursFreqAct = new Utilisateurs();
    List<Utilisateurs> utilisateursFreqActLists = new ArrayList<Utilisateurs>();

    SQLiteDatabase db = this.getWritableDatabase();
    String selectQuery =  " SELECT " + TABLE_UTILISATEURS + "." + KEY_ID + " As KEY_ID"
            + ", " + TABLE_UTILISATEURS + "." + KEY_PSEUDO + " As KEY_PSEUDO"
            + ", " + TABLE_UTILISATEURS + "." + KEY_INDICE_CALORIQUE + " As KEY_INDICE_CALORIQUE"
            + ", " + TABLE_UTILISATEURS + "." + KEY_AGE + " As KEY_AGE"
            + ", " + TABLE_UTILISATEURS + "." + KEY_GENRE + " As KEY_GENRE"
            + ", " + TABLE_UTILISATEURS + "." + KEY_TAILLE + " As KEY_TAILLE"
            + ", " + TABLE_UTILISATEURS + "." + KEY_TAUX_MASSE_GRASSE + " As KEY_TAUX_MASSE_GRASSE"
            + ", " + TABLE_FREQUENCE_ACTIVITE + "." + KEY_LIBELLE + " As KEY_LIBELLE"
            + " FROM " + TABLE_UTILISATEURS
            + " INNER JOIN " + TABLE_FREQUENCE_ACTIVITE + " ON " + TABLE_UTILISATEURS + "." + KEY_ID_FREQUENCE_ACTIVITE;

    Cursor cursor = db.rawQuery(selectQuery, null);
    // looping through all rows and adding to list
    if (cursor.moveToFirst()) {
        do {
            utilisateursFreqAct = new Utilisateurs();
            utilisateursFreqAct.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex("KEY_ID"))));
            utilisateursFreqAct.setPseudo(cursor.getString(cursor.getColumnIndex("KEY_PSEUDO")));
            utilisateursFreqAct.setIndice_calorique(Integer.parseInt(cursor.getString(cursor.getColumnIndex("KEY_INDICE_CALORIQUE"))));
            utilisateursFreqAct.setAge(cursor.getString(cursor.getColumnIndex("KEY_AGE")));
            utilisateursFreqAct.setGenre(cursor.getString(cursor.getColumnIndex("KEY_GENRE")));
            utilisateursFreqAct.setTaille(Integer.parseInt(cursor.getString(cursor.getColumnIndex("KEY_TAILLE"))));
            utilisateursFreqAct.setTaux_masse_grasse(Integer.parseInt(cursor.getString(cursor.getColumnIndex("KEY_TAUX_MASSE_GRASSE"))));
            utilisateursFreqAct.setStringId_frequence_activite(cursor.getString(cursor.getColumnIndex("KEY_LIBELLE")));

            utilisateursFreqActLists.add(utilisateursFreqAct);
        } while (cursor.moveToNext());
    }

    cursor.close();

    return utilisateursFreqActLists;

}

But to run this I had to add a new String in my class description:

int id;
String pseudo;
int indice_calorique;
String age;
String genre;
int taille;
int taux_masse_grasse;
int id_frequence_activite;

String stringId_frequence_activite;

...other contructors getter and setter

public Utilisateurs(int id, String pseudo, int indice_calorique, String age, String genre, int taille, int taux_masse_grasse, String stringId_frequence_activite) {
    this.id = id;
    this.pseudo = pseudo;
    this.indice_calorique = indice_calorique;
    this.age = age;
    this.genre = genre;
    this.taille = taille;
    this.taux_masse_grasse = taux_masse_grasse;
    this.stringId_frequence_activite = stringId_frequence_activite;
}

public String getStringId_frequence_activite() {
    return stringId_frequence_activite;
}

public void setStringId_frequence_activite(String stringId_frequence_activite) {
    this.stringId_frequence_activite = stringId_frequence_activite;
}

But can I get the string of the another class "frequence_activite" to do not create an another string ?

How I call it:

    db.getAllUtilisateursFreqAct();
    List<Utilisateurs> contacts = db.getAllUtilisateursFreqAct();

    for (Utilisateurs cn : contacts) {
        String log = "Id: " + cn.getId() + " ,Pseudo: " + cn.getPseudo() + " ,Indice calorique: " + cn.getIndice_calorique() + " ,Age: " + cn.getAge() + " ,Genre: " + cn.getGenre() + " ,Taille: " + cn.getTaille() + " ,TMG: " + cn.getTaux_masse_grasse() + " ,Frequence activité: " + cn.getStringId_frequence_activite();
        // Writing Contacts to log
        Log.d("Name: ", log);
    }

Aucun commentaire:

Enregistrer un commentaire