com.cleancode.jdbc
Class DBprobe

java.lang.Object
  com.cleancode.jdbc.DBprobe

public class DBprobe
extends Object

A simple probe to explore data and metadata of different vendors' databases. With DBprobe, you specify a "path" to a database, i.e. a driver, a connection, a server, a db name, user name, and password. DBprobe then attempts to query the database for the highest level information, i.e. what tables does it contain, both to ascertain what database type it is (oracle, mysql, etc.) and to display this metadata to you. You may optionally specify a specific table in which case DBprobe will provide metadata about it, i.e. its columns and properties of each column. Alternatively, you may specify a specific query, if you wish to ascertain other information. Invoke the main program with no arguments for details on execution parameters.

DBprobe is a simple, command-line tool for database exploration. For more flexibility and a fancier interface, consider the JDBCTest tool available directly from Sun.

Another excellent learning resource is SQLzoo.net, which allows you to try out a variety of basic SQL statements in 6 different database systems at the click of a button (see the Meta Data section).

Parameter Sets

Connection details for one or more databases may be stored in a configuration file (CONFIG_FILE) in the current directory, or may be supplied on the command line invocation. You may store multiple sets using the InputOptions parameter engine, allowing you to select at runtime the set of parameters you wish to use via the MODE property. For example, here we specify two sets of parameters, one for the database my_reports on server ips1, and one for database my_data on machine integration:

   SERVER:ips1 = ips_server1
   DBNAME:ips1 = my_reports
   USERID:ips1 = myuid1
   PASSWD:ips1 = mypass1
   TABLE:ips1  = COUNTRIES
   SERVER:integ = integration
   DBNAME:integ = my_data
   USERID:integ = myuid2
   PASSWD:integ = mypass2
   TABLE:integ  = STUFF
 

At runtime, we simply add MODE=ips1 or MODE=integ to select one or the other parameter sets.

Available Parameters

