Help regarding Logic for sqlite database

[Pramod P Deore]

I am developing an application for Android platform. And I am stuck at one point.This question is not about android specific therefore I am asking it here.

At the start of application I a showing a list of rooms (i.e list containing room names) to the user. User select one room name from this list. Now I want to store this selected item to database. For that I had created one table name as RoomTable. which has 3 columns as - RoomID (primary key), RoomName, and RoomSuffix.

So let suppose user selected room name as Hall then inside database entry becomes as

RoomID RoomName RoomSuffix
1 Hall 0
2 Kitchen 0
3 Hall1 1
4 Hall2 2
5 Bedroom 0

Means suppose user select second time again room name as Hall then it must becomes roomname+roomsuffix (i.e Hall1, Hall2... )

For this I had write following code

public void onCreate(Bundle savedInstanceState)
	{
     ............
    ..............

roomList.setOnItemClickListener(new OnItemClickListener()
		{
			@Override
			public void onItemClick(AdapterView<?> parent, View view, int position,long id)
			{
				roomName = (String) roomList.getItemAtPosition(position);
				System.out.println ("User click on "+roomList.getItemAtPosition(position));
				
				if (roomName.equalsIgnoreCase("other"))
				{
					System.out.println ("Inside if condition. i.e User click on other room");
				}
				
				insertIntoRoomTable(roomName);
			}
		});

public void insertIntoRoomTable(String rnm)
	{
		sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
                ROOM_TABLE_NAME +
                " (RoomID integer primary key autoincrement,RoomName VARCHAR,RoomSuffix integer);");
		
		boolean roomExist = checkRoomExist(rnm);
		System.out.println ("checkRoomExist: "+roomExist);
		
		if (roomExist)
		{
			rsuf = getRoomSuffix();
			++rsuf;
			System.out.println ("******************"+rsuf);
			
		}
		else
		{
			rsuf=0;
		}
		rnm = rnm+rsuf;
		
		sampleDB.execSQL("INSERT INTO " +
    			ROOM_TABLE_NAME +
    			" Values (null,'"+rnm+"','"+rsuf+"');");
        	
    	System.out.println ("Inserted values into RoomTable are"+rnm+":"+rsuf);
	}
	
	public boolean checkRoomExist(String room)
	{
		Cursor c = sampleDB.rawQuery("SELECT RoomName FROM " +ROOM_TABLE_NAME , null);
		if (c != null )
    	{
    		System.out.println ("%%%%%%%%%%%"+c.getCount());
    		if  (c.moveToFirst())
    		{
    			do
    			{ 
    				rname = c.getString(c.getColumnIndex("RoomName"));
    				System.out.println ("RoomName is"+rname);
    				
    				if (rname.equalsIgnoreCase(room))
    				{
    					System.out.println ("Room Exist");
    					return true;
    				}
    						
    				
    			}
    			while (c.moveToNext());
    		} 
    	}
		
    	c.close();
    	return false;
	}
	
	public int getRoomSuffix()
	{
		System.out.println ("Room name inside getRoomSufix is "+roomName);
		String temp = roomName.substring(0,roomName.length()-1);
		System.out.println ("Temp is"+temp);
		Cursor c = sampleDB.rawQuery("SELECT RoomSuffix FROM " +ROOM_TABLE_NAME +" where RoomName LIKE '"+temp+"'", null);
		if (c != null )
    	{
    		System.out.println ("Total from getRoomSuffix is"+c.getCount());
    		if  (c.moveToFirst())
    		{
    			do
    			{ 
    				roomsx = c.getInt(c.getColumnIndex("RoomSuffix"));
    				System.out.println ("RoomSuffix is"+roomsx);
    			}
    			while (c.moveToNext());
    		} 
    	}
		
    	c.close();
    	return roomsx;
	}
}
  

But this code doesn't give me the expected output. I got following output.

