In android studio :how I can create two tables with references?

Question

I have project in android Studio which talk about Hotels and it have Database with two tables (Room , Customer). I try to create customer table with foreign key(Room_ID) but I have Errors . How I can create it ? Another problem , when i add a new room and go to (Show Rooms) activity , the first room which i added it don’t show , what is the problem ?

This is the Database class extends SqliteOpenHelper

  public DatabaseHelper(Context context) {
    super(context, "Hotel", null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
        db .execSQL(room.Create_table);
        db .execSQL(customer.Create_table);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("drop table " + room.Table_Name);
    db.execSQL("drop table " + customer.Table_Name);
    onCreate(db);
}
public  boolean insert_room (String name , String type , String price){
SQLiteDatabase db = getWritableDatabase() ;
    ContentValues data = new ContentValues();
    data.put(room.Col_Name,name);
    data.put(room.Col_Type,type);
    data.put(room.Col_Price,price);
    return db.insert(room.Table_Name,null ,data) >0 ;
 }
 public  boolean insert_customer (String name , String mobile , String 
 passport,int days,int room_id){
     SQLiteDatabase db = getWritableDatabase() ;

     ContentValues data = new ContentValues();
     data.put(customer.Col_Name,name);
     data.put(customer.Col_Passport,passport);
     data.put(customer.Col_Mobile,mobile);
     data.put(customer.Col_Days,days);
     data.put(customer.Col_Room_ID,room_id);

    return db.insert(customer.Table_Name,null ,data) >0 ;
}
public ArrayList<room> getAll_rooms(){
    SQLiteDatabase db = getWritableDatabase() ;

    ArrayList<room> rooms=new ArrayList<>();
    Cursor cursor = db.rawQuery("select * from " +room.Table_Name  ,null);
    if (cursor.moveToFirst()){
        while (cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex(room.Col_Id));
            String name =cursor.getString(cursor.getColumnIndex(room.Col_Name));
            String type =cursor.getString(cursor.getColumnIndex(room.Col_Type));
            String price =cursor.getString(cursor.getColumnIndex(room.Col_Price));
            room r =new room(id,name,type,price);
            rooms.add(r);
        }
    }
    cursor.close();
    return rooms;
}

public ArrayList<customer> getAll_customers(){
    SQLiteDatabase db = getWritableDatabase() ;

    ArrayList<customer> customers=new ArrayList<>();
    Cursor cursor = db.rawQuery("select * from " +customer.Table_Name ,null);
    if (cursor.moveToFirst()){
        while (cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex(customer.Col_Id));
            String name =cursor.getString(cursor.getColumnIndex(customer.Col_Name));
          int days =cursor.getInt(cursor.getColumnIndex(customer.Col_Days));
            String mobile =cursor.getString(cursor.getColumnIndex(customer.Col_Mobile));
            String passport =cursor.getString(cursor.getColumnIndex(customer.Col_Passport));
            int room_id =cursor.getInt(cursor.getColumnIndex(customer.Col_Room_ID));
            customer c =new customer(id,name,passport,mobile,days,room_id);
            customers.add(c);
        }
    }
    cursor.close();
    return customers;
}


 }

And this Is the Customer Class

      public class customer {
    int id ;
    String name ;
    String passport_num;
    String mobile;
    int num_ofDays;
    int room_id ;

    public  static  final String Col_Id = "id";
    public  static  final String Col_Name = "name";
    public  static  final String Col_Passport= "passport_num";
    public  static  final String Col_Days= "num_ofDays";
    public  static  final String Col_Mobile= "mobile";
    public  static  final String Col_Room_ID= "room_id";
    public  static  final String Table_Name= "Customer";
    public  static  final String Create_table = " create table Customer(id integer primary key autoincrement ," +
            " name text , passport_num text , mobile text , num_ofDays integer " +
            " room_id integer , FOREIGN KEY(room_id) references Room (id))";

    public customer() {
    }

    public customer(int id, String name, String passport_num, String mobile, int num_ofDays, int room_id) {
        this.id = id;
        this.name = name;
        this.passport_num = passport_num;
        this.mobile = mobile;
        this.num_ofDays = num_ofDays;
        this.room_id = room_id;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public String getPassport_num() {
        return passport_num;
    }

    public void setPassport_num(String passport_num) {
        this.passport_num = passport_num;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public int getNum_ofDays() {
        return num_ofDays;
    }

    public void setNum_ofDays(int num_ofDays) {
        this.num_ofDays = num_ofDays;
    }

    public int getRoom_id() {
        return room_id;
    }

    public void setRoom_id(int room_id) {
        this.room_id = room_id;
    }
}

This is 'Add Customer Calss'

    public class AddCustomer extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_add_customer);

        final DatabaseHelper db=new DatabaseHelper(this);
        final Spinner type = findViewById(R.id.room_type_add);
        final EditText name = findViewById(R.id.customer_name_add);
        final EditText mobile = findViewById(R.id.mobile_num_add);
        final EditText passport = findViewById(R.id.passport_num_add);
        final EditText days = findViewById(R.id.num_days_add);
        final Button add =findViewById(R.id.addCustomer);
        final ArrayList<room> spData=db.getAll_rooms();

        room_adapter_spinner adapter=new room_adapter_spinner(this,spData);
        type.setAdapter(adapter);
        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String Name = name.getText().toString();
                String Mobile = mobile.getText().toString();
                String Passport = passport.getText().toString();
                String Days = days.getText().toString() ;
                int Days_i = Integer.parseInt(Days);
                int TYpe=(int) type.getSelectedItemId();
                if (db.insert_customer(Name,Mobile,Passport,Days_i ,TYpe)) {
                    Intent i = new Intent(AddCustomer.this,ShowCustomers.class);
                    startActivity(i);
                }
                else Toast.makeText(AddCustomer.this, "error", Toast.LENGTH_SHORT).show();
            }
        });


    }

}

