KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > teamkonzept > webman > mainint > db > queries > oracle > TKDBContentTreeGetOpen


1 package com.teamkonzept.webman.mainint.db.queries.oracle;
2
3 import java.util.Enumeration JavaDoc;
4 import java.sql.*;
5 import java.io.*;
6 import com.teamkonzept.db.*;
7 //import com.teamkonzept.db.atomic.content.*;
8 //import com.teamkonzept.db.atomic.CheckTableExistence;
9 import com.teamkonzept.webman.mainint.db.queries.oracle.helper.*;
10 import com.teamkonzept.webman.mainint.db.queries.content.*;
11 import com.teamkonzept.webman.mainint.WebmanExceptionHandler;
12 import com.teamkonzept.lib.TKNull;
13
14 /*
15  * TKDBContentTreeGetOpen
16  * gibt eine teilweise geoeffneten Baum zurueck
17  * Input: NODE_ID des zu oefneten Knotens
18  * Output: CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME
19  * CONTENT_NODE_TYPE, CONTENT_FORM, CONTENT_NODE_PARENT, NODE_ID
20  * wenn die NODE_ID = CONTENT_NODE_ID dann ist dieser Knoten oeffenbar, aber geschlossen
21  * wenn die NODE_ID = NULL dann ist dieser ein Blatt
22  * wenn die NODE_ID = CONTENT_NODE_PARENT dann ist dieser Knoten oeffenbar und offen
23  */
24 public class TKDBContentTreeGetOpen extends TKExtendedPrepQuery
25 {
26
27     public final static boolean isPrepared = true;
28
29     public final static String JavaDoc[] paramOrder = { "NODE_ID" };
30
31     public final static Object JavaDoc[][] types = null;
32
33     public final static boolean[] setRelevants = { true };
34
35 /* public final static String sqlString =
36         "DECLARE @N_ID int " +
37         "DECLARE @LNR int " +
38         "DECLARE @RNR int " +
39
40         "SELECT @N_ID = ? " +
41
42         "IF (@N_ID IS NULL) " +
43         "BEGIN " +
44             "SELECT " +
45             " @N_ID = CONTENT_NODE_ID " +
46             "FROM " +
47             " CONTENT_TREE " +
48             "WHERE " +
49             " CONTENT_NODE_PARENT = NULL " +
50         "END " +
51
52         "SELECT " +
53         " @LNR = LEFT_NR, " +
54         " @RNR = RIGHT_NR " +
55         "FROM " +
56         " CONTENT_TREE " +
57         "WHERE " +
58         " CONTENT_NODE_ID = @N_ID " +
59
60         "SELECT " +
61         " CONTENT_NODE_ID " +
62         "INTO " +
63         " #temp " +
64         "FROM " +
65         " CONTENT_TREE " +
66         "WHERE " +
67         " LEFT_NR <= @LNR " +
68         "AND " +
69         " RIGHT_NR >= @RNR " +
70
71         "CREATE TABLE " +
72         " #temp2 " +
73         " (CONTENT_NODE_ID int null, NODE_ID int null) " +
74
75         "INSERT INTO " +
76         " #temp2 " +
77         "SELECT " +
78         " CONTENT_NODE_ID, CONTENT_NODE_ID " +
79         "FROM " +
80         " CONTENT_TREE " +
81         "WHERE " +
82         " CONTENT_NODE_PARENT IN " +
83             "( SELECT CONTENT_NODE_ID FROM #temp ) " +
84         "OR " +
85         " CONTENT_NODE_ID IN " +
86             "( SELECT CONTENT_NODE_ID FROM #temp ) " +
87
88          "UPDATE " +
89          " #temp2 " +
90          "SET " +
91          " NODE_ID = CT.CONTENT_NODE_PARENT " +
92          "FROM " +
93          " #temp T, CONTENT_TREE CT " +
94          "WHERE " +
95          " CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID " +
96          "AND " +
97          " CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID " +
98
99          "UPDATE " +
100          " #temp2 " +
101          "SET " +
102          " NODE_ID = NULL " +
103          "FROM " +
104          " CONTENT_TREE CT " +
105          "WHERE " +
106          " CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID " +
107          "AND " +
108          " CT.CONTENT_NODE_TYPE > 2 " +
109
110         "SELECT " +
111         " CT.CONTENT_NODE_ID, CI.INSTANCE_ID, CI.NAME, " +
112         " CT.CONTENT_NODE_NAME, CT.CONTENT_NODE_SHORTNAME, " +
113         " CT.CONTENT_NODE_TYPE, CT.LEFT_NR, CT.RIGHT_NR, CT.CONTENT_FORM, CT.CONTENT_NODE_PARENT, " +
114         " T.NODE_ID " +
115         "FROM " +
116         " CONTENT_TREE CT, CONTENT_INSTANCE CI, #temp2 T " +
117         "WHERE " +
118         " CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID AND CI.CONTENT_NODE_ID =* CT.CONTENT_NODE_ID " +
119         "ORDER BY " +
120         " CT.LEFT_NR " +
121
122         "DROP TABLE #temp " +
123         "DROP TABLE #temp2 "; */
124
125     protected static Class JavaDoc[] queryClasses = {
126     CheckTableExistence.class, // [0]
127 SelectContentNodeIdFromContentTree.class, // [1]
128 SelectLeftRightNrFromContentTree.class, // [2]
129 CreateTempTableContentNodeId.class, // [3]
130 CreateTempTableContentNodeIdNodeId.class, // [4]
131 InsertIntoTempSelectContentNodeIdLeftRightFromContentTree.class, // [5]
132 InsertTempTableContentNodeIdNodeId.class, // [6]
133 UpdateTempTableContentNodeIdNodeId.class, // [7]
134 UpdateTempTableContentNodeIdNodeId2.class, // [8]
135 SelectFromContentTreeContentInstanceTempTable.class, // [9]
136 };
137
138     public boolean execute()
139     {
140     try
141     {
142         init(queryClasses); // init query objects
143
144         // SELECT @N_ID = ?
145 Object JavaDoc nid = queryParams.get("NODE_ID");
146
147         boolean isNotOpen = aTKDBConnection.isAutoCommit();
148
149         if (isNotOpen)
150         {
151         TKDBManager.beginTransaction(); // begin transaction
152 }
153
154         // IF (@N_ID IS NULL)
155 if (nid == null || nid instanceof TKNull)
156         {
157         // SELECT @N_ID = CONTENT_NODE_ID FROM CONTENT_TREE WHERE CONTENT_NODE_PARENT IS NULL
158 queries[1].execute();
159         ResultSet rs = queries[1].fetchResultSet();
160         if (rs != null && rs.next())
161         {
162             nid = rs.getObject(1);
163         }
164         }
165         // SELECT @LNR = LEFT_NR, @RNR = RIGHT_NR FROM CONTENT_TREE WHERE CONTENT_NODE_ID = @N_ID
166 Object JavaDoc lnr = null;
167         Object JavaDoc rnr = null;
168         queries[2].setQueryParams("NODE_ID", nid);
169         queries[2].execute();
170         ResultSet rs = queries[2].fetchResultSet();
171         if (rs != null && rs.next())
172         {
173         lnr = rs.getObject(1);
174         rnr = rs.getObject(2);
175         }
176
177         queries[0].setQueryParams("TABLE_NAME", "WEBMAN_TEMP"); // check existence!!!
178 queries[0].execute();
179         ResultSet exist = queries[0].fetchResultSet();
180         if (exist != null && exist.next())
181         {
182         }
183         else
184         {
185         // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
186 queries[3].execute();
187         }
188
189         queries[0].setQueryParams("TABLE_NAME", "WEBMAN_TEMP_2"); // check existence!!!
190 queries[0].execute();
191         exist = queries[0].fetchResultSet();
192         if (exist != null && exist.next())
193         {
194         }
195         else
196         {
197         // CREATE GLOBAL TEMPORARY TABLE WEBMAN_TEMP (CONTENT_NODE_ID int)
198 queries[4].execute();
199         }
200
201         // OLD:
202 // SELECT CONTENT_NODE_ID INTO #temp FROM CONTENT_TREE " +
203 // WHERE LEFT_NR <= @LNR AND RIGHT_NR >= @RNR " +
204
205         // NEW:
206 // INSERT INTO WEBMAN_TEMP SELECT CONTENT_NODE_ID FROM CONTENT_TREE WHERE LEFT_NR <= @LNR AND RIGHT_NR >= @RNR
207 queries[5].setQueryParams("LEFT_NR", lnr);
208         queries[5].setQueryParams("RIGHT_NR", rnr);
209         queries[5].execute();
210
211         // INSERT INTO #temp2 SELECT CONTENT_NODE_ID, CONTENT_NODE_ID FROM CONTENT_TREE
212 // WHERE CONTENT_NODE_PARENT IN ( SELECT CONTENT_NODE_ID FROM #temp )
213 // OR
214 // CONTENT_NODE_ID IN ( SELECT CONTENT_NODE_ID FROM #temp )
215 queries[6].execute();
216
217         // OLD:
218 // UPDATE #temp2 SET NODE_ID = CT.CONTENT_NODE_PARENT FROM
219 // #temp T, CONTENT_TREE CT WHERE CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID
220 // AND CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID
221 // NEW:
222 // UPDATE WEBMAN_TEMP_2 SET NODE_ID =
223 // (SELECT CT.CONTENT_NODE_PARENT FROM CONTENT_TREE CT, WEBMAN_TEMP T
224 // WHERE CT.CONTENT_NODE_ID = WEBMAN_TEMP_2.CONTENT_NODE_ID
225 // AND CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID)
226 // WHERE CONTENT_NODE_ID IN (SELECT CONTENT_NODE_ID FROM WEBMAN_TEMP)
227 queries[7].execute();
228
229         // OLD:
230 // UPDATE #temp2 SET NODE_ID = NULL FROM CONTENT_TREE CT
231 // WHERE CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID AND CT.CONTENT_NODE_TYPE > 1
232 // NEW: UPDATE WEBMAN_TEMP_2 SET NODE_ID =
233 // (SELECT NULL FROM CONTENT_TREE CT, WEBMAN_TEMP T WHERE CT.CONTENT_NODE_ID = WEBMAN_TEMP_2.CONTENT_NODE_ID AND CT.CONTENT_NODE_TYPE > 1) WHERE CONTENT_NODE_ID IN (SELECT T.CONTENT_NODE_ID FROM WEBMAN_TEMP T, CONTENT_TREE CT WHERE CT.CONTENT_NODE_ID = WEBMAN_TEMP_2.CONTENT_NODE_ID AND CT.CONTENT_NODE_TYPE > 1)
234 queries[8].execute();
235         // SELECT CT.CONTENT_NODE_ID, CI.INSTANCE_ID, CI.NAME, CT.CONTENT_NODE_NAME,
236 // CT.CONTENT_NODE_SHORTNAME, CT.CONTENT_NODE_TYPE, CT.LEFT_NR, CT.RIGHT_NR,
237 // CT.CONTENT_FORM, CT.CONTENT_NODE_PARENT, T.NODE_ID
238 // FROM CONTENT_TREE CT, CONTENT_INSTANCE CI, #temp2 T
239 // WHERE
240 // CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID AND CI.CONTENT_NODE_ID =* CT.CONTENT_NODE_ID
241 // ORDER BY
242 // CT.LEFT_NR
243
244
245         queries[9].execute();
246         addResult(queries[9].fetchResultSet());
247
248         if (isNotOpen)
249         {
250         aTKDBConnection.commitTransaction(); // commit all changes
251 }
252
253     }
254     catch(Throwable JavaDoc t)
255     {
256         TKDBManager.safeRollbackTransaction(t);
257     }
258     return hasResults();
259     }
260
261     public void initQuery(Connection con)
262     {
263     super.initQuery(con,
264             isPrepared, paramOrder, types, setRelevants, null);
265     }
266 }
267
Popular Tags