Multi-table database SQLite android

Question

I'm new with database (SQLite) and I'm trying to do a multi-table database in android. Each table has the same rows (id, name , ph_number). Every tables store a "contact" object.

When I want to store a contact in one table with the method addContactDatos(Contact contact, int partido), it's stored in every tables not just in the one I want. How can I solve it?

This is the Handler:

public class DatabaseHandler extends SQLiteOpenHelper {

// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "contactsManager";

// Contacts Tables  names
private static final String TABLE_PRIMERO = "contacts";
private static final String TABLE_SEGUNDO = "contacts";
private static final String TABLE_TERCERO = "contacts";  
private static final String TABLE_CUARTO = "contacts";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PH_NO = "phone_number";

int oldVersion=1;

public DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_CONTACTS_TABLE1 = "CREATE TABLE_PRIMERO " + TABLE_PRIMERO + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_PH_NO + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE1);


    String CREATE_CONTACTS_TABLE2 = "CREATE TABLE_SEGUNDO " + TABLE_SEGUNDO + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_PH_NO + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE2);


    String CREATE_CONTACTS_TABLE3 = "CREATE TABLE_TERCERO " + TABLE_TERCERO + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_PH_NO + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE3);


    String CREATE_CONTACTS_TABLE4 = "CREATE TABLE_CUARTO " + TABLE_CUARTO + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
            + KEY_PH_NO + " TEXT" + ")";
    db.execSQL(CREATE_CONTACTS_TABLE4);    
}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PRIMERO);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_SEGUNDO);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_TERCERO);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CUARTO);

    // Create tables again
    onCreate(db);
}

public void addContactDatos(Contact contact, int partido) {

    SQLiteDatabase db = this.getWritableDatabase();

    // Inserting Row
    if (partido == 1) {
        ContentValues values = new ContentValues();
        values.put(KEY_NAME, contact.getName()); // Contact Name
        values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number

        db.insert(TABLE_PRIMERO, null, values);
        db.close(); // Closing database connection
        Log.d("Entra","MAL");
    }

 // Inserting Row
    if (partido == 2) {
         ContentValues values = new ContentValues();
         values.put(KEY_NAME, contact.getName()); // Contact Name
         values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number
        db.insert(TABLE_SEGUNDO, null, values);
        db.close(); // Closing database connection
        Log.d("Entra","2");
              }

 // Inserting Row
    if (partido == 3) {
         ContentValues values = new ContentValues();
         values.put(KEY_NAME, contact.getName()); // Contact Name
         values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number
        db.insert(TABLE_TERCERO, null, values);
        db.close(); // Closing database connection
        Log.d("Entra","MAL");
    }

 // Inserting Row
    if (partido == 4) {
        ContentValues values = new ContentValues();
        values.put(KEY_NAME, contact.getName()); // Contact Name
        values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone Number
        db.insert(TABLE_CUARTO, null, values);
        db.close(); // Closing database connection
        Log.d("Entra","MAL");
    }



}


