import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
/*
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 {
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
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) {
}
}
}
}
</source>
Database MetaData: Database version
<source lang="java">
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;
}
}
</source>
Detect if a table exists
<source lang="java">
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");
}
}
}
</source>
Driver Property Info
<source lang="java">
/*
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());
}
}
}
</source>
Get all key words in database
<source lang="java">
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;
}
}
</source>
Get column names of a table using ResultSetMetaData
<source lang="java">
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);
}
}
}
}
</source>
Get column"s precision and scale value?
<source lang="java">
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);
}
}
</source>
Get database maximum table name length
<source lang="java">
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();
}
}
</source>
Get database product information
<source lang="java">
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();
}
}
</source>
Get Database Schema From MetaData
<source lang="java">
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;
}
}
</source>
Get data types supported by database
<source lang="java">
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();
}
}
</source>
Get date time functions supported by database
<source lang="java">
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();
}
}
</source>
Get JDBC driver information
<source lang="java">
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();
}
}
</source>
Get numeric functions supported by database
<source lang="java">
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();
}
}
</source>
Get string functions supported by database?
<source lang="java">
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();
}
}
</source>
Get system functions supported by database?
<source lang="java">
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);
}
}
}
</source>
Get the max concurrent connection to a database?
<source lang="java">
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();
}
}
</source>
Getting the Maximum Table Name Length allowed in a Database
<source lang="java">
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);
}
}
</source>
If database support batch update
<source lang="java">
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();
}
}
</source>
If database support scrollable result sets
<source lang="java">
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();
}
}
</source>
If database support transaction
<source lang="java">
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();
}
}
</source>
Is statement pooling supported?
<source lang="java">
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();
}
}
}
</source>
JDBC Performance
<source lang="java">
/*
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();
}
}
</source>
JDBC Version App
<source lang="java">
/*
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();
}
}
</source>
Listing All Non-SQL92 Keywords Used by a Database
<source lang="java">
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);
}
}
</source>
Listing the Numeric Functions Supported by a Database
<source lang="java">
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);
}
}
</source>
Listing the String Functions Supported by a Database: retrieves a list of string functions that a database supports.
<source lang="java">
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);
}
}
</source>
Listing the System Functions Supported by a Database
<source lang="java">
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);
}
}
</source>
Listing the Time and Date Functions Supported by a Database
<source lang="java">
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);
}
}
</source>
Type info in database metadata
<source lang="java">
/*
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());
}
}
}
</source>
