Thursday, April 19, 2012

There's a PDF going around that I wrote a few years ago with a Database tutorial in it for Java. I've been asked to please release the source code, so, here it is. This code will connect to a Microsoft Access database file on the local system and allow you to read and write via JDBC.
/**
 * Opens a Microsoft Access Database without having need to
 * have access rights to the Administrative Tools on Windows
 * to set up any ODBC connections.
 */

import java.sql.*;
import java.io.*;

/**
 * @author Ewald Horn
 * @company JavaK
 */
public class ConnectDB
{

   public static Connection con;
   public static final String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
   public static final String url = "jdbc:odbc:" +
       "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=";

   String path; // where the database can be found

   ////////////////////////////////////////////////////////////
   /**
    * Sets the path to the database.
    */
   public ConnectDB ()
   {
       path = "c:" + File.separator + "projects" +
           File.separator + "tutorial.mdb";
   }


   ////////////////////////////////////////////////////////////
   /**
    * Runner method for the TestAccess class.
    */
   public void go ()
   {
       makeConnection ();
       addRecipe ();
       removeRecipe ();
       updateRecipe ();
       showRecipes ();
       closeConnection ();
   }


   ////////////////////////////////////////////////////////////
   /**
    * Creates the database connection.
    */
   private void makeConnection ()
   {
       System.out.println ("Opening database...\n");
       try
       {
           Class.forName (driver);
           con = DriverManager.getConnection (url + path);
       }
       catch (Exception ex)
       {
           System.out.println ("Error opening the database!");
           System.out.println (ex);
           System.exit (0);
       }
       System.out.println ("Success!");
   }


   ////////////////////////////////////////////////////////////
   /**
    * Removes a recipe from the database.
    */
   private void removeRecipe ()
   {
       String sql = "DELETE FROM RECIPESTABLE WHERE RECIPEID=11";
       System.out.print ("\nRemoving a recipe : ");

       try
       {
           Statement statement = con.createStatement ();
           int result = statement.executeUpdate (sql);
           System.out.println (" Removed " + result + " recipe(s).");
       }
       catch (Exception ex)
       {
           System.out.println ("Error removing a recipe!");
           System.out.println (ex);
       }
   }


   ////////////////////////////////////////////////////////////
   /**
    * Modifies an existing record.
    */
   private void updateRecipe ()
   {
       String sql = "UPDATE RECIPESTABLE SET " +
           "RECIPENAME='Pizza' WHERE RECIPEID=8";
       System.out.print ("Updating a record : ");
       try
       {
           Statement statement = con.createStatement ();
           int result = statement.executeUpdate (sql);
           System.out.println (" Updated " + result + " recipe(s).");
       }
       catch (Exception ex)
       {
           System.out.println ("Error removing a recipe!");
           System.out.println (ex);
       }
   }


   ////////////////////////////////////////////////////////////
   /**
    * Adds a recipe to the database.
    */
   private void addRecipe ()
   {
       String sql = "INSERT INTO RECIPESTABLE(RECIPENAME,INGREDIENTS)" +
           " VALUES('Any Recipe','Ingredients')";

       System.out.print ("\nAdding a recipe : ");

       try
       {
           Statement statement = con.createStatement ();
           int result = statement.executeUpdate (sql);
           System.out.println (" Added " + result + " recipe(s).");
       }
       catch (Exception ex)
       {
           System.out.println ("Error adding a recipe!");
           System.out.println (ex);
       }
   }


   ////////////////////////////////////////////////////////////
   /**
    * Displays all the recipes in the database.
    */
   private void showRecipes ()
   {
       String sql = "SELECT * FROM RECIPESTABLE";

       System.out.println ("\nRecipes in the database : \n");

       try
       {

           Statement statement = con.createStatement ();
           ResultSet rs = statement.executeQuery (sql);

           if (rs != null)
           {
               while (rs.next ())
               {
                   int recipeID = rs.getInt ("RECIPEID");
                   String recipeName = rs.getString ("RECIPENAME");
                   System.out.println (recipeID + " " + recipeName);
               }
           }

           rs.close ();
           statement.close ();

       }
       catch (Exception ex)
       {
           System.out.println ("Error reading database information!");
           System.out.println (ex);
       }
   }


   ////////////////////////////////////////////////////////////
   /**
    * Closes the connection cleanly.
    */
   private void closeConnection ()
   {
       System.out.println ("\nClosing database.");
       try
       {
           con.close ();
       }
       catch (Exception ex)
       {
           System.out.println ("Error closing the database!");
           System.out.println (ex);
       }
   }


   ////////////////////////////////////////////////////////////
   /**
    * Main method for ConnectDB.java
    */
   public static void main (String args[])
   {
       ConnectDB testApp = new ConnectDB ();
       testApp.go ();
   }
}