// Getting All Contacts
public List<Contact> getAllContacts(int partido) {

   List<Contact> contactList = new ArrayList<Contact>();
   String selectQuery="";
// Select All Query
   SQLiteDatabase db = this.getWritableDatabase();
   Cursor cursor = db.rawQuery(selectQuery, null); 

   switch (partido){
    case 1:
        selectQuery = "SELECT  * FROM " + TABLE_PRIMERO;  

         // looping through all rows and adding to list
         if (cursor.moveToFirst()) {
             do {
                 Contact contact = new Contact();
                 contact.setID(Integer.parseInt(cursor.getString(0)));
                 contact.setName(cursor.getString(1));
                 contact.setPhoneNumber(cursor.getString(2));
                 // Adding contact to list
                 contactList.add(contact);
             } while (cursor.moveToNext());
         }
    break;

    case 2:
        selectQuery = "SELECT  * FROM " + TABLE_SEGUNDO;   

         // looping through all rows and adding to list
         if (cursor.moveToFirst()) {
             do {
                 Contact contact = new Contact();
                 contact.setID(Integer.parseInt(cursor.getString(0)));
                 contact.setName(cursor.getString(1));
                 contact.setPhoneNumber(cursor.getString(2));
                 // Adding contact to list
                 contactList.add(contact);
             } while (cursor.moveToNext());
         }
        break;

    case 3:
        selectQuery = "SELECT  * FROM " + TABLE_TERCERO;   

         // looping through all rows and adding to list
         if (cursor.moveToFirst()) {
             do {
                 Contact contact = new Contact();
                 contact.setID(Integer.parseInt(cursor.getString(0)));
                 contact.setName(cursor.getString(1));
                 contact.setPhoneNumber(cursor.getString(2));
                 // Adding contact to list
                 contactList.add(contact);
             } while (cursor.moveToNext());
         }
        break;

    case 4:
        selectQuery = "SELECT  * FROM " + TABLE_CUARTO;   


         // looping through all rows and adding to list
         if (cursor.moveToFirst()) {
             do {
                 Contact contact = new Contact();
                 contact.setID(Integer.parseInt(cursor.getString(0)));
                 contact.setName(cursor.getString(1));
                 contact.setPhoneNumber(cursor.getString(2));
                 // Adding contact to list
                 contactList.add(contact);
             } while (cursor.moveToNext());
         }
        break;

  }


   // return contact list
   return contactList;
}

public void deleteDB() {

    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_PRIMERO, null, null);
    db.delete(TABLE_SEGUNDO, null, null);
    db.delete(TABLE_TERCERO, null, null);
    db.delete(TABLE_CUARTO, null, null);

}

And this is the object model contact: public class Contact {

 //private variables
int _id;
String _name;
String _phone_number;

// Empty constructor
public Contact(){

}
// constructor
public Contact(int id, String name, String _phone_number){
    this._id = id;
    this._name = name;
    this._phone_number = _phone_number;
}

// constructor
public Contact(String name, String _phone_number){
    this._name = name;
    this._phone_number = _phone_number;
}
// getting ID
public int getID(){
    return this._id;
}

// setting id
public void setID(int id){
    this._id = id;
}

// getting name
public String getName(){
    return this._name;
}

// setting name
public void setName(String name){
    this._name = name;
}

// getting phone number
public String getPhoneNumber(){
    return this._phone_number;
}

// setting phone number
public void setPhoneNumber(String phone_number){
    this._phone_number = phone_number;
}
}

Is ther any way to do wat I want . I would really appreciate a little help. Thank you very much.

Coolth

Answer 1

You MUST use DIFFERENT TABLE NAMES!!

i.e.:

// Contacts Tables  names
private static final String TABLE_PRIMERO = "contacts_1";
private static final String TABLE_SEGUNDO = "contacts_2";
private static final String TABLE_TERCERO = "contacts_3";  
private static final String TABLE_CUARTO = "contacts_4";

Otherwise, you are always working on the same table (the only one you create), named "contacts"

Sunburn

I tried it but it did not work as the tables weren't created. Do I have to do a different object model (contact_1, contact_2, contact_3, contact_4) for each table? or just one? – Coolth

You don't need to differentiate the model. But then, what is the need of having 4 tables, if you can live better with only one? If you add a numeric field, say "id_partido", you can then insert and retrieve values that relate to just that partido... because if you have 1000 partidos, with your logic, you should make 1000 tables - which is not healthy... – Oyster

Answer 2

All your table names are the same one

private static final String TABLE_PRIMERO = "contacts";
private static final String TABLE_SEGUNDO = "contacts";
private static final String TABLE_TERCERO = "contacts";  
private static final String TABLE_CUARTO = "contacts";

here you don't have multi-table you just one table named contacts

change table name

private static final String TABLE_PRIMERO = "contacts_primero";
private static final String TABLE_SEGUNDO = "contacts_segundo";
private static final String TABLE_TERCERO = "contacts_tercero";  
private static final String TABLE_CUARTO = "contacts_cuarto";

Danyel