Section 12.5. Example GUI Application: Database Query Viewer
12.5 Example GUI Application: Database Query Viewer
In this section, we write an application with a graphical user interface that retrieves a result set from a database and displays the data in a resizable table. Users of <cfgrid> will find this application somewhat familiar. While this simple example does not allow the user to edit the cells of the query result set from within the grid, or re- sort the data by doubleclicking the column name headings, such functionality could be added with existing classes in the API.
Note
This example can be used with drivers other than Type I drivers. In order to use a query result set in this way with a Type I driver (such as the JDBC:ODBC bridge), you must manually cache the result set to scroll through the results. You can use this example as is with SQLServer, Sybase, MySQL, and so forth. Just download the required driver.
For the example to work, you must have the driver on your class path . Remember that if you are using IDEs such as IntelliJ or Forte, they have their own JDKs built in, and they might not be using the JDK you think they are to execute your programs. You can update these settings within each IDE, usually on a per-project basis.
This application defines a few text fields that allow the user to type in a host name, which is the string to be passed to the Connection object. The driver field accepts the fully qualified path to the driver you use to connect to this database. The username and password fields accept values that may be required to connect to this database. In the query field, type the SQL statement just as you would in your CF code. For instance: SELECT * FROM Products .
Once you have the required information entered, click the "Show Results" button. The application will hit the database and retrieve the records. It will then build a table to display the column names and values for each row in a grid.
The application is divided into a Model-View-Controller kind of architecture. This dictates that the data in an application must be separate entirely from the resulting view of that data. The MVC architecture has rarely been used in ColdFusion, because the broad tag-based language makes it easy to write low-level application code directly intertwined with the HTML showing the results. If you have worked with ColdFusion Components, these encourage a strong separation of the presentation and logical layers . In another way, if you have worked with the Fusebox architectural model for ColdFusion, you have these same goals in mind.
The files are commented to make it easy to follow along. I am grateful to my colleague Vic Miller for his contributions to this application.
12.5.1 DBMain.java
/* File: DBMain.java Purpose: this simply starts the program */ public class DBMain { public static void main(String [] ar) { // instantiate a new controller object DBController controller = new DBController(); // make the display controller.makeDefaultDisplay(); } } The DBMain class simply holds the main() method to instantiate a new DBController object. Once you have a Controller object, you call the make-DefaultDisplay() method to show the GUI elements of the application so the user has something with which to interact.
12.5.2 DBController
/* File: DBController.java Purpose: route messages between the display DBDisplay, which shows the panels holding buttons and text fields, and the DBWorkHandler, which goes to the database and returns a result set for display */ import javax.swing.*; import java.sql.*; public class DBController { // declare variables private DBResultSetModel resultSetModel; private DBWorkHandler worker; private DBDisplay display; // constructor public DBController() { //Make a DBWorkHandler object worker = new DBWorkHandler(); } public void makeDefaultDisplay() { /* *Make a new display and pass a copy of the controller into display *so that the display object can send input to the controller which will *react to that input */ display = new DBDisplay(this); // tell the application to stop its process // when the window is closed display.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); // show the display on the screen display.show(); } // this method accepts the necessary params // to pass to the DBWorkHandler object. The // getQueryResults method in there will do the // heavy lifting public void getResults(String driverName, String hostName, String query, String username, String password) { ResultSet rs; //get the result set and build a model out of it rs = worker.getQueryResults(driverName, hostName, query, username, password); resultSetModel = new DBResultSetModel(rs); //call the method in Display to show the model display.showModel(resultSetModel); } } The above listing, DBController , routes messages where they are supposed to go in the application. The makeDefaultDisplay() method, which we call in the previous listing, instantiates a new object of type DBDisplay . This class is defined in the next listing. The only other main thing the application does is get a result set, so the controller passes that handler off as well.
12.5.3 DBDisplay.java
[View full width]/* File: DBDisplay.java Purpose: as this is a GUI application, this class shows all of the action as it happens. the text fields, button, and results table are organized for display. The window gets its title and dimensions set here. An ActionListener reacts to events that happen in the GUI (such as the button being pressed) */ import javax.swing.*; import java.awt.*; import java.awt.event.*; import javax.swing.table.*; import java.sql.*; public class DBDisplay extends JFrame { /* add fields for username, password, or DB */ // name elements ending with their type private JTextField hostField, driverField, queryField, usernameField; // this will put stars over the private JPasswordField passwordField; // button to send query to DB private JButton showResultsButton; // get a place to put things private Container contentPane; private JPanel tablePanel; // Hold a reference to the controller private DBController controller; /** Constructor */ public DBDisplay(DBController control) { // Get the controller controller = control; // Set the title and the dimensions setTitle("Query Tool"); setLocation(250, 150); // get the content pane contentPane = getContentPane(); // add the panels to it contentPane.add(makeControlPanel(), BorderLayout.NORTH); // let the panel determine how // to place items in the layout pack(); } // create a holder for the various // panels that hold the text entry fields private JPanel makeControlPanel() { JPanel panel = new JPanel(new GridLayout(0,1)); // add the little panels to the // control panel panel.add(makeHostPanel()); panel.add(makeUserPassPanel()); panel.add(makeQueryPanel()); panel.add(makeButtonPanel()); panel.setBorder (BorderFactory.createTitledBorder("Query DATA")); return (panel); } //end of makeControlPanel // this panel will hold the host/database name // field and the name of the driver. // The driver must be on your classpath private JPanel makeHostPanel() { JPanel panel = new JPanel(); panel.add(new JLabel(" Host:")); // the JTextField takes an int param // specifying number of columns hostField = new JTextField(15); panel.add(hostField); panel.add(new JLabel(" Driver Name:")); driverField = new JTextField(15); panel.add(driverField); return (panel); } // this panel holds text fields // for username and password private JPanel makeUserPassPanel() { JPanel panel = new JPanel(); panel.add(new JLabel(" Username:")); usernameField = new JTextField(15); panel.add(usernameField); panel.add(new JLabel(" Password:")); passwordField = new JPasswordField(15); panel.add(passwordField); return (panel); } // place query panel in control panel private JPanel makeQueryPanel() { JPanel panel = new JPanel(); // length 40 queryField = new JTextField(40); panel.add(new JLabel("Query")); panel.add(queryField); return(panel); } // show results button private JPanel makeButtonPanel() { JPanel panel = new JPanel(); showResultsButton = new JButton("Show Results"); // add an actionlistener to the button DisplayActionHandler buttonListener = new DisplayActionHandler(); showResultsButton.addActionListener(buttonListener); panel.add(showResultsButton); return(panel); } // show warning for bad queries private JLabel makeErrorLabel() { JLabel label = new JLabel("no results or error", JLabel.CENTER); label.setFont(new Font("Serif", Font.BOLD, 17)); return (label); } // Show the table made from the query results public void showModel(DBResultSetModel resultSetModel) { JTable table = new JTable(resultSetModel); JScrollPane scrollPane = new JScrollPane(table); // add the scrollpane to the contentpane contentPane.add(scrollPane, "Center"); pack(); } // Action Listener Inner Class private class DisplayActionHandler implements ActionListener { String username = ""; String password = ""; public void actionPerformed(ActionEvent actionEvent) { // test if the user has typed something // into the fields necessary for connecting to // the DB if(driverField.getText().length() != 0 && hostField.getText().length() != 0 && queryField.getText().length() != 0) { String driverName = driverField.getText(); String hostName = hostField.getText(); String query = queryField.getText(); if ( usernameField.getText().length() != 0) { String username = usernameField.getText(); } if (passwordField.getPassword().length != 0) { char[] passwordCA = passwordField.getPassword(); String password = passwordCA.toString(); } controller.getResults(driverName, hostName, query, username, password); } // if user leaves any of the necessary fields blank // show an alert else { JOptionPane.showMessageDialog(null, "Please type in some info dude, duh, Imean hello", "Error Message", JOptionPane.ERROR_MESSAGE); } } } } // eof: DBDisplay.java
The job of DBDisplay (the listing above) is to create panels and text fields and a button, and handle the events that the user creates when interacting with the application. When a result set is required, the app calls the WorkHandler , whose job it is to connect to the database and get a result set. Figures 12.1 and 12.2 show the GUI so far.
Figure 12.1. The initial view of the application on startup.
Figure 12.2. An error message is displayed when the user neglects to enter all required information.
12.5.4 DBWorkHandler.java
/* File: DBController.java Purpose: route messages between the display DBDisplay, which shows the panels holding buttons and text fields,and the DBWorkHandler */ import java.sql.*; import javax.swing.table.*; import java.util.*; public class DBWorkHandler { private Connection connection; private ResultSet rs; // no-arg constructor public DBWorkHandler() { } public ResultSet getQueryResults(String driverName, String hostName, String query, String username, String password) { try { Class.forName(driverName); Connection con = DriverManager.getConnection(hostName, username, password); // create a statement Statement s; s = con.createStatement(); // hit the database rs = s.executeQuery(query); // return the result set from the DB return (rs); } catch (ClassNotFoundException cnfe) { System.err.println("Error loading driver: " + cnfe); } catch(SQLException sqle) { System.err.println("Connection error: " + sqle); } return (null); } } In DBWorkHandler.java , the database is hit, and exceptions are caught that relate to that operation. The DBController class hands off the result set to the DBResultSetModel class, which will handle the data for display and get it sent back up to the DBDisplay when it is ready.
12.5.5 DBResultSetModel.java
/* File: DBResultSetModel.java Purpose: get the results and metadata */ import java.sql.*; import javax.swing.table.*; import javax.swing.*; public class DBResultSetModel extends AbstractTableModel{ // vars private ResultSet rs; private ResultSetMetaData rsmd; // constructor public DBResultSetModel(ResultSet inrs) { //accept the result set rs = inrs; // make the result set metadata // from the result set try { rsmd = getResultSet().getMetaData(); } catch(SQLException sqle) { System.out.println("Constructor SQL Error: " + sqle); } catch(Exception e) { System.out.println("Constructor Error: " + e); } // we have to override these methods } public int getColumnCount() { try { return rsmd.getColumnCount(); } catch(SQLException sqle) { System.out.println("getColumnCount SQL Error: " + sqle); return 0; } catch(Exception e) { System.out.println("getColumnCount Error: " + e); return 0; } } public String getColumnName(int columnNum) { try { return rsmd.getColumnName(columnNum + 1); } catch(SQLException sqle) { System.out.println("getColumnName SQL Error: " + sqle); return ""; } catch(Exception e) { System.out.println("getColumnName Error: " + e); return ""; } } public int getRowCount() { try { ResultSet temprs = getResultSet(); temprs.last(); return temprs.getRow(); } catch(SQLException sqle) { System.out.println("getRowCount SQL Error: " + sqle); return 0; } catch(Exception e) { System.out.println("getRowCount Error: " + e); return 0; } } public Object getValueAt(int rowNum, int columnNum) { try { ResultSet temprs = getResultSet(); rs.absolute(rowNum + 1); return rs.getObject(columnNum + 1); } catch(SQLException sqle) { System.out.println("getValueAt SQL Error: " + sqle); return null; } } public ResultSet getResultSet() { return rs; } } // end of app Figure 12.3 shows the results of a query in our application.
Figure 12.3. The application with a query result set in the display.
Again, this application works with GUI programming constructs that we have not covered. In the event that you are interested in this kind of development, this example gives you an idea of what is required to create such interfaces. You can run this example locally, with no Internet connection, because it is a standalone executable program. You can use this example to create text fields and buttons to enhance and extend your work with applets. Finally, this is a fun tool that can even be useful in development, or can be extended to create a front end to command line database systems or text files that you may be working with as you develop.
 |
| Top |
EAN: 2147483647
Pages: 206