• DB_DRIVER (default = DEFAULT_DB_DRIVER) -- the Java object class that defines the database driver.
• DB_CONN (default = DEFAULT_DB_CONN -- the database connection.
• SERVER -- the name of the database server machine (should include the port, if any)
• DBNAME -- the name of the database to query
• USERID -- the user id to access the database
• PASSWD -- password corresponding to USERID
• TABLE -- name of table to query: specifying the pseudo-table DB_TABLE describes the database itself; any other table name describes that table and dumps its contents, in addition to describing the database itself.
• QUERY (optional) -- SQL statement to execute: if supplied, TABLE must be the name of an actual table, not the pseudo-table DB_TABLE. If QUERY is not supplied, all data in the table is dumped via the query SELECT * FROM TABLE.

Database Specifications

A second configuration file (DB_SPEC_FILE) specifies the known database systems to be used to try to identify any given database connection. This is an XML file with a dbspecs root, and a collection of server children. Each server has two sub-elements, tableQuery, used to return a list of tables in a database; and columnQuery, used to return a list of columns in a table. The tableQuery is used to identify the type of database. Each one is attempted to be executed; if it returns results and does not cause an exception, then it has successfully identified a database. Both the tableQuery and the columnQuery are then used to collect and display meta-data about the database and optionally, the selected table. Here is an excerpt from the default configuration file:

 <dbspecs>
   <server name="mysql">
     <tableQuery>show tables</tableQuery>
     <columnQuery>show columns from -TABLENAME-</columnQuery>
   </server>
   <server name="oracle">
     <tableQuery>SELECT * FROM cat</tableQuery>
     <columnQuery>SELECT * FROM all_tab_columns
       WHERE table_name='-TABLENAME-'</columnQuery>
   </server>
 </dbspecs>
 

Since:

CleanCode 0.9.02

Version:

$Revision: 136 $

Author:

Michael Sorens

Field Summary

static String	CONFIG_FILE Configuration file name (in current directory) detailing runtime parameters and specifics of database instances.
static String	DB_SPEC_FILE Configuration file detailing available database systems.
static String	DB_TABLE Pseudo-table name indicating to describe only the database itself.
static String	DEFAULT_DB_CONN Default database connection.
static String	DEFAULT_DB_DRIVER Default database driver.
static String	MARKER Marker to use in database specs file as a placeholder for table name.

Constructor Summary

DBprobe(InputOptions inputOptions, String dbSpecFile)
Create a standard DBprobe object.

Method Summary

void	complete() Closes the database connection.
void	dumpMetaTable(String title, ResultSet rs) Describes the results of a database meta-query, that is, a query that returns either a list of tables or a list of fields for a specific table.
void	dumpTable(ResultSet rs) Displays a result set.
Connection	getConnection() Retrieves the database Connection object.
String	getFieldQuery(String tableName) Generates a query to return a list of fields for a given table.
String	getServerName() Retrieves the database server name.
Statement	getStatement() Retrieves the database Statement object.
boolean	identifyDB() Attempts to identify the database (access, mysql, oracle, etc) by running a basic query to list the database tables for each known database type.
boolean	init() Initializes the DB resources, the DB connection, and setting up the DB statement.
String	listDBChoices() Lists the predefined databases known to the system.
static void	main(String[] args) Main program for standalone operation.
static void	usage(DBprobe myDB) Displays the program usage.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

CONFIG_FILE

public static final String CONFIG_FILE

Configuration file name (in current directory) detailing runtime parameters and specifics of database instances.

See Also:

Constant Field Values

DB_SPEC_FILE

public static final String DB_SPEC_FILE

Configuration file detailing available database systems.

See Also:

Constant Field Values

DEFAULT_DB_DRIVER

public static final String DEFAULT_DB_DRIVER

Default database driver.

See Also:

Constant Field Values

DEFAULT_DB_CONN

public static final String DEFAULT_DB_CONN

Default database connection.

See Also:

Constant Field Values

DB_TABLE

public static final String DB_TABLE

Pseudo-table name indicating to describe only the database itself.

See Also:

Constant Field Values

MARKER

public static final String MARKER

Marker to use in database specs file as a placeholder for table name.

See Also:

Constant Field Values

Constructor Detail

DBprobe

public DBprobe(InputOptions inputOptions,
               String dbSpecFile)

Create a standard DBprobe object.

Parameters:

inputOptions - user parameter object

dbSpecFile - name of file containing DB specs

Method Detail

getConnection

public Connection getConnection()

Retrieves the database Connection object.

Returns:

database Connection object

getStatement

public Statement getStatement()

Retrieves the database Statement object.

Returns:

database Statement object

getServerName

public String getServerName()

Retrieves the database server name.

Returns:

name of the server.

init

public boolean init()

Initializes the DB resources, the DB connection, and setting up the DB statement.

Returns:

true if successfully initialized

complete

public void complete()

Closes the database connection.

listDBChoices

public String listDBChoices()

Lists the predefined databases known to the system.

Returns:

string containing list of known database choices

identifyDB

public boolean identifyDB()

Attempts to identify the database (access, mysql, oracle, etc) by running a basic query to list the database tables for each known database type. As each database type (as the system is currently defined) has a unique query for returning the list of tables, the one that does not fail should correctly identify the database. Once this method is run, other methods will then retrieve type-appropriate queries for the identified database. If no database can be identified, the system defaults to the first one from the dbspecs configuration file.

Returns:

boolean if database successfully identified

getFieldQuery

public String getFieldQuery(String tableName)

Generates a query to return a list of fields for a given table.

Parameters:

tableName - table for which to construct a query

Returns:

query string that will return a list of fields for the specified table

dumpMetaTable

public void dumpMetaTable(String title,
                          ResultSet rs)

Describes the results of a database meta-query, that is, a query that returns either a list of tables or a list of fields for a specific table. The results describe either the table of tables or the single table including name, number of columns, and information about each column.

Parameters:

title - display table

rs - resultSet from an appropriate query

dumpTable

public void dumpTable(ResultSet rs)

Displays a result set.

Parameters:

rs - result set to dump.

usage

public static void usage(DBprobe myDB)

Displays the program usage.

Parameters:

myDB - DBprobe object that can be queried for known databases to include in the description.

main

public static void main(String[] args)

Main program for standalone operation.

Parameters:

args - command-line arguments