Firebird driver example using jaybird-full jar

DriverExample.java

// Original version of this file was part of InterClient 2.01 examples
//
// Copyright InterBase Software Corporation, 1998.
// Written by com.inprise.interbase.interclient.r&d.PaulOstler :-)
//
// Code was modified by Roman Rokytskyy to show that Firebird JCA-JDBC driver
// does not introduce additional complexity in normal driver usage scenario.
//
// A small application to demonstrate basic, but not necessarily simple, JDBC features.
//
// Note: you will need to hardwire the path to your copy of employee.gdb
//       as well as supply a user/password in the code below at the
//       beginning of method main().

public class DriverExample
{
  // Make a connection to an employee.gdb on your local machine,
  // and demonstrate basic JDBC features.
  // Notice that main() uses its own local variables rather than
  // static class variables, so it need not be synchronized.
  public static void main (String args[]) throws Exception
  {
    // Modify the following hardwired settings for your environment.
    // Note: localhost is a TCP/IP keyword which resolves to your local machine's IP address.
    //       If localhost is not recognized, try using your local machine's name or
    //       the loopback IP address 127.0.0.1 in place of localhost.
//    String databaseURL = "jdbc:firebirdsql:localhost/3050:c:/database/employee.gdb";
    //String databaseURL = "jdbc:firebirdsql:native:localhost/3050:c:/database/employee.gdb";
    //String databaseURL = "jdbc:firebirdsql:local:c:/database/employee.gdb";
    //String databaseURL = "jdbc:firebirdsql:embedded:c:/database/employee.fdb?lc_ctype=WIN1251";
    String databaseURL = "jdbc:firebirdsql:localhost/3050:/var/lib/firebird/2.5/data/employee.fdb?sql_dialect=3";
    String user = "sysdba";
    String password = "masterkey";
    String driverName = "org.firebirdsql.jdbc.FBDriver";

    // As an exercise to the reader, add some code which extracts databaseURL,
    // user, and password from the program args[] to main().
    // As a further exercise, allow the driver name to be passed as well,
    // and modify the code below to use driverName rather than the hardwired
    // string "org.firebirdsql.jdbc.FBDriver" so that this code becomes
    // driver independent.  However, the code will still rely on the
    // predefined table structure of employee.gdb.

    // Here are the JDBC objects we're going to work with.
    // We're defining them outside the scope of the try block because
    // they need to be visible in a finally clause which will be used
    // to close everything when we are done.
    // The finally clause will be executed even if an exception occurs.
    java.sql.Driver d = null;
    java.sql.Connection c = null;
    java.sql.Statement s = null;
    java.sql.ResultSet rs = null;

    // Any return from this try block will first execute the finally clause
    // towards the bottom of this file.
    try {

      // Let's try to register the Firebird JCA-JDBC driver with the driver manager
      // using one of various registration alternatives...
      int registrationAlternative = 1;
      switch (registrationAlternative) {

      case 1:
        // This is the standard alternative and simply loads the driver class.
        // Class.forName() instructs the java class loader to load
        // and initialize a class.  As part of the class initialization
        // any static clauses associated with the class are executed.
        // Every driver class is required by the jdbc specification to automatically
        // create an instance of itself and register that instance with the driver
        // manager when the driver class is loaded by the java class loader
        // (this is done via a static clause associated with the driver class).
        //
        // Notice that the driver name could have been supplied dynamically,
        // so that an application is not hardwired to any particular driver
        // as would be the case if a driver constructor were used, eg.
        // new org.firebirdsql.jdbc.FBDriver().
        try {
          Class.forName ("org.firebirdsql.jdbc.FBDriver");
        }
        catch (java.lang.ClassNotFoundException e) {
          // A call to Class.forName() forces us to consider this exception :-)...
          System.out.println ("Firebird JCA-JDBC driver not found in class path");
          System.out.println (e.getMessage ());
          return;
        }
        break;

      case 2:
        // There is a bug in some JDK 1.1 implementations, eg. with Microsoft
        // Internet Explorer, such that the implicit driver instance created during
        // class initialization does not get registered when the driver is loaded
        // with Class.forName().
        // See the FAQ at http://java.sun.com/jdbc for more info on this problem.
        // Notice that in the following workaround for this bug, that if the bug
        // is not present, then two instances of the driver will be registered
        // with the driver manager, the implicit instance created by the driver
        // class's static clause and the one created explicitly with newInstance().
        // This alternative should not be used except to workaround a JDK 1.1
        // implementation bug.
        try {
          java.sql.DriverManager.registerDriver (
             (java.sql.Driver) Class.forName ("org.firebirdsql.jdbc.FBDriver").newInstance ()
          );
        }
        catch (java.lang.ClassNotFoundException e) {
          // A call to Class.forName() forces us to consider this exception :-)...
          System.out.println ("Driver not found in class path");
          System.out.println (e.getMessage ());
          return;
        }
        catch (java.lang.IllegalAccessException e) {
          // A call to newInstance() forces us to consider this exception :-)...
          System.out.println ("Unable to access driver constructor, this shouldn't happen!");
          System.out.println (e.getMessage ());
          return;
        }
        catch (java.lang.InstantiationException e) {
          // A call to newInstance() forces us to consider this exception :-)...
          // Attempt to instantiate an interface or abstract class.
          System.out.println ("Unable to create an instance of driver class, this shouldn't happen!");
          System.out.println (e.getMessage ());
          return;
        }
        catch (java.sql.SQLException e) {
          // A call to registerDriver() forces us to consider this exception :-)...
          System.out.println ("Driver manager failed to register driver");
          showSQLException (e);
          return;
        }
        break;

      case 3:
        // Add the Firebird JCA-JDBC driver name to your system's jdbc.drivers property list.
        // The driver manager will load drivers from this system property list.
        // System.getProperties() may not be allowed for applets in some browsers.
        // For applets, use one of the Class.forName() alternatives above.
        java.util.Properties sysProps = System.getProperties ();
        StringBuffer drivers = new StringBuffer ("org.firebirdsql.jdbc.FBDriver");
        String oldDrivers = sysProps.getProperty ("jdbc.drivers");
        if (oldDrivers != null)
          drivers.append (":" + oldDrivers);
        sysProps.put ("jdbc.drivers", drivers.toString ());
        System.setProperties (sysProps);
        break;

      case 4:
        // Advanced: This is a non-standard alternative, and is tied to
        // a particular driver implementation, but is very flexible.
        //
        // It may be possible to configure a driver explicitly, either thru
        // the use of non-standard driver constructors, or non-standard
        // driver "set" methods which somehow tailor the driver to behave
        // differently from the default driver instance.
        // Under this alternative, a driver instance is created explicitly
        // using a driver specific constructor.  The driver may then be
        // tailored differently from the default driver instance which is
        // created automatically when the driver class is loaded by the java class loader.
        // For example, perhaps a driver instance could be created which
        // is to behave like some older version of the driver.
        //
        // d = new org.firebirdsql.jdbc.FBDriver ();
        // DriverManager.registerDriver (d);
        // c = DriverManager.getConnection (...);
        //
        // Since two drivers, with differing behavior, are now registered with
        // the driver manager, they presumably must recognize different jdbc
        // subprotocols.  For example, the tailored driver may only recognize
        // "jdbc:interbase:old_version://...", whereas the default driver instance
        // would recognize the standard "jdbc:interbase://...".
        // There are currently no methods, such as the hypothetical setVersion(),
        // for tailoring an Firebird JCA-JDBC driver so this 4th alternative is academic
        // and not necessary for Firebird JCA-JDBC driver.
        //
        // It is also possible to create a tailored driver instance which
        // is *not* registered with the driver manager as follows
        //
        // d = new org.firebirdsql.jdbc.FBDriver ();
        // c = d.connect (...);
        //
        // this is the most usual case as this does not require differing
        // jdbc subprotocols since the connection is obtained thru the driver
        // directly rather than thru the driver manager.
        d = new org.firebirdsql.jdbc.FBDriver ();
      }

      // At this point the driver should be registered with the driver manager.
      // Try to find the registered driver that recognizes interbase URLs...
      try {
        // We pass the entire database URL, but we could just pass "jdbc:interbase:"
        d = java.sql.DriverManager.getDriver (databaseURL);
        System.out.println ("Firebird JCA-JDBC driver version " +
                            d.getMajorVersion () +
                            "." +
                            d.getMinorVersion () +
                            " registered with driver manager.");
      }
      catch (java.sql.SQLException e) {
        System.out.println ("Unable to find Firebird JCA-JDBC driver among the registered drivers.");
        showSQLException (e);
        return;
      }

      // Advanced info: Class.forName() loads the java class for the driver.
      // All JDBC drivers are required to have a static clause that automatically
      // creates an instance of themselves and registers that instance
      // with the driver manager.  So there is no need to call
      // DriverManager.registerDriver() explicitly unless the driver allows
      // for tailored driver instances to be created (each instance recognizing
      // a different jdbc sub-protocol).

      // Now that Firebird JCA-JDBC driver is registered with the driver manager,
      // try to get a connection to an employee.gdb database on this local machine
      // using one of two alternatives for obtaining connections...
      int connectionAlternative = 1;
      switch (connectionAlternative) {

      case 1:
        // This alternative is driver independent;
        // the driver manager will find the right driver for you based on the jdbc subprotocol.
        // In the past, this alternative did not work with applets in some browsers because of a
        // bug in the driver manager.  I believe this has been fixed in the jdk 1.1 implementations.
        try {
          c = java.sql.DriverManager.getConnection (databaseURL, user, password);
          System.out.println ("Connection established.");
        }
        catch (java.sql.SQLException e) {
            e.printStackTrace();
          System.out.println ("Unable to establish a connection through the driver manager.");
          showSQLException (e);
          return;
        }
        break;
        
      case 2:
        // If you're working with a particular driver d, which may or may not be registered,
        // you can get a connection directly from it, bypassing the driver manager...
        try {
          java.util.Properties connectionProperties = new java.util.Properties ();
          connectionProperties.put ("user", user);
          connectionProperties.put ("password", password);
          connectionProperties.put ("lc_ctype", "WIN1251");
          c = d.connect (databaseURL, connectionProperties);
          System.out.println ("Connection established.");
        }
        catch (java.sql.SQLException e) {
            e.printStackTrace();
          System.out.println ("Unable to establish a connection through the driver.");
          showSQLException (e);
          return;
        }
        break;
      }

      // Let's disable the default autocommit so we can undo our changes later...
      try {
        c.setAutoCommit (false);
        System.out.println ("Auto-commit is disabled.");
      }
      catch (java.sql.SQLException e) {
        System.out.println ("Unable to disable autocommit.");
        showSQLException (e);
        return;
      }

      // Now that we have a connection, let's try to get some meta data...
      try {
        java.sql.DatabaseMetaData dbMetaData = c.getMetaData ();
       
        // Ok, let's query a driver/database capability
        if (dbMetaData.supportsTransactions ())
          System.out.println ("Transactions are supported.");
        else
          System.out.println ("Transactions are not supported.");

        // What are the views defined on this database?
        java.sql.ResultSet tables = dbMetaData.getTables (null, null, "%", new String[] {"VIEW"});
        while (tables.next ()) {
          System.out.println (tables.getString ("TABLE_NAME") + " is a view.");
        }
        tables.close ();
      }
      catch (java.sql.SQLException e) {
        System.out.println ("Unable to extract database meta data.");
        showSQLException (e);
        // What the heck, who needs meta data anyway ;-(, let's continue on...
      }

      // Let's try to submit some static SQL on the connection.
      // Note: This SQL should throw an exception on employee.gdb because
      //       of an integrity constraint violation.  
      try {
        s = c.createStatement ();
        
        s.executeQuery("select cast('????' as varchar(30) character set win1251) from rdb$database order by 1 collate pxw_cyrl");
        
        s.executeUpdate ("update employee set salary = salary + 10000");
      }
      catch (java.sql.SQLException e) {
          e.printStackTrace();
        System.out.println ("Unable to increase everyone's salary.");
        showSQLException (e);
        // We expected this to fail, so don't return, let's keep going...
      }

      // Let's submit some static SQL which produces a result set.
      // Notice that the statement s is reused with a new SQL string.
      try {
        rs = s.executeQuery ("select full_name from employee where salary < 50000");
      }
      catch (java.sql.SQLException e) {
        System.out.println ("Unable to submit a static SQL query.");
        showSQLException (e);
        // We can't go much further without a result set, return...
        return;
      }

      // The query above could just as easily have been dynamic SQL,
      // eg. if the SQL had been entered as user input.
      // As a dynamic query, we'd need to query the result set meta data
      // for information about the result set's columns.
      try {
        java.sql.ResultSetMetaData rsMetaData = rs.getMetaData ();
        System.out.println ("The query executed has " +
                            rsMetaData.getColumnCount () +
                            " result columns.");
        System.out.println ("Here are the columns: ");
        for (int i = 1; i <= rsMetaData.getColumnCount (); i++) {
          System.out.println (rsMetaData.getColumnName (i) +
                              " of type " +
                              rsMetaData.getColumnTypeName (i));
        }
      }
      catch (java.sql.SQLException e) {
        System.out.println ("Unable to extract result set meta data.");
        showSQLException (e);
        // What the heck, who needs meta data anyway ;-(, let's continue on...
      }

      // Ok, lets step thru the results of the query...
      try {
        System.out.println ("Here are the employee's whose salary < $50,000");
        while (rs.next ()) {
          System.out.println (rs.getString ("full_name"));
        }
      }
      catch (java.sql.SQLException e) {
        System.out.println ("Unable to step thru results of query");
        showSQLException (e);
        return;
      }

      // As an exercise to the reader, rewrite this code so that required
      // table structures are created dynamically using executeUpdate() on DDL.
      // In this way the code will be able to run against any database file rather
      // than just a previously setup employee.gdb.
      // Just to get you started, you'll want to define a method something like
      // the following...
      //
      // private static void createTableStructures (java.sql.Connection c) throws java.sql.SQLException
      // {
      //   // Some drivers don't force commit on DDL, Firebird JCA-JDBC driver does,
      //   // see DatabaseMetaData.dataDefinitionCausesTransactionCommit().
      //   // This is not necessary for Firebird JCA-JDBC driver, but may be for other drivers...
      //   c.setAutoCommit (true);
      //
      //   java.sql.Statement s = c.createStatement();
      //
      //   // Drop table EMPLOYEE if it already exists, if not that's ok too.
      //   try { s.executeUpdate ("drop table EMPLOYEE"); } catch (java.sql.SQLException e) {}
      //
      //   // Ok, now that we're sure the table isn't already there, create it...
      //   s.executeUpdate ("create table EMPLOYEE (...)");
      //
      //   // Ok, now populate the EMPLOYEE table...
      //   s.executeUpdate ("insert into EMPLOYEE values (...)");
      //
      //   s.close();
      //   c.setAutoCommit (false);
      // }
      //
    }

    // This finally clause will be executed even if "return" was called in case of any exceptions above.
    finally {
      System.out.println ("Closing database resources and rolling back any changes we made to the database.");

      // Now that we're all finished, let's release database resources.
      try { if (rs!=null) rs.close (); } catch (java.sql.SQLException e) { showSQLException (e); }
      try { if (s!=null) s.close (); } catch (java.sql.SQLException e) { showSQLException (e); }

      // Before we close the connection, let's rollback any changes we may have made.
      try { if (c!=null) c.rollback (); } catch (java.sql.SQLException e) { showSQLException (e); }
      try { if (c!=null) c.close (); } catch (java.sql.SQLException e) { showSQLException (e); }
    }
  }

  // Display an SQLException which has occured in this application.
  private static void showSQLException (java.sql.SQLException e)
  {
    // Notice that a SQLException is actually a chain of SQLExceptions,
    // let's not forget to print all of them...
    java.sql.SQLException next = e;
    while (next != null) {
      System.out.println (next.getMessage ());
      System.out.println ("Error Code: " + next.getErrorCode ());
      System.out.println ("SQL State: " + next.getSQLState ());
      next = next.getNextException ();
    }
  }
}

Fantastic code, you solved me a great trouble.

thank you mariuz, you are great!!

Excellent code. Thank you mariuz. I search answer to my problem with firebird 2 day, your example gives me it at minute.

Doesn't work for me on Mac (only embedded tried).