Java Tutorial/Database/DatabaseMetadata

Материал из Java эксперт
Перейти к: навигация, поиск

Содержание

Database Metadata

  1. Metadata is data about data.
  2. Database metadata is information about a database.
  3. Database metadata provides information about the structure of a database and its tables, views, and stored procedures.

JDBC provides four interfaces that deal with database metadata

  1. java.sql.DatabaseMetaData: about the database as a whole: table names, table indexes, database product name and version, and actions the database supports.
  2. java.sql.ResultSetMetaData: about the types and properties of the columns in a ResultSet object.
  3. java.sql.ParameterMetaData: about the types and properties of the parameters in a PreparedStatement object.
  4. javax.sql.RowSetMetaData: about the columns in a RowSet object.


DatabaseMetaData Methods for Database Information

  1. boolean allProceduresAreCallable();
  2. boolean allTablesAreSelectable();
  3. String getURL();
  4. String getUserName();
  5. boolean isReadOnly();
  6. boolean nullsAreSortedHigh();
  7. boolean nullsAreSortedLow();
  8. boolean nullsAreSortedAtStart();
  9. boolean nullsAreSortedAtEnd();
  10. String getDatabaseProductName();
  11. String getDatabaseProductVersion();
  12. String getDriverName();
  13. String getDriverVersion();
  14. int getDriverMajorVersion();
  15. int getDriverMinorVersion();
  16. boolean usesLocalFiles();
  17. boolean usesLocalFilePerTable();
  18. boolean supportsMixedCaseIdentifiers();
  19. boolean storesUpperCaseIdentifiers();
  20. boolean storesLowerCaseIdentifiers();
  21. boolean storesMixedCaseIdentifiers();
  22. boolean supportsMixedCaseQuotedIdentifiers();
  23. boolean storesUpperCaseQuotedIdentifiers();
  24. boolean storesLowerCaseQuotedIdentifiers();
  25. boolean storesMixedCaseQuotedIdentifiers();
  26. String getIdentifierQuoteString();
  27. String getSQLKeywords();
  28. String getNumericFunctions();
  29. String getStringFunctions();
  30. String getSystemFunctions();
  31. String getTimeDateFunctions();
  32. String getSearchStringEscape();
  33. String getExtraNameCharacters();


DatabaseMetaData Methods for Information on the Database"s Contents

  1. ResultSet getProcedures(String catalog, String schemaPattern,String procedureNamePattern);
  2. ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern);
  3. ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]);
  4. ResultSet getschemas();
  5. ResultSet getCatalogs();
  6. ResultSet getTableTypes();
  7. ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern);
  8. ResultSet getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern);
  9. ResultSet getTablePrivileges(String catalog, String schemaPattern, String tableNamePattern);
  10. ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable);
  11. ResultSet getVersionColumns(String catalog, String schema, String table);
  12. ResultSet getPrimaryKeys(String catalog, String schema, String table);
  13. ResultSet getImportedKeys(String catalog, String schema, String table);
  14. ResultSet getExportedKeys(String catalog, String schema, String table);
  15. ResultSet getCrossReference(String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema,String foreignTable);
  16. ResultSet getTypeInfo();
  17. ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate);


DatabaseMetaData Methods for Information on the Database"s Limitations

  1. int getMaxBinaryLiteralLength();
  2. int getMaxCharLiteralLength();
  3. int getMaxColumnNameLength();
  4. int getMaxColumnsInGroupBy();
  5. int getMaxColumnsInIndex();
  6. int getMaxColumnsInOrderBy();
  7. int getMaxColumnsInSelect();
  8. int getMaxColumnsInTable();
  9. int getMaxConnections();
  10. int getMaxCursorNameLength();
  11. int getMaxIndexLength();
  12. int getMaxSchemaNameLength();
  13. int getMaxProcedureNameLength();
  14. int getMaxCatalogNameLength();
  15. int getMaxRowSize();
  16. boolean doesMaxRowSizeIncludeBlobs();
  17. int getMaxStatementLength();
  18. int getMaxStatements();
  19. int getMaxTableNameLength();
  20. int getMaxTablesInSelect();
  21. int getMaxUserNameLength();
  22. int getDefaultTransactionIsolation();
  23. boolean supportsTransactions();
  24. boolean supportsTransactionIsolationLevel(int level);
  25. boolean supportsDataDefinitionAndDataManipulationTransactions();
  26. boolean supportsDataManipulationTransactionsOnly();
  27. boolean dataDefinitionCausesTransactionCommit();
  28. boolean dataDefinitionIgnoredInTransactions();


