Java/Database SQL JDBC/Metadata DB Info

A database MetaData query

 
/*
 * Copyright (c) Ian F. Darwin, http://www.darwinsys.ru/, 1996-2002.
 * All rights reserved. Software written by Ian F. Darwin and others.
 * $Id: LICENSE,v 1.8 2004/02/09 03:33:38 ian Exp $
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 * 2. 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.
 *
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR 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 AUTHOR 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.
 * 
 * Java, the Duke mascot, and all variants of Sun"s Java "steaming coffee
 * cup" logo are trademarks of Sun Microsystems. Sun"s, and James Gosling"s,
 * pioneering role in inventing and promulgating (and standardizing) the Java 
 * language and environment is gratefully acknowledged.
 * 
 * The pioneering role of Dennis Ritchie and Bjarne Stroustrup, of AT&T, for
 * inventing predecessor languages C and C++ is also gratefully acknowledged.
 */
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
 * A database MetaData query
 * 
 * @version $Id: JDBCMeta.java,v 1.7 2004/03/09 04:27:42 ian Exp $
 */
public class JDBCMeta {
  public static void main(String[] av) {
    int i;
    try {
         // Load the driver
      Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
      // Enable logging
      // DriverManager.setLogStream(System.err);
      System.out.println("Getting Connection");
      Connection conn = DriverManager.getConnection (
        "jdbc:odbc:Companies",
        "ian", "");  // user, passwd
      // Get a Database MetaData as a way of interrogating
      // the names of the tables in this database.
      DatabaseMetaData meta = conn.getMetaData();
      System.out.println("We are using " + meta.getDatabaseProductName());
      System.out
          .println("Version is " + meta.getDatabaseProductVersion());
      int txisolation = meta.getDefaultTransactionIsolation();
      System.out.println("Database default transaction isolation is "
          + txisolation + " ("
          + transactionIsolationToString(txisolation) + ").");
      conn.close();
      System.out.println("All done!");
    }  catch (ClassNotFoundException e) {
      System.out.println("Can"t load driver " + e);
    } catch (SQLException ex) {
      System.out.println("Database access failed:");
      System.out.println(ex);
    }
  }
  /**
   * Convert a TransactionIsolation int (defined in java.sql.Connection) to
   * the corresponding printable string.
   */
  public static String transactionIsolationToString(int txisolation) {
    switch (txisolation) {
    case Connection.TRANSACTION_NONE:
      // transactions not supported.
      return "TRANSACTION_NONE";
    case Connection.TRANSACTION_READ_UNCOMMITTED:
      // All three phenomena can occur
      return "TRANSACTION_NONE";
    case Connection.TRANSACTION_READ_COMMITTED:
      // Dirty reads are prevented; non-repeatable reads and
      // phantom reads can occur.
      return "TRANSACTION_READ_COMMITTED";
    case Connection.TRANSACTION_REPEATABLE_READ:
      // Dirty reads and non-repeatable reads are prevented;
      // phantom reads can occur.
      return "TRANSACTION_REPEATABLE_READ";
    case Connection.TRANSACTION_SERIALIZABLE:
      // All three phenomena prvented; slowest!
      return "TRANSACTION_SERIALIZABLE";
    default:
      throw new IllegalArgumentException(txisolation
          + " not a valid TX_ISOLATION");
    }
  }
}

Database Info

 
/*
MySQL and Java Developer"s Guide
Mark Matthews, Jim Cole, Joseph D. Gradecki
Publisher Wiley,
Published February 2003, 
ISBN 0471269239
*/

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class DatabaseInfo extends HttpServlet {
  public void doGet(HttpServletRequest inRequest,
      HttpServletResponse outResponse) throws ServletException,
      IOException {
    PrintWriter out = null;
    Connection connection = null;
    Statement statement;
    ResultSet rs;
    outResponse.setContentType("text/html");
    out = outResponse.getWriter();
    try {
      Context ctx = new InitialContext();
      DataSource ds = (DataSource) ctx
          .lookup("java:comp/env/jdbc/AccountsDB");
      connection = ds.getConnection();
      DatabaseMetaData md = connection.getMetaData();
      statement = connection.createStatement();
      out
          .println("<HTML><HEAD><TITLE>Database Server Information</TITLE></HEAD>");
      out.println("<BODY>");
      out.println("<H1>General Source Information</H1>");
      out.println("getURL() - " + md.getURL() + "<BR>");
      out.println("getUserName() - " + md.getUserName() + "<BR>");
      out.println("getDatabaseProductVersion - "
          + md.getDatabaseProductVersion() + "<BR>");
      out.println("getDriverMajorVersion - " + md.getDriverMajorVersion()
          + "<BR>");
      out.println("getDriverMinorVersion - " + md.getDriverMinorVersion()
          + "<BR>");
      out.println("nullAreSortedHigh - " + md.nullsAreSortedHigh()
          + "<BR>");
      out.println("<H1>Feature Support</H1>");
      out.println("supportsAlterTableWithDropColumn - "
          + md.supportsAlterTableWithDropColumn() + "<BR>");
      out.println("supportsBatchUpdates - " + md.supportsBatchUpdates()
          + "<BR>");
      out.println("supportsTableCorrelationNames - "
          + md.supportsTableCorrelationNames() + "<BR>");
      out.println("supportsPositionedDelete - "
          + md.supportsPositionedDelete() + "<BR>");
      out.println("supportsFullOuterJoins - "
          + md.supportsFullOuterJoins() + "<BR>");
      out.println("supportsStoredProcedures - "
          + md.supportsStoredProcedures() + "<BR>");
      out.println("supportsMixedCaseQuotedIdentifiers - "
          + md.supportsMixedCaseQuotedIdentifiers() + "<BR>");
      out.println("supportsANSI92EntryLevelSQL - "
          + md.supportsANSI92EntryLevelSQL() + "<BR>");
      out.println("supportsCoreSQLGrammar - "
          + md.supportsCoreSQLGrammar() + "<BR>");
      out.println("<H1>Data Source Limits</H1>");
      out.println("getMaxRowSize - " + md.getMaxRowSize() + "<BR>");
      out.println("getMaxStatementLength - " + md.getMaxStatementLength()
          + "<BR>");
      out.println("getMaxTablesInSelect - " + md.getMaxTablesInSelect()
          + "<BR>");
      out.println("getMaxConnections - " + md.getMaxConnections()
          + "<BR>");
      out.println("getMaxCharLiteralLength - "
          + md.getMaxCharLiteralLength() + "<BR>");
      out.println("<H1>SQL Object Available</H1>");
      out.println("getTableTypes()<BR><UL>");
      rs = md.getTableTypes();
      while (rs.next()) {
        out.println("<LI>" + rs.getString(1));
      }
      out.println("</UL>");
      out.println("getTables()<BR><UL>");
      rs = md.getTables("accounts", "", "%", new String[0]);
      while (rs.next()) {
        out.println("<LI>" + rs.getString("TABLE_NAME"));
      }
      out.println("</UL>");
      out.println("<H1>Transaction Support</H1>");
      out.println("getDefaultTransactionIsolation() - "
          + md.getDefaultTransactionIsolation() + "<BR>");
      out.println("dataDefinitionIgnoredInTransactions() - "
          + md.dataDefinitionIgnoredInTransactions() + "<BR>");
      out.println("<H1>General Source Information</H1>");
      out.println("getMaxTablesInSelect - " + md.getMaxTablesInSelect()
          + "<BR>");
      out.println("getMaxColumnsInTable - " + md.getMaxColumnsInTable()
          + "<BR>");
      out.println("getTimeDateFunctions - " + md.getTimeDateFunctions()
          + "<BR>");
      out.println("supportsCoreSQLGrammar - "
          + md.supportsCoreSQLGrammar() + "<BR>");
      out.println("getTypeInfo()<BR><UL>");
      rs = md.getTypeInfo();
      while (rs.next()) {
        out.println("<LI>" + rs.getString(1));
      }
      out.println("</UL>");
      out.println("</BODY></HTML>");
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  public void doPost(HttpServletRequest inRequest,
      HttpServletResponse outResponse) throws ServletException,
      IOException {
    doGet(inRequest, outResponse);
  }
}

DatabaseMetaData class to obtain information about the

 
/*
 * Copyright (c) 2000 David Flanagan.  All rights reserved.
 * This code is from the book Java Examples in a Nutshell, 2nd Edition.
 * It is provided AS-IS, WITHOUT ANY WARRANTY either expressed or implied.
 * You may study, use, and modify it for any non-commercial purpose.
 * You may distribute it non-commercially as long as you retain this notice.
 * For a commercial use license, or to purchase the book (recommended),
 * visit http://www.davidflanagan.ru/javaexamples2.
 */
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
 * This class uses the DatabaseMetaData class to obtain information about the
 * database, the JDBC driver, and the tables in the database, or about the
 * columns of a named table.
 */
public class GetDBInfo {
  public static void main(String[] args) {
    Connection c = null; // The JDBC connection to the database server
    try {
      // Look for the properties file DB.props in the same directory as
      // this program. It will contain default values for the various
      // parameters needed to connect to a database
      Properties p = new Properties();
      try {
        p.load(GetDBInfo.class.getResourceAsStream("DB.props"));
      } catch (Exception e) {
      }
      // Get default values from the properties file
      String driver = p.getProperty("driver"); // Driver class name
      String server = p.getProperty("server", ""); // JDBC URL for server
      String user = p.getProperty("user", ""); // db user name
      String password = p.getProperty("password", ""); // db password
      // These variables don"t have defaults
      String database = null; // The db name (appended to server URL)
      String table = null; // The optional name of a table in the db
      // Parse the command-line args to override the default values above
      for (int i = 0; i < args.length; i++) {
        if (args[i].equals("-d"))
          driver = args[++i]; //-d <driver>
        else if (args[i].equals("-s"))
          server = args[++i];//-s <server>
        else if (args[i].equals("-u"))
          user = args[++i]; //-u <user>
        else if (args[i].equals("-p"))
          password = args[++i];
        else if (database == null)
          database = args[i]; // <dbname>
        else if (table == null)
          table = args[i]; // <table>
        else
          throw new IllegalArgumentException("Unknown argument: "
              + args[i]);
      }
      // Make sure that at least a server or a database were specified.
      // If not, we have no idea what to connect to, and cannot continue.
      if ((server.length() == 0) && (database.length() == 0))
        throw new IllegalArgumentException("No database specified.");
      // Load the db driver, if any was specified.
      if (driver != null)
        Class.forName(driver);
      // Now attempt to open a connection to the specified database on
      // the specified server, using the specified name and password
      c = DriverManager.getConnection(server + database, user, password);
      // Get the DatabaseMetaData object for the connection. This is the
      // object that will return us all the data we"re interested in here
      DatabaseMetaData md = c.getMetaData();
      // Display information about the server, the driver, etc.
      System.out.println("DBMS: " + md.getDatabaseProductName() + " "
          + md.getDatabaseProductVersion());
      System.out.println("JDBC Driver: " + md.getDriverName() + " "
          + md.getDriverVersion());
      System.out.println("Database: " + md.getURL());
      System.out.println("User: " + md.getUserName());
      // Now, if the user did not specify a table, then display a list of
      // all tables defined in the named database. Note that tables are
      // returned in a ResultSet, just like query results are.
      if (table == null) {
        System.out.println("Tables:");
        ResultSet r = md.getTables("", "", "%", null);
        while (r.next())
          System.out.println("\t" + r.getString(3));
      }
      // Otherwise, list all columns of the specified table.
      // Again, information about the columns is returned in a ResultSet
      else {
        System.out.println("Columns of " + table + ": ");
        ResultSet r = md.getColumns("", "", table, "%");
        while (r.next())
          System.out.println("\t" + r.getString(4) + " : "
              + r.getString(6));
      }
    }
    // Print an error message if anything goes wrong.
    catch (Exception e) {
      System.err.println(e);
      if (e instanceof SQLException)
        System.err.println(((SQLException) e).getSQLState());
      System.err.println("Usage: java GetDBInfo [-d <driver] "
          + "[-s <dbserver>]\n"
          + "\t[-u <username>] [-p <password>] <dbname>");
    }
    // Always remember to close the Connection object when we"re done!
    finally {
      try {
        c.close();
      } catch (Exception e) {
      }
    }
  }
}

Database MetaData: Database version

 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class TestDatabaseMetaDataToolDatabaseInformation {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    DatabaseMetaData meta = conn.getMetaData();
    // Oracle (and some other vendors) do not support
    // some the following methods; therefore, we need
    // to use try-catch block.
    try {
      int majorVersion = meta.getDatabaseMajorVersion();
      System.out.println("major Version: " + majorVersion);
    } catch (Exception e) {
      System.out.println("major Version: unsupported feature");
    }
    try {
      int minorVersion = meta.getDatabaseMinorVersion();
      System.out.println("minorVersion" + minorVersion);
    } catch (Exception e) {
      System.out.println("minorVersion unsupported feature");
    }
    String productName = meta.getDatabaseProductName();
    String productVersion = meta.getDatabaseProductVersion();
    System.out.println("productName" + productName);
    System.out.println("productVersion" + productVersion);
    conn.close();
  }
  
  
  
  
  
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/databaseName";
    String username = "root";
    String password = "root";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Detect if a table exists

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection c = null;
    DatabaseMetaData dbm = c.getMetaData();
    ResultSet rs = dbm.getTables(null, null, "employee", null);
    if (rs.next()) {
      System.out.println("Table exists"); 
    } else {
      System.out.println("Table does not exist"); 
    }
  }
}

Driver Property Info

 
/*
Database Programming with JDBC and Java, Second Edition
By George Reese
ISBN: 1-56592-616-1
Publisher: O"Reilly
*/

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
 * Examples 4.4 through 4.6.
 */
public class TerminalMonitor {
  static Connection connection = null;
  static BufferedReader input;
  static public void main(String args[]) {
    DriverPropertyInfo[] required;
    StringBuffer buffer = new StringBuffer();
    Properties props = new Properties();
    boolean connected = false;
    Driver driver;
    String url;
    int line = 1; // Mark current input line
    if (args.length < 1) {
      System.out.println("Syntax: <java -Djdbc.drivers=DRIVER_NAME "
          + "TerminalMonitor JDBC_URL>");
      return;
    }
    url = args[0];
    // We have to get a reference to the driver so we can
    // find out what values to prompt the user for in order
    // to make a connection.
    try {
      driver = DriverManager.getDriver(url);
    } catch (SQLException e) {
      e.printStackTrace();
      System.err.println("Unable to find a driver for the specified "
          + "URL.");
      System.err.println("Make sure you passed the jdbc.drivers "
          + "property on the command line to specify "
          + "the driver to be used.");
      return;
    }
    try {
      required = driver.getPropertyInfo(url, props);
    } catch (SQLException e) {
      e.printStackTrace();
      System.err.println("Unable to get driver property information.");
      return;
    }
    input = new BufferedReader(new InputStreamReader(System.in));
    // some drivers do not implement this properly
    // if that is the case, prompt for user name and password
    try {
      if (required.length < 1) {
        props.put("user", prompt("user: "));
        props.put("password", prompt("password: "));
      } else {
        // for each required attribute in the driver property info
        // prompt the user for the value
        for (int i = 0; i < required.length; i++) {
          if (!required[i].required) {
            continue;
          }
          props
              .put(required[i].name, prompt(required[i].name
                  + ": "));
        }
      }
    } catch (IOException e) {
      e.printStackTrace();
      System.err.println("Unable to read property info.");
      return;
    }
    // Make the connection.
    try {
      connection = DriverManager.getConnection(url, props);
    } catch (SQLException e) {
      e.printStackTrace();
      System.err.println("Unable to connect to the database.");
    }
    connected = true;
    System.out.println("Connected to " + url);
    // Enter into a user input loop
    while (connected) {
      String tmp, cmd;
      // Print a prompt
      if (line == 1) {
        System.out.print("TM > ");
      } else {
        System.out.print(line + " -> ");
      }
      System.out.flush();
      // Get the next line of input
      try {
        tmp = input.readLine();
      } catch (java.io.IOException e) {
        e.printStackTrace();
        return;
      }
      // Get rid of extra space in the command
      cmd = tmp.trim();
      // The user wants to commit pending transactions
      if (cmd.equals("commit")) {
        try {
          connection.rumit();
          System.out.println("Commit successful.");
        } catch (SQLException e) {
          System.out.println("Error in commit: " + e.getMessage());
        }
        buffer = new StringBuffer();
        line = 1;
      }
      // The user wants to execute the current buffer
      else if (cmd.equals("go")) {
        if (!buffer.equals("")) {
          try {
            executeStatement(buffer);
          } catch (SQLException e) {
            System.out.println(e.getMessage());
          }
        }
        buffer = new StringBuffer();
        line = 1;
        continue;
      }
      // The user wants to quit
      else if (cmd.equals("quit")) {
        connected = false;
        continue;
      }
      // The user wants to clear the current buffer
      else if (cmd.equals("reset")) {
        buffer = new StringBuffer();
        line = 1;
        continue;
      }
      // The user wants to abort a pending transaction
      else if (cmd.equals("rollback")) {
        try {
          connection.rollback();
          System.out.println("Rollback successful.");
        } catch (SQLException e) {
          System.out.println("An error occurred during rollback: "
              + e.getMessage());
        }
        buffer = new StringBuffer();
        line = 1;
      }
      // The user wants version info
      else if (cmd.startsWith("show")) {
        DatabaseMetaData meta;
        try {
          meta = connection.getMetaData();
          cmd = cmd.substring(5, cmd.length()).trim();
          if (cmd.equals("version")) {
            showVersion(meta);
          } else {
            System.out.println("show version"); // Bad arg
          }
        } catch (SQLException e) {
          System.out.println("Failed to load meta data: "
              + e.getMessage());
        }
        buffer = new StringBuffer();
        line = 1;
      }
      // The input that is not a keyword should appended be to the buffer
      else {
        buffer.append(" " + tmp);
        line++;
        continue;
      }
    }
    try {
      connection.close();
    } catch (SQLException e) {
      System.out.println("Error closing connection: " + e.getMessage());
    }
    System.out.println("Connection closed.");
  }
  static public void executeStatement(StringBuffer buff) throws SQLException {
    String sql = buff.toString();
    Statement statement = null;
    try {
      statement = connection.createStatement();
      if (statement.execute(sql)) { // true means the SQL was a SELECT
        processResults(statement.getResultSet());
      } else { // no result sets, see how many rows were affected
        int num;
        switch (num = statement.getUpdateCount()) {
        case 0:
          System.out.println("No rows affected.");
          break;
        case 1:
          System.out.println(num + " row affected.");
          break;
        default:
          System.out.println(num + " rows affected.");
        }
      }
    } catch (SQLException e) {
      throw e;
    } finally { // close out the statement
      if (statement != null) {
        try {
          statement.close();
        } catch (SQLException e) {
        }
      }
    }
  }
  static public String prompt(String prop) throws IOException {
    String tmp = "";
    while (tmp.length() < 1) {
      System.out.print(prop);
      tmp = input.readLine().trim();
    }
    return tmp;
  }
  static public void processResults(ResultSet results) throws SQLException {
    try {
      ResultSetMetaData meta = results.getMetaData();
      StringBuffer bar = new StringBuffer();
      StringBuffer buffer = new StringBuffer();
      int cols = meta.getColumnCount();
      int row_count = 0;
      int i, width = 0;
      // Prepare headers for each of the columns
      // The display should look like:
      //  --------------------------------------
      //  | Column One | Column Two |
      //  --------------------------------------
      //  | Row 1 Value | Row 1 Value |
      //  --------------------------------------
      // create the bar that is as long as the total of all columns
      for (i = 1; i <= cols; i++) {
        width += meta.getColumnDisplaySize(i);
      }
      width += 1 + cols;
      for (i = 0; i < width; i++) {
        bar.append("-");
      }
      bar.append("\n");
      buffer.append(bar.toString() + "|");
      // After the first bar goes the column labels
      for (i = 1; i <= cols; i++) {
        StringBuffer filler = new StringBuffer();
        String label = meta.getColumnLabel(i);
        int size = meta.getColumnDisplaySize(i);
        int x;
        // If the label is longer than the column is wide,
        // then we truncate the column label
        if (label.length() > size) {
          label = label.substring(0, size);
        }
        // If the label is shorter than the column, pad it with spaces
        if (label.length() < size) {
          int j;
          x = (size - label.length()) / 2;
          for (j = 0; j < x; j++) {
            filler.append(" ");
          }
          label = filler + label + filler;
          if (label.length() > size) {
            label = label.substring(0, size);
          } else {
            while (label.length() < size) {
              label += " ";
            }
          }
        }
        // Add the column header to the buffer
        buffer.append(label + "|");
      }
      // Add the lower bar
      buffer.append("\n" + bar.toString());
      // Format each row in the result set and add it on
      while (results.next()) {
        row_count++;
        buffer.append("|");
        // Format each column of the row
        for (i = 1; i <= cols; i++) {
          StringBuffer filler = new StringBuffer();
          Object value = results.getObject(i);
          int size = meta.getColumnDisplaySize(i);
          String str;
          if (results.wasNull()) {
            str = "NULL";
          } else {
            str = value.toString();
          }
          if (str.length() > size) {
            str = str.substring(0, size);
          }
          if (str.length() < size) {
            int j, x;
            x = (size - str.length()) / 2;
            for (j = 0; j < x; j++) {
              filler.append(" ");
            }
            str = filler + str + filler;
            if (str.length() > size) {
              str = str.substring(0, size);
            } else {
              while (str.length() < size) {
                str += " ";
              }
            }
          }
          buffer.append(str + "|");
        }
        buffer.append("\n");
      }
      // Stick a row count up at the top
      if (row_count == 0) {
        buffer = new StringBuffer("No rows selected.\n");
      } else if (row_count == 1) {
        buffer = new StringBuffer("1 row selected.\n"
            + buffer.toString() + bar.toString());
      } else {
        buffer = new StringBuffer(row_count + " rows selected.\n"
            + buffer.toString() + bar.toString());
      }
      System.out.print(buffer.toString());
      System.out.flush();
    } catch (SQLException e) {
      throw e;
    } finally {
      try {
        results.close();
      } catch (SQLException e) {
      }
    }
  }
  static public void showVersion(DatabaseMetaData meta) {
    try {
      System.out.println("TerminalMonitor v2.0");
      System.out.println("DBMS: " + meta.getDatabaseProductName() + " "
          + meta.getDatabaseProductVersion());
      System.out.println("JDBC Driver: " + meta.getDriverName() + " "
          + meta.getDriverVersion());
    } catch (SQLException e) {
      System.out.println("Failed to get version info: " + e.getMessage());
    }
  }
}

Get all key words in database

 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
     DatabaseMetaData meta = conn.getMetaData();
     String sqlKeywords = meta.getSQLKeywords();
     System.out.println(sqlKeywords);
    st.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Get column names of a table using ResultSetMetaData

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/testdb";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT * FROM users");
    ResultSetMetaData metadata = resultSet.getMetaData();
    int columnCount = metadata.getColumnCount();
    ArrayList<String> columns = new ArrayList<String>();
    for (int i = 1; i < columnCount; i++) {
      String columnName = metadata.getColumnName(i);
      columns.add(columnName);
    }
    while (resultSet.next()) {
      for (String columnName : columns) {
        String value = resultSet.getString(columnName);
        System.out.println(columnName + " = " + value);
      }
    }
  }
}