This is Add room class

public class AddRoom extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_add_room);
        final  DatabaseHelper db = new DatabaseHelper(this);
        final EditText name = findViewById(R.id.room_name_add);
        final Spinner type = findViewById(R.id.room_type_add);
        final EditText price = findViewById(R.id.room_price_add);
        final Button add =findViewById(R.id.addRoom);
       add.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View v) {
               String name_ = name.getText().toString();
              String type_s= type.getSelectedItem().toString();
               String price_ = price.getText().toString();
               if (db.insert_room(name_,type_s,price_)) {
                   Intent i = new Intent(AddRoom.this,ShowRooms.class);
                   startActivity(i);
               }
           }
       });

    }
}

This is (Show Rooms) class

public class ShowRooms extends AppCompatActivity {
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_show_rooms);

        final ListView list = findViewById(R.id.list_rooms);
        DatabaseHelper db = new DatabaseHelper(this) ;
        ArrayList<room> data = db.getAll_rooms();
        room_adapter adapter = new room_adapter(this,data);
        list.setAdapter(adapter);

    }
}

When I run the project I have this errror

 Caused by: android.database.sqlite.SQLiteException: unknown column "room_id" in foreign key definition (code 1): , while compiling: create table Customer(id integer primary key autoincrement , name text , passport_num text , mobile text , num_ofDays integer  room_id integer , FOREIGN KEY(room_id) references Room (id))
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
        at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
        at com.example.the_hotel_project.DatabaseHelper.onCreate(DatabaseHelper.java:23)
        at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
        at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
        at com.example.the_hotel_project.DatabaseHelper.getAll_rooms(DatabaseHelper.java:54)
        at com.example.the_hotel_project.AddCustomer.onCreate(AddCustomer.java:32)
        at android.app.Activity.performCreate(Activity.java:5104)
        at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
        at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
        at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230) 
        at android.app.ActivityThread.access$600(ActivityThread.java:141) 
        at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234) 
        at android.os.Handler.dispatchMessage(Handler.java:99) 
        at android.os.Looper.loop(Looper.java:137) 
        at android.app.ActivityThread.main(ActivityThread.java:5041) 
        at java.lang.reflect.Method.invokeNative(Native Method) 
        at java.lang.reflect.Method.invoke(Method.java:511) 
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793) 
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560) 
        at dalvik.system.NativeStart.main(Native Method) 

Answer 1

For the 2nd question (please make two separate question next time), you call moveToFirst() then while(moveToNext()). The cursor is at the first value when you call moveToFirst() and go to second position when you call moveToNext(). It is why your list starts from the second value.

What you can do is call moveToFirst() and invert the while loop :

if (cursor.moveToFirst()) {
    do {
        int id = cursor.getInt(cursor.getColumnIndex(room.Col_Id));
        String name = cursor.getString(cursor.getColumnIndex(room.Col_Name));
        String type = cursor.getString(cursor.getColumnIndex(room.Col_Type));
        String price = cursor.getString(cursor.getColumnIndex(room.Col_Price));
        room r = new room(id, name, type, price);
        rooms.add(r);
    } while (cursor.moveToNext());
}

Or check for the count and only call moveToNext()

if(cursor.getCount() != 0) {
    while(cursor.moveToNext()){
        int id = cursor.getInt(cursor.getColumnIndex(room.Col_Id));
        String name = cursor.getString(cursor.getColumnIndex(room.Col_Name));
        String type = cursor.getString(cursor.getColumnIndex(room.Col_Type));
        String price = cursor.getString(cursor.getColumnIndex(room.Col_Price));
        room r = new room(id, name, type, price);
        rooms.add(r);
    }
}

Use the same logic for getAll_customers()

Answer 2

You have a "," (comma) missing in the String Create_table of the Customer class. After num_ofDays.

It should be:

    public  static  final String Create_table = " create table Customer(id integer primary key autoincrement ," 
+ " name text , passport_num text , mobile text , num_ofDays integer, " 
+ " room_id integer , FOREIGN KEY(room_id) references Room (id))";