CODEWITHSUNDEEP
javaandroiddatabasesqlite
Seenu69
Seenu69

Reputation: 1054

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..

Upvotes: 1

Views: 3963

Answers (1)

Vladimir Mironov
Vladimir Mironov

Reputation: 30874

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);
    }
}

Upvotes: 2

Related Questions