Get column"s precision and scale value?

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/testdb";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    Statement statement = connection.createStatement();
    String query = "SELECT stock_id, name, price FROM stocks";
    ResultSet resultSet = statement.executeQuery(query);
    ResultSetMetaData metadata = resultSet.getMetaData();
    int precision = metadata.getPrecision(3);
    int scale = metadata.getScale(3);
    System.out.println("Precision: " + precision);
    System.out.println("Scale    : " + scale);
  }
}

Get database maximum table name length

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    int maxLength = metadata.getMaxTableNameLength();
    System.out.println("Max Table Name Length = " + maxLength);
    connection.close();
  }
}

Get database product information

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    int majorVersion = metadata.getDatabaseMajorVersion();
    System.out.println("majorVersion = " + majorVersion);
    int minorVersion = metadata.getDatabaseMinorVersion();
    System.out.println("minorVersion = " + minorVersion);
    String productName = metadata.getDatabaseProductName();
    System.out.println("productName = " + productName);
    String productVersion = metadata.getDatabaseProductVersion();
    System.out.println("productVersion = " + productVersion);
    connection.close();
  }
}

Get Database Schema From MetaData

 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    DatabaseMetaData meta = conn.getMetaData();
    ResultSet schemas = meta.getSchemas();
    while (schemas.next()) {
      String tableSchema = schemas.getString(1);    // "TABLE_SCHEM"
      String tableCatalog = schemas.getString(2); //"TABLE_CATALOG"
      System.out.println("tableSchema"+tableSchema);
    }
   
    st.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Get data types supported by database

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    ResultSet resultSet = null;
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    resultSet = metadata.getTypeInfo();
    while (resultSet.next()) {
      String typeName = resultSet.getString("TYPE_NAME");
      System.out.println("Type Name = " + typeName);
    }
    resultSet.close();
    connection.close();
  }
}

