Running a query and displaying the result set

The following code example is a Swing application that performs the following operations:

• Prompts the user to select a database from those available
• Allows the user to select a query from the available queries in the selected database
• Displays the rows of the result set in a table.

The selection and execution of the query takes place in the run method. The display of the result set happens in the showResults method. Here is the complete example:

static void run(String title, CqProvider provider, Viewer viewer)
    throws WvcmException
    {
        ResourceList<CqUserDb> databases =
            setUserFriendlyLocation(Utilities
                .getUserDbList(provider,
                               new PropertyRequest(CqUserDb.USER_FRIENDLY_LOCATION)));
        CqUserDb userDb = (CqUserDb)JOptionPane.showInputDialog
            (null, "Choose a Database to Explore", title, 
             JOptionPane.INFORMATION_MESSAGE, 
             null, databases.toArray(), databases.get(0));
    
        if (userDb == null) System.exit(0);
        
        userDb = (CqUserDb)userDb.doReadProperties
            (new PropertyRequest(CqUserDb.ALL_QUERIES.nest(CqQuery.USER_FRIENDLY_LOCATION)));
    
        // Convert the list to a sorted array for use in the selection dialog
        CqQuery[] queries = 
            setUserFriendlyLocation(userDb.getAllQueries()).toArray(new CqQuery[]{});
    
        Arrays.sort(queries, new Comparator<CqQuery>(){
                public int compare(CqQuery arg0, CqQuery arg1)
                { return arg0.toString().compareTo(arg1.toString()); }});
    
        // Present the list of queries to the user and allow the user to select one
        CqQuery query = (CqQuery)JOptionPane.showInputDialog
                    (null, "Choose a Query to Execute", 
                     "All Queries in " + userDb.location().string(), 
                     JOptionPane.INFORMATION_MESSAGE, null, 
                     queries, queries[0]);
        
        if (query == null) System.exit(0);
        
        CqResultSet results = 
            query.doExecute(1, Long.MAX_VALUE, CqQuery.COUNT_ROWS);
        
        // If the query executed properly, save the data and prepare it for display
        if (results.hasNext()) {
            // Column information accessed from the viewer
            g_columns = results.getColumnLabels();
            g_cell = new CqRowData[(int)results.getRowCount()];
    
            for (CqRowData row: results)
                (g_cell[(int)row.getRowNumber()-1] = row).getValues();
            
            // Display the query result data
            showResults(query.location().string(), viewer);
        }
    }
    
    /** The result set made accessible to the GUI components for display */
    static CqRowData[] g_cell;
    /** The column headings made accessible to the GUI components for display */
    static String[] g_columns;
    
    /**
     * Displays the result set (in g_cell) in a table.
     * 
     * @param title The title string for the result set window
     * @param viewer A Viewer instance to be used for a detailed display of a
     *            single resource of the result set. May be null, in which case
     *            the option to display a single resource is not presented.
     */
    static void showResults(String title, final Viewer viewer) {
        // Define the table model for the JTable window; one column for each
        // query display field and one row for each row of the query result set.
        TableModel dataModel = new AbstractTableModel() {
    		 		 private static final long serialVersionUID = -3764643269044024406L;
    		 		 public int getColumnCount() { return g_columns.length; }
            public int getRowCount() { return g_cell.length;}
            public Object getValueAt(int row, int col) 
                { return g_cell[row].getValues()[col]; }
            public String getColumnName(int col)
                { return g_columns[col]; }
        };
        
        // Construct the query result window with an optional button for
        // displaying the record in a selected row (used in the View Record and
        // Modify Record examples)
        final JFrame frame = new JFrame(title);
        final JTable table = new JTable(dataModel);
        JPanel panel = new JPanel(new BorderLayout());
        
        if (viewer != null) {
            JButton button = new JButton("Open");
    
            panel.add(button, BorderLayout.SOUTH);
            button.addActionListener(new ActionListener(){
                    public void actionPerformed(ActionEvent arg0)
                    {
                        int[] selected = table.getSelectedRows();
                        
                        for (int i = 0; i < selected.length; ++i)
                            try {
                                viewer.view((CqRecord) g_cell[selected[i]]
                                    .getRecord());
                            } catch (WvcmException e) {
                                Utilities.exception(frame, "View Record", e);
                            }
                    }
                });
        }
        
        panel.add(new JScrollPane(table), BorderLayout.CENTER);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setContentPane(panel);
        frame.setBounds(300, 300, 600, 300);
        frame.setVisible(true);
    }
    
    static <U extends CqResource> ResourceList<U> 
    setUserFriendlyLocation(ResourceList<U> list)
    throws WvcmException
    {
        for (U res: list)
            res.modifyLocation(res.getUserFriendlyLocation());
    
        return list;
    }
    
    /**
     * A simple interface for an object that will display a Record resource.
     * (Used by extensions to the ExecuteQuery example.)
     */
    static interface Viewer {
        /**
         * Displays a Record resource
         * @param resource The Record proxy for the record to be displayed.
         * @return TODO
         */
        JFrame view(CqRecord resource);
    }
    
    /**
     * The main program for the ExecuteQuery example.
     * @param args Not used.
     * @throws Exception If a provider cannot be instantiated.
     */
    public static void main(String[] args) throws Exception
    {
        try {
            run("Execute Query", Utilities.getProvider().cqProvider(), null);
        } catch(Throwable ex) {
            Utilities.exception(null, "Execute Query", ex);
            System.exit(0);
        }
    }

In this example, the list of available databases is constructed as in the previous lesson. And the list is presented to the user for selection of a single database to login to.

After the user selects a user database, the ALL_QUERIES property of that database is read into the application. The value of this property is a ResourceList of CqQuery proxies. This list is sorted on the location of the query and presented to the user for selection of a single query to execute.

For selection of the database and selection of the query, the same general-purpose Swing method, JOptionPane.showInputDialog, is used. The input is the array of the proxies to select from and the result is the selected proxy. The proxy toString() method is used to generate the list displayed to the user. The toString() method of a proxy generates an image of the location field of the proxy, i.e. Resource.location().string().

Because the proxy's location is being displayed, we need to make sure that the proxy's location is user-friendly, that is, composed of segmented path names instead of database ids. Servers are free to use any form of location in the proxies that they return and. in general, will select the format that is most efficiently processed if the proxy is used to get back to the server. The most efficient format is rarely user-friendly. In any case, a client should not assume what form of location is used. So, when we request the database list and the query list we also request the USER_FRIENDLY_LOCATION property of each item on the list. Then, in the setUserFriendlyLocation method, we modify each proxy's location with its user-friendly version.