Connecting to a database with Java (oracle)

This howto will show you how to connect to a database (Oracle, MySQL or PostgreSQL) with Java.

In order to connect to a database you will have to download the JDBC driver from the corresponding website and put it into the root directory of your project. If you are using Eclipse you can easiely add this jar file to your project and run the program within Eclipse. If you want to start the program from a comandline, then you have to tell java the classpath where it looks for the driver. For the Oracle thin driver you can do it as follows:

java -classpath .;ojdbc14.jar DbConnect [option]

In the followin program I use 2 switches as option:
clear: clears the table „testTable“
populate: fills the table „testTable“ with content

On some platforms the seperator for the classpathes differs. To get the correct seperator just run

java -help

and look for the switch -classpath and -cp. On windows the seperator should be „;“ and on MacOS X it should be „:“

The code

//import the needed libs
import java.sql.*;

/**
 * @author Christian Krause
 * @version 2006-09-14
 */

public class DbConnect
{
  DbConnect()
  {
    //
  }

  /**
    * @return SQL - the SQL statement to add a new record to the databse (String)
    */
  public String populate()
  {
    String sql ="INSERT INTO testTable VALUES ("1, 'Christian', 'Krause'")";
    return sql;
  }

  /**
    * @return sql - the SQL statement to clear table (String)
    */
  public String clear()
  {
    String sql ="TRUNCATE TABLE testTable";
    return sql;
  }

  /** print some usage help */
  public static void printHelp()
  {
    System.out.println("Usage: java -classpath .;ojdbc14.jar DbConnect [option]");
    System.out.println("Possible options:");
    System.out.println("populate: populates the table "testTable"");
    System.out.println("clear: cleares the table "testTable"");
  }

  public static void main(String[] args)
  {
    DbConnect db = new DbConnect();

    /*
     * try to load the database driver
     *
     * the parameter for the common DBMS
     * Oracle: "oracle.jdbc.driver.OracleDriver"
     * MySQL: "com.mysql.jdbc.Driver"
     * PostgreSQL: "org.postgresql.Driver"
     *
     * For other DBMS ask Google... 
     */
    try
    {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    }
    catch(ClassNotFoundException e)
    {
      System.out.println(e);
    }

    //try to connect to the database and execute some statements
    try
    {
      /*
       * create an new SQL connection to the database server
       * DriverManager.getConnection(String url, String user, String password)
       *
       * the url parameter for the common DBMS
       *
       * Oracle:      jdbc:oracle:thin:@host:1521:SID
       * MySQL:       jdbc:mysql://host:3306/theDatabase
       * PostgreSQL:  jdbc:postgresql:host:5432/theDatabase
       *
       * For other DBMS ask Google...  
       */
      Connection con = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:ORCL",
                        "theUser",
                        "thePassword");
      System.out.println("Connection established!");

      //create a new statement
      Statement stmt = con.createStatement();

      //if the comandline switch was "populate"
      if(args[0].compareTo("populate") == 0)
      {
        //execute the sql statement
        stmt.executeUpdate(db.populate());
        System.out.println("testTable populated!");
      }

      //if the comandline switch was "clear"
      if(args[0].compareTo("clear") == 0)
      {
        //execute the sql statement
        stmt.executeUpdate(db.clear());
        System.out.println("testTable cleared!");
      }
      else
      {
      	printHelp();
      }
    }
    catch(SQLException sE)
    {
      System.out.println(sE);
      System.out.println("ERROR");
    }
  }
}

You can download the code here: DbConnect.java