KickJava   Java API By Example, From Geeks To Geeks.

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


1 package com.teamkonzept.webman.mainint.db.queries.sybase;
2
3 import com.teamkonzept.db.*;
4 import java.sql.*;
5
6 /*
7  * TKDBContentTreeGetOpen
8  * gibt eine teilweise geoeffneten Baum zurueck
9  * Input: NODE_ID des zu oefneten Knoten
10  * Output: CONTENT_NODE_ID, CONTENT_NODE_NAME, CONTENT_NODE_SHORTNAME
11  * CONTENT_NODE_TYPE, CONTENT_FORM, CONTENT_NODE_PARENT, NODE_ID
12  * wenn die NODE_ID = CONTENT_NODE_ID dann ist dieser Knoten oeffenbar, aber geschlossen
13  * wenn die NODE_ID = NULL dann ist dieser ein Blatt
14  * wenn die NODE_ID = CONTENT_NODE_PARENT dann ist dieser Knoten oeffenbar und offen
15  */
16 public class TKDBContentTreeGetOpen extends TKPrepQuery {
17
18     public final static boolean isPrepared =
19         true;
20     
21     public final static String JavaDoc[] paramOrder =
22         { "NODE_ID" };
23     public final static Object JavaDoc[][] paramTypes =
24         null;
25         
26     public final static boolean[] setRelevants =
27         { true };
28         
29     public final static String JavaDoc sqlString =
30         "DECLARE @N_ID int " +
31         "DECLARE @LNR int " +
32         "DECLARE @RNR int " +
33         
34         "SELECT @N_ID = ? " +
35         
36         "IF (@N_ID IS NULL) " +
37         "BEGIN " +
38             "SELECT " +
39             " @N_ID = CONTENT_NODE_ID " +
40             "FROM " +
41             " CONTENT_TREE " +
42             "WHERE " +
43             " CONTENT_NODE_PARENT = NULL " +
44         "END " +
45         
46         "SELECT " +
47         " @LNR = LEFT_NR, " +
48         " @RNR = RIGHT_NR " +
49         "FROM " +
50         " CONTENT_TREE " +
51         "WHERE " +
52         " CONTENT_NODE_ID = @N_ID " +
53         
54         "SELECT " +
55         " CONTENT_NODE_ID " +
56         "INTO " +
57         " #temp " +
58         "FROM " +
59         " CONTENT_TREE " +
60         "WHERE " +
61         " LEFT_NR <= @LNR " +
62         "AND " +
63         " RIGHT_NR >= @RNR " +
64
65         "CREATE TABLE " +
66         " #temp2 " +
67         " (CONTENT_NODE_ID int null, NODE_ID int null) " +
68         
69         "INSERT INTO " +
70         " #temp2 " +
71         "SELECT " +
72         " CONTENT_NODE_ID, CONTENT_NODE_ID " +
73         "FROM " +
74         " CONTENT_TREE " +
75         "WHERE " +
76         " CONTENT_NODE_PARENT IN " +
77             "( SELECT CONTENT_NODE_ID FROM #temp ) " +
78         "OR " +
79         " CONTENT_NODE_ID IN " +
80             "( SELECT CONTENT_NODE_ID FROM #temp ) " +
81          
82          "UPDATE " +
83          " #temp2 " +
84          "SET " +
85          " NODE_ID = CT.CONTENT_NODE_PARENT " +
86          "FROM " +
87          " #temp T, CONTENT_TREE CT " +
88          "WHERE " +
89          " CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID " +
90          "AND " +
91          " CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID " +
92         
93          "UPDATE " +
94          " #temp2 " +
95          "SET " +
96          " NODE_ID = NULL " +
97          "FROM " +
98          " CONTENT_TREE CT " +
99          "WHERE " +
100          " CT.CONTENT_NODE_ID = #temp2.CONTENT_NODE_ID " +
101          "AND " +
102          " CT.CONTENT_NODE_TYPE > 2 " +
103         
104         "SELECT " +
105         " CT.CONTENT_NODE_ID, CI.INSTANCE_ID, CI.NAME, " +
106         " CT.CONTENT_NODE_NAME, CT.CONTENT_NODE_SHORTNAME, " +
107         " CT.CONTENT_NODE_TYPE, CT.LEFT_NR, CT.RIGHT_NR, CT.CONTENT_FORM, CT.CONTENT_NODE_PARENT, " +
108         " T.NODE_ID " +
109         "FROM " +
110         " CONTENT_TREE CT, CONTENT_INSTANCE CI, #temp2 T " +
111         "WHERE " +
112         " CT.CONTENT_NODE_ID = T.CONTENT_NODE_ID AND CI.CONTENT_NODE_ID =* CT.CONTENT_NODE_ID " +
113         "ORDER BY " +
114         " CT.LEFT_NR " +
115         
116         "DROP TABLE #temp " +
117         "DROP TABLE #temp2 ";
118     
119     public void initQuery(Connection con) {
120         super.initQuery(
121             con,
122             isPrepared,
123             paramOrder,
124             paramTypes,
125             setRelevants,
126             sqlString );
127     }
128 }
129
Popular Tags