Get date time functions supported by database

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    String[] functions = metadata.getTimeDateFunctions().split(",\\s*");
    for (int i = 0; i < functions.length; i++) {
      String function = functions[i];
      System.out.println("Function = " + function);
    }
    connection.close();
  }
}

Get JDBC driver information

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main{
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    String driverName = metadata.getDriverName();
    String driverVersion = metadata.getDriverVersion();
    int majorVersion = metadata.getDriverMajorVersion();
    int minorVersion = metadata.getDriverMinorVersion();
    System.out.println("driverName = " + driverName);
    System.out.println("driverVersion = " + driverVersion);
    System.out.println("majorVersion = " + majorVersion);
    System.out.println("minorVersion = " + minorVersion);
    connection.close();
  }
}

Get numeric functions supported by database

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    String[] functions = metadata.getNumericFunctions().split(",\\s*");
    for (int i = 0; i < functions.length; i++) {
      String function = functions[i];
      System.out.println("Function = " + function);
    }
    connection.close();
  }
}

Get string functions supported by database?

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    String[] functions = metadata.getStringFunctions().split(",\\s*");
    for (int i = 0; i < functions.length; i++) {
      String function = functions[i];
      System.out.println("Function = " + function);
    }
    connection.close();
  }
}

Get system functions supported by database?

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    String[] functions = metadata.getSystemFunctions().split(",\\s*");
    for (int i = 0; i < functions.length; i++) {
      String function = functions[i];
      System.out.println("Function = " + function);
    }
  }
}

