com.ingby.socbox.bischeck.test.JDBCtest.java Source code

Java tutorial

Introduction

Here is the source code for com.ingby.socbox.bischeck.test.JDBCtest.java

Source

/*
#
# Copyright (C) 2010-2012 Anders Hl, Ingenjorsbyn AB
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
 */

package com.ingby.socbox.bischeck.test;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.CommandLineParser;
import org.apache.commons.cli.GnuParser;
import org.apache.commons.cli.HelpFormatter;
import org.apache.commons.cli.Options;

import com.ingby.socbox.bischeck.Util;

public class JDBCtest {
    private static boolean verbose = false;

    static public void main(String[] args)
            throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

        CommandLineParser parser = new GnuParser();
        CommandLine line = null;

        // create the Options
        Options options = new Options();
        options.addOption("u", "usage", false, "show usage.");
        options.addOption("c", "connection", true, "the connection url");
        options.addOption("s", "sql", true, "the sql statement to run");
        options.addOption("m", "meta", true, "get the table meta data");
        options.addOption("C", "columns", true, "the number of columns to display, default 1");
        options.addOption("d", "driver", true, "the driver class");
        options.addOption("v", "verbose", false, "verbose outbout");

        try {
            // parse the command line arguments
            line = parser.parse(options, args);
        } catch (org.apache.commons.cli.ParseException e) {
            System.out.println("Command parse error:" + e.getMessage());
            HelpFormatter formatter = new HelpFormatter();
            formatter.printHelp("JDBCtest", options);
            Util.ShellExit(1);
        }

        if (line.hasOption("verbose")) {
            verbose = true;
        }

        if (line.hasOption("usage")) {
            HelpFormatter formatter = new HelpFormatter();
            formatter.printHelp("Bischeck", options);
            Util.ShellExit(0);
        }

        String driverclassname = null;
        if (!line.hasOption("driver")) {
            System.out.println("Driver class must be set");
            Util.ShellExit(1);
        } else {
            driverclassname = line.getOptionValue("driver");
            outputln("DriverClass: " + driverclassname);
        }

        String connectionname = null;
        if (!line.hasOption("connection")) {
            System.out.println("Connection url must be set");
            Util.ShellExit(1);
        } else {
            connectionname = line.getOptionValue("connection");
            outputln("Connection: " + connectionname);
        }

        String sql = null;
        String tablename = null;

        if (line.hasOption("sql")) {
            sql = line.getOptionValue("sql");
            outputln("SQL: " + sql);

        }

        if (line.hasOption("meta")) {
            tablename = line.getOptionValue("meta");
            outputln("Table: " + tablename);
        }

        int nrColumns = 1;
        if (line.hasOption("columns")) {
            nrColumns = new Integer(line.getOptionValue("columns"));
        }

        long execStart = 0l;
        long execEnd = 0l;
        long openStart = 0l;
        long openEnd = 0l;
        long metaStart = 0l;
        long metaEnd = 0l;

        Class.forName(driverclassname).newInstance();
        openStart = System.currentTimeMillis();
        Connection conn = DriverManager.getConnection(connectionname);
        openEnd = System.currentTimeMillis();

        if (tablename != null) {
            ResultSet rsCol = null;
            metaStart = System.currentTimeMillis();
            DatabaseMetaData md = conn.getMetaData();
            metaEnd = System.currentTimeMillis();

            rsCol = md.getColumns(null, null, tablename, null);
            if (verbose) {
                tabular("COLUMN_NAME");
                tabular("TYPE_NAME");
                tabular("COLUMN_SIZE");
                tabularlast("DATA_TYPE");
                outputln("");
            }

            while (rsCol.next()) {
                tabular(rsCol.getString("COLUMN_NAME"));
                tabular(rsCol.getString("TYPE_NAME"));
                tabular(rsCol.getString("COLUMN_SIZE"));
                tabularlast(rsCol.getString("DATA_TYPE"));
                outputln("", true);
            }
        }

        if (sql != null) {
            Statement stat = conn.createStatement();
            stat.setQueryTimeout(10);

            execStart = System.currentTimeMillis();
            ResultSet res = stat.executeQuery(sql);
            ResultSetMetaData rsmd = res.getMetaData();
            execEnd = System.currentTimeMillis();

            if (verbose) {
                for (int i = 1; i < nrColumns + 1; i++) {
                    if (i != nrColumns)
                        tabular(rsmd.getColumnName(i));
                    else
                        tabularlast(rsmd.getColumnName(i));
                }
                outputln("");
            }
            while (res.next()) {
                for (int i = 1; i < nrColumns + 1; i++) {
                    if (i != nrColumns)
                        tabular(res.getString(i));
                    else
                        tabularlast(res.getString(i));
                }
                outputln("", true);
            }

            stat.close();
            res.close();
        }

        conn.close();

        // Print the execution times
        outputln("Open time: " + (openEnd - openStart) + " ms");

        if (line.hasOption("meta")) {
            outputln("Meta time: " + (metaEnd - metaStart) + " ms");
        }

        if (line.hasOption("sql")) {
            outputln("Exec time: " + (execEnd - execStart) + " ms");
        }
    }

    private static void tabular(String str) {
        if (verbose) {
            output(str + "\t| ");
        } else {
            output(str + "|", true);
        }
    }

    private static void tabularlast(String str) {
        if (verbose) {
            output(str);
        } else {
            output(str, true);
        }
    }

    private static void outputln(String str) {
        outputln(str, verbose);
    }

    private static void outputln(String str, boolean verbose) {
        if (verbose) {
            System.out.println(str);
        }
    }

    private static void output(String str) {
        output(str, verbose);
    }

    private static void output(String str, boolean verbose) {
        if (verbose) {
            System.out.print(str);
        }
    }

}