Apache-Cayenne Custom Query Results

To preface my question, I'm in school working on a project that has been previously worked on in past semesters. So the choices made in the design and development were something I am not 100% sure on and so I cannot comment on the reasoning behind why things are set up how they are. I also looked through all of the questions on Apache-Cayenne tag and could not find something that addresses this. Please inform me if I'm mistaken on that and I'll gladly check it out.

Essentially, we have a table that has a plethora of columns and I only want a few of those columns to be loaded (otherwise there is way too much unnecessary data being queried and it takes the page a lot longer to load).

As an example to keep it generic, let's say I have a table with object that have 10 columns. I want only 3 of the columns. So in my code I would have this:

public List<objectToQuery> getSpecificColumns() {

ObjectContext context = getNewContext();

SQLTemplate select = new SQLTemplate(objectToQuery.class, "select column1, column2, column3 from table");

List<objectToQuery> results = context.performQuery(select);

return results;
}

And when I run this, it works and returns the proper amount of rows. However, when I try to interact with and retrieve data, it will run individual queries on every row that includes all columns anyways.

So for examle if on the page that gets the results I would have:

object.getColumn1();

It will run a query on the object for all of the columns... Why does this happen and is there any way to accomplish what I'm trying to do?

Many thanks, -R

Solution

In Cayenne you can't fetch a partial object. Or rather you can fetch it as long as its ID columns are present, but the object will be in a "hollow" state. Then whenever you try to read a property of such object, it will be lazily resolved from DB (exactly what you are observing). Partial object fetch feature was requested recently, so this may change soon.

Now you have two options:

Use full objects (via ObjectSelect query instead of SQLTemplate assuming you are on 4.0 or newer), as most often than not you don't care about micro-optimizations.
Use column select (ObjectSelect.columnQuery(..)), that will produce a result that is a List of Object[], where each array contains values of the specified columns for a single DB row.

In the former case you preserve the object model sacrificing some extra memory (and perhaps fetch speed), in the latter - you optimize your query, but work with raw data. Programming is often about trade-offs after all :)