NetBeans - Java Database Connection Tutorial

/*
 * This console application demonstrates a basic database connection.
 *
 * NetBeans Database Connection Tutorial
 *
 *       - Completed with NetBeans 6.9.1 on Microsoft Windows 7
 *
 *       - When you see a -> in this tutorial it is typically instructing
 *       you to open a menu/submenu
 *
 *       - I try not to assume too much about the reader, but you should
 *       already be comfortable with some Java basics and maybe even SQL.
 *       I reference several tutorial resources throughout this tutorial
 *       that can get you started.
 *
 *
 * Finding a tutorial that simply explained connecting to a database
 * was beginning to seem impossible. In the end I never did find a
 * rock solid tutorial. This is a collection of the bits and pieces I
 * was able to piece together from around the web. Hopefully it will help
 * others who are trying to accomplish a seemingly simple task.
 *
 * A little background about me and this tutorial. I would like to point out
 * that this is simple, dead simple. It might not be useful to many. A vast
 * majority of the tutorials that I felt "blocked my way" were full on CRUD
 * interfaces. So the connection took backseat to creating that interface. I
 * suspect that creating the CRUD interface is what more people really want
 * to do. So demand has dictated that those tutorials dominate search results.
 * This is the 2nd thing I have written in Java outside of one college class
 * on Java about 10 years ago and the video tutorials on
 * http://www.javavideotutes.com/. The first thing I wrote, more like clicked
 * buttons, was just a silly swing calculator tutorial on the NetBeans site
 * (http://netbeans.org/kb/docs/java/gui-functionality.html). So, why this?
 * Why not move onto a CRUD interface? Because I'm a web developer. I think
 * in databases. I think of that as the centerpiece of any web application. So,
 * I attacked that problem first. This tutorial is the result.
 *
 * Open up NetBeans and let's get started.
 *
 * Step One: Create the project
 *
 * File -> New Project -> Categories: Java | Projects: Java Application
 *
 * Click "Next"
 *
 * Enter the name of your project and where you would like to save it. I left
 * the other fields untouched. On my system all of the checkboxes were checked
 * by default.
 *
 * You now have a very simple Java Application ready to go.
 *
 * Step Two: Add the database
 *
 * What follows are a few quick and dirty steps to creating a database, tables,
 * and data in Netbeans. If you would like to explore this topic further I
 * recommend you visit the NetBeans tutorial on database interaction here:
 * http://netbeans.org/kb/docs/ide/java-db.html
 *
 * If you don't have a sidebar with a services tab you can go to
 * Window -> Services
 *
 * You should now see the Services window. We are after the Service called
 * Java DB under Databases. So go to Databases -> Java DB, now right click and
 * select "Create Database..."
 *
 * Enter a name for your database, the username and password to access your
 * database. Go to properties and take note of the Java DB Installation path.
 * Mine was:
 *
 * "C:\Program Files\glassfish-3.0.1\javadb"
 *
 * Now click "Cancel" to go back to the database setup dialog and double
 * check the info you entered for your database. Don't forget your password,
 * I'm not sure if there is an easy way to recover it. Now click "Ok". If you
 * already clicked okay before getting the path you can just right click on the
 * Java DB icon and go to properties to see the path again.
 *
 * Below Java DB you should now see the database you created. It will look
 * something like this:
 *
 * jdbc:derby://localhost:1527/your_database_name [your_name on your_name]
 *
 * Right click on that icon and select "Connect..."
 *
 * If you encounter connection errors in your code double check this. I
 * restarted NetBeans in the middle of writing this code and discovered that
 * I had to manually reconnect.
 *
 * Step Three: Create the Tables
 *
 * If you expand the tree for your new database you should multiple entries.
 *
 * We are only concerned about the "APP" entry. Expand "APP", you should now
 * see three folders labeled "Tables", "Views", and "Procedures", we will be
 * focusing on the Tables folder.
 *
 * Note: If you can not expand the database
 * information you are most likely not connected. This should be indicated by
 * a "broken" icon next to your database name.
 *
 * Right click on the "Tables" folder and select "Create Table..."
 *
 * This should bring up a dialog box where you can create your table. Do so by
 * giving it a name. I called mine "users". If you change the name of the table
 * you will need to change the code to reflect that change.
 *
 * Now add some columns. I created two columns. One called "id" and another
 * called "name". They were configured as follows:
 *
 * id:
 *
 * Name: id
 * Type: NUMERIC
 * Size: 16
 * Checked "Primary Key"
 * Checked "Index"
 *
 * name:
 *
 * Name: name
 * Type: VARCHAR
 * Size: 255
 *
 * Once our Table and Columns are created we need to add data to them.
 *
 * Right click on our new table and select "View Data..."
 *
 * Two things happen here. One, you have just automatically created the query
 * you are going to use in your app. Two, you should have a table with a
 * "Insert Records" icon on it.
 *
 * First, the query:
 *
 * If you named your table users then it should be as follows:
 *
 * select * from APP.USERS
 *
 * You will notice that in the code mine is this:
 *
 * SELECT name FROM app.users
 *
 * In web development using the "*" selector is considered bad practice. I
 * imagine this is the same for Java development. So I replaced it with the
 * column I wanted to display ("name"). I also changed the case to reflect
 * what I commonly see in SQL queries. All SQL commands are all caps, while
 * other content is in lower case. It will work either way, these are more my
 * personal preferences.
 *
 * If you are not comfortable with SQL this is a decent way to generate your
 * query until you get up to speed. If you changed the table name or create
 * different columns you will want to keep this method in mind.
 *
 * Second, the table:
 *
 * There are lots of options here, but all we are concerned with is adding a
 * record or two. So click the "Insert Records" icon.
 *
 * This will bring up a dialog where we can start entering in some data. I
 * created two records. You will need to manually number your records here. SQL
 * GUI's I have dealt with in the past have always auto-incremented your
 * primary key for you. This one does not do this via the GUI.
 *
 * I entered the following:
 *
 * id: 1
 * name: bob
 *
 * id: 2
 * name: fred
 *
 * Step Four: Adding the database to your project
 *
 * This was tricky to figure out. Your database will not work without it.
 * What you are doing here is giving your project access to the database
 * drivers.
 *
 * Go to your "Projects" tab or click Window -> Projects
 *
 * Right click on your project and select "Properties"
 *
 * A dialog box will pop up. Go to Categories: Libraries. Now under the
 * "Compile" tab click the "Add JAR/Folder" button. Remember that "Java DB
 * Installation path" we talked about earlier? Navigate to there from the file
 * browser that pops up.
 *
 * go into the "lib" folder and select the file called "derbyclient.jar"
 *
 * Step 5: The code (finally!)
 *
 * Everything you need is below. I have commented the code as best as my
 * limited knowledge of Java will allow me.
 *
 * There are a few things about this code that I would like to cover.
 *
 * The driver name: I am getting the driver name through some most likely
 * unnecessary code -
 *
 * Driver driverClass = (Driver) DriverManager.getDriver(db_loc);
 * String db_driver = driverClass.getClass().getName();
 *
 * You could alternative manually assign the value to the db_driver String.
 *
 * for example:
 *
 * String db_driver = "org.apache.derby.jdbc.ClientDriver";
 *
 * What my complex code is doing is grabbing the name of the driver based on
 * the URL of our database. I thought it was some handy code for trouble
 * shooting. It led me to realize that my project did not have access to the
 * database. So I left it in.
 *
 * Imports:
 *
 * I saw a lot of tutorials that listed the SQL import as:
 *
 * import java.sql.*;
 *
 * I applied the SQL "*" SELECT opinions here. Why import all of that code if
 * you aren't going to use it? Granted, this could cause problems while
 * learning. You may attempt to use some code from another tutorial and not
 * understand why it isn't working. A lack of an imported class might be the
 * reason!
 *
 * Displaying results:
 *
 * This is the next challenge for myself. The method I'm using here is pretty
 * simple. It loops over the result set and displays results from a particular
 * column. My challenge now is to learn all of the ways that you can deal with
 * that result set in Java. My instinct is to get it all into an array so I can
 * just manipulate that. We will see where that leads me.
 *
 * Conclusion:
 *
 * I hope you have enjoyed this tutorial and found it useful.
 *
 * If you are new to Java or any language, please, write Tutorials! You may
 * think you don't know enough or don't have anything to contribute, but keep
 * in mind, you are bringing a fresh perspective to even the veteran users. My
 * opinion, especially in the case of this tutorial, is that if no one else is
 * doing it, you should. So, even if my way, or your way, is the "wrong" way,
 * at least it works.
 *
 * A note to all tutorial writers out there: This is a "Working Tutorial"
 * Please, don't write non-working tutorials. When your tutorial has the words
 * "You can figure out that part", you are missing the point. No, we can't, or
 * we wouldn't be reading your tutorial!
 *
 * I apologize for any grammar mistakes, spelling errors, coding nightmares, or
 * Java good practices that I have violated during the course of this tutorial.
 * Please leave comments if this tutorial is in a blog/forum or email me at
 * info@mitchellshelton.com if you have questions or suggestions.
 *
 * Thank you,
 * -Mitchell
 *
 */