Get the max concurrent connection to a database?

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    int maxConnection = metadata.getMaxConnections();
    System.out.println("Maximum Connection = " + maxConnection);
    connection.close();
  }
}

Getting the Maximum Table Name Length allowed in a Database

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbmd = connection.getMetaData();
    // Get max table name length
    int length = dbmd.getMaxTableNameLength();
    System.out.println(length);
  }
}

If database support batch update

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    boolean isBatchingSupported = metadata.supportsBatchUpdates();
    System.out.println("Batching Supported = " + isBatchingSupported);
    connection.close();
  }
}

If database support scrollable result sets

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    boolean supportForwardOnly = metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY);
    System.out.println("supportForwardOnly = " + supportForwardOnly);
    boolean supportScrollInsensitive = metadata
        .supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
    System.out.println("supportScrollInsensitive = " + supportScrollInsensitive);
    boolean supportScrollSensitive = metadata
        .supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
    System.out.println("supportScrollSensitive = " + supportScrollSensitive);
    connection.close();
  }
}

If database support transaction

  
 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  private static final String DRIVER = "com.mysql.jdbc.Driver";
  private static final String URL = "jdbc:mysql://localhost/yourDatabase";
  private static final String USERNAME = "root";
  private static final String PASSWORD = "";
  public static void main(String[] args) throws Exception {
    Class.forName(DRIVER);
    Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
    DatabaseMetaData metadata = connection.getMetaData();
    boolean isSupportTransaction = metadata.supportsTransactions();
    System.out.println("Support Transaction = " + isSupportTransaction);
    connection.close();
  }
}

