Getting item by a primary key in SQLContainer

Hi,

I would like to as for some help with the following issue:

I create an SQLContainer using TableQuary:


TableQuery q2 = new TableQuery("Users", connectionPool);
            usersContainer = new SQLContainer(q2);

As I understand a primary key from the DB is used to create IDs in the container. So now I want to access items in the container by their IDs.

If I am not mistaken I can not use getIdByIndex as it returns an ID based on container index. If I try using getItem by providing integer as an ID, I get casting error.

It should be peaty simple but I got lost. Thanks for help in advance.

Hi,

if your database is correctly configured (I mean that there is at least one primary key column defined) the item IDs you get from SQLContainer should be of type RowId. You can call getId() for this RowId and you will get back an Object array containing the value(s) of the primary key column(s).

So in reverse, if you know the primary key of the item in the database, you could get it like this (assumes you have one primary key column in the table):

SQLContainer.getItem(new RowId(new Object[]{"foo"}));

where “foo” is the primary key value of the row you want to fetch.

-Tepi

Wow, that was quick and precisely what I needed!

Amazing work is that your are doing there.

Thanks.

Hi,

Object cityItemId1= cityContainer.getItem(new RowId(new Object{cityId}));

cityItemId1 contains value. but when i call below line NullPointerException is thrown.

cityContainer.getItem(cityItemId1).getItemProperty(“name”).getValue().toString();

Please help in this regards

Hi,

can you determine in which call the NPE is thrown? Could it be that the value you are calling toString() for is null?

-tepi

Hi,

Thanks for the reply.

I can’t figure out where NPE is thrown ? But i have broken that one line code into two line as follows. and it works for me to get the property value.

    Object objectId = moduleContainer.getItem(new RowId(new Object { id }));
    if (objectId instanceof RowItem) {
        RowItem item = (RowItem) objectId;
        return item.getItemProperty("CONTENT").toString();
    }

now getting the value of content property.

Hi Tepi,

For updating the database table i am using following code snippet.

    Object objectId = moduleContainer
            .getItem(new RowId(new Object[] { id }));
    RowItem item = (RowItem) objectId;
    item.getItemProperty("CONTENT").setValue(content);
    try {
        moduleContainer.commit();

Please let me know if you have best practise other than this.

Thanks in advance
Hemant

I´d like to comment on this one.
I had exactly the same problem having the following situation getting a NPE and I don´t know how this is supposed to be treated.

// new item and commit
RowItem newItem = mySQLContainer.getItem(mySQLContainer.addItem());
newItem.getItemProperty(“XY”).setValue(“abc”);
mySQLContainer.commit()

so far ok! I even implemented the addRowIdChangeListener method to get the actual item id from the DB

addRowIdChangeListener(new QueryDelegate.RowIdChangeListener() {
@Override
public void rowIdChange(RowIdChangeEvent event) {
newItemId = event.getNewRowId();

}}

Ever attempt to get this newly commited item by using the new itemID failed, null is returned
Object o = mySQLContainer.getItem(newItemId.getId()[0]
));
or
Object o = mySQLContainer.getItem(new RowId(new Object {theLongValueForTheId})

only way with success was
Object o = mySQLContainer.getItem(mySQLContainer.lastItemId());

cheers