// Define our package
package databasetest;

// Import libraries
import java.io.IOException;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;

/**
 * @author Mitchell Shelton
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws IOException {
        // Display a message to wrap around our test
        System.out.println("Testing some SQL!");

        // Set variables to hold our database information
        /*
         * db_loc: The URL that points to our database. In NetBeans (6.9.1)
         * this can be found by going to services -> right click on database ->
         * properties
         *
         * db_user & db_pass: credentials to access the database that would have
         * been entered when the database was created
         *
         * db_query: The SQL query we are testing
         *
         */
        String db_loc = "jdbc:derby://localhost:1527/testdatabase";
        String db_user = "test";
        String db_pass = "test";
        String db_query = "SELECT name FROM app.users";

        // The database connection must be in a catch/try
        try {
            // Find the driver based on the database URL
            Driver driverClass = (Driver) DriverManager.getDriver(db_loc);
            String db_driver = driverClass.getClass().getName();

            // Connect to the database
            Class.forName(db_driver).newInstance();
            Connection c = DriverManager.getConnection(db_loc, db_user, db_pass);

            // Run our SQL query
            Statement sql = c.createStatement();
            ResultSet rs = sql.executeQuery(db_query);

            // Display our results
            while (rs.next()) {
                String lastName = rs.getString("name");
                System.out.println(lastName + "\n");
            }

            // Display a confirmation message that our test worked,
            // catch should trigger before this is displayed if there
            // is a problem.
            System.out.println("Connection Successful");
        } catch(Exception e) {
            // Display our error message
            System.out.println("The following exception occurred:");
            System.out.println(e.getMessage());
        }

        // Close our output wrapper message
        System.out.println("Done Testing SQL!.");
    } // end public static void main

} // end public class Main