Three years ago I started with my first project in Java. It was a desktop application which depends on database to store its data and retrieve them according to some business rules. Through the development of this project and because I worked a little bit with Java before, I had many questions of the kind “How to do X in Java?” of course I needed to do some researches to find the answers; that was easy due the simplicity of my questions :-). Eventually I summarized these questions and their answers in three parts post and we’re in the first part. I assume here that you already know Java and SQL for the part of database in this post.

Java and Database

Java has a standard API which is called Java Database Connectivity (JDBC) and it is used to access the database regardless of the database management system which is used in the backend.

As a first step you need to choose which database management system (DBMS) you’re going to use in your project, of course there are many options which are supported on JDBC from SQLite to Oracle. After deciding the what is your DBMS you need to find a JDBC driver for it by using your favorite search engine. Usually the JDBC driver is a JAR file (Java Library) which should be included in your project to use the driver in your code.

For my project I chose SQLite as a DBMS because, you know, it is simple and the project actually didn’t need a large-server-client-architecture-based DBMS. After a small search I found this driver. After downloading the JAR file I needed to include it in my project and that was a simple step with Eclipse. I just right-clicked my project and from “Build Path” sub-menu I selected “Add External Archives” and added the JAR file to my project which is after that has been shown in “Referenced Libraries”.

Loading the Driver and Connecting to the Database

That was the initial step of working with database on Java. You just need to find the right JDBC driver and include it in your project. Let’s move to the coding part, first you need to import the package java.sql in your source code then you could start with the following code.

try {
	Class.forName( "org.sqlite.JDBC" );
} catch (ClassNotFoundException e) { }

Well, what does this code do? simply it loads the JDBC driver. As you can see we used the static method Class.forName here to load the driver; I’m going to talk about this method later in another section of this post. However; the method forName receives one parameter and the value of it should be found in the documentation of the JDBC driver which you depends on. In my case the value of SQLite’s JDBC driver was the one which is presented in the previous bunch of cde.

After loading the JDBC driver using Class.forName method we could start using the database in our code and obviously the first thing we want to do is connecting to the database:

try {
	Connection con = DriverManager.getConnection("jdbc:sqlite:db_file.db");
} catch (SQLException e) { e.printStackTrace(); }

In this code we used the class DriverManager which manages the drivers of JDBC. The method getConnection tries to connect to the database and in case of success it returns an object of Connection. The parameter which we passed to this method is the database URL which usually comes in the following format: jdbc:subprotocol:subname and this parameter also is a driver-specific. In our example you could see that the last part of the URL is the database name.

Let’s talk a little bit about the object which will be returned from the method DriverManager.getConnection; this object represents a session of the database; by using this object we could manipulate our database using SQL statements.

Preparing the SQL Statement

Now, we loaded the driver; got our connection to the database. The next logical step is manipulating our database. Inserting some data, retrieving them, updating them or deleting them; that could be done using SQL so we need a way to execute SQL statements. The object which had been returned by the method DriverManager.getConnection to do that which means that the interface Connection has the required methods to deal with SQL statements.

One of these methods is prepareStatement which has the following declaration - actually one of its declarations -:

PreparedStatement prepareStatement(String sql)

It requires one parameter which is the SQL statement and returns an object of type PreparedStatement.

As you know that SQL is just another programming language which is used as an interface for the databases. And we know that every code we write in a high-level language should be compiled to a language which the machine could understand and SQL is not an exception.

The job of the method prepareStatement is to compile the passed SQL statement and the result of the compilation process will be stored in an object of PreparedStatement which means that after using this method we got a compiled version of our SQL statement and we could use it several times with no need to recompile the original statement over and over again.

In addition the main purpose of the method prepareStatement is preparing the SQL statements which have parameters and we could say that it is the best way to prevent SQL Injections.

Retrieving Data

Let’s see an example:

try {
		PreparedStatement stat = con.prepareStatement( "SELECT * FROM employee WHERE id=?" );

		stat.setLong( 1, req_id );

		ResultSet res = stat.executeQuery();

		if ( res.next() )
		{
			String name = res.getString( "name" );
			String address = res.getString( "address" );
			int phone = res.getInt( "phone_number" );

			System.out.println( name + " " + address + " " + phone );
		}

		stat.close();

} catch (SQLException e) { System.out.println( e.getMessage() ); }

Let’s assume that we have a table called “employee” which has the column “id” as a primary key and we have a variable “req_id” which holds the id of the employee which we need to show his/her information. If you already know SQL so you can see nothing new in the SQL statement in the example but only the weird question mark. The question mark in this context tells JDBC that in this place we are going to pass a parameter and the value of this parameter must replace the question mark in this statement.

As we said the id of the employee which we want to show his/her information resides in the variable req_id; you can see in this line of the example we request from JDBC to change the question mark to the value which resides in “req_id”:

stat.setLong( 1, req_id );

You can observe that we used a method which is called setLong and we did that because the type of “req_id” is long. The name of the method should be changed according to the value which we need to pass for the SQL statement. The first parameter of setLong is the position of the question mark which we would like to replace in the SQL statement. Assume that we have a SQL statement which has two parameters - which mean two question marks - the first question mark shows in the SQL statement has position 1 and the second has position 2 and so on.

Well, why we use this way to pass the values in SQL statements? simply to get rid of SQL Injections (and maybe there are more advantages which I missed).

What we done until now is compiling the SQL statement; we need to execute the query to get the result of the query and that’s done by the following line:

