JDBC Verbindungen, insbesondere die Connection URLS sind nicht immer einfach zu testen. Für Oracle habe ich ein kleines Beispiel zusammengestellt.
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.Console;
public class JDBCTester {
private static final String ORACLE_DRIVER ="oracle.jdbc.driver.OracleDriver";
private static String driverClassName;
private static String jdbcURL;
private static String username;
private static String password;
/**
Simple Test Program for JDBC Connections
EXIT CODES (for further processing in bash/console)
0 = Everything OK
1 = No Connection to Database
2 = No Statement from Connection
3 = No Result from Test Statement
-1 = Can not close Database Connection
-2 = No Database Driver
-3 = No Database URL
-4 = No Console Instance for User/Password Input
@author Alexander von Boguszewski
**/
public static void main(String[] args) {
System.out.println ( "\n------------------------------------------------------------------------" );
parseArgs ( args );
validateParams ();
System.out.println ( "\n------------------------------------------------------------------------" );
System.out.println ( "Driver Class ................. " + driverClassName );
System.out.println ( "JDBC URL ................. " + jdbcURL );
System.out.println ( "DB User ................. " + username );
System.out.println ( "------------------------------------------------------------------------\n\n" );
Connection connection = null;
try{
/* *****************************************
*** TEST Connection with given params ***
*****************************************/
try {
Class.forName(driverClassName);
System.out.println("Try connect to database...");
connection = DriverManager.getConnection(
jdbcURL, username,
password);
if (connection != null) {
System.out.println("got Connection to database");
} else {
System.err.println("ERROR: No connection to database!");
if (connection != null) {
try {
connection.close();
System.out.println("Close Connection to database");
connection = null;
} catch (SQLException e) {
connection = null;
System.err.println("ERROR can't close Connection to database normally");
e.printStackTrace();
System.exit ( -1 );
}
}
System.exit ( 1 );
}
} catch (SQLException e) {
printSQLException(e);
if (connection != null) {
try {
connection.close();
System.out.println("Close Connection to database");
connection = null;
} catch (SQLException e2) {
connection = null;
System.err.println("ERROR can't close Connection to database normally");
e2.printStackTrace();
System.exit ( -1 );
}
}
System.exit ( 1 );
} catch (ClassNotFoundException e) {
System.err.println("ERROR could not find the JDBC Driver, check classpath and drivername");
usage ();
System.exit ( -2 );
}
/* *****************************************
*** TEST perform select statement *******
*****************************************/
System.out.println("Try to perform select Statement...");
try{
switch(driverClassName)
{
case ORACLE_DRIVER :
String query = "SELECT 1 FROM DUAL";
Statement stmt = connection.createStatement();
if(stmt != null){
ResultSet rs = stmt.executeQuery(query);
if(rs != null && rs.next()){
int result = rs.getInt(1);
System.out.println("got Result "+result+" from Statement "+query);
} else {
System.err.println("ERROR: Get no ResultSet from Query " + query);
System.exit ( 3 );
}
} else {
System.err.println("ERROR: Can not create Statement form Connection");
System.exit ( 2 );
}
break;
default: System.out.println("NO SELECT Tests for " + driverClassName);
}
} catch (SQLException e) {
printSQLException(e);
if (connection != null) {
try {
connection.close();
System.out.println("Close Connection to database");
connection = null;
} catch (SQLException e2) {
connection = null;
System.err.println("ERROR can't close Connection to database normally");
e2.printStackTrace();
System.exit ( -1 );
}
}
System.exit ( 1 );
}
System.out.println("End Tests");
} finally{
if (connection != null) {
try {
connection.close();
System.out.println("Close Connection to database");
connection = null;
} catch (SQLException e) {
connection = null;
System.err.println("ERROR can't close Connection to database normally");
e.printStackTrace();
System.exit ( -1 );
}
}
System.exit ( 0 );
}
}
/**
* Reads argiments from commandline
**/
private static void parseArgs ( String[] args )
{
int i = 0;
while ( i < args.length )
{
if ( args[i].compareTo ( "-help" ) == 0 )
{
usage();
System.exit ( 0 );
} else if ( args[i].compareTo ( "-driver" ) == 0 )
{
if ( (i+1) >= args.length ) usage ();
driverClassName = args[i+1];
i += 2;
} else if ( args[i].compareTo ( "-url" ) == 0 )
{
if ( (i+1) >= args.length ) usage ();
jdbcURL = args[i+1];
i += 2;
} else if ( args[i].compareTo ( "-user" ) == 0 )
{
if ( (i+1) >= args.length ) usage ();
username = args[i+1];
i += 2;
} else if ( args[i].compareTo ( "-pw" ) == 0 )
{
if ( (i+1) >= args.length ) usage ();
password = args[i+1];
i += 2;
}
/**
else if ( args[i].compareTo ( "-port" ) == 0 )
{
if ( (i+1) >= args.length ) usage ();
String tmp = args[i+1];
try
{
port = Integer.parseInt ( tmp );
}
catch ( NumberFormatException nfe )
{
usage ();
}
i += 2;
}
**/
}
}
/**
* Set DEFAULTS to driverClassName and jdbcURL if no parameter given.
* Ask for Username and Password if no parameter given
**/
private static void validateParams (){
if(driverClassName==null){
driverClassName=ORACLE_DRIVER;
}
if(jdbcURL==null){
System.err.println("ERROR no JDBC URL given. Try something like \"jdbc:oracle:thin:@localhost:1521:XE\"");
System.exit(-3);
}
if(username==null){
do{
try{
Console console = System.console();
if (console != null) {
console.printf("Enter DB user name: ");
username = console.readLine();
} else {
System.err.println("Couldn't get Console instance, use console instead of eclipse. Otherwise set user as param");
System.exit(-4);
}
} catch (Exception e){
System.err.println ( "Cant read username, try again" );
}
} while (username==null);
}
if(password==null){
do{
try{
Console console = System.console();
if (console != null) {
char[] passwordChars = console.readPassword("Enter DB user password (hidden): ");
password = new String(passwordChars);
} else {
System.err.println("Couldn't get Console instance, use console instead of eclipse. Otherwise set password as param");
System.exit(-4);
}
} catch (Exception e){
System.err.println ( "Cant read password, try again" );
}
} while (password==null);
}
}
/**
* Prints Usage Information to comandline in case the programm is called with wrong parameters
**/
private static void usage ()
{
System.out.println ( "\nUsage: java -cp ojdbc6.jar;. JDBCTester [options]" );
System.out.println ( "\nwhere options are:" );
System.out.println ( "" );
System.out.println ( " -driver <driver class name> - default is oracle.jdbc.driver.OracleDriver" );
System.out.println ( " -url <jdbc url> - JDBC URL for the connection (escape this with \"...\"), example \"jdbc:oracle:thin:@localhost:1521:XE\"" );
System.out.println ( " -user <user name> - user name, if param not present you will be ask for this" );
System.out.println ( " -pw <password> - password, if param not present you will be ask for this" );
System.out.println ( " -help print this" );
}
/**
* Generic SQLException printout
**/
private static void printSQLException(SQLException ex) {
for (Throwable e : ex) {
if (e instanceof SQLException) {
System.out.println ( "\nSQL EXCEPTION" );
SQLException sqlEx = (SQLException) e;
System.err.println("SQLState: " +
sqlEx.getSQLState());
System.err.println("Error Code: " +
sqlEx.getErrorCode());
System.err.println("Message: " + sqlEx.getMessage());
Throwable t = ex.getCause();
while(t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}Der Oracle JDBC Treiber muss heruntergeladen werden (http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html) Die Tester Klasse und der Treiber kopieren wir in unseren Arbeitsordner. Anschließend kann der Tester kompiliert und ausgeführt werden:
javac JDBCTester.java
java -cp ojdbc6.jar;. JDBCTester
Um den Aufruf etwas zu vereinfachen kann noch ein Shell Script um den Java Aufruf gebaut werden.
vi test_DBXZY.sh
#!/bin/bash
URL="jdbc:oracle:thin:@localhost:1521:XE"
USER=“avonbo”
PW=“avonboPW”
java -cp ojdbc6.jar;. JDBCTester -url $URL -user $USER -pw $PW