Table/SQLContainer questions

Not sure if this is a UI or data binding question… or perhaps both…

I have a database table which I am reading into an SQLContainer, and then displaying it in a Table. And that works.

But there are a couple of columns in the database which are foreign key values - and I would like the UI table to show the joined value. In other words, the database table has a ‘UserId’ field - an integer, and I would like to show the UserName field from the Users database table, which is indexed by the ‘UserId’ in my original table. I have at least 2 of these foreign key columns. I saw something about this in one of the examples, but it was creating the data programmatically (unless I misunderstood the example) and I’m not sure how to make this happen using the database calls. Can someone give me some pointers to documentation, and/or code samples on how to do this?

Related question - once I have the UI column showing Support Group (my second foreign key field), I would like to make that column editable, and use a drop-down selector rather than making the user type in a complete string. Can someone point me at an example of doing this as well, also using the database to supply the data?

I can provide more details on the tables if that is necessary…

Thanks very much,

nbc

If you are willing to express your queries using JPA Criteria Queries, you can do that using the BeanTupleContainer from the JPA Criteria Container add-on – it allows you to retrieve and display arbitrary columns from a query.

Hi - I’d consider the JPA route, but I have not looked at that yet. I’ve used Hibernate with Netbeans, but most of the actual database work is hidden by the IDE there, so I don’t know much about using those types of libraries. And I’m afraid I don’t see how that would help me in this situation - how exactly would that work?

Thanks,

nbc

If (say) a person performs a task, and you have a doer id in your task, you would join person and task over the doerId.
Then you would select whatever attributes of the person and task you wish.

Using the standard JPA 2.0 Criteria API you’d write this something like the following (I’ve left out the type declarations to keep things short)

task = query.from(Task.class);
doer = tasks.join(Task_.doerId); // JPA knows that a doerId leads to a Person
query.multiselect(task,doer);

The JPA 2.0 Criteria container automatically makes available all the properties of the objects in each row of the join, so you could set your visible columns to
task.name, task.duration, doer.lastname, doer.firstname
(assuming of course that the Task and Person have these attributes inside them). You don’t have to define the properties to the container, it figures everything out through the information JPA makes available.

In this way, you can show the actual data from the entities and not the join keys (as you would do with a resultSet).

Thanks - I think I get the idea… I will have to look into this more closely…

Much obliged,

nbc