Is statement pooling supported?

 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class CheckStatementPooling {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    try {
      DatabaseMetaData dbmd = conn.getMetaData();
      if (dbmd == null) {
        System.out.println("No");
      }
      if (dbmd.supportsStatementPooling()) {
        System.out.println("statement pooling is supported");
      } else {
        System.out.println("No");
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      conn.close();
    }
  }
}

JDBC Performance

 
/*
MySQL and Java Developer"s Guide
Mark Matthews, Jim Cole, Joseph D. Gradecki
Publisher Wiley,
Published February 2003, 
ISBN 0471269239
*/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
public class Performance {
  Connection connection;
  public Performance() {
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
    } catch (Exception e) {
      System.err.println("unable to load driver");
    }
    try {
      connection = DriverManager
          .getConnection("jdbc:mysql://192.168.1.81/archives?user=archives&password=archives");
    } catch (SQLException e) {
      System.out.println("SQLException: " + e.getMessage());
      System.out.println("SQLState: " + e.getSQLState());
      System.out.println("VendorError:  " + e.getErrorCode());
    }
  }
  public void run() {
    long startTime;
    try {
      /*
       * PreparedStatement ps = connection.prepareStatement("INSERT INTO
       * product VALUES(null, "title", 5.54, "supplier", null, ?)");
       * startTime = new Date().getTime(); for (int i=0;i <1000;i++) {
       * ps.setInt(1, i); ps.executeUpdate(); } System.out.println("INSERT = " +
       * ((new Date().getTime()) - startTime));
       */
      Statement statement = connection.createStatement();
      startTime = new Date().getTime();
      for (int i = 0; i < 60; i++) {
        ResultSet rs = statement
            .executeQuery("SELECT pic_id, length, tlength, ts FROM them limit 100, "
                + (i * 1000));
        rs.close();
      }
      //      ResultSet rs = statement.executeQuery("SELECT pic_id, length,
      // tlength, ts FROM them");
      //      rs.close();
      statement.close();
      System.out.println("SELECT = "
          + ((new Date().getTime()) - startTime));
      /*
       * ps = connection.prepareStatement("UPDATE product SET inventory=10
       * WHERE inventory = ?"); startTime = new Date().getTime(); for (int
       * i=0;i <1000;i++) { ps.setInt(1, i); ps.executeUpdate(); }
       * System.out.println("UPDATE = " + ((new Date().getTime()) -
       * startTime));
       */
      connection.close();
    } catch (SQLException e) {
    }
  }
  public static void main(String[] args) {
    Performance test = new Performance();
    test.run();
  }
}

