코드 java JDBC DATABASE PING

출처: https://chadstechnoworks.com/wptech/app/jdbc_database_connection_test_code.html

JDBC DATABASE PING

 

This is my java application which I use to verify JDBC connectivity to a database. I usually deploy this on the application server side. You can modify the connection parameters in accordance to the type of database you are connecting. In my case, I am connecting to Oracle database in my sample code below. And I have the option whether to use OCI driver or Thin driver. The SQL query reply will be a time stamp of a successful connection. What the code lacks is the error trapping; but that is up to you to enhance the code.

/*

 Filename: jdbcping.java

 By: Chad Dimatulac

 Description: This will display the table names that the user owns.

 Notes: Make sure that a CLASSPATH env variable is defined according

        to JDK version needed. See specific Oracle version JDBC readme.txt

        for details found at $ORACLE_HOME/jdbc.

          use ojdbc14.jar for JDK 1.4 (10g)

          use ojdbc5.jar for JDK 1.5  (11g)

          use ojdbc6.jar for JDK 1.6  (11g)

        Check if java compiler exists by typing at the prompt: javac

        Check if java interpreter exists and its version by typing at

           the prompt: java -version

 

        Compile this source code by: javac jdbcping.java

        Run the program: java jdbcping

 

        For Thin Driver - enter database as: <hostname>:<listener port>:<SID>

        For OCI Driver - enter database as: <TNSName>

        

        OCI requires:

           - Oracle Client installed on the system.

           - set env variables JAVA_HOME, ORACLE_HOME, CLASSPATH, LD_LIBRARY_PATH, PATH

               ORACLE_BASE=/ora/stg/sys/10gR2/app/oracle

               ORACLE_HOME=$ORACLE_BASE/product/10.2.0/client

               TNS_ADMIN=$ORACLE_HOME/network/admin

               NLS_LANG=AMERICAN_AMERICA.UTF8

               # For JDK 1.4 (Oracle 10g)

               CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/ojdbc14.jar:$ORACLE_HOME/jdbc/lib/nls_charset12.jar:.

               # For JDK 1.5 (Oracle 11g)

               CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/jlib/orai18n.jar:.

               # For JDK 1.6 (Oracle 11g)

               CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/ojdbc6.jar:$ORACLE_HOME/jlib/orai18n.jar:.

               # If you need JTA or JNDI then

               CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/jta.jar:$ORACLE_HOME/jlib/jndi.jar

               LD_LIBRARY_PATH=$ORACLE_HOME/jdbc/lib:$ORACLE_HOME/lib32:$LD_LIBRARY_PATH

               JAVA_HOME=/usr/java

               PATH=$ORACLE_HOME/bin:$JAVA_HOME/bin:$PATH

*/

 

//---- Import JDBC Packages

import java.sql.*;

import oracle.jdbc.driver.*;

import oracle.sql.*;

 

//---- Import io packages for command line and file i/o

import java.io.*;

 

class jdbcping {

  public static void main (String args []) throws SQLException, IOException

  {

  //---- Register The JDBC Driver

  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

 

  //---- Prompt the user for connect information

    System.out.println ("Please enter information to connect to the database");

    String user;

    String password;

    String database;

 

    user = readEntry ("user: ");

    int slash_index = user.indexOf ('/');

    if (slash_index != -1)

    {

      password = user.substring (slash_index + 1);

      user = user.substring (0, slash_index);

    }

    else

      password = readEntry ("password: ");

      //---- Thin driver db string

      //database = readEntry ("database (<hostname>:<listener port>:<SID> entry): ");

      //---- OCI driver db string

      database = readEntry ("database (TNS alias): ");

 

      System.out.print ("Connecting to the database...");

      System.out.flush ();

 

      //---- Establish a connection using thin driver

      //Connection conn =

      //   DriverManager.getConnection ("jdbc:oracle:thin:@" + database,

      //       user, password);

      //---- Establish a connection using oci driver

      Connection conn =

         DriverManager.getConnection ("jdbc:oracle:oci:@" + database,

             user, password);

 

      System.out.println ("connected.");

 

      //---- Do query of tables the user owned

      Statement sql_stmt = conn.createStatement();

      String q1 = "SELECT 'Ping... Reply received from database on '||to_char(sysdate,'DD-MON-YYYY HH24:MI') qreply FROM dual";

      ResultSet rset = sql_stmt.executeQuery(q1);

 

      //---- Process the multiple rows of query result

      String q_row = "";

      while (rset.next())

        {

         q_row = "\n"+rset.getString("QREPLY")+"\n";

         System.out.println (q_row);

        }

      rset.close();

      sql_stmt.close();

      conn.close();

  }

 

  //---- Utility function to read a line from standard input

  static String readEntry (String prompt)

  {

    try

    {

      StringBuffer buffer = new StringBuffer ();

      System.out.print (prompt);

      System.out.flush ();

      int c = System.in.read ();

      while (c != '\n' && c != -1)

      {

        buffer.append ((char)c);

        c = System.in.read ();

      }

      return buffer.toString ().trim ();

    }

    catch (IOException e)

    {

      return "";

    }

  }

 

}