JDBC

Before you Begin

There are a few things you should make sure you understand/obtain before you begin. Take a look below:
  • Basic understanding of object oriented programming fundamentals.
  • Limited Database knowledge including SQL Syntax.
  • JDK 1.6 or greater installed and ready to go.
  • A text editor.
  • A JDBC Database Driver contained in products such as MySQL, PostgreSQL or RDM Workgroup

Steps to Creating your Application

This JDBC tutorial is meant for those using a command line interface. Here is the Full .java File – JDBC Tutorial to use during the tutorial.

Step 1 Open a command line prompt

If using a Windows operating system, click the start menu and where it says, “search for programs or files”, type cmd. Then press return.
For a Unix based system, find the application labeled “Terminal”. Open that application.

Step 2 Creating your .java file

Using your text editor, create a new .java file called HelloWorld_JDBCTutorial_main.java

Step 3 Creating your class

Your class must contain the same name as the .java file you just created. Create your class exactly like the code snippet below.
public class HelloWorld_JDBCTutorial_main {
This class is a public class because it has the ability to be accessed outside the scope of this class. In this example everything is done within this class.

Step 4 Creating your main method

The main method is the entry point for your program. For this simple example we are only using one .java file. Therefore, HelloWorld_JDBCTutorial_main { will contain the main method as shown below. For this example we will throw the outermost SQLException (The exception related to the Connection object) for simplicity.
public static void main(String[] args) throws SQLException {

Step 5 Creating and initializing your Connection Object

You will need to initialize your Connection object before you have access to any of the methods it contains. You will retrieve the connection object by calling a method from the DriverManager class called DriverManager.getConnection({Connection URL}). The return from this method is your Connection object.
It is good practice to start a new try block for every object that you initialize. When you are done with the object, simply add a finally block that performs the corresponding close() method, and the outermost block will contain your catch block to handle all possible SQLExceptions. This will be easier to see with the full code.
*Note: The URL will change depending on the Driver you are using, in this case, the RDM JDBC driver is being used. The comments are specific to this driver.
// Note that you can use TCP/IP by using this URL
// “jdbc:raima:rdm://localhost”
// The rdmsqlserver must be running to use TCP/IP

Connection Conn = DriverManager.getConnection(“jdbc:raima:rdm://local”);
try {

Step 6 Creating your Statement Object

The newly created Connection object Conn has a method in it called createStatement() that will return a Statement object. You will then use with this Connection to the database.
/* Creates the Statement object to be used for future SQL Statement executions */
Statement Stmt = Conn.createStatement();
try {

Step 7 Execute Statements with your newly created Statement Object

Using the Statement object Stmt you just created (Stmt) you can execute several different methods depending on the type of statement you want to execute. For example, if you would like to execute a statement such as “OPEN database_name” or “DELETE * FROM table_name” you would perform a Statement.execute() method. You can see execute() used in the code snippet below. In this example we will create the database programmatically, and we want it to be self contained (if it is run twice the database will be deleted before being created again). See step 9 for committing changes.
*Note: This database is created programatically. There are tools out there to create your database separate from your program. View a quick “How to Create a SQL Database” tutorial on how to do this.

try {
//Since the database is created here, it cannot be created twice
Stmt.execute(“DROP DATABASE hello_db”);
} catch (SQLException exception) {}
Stmt.execute(“CREATE DATABASE hello_db”);
Stmt.execute(“CREATE TABLE hello_table (f00 char(31))”);
Conn.commit();

Step 8 Creating a Prepared Statement Object and using it in your program

A good way to insert data into your database is to use a Prepared Statement. This statement will be prepared at compile time and have numerous other advantages. The main advantage being that you can dynamically tell the program what data values you would like to insert into your database. This is done using setter and getter methods from the Prepared Statement object. A setter method is a method that is used to set a specific field or in this case a parameter to a given value. You will be able to see this used below. A getter method is used to retrieve a field or value, but we are not going to be using that here.
PreparedStatement PrepStmt = Conn.prepareStatement(“INSERT INTO hello_table (f00) VALUES (?)”);
try { //Sets parameter value to a string
PrepStmt.setString(1, “Hello World!”);
PrepStmt.execute();
What is happening here is that you first create the Object and it contains your Prepared SQL Statement inside of it. In this example the Statement is an insert statement that inserts into the table at a single row with an unknown value (?). By performing the next prepStmt.setString(1, “Hello World!”), you are setting this parameter to the value “Hello World!”. When the prepStmt.execute() is called, that string is inserted into the database.

Step 9 Committing Changes

In order to have your changes finalized in the database you must perform a commit. In JDBC this is done through a method using the Connection object. The method we will be using is Connection.commit() and that will finalize any changes you made during a transaction.

Conn.commit();
//Commits all changes

Step 10 Creating your Result Set Object (retrieving data from the database)

In JDBC, when you want to retrieve data from the database, you perform a SQL Select Statement using your Statement object with an execute method that returns a Result Set object. This method is called Statement.executeQuery(). This means it will execute the specified Query and return the Query results in the given Result Set.
ResultSet RS = Stmt.executeQuery(“SELECT * FROM hello_table”);
try {

Step 11 Accessing the Result Set

In order to access every piece of data in your Result Set, you must iterate through it. A method is provided within the Result Set to check if the next result in the Result Set is NULL, meaning no more data. If the method ResultSet.next() returns TRUE then there is data in the database and you can retrieve it from your result set.
To access the data inside the Result Set you must perform a getter method, similar to the setter method you performed above. There are numerous getter methods available to retrieve the specific data type from the Result Set. In this example we want a string, therefore we use the ResultSet.getString() method, with the parameter being the column you are retrieving from.
Take a look at the code below to see an example of how this can be done.
while(RS.next() != false)
{
System.out.println(RS.getString(1));
}

Step 12 Deallocating Resources

Here you will deallocate all of the resources you used above. In this case your resources are each object that you used above, being your Connection object, Statement, Prepared Statement and Result Set objects. For each nested try block you will have a finally block, which performs the corresponding close method. In this example we dropped the database to clean up resources and keep the program self contained. This would normally not be necessary. Look at the code snippet below.
} finally {
RS.close();
}
} finally {
Stmt.execute(“DROP DATABASE hello_db”);
PrepStmt.close();
}
} finally {
Stmt.close();
}

Step 13 Final Catch and Finally block

The very last block contains both a catch block and a finally block. The catch block determines what to do if an exception was thrown in the code above. In this case just displaying the exception to standard out is sufficient. The finally block will be executed regardless of an exception being thrown. Here we will deallocate our Connection object. In this example, if your Connection object does throw an exception for some reason, it is “thrown” out of the method. In a normal situation you could either add another try catch block, or declare this program as a separate method and handle it elsewhere.

} catch (SQLException exception) {
System.err.println(“SQLException : ” + exception.toString());
} finally {
Conn.close();
}

Step 14 Compiling your application

If your JDK is installed correctly you should have access to the Java Compiler, called javac. In order to have your program successfully compile, you must first set your CLASSPATH to contain all of your class files, including .jar files that you are using. Since the JDBC driver is usually a .jar file you will need to include this in your CLASSPATH. If you are using command line you will perform operations similar to this:

Linux: CLASSPATH=$CLASSPATH;{Current working directory};{Direct Path to .jar file}
CLASSPATH=$CLASSPATH;/home/users/kevin/workspace/;/home/users/kevin/workspace/rdmjdbc11.jar

*Note: ./ can be used to signify current working directory

Windows: set CLASSPATH=%CLASSPATH%:{Current working directory}:{Direct path to .jar file}
set CLASSPATH=%CLASSPATH%:C:\users\kevin\workspace:C:\users\kevin\workspace\rdmjdbc11.jar

After you have set up your CLASSPATH you should be ready to compile. In order to do this you must use the javac compiler. On all platforms the compile will be the same. The format looks like this:
javac {main_class.java (entry point to program)}

In this case you would type:
javac HelloWorld_JDBCTutorial_main.java

You should see no warnings, and after completion a .class file will have been generated. You directory should contain:
HelloWorld_JDBCTutorial_main.java
HelloWorld_JDBCTutorial_main.class

Step 15 Running the program

Running the program is as simple as typing “java {executable name}”. In this case you would have “java HelloWorld_JDBCTutorial_main” as that is the entry point to your program. If everything works as expected you should see this displayed on your screen:
JDBC Tutorial "Hello World!" Printout

Here is the Full .java Code

import java.sql.*;
/**
* HelloWorldExample : JDBC Tutorial that creates a simple database, stores in a single row
* and retrieves the data to then be displayed on standard out. The database is cleaned up
* to reduce clutter for this example.
* Author: Kevin Hooks
* Date: April 18th, 2012
**/
public class HelloWorldExample {



    public static void main(String[] args) throws SQLException{
        // Note that you can use TCP/IP by using this URL
        // "jdbc:raima:rdm://localhost"
        // The rdmsqlserver must be running to use TCP/IP
        Connection Conn = DriverManager.getConnection("jdbc:raima:rdm://local");
        try {
          
            Statement Stmt =  Conn.createStatement();
            try {
                try { //Since the database is created here, it cannot be created twice
                    Stmt.execute("DROP DATABASE hello_db");
                } catch (SQLException exception) {}
               
                Stmt.execute("CREATE DATABASE hello_Db");
                Stmt.execute("CREATE TABLE hello_table (f00 char(31))");
                Conn.commit();
               
                PreparedStatement PrepStmt = Conn.prepareStatement("INSERT INTO hello_table (f00) VALUES (?)");
                try { //Sets parameter value to a string        
                    PrepStmt.setString(1, "Hello World!");
                    PrepStmt.execute();
                    Conn.commit();

                    ResultSet RS = Stmt.executeQuery("SELECT * FROM hello_table");
                    try {

                        while(RS.next() != false) {
                            System.out.println(RS.getString(1));
                        }
                    } finally {
                        RS.close();
                    }
                } finally { //Cleans up by dropping database in this case
                    Stmt.execute("DROP DATABASE hello_db");
                    PrepStmt.close();
                }
            } finally {
                Stmt.close();
            }
           
        } catch (SQLException exception) {
            System.err.println("SQLException: " + exception.toString());

        }finally {
             Conn.close();          
        }

    }

}

to compare to your own.

Done!   Get Your Free Badge Below

Congratulations! You have just finished your first JDBC application. This specific JDBC Tutorial is very simple, and your future endeavors may prove to be much more difficult.

No comments:

Post a Comment