Seenu69 Seenu69 - 28 days ago 11
Java Question

How do I create and operate two tables in one database for an Android application

I'm developing a simple shopping application for Android.
The app requires two SQLite database tables, but I didn't create them at the same time(created only one initially for the MainActivity).
After searching on the web I have learnt that we have to create the multiple tables for the database at the same time using SQLiteOpenHelper class. Is that statement true?

If not please check my code below and suggest solution for my error--

The app has two activities, MainActivity.java, and ItemsActivity.java, one DatabaseAdapter(DBAdapter.java) class. DatabaseAdapter class have two table, MainActivity class uses first table named TABLE_NAME, ItemsActivity class uses the second table named TABLE_NAME_ITEMS from DBAdapter class.

ItemsActivity.java is the class through which I insert items into the second table named TABLE_NAME_ITEMS in the DBAdapter.
And here is ItemsActivity.java class

public class ItemsActivity extends Activity implements OnClickListener{
DBAdapter dbAdapterItems;
String listId;
//Cursor cItems;
//MyItemsAdapter adapter;
int position;

@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.items_activity);

dbAdapterItems = new DBAdapter(this);
dbAdapterItems.openDatabase();

Intent itemsActivity = getIntent();
Bundle b = itemsActivity.getExtras();
listId = b.getString("LIST_ID");


Button addItem = (Button) findViewById(R.id.addItemButton);
/* ListView itemslist = (ListView)findViewById(R.id.itemsListView);

cItems = dbAdapter.getAllItemRecords(listId);
adapter = new MyItemsAdapter();
itemslist.setAdapter(adapter);

**/
addItem.setOnClickListener(this);

}

@Override
public void onClick(View v) {
// TODO Auto-generated method stub

// TODO Auto-generated method stub
final Dialog d = new Dialog(ItemsActivity.this);
d.setTitle("Add Item");
d.setContentView(R.layout.customdialog);
d.show();

final EditText itemNameEt = (EditText) d.findViewById(R.id.dialogEditText);
Button addButton = (Button) d.findViewById(R.id.dialogAddButton);
Button cancelButton = (Button) d.findViewById(R.id.dialogCancelButton);

addButton.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View v) {
// TODO Auto-generated method stub

String itemName = itemNameEt.getText().toString();
dbAdapterItems.insertItemsRecord(itemName,listId);
Toast.makeText(ItemsActivity.this, "Item name: "+itemName+" added", Toast.LENGTH_LONG).show();
d.dismiss();
}
});
cancelButton.setOnClickListener(new OnClickListener() {

@Override
public void onClick(View v) {
// TODO Auto-generated method stub
d.dismiss();
Toast.makeText(ItemsActivity.this, listId, Toast.LENGTH_LONG).show();
}
});

}
/**
class MyItemsAdapter extends BaseAdapter{

@Override
public int getCount() {
// TODO Auto-generated method stub
return cItems.getCount();
}

@Override
public Object getItem(int arg0) {
// TODO Auto-generated method stub
return null;
}

@Override
public long getItemId(int arg0) {
// TODO Auto-generated method stub
return 0;
}

@Override
public View getView(int position, View view, ViewGroup parent) {
// TODO Auto-generated method stub
if(view==null){
LayoutInflater inflater = getLayoutInflater();
view = inflater.inflate(R.layout.custom_items_row, parent, false);
}

TextView itemsRowTv = (TextView) view.findViewById(R.id.customitemsrowTV);

cItems.moveToPosition(position);
String itemName = cItems.getString(1);
itemsRowTv.setText(itemName);

return view;
}

}
**/
}


Here is DBAdapter.java class

public class DBAdapter {

String DATABASE_NAME = "SeenuDB";
String TABLE_NAME = "listTable"; //First Table Name
int DATABASE_VERSION = 1;

String TABLE_NAME_ITEMS = "itemsTable"; //Second Table Name
String COLUMN_ITEMS_ONE = "rowitemsid";
String COLUMN_ITEMS_TWO = "itemname";
String COLUMN_ITEMS_THREE = "listid";

public static final String COLUMN_ONE = "rowid";
public static final String COLUMN_TWO = "listname";

SQLiteDatabase db;
Context context;
DBHelper dbHelper;

String CREATE_TABLE = "create table if not exists listTable(rowid integer primary key autoincrement,listname text not null)";
String CREATE_TABLE_ITEMS = "create table if not exists " +TABLE_NAME_ITEMS+ "(rowitemsid integer primary key autoincrement,itemname text not null,listid text not null);";

public DBAdapter(Context c) {
// TODO Auto-generated constructor stub
this.context = c;
dbHelper = new DBHelper(context);
}

class DBHelper extends SQLiteOpenHelper{

public DBHelper(Context context) {
// TODO Auto-generated constructor stub
super(context,TABLE_NAME,null,DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(CREATE_TABLE);
db.execSQL(CREATE_TABLE_ITEMS);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME_ITEMS);
onCreate(db);
}

}

DBAdapter openDatabase(){
db = dbHelper.getWritableDatabase();
return this;
}

void closeDatabase(){
dbHelper.close();
}


long insertRecord(String listname){
ContentValues con = new ContentValues();
con.put(COLUMN_TWO, listname);
return db.insert(TABLE_NAME, null, con);
}


Cursor getAllRecords(){
String[] columns = {COLUMN_ONE,COLUMN_TWO};
return db.query(TABLE_NAME, columns, null, null, null, null, null);
}

void deleteAllRecords(){
db.delete(TABLE_NAME, null, null);
}

void deleteOneRecord(String rowid){
db.delete(TABLE_NAME, rowid+"="+COLUMN_ONE, null);
}



long insertItemsRecord(String itemname,String listid){
ContentValues con = new ContentValues();
con.put(COLUMN_ITEMS_TWO, itemname);
con.put(COLUMN_ITEMS_THREE, listid);
return db.insert(TABLE_NAME_ITEMS, null, con);
}

Cursor getAllItemRecords(String listid){
String[] columns = {COLUMN_ITEMS_ONE,COLUMN_ITEMS_TWO,COLUMN_ITEMS_THREE};
return db.query(TABLE_NAME_ITEMS, columns, listid+"="+COLUMN_ITEMS_THREE, null, null, null, null);
}
}


Using the first table I'm able to perform all the CURD operations, but while using the second table to perform insert operations I'm getting Error no such table itesTable in the LogCat.

Hope the reader have understood my problem, kindly provide solution for me..

Answer

we have to create the multiple tables for the database at the same time using SQLiteOpenHelper class

You can change the database scheme at any time you want. Just increase DATABASE_VERSION to trigger onUpgrade callback where you can do all the stuff related to the schema changes.

In your code you can set DATABASE_VERSION to 2 and it should work well.

int DATABASE_VERSION = 2;

But it recreates an entire database and you will lose all existing data. You can handle this case in more appropriate way:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion == 1 && newVersion == 2) {
        db.execSQL(CREATE_TABLE_ITEMS);
    }
}