|
1
19
20 package org.netbeans.modules.dbschema.jdbcimpl;
21
22 import java.sql.*;
23 import java.util.*;
24
25 public class ViewDependency {
26 private Connection con;
27 private String  user;
28 private String view;
29 private DatabaseMetaData dmd;
30 private LinkedList tables;
31 private LinkedList columns;
32
33
34 public ViewDependency(ConnectionProvider cp, String user, String view) throws SQLException {
35 con = cp.getConnection();
36 this.user = user;
37 this.view = view;
38 dmd = cp.getDatabaseMetaData();
39
40 tables = new LinkedList();
41 columns = new LinkedList();
42 }
43
44 public LinkedList getTables() {
45 return tables;
46 }
47
48 public LinkedList getColumns() {
49 return columns;
50 }
51
52 public void constructPK() {
53 try {
54 String database = dmd.getDatabaseProductName();
55 if (database==null){
56 return;
57 }
58 database = database.trim();
59 if (database.equalsIgnoreCase("Oracle")) {
60 getOraclePKTable(user, view);
61 getOracleViewColumns();
62 return;
63 }
64
65 if (database.equalsIgnoreCase("Microsoft SQL Server")) {
66 getMSSQLServerPKTable(user, view);
67 getMSSQLServerViewColumns();
68 return;
69 }
70 } catch (SQLException exc) {
71 exc.printStackTrace();
72 }
73 }
74
75 private void getOraclePKTable(String user, String view) throws SQLException {
76 PreparedStatement stmt;
77 ResultSet rs;
78
79 String query = "select OWNER, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE from ALL_DEPENDENCIES where NAME = ? AND OWNER = ?";
80
81 stmt = con.prepareStatement(query);
82 stmt.setString(1, view);
83 stmt.setString(2, user);
84 rs = stmt.executeQuery();
85
86 while (rs.next()) {
87 String type = rs.getString(4).trim();
88 if (type.equalsIgnoreCase("TABLE")) {
89 tables.add(rs.getString(3).trim());
90 continue;
91 }
92
93 if (type.equalsIgnoreCase("VIEW"))
94 getOraclePKTable(rs.getString(2), rs.getString(3));
95 }
96 rs.close();
97 stmt.close();
98 }
99
100 private void getMSSQLServerPKTable(String user, String view) throws SQLException {
101 CallableStatement cs;
102 ResultSet rs;
103 String name;
104 int pos;
105
106 cs = con.prepareCall("{call sp_depends(?)}");
107 cs.setString(1, view);
108 try {
109 rs = cs.executeQuery();
110
111 while (rs.next()) {
112 String type = rs.getString(2).trim().toLowerCase();
113 name = rs.getString(1).trim();
114 pos = name.lastIndexOf(".");
115 name = name.substring(pos + 1);
116
117 if (type.indexOf("table") != -1)
118 if (! tables.contains(name)) {
119 tables.add(name);
120 continue;
121 }
122
123 if (type.equals("view"))
124 getMSSQLServerPKTable(user, name);
125 }
126 rs.close();
127 } catch (Exception exc) {
128 rs = null;
130 return;
131 }
132 }
133
134 private void getOracleViewColumns() throws SQLException {
135 PreparedStatement stmt;
136 ResultSet rs;
137 String text = null;
138 int startPos, endPos;
139
140 String query = "select TEXT from ALL_VIEWS where VIEW_NAME = ?";
141
142 stmt = con.prepareStatement(query);
143 stmt.setString(1, view);
144 rs = stmt.executeQuery();
145
146 if (rs.next())
147 text = rs.getString(1).trim();
148 rs.close();
149 stmt.close();
150
151 if (text == null)
152 return;
153
154 startPos = text.indexOf(" ");
155 endPos = text.toLowerCase().indexOf("from");
156 text = text.substring(startPos, endPos).trim();
157
158 StringTokenizer st = new StringTokenizer(text, ",");
159 String colName;
160 while (st.hasMoreTokens()) {
161 colName = st.nextToken().trim();
162 if (colName.startsWith("\""))
163 colName = colName.substring(1, colName.length() - 1);
164 columns.add(colName.toLowerCase());
165 }
166 }
167
168 private void getMSSQLServerViewColumns() throws SQLException {
169 CallableStatement cs;
170 ResultSet rs;
171 String text = null;
172 int startPos, endPos;
173
174 cs = con.prepareCall("{call sp_helptext(?)}");
175 cs.setString(1, view);
176 try {
177 rs = cs.executeQuery();
178
179 if (rs != null)
180 while (rs.next())
181 text += rs.getString(1).trim();
182 rs.close();
183 cs.close();
184
185 if (text == null)
186 return;
187
188 startPos = text.toLowerCase().indexOf("select") + 6;
189 endPos = text.toLowerCase().indexOf("from");
190 text = text.substring(startPos, endPos).trim();
191
192 StringTokenizer st = new StringTokenizer(text, ",");
193 String colName;
194 while (st.hasMoreTokens()) {
195 colName = st.nextToken().trim();
196 if (colName.startsWith("\""))
197 colName = colName.substring(1, colName.length() - 1);
198 columns.add(colName.toLowerCase());
199 }
200 } catch (Exception exc) {
201 rs = null;
203 return;
204 }
205 }
206
207 }
208 |
Popular Tags |
Java API By Example, From Geeks To Geeks.