join tables sqlite android

Question

I have two tables: user and balance. User DAO:

public class NewUserDAO {
    public static final String TAG = "NewUserDAO";
    public static final String TABLE_NEWUSER = "newUser";
    //database fields
    private SQLiteDatabase mDataBase;
    private DatabaseHandler mDbHelper;
    private Context mContext;
    private String [] mAllColumns = {
            DatabaseHandler.COLUMN_NEWUSER_ID,
            DatabaseHandler.COLUMN_NEWUSER_NAME, DatabaseHandler.COLUMN_NEW_USER_PASSWORD,
            DatabaseHandler.COLUMN_NEW_USER_AGE };

    public NewUserDAO(Context context){
        this.mContext = context;
        mDbHelper = new DatabaseHandler(context);
        try{
            open();
        } catch (SQLException e){
            Log.e(TAG,"SQLexception on opening database" + e.getMessage());
            e.printStackTrace();
        }
    }
    public void open() throws SQLException{
        mDataBase = mDbHelper.getWritableDatabase();
    }
    public void close(){
        mDbHelper.close();
    }
    public void createNewUser(NewUserTable newUserTable){
        ContentValues values = new ContentValues();
        values.put(DatabaseHandler.COLUMN_NEWUSER_NAME,newUserTable.getName());
        values.put(DatabaseHandler.COLUMN_NEW_USER_PASSWORD, newUserTable.getPassword());
        values.put(DatabaseHandler.COLUMN_NEW_USER_AGE, newUserTable.getAge());
        mDataBase.insert(TABLE_NEWUSER, null, values);
        mDataBase.close();
    }

}

balance DAO:

public class BalanceDAO {
public static final String TAG = "BalanceDAO";
public static final String TABLE_BALANCE = "balanceOfUser";
private Context mContext;

//Database fields
private SQLiteDatabase mDatabase;
private DatabaseHandler mDhelper;
private String[] mAllColumns = {
        DatabaseHandler.COLUMN_BALANCE_ID,
        DatabaseHandler.COLUMN_BALANCE_DOLLARBALANCE,
        DatabaseHandler.COLUMN_BALANCE_RUBBALANCE,
        DatabaseHandler.COLUMN_BALANCE_NEW_USER_ID
};
public BalanceDAO (Context context){
    mDhelper = new DatabaseHandler(context);
    this.mContext = context;
    try{
        open();
    }
    catch (SQLException e){
        Log.e(TAG, "SQLException on openning database" + e.getMessage());
        e.printStackTrace();
    }
}
public void open() throws SQLException {
    mDatabase = mDhelper.getWritableDatabase();
}
public void close(){
    mDhelper.close();
}
public void createBalance (BalanceTable balanceTable){
    ContentValues values = new ContentValues();
    values.put(DatabaseHandler.COLUMN_BALANCE_DOLLARBALANCE,balanceTable.getDollarBalance());
    values.put(DatabaseHandler.COLUMN_BALANCE_RUBBALANCE,balanceTable.getRubBalance());
    mDatabase.insert(TABLE_BALANCE, null, values);
    mDatabase.close();
 }
}

And SQLiteOpenHelper class:

public class DatabaseHandler extends SQLiteOpenHelper {

 //COLUMNS OF THE NEW USER TABLE
public static final String TABLE_NEWUSER = "newUser";
public static final String COLUMN_NEWUSER_ID = "id";
public static final String COLUMN_NEWUSER_NAME = "name";
public static final String COLUMN_NEW_USER_PASSWORD = "password";
public static final String COLUMN_NEW_USER_AGE = "age";

//COLUMNS OF THE BALANCE TABLE
public static final String COLUMN_BALANCE_ID = "id";
public static final String TABLE_BALANCE = "balanceOfUser";
public static final String COLUMN_BALANCE_DOLLARBALANCE = "dollarBalance";
public static final String COLUMN_BALANCE_RUBBALANCE = "rubBalance";
public static final String COLUMN_BALANCE_NEW_USER_ID = "newUserId";

private static final String DATABASE_NAME = "webStore";
private static final int DATABASE_VERSION = 1;


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

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    if (!db.isReadOnly()) {
        // Enable foreign key constraints
        db.execSQL("PRAGMA foreign_keys=ON;");
    }
}

@Override
public void onCreate(SQLiteDatabase db) {
    String SQL_CREATE_NEWUSER = "CREATE TABLE " + TABLE_NEWUSER + "("
            + COLUMN_NEWUSER_ID + " INTEGER PRIMARY KEY autoincrement,"
            + COLUMN_NEWUSER_NAME + " TEXT not null,"
            + COLUMN_NEW_USER_PASSWORD + " TEXT not null,"
            + COLUMN_NEW_USER_AGE + " INTEGER"
            + ")";
    db.execSQL(SQL_CREATE_NEWUSER);
    String SQL_CREATE_BALANCE = "CREATE TABLE " + TABLE_BALANCE + "("
            + COLUMN_BALANCE_ID + " INTEGER PRIMARY KEY autoincrement,"
            + COLUMN_BALANCE_DOLLARBALANCE + " INTEGER,"
            + COLUMN_BALANCE_RUBBALANCE + " INTEGER,"
            + COLUMN_BALANCE_NEW_USER_ID + " INTEGER," + "FOREIGN KEY("+COLUMN_BALANCE_NEW_USER_ID+") REFERENCES "
            + TABLE_NEWUSER + "(id) "+ ")" ;
    db.execSQL(SQL_CREATE_BALANCE);
    onCreate(db);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NEWUSER);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_BALANCE);
 }
}

I want to join user and balance tables. How can i do? When i call Create Method, i have exception.

public void CreateUser(View view) {
    etName = (EditText)findViewById(R.id.etName);
    etPassword = (EditText)findViewById(R.id.etPassword);
    etAge = (EditText)findViewById(R.id.etAge);
    String name = String.valueOf(etName.getText());
    String password = String.valueOf(etPassword.getText());
    int age = Integer.parseInt(String.valueOf(etAge.getText()));
    BalanceTable balanceTable = new BalanceTable(0,0);
    NewUserTable newUserTable = new NewUserTable(name,password,age);
    //write to database of user from our edit texts
    DatabaseHandler databaseHandler = new DatabaseHandler(this);
    Log.d("Insert: ", "Inserting ..");
    NewUserDAO dbForUser = new NewUserDAO(this);
    dbForUser.createNewUser(newUserTable);
    BalanceDAO balanceDAO = new BalanceDAO(this);
    balanceDAO.createBalance(balanceTable);
}

From edit text i take data. Help please

Answer 1

There are a few types if join, inner (which I think is what you want) which shows the data that matches, left showing all data from the first table and any additional data from the second table that meets the on condition or right which is shows all from the second table and any form the right that matches.

to Join two table you must use a statement such as

"SELECT <all the values you want> "+  
 " FROM users" +
 " left join balance" +
 " ON users.id=balance.userid " +
 " where <some condition> "+
 " order by gmttimestamp ;"; "

this will give you result set of mixed tables