JDO : querying between classes without relation

Let’s say we have the following classes and are using RDBMS for persistence
public class Country
{
long id;
String name;
}

public class Region
{
long id;
String name;
Long countryId;
}

public class District
{
long id;
String name;
Long regionId;
}

So we ignored all good object-oriented guidance and didn’t use real relations. Now lets suppose I want to query for the Country that has a district called “Cross Gates”. We need to write some JDOQL, but don’t have any real relations … just countryId and regionId to navigate through. So we introduce 2 variables to navigate across the 2 “pseudo relations”.

Query q = pm.newQuery("SELECT FROM mydomain.Country " +
"WHERE this.id == reg.countryId && reg.id == dist.regionId && dist.name == 'Cross Gates' " +
"VARIABLES mydomain.Region reg; mydomain.District dist;");

This creates the following SQL
SELECT DISTINCT 'mydomain.Country' AS NUCLEUS_TYPE,A0.ID,A0."NAME"
FROM COUNTRY A0
CROSS JOIN REGION VAR_REG ON A0.ID = VAR_REG.COUNTRYID
CROSS JOIN DISTRICT VAR_DIST ON VAR_REG.ID = VAR_DIST.REGIONID
WHERE VAR_DIST."NAME" = 'Cross Gates'

In our particular case we know that there are no NULL pseudo relations, so would like “INNER JOIN” for performance. So we make use of a DataNucleus extension, like this

Query q = pm.newQuery("... query as before ...");
q.addExtension("datanucleus.query.jdoql.reg.join", "INNERJOIN");
q.addExtension("datanucleus.query.jdoql.dist.join", "INNERJOIN");

So we set joins for both variables to INNER JOIN, and the SQL becomes

SELECT DISTINCT 'mydomain.Country' AS NUCLEUS_TYPE,A0.ID,A0."NAME"
FROM COUNTRY A0
INNER JOIN REGION VAR_REG ON A0.ID = VAR_REG.COUNTRYID
INNER JOIN DISTRICT VAR_DIST ON VAR_REG.ID = VAR_DIST.REGIONID
WHERE VAR_DIST."NAME" = 'Cross Gates'

Easy really 😉 Would clearly have been easier if we had designed our classes around good O-O practice though. And if using some other type of datastore then your datastore would have to allow joins to even attempt this

Advertisements
This entry was posted in JDO, JDOQL. Bookmark the permalink.

6 Responses to JDO : querying between classes without relation

  1. chescot2302 says:

    public class Country
    {
    long id;
    String name;
    }

    public class Region
    {
    long id;
    String name;
    Long contryId;;
    }

    public class District
    {
    long id;
    String name;
    long regionId;
    }

    country 1 – N Region; Region 1 -N District

    It would be like?

    Like

  2. public Collection getListarBeans() throws UnknownException {
    Query query = pm.newQuery(“SELECT FROM com.lg.server.model.beans.Departamento WHERE this.codePais==pai.idPais VARIABLES Pais pai import com.lg.server.model.beans.Pais”);
    try{
    List lista=new ArrayList();
    lista.addAll((List)query.execute());
    return lista;
    }catch(Exception ex){
    throw new UnknownException(ex.getMessage());
    }finally{
    query.closeAll();
    }
    }

    This query does not work in google datastore

    Like

    • datanucleus says:

      Nowhere did I say that it would work in GAE/Datastore (the text in BOLD that says RDBMS should be enough). DataNucleus can only do what the datastore allows it to do. And for the final time for people reading this blog … the DataNucleus project is absolutely nothing to do with Google’s plugin for GAE/Datastore (talk to Google for support on that if you can find them).

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s