Java Database Connectivity

When developing a Java application which uses a database, you need to consider several things, such as are you developing the Java Application first, or the Database first? Which database are you going to use (Oracle, DB2, SQL Server, MySQL, etc)

Regardless, you will need a JDBC driver for your Java application. Most likely this will be available from the developer of your database. JDBC stands for Java DataBase Connectivity. This will allow you to connect to the actual database server, and connect to the database itself, so you can send it commands.

It is worth noting that you will need a Java Database connector specific for your database. So having one for one application doesn’t mean that you won’t be able to use it for a different database.

Note: This does require that you know SQL to use JDBC, as you will be passing in SQL, not just using an API call.

Connecting to a Database

Connecting to a database is a time expensive process. Therefore, you usually do not connect directly to a database, rather you use a connection pool that is already connected and ready for you. This connection pool may have several connections open and ready so you can do other things, and/or in a case where an application is share – such as a web application, other users can access the database. In some cases a connection pool comes with the driver, in some cases you have to have another download.

There is a Connection class you will need to use. This establishes a connection with a data source.

Connection conn = null;
try{
    conn = DriverManager.getConnection("jdbc:mysql://localhost/test");
} catch(SQLException e) { }

Each database will look to have a slightly different way to connect, maybe a DataSource, or the DataManager as an example.

Creating a Statement

Statements are SQL statements which you can pass to the DB. You will often want to use a prepared statement.

User user = ...;
PreparedStatement statement = conn.prepareStatement("insert into users (first_name, last_name) values (?,?)");
statement.setString(1, user.getFirstName());
statement.setString(2, user.getLastName());
statement.executeUpdate();

Each question mark is a place holder for a value. We could write those values by concatenating a string, but using prepared statements has several advantages, such as letting the JDBC driver determine the symbol to enclose the value which may vary between databases, it protects against SQL injection attacks, and more.

To replace the question mark, you use a setString, or other data type to fix the value to the SQL statement.

Once you have your statement ready, you simply executeUpdate(), this will work for any general type of SQL command.

Depending upon your database, and if you are automatically committing commands, you may need to call the commit() method, or if there was an exception, a rollback() method.

Reading Data

To read data, you need pass in the SQL you want to read, and you will need to store that result in a special data type which will hold the results.

ResultSet resultSet = statement.executeQuery("select * from users");

The ResultSet holds all of the result, which you get from the executeQuery method. You will need to navigate through the result set, and then access the individual objects.

while (resultSet.next()) {
	String firstName = resultSet.getString("first_name");
	String lastName = resultSet.getString("last_name");

	System.out.println("first name : " + firstName);
	System.out.println("last name : " + lastName);
}

As you can see, the next() method allows you to move through the result set, and returns a true if it was able to, or false if it couldn’t.

You will use a method, like getString to get a given field. As you see, you can use the index name, or you can pass a number, for the numeric index into the record set. Typically I find the name to be easier to use.

While JDBC is a good starting point, there are other libraries out there that can help with putting together your application, such as Spring, Hibernate, etc which provide methodologies for quickly merging your SQL data into Java objects.

Java Database Connectivity was originally found on Access 2 Learn