12-06 12:49:35.146: INFO/System.out(587): User click on Hall
12-06 12:49:35.187: INFO/System.out(587): %%%%%%%%%%%0
12-06 12:49:35.208: INFO/System.out(587): checkRoomExist: false
12-06 12:49:35.237: INFO/System.out(587): Inserted values into RoomTable areHall0:0
12-06 12:49:37.907: INFO/System.out(587): User click on Kitchen
12-06 12:49:37.927: INFO/System.out(587): %%%%%%%%%%%1
12-06 12:49:37.936: INFO/System.out(587): RoomName isHall0
12-06 12:49:37.957: INFO/System.out(587): checkRoomExist: false
12-06 12:49:37.987: INFO/System.out(587): Inserted values into RoomTable areKitchen0:0
12-06 12:49:39.616: INFO/System.out(587): User click on Hall
12-06 12:49:39.678: INFO/System.out(587): %%%%%%%%%%%2
12-06 12:49:39.678: INFO/System.out(587): RoomName isHall0
12-06 12:49:39.687: INFO/System.out(587): RoomName isKitchen0
12-06 12:49:39.727: INFO/System.out(587): checkRoomExist: false
12-06 12:49:39.759: INFO/System.out(587): Inserted values into RoomTable areHall0:0
12-06 13:00:52.988: DEBUG/dalvikvm(201): GC freed 7564 objects / 439416 bytes in 175ms
12-06 13:00:59.387: DEBUG/Email(201): *** synchronizeMailboxGeneric ***
12-06 13:13:21.437: INFO/System.out(587): User click on Hall
12-06 13:13:21.456: INFO/System.out(587): %%%%%%%%%%%3
12-06 13:13:21.487: INFO/System.out(587): RoomName isHall0
12-06 13:13:21.496: INFO/System.out(587): RoomName isKitchen0
12-06 13:13:21.517: INFO/System.out(587): RoomName isHall0
12-06 13:13:21.536: INFO/System.out(587): checkRoomExist: false
12-06 13:13:21.577: INFO/System.out(587): Inserted values into RoomTable areHall0:0
12-06 13:13:56.206: INFO/System.out(587): User click on Kitchen
12-06 13:13:56.239: INFO/System.out(587): %%%%%%%%%%%4
12-06 13:13:56.247: INFO/System.out(587): RoomName isHall0
12-06 13:13:56.257: INFO/System.out(587): RoomName isKitchen0
12-06 13:13:56.276: INFO/System.out(587): RoomName isHall0
12-06 13:13:56.286: INFO/System.out(587): RoomName isHall0
12-06 13:13:56.326: INFO/System.out(587): checkRoomExist: false
12-06 13:13:56.367: INFO/System.out(587): Inserted values into RoomTable areKitchen0:0
12-06 13:13:57.188: INFO/System.out(587): User click on Hall
12-06 13:13:57.207: INFO/System.out(587): %%%%%%%%%%%5
12-06 13:13:57.217: INFO/System.out(587): RoomName isHall0
12-06 13:13:57.227: INFO/System.out(587): RoomName isKitchen0
12-06 13:13:57.247: INFO/System.out(587): RoomName isHall0
12-06 13:13:57.247: INFO/System.out(587): RoomName isHall0
12-06 13:13:57.267: INFO/System.out(587): RoomName isKitchen0
12-06 13:13:57.287: INFO/System.out(587): checkRoomExist: false
12-06 13:13:57.347: INFO/System.out(587): Inserted values into RoomTable areHall0:0

[Rob Spoor]

To search the suffix you are searching for Hall, but Hall1 and Hall2 do not have Hall as room name.

If I were you I'd keep all room names as Hall, then either calculate the displayed room name or add a new field for the full room name.

So:
1)

RoomID   RoomName   RoomSuffix
1        Hall       0
2        Kitchen    0
3        Hall       1
4        Hall       2
5        Bedroom    0

In displaying, you append RoomSuffix to RoomName if it isn't 0.

2)

RoomID   RoomName   RoomSuffix   RoomFullName
1        Hall       0            Hall
2        Kitchen    0            Kitchen
3        Hall       1            Hall1
4        Hall       2            Hall2
5        Bedroom    0            Bedroom

The difference is when you calculate the full name. In 1) it's when displaying, in 2) it's when inserting into the database.

Now you can search for RoomName to find the RoomSuffix.

[Pramod P Deore]

Thanks Rob, you are right. After adding one more column it works fine. Thanks once again.

[Rob Spoor]

You're welcome.