1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
|
import java.sql.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.table.*;
import javax.swing.tree.*;
import javax.swing.border.*;
import java.awt.*;
import java.awt.event.*;
import java.util.Vector;
public class DbProject extends JFrame
implements ActionListener, TreeSelectionListener
{
private JTextField tfSqlStr;
private JTextArea status;
private JMenuBar menubar;
private JMenu filemenu;
private JMenuItem exititem, clearitem;
private JScrollPane tableScroll;
private JTable dbTable;
ResultsModel model;
private JScrollPane treeScroll;
private DefaultMutableTreeNode dbNode; // Root node for the database tree
private DefaultTreeModel dbTreeModel;
private JTree dbTree;
private JPanel pN;
private JSplitPane split;
String userid = "plestrange";
String pass = "plcae4";
String url = "jdbc:oracle:thin:@pisang:1521:car";
Connection conn;
Statement state;
public static void main(String[] args)
{
DbProject dbInst = new DbProject();
dbInst.setSize(500, 400);
dbInst.setVisible(true);
}
public DbProject()
{
super("Query Database");
getContentPane().setLayout(new BorderLayout());
setDefaultCloseOperation(DISPOSE_ON_CLOSE);
addWindowListener(new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
System.exit(0);
}
});
pN = new JPanel();
pN.setLayout(new FlowLayout());
pN.add(new JLabel("Enter SQL String:"));
pN.add(tfSqlStr = new JTextField(35));
tfSqlStr.addActionListener(this); //register ActionListener
getContentPane().add(pN, BorderLayout.NORTH);
menubar = new JMenuBar();
filemenu = new JMenu("File");
filemenu.setMnemonic('F');
exititem = new JMenuItem("Exit");
clearitem = new JMenuItem("Clear");
exititem.addActionListener(this); //register ActionListener
clearitem.addActionListener(this); //register ActionListener
filemenu.add(clearitem);
filemenu.add(exititem);
menubar.add(filemenu);
setJMenuBar(menubar);
// Create tree to go in left split pane
dbNode = new DefaultMutableTreeNode("No database");
dbTreeModel = new DefaultTreeModel(dbNode);
dbTree = new JTree(dbTreeModel);
dbTree.addTreeSelectionListener(this);
// Create table to go in right split pane
model = new ResultsModel();
dbTable = new JTable(model);
dbTable.setPreferredSize(new Dimension(800,400));
dbTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
treeScroll = new JScrollPane(dbTree);
treeScroll.setBorder(BorderFactory.createLineBorder(Color.darkGray));
tableScroll = new JScrollPane(dbTable);
tableScroll.setBorder(BorderFactory.createLineBorder(Color.darkGray));
//split = new JSplitPane(HORIZONTAL_SPLIT, true, treeScroll, tableScroll);
split = new JSplitPane();
split.setOrientation(1);
split.setContinuousLayout(true);
split.setLeftComponent(treeScroll);
split.setRightComponent(tableScroll);
getContentPane().add(split, BorderLayout.CENTER);
status = new JTextArea();
status.setEditable(false);
getContentPane().add(status, BorderLayout.SOUTH);
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url, userid, pass);
state = conn.createStatement();
dbNode = new DefaultMutableTreeNode(url); // Root node is URL
dbTreeModel.setRoot(dbNode); // Set root in model
setupTree(conn.getMetaData()); // Set up tree with metadata
treeScroll.setBorder(BorderFactory.createTitledBorder(
BorderFactory.createLineBorder(Color.darkGray),
url,
TitledBorder.CENTER,
TitledBorder.DEFAULT_POSITION));
dbTree.setRootVisible(false); // Now show the root node
dbTreeModel.reload(); // Get the tree redisplayed
}
catch (SQLException ex){
while (ex != null){
System.out.println ("SQL Exception: " + ex.getMessage() );
ex = ex.getNextException();
}
}
catch (java.lang.Exception ex){
ex.printStackTrace();
}
tfSqlStr.requestFocus();
}
public void actionPerformed(ActionEvent evt)
{
//String actionCommand = evt.getActionCommand();
Object source = evt.getSource();
if(source.equals(tfSqlStr))
{
try
{
String sql = tfSqlStr.getText();
model.setResultSet(state.executeQuery(sql));
status.setText("Resultset has " + model.getRowCount() + " rows.");
}
catch(SQLException sqle)
{
status.setText(sqle.getMessage());
}
}
else if(source.equals(clearitem))
{
tfSqlStr.setText("");
}
else if(source.equals(exititem))
{
dispose();
System.exit(0);
}
}
private void setupTree(DatabaseMetaData metadata) throws SQLException
{
String[] tableTypes = { "TABLE"}; // We want only tables
ResultSet tables = metadata.getTables( // Get the tables info
null,
null,
null,
tableTypes);
String tableName; // Stores a table name
DefaultMutableTreeNode tableNode; // Stores a tree node for a table
while(tables.next()) // For each table
{
tableName = tables.getString("TABLE_NAME"); // get the table name
tableNode = new DefaultMutableTreeNode(tableName);
dbNode.add(tableNode); // Add the node to the tree
// Get all the columns for the current table
ResultSet columnNames = metadata.getColumns(null, null, tableName, null);
// Add nodes for the columns as children of the table node
while(columnNames.next())
tableNode.add(
new DefaultMutableTreeNode(columnNames.getString("COLUMN_NAME")));
}
}
public void valueChanged(TreeSelectionEvent e)
{
TreePath[] paths = dbTree.getSelectionPaths();
if(paths == null)
return;
boolean tableSelected = false; // Set true if a table is selected
String column; // Stores a column name from a path
String table; // Stores a table name from a path
String columnsParam = null; // Column names in SQL SELECT
String tableParam = null; // Table name in SQL SELECT
String message = null; // Message for status area
for(int j = 0; j < paths.length ; j++)
{
switch(paths[j].getPathCount())
{
case 2: // We have a table selected
tableParam = (String)
(((DefaultMutableTreeNode)
(paths[j].getPathComponent(1))).getUserObject());
columnsParam = "*"; // Select all columns
tableSelected = true; // Set flag for a table selected
message = "Complete " + tableParam + " table displayed";
break;
case 3: // Column selected
table = (String)
(((DefaultMutableTreeNode)
(paths[j].getPathComponent(1))).getUserObject());
if(tableParam == null)
tableParam = table;
else if(tableParam != table)
break;
column = (String)
(((DefaultMutableTreeNode)
(paths[j].getPathComponent(2))).getUserObject());
if(columnsParam == null) // If no previous columns
columnsParam = column; // add the column
else // otherwise
columnsParam += "," + column; // we need a comma too
message = columnsParam + " displayed from " + tableParam + " table.";
break;
}
if(tableSelected) // If a table was selected
break; // we are done
}
if(message != null)
status.setText(message);
}
}
class ResultsModel extends AbstractTableModel
{
String[] columnNames = new String[0];
Vector dataRows; // Empty vector of rows
public void setResultSet(ResultSet results)
{
if(results == null)
{
columnNames = new String[0]; // Reset the columns names
dataRows.clear(); // Remove all entries in the Vector
fireTableChanged(null); // Tell the table there is new model data
return;
}
try
{
ResultSetMetaData metadata = results.getMetaData();
int columns = metadata.getColumnCount(); // Get number of columns
columnNames = new String[columns]; // Array to hold names
// Get the column names
for(int i = 0; i < columns; i++)
columnNames[i] = metadata.getColumnLabel(i+1);
// Get all rows.
dataRows = new Vector(); // New Vector to store the data
String[] rowData; // Stores one row
while(results.next()) // For each row...
{
rowData = new String[columns]; // create array to hold the data
for(int i = 0; i < columns; i++) // For each column
rowData[i] = results.getString(i+1); // retrieve the data item
dataRows.addElement(rowData); // Store the row in the vector
}
fireTableChanged(null); // Signal the table there is new model data
}
catch (SQLException sqle)
{
System.err.println(sqle);
}
}
public int getColumnCount()
{
return columnNames.length;
}
public int getRowCount()
{
if(dataRows == null)
return 0;
else
return dataRows.size();
}
public Object getValueAt(int row, int column)
{
return ((String[])(dataRows.elementAt(row)))[column];
}
public String getColumnName(int column)
{
return columnNames[column] == null ? "No Name" : columnNames[column];
}
}
|