JDBC Version App

 
/*
Java Programming with Oracle JDBC
by Donald Bales 
ISBN: 059600088X
Publisher: O"Reilly
*/

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCVersionApp {
  public static void main(String args[]) throws ClassNotFoundException,
      SQLException {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@dssnt01:1521:dssora01", "scott", "tiger");
    DatabaseMetaData meta = conn.getMetaData();
    System.out.println("You"re using version: " + meta.getDriverVersion()
        + " of the Oracle JDBC driver.");
    conn.close();
  }
}

Listing All Non-SQL92 Keywords Used by a Database

  

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.util.Arrays;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbmd = connection.getMetaData();
    String[] keywords = dbmd.getSQLKeywords().split(",\\s*");
    Arrays.toString(keywords);
  }
}

Listing the Numeric Functions Supported by a Database

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.util.Arrays;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbmd = connection.getMetaData();
    // Get the list of numeric functions
    String[] numericFunctions = dbmd.getNumericFunctions().split(",\\s*");
    Arrays.toString(numericFunctions);
  }
}

Listing the String Functions Supported by a Database: retrieves a list of string functions that a database supports.

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.util.Arrays;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbmd = connection.getMetaData();
    // Get the list of string functions
    String[] stringFunctions = dbmd.getStringFunctions().split(",\\s*");
    Arrays.toString(stringFunctions);
  }
}

