36.7. Key Terms

[Page 1126 ( continued )]

33.2. A Universal SQL Client

In the preceding chapter, you used various drivers to connect to the database, created statements for executing SQL statements, and processed the results from SQL queries. This section presents a universal SQL client that enables you to connect to any relational database and execute SQL commands interactively, as shown in Figure 33.1. The client can connect to any JDBC data source, and can submit SQL SELECT commands and non-SELECT commands for execution. The execution result is displayed for the SELECT queries, and the execution status is displayed for the non-SELECT commands. Listing 33.1 gives the solution to the problem.

Figure 33.1. You can connect to any JDBC data source and execute SQL commands interactively.

Listing 33.1. SQLClient.java

(This item is displayed on pages 1126 - 1130 in the print version)

 1   import   java.awt.*; 2   import   java.awt.event.*; 3   import   javax.swing.*; 4   import   javax.swing.border.*; 5   import   java.sql.*; 6   import   java.util.*; 7 8   public class   SQLClient   extends   JApplet { 9  // Connection to the database  10   private    Connection connection  ; 11 12  // Statement to execute SQL commands  13   private    Statement statement  ; 14 

---

[Page 1127]

 15  // Text area to enter SQL commands  16   private   JTextArea jtasqlCommand =   new   JTextArea(); 17 18  // Text area to display results from SQL commands  19   private   JTextArea jtaSQLResult =   new   JTextArea(); 20 21  // JDBC info for a database connection  22 JTextField jtfUsername =   new   JTextField(); 23 JPasswordField jpfPassword =   new   JPasswordField(); 24 JComboBox jcboURL =   new   JComboBox(   new   String[] { 25   "jdbc:mysql://liang.armstrong.edu/test"   , 26   "jdbc:odbc:exampleMDBDataSource"   , 27   "jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl"   }); 28 JComboBox jcboDriver =   new   JComboBox(   new   String[] { 29   "com.mysql.jdbc.Driver"   ,   "sun.jdbc.odbc.JdbcOdbcDriver"   , 30   "oracle.jdbc.driver.OracleDriver"   }); 31 32 JButton jbtExecuteSQL =   new   JButton(   "Execute SQL Command"   ); 33 JButton jbtClearSQLCommand =   new   JButton(   "Clear"   ); 34 JButton jbtConnectDB1 =   new   JButton(   "Connect to Database"   ); 35 JButton jbtClearSQLResult =   new   JButton(   "Clear Result"   ); 36 37  // Create titled borders  38 Border titledBorder1 =   new   TitledBorder(   "Enter an SQL Command"   ); 39 Border titledBorder2 =   new   TitledBorder(   "SQL Execution Result"   ); 40 Border titledBorder3 =   new   TitledBorder( 41   "Enter Database Information"   ); 42 43 JLabel jlblConnectionStatus =   new   JLabel(   "No connection now"   ); 44 45  /** Initialize the applet */  46   public void   init() { 47 JScrollPane jScrollPane1 =   new   JScrollPane(jtasqlCommand); 48 jScrollPane1.setBorder(titledBorder1); 49 JScrollPane jScrollPane2 =   new   JScrollPane(jtaSQLResult); 50 jScrollPane2.setBorder(titledBorder2); 51 52 JPanel jPanel1 =   new   JPanel(   new   FlowLayout(FlowLayout.RIGHT)); 53 jPanel1.add(jbtClearSQLCommand); 54 jPanel1.add(jbtExecuteSQL); 55 56 JPanel jPanel2 =   new   JPanel(); 57 jPanel2.setLayout(   new   BorderLayout()); 58 jPanel2.add(jScrollPane1, BorderLayout.CENTER); 59 jPanel2.add(jPanel1, BorderLayout.SOUTH); 60 jPanel2.setPreferredSize(   new   Dimension(   100   ,   100   )); 61 62 JPanel jPanel3 =   new   JPanel(); 63 jPanel3.setLayout(   new   BorderLayout()); 64 jPanel3.add(jlblConnectionStatus, BorderLayout.CENTER); 65 jPanel3.add(jbtConnectDB1, BorderLayout.EAST); 66 67 JPanel jPanel4 =   new   JPanel(); 68 jPanel4.setLayout(   new   GridLayout(   4   ,   1   ,   10   ,   5   )); 69 jPanel4.add(jcboDriver); 70 jPanel4.add(jcboURL); 71 jPanel4.add(jtfUsername); 72 jPanel4.add(jpfPassword); 73 74 JPanel jPanel5 =   new   JPanel(); 

---

[Page 1128]

 75 jPanel5.setLayout(   new   GridLayout(   4   ,   1   )); 76 jPanel5.add(   new   JLabel(   "JDBC Driver"   )); 77 jPanel5.add(   new   JLabel(   "Database URL"   )); 78 jPanel5.add(   new   JLabel(   "Username"   )); 79 jPanel5.add(   new   JLabel(   "Password"   )); 80 81 JPanel jPanel6 =   new   JPanel(); 82 jPanel6.setLayout(   new   BorderLayout()); 83 jPanel6.setBorder(titledBorder3); 84 jPanel6.add(jPanel4, BorderLayout.CENTER); 85 jPanel6.add(jPanel5, BorderLayout.WEST); 86 87 JPanel jPanel7 =   new   JPanel(); 88 jPanel7.setLayout(   new   BorderLayout()); 89 jPanel7.add(jPanel3, BorderLayout.SOUTH); 90 jPanel7.add(jPanel6, BorderLayout.CENTER); 91 92 JPanel jPanel8 =   new   JPanel(); 93 jPanel8.setLayout(   new   BorderLayout()); 94 jPanel8.add(jPanel2, BorderLayout.CENTER); 95 jPanel8.add(jPanel7, BorderLayout.WEST); 96 97 JPanel jPanel9 =   new   JPanel(   new   FlowLayout(FlowLayout.LEFT)); 98 jPanel9.add(jbtClearSQLResult); 99 100 jcboURL.setEditable(   true   ); 101 jcboDriver.setEditable(   true   ); 102 103 add(jPanel8, BorderLayout.NORTH); 104 add(jScrollPane2, BorderLayout.CENTER); 105 add(jPanel9, BorderLayout.SOUTH); 106 107 jbtExecuteSQL.addActionListener(   new   ActionListener() { 108   public void   actionPerformed(ActionEvent e) { 109  executeSQL();  110 } 111 }); 112 jbtConnectDB1.addActionListener(   new   ActionListener() { 113   public void   actionPerformed(ActionEvent e) { 114  connectToDB();  115 } 116 }); 117 jbtClearSQLCommand.addActionListener(   new   ActionListener() { 118   public void   actionPerformed(ActionEvent e) { 119 jtasqlCommand.setText(   null   ); 120 } 121 }); 122 jbtClearSQLResult.addActionListener(   new   ActionListener() { 123   public void   actionPerformed(ActionEvent e) { 124 jtaSQLResult.setText(   null   ); 125 } 126 }); 127 } 128 129  /** Connect to DB */  130   private void   connectToDB() { 131  // Get database information from the user input  132 String driver = (String)jcboDriver.getSelectedItem(); 133 String url = (String)jcboURL.getSelectedItem(); 134 String username = jtfUsername.getText().trim(); 

---

[Page 1129]

 135 String password =   new   String(jpfPassword.getPassword()); 136 137  // Connection to the database  138   try   { 139  Class.forName(driver);  140  connection = DriverManager.getConnection(  141  url, username, password);  142 jlblConnectionStatus.setText(   "Connected to "   + url); 143 } 144   catch   (java.lang.Exception ex) { 145 ex.printStackTrace(); 146 } 147 } 148 149  /** Execute SQL commands */  150   private void   executeSQL() { 151   if   (connection ==   null   ) { 152 jtaSQLResult.setText(   "Please connect to a database first"   ); 153   return   ; 154 } 155   else   { 156 String sqlCommands = jtasqlCommand.getText().trim(); 157 String[] commands = sqlCommands.replace(   '\n'   ,   ' '   ).split(   ";"   ); 158 159   for   (String aCommand: commands) { 160   if   (aCommand.trim().toUpperCase().startsWith(   "SELECT"   )) { 161  processSQLSelect(aCommand);  162 } 163   else   { 164  processSQLNonSelect(aCommand);  165 } 166 } 167 } 168 } 169 170  /** Execute SQL SELECT commands */  171   private void   processSQLSelect(String sqlCommand) { 172   try   { 173  // Get a new statement for the current connection  174  statement = connection.createStatement();  175 176  // Execute a SELECT SQL command  177  ResultSet resultSet = statement.executeQuery(sqlCommand);  178 179  // Find the number of columns in the result set  180   int   columnCount =  resultSet.getMetaData().getColumnCount()  ; 181 String row =   " "   ; 182 183  // Display column names  184   for   (   int   i =   1   ; i <= columnCount; i++) { 185 row += resultSet.getMetaData().getColumnName(i) +   "\t"   ; 186 } 187 188 jtaSQLResult.append(row +   '\n'   ); 189 190   while   (resultSet. next ()) { 191  // Reset row to empty  192 row =   " "   ; 193 194   for   (   int   i =   1   ; i <= columnCount; i++) { 

---

[Page 1130]

 195  // A non-String column is converted to a string  196 row += resultSet.getString(i) +   "\t"   ; 197 } 198 199 jtaSQLResult.append(row +   '\n'   ); 200 } 201 } 202   catch   (SQLException ex) { 203 jtaSQLResult.setText(ex.toString()); 204 } 205 } 206 207  /** Execute SQL DDL, and modification commands */  208   private void   processSQLNonSelect(String sqlCommand) { 209   try   { 210  // Get a new statement for the current connection  211  statement = connection.createStatement();  212 213  // Execute a non-SELECT SQL command  214  statement.executeUpdate(sqlCommand);  215 216 jtaSQLResult.setText(   "SQL command executed"   ); 217 } 218   catch   (SQLException ex) { 219 jtaSQLResult.setText(ex.toString()); 220 } 221 } 222 } 

The user selects or enters the JDBC driver, database URL, username, and password, and clicks the Connect to Database button to connect to the specified database using the connectToDB() method (lines 130 “147).

When the user clicks the Execute SQL Command button, the executeSQL() method is invoked (lines 150 “168) to get the SQL commands from the text area ( jtaSQLCommand ) and extract each command separated by a semicolon (;). It then determines whether the command is a SELECT query or a DDL or data modification statement (lines 160 “165). If the command is a SELECT query, the processSQLSelect method is invoked (lines 171 “205). This method uses the executeQuery method (line 177) to obtain the query result. The result is displayed in the text area ( jtaSQLResult ). If the command is a non-SELECT query, the processSQLNonSelect() method is invoked (lines 208 “221). This method uses the executeUpdate method (line 214) to execute the SQL command.

The getMetaData method (lines 180, 185) in the ResultSet interface is used to obtain an instance of ResultSetMetaData . The getColumnCount method (line 180) returns the number of columns in the result set, and the getColumnName(i) method (line 185) returns the column name for the i th column.