PlayFramework get field from model select

Question

Since the play documentation on models is terrible I'll ask here. I have the basic code;

public static void Controller() {
        List<Item> item = Item.find("SELECT itemname,id FROM Item WHERE itembool = true ORDER BY itemcreated ASC LIMIT 0,1").fetch();
        
        if ( item == null ) {
            notFound();
        }
    }

What I'm trying to do is get the value for 'itemname' returned for the first value returned from an SQL query (The real query is much more complicated and other things so it can't be replaced with methods). I can get the entire first object with item.get(0) but I can't figure out how to get the value of 'itemname' as a string and it doesn't seem to be documented anywhere.

Edit

Probably should have mentioned in the original question, I need to retrieve by field name, not index. I.E. I can't do items.get(0)[0]; I need to do items.get(0)['itemname'];

Answer 1

The documentation explains this if you read it, in here. Hibernate doesn't use SQL, but JPQL, which has a different syntax as it works with objects, not individual fields.

What you want to do can be achieved in two ways (both in the documentation):

List<Item> item = Item.find("SELECT i FROM Item i WHERE i.itembool = true ORDER BY i.itemcreated ASC").fetch(1);

List<Item> item = Item.find("itembool = true ORDER BY itemcreated ASC").fetch(1);

EDIT:

On the retrieval part, you will get a list of Item, so you can just access the field directly on the object:

item.get(0).getItemName();

Answer 2

Since Play uses Hibernate under the hood, you need to take a look at Hibernate's documentation.

In particular, SELECT itemname,id ... yields Object[] rather than Item, so that you can get itemname as follows:

List<Object[]> items = ...;
String itemname = items.get(0)[0];