Listing the System Functions Supported by a Database

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.util.Arrays;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbmd = connection.getMetaData();
    // Get the list of system functions
    String[] systemFunctions = dbmd.getSystemFunctions().split(",\\s*");
    Arrays.toString(systemFunctions);
  }
}

Listing the Time and Date Functions Supported by a Database

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.util.Arrays;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dbmd = connection.getMetaData();
    // Get the list of time and date functions
    String timedateFunctions[] = dbmd.getTimeDateFunctions().split(",\\s*");
    Arrays.toString(timedateFunctions);
  }
}

Type info in database metadata

 
/*
Copyright 2003 Sun Microsystems, Inc. ALL RIGHTS RESERVED.
Use of this software is authorized pursuant to the terms of the license found at
http://developer.java.sun.ru/berkeley_license.html.
Copyright 2003 Sun Microsystems, Inc. All Rights Reserved.  
Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met: 
- Redistribution of source code must retain the above copyright notice, 
this list of conditions and the following disclaimer.
- Redistribution 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 Sun Microsystems, Inc. or the names of contributors may 
be used to endorse or promote products derived from this software without
specific prior written permission.
This software is provided "AS IS," without a warranty of any kind.  
ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING
ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR
NON-INFRINGEMENT, ARE HEREBY EXCLUDED. SUN MICORSYSTEMS, INC. ("SUN")
AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE
AS A RESULT OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS
DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE FOR ANY LOST
REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, 
INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN
IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
You acknowledge that this software is not designed, licensed or intended for
use in the design, construction, operation or maintenance of any nuclear
facility.

*/