DatabaseMetaData Methods for Information on the Database"s Supported Features

  1. boolean supportsAlterTableWithAddColumn();
  2. boolean supportsAlterTableWithDropColumn();
  3. boolean supportsColumnAliasing();
  4. boolean nullPlusNonNullIsNull();
  5. boolean supportsConvert();
  6. boolean supportsConvert(int fromType, int toType);
  7. boolean supportsTableCorrelationNames();
  8. boolean supportsDifferentTableCorrelationNames();
  9. boolean supportsExpressionsInOrderBy();
  10. boolean supportsOrderByUnrelated();
  11. boolean supportsGroupBy();
  12. boolean supportsGroupByUnrelated();
  13. boolean supportsGroupByBeyondSelect();
  14. boolean supportsLikeEscapeClause();
  15. boolean supportsMultipleResultSets();
  16. boolean supportsMultipleTransactions();
  17. boolean supportsNonNullableColumns();
  18. boolean supportsMinimumSQLGrammar();
  19. boolean supportsCoreSQLGrammar();
  20. boolean supportsExtendedSQLGrammar();
  21. boolean supportsANSI92EntryLevelSQL();
  22. boolean supportsANSI92IntermediateSQL();
  23. boolean supportsANSI92FullSQL();
  24. boolean supportsIntegrityEnhancementFacility();
  25. boolean supportsOuterJoins();
  26. boolean supportsFullOuterJoins();
  27. boolean supportsLimitedOuterJoins();
  28. String getSchemaTerm();
  29. String getProcedureTerm();
  30. String getCatalogTerm();
  31. boolean isCatalogAtStart();
  32. String getCatalogSeparator();
  33. boolean supportsSchemasInDataManipulation();
  34. boolean supportsSchemasInProcedureCalls();
  35. boolean supportsSchemasInTableDefinitions();
  36. boolean supportsSchemasInIndexDefinitions();
  37. boolean supportsSchemaInPrivilegeDefinitions();
  38. boolean supportsCatalogsInDataManipulation();
  39. boolean supportsCatalogsInProcedureCalls();
  40. boolean supportsCatalogsInTableDefinitions();
  41. boolean supportsCatalogsInIndexDefinitions();
  42. boolean supportsCatalogsInPrivilegeDefinitions();
  43. boolean supportsPositionedDelete();
  44. boolean supportsPositionedUpdate();
  45. boolean supportsSelectForUpdate();
  46. boolean supportsStoredProcedures();
  47. boolean supportsSubqueriesInComparisons();
  48. boolean supportsSubqueriesInExists();
  49. boolean supportsSubqueriesInIns();
  50. boolean supportsSubqueriesInQuantifieds();
  51. boolean supportsCorrelatedSubqueries();
  52. boolean supportsUnion();
  53. boolean supportsUnionAll();
  54. boolean supportsOpenCursorAcrossCommit();
  55. boolean supportsOpenCursorAcrossRollback();
  56. boolean supportsOpenStatementAcrossCommit();
  57. boolean supportsOpenStatementAcrossRollback();


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"); 
    }
  }
}





Display Database contents for current connection

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    DatabaseMetaData mtdt = conn.getMetaData();
    // list catalogs managed by this dbms
    System.out.println(mtdt.getCatalogTerm());
    ResultSet rs = mtdt.getCatalogs();
    ResultSetMetaData rsmd = rs.getMetaData();
    int numCols = rsmd.getColumnCount();
    for (int i = 1; i <= numCols; i++) {
      if (i > 1)
        System.out.print(", ");
      System.out.print(rsmd.getColumnLabel(i));
    }
    System.out.println("");
    while (rs.next()) {
      for (int i = 1; i <= numCols; i++) {
        if (i > 1)
          System.out.print(", ");
        System.out.print(rs.getString(i));
      }
      System.out.println("");
    }
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}





Display Database Limitation for your connection

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    DatabaseMetaData mtdt = conn.getMetaData();
    System.out.println("ANSI92 Entry Level: " + mtdt.supportsANSI92EntryLevelSQL());
    System.out.println("ANSI92 Intermediate: " + mtdt.supportsANSI92IntermediateSQL());
    System.out.println("ANSI92 Full SQL: " + mtdt.supportsANSI92FullSQL());
    System.out.println("Minimum SQL Grammar: " + mtdt.supportsMinimumSQLGrammar());
    System.out.println("Core SQL Grammar: " + mtdt.supportsCoreSQLGrammar());
    System.out.println("Extended SQL Grammar: " + mtdt.supportsExtendedSQLGrammar());
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}



ANSI92 Entry Level: false
ANSI92 Intermediate: false
ANSI92 Full SQL: false
Minimum SQL Grammar: false
Core SQL Grammar: true
Extended SQL Grammar: false


Get Database information from DatabaseMetaData

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    DatabaseMetaData mtdt = conn.getMetaData();
    System.out.println("URL in use: " + mtdt.getURL());
    System.out.println("User name: " + mtdt.getUserName());
    System.out.println("DBMS name: " + mtdt.getDatabaseProductName());
    System.out.println("DBMS version: " + mtdt.getDatabaseProductVersion());
    System.out.println("Driver name: " + mtdt.getDriverName());
    System.out.println("Driver version: " + mtdt.getDriverVersion());
    System.out.println("supp. SQL Keywords: " + mtdt.getSQLKeywords());
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}



URL in use: jdbc:hsqldb:mem:data/tutorial
User name: SA
DBMS name: HSQL Database Engine
DBMS version: 1.8.0
Driver name: HSQL Database Engine Driver
Driver version: 1.8.0
supp. SQL Keywords: BEFORE,BIGINT,BINARY,CACHED,DATETIME,LIMIT,LONGVARBINARY,LONGVARCHAR,OBJECT,OTHER,SAVEPOINT,TEMP,TEXT,TOP,TRIGGER,TINYINT,VARBINARY,VARCHAR_IGNORECASE


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 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 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 procedure information from DatabaseMetaData

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    DatabaseMetaData mtdt = conn.getMetaData();
    
    System.out.println(mtdt.getProcedureTerm());
    
    ResultSet rs = mtdt.getProcedures(conn.getCatalog(),"%", "%");
    
    ResultSetMetaData rsmd = rs.getMetaData();
    int numCols = rsmd.getColumnCount();
    for (int i = 1; i <= numCols; i++) {
      if (i > 1)
        System.out.print(", ");
      System.out.print(rsmd.getColumnLabel(i));
    }
    System.out.println("");
    while (rs.next()) {
      for (int i = 1; i <= numCols; i++) {
        if (i > 1)
          System.out.print(", ");
        System.out.print(rs.getString(i));
      }
      System.out.println("");
    }
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}



PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS, NUM_RESULT_SETS, REMARKS, PROCEDURE_TYPE, ORIGIN, SPECIFIC_NAME
null, PUBLIC, ABS, 1, 0, 1, Returns the absolute value of the given double value., 2, ALIAS, org.hsqldb.Library.abs(double)
null, PUBLIC, ACOS, 1, 0, 1, Returns the arc cosine of an angle, in the range of 0.0 through <i>pi</i>. Special case: <ul><li>If the argument is NaN or its absolute value is greater than 1, then the result is NaN.</ul> <p> A result must be within 1 ulp of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.acos(double)
null, PUBLIC, ASCII, 1, 0, 1, Returns the Unicode code value of the leftmost character of s as an int. This is the same as the ASCII value if the string contains only ASCII characters., 2, ALIAS, org.hsqldb.Library.ascii(java.lang.String)
null, PUBLIC, ASIN, 1, 0, 1, Returns the arc sine of an angle, in the range of -<i>pi</i>/2 through <i>pi</i>/2. Special cases: <ul><li>If the argument is NaN or its absolute value is greater than 1, then the result is NaN. <li>If the argument is zero, then the result is a zero with the same sign as the argument.</ul> <p> A result must be within 1 ulp of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.asin(double)
null, PUBLIC, ATAN, 1, 0, 1, Returns the arc tangent of an angle, in the range of -<i>pi</i>/2 through <i>pi</i>/2. Special cases: <ul><li>If the argument is NaN, then the result is NaN. <li>If the argument is zero, then the result is a zero with the same sign as the argument.</ul> <p> A result must be within 1 ulp of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.atan(double)
null, PUBLIC, ATAN2, 2, 0, 1, Converts rectangular coordinates (x, y) to polar (r, <i>theta</i>). This method computes the phase <i>theta</i> by computing an arc tangent of y/x in the range of -<i>pi</i> to <i>pi</i>. Special cases: <ul><li>If either argument is NaN, then the result is NaN. <li>If the first argument is positive zero and the second argument is positive, or the first argument is positive and finite and the second argument is positive infinity, then the result is positive zero. <li>If the first argument is negative zero and the second argument is positive, or the first argument is negative and finite and the second argument is positive infinity, then the result is negative zero. <li>If the first argument is positive zero and the second argument is negative, or the first argument is positive and finite and the second argument is negative infinity, then the result is the double value closest to <i>pi</i>. <li>If the first argument is negative zero and the second argument is negative, or the first argument is negative and finite and the second argument is negative infinity, then the result is the double value closest to -<i>pi</i>. <li>If the first argument is positive and the second argument is positive zero or negative zero, or the first argument is positive infinity and the second argument is finite, then the result is the double value closest to <i>pi</i>/2. <li>If the first argument is negative and the second argument is positive zero or negative zero, or the first argument is negative infinity and the second argument is finite, then the result is the double value closest to -<i>pi</i>/2. <li>If both arguments are positive infinity, then the result is the double value closest to <i>pi</i>/4. <li>If the first argument is positive infinity and the second argument is negative infinity, then the result is the double value closest to 3*<i>pi</i>/4. <li>If the first argument is negative infinity and the second argument is positive infinity, then the result is the double value closest to -<i>pi</i>/4. <li>If both arguments are negative infinity, then the result is the double value closest to -3*<i>pi</i>/4.</ul> <p> A result must be within 2 ulps of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.atan2(double,double)
null, PUBLIC, BITAND, 2, 0, 1, Returns the bit-wise logical <em>and</em> of the given integer values., 2, ALIAS, org.hsqldb.Library.bitand(int,int)
null, PUBLIC, BITOR, 2, 0, 1, Returns the bit-wise logical <em>xor</em> of the given integer values., 2, ALIAS, org.hsqldb.Library.bitor(int,int)
null, PUBLIC, BITXOR, 2, 0, 1, null, 2, ALIAS, org.hsqldb.Library.bitxor(int,int)
null, PUBLIC, BIT_LENGTH, 1, 0, 1, Returns the number of bits in the given String. This includes trailing blanks., 2, ALIAS, org.hsqldb.Library.bitLength(java.lang.String)
null, PUBLIC, CEILING, 1, 0, 1, Returns the smallest (closest to negative infinity) double value that is not less than the argument and is equal to a mathematical integer. Special cases: <ul><li>If the argument value is already equal to a mathematical integer, then the result is the same as the argument. <li>If the argument is NaN or an infinity or positive zero or negative zero, then the result is the same as the argument. <li>If the argument value is less than zero but greater than -1.0, then the result is negative zero.</ul> Note that the value of Math.ceil(x) is exactly the value of -Math.floor(-x)., 2, ALIAS, java.lang.Math.ceil(double)
null, PUBLIC, CHAR, 1, 0, 1, Returns the character string corresponding to the given ASCII (or Unicode) value. Note: <p> In some SQL CLI implementations, a null</CODE> is returned if the range is outside 0..255. In HSQLDB, the corresponding Unicode character is returned unchecked., 2, ALIAS, org.hsqldb.Library.character(int)
null, PUBLIC, CHARACTER_LENGTH, 1, 0, 1, Returns the number of characters in the given String. This includes trailing blanks., 2, ALIAS, org.hsqldb.Library.length(java.lang.String)
null, PUBLIC, CHAR_LENGTH, 1, 0, 1, Returns the number of characters in the given String. This includes trailing blanks., 2, ALIAS, org.hsqldb.Library.length(java.lang.String)
null, PUBLIC, CONCAT, 2, 0, 1, Returns a String</CODE> object that is the result of an <em>SQL-style</em> concatenation of the given String</CODE> objects. <p> Note: by <em>SQL-style</em>, it is meant: <UL> <LI> if both String</CODE> objects are null</CODE>, return null</CODE> <LI> if only one string is null</CODE>, return the other <LI> if both String</CODE> objects are non-null, return as a String</CODE> object the character sequence obtained by listing, in left to right order, the characters of the first string followed by the characters of the second </UL>, 2, ALIAS, org.hsqldb.Library.concat(java.lang.String,java.lang.String)
null, PUBLIC, COS, 1, 0, 1, Returns the trigonometric cosine of an angle. Special cases: <ul><li>If the argument is NaN or an infinity, then the result is NaN.</ul> <p> A result must be within 1 ulp of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.cos(double)
null, PUBLIC, COT, 1, 0, 1, Returns the cotangent of the given double value expressed in radians., 2, ALIAS, org.hsqldb.Library.cot(double)
null, PUBLIC, CURDATE, 1, 0, 1, null, 2, ALIAS, org.hsqldb.Library.curdate(java.sql.Connection)
null, PUBLIC, CURTIME, 1, 0, 1, null, 2, ALIAS, org.hsqldb.Library.curtime(java.sql.Connection)
null, PUBLIC, DATABASE, 1, 0, 1, Returns the name of the database corresponding to this connection., 2, ALIAS, org.hsqldb.Library.database(java.sql.Connection)
null, PUBLIC, DATEDIFF, 3, 0, 1, Returns the number of date and time boundaries crossed between two specified datetime values., 2, ALIAS, org.hsqldb.Library.datediff(java.lang.String,java.sql.Timestamp,java.sql.Timestamp)
null, PUBLIC, DAY, 1, 0, 1, Returns the day of the month from the given date value, as an integer value in the range of 1-31., 2, ALIAS, org.hsqldb.Library.dayofmonth(java.sql.Date)
null, PUBLIC, DAYNAME, 1, 0, 1, Returns a character string containing the name of the day (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday ) for the day portion of the given java.sql.Date., 2, ALIAS, org.hsqldb.Library.dayname(java.sql.Date)
null, PUBLIC, DAYOFMONTH, 1, 0, 1, Returns the day of the month from the given date value, as an integer value in the range of 1-31., 2, ALIAS, org.hsqldb.Library.dayofmonth(java.sql.Date)
null, PUBLIC, DAYOFWEEK, 1, 0, 1, Returns the day of the week from the given date value, as an integer value in the range 1-7, where 1 represents Sunday., 2, ALIAS, org.hsqldb.Library.dayofweek(java.sql.Date)
null, PUBLIC, DAYOFYEAR, 1, 0, 1, Returns the day of the year from the given date value, as an integer value in the range 1-366., 2, ALIAS, org.hsqldb.Library.dayofyear(java.sql.Date)
null, PUBLIC, DEGREES, 1, 0, 1, Converts an angle measured in radians to an approximately equivalent angle measured in degrees. The conversion from radians to degrees is generally inexact; users should <i>not</i> expect cos(toRadians(90.0)) to exactly equal 0.0., 2, ALIAS, java.lang.Math.toDegrees(double)
null, PUBLIC, DIFFERENCE, 2, 0, 1, Returns a count of the characters that do not match when comparing the 4 digit numeric SOUNDEX character sequences for the given String objects. If either String object is null, zero is returned., 2, ALIAS, org.hsqldb.Library.difference(java.lang.String,java.lang.String)
null, PUBLIC, EXP, 1, 0, 1, Returns Euler"s number <i>e</i> raised to the power of a double value. Special cases: <ul><li>If the argument is NaN, the result is NaN. <li>If the argument is positive infinity, then the result is positive infinity. <li>If the argument is negative infinity, then the result is positive zero.</ul> <p> A result must be within 1 ulp of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.exp(double)
null, PUBLIC, FLOOR, 1, 0, 1, Returns the largest (closest to positive infinity) double value that is not greater than the argument and is equal to a mathematical integer. Special cases: <ul><li>If the argument value is already equal to a mathematical integer, then the result is the same as the argument. <li>If the argument is NaN or an infinity or positive zero or negative zero, then the result is the same as the argument.</ul>, 2, ALIAS, java.lang.Math.floor(double)
null, PUBLIC, HEXTORAW, 1, 0, 1, Converts a String of hexidecimal digit characters to a raw binary value, represented as a String.<p> The given String object must consist of a sequence of 4 digit hexidecimal character substrings.<p> If its length is not evenly divisible by 4, null is returned. If any any of its 4 character subsequences cannot be parsed as a 4 digit, base 16 value, then a NumberFormatException is thrown. This conversion has the effect of reducing the character count 4:1., 2, ALIAS, org.hsqldb.Library.hexToRaw(java.lang.String)
null, PUBLIC, HOUR, 1, 0, 1, Returns the hour from the given time value, as an integer value in the range of 0-23., 2, ALIAS, org.hsqldb.Library.hour(java.sql.Time)
null, PUBLIC, IDENTITY, 0, 0, 1, Retrieves the last auto-generated integer indentity value used by this connection. As of 1.7.1 this is a dummy function. The return value is supplied directly by Function.java, 2, ALIAS, org.hsqldb.Library.identity()
null, PUBLIC, INSERT, 4, 0, 1, Returns a character sequence which is the result of writing the first length number of characters from the second given String over the first string. The start position in the first string where the characters are overwritten is given by start.<p> Note: In order of precedence, boundry conditions are handled as follows:<p> <UL> <LI>if either supplied String is null, then the other is returned; the check starts with the first given String. <LI>if start is less than one, s1 is returned <LI>if length is less than or equal to zero, s1 is returned <LI>if the length of s2 is zero, s1 is returned <LI>if start is greater than the length of s1, s1 is returned <LI>if length is such that, taken together with start, the indicated interval extends beyond the end of s1, then the insertion is performed precisely as if upon a copy of s1 extended in length to just include the indicated interval </UL>, 2, ALIAS, org.hsqldb.Library.insert(java.lang.String,int,int,java.lang.String)
null, PUBLIC, LCASE, 1, 0, 1, Returns a copy of the given String, with all upper case characters converted to lower case. This uses the default Java String conversion., 2, ALIAS, org.hsqldb.Library.lcase(java.lang.String)
null, PUBLIC, LEFT, 2, 0, 1, Returns the leftmost count characters from the given String. <p> Note: boundry conditions are handled in the following order of precedence: <UL> <LI>if s is null, then null is returned <LI>if count is less than 1, then a zero-length String is returned <LI>if count is greater than the length of s, then a copy of s is returned </UL>, 2, ALIAS, org.hsqldb.Library.left(java.lang.String,int)
null, PUBLIC, LENGTH, 1, 0, 1, Returns the number of characters in the given String. This includes trailing blanks., 2, ALIAS, org.hsqldb.Library.length(java.lang.String)
null, PUBLIC, LOCATE, 3, 0, 1, Returns the starting position of the first occurrence of the given search String object within the given String object, s. The search for the first occurrence of search begins with the first character position in s, unless the optional argument, start, is specified (non-null). If start is specified, the search begins with the character position indicated by the value of start, where the first character position in s is indicated by the value 1. If search is not found within s, the value 0 is returned., 2, ALIAS, org.hsqldb.Library.locate(java.lang.String,java.lang.String,java.lang.Integer)
null, PUBLIC, LOG, 1, 0, 1, Returns the natural logarithm (base <i>e</i>) of a double value. Special cases: <ul><li>If the argument is NaN or less than zero, then the result is NaN. <li>If the argument is positive infinity, then the result is positive infinity. <li>If the argument is positive zero or negative zero, then the result is negative infinity.</ul> <p> A result must be within 1 ulp of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.log(double)
null, PUBLIC, LOG10, 1, 0, 1, Returns the base 10 logarithm of the given double value., 2, ALIAS, org.hsqldb.Library.log10(double)
null, PUBLIC, LOWER, 1, 0, 1, Returns a copy of the given String, with all upper case characters converted to lower case. This uses the default Java String conversion., 2, ALIAS, org.hsqldb.Library.lcase(java.lang.String)
null, PUBLIC, LTRIM, 1, 0, 1, Returns the characters of the given String, with the leading spaces removed. Characters such as TAB are not removed., 2, ALIAS, org.hsqldb.Library.ltrim(java.lang.String)
null, PUBLIC, MINUTE, 1, 0, 1, Returns the minute from the given time value, as integer value in the range of 0-59., 2, ALIAS, org.hsqldb.Library.minute(java.sql.Time)
null, PUBLIC, MOD, 2, 0, 1, Returns the remainder (modulus) of the first given integer divided by the second. <p>, 2, ALIAS, org.hsqldb.Library.mod(int,int)
null, PUBLIC, MONTH, 1, 0, 1, Returns the month from the given date value, as an integer value in the range of 1-12 or 0-11. <p> If the sql_month database property is set true, then the range is 1-12, else 0-11, 2, ALIAS, org.hsqldb.Library.month(java.sql.Date)
null, PUBLIC, MONTHNAME, 1, 0, 1, Returns a character string containing the name of month (January, February, March, April, May, June, July, August, September, October, November, December) for the month portion of the given date value., 2, ALIAS, org.hsqldb.Library.monthname(java.sql.Date)
null, PUBLIC, NOW, 1, 0, 1, null, 2, ALIAS, org.hsqldb.Library.now(java.sql.Connection)
null, PUBLIC, OCTET_LENGTH, 1, 0, 1, Returns the number of bytes in the given String. This includes trailing blanks., 2, ALIAS, org.hsqldb.Library.octetLength(java.lang.String)
null, PUBLIC, PI, 0, 0, 1, Returns the constant value, pi., 2, ALIAS, org.hsqldb.Library.pi()
null, PUBLIC, POWER, 2, 0, 1, Returns of value of the first argument raised to the power of the second argument. Special cases: <ul><li>If the second argument is positive or negative zero, then the result is 1.0. <li>If the second argument is 1.0, then the result is the same as the first argument. <li>If the second argument is NaN, then the result is NaN. <li>If the first argument is NaN and the second argument is nonzero, then the result is NaN. <li>If the absolute value of the first argument is greater than 1 and the second argument is positive infinity, or the absolute value of the first argument is less than 1 and the second argument is negative infinity, then the result is positive infinity. <li>If the absolute value of the first argument is greater than 1 and the second argument is negative infinity, or the absolute value of the first argument is less than 1 and the second argument is positive infinity, then the result is positive zero. <li>If the absolute value of the first argument equals 1 and the second argument is infinite, then the result is NaN. <li>If the first argument is positive zero and the second argument is greater than zero, or the first argument is positive infinity and the second argument is less than zero, then the result is positive zero. <li>If the first argument is positive zero and the second argument is less than zero, or the first argument is positive infinity and the second argument is greater than zero, then the result is positive infinity. <li>If the first argument is negative zero and the second argument is greater than zero but not a finite odd integer, or the first argument is negative infinity and the second argument is less than zero but not a finite odd integer, then the result is positive zero. <li>If the first argument is negative zero and the second argument is a positive finite odd integer, or the first argument is negative infinity and the second argument is a negative finite odd integer, then the result is negative zero. <li>If the first argument is negative zero and the second argument is less than zero but not a finite odd integer, or the first argument is negative infinity and the second argument is greater than zero but not a finite odd integer, then the result is positive infinity. <li>If the first argument is negative zero and the second argument is a negative finite odd integer, or the first argument is negative infinity and the second argument is a positive finite odd integer, then the result is negative infinity. <li>If the first argument is less than zero and the second argument is a finite even integer, then the result is equal to the result of raising the absolute value of the first argument to the power of the second argument. <li>If the first argument is less than zero and the second argument is a finite odd integer, then the result is equal to the negative of the result of raising the absolute value of the first argument to the power of the second argument. <li>If the first argument is finite and less than zero and the second argument is finite and not an integer, then the result is NaN. <li>If both arguments are integers, then the result is exactly equal to the mathematical result of raising the first argument to the power of the second argument if that result can in fact be represented exactly as a double value.</ul> <p>(In the foregoing descriptions, a floating-point value is considered to be an integer if and only if it is a fixed point of the method {@link #ceil ceil} or, equivalently, a fixed point of the method {@link #floor floor}. A value is a fixed point of a one-argument method if and only if the result of applying the method to the value is equal to the value.) <p> A result must be within 1 ulp of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.pow(double,double)
null, PUBLIC, QUARTER, 1, 0, 1, Returns the quarter of the year in the given date value, as an integer value in the range of 1-4., 2, ALIAS, org.hsqldb.Library.quarter(java.sql.Date)
null, PUBLIC, RADIANS, 1, 0, 1, Converts an angle measured in degrees to an approximately equivalent angle measured in radians. The conversion from degrees to radians is generally inexact., 2, ALIAS, java.lang.Math.toRadians(double)
null, PUBLIC, RAND, 0, 0, 1, Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0. Returned values are chosen pseudorandomly with (approximately) uniform distribution from that range. <p> When this method is first called, it creates a single new pseudorandom-number generator, exactly as if by the expression <blockquote><pre>new java.util.Random
</blockquote> This new pseudorandom-number generator is used thereafter for all calls to this method and is used nowhere else.

This method is properly synchronized to allow correct use by more than one thread. However, if many threads need to generate pseudorandom numbers at a great rate, it may reduce contention for each thread to have its own pseudorandom-number generator., 2, ALIAS, java.lang.Math.random()

null, PUBLIC, RAWTOHEX, 1, 0, 1, Converts a raw binary value, as represented by the given String, to the equivalent String of hexidecimal digit characters. <p> This conversion has the effect of expanding the character count 1:4., 2, ALIAS, org.hsqldb.Library.rawToHex(java.lang.String) null, PUBLIC, REPEAT, 2, 0, 1, Returns a String composed of the given String, repeated count times., 2, ALIAS, org.hsqldb.Library.repeat(java.lang.String,java.lang.Integer) null, PUBLIC, REPLACE, 3, 0, 1, Replaces all occurrences of replace in s with the String object: with, 2, ALIAS, org.hsqldb.Library.replace(java.lang.String,java.lang.String,java.lang.String)

null, PUBLIC, RIGHT, 2, 0, 1, Returns the rightmost count characters of the given String, s. Note: boundry conditions are handled in the following order of precedence:
  • if s is null</CODE>, null</CODE> is returned
  • if count is less than one, a zero-length String is returned
  • if count is greater than the length of s, a copy of s is returned </UL>, 2, ALIAS, org.hsqldb.Library.right(java.lang.String,int) null, PUBLIC, ROUND, 2, 0, 1, Returns the given double value, rounded to the given int places right of the decimal point. If the supplied rounding place value is negative, rounding is performed to the left of the decimal point, using its magnitude (absolute value)., 2, ALIAS, org.hsqldb.Library.round(double,int) null, PUBLIC, ROUNDMAGIC, 1, 0, 1, Retrieves a magically rounded double value produced from the given double value. This method provides special handling for numbers close to zero and performs rounding only for numbers within a specific range, returning precisely the given value if it does not lie in this range.

    Special handling includes: <p>

    • input in the interval -0.0000000000001..0.0000000000001 returns 0.0
    • input outside the interval -1000000000000..1000000000000 returns input unchanged
    • input is converted to String form
    • input with a String</CODE> form length greater than 16 returns input unchaged
    • String</CODE> form with last four characters of "...000x" where x != "." is converted to "...0000"
    • String</CODE> form with last four characters of "...9999" is converted to "...999999"
    • the java.lang.Double.doubleValue</CODE> of the String</CODE> form is returned </UL>, 2, ALIAS, org.hsqldb.Library.roundMagic(double) null, PUBLIC, RTRIM, 1, 0, 1, Returns the characters of the given String, with trailing spaces removed., 2, ALIAS, org.hsqldb.Library.rtrim(java.lang.String) null, PUBLIC, SECOND, 1, 0, 1, Returns the second of the given time value, as an integer value in the range of 0-59., 2, ALIAS, org.hsqldb.Library.second(java.sql.Time) null, PUBLIC, SIGN, 1, 0, 1, Returns an indicator of the sign of the given double value. If the value is less than zero, -1 is returned. If the value equals zero, 0 is returned. If the value is greater than zero, 1 is returned., 2, ALIAS, org.hsqldb.Library.sign(double) null, PUBLIC, SIN, 1, 0, 1, Returns the trigonometric sine of an angle. Special cases:
      • If the argument is NaN or an infinity, then the result is NaN.
      • If the argument is zero, then the result is a zero with the same sign as the argument.

      A result must be within 1 ulp of the correctly rounded result. Results must be semi-monotonic., 2, ALIAS, java.lang.Math.sin(double)

      null, PUBLIC, SOUNDEX, 1, 0, 1, Returns a four character code representing the sound of the given String. Non-ASCCI characters in the input String are ignored. <p> This method was rewritten for HSQLDB by fredt@users to comply with the description at .<p>, 2, BUILTIN ROUTINE, org.hsqldb.Library.soundex(java.lang.String) null, PUBLIC, org.hsqldb.Library.space, 1, 0, 1, Returns a String consisting of count spaces, or null if count is less than zero. <p>, 2, BUILTIN ROUTINE, org.hsqldb.Library.space(int) null, PUBLIC, org.hsqldb.Library.substring, 3, 0, 1, Returns the characters from the given String, staring at the indicated one-based start position and extending the (optional) indicated length. If length is not specified (is null), the remainder of s is implied., 2, BUILTIN ROUTINE, org.hsqldb.Library.substring(java.lang.String,int,java.lang.Integer) null, PUBLIC, org.hsqldb.Library.to_char, 2, 0, 1, null, 2, BUILTIN ROUTINE, org.hsqldb.Library.to_char(java.util.Date,java.lang.String) null, PUBLIC, org.hsqldb.Library.trim, 4, 0, 1, Retrieves a character sequence derived from s with the leading, trailing or both leading and trailing occurances of trimstr removed, 2, BUILTIN ROUTINE, org.hsqldb.Library.trim(java.lang.String,java.lang.String,boolean,boolean) null, PUBLIC, org.hsqldb.Library.truncate, 2, 0, 1, Returns the given double value, truncated to the given int places right of the decimal point. If the given place value is negative, the given double value is truncated to the left of the decimal point, using the magnitude (aboslute value) of the place value., 2, BUILTIN ROUTINE, org.hsqldb.Library.truncate(double,int) null, PUBLIC, org.hsqldb.Library.ucase, 1, 0, 1, Returns a copy of the given String, with all lower case characters converted to upper case using the default Java method., 2, BUILTIN ROUTINE, org.hsqldb.Library.ucase(java.lang.String) null, PUBLIC, org.hsqldb.Library.user, 1, 0, 1, Returns the user"s authorization name (the user"s name as known to this database)., 2, BUILTIN ROUTINE, org.hsqldb.Library.user(java.sql.Connection) null, PUBLIC, org.hsqldb.Library.week, 1, 0, 1, Returns the week of the year from the given date value, as an integer value in the range of 1-53., 2, BUILTIN ROUTINE, org.hsqldb.Library.week(java.sql.Date) null, PUBLIC, org.hsqldb.Library.year, 1, 0, 1, Returns the year from the given date value, as an integer value in the range of 1-9999., 2, BUILTIN ROUTINE, org.hsqldb.Library.year(java.sql.Date)</pre>


      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 Table and View Names from a 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 = getConnection();
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,myURL CHAR);");
    st.executeUpdate("create view surveyview as (select * from survey);");
    getTables(conn);
   
    st.close();
    conn.close();
  }
  public static void getTables(Connection conn) throws Exception {
    String TABLE_NAME = "TABLE_NAME";
    String TABLE_SCHEMA = "TABLE_SCHEM";
    String[] TABLE_AND_VIEW_TYPES = {"TABLE","VIEW"};
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet tables = dbmd.getTables(null, null, null, TABLE_AND_VIEW_TYPES );
    while (tables.next()) {
      System.out.println(tables.getString(TABLE_NAME));
          System.out.println(tables.getString(TABLE_SCHEMA));
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}





      Get table information from DatabaseMetaData

      import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    DatabaseMetaData mtdt = conn.getMetaData();
    ResultSet rs = mtdt.getTables(conn.getCatalog(), "%", "%", null);
    
    ResultSetMetaData rsmd = rs.getMetaData();
    int numCols = rsmd.getColumnCount();
    for (int i = 1; i <= numCols; i++) {
      if (i > 1)
        System.out.print(", ");
      System.out.print(rsmd.getColumnLabel(i));
    }
    System.out.println("");
    while (rs.next()) {
      for (int i = 1; i <= numCols; i++) {
        if (i > 1)
          System.out.print(", ");
        System.out.print(rs.getString(i));
      }
      System.out.println("");
    }
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}



      TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS, TYPE_CAT, TYPE_SCHEM, TYPE_NAME, SELF_REFERENCING_COL_NAME, REF_GENERATION, HSQLDB_TYPE, READ_ONLY