ResultSet res = stat.executeQuery();

We can see that the method executeQuery returns an object of type ResultSet and here where query’s result resides. This object has a cursor which initially points on position before the first row of the result; we could use the method next to move this cursor one step forward. The method next returns true when there is a row otherwise it returns false.

To get the value of a column we could use one of the getters which are available in ResultSet as we can see in this part of the example:

if ( res.next() )
{
	String name = res.getString( "name" );
	String address = res.getString( "address" );
	int phone = res.getInt( "phone_number" );

	System.out.println( name + " " + address + " " + phone );
}

In the first line we check if there is any data fetched from the database and we move the cursor one step forward and in the block of the if statement we get the values of the row according to the table’s columns and stores these value in some local variables. You can see that the parameters of getter methods represent the name of the column on the database.

Inserting and Updating Data

Let’s now talk about inserting data. We can use the already explained prepareStatement way to prepare our insert statement as the following:

try
{
	PreparedStatement stat = null;

	stat = con.prepareStatement( "INSERT INTO employee VALUES( ?, ?, ? )" );

	stat.setString( 1, name );
	stat.setString( 2, address );
	stat.setInt( 3, phone );

	int effected_rows = stat.executeUpdate();

	stat.close();

	if ( effected_rows > 0 )
		System.out.println( "Inserted" );
	else
		System.out.println( "Error" );
} catch (SQLException e) { e.printStackTrace(); }

There are only two differences here from retrieving data. First we used the method executeUpdate instead of executeQuery and this method returns the number of effected rows and in our case it should be one and according to this value we could tell if the execution of the SQL statement succeed or failed.

To update data we could use the same way but we just need to change the SQL statement.

What exactly is Class.forName method?

(AKA: Welcome to “Reflection” World!)

We already used the method Class.forName in the previous part to load the database driver. In this part we are going to take a look at it. The method forName is a static method which resides in a class called Class; this method load classes dynamically at the run-time; that is while the application is running.

Java has a feature which is called “Reflection”; usually the method forName is used to take advantage of this feature. Simply “Reflection” allow the programmer to access to all information that related to a specific class at the run-time. For example we could use reflection to get the list of all class’s methods also we can call them at run-time.

Let’s see an example:

import java.lang.reflect.Method;

public class Main
{
	public static void main(String[] args)
	{
		String cName = args[ 0 ];

		try {
			Class<?> myClass = Class.forName( cName );

			Method[] methods = myClass.getDeclaredMethods();

			for( Method k: methods)
			{
				System.out.println( k.getName() );
			}
		} catch (ClassNotFoundException e) { }
	}
}

This example works as the following: It should be run from the command line and a class name should be passed as an argument; the class will be loaded at the run-time; that is the name of the class is unknown until the user runs the code. After loading the required class our code is going to print the name of all declared method in the class. Let’s assume that we have the following class:

public class RefTest
{
	public void one()
	{
		two();
	}

	private void two()
	{
		System.out.println( "Hello World!" );
	}
}

If we run the main example and pass the class name “RefTest” as an argument for it we could see it prints the name of the methods of “RefTest” and they are “one” and “two”. We could pass the name of the example’s class itself which is “Main” and it will print “main”.

As you can see in the main example that the method forName receives one parameter which is the name of the class we would like to load at run-time; it returns an object of type Class which represents a Java class and the object itself represents the class which we loaded it at run-time and in our case it is “RefTest”. There are many methods in “Class” which we could use to get any information about the loaded class.

We already used one of “Class” methods in our main example and this method is “getDeclaredMethods” which is obviously returns the list of declared methods in the loaded class. This list will be an array of type Method which is another class that represents a Java method. We used the method “getName” from class Method to get the name of loaded class’s methods.

How to reach application’s files?

In some cases you may include some files in you application and you would like to reach to these files to use them in you application’s code. These files could be images for instance. In my case the SQLite database file was included with the application itself and I needed a way to reach the location of my application’s directory in the user’s computer so I could reach the database file to establish the connection with it. The following snippet was the solution:

String path = new File( Main.class.getProtectionDomain().getCodeSource().getLocation().getPath() ).getParent();

Note that Main is the name of the class which has this line on it. In this way you could find the location of your application’s executable file so you could locate your files in the same directory of the executable file and also you may create sub-directories to organize your hierarchy.

How to call a method before closing the application?

In Swing applications that can be done by simply bind some callback to the window listener as the following:

JFrame mainWin = new JFrame( "Example" );

mainWin.addWindowListener( (WindowListener) this );

Then we could override the method “windowClosing” and write inside it the code which we would like to execute when the user close the application:

@Override
public void windowClosing(WindowEvent arg0)
{
	// Do something when the use close the main window ...
}

The other solution which is applicable when we don’t use Swing is the following:

Runtime.getRuntime().addShutdownHook(new Thread(new Runnable()
{
    public void run()
    {
		// Do something
    }
}));

Using this way when the application is closed a new thread will be started and the code which resides in the method “run” will be executed.

The Look & Feel of the Swing’s application

Swing has its own Look & Feel but you probbably would like to use the user’s operating system’s Look & Feel for your application. The following code does this trick:

try
{
	UIManager.setLookAndFeel( UIManager.getSystemLookAndFeelClassName() );
} catch (ClassNotFoundException e) {}
catch (InstantiationException e) {}
catch (IllegalAccessException e) {}
catch (UnsupportedLookAndFeelException e) {}

See you in the second part :-).