Mitchell Shelton: April 2011

/*
* 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

Mitchell Shelton

Monday, April 25, 2011

Tilt Shift Photography

Sunday, April 24, 2011

NetBeans - Java Database Connection Tutorial

Friday, April 22, 2011

Command Line Basics Videos by Addison Berry on Lullabot