null, INFORMATION_SCHEMA, SYSTEM_ALIASES, SYSTEM TABLE, the aliases defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_ALLTYPEINFO, SYSTEM TABLE, a description of all non user-defined data types known to this database and the level of support for them in various capacities, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_AUTHORIZATIONS, SYSTEM TABLE, one row for each user and one row for each role , null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_BESTROWIDENTIFIER, SYSTEM TABLE, for each accessible table defined within this database, the optimal set of visible columns that uniquely identifies a row, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_CACHEINFO, SYSTEM TABLE, the current state of the system row caching mechanism, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_CATALOGS, SYSTEM TABLE, the accessible catalogs defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_CHECK_COLUMN_USAGE, SYSTEM TABLE, one row for each column identified by a <column reference> contained in the <search condition> of a check constraint, domain constraint, or assertion., null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_CHECK_CONSTRAINTS, SYSTEM TABLE, one row for each domain constraint, table check constraint, and assertion., null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_CHECK_ROUTINE_USAGE, SYSTEM TABLE, one row for each SQL-invoked routine identified as the subject routine of either a <routine invocation>, a <method reference>, a  <method invocation>, or a <static method invocation>  contained in an <assertion definition>, a <domain constraint>, or a ., null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_CHECK_TABLE_USAGE, SYSTEM TABLE, one row for each table identified by a  simply contained in a  contained in the <search condition> of a check constraint, domain constraint, or assertion., null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_CLASSPRIVILEGES, SYSTEM TABLE, the visible user level access permissions for each Java class providing implementation for each accessible callable procedure, SQL function, trigger body and UDT method defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_COLLATIONS, SYSTEM TABLE, one row for each character collation descriptor., null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_COLUMNPRIVILEGES, SYSTEM TABLE, the visible user level access permissions of each visible column of each accessible table defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_COLUMNS, SYSTEM TABLE, the visible columns of each accessible table defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_CROSSREFERENCE, SYSTEM TABLE, a description of how the accessible tables defined within this database import visible columns to enforce referential integrity, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_INDEXINFO, SYSTEM TABLE, information about the indicies of each accessible table defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_PRIMARYKEYS, SYSTEM TABLE, the visible columns of the primary key of each accessible table defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_PROCEDURECOLUMNS, SYSTEM TABLE, a description of the return type, parameters and result columns of each accessible callable procedure, SQL function, trigger body and UDT method defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_PROCEDURES, SYSTEM TABLE, the procedures, SQL functions, trigger body routines and UDT methods defined within the database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_PROPERTIES, SYSTEM TABLE, the static and dynamic system properties and operating parameters of this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS, SYSTEM TABLE, one row for each role granted directly to a grantee, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_SCHEMAS, SYSTEM TABLE, the accessible schemas defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_SCHEMATA, SYSTEM TABLE, one row for each schema., null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_SEQUENCES, SYSTEM TABLE, one row for each external sequence generator, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_SESSIONINFO, SYSTEM TABLE, information about the current database session, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_SESSIONS, SYSTEM TABLE, the visible sessions open in this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_SUPERTABLES, SYSTEM TABLE, the table hierarchies defined in this database. Only the immediate super type/sub type relationship is modeled, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_SUPERTYPES, SYSTEM TABLE, the user-defined type (UDT) hierarchies defined in this database. Only the immediate super type/sub type relationship is modeled, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_TABLEPRIVILEGES, SYSTEM TABLE, the visible user level access permissions for each accessible table defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_TABLES, SYSTEM TABLE, the accessible tables defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_TABLETYPES, SYSTEM TABLE, the types of tables that can be created/found within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_TABLE_CONSTRAINTS, SYSTEM TABLE, one row for each table constraint associated with a table, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_TEXTTABLES, SYSTEM TABLE, the data source descriptors of the accessible TEXT TABLE objects defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_TRIGGERCOLUMNS, SYSTEM TABLE, the column usage of visible triggers defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_TRIGGERS, SYSTEM TABLE, the visible triggers defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_TYPEINFO, SYSTEM TABLE, a description of predefined table column data types known to this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_UDTATTRIBUTES, SYSTEM TABLE, a description of the attributes of the user-defined types (UDTs) that are available in this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_UDTS, SYSTEM TABLE, the user-defined types that are available within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_USAGE_PRIVILEGES, SYSTEM TABLE, one row for each usage privilege descriptor., null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_USERS, SYSTEM TABLE, users defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_VERSIONCOLUMNS, SYSTEM TABLE, the visible columns of the accessible tables that are automatically updated when any value in a row is updated, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_VIEWS, SYSTEM TABLE, the view descriptors of the accessible views defined within this database, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_VIEW_COLUMN_USAGE, SYSTEM TABLE, one row for each column of a table that is explicitly or implicitly referenced in the <query expression> of the view being described., null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_VIEW_ROUTINE_USAGE, SYSTEM TABLE, one row for each SQL-invoked routine identified as the subject routine of either a <routine invocation>, a <method reference>, a <method invocation>, or a <static method invocation> contained in a <view definition>, null, null, null, null, null, MEMORY, true
null, INFORMATION_SCHEMA, SYSTEM_VIEW_TABLE_USAGE, SYSTEM TABLE, one row for each table identified by a  simply contained in a  that is contained in the <query expression> of a view, null, null, null, null, null, MEMORY, true


      Get Table Names from a 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 = getConnection();
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,myURL CHAR);");
   
    getTables(conn);
   
    st.close();
    conn.close();
  }
  public static void getTables(Connection conn) throws Exception {
    String TABLE_NAME = "TABLE_NAME";
    String TABLE_SCHEMA = "TABLE_SCHEM";
    String[] TABLE_TYPES = {"TABLE"};
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet tables = dbmd.getTables(null, null, null, TABLE_TYPES);
    while (tables.next()) {
      System.out.println(tables.getString(TABLE_NAME));
          System.out.println(tables.getString(TABLE_SCHEMA));
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}





      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);
  }
}





      Get View Names from a 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 = getConnection();
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,myURL CHAR);");
    st.executeUpdate("create view surveyview as (select * from survey);");
    getTables(conn);
   
    st.close();
    conn.close();
  }
  public static void getTables(Connection conn) throws Exception {
    String TABLE_NAME = "TABLE_NAME";
    String TABLE_SCHEMA = "TABLE_SCHEM";
    String[] VIEW_TYPES = {"VIEW"};
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet tables = dbmd.getTables(null, null, null, VIEW_TYPES);
    while (tables.next()) {
      System.out.println(tables.getString(TABLE_NAME));
          System.out.println(tables.getString(TABLE_SCHEMA));
    }
  }
  private static Connection getConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    String url = "jdbc:hsqldb:mem:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
}





      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();
  }
}





      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 Available SQL data Types Used by a Database

      import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
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();
    ResultSet resultSet = dbmd.getTypeInfo();
    while (resultSet.next()) {
      // Get the database-specific type name
      String typeName = resultSet.getString("TYPE_NAME");
      // Get the java.sql.Types type to which this database-specific type is mapped
      short dataType = resultSet.getShort("DATA_TYPE");
    }
  }
}





      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);
  }
}


Источник — «http://jexp.ru/index.php?title=Java_Tutorial/Database/DatabaseMetadata&oldid=4846»