/*
 * Copyright 2003 Sun Microsystems, Inc.  ALL RIGHTS RESERVED.
 * Use of this software is authorized pursuant to the terms of the license found at
 * http://developer.java.sun.ru/berkeley_license.html.
 */ 
import java.net.URL;
import java.sql.*;
     
public class TypeInfo {
  public static void main(String args[]) {
      
    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;
    DatabaseMetaData dbmd;      
  
  
    try {
      Class.forName("myDriver.ClassName");
    } catch(java.lang.ClassNotFoundException e) {
      System.err.print("ClassNotFoundException: "); 
      System.err.println(e.getMessage());
    }
    try {
      con = DriverManager.getConnection(url, 
                 "myLogin", "myPassword");
      dbmd = con.getMetaData();      
      ResultSet rs = dbmd.getTypeInfo();
      while (rs.next()) {
        String typeName = rs.getString("TYPE_NAME");
        short dataType = rs.getShort("DATA_TYPE");
        String createParams = rs.getString("CREATE_PARAMS");
        int nullable = rs.getInt("NULLABLE");
        boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
        System.out.println("DBMS type " + typeName + ":");
        System.out.println("     java.sql.Types:  "  + dataType);
        System.out.print("     parameters used to create: ");
        System.out.println(createParams);
        System.out.println("     nullable?:  "  + nullable);
        System.out.print("     case sensitive?:  ");
        System.out.println(caseSensitive);
        System.out.println("");
      }
      con.close();

    } catch(SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());
    }
  }
}