select * from project p, project_strategic_bucket psb where psb.project_id_oid = p.id
and psb.strategic_bucket_id_oid in (select sb.id from strategic_bucket sb where sb.id in (1, 2, 3, ...)) order by p.prj_nr;
(An alternative SQL statement that also works is:
select * from project p inner join project_strategic_bucket psb on psb.project_id_oid = p.id
where psb.strategic_bucket_id_oid in (select sb.id from strategic_bucket sb where sb.id in (1, 2, 3, ...)) order by p.prj_nr;)
I try with something like this query:
Query<ProjectStrategicBucket> subQuery = pm.newQuery(ProjectStrategicBucket.class);
subQuery.setFilter("param.contains(this.strategicBucket)");
subQuery.declareParameters("java.util.List param");
subQuery.setParameters(searchBean.getStrategicBuckets());
Query<Project> query = pm.newQuery(Project.class, "this.projectStrategicBuckets.contains(projectStrategicBucket)");
query.declareVariables("com.siemens.energy.rdst.data.db.ProjectStrategicBucket projectStrategicBucket");
query.addSubquery(subQuery, "com.siemens.energy.rdst.data.db.ProjectStrategicBucket projectStrategicBucket", null, "param");
results = (List<Project>)query.execute();
But I get the exception:
org.datanucleus.exceptions.NucleusUserException: Query has reference to member "strategicBucket" of class "com.siemens.energy.rdst.data.db.Project" yet this doesnt exist! at org.datanucleus.store.rdbms.query.QueryToSQLMapper.getSQLTableMappingForPrimaryExpression(QueryToSQLMapper.java:3606) at org.datanucleus.store.rdbms.query.QueryToSQLMapper.processPrimaryExpression(QueryToSQLMapper.java:3253) at org.datanucleus.store.rdbms.query.QueryToSQLMapper.processInvokeExpression(QueryToSQLMapper.java:4300) at org.datanucleus.store.rdbms.query.QueryToSQLMapper.processInvokeExpression(QueryToSQLMapper.java:4264)How can I solve this problem or implement the query better?
I prefer JDOQL because I'd to add multiple several subqueries, for example for ProjectStrategicLens and so on.
The O/R Java classes are:
Project 1 : N ProjectStrategicBucket N : 1 StrategicBucket
Project.java
...
@Persistent(mappedBy="project")
@Order(column = "LIST_ORDER_IDX")
@ForeignKey(deleteAction = ForeignKeyAction.CASCADE)
private List<ProjectStrategicBucket> projectStrategicBuckets = new ArrayList<ProjectStrategicBucket>();
...
ProjectStrategicBucket.java:
@PersistenceCapable(table = "PROJECT_STRATEGIC_BUCKET", identityType = IdentityType.APPLICATION)
@Sequence(name = "ProjectStrategicBucketSeq", datastoreSequence = "PROJECT_STRATEGIC_BUCKET_SEQ", strategy = SequenceStrategy.CONTIGUOUS, allocationSize = 1)
@FetchGroups({
@FetchGroup(name = DataServiceConstants.STRATEGIC_BUCKET, members = { @Persistent(name = "strategicBucket"), @Persistent(name = "value"),
@Persistent(name = "modificationTimestamp"), @Persistent(name = "person"), @Persistent(name = "project")})
})
public class ProjectStrategicBucket extends AbstractBeanWithId implements Comparable<ProjectStrategicBucket>
{
private static final long serialVersionUID = 1L;
public final static Comparator<ProjectStrategicBucket> BACKET_VALUE_COMPARATOR = new Comparator<ProjectStrategicBucket>() {
@Override public int compare( ProjectStrategicBucket bucket1, ProjectStrategicBucket bucket2 ) {
return bucket2.value.compareTo( bucket1.value );
}
};
@Persistent(primaryKey = "true", nullValue = NullValue.EXCEPTION, valueStrategy = IdGeneratorStrategy.SEQUENCE, sequence = "ProjectStrategicBucketSeq")
private Long _id;
@Persistent(column = "STRATEGIC_BUCKET_ID_OID")
private StrategicBucket strategicBucket;
@Persistent
private Integer value;
@Persistent
private DateTime modificationTimestamp;
@Persistent
private Person person;
@Persistent
@Column(name="PROJECT_ID_OID")
private Project project;
/**
* @return the project
*/
public Project getProject() {
return project;
}
/**
* @param project the project to set
*/
public void setProject(Project project) {
this.project = project;
}
/**
* Constructor.
*/
public ProjectStrategicBucket() {
}
/**
* Constructor.
* @param strategicBucket
*/
public ProjectStrategicBucket(StrategicBucket strategicBucket){
this.strategicBucket = strategicBucket;
}
@Override
public Long getId() {
return _id;
}
@Override
public void setId(Long id) {
_id = id;
}
/**
* @return the Strategic Bucket
*/
public StrategicBucket getStrategicBucket()
{
return strategicBucket;
}
/**
* @param strategicBucket - the Strategic Bucket
*/
public void setStrategicBucket(StrategicBucket strategicBucket)
{
this.strategicBucket = strategicBucket;
}
/**
* @return the percentage value of Strategic Bucket
*/
public Integer getValue()
{
return value;
}
/**
* @param value - the percentage value of Strategic Bucket
*/
public void setValue(Integer value)
{
this.value = value;
}
@Override
public int compareTo(ProjectStrategicBucket other)
{
return strategicBucket.compareTo(other.strategicBucket);
}
/**
* @return the modificationTimestamp
*/
public DateTime getModificationTimestamp() {
return modificationTimestamp;
}
/**
* @param modificationTimestamp
*/
public void setModificationTimestamp(DateTime modificationTimestamp) {
this.modificationTimestamp = modificationTimestamp;
}
/**
* @return the person
*/
public Person getPerson() {
return person;
}
/**
* @param person
*/
public void setPerson(Person person) {
this.person = person;
}
}
StrategicBucket.java:
@PersistenceCapable(table = "STRATEGIC_BUCKET", identityType = IdentityType.APPLICATION)
@Sequence(name = "StrategicBucketSeq", datastoreSequence = "STRATEGIC_BUCKET_SEQ", strategy = SequenceStrategy.CONTIGUOUS, allocationSize = 1)
@FetchGroups({
@FetchGroup(name = DataServiceConstants.STRATEGIC_BUCKET, members = { @Persistent(name = "shortName"), @Persistent(name = "name"), @Persistent(name = "description")
, @Persistent(name = "projectStrategicBuckets") })
})
public class StrategicBucket extends AbstractBeanWithId implements Comparable<StrategicBucket>
{
private static final long serialVersionUID = 1L;
@Persistent(primaryKey = "true", nullValue = NullValue.EXCEPTION, valueStrategy = IdGeneratorStrategy.SEQUENCE,
sequence = "StrategicBucketSeq")
private Long _id;
@Persistent(nullValue = NullValue.EXCEPTION)
@Column(length = 50)
@Unique
private String name;
@Persistent(nullValue = NullValue.EXCEPTION)
@Column(length = 20)
@Unique
private String shortName;
@Persistent()
@Column(length = 200)
private String description;
/**
* Constructor
*/
public StrategicBucket()
{
}
/**
* Constructor
* @param name - the name of strategic bucket
* @param description - the description of strategic bucket
*/
public StrategicBucket(String name, String description)
{
this.name = name;
this.description = description;
}
/**
* Constructor
* @param shortName
* @param name - the name of strategic bucket
* @param description - the description of strategic bucket
*/
public StrategicBucket(String shortName, String name, String description)
{
this.shortName = shortName;
this.name = name;
this.description = description;
}
/**
* @return the name of strategic bucket
*/
public String getName()
{
return name;
}
/**
* @param name - the name of strategic bucket
*/
public void setName(String name)
{
this.name = name;
}
/**
* @return the shortName
*/
public String getShortName() {
return shortName;
}
/**
* @param shortName
*/
public void setShortName(String shortName) {
this.shortName = shortName;
}
/**
* @return the description of strategic bucket
*/
public String getDescription()
{
return description;
}
/**
* @param description - the description of strategic bucket
*/
public void setDescription(String description)
{
this.description = description;
}
@Override
public Long getId()
{
return _id;
}
@Override
public void setId(Long id)
{
_id = id;
}
@Persistent(mappedBy="strategicBucket")
@ForeignKey(deleteAction = ForeignKeyAction.CASCADE)
Collection<ProjectStrategicBucket> projectStrategicBuckets = new HashSet<ProjectStrategicBucket>();
/**
* @return the projectStrategicBuckets
*/
public Collection<ProjectStrategicBucket> getProjectStrategicBuckets() {
return this.projectStrategicBuckets;
}
/**
* @param projectStrategicBuckets the projectStrategicBuckets to set
*/
public void setProjectStrategicBuckets(Collection<ProjectStrategicBucket> projectStrategicBuckets) {
this.projectStrategicBuckets = projectStrategicBuckets;
}
@Override
public String toString()
{
final StringBuilder sb = new StringBuilder(100);
sb.append(getClass().getSimpleName());
sb.append("(id=").append(_id);
sb.append(",name='").append(name).append('\'');
sb.append(')');
return sb.toString();
}
@Override
public int compareTo(StrategicBucket other)
{
return name.compareTo(other.name);
}
}
