querytool.java

hsqldb是100％java实现的数据库,是一个开放源代码的JAVA数据库 l 具有标准的SQL语法和JAVA接口 l HSQLDB可以自由使用和分发 l 非常简洁和快速的

/* Copyright (c) 1995-2000, The Hypersonic SQL Group.
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the Hypersonic SQL Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE HYPERSONIC SQL GROUP,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals 
 * on behalf of the Hypersonic SQL Group.
 *
 *
 * For work added by the HSQL Development Group:
 *
 * Copyright (c) 2001-2005, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


package org.hsqldb.util;

import java.applet.Applet;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.awt.BorderLayout;
import java.awt.Button;
import java.awt.Choice;
import java.awt.Event;
import java.awt.Font;
import java.awt.Frame;
import java.awt.Label;
import java.awt.Menu;
import java.awt.MenuBar;
import java.awt.Panel;
import java.awt.SystemColor;
import java.awt.TextArea;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowEvent;
import java.awt.event.WindowListener;

import org.hsqldb.lib.java.JavaSystem;

/**
 * Simple demonstration applet
 *
 *
 * @author Thomas Mueller (Hypersonic SQL Group)
 * @version 1.7.0
 * @since Hypersonic SQL
 */
public class QueryTool extends Applet
implements WindowListener, ActionListener {

    static Properties pProperties = new Properties();
    boolean           bApplication;

    /**
     * You can start QueryTool without a browser and applet
     * using using this method. Type 'java QueryTool' to start it.
     * This is necessary if you want to use the standalone version
     * because appletviewer and internet browers do not allow the
     * applet to write to disk.
     */
    static Frame fMain;

    /**
     * Method declaration
     *
     *
     * @param arg
     */
    public static void main(String[] arg) {

        fMain = new Frame("Query Tool");

        QueryTool q = new QueryTool();

        q.bApplication = true;

        for (int i = 0; i < arg.length; i++) {
            String p = arg[i];

            if (p.equals("-?")) {
                printHelp();
            }

            if (p.charAt(0) == '-') {
                pProperties.put(p.substring(1), arg[i + 1]);

                i++;
            }
        }

        q.init();
        q.start();
        fMain.add("Center", q);

        MenuBar menu = new MenuBar();
        Menu    file = new Menu("File");

        file.add("Exit");
        file.addActionListener(q);
        menu.add(file);
        fMain.setMenuBar(menu);
        fMain.setSize(500, 400);
        fMain.show();
        fMain.addWindowListener(q);
    }

    Connection cConn;
    Statement  sStatement;

    /**
     * Initializes the window and the database and inserts some test data.
     */
    public void init() {

        initGUI();

        Properties p = pProperties;

        if (!bApplication) {

            // default for applets is in-memory (.)
            p.put("database", ".");

            try {

                // but it may be also a HTTP connection (http://)
                // try to use url as provided on the html page as parameter
                pProperties.put("database", getParameter("database"));
            } catch (Exception e) {}
        }

        String  driver   = p.getProperty("driver", "org.hsqldb.jdbcDriver");
        String  url      = p.getProperty("url", "jdbc:hsqldb:");
        String  database = p.getProperty("database", ".");
        String  user     = p.getProperty("user", "sa");
        String  password = p.getProperty("password", "");
        boolean test = p.getProperty("test", "true").equalsIgnoreCase("true");
        boolean log = p.getProperty("log", "true").equalsIgnoreCase("true");

        try {
            if (log) {
                trace("driver  =" + driver);
                trace("url     =" + url);
                trace("database=" + database);
                trace("user    =" + user);
                trace("password=" + password);
                trace("test    =" + test);
                trace("log     =" + log);
                JavaSystem.setLogToSystem(true);
            }

            // As described in the JDBC FAQ:
            // http://java.sun.com/products/jdbc/jdbc-frequent.html;
            // Why doesn't calling class.forName() load my JDBC driver?
            // There is a bug in the JDK 1.1.x that can cause Class.forName() to fail.
//            new org.hsqldb.jdbcDriver();
            Class.forName(driver).newInstance();

            cConn = DriverManager.getConnection(url + database, user,
                                                password);
        } catch (Exception e) {
            System.out.println("QueryTool.init: " + e.getMessage());
            e.printStackTrace();
        }

        sRecent = new String[iMaxRecent];
        iRecent = 0;

        try {
            sStatement = cConn.createStatement();
        } catch (SQLException e) {
            System.out.println("Exception: " + e);
        }

        if (test) {
            insertTestData();
        }

        txtCommand.requestFocus();
    }

    /**
     * Method declaration
     *
     *
     * @param s
     */
    void trace(String s) {
        System.out.println(s);
    }

    /**
     * This is function handles the events when a button is clicked or
     * when the used double-clicked on the listbox of recent commands.
     */
    public boolean action(Event evt, Object arg) {

        String s = arg.toString();

        if (s.equals("Execute")) {
            String   sCmd = txtCommand.getText();
            String[] g    = new String[1];

            try {
                sStatement.execute(sCmd);

                int r = sStatement.getUpdateCount();

                if (r == -1) {
                    formatResultSet(sStatement.getResultSet());
                } else {
                    g[0] = "update count";

                    gResult.setHead(g);

                    g[0] = String.valueOf(r);

                    gResult.addRow(g);
                }

                setRecent(txtCommand.getText());
            } catch (SQLException e) {
                g[0] = "SQL Error";

                gResult.setHead(g);

                g[0] = e.getMessage();

                gResult.addRow(g);
            }

            gResult.repaint();
            txtCommand.selectAll();
            txtCommand.requestFocus();
        } else if (s.equals("Script")) {
            String sScript = getScript();

            txtCommand.setText(sScript);
            txtCommand.selectAll();
            txtCommand.requestFocus();
        } else if (s.equals("Import")) {
            String sImport = getImport();

            txtCommand.setText(sImport);
            txtCommand.selectAll();
            txtCommand.requestFocus();
        } else if (s.equals("Exit")) {
            System.exit(0);
        } else {    // recent
            txtCommand.setText(s);
        }

        return true;
    }

    /**
     * Method declaration
     *
     *
     * @param r
     */
    void formatResultSet(ResultSet r) {

        try {
            ResultSetMetaData m   = r.getMetaData();
            int               col = m.getColumnCount();
            String[]          h   = new String[col];

            for (int i = 1; i <= col; i++) {
                h[i - 1] = m.getColumnLabel(i);
            }

            gResult.setHead(h);

            while (r.next()) {
                for (int i = 1; i <= col; i++) {
                    h[i - 1] = r.getString(i);

                    if (r.wasNull()) {
                        h[i - 1] = "(null)";
                    }
                }

                gResult.addRow(h);