Read the rules before posting!
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version:
2.1.6
Mapping documents:
Code:
<hibernate-mapping package="QT">
<class name="QTTask" table="TASK" optimistic-lock="none">
<id name="taskId" column="TASK_ID" type="java.lang.String">
<generator class="assigned"/>
</id>
<property name="name" column="NAME" type="java.lang.String"/>
<property name="active" column="ACTIVE" type="java.lang.Boolean"/>
<set name="taskSubtasks" table="TASK_SUBTASK" cascade="all" lazy="true" order-by="SUBTASK_ID ASC">
<key column="TASK_ID"/>
<many-to-many class="QT.QTSubtask" column="SUBTASK_ID"/>
</set>
</class>
</hibernate-mapping>
Code:
<class name="QTSubtask" table="SUBTASK" optimistic-lock="none">
<id name="subtaskId" column="SUBTASK_ID" type="java.lang.String">
<generator class="assigned"/>
</id>
<property name="name" column="NAME" type="java.lang.String"/>
<property name="active" column="ACTIVE" type="java.lang.Boolean"/>
<set name="subtaskTasks" table="TASK_SUBTASK" cascade="all" lazy="true" order-by="TASK_ID ASC">
<key column="SUBTASK_ID"/>
<many-to-many class="QT.QTTask" column="TASK_ID"/>
</set>
</class>
Code:
String sql = "select subtask from QTSubtask as st " +
" where st.subtaskId not in( " +
"select subtask from st inner join QTTask.getTaskSubtasks tSt " +
"on tSt.taskId = :taskId and st.subtaskId = tSt.subtaskId" +
" )";
Query q = ses.createQuery(sql);
q.setString("taskId", taskId);
List subtaskList = q.list();
Full stack trace of any exception that occurs:no exceptions
Name and version of the database you are using:SQL-Server 7.0
The generated SQL (show_sql=true):Code:
Hibernate: select tasksubtas0_.TASK_ID as TASK_ID__, tasksubtas0_.SUBTASK_ID as SUBTASK_ID__, qtsubtask1_.SUBTASK_ID as SUBTASK_ID0_, qtsubtask1_.NAME as NAME0_, qtsubtask1_.ACTIVE as ACTIVE0_ from TASK_SUBTASK tasksubtas0_ inner join SUBTASK qtsubtask1_ on tasksubtas0_.SUBTASK_ID=qtsubtask1_.SUBTASK_ID where tasksubtas0_.TASK_ID=? order by tasksubtas0_.SUBTASK_ID ASC
Debug level Hibernate log excerpt:--
I've gone through chapters 9 & ll of the reference documentation as well as perused the forums extensively and haven't found something quite like this. I'm obviously doing something wrong with my subquery but I'm at a loss as to where this is. I can run the below code and it returns the resultset exactly as I'd like:
Code:
ArrayList idS = new ArrayList();
idS.add("ACCT");
idS.add("CLER");
idS.add("RLX");
Query q = ses.createQuery("from QTSubtask as st where st.subtaskId not in (:idS)");
q.setParameterList("idS", idS);
List subtaskList = q.list();
... however I'd like to simply get my list based on the taskId and not go through retrieving a list of associated subtasks before excluding them from my query, if at all possible.
Thanks for the help. You guys are great here.
Login
FAQ
Search