Java Database Access Lab Page 1

Using JDBC
In this lab you will create an application to access the World database using the Java
Database Connectivity (JDBC). We will use MySQL, but the same code will work with
other databases.

Required Software
1. Java SE 1.6.0 SDK. Java version 1.5 will also work.
2. A JDBC connector for your database. For MySQL, this is mysql-connector-java-bin-
5.1.7-bin.jar. The JDBC JAR file belongs in a subdirectory of your software library, e.g.
C:\lib\mysql or /opt/lib/mysql (Linux). Don't bury it in C:\Program Files.
3. A database query tool to check your database schema and verify results.
4. Software development tool, such as Eclipse, NetBeans, or a text editor.
At K.U. download these from: http://se.cpe.ku.ac.th/download/mysql

Goals
1. Write a CityBrowser application to find a city in the World database and display information.
2. Create a graphical UI for CityBrowser.
3. Separate the database operations from the rest of the code by designing a Data Access Object
(DAO).
4. Use a Properties file to separate database properties from our Java code (like user name and
password).

The World Database

World is a sample database provided by MySQL. The data is from the 1990s, but it's still useful.
There are 3 tables as shown below. It's not a great database design: the Continent attribute is an
enumeration of Strings, the Region is a String, and Language is a String.

country city

Code ID
Capital CountryCode
Code2 District
Continent Name
GNP Population
LifeExpectancy
LocalName
Name
Population countrylanguage
Region
SurfaceArea CountryCode
Language
isOfficial
Percentage
Java Database Access Lab Page 2

1. Configure a Project
In your favorite IDE, create a new Java project named World.
 inside the "src" tree, create a "world" package. This will be the base package for our app.
 add the MySQL JDBC connector to the project as an "External JAR" or library.

An Eclipse project would have this structure:

World/ the project base directory
src/ source directory
world / base package for our source code
bin/ generated class files. In NetBeans
... this is usually named "classes".
Referenced Libraries/
mysql-connector-java-5.1.7.jar
Java Database Access Lab Page 3

How JDBC Works

To use JDBC you first create a connection to a database, using a Connection object. Connection
objects are specific to the type of database, e.g. MySQL Connection, Oracle Connection.
Use the Connection object to create a Statement. Statement is a reusable command object that
you use to execute SQL commands and get the results. You can specify attributes for a Statement
(such as how many results it can hold). Connection can also create another kind of Statement
called a PreparedStatement, which has better performance and security.
SQL "SELECT" queries return results as a ResultSet object, which lets you iterate over rows in the
result. A ResultSet also contains metadata you can use to discover information about the results.

Database

Connect and authenticate

execute

How does DriverManager know what database to use?

The first parameter to DriverManager.getConnection( ) is a URL.
The url parameter identifies the location of the database server and the kind of database (DB2,
MySQL, etc). Here are some examples of URLs:
jdbc:mysql://se.cpe.ku.ac.th/world MySQL database on a server
jdbc:mysql://localhost:3306/world MySQL database on this host
jdbc:derby:/database/world Derby database on this host
jdbc:hsqldb:file:/database/world HSQLDB database on this host

The URLs for Derby and HSQLDB are for embedded mode.

The general format of a URL is:

protocol:subprotocol://host:port/database
The protocol is "jdbc", the subprotocol identifies the type of database. You can omit the
host or port to use the default values. Default host is usually localhost.
Java Database Access Lab Page 4

2. Create a Database Connection

There are two ways to create a database Connection object:
(1) use DriverManager - your app creates a database connection
(2) use a DataSource - your app uses a connection managed by something else
DriverManager is typical for stand-alone applications and simpler to set-up.
A DataSource is preferred when you want a framework or web container to manage the data
source. DataSource uses JNDI to remove details of the data source from your application and may
enable connection pooling.
DriverManager has an overloaded getConnection method to create a database connection:
1. Specify a url, database username, and password as strings.
connection = DriverManager.getConnection( url, username, password );
2. Specify a url and a Properties object for other connection properties.
connection = DriverManager.getConnection( url, properties );

Exercise: Create Connection and Statement object

Create a connection to the world database in the CPE department at KU.
Then, inspect the connection and statement to discover how the database driver is used.
1. In the world package of your application, create a class JdbcDemo.java (any name is OK).
2. Enter this code to create a connection and statement.
import java.sql.*; // DON'T import com.mysql.jdbc !!
public class JdbcDemo {
static String url = "jdbc:mysql://se.cpe.ku.ac.th/world";
static String user = "student";
static String passwd = "secret";

public static void main(String [] args) {

// Parameters are: getConnection( database, user, password )
Connection conn = DriverManager.getConnection( url, user, passwd);
Statement statement = conn.createStatement( );
// what is the actual class of Connection and Statement?
System.out.println("Connection type is " + conn.getClass() );
System.out.println("Statement type is " + statement.getClass() );
}

3. You will notice that the JDBC methods throw SQLException. Add a throws declaration to
main: (In a real project, we would catch the exception and report it.)
public static void main(String [] args) throws SQLException {
Java Database Access Lab Page 5

4. Run the class. You may get this exception:

java.sql.SQLException: No suitable driver found for jdbc:mysql://...
DriverManager couldn't find a JDBC driver for MySQL.
We can "load" the MySQL driver at runtime so that DriverManager can find it. Add this hacky
code to your application. Later, you will see how to avoid this.
public static void main(String [] args) throws SQLException {
try {
Class.forName( "com.mysql.jdbc.Driver" );
} catch (ClassNotFoundException ex) {
System.out.println("driver not found");
}

Class.forName( ) causes the class to be loaded at runtime.

Run the application again.
If you get a ClassNotFoundException, it means that the mysql-connector-5.1.X-java.jar file
isn't on your application classpath. Check your IDE project configuration. Make sure the JAR file
(not the ZIP file) is added to the project as external JAR or library.
You should get output like this:
Connection type is com.mysql.jdbc.JDBC4Connection
Statement type is com.mysql.jdbc.StatementImpl

Discussion
What does the output tell you?
We asked DriverManager for a java.sql.Connection, but we got an object of type
com.mysql.jdbc.JDBC4Connection.
Why?

JDBC Drivers and Class Names

Database Driver JAR file Driver Class Name

MySQL mysql-connector- com.mysql.jdbc.Driver
java-5.X.Y.jar
Derby embedded derby.jar org.apache.derby.jdbc.EmbeddedDriver
mode
Derby client- derbyclient.jar org.apache.derby.jdbc.ClientDriver
server mode
HSQLDB, any hsqldb.jar org.hsqldb.jdbcDriver
mode
For HSQLDB and Derby the URL format is different for embedded and client-server mode.
Database Client-server mode (URL used by client) Embedded mode
Derby jdbc:derby://hostname:port/path/ jdbc:derby:D:/path/database
database
Java Database Access Lab Page 6

HSQLDB jdbc:hsqldb:hsql://host:port/database jdbc:hsqldb:file:/path/database

Java Database Access Lab Page 7

3. Perform a Database Query and View Results

You use a Statement object to perform database commands. To issue an SQL SELECT, we'll use
the Statement.executeQuery( string ) method.
We will query the city table for all cities named "Bangkok" (use any name you like).
// compose the SQL we want to use
String query = "SELECT * FROM city WHERE name='Bangkok'";
ResultSet rs = statement.executeQuery( query );
// The ResultSet is never null. The next() method iterates over results.
while ( rs.next() ) {
String name = rs.getString("name");
String district = rs.getString("district");
String country = rs.getString("countrycode");
int population = rs.getInt("population");
System.out.printf("%s, %s, %s pop. %d\n",
name, district, country, population);
}

Exercise
1. Add the above code to your application and run it.
2. Modify this code to ask the user for a city name instead of "Bangkok". A city name may contain
spaces, so you shown read an entire input line as city name. Also trim excess space from the
input.
Example:
City name to find? Los Angeles
Matching cities:
Los Angeles, Bobo, CHL pop. 158,215
Los Angeles, California, USA pop. 3,694,820
City name to find? Pattaya
Sorry, Pattaya not found.
City name to find?
Java Database Access Lab Page 8

4. Design Patterns in JDBC

JDBC uses many interfaces, as shown here. Each database driver provides it own classes to
implement these interfaces.

com.mysql.jdbc

1. A ResultSet lets us iteratively access each row of a query result, without knowing how the
results are stored. What design pattern is this?

2. In the Adapter Pattern, an Adaptor is used to convert from one interface (the interface used by
existing software) to another interface (the required interface). How is adapter used here?

3. An Abstract Factory is an abstract class (or interface) that defines methods for creating a family
of related products. A concrete implementation of the factory creates the actual products. Which
JDBC class is an Abstract Factory?
What methods create the products?
Java Database Access Lab Page 9

5. More about ResultSet

ResultSet is a "live" connection to rows in a database table. You can use ResultSet to read, test,
and modify contents of a database. ResultSet methods that "get" data have 2 forms:
1) Get data by field number (first field in number 1, not 0):
String name = rs.getString( 2 ); // get 2nd field as a string
2) Get data by field name:
int population = rs.getInt( "population" ); // get field by name

ResultSet also has methods to test or change the current position in the results.

Read the Java API doc for ResultSet and write the method names in this table:

Method Name Description

Test if the current position is before the first row in ResultSet.
Returns false if the ResultSet is empty
first( ) Move the current position to the first row of data in ResultSet.
Returns true if successful. Returns false if there are no results.
Test if there are more results in ResultSet.
If true, move current position to the next result.
Test if the current position is the last result in ResultSet.

Close the ResultSet and release all its resources.

Important: you should close a ResultSet when you finished using
it to free resources.
ResultSet doesn't have a "hasNext" method.

Exercise
1. Modify JdbcDemo so that it prints a "city not found" message when the user's city isn't found in
the database.
Hint: the first( ) method returns true and moves the ResultSet cursor to the first item.
You should also change the "while" loop to a "do - while" loop, because if you call first() and
then call next()it will skip the first record!
Example:
City name to find? Pattaya
Sorry, no city named Pattaya
City name to find?
Java Database Access Lab Page 10

6. Other Kinds of SQL Commands

You can use a Statement to execute any SQL command. Statement has methods for the different
kinds of SQL commands and getting the results. We just saw how to perform a query.

Command Statement Method Example

SELECT executeQuery("select ...."); ResultSet rs = stmt.executeQuery(
"SELECT * FROM city);
INSERT, executeUpdate("sql command") int count = stmt.executeUpdate(
UPDATE, "DELETE FROM city WHERE
DELETE name='Bangsaen'")
INSERT executeUpdate("sql command", Perform INSERT and make auto-
Statement.RETURN_GENERATED_KEYS) generated keys available. Useful for
DAO that must set a key value in object
it saves.
ANY execute("sql command") boolean results = execute(
"CREATE TABLE countryflag ...")

The Statement class has many useful methods for getting information about results of database
operations. For example, when we INSERT a new City the database assigns the City an ID. What
is the ID? We can use Statement methods to get it.
Statement statement = connection.createStatement( );
statement.executeUpdate(
"INSERT INTO city VALUES('Bangsaen','Chonburi','THA',25000)",
Statement.RETURN_GENERATED_KEYS);
ResultSet rs = statment.getGeneratedKeys( );
if ( rs.first() ) {
int id = rs.getInt( 1 );
//TODO: assign id to the city object
System.out.println("Created City with id "+id);
}
A PreparedStatement can also return generated keys, and PreparedStatement is faster and safer
for most tasks.
Java Database Access Lab Page 11

7. Security and SQL Injection

In JdbcDemo, can the user insert his own SQL command into our application?
Our application takes the user's input and inserts it into an SQL SELECT statement that we execute
on the server. A malicious user can take advantage of this. Here is how.
1. Run JdbcDemo. Enter this input (exactly as shown). Be sure to input single quotes.

City name to find? Pattaya' OR 'a'='a

2. What is the output?

Exercise
1. (Discussion) Explain what happened.
2. Be the hacker. What should you input to display all the cities in China?
Hint: the countrycode for China is CHN. Can you inject it?
3. Can you use SQL injection to delete data from the City table?

Discussion
This is called SQL Injection and it is a common form of hacking. The group Lulz Security hacked
into the Sony Pictures customer database (twice!) and stole data for millions of customers.
They published the data on the Internet and posted this message:
"SonyPictures.com was owned
(hacked) by a very simple SQL
injection, one of the most
primitive and common
vulnerabilities, as we should all
know by now.
From a single injection, we
accessed EVERYTHING.
Why do you put such faith in a
company that allows itself to
become open to these simple
attacks?"

Reference:
http://www.bbc.co.uk/news/business-13636704
Java Database Access Lab Page 12

8. Using a PreparedStatement
A prepared statement is an SQL command that is pre-compiled rather than interpreting the SQL
during execution. A prepared statement can contain placeholders (?) where you insert values
before executing the statement. Use Connection to create a Prepared Statement.
For example, to find all cities having a given countrycode:
PreparedStatement pstmt = connection.prepareStatement(
"SELECT * FROM city WHERE countrycode=?" );
// insert a value for countrycode into PreparedStatement
pstmt.setString( 1, "THA" ); // replace the 1st ? with string "THA"
ResultSet rs = pstmt.executeQuery( );

We specify the SQL SELECT query when we create the prepared statement. The ? in the query is
a placeholder where you can insert a value later. Do not put quotes around ?, even if the value will
be a string. The PreparedStatement will take care of that.
The first ? in a prepared statement is parameter 1, the second ? is parameter 2, etc.
To add a new city to the database, with name, district, and population, we would need 3
placeholders:
PreparedStatement pstmt = connection.prepareStatement(
"INSERT INTO city(name,district,population) VALUES(?,?,?)" );
// insert values into prepared statement
pstmt.setString( 1, "Santa Village" );
pstmt.setString( 2, "North Pole" );
pstmt.setInt( 3, 101 ); // populaton of 100 Elves + Santa Claus
int count = pstmt.executeUpdate( );

Since population is an integer value, we use setInt( ) to set the value of placeholder #3.
The executeUpdate() method is used to perform INSERT, UPDATE, and DELETE
commands. It returns the number of rows affected.
PreparedStatement has some advantages over a Statement:
1. It is executed more efficiently than a plain Statement, since the SQL is pre-compiled.
2. It avoids hacking via SQL Injection since the SQL is compiled before inserting user data.

Exercise
1. Modify JdbcDemo to use a PreparedStatement instead of Statement.
2. Try SQL injection again. When asked for a city name, input: Pattaya' OR 'a'='a
What happens?
Java Database Access Lab Page 13

9. Design a CityBrowser
Now that you know how JDBC works, we will design an application to search for cities and display
information. The application should have a graphical user interface, and be reconfigurable. For
example, we should be able to change the database location or user password without changing our
code.

Applying Software Design Principles

We will design the application using some common design principles:
1. Single Responsibility Principle - each class should have a single purpose.
2. Separation of Concerns - separate parts of the application that are concerned with different kinds
of behavior or services.
3. Separate what may change and encapsulate it (Protected Variations) - try to identify the parts of
the program may need (or want) to change and design so it is easy to make changes.
How? Interfaces, dependency injection, and configuration files are ways to make code easy to
change.

Exercise
1. Identify kinds of things and responsibilities in the CityBrowser. Write them here.

Things (Objects) Responsibilities or Behavior needed

City knows its name, country, population, etc.
User Interface accept user input, display results
database connection manage the connection to database. Open a
connection when needed. There should only be
one connection.
manage database connection Create connection, provide access to it

2. What parts of the application might need to change?

user name and password for database connection
database server host or the database driver software

3. Define classes for our application and assign responsibilities.

Java Database Access Lab Page 14

10. Layered Design

It is helpful to visualize software design as layers. Layers are defined by the type of services they
provide, and the level of abstraction. Layers are also designed to minimize coupling between layers.
Lower layers provide services to upper layers. With good design, you can reuse code from lower
layers, and replace code inside a layer without affecting the other layers.
It is common to separate domain objects and domain logic from the database services. A database is
just one way to store or persist data in our application, so the database layer is often called a
Persistence Layer. Data access objects separate the domain layer and persistence layer, so the
domain is unaware of how persistence is done.
Application Layer provides the logic and
functionality for our application.

Domain objects represent things in our No SQL or ResultSet here!

application domain.

May use SQL, but does not

Persistence services handle saving and
depend on a particular type of
retrieving data as objects.
database, such as MySQL.

Object Design
The domain layer contains objects and logic related to our application. City and Country classes
belong here.
The persistence layer contains data access objects (DAO) that provide services for accessing
persistent data. A DAO converts data to/from objects (for our application) and external form.
DAO provide 4 basic services referred to as CRUD:
Create save new object data to the database.
Retrieve find object data in the database and recreate objects. There may be several
methods for this service to enable different forms of object lookup.
Update update data for an object already saved to the database
Delete delete object data from the database
We will design one DAO for each kind of object to save. For example:
CityDao - create, retrieve, update, delete City objects
CountryDao - create, retrieve, update, delete Country objects

The persistence service is provided by JDBC. We will add our own connection manager class to
handle creation of a singleton Connection object. This hides details so we can easily change it. An
application usually needs only one connection to the database.
Java Database Access Lab Page 15

11. Implementation
The CityBrowser will use these classes.
City - a plain old Java object (POJO) with default constructor and
get/set methods for all attributes. The countrycode doesn't belong in
the City class -- we will change that to be a Country reference later.
The id attribute is an Integer (not int) so we know which Cities
haven't been saved in the database. Unsaved cities have a null id.
The database assigns an id when a City is saved.

CityDao - a data access object for City objects. Since this class depends
on the others, write this last.

ConnectionManager - provides access to the database Connection and

ensure there is only one connection. In long running applications (like
web apps), you may require ConnectionManager to close and re-open
the connection.

12. Implement City

City is a POJO with attributes for the city properties. It should have:
 a public default constructor,
 a reference type variable for the key attribute (id) rather than a primitive datatype.
 get/set methods for the saved attributes (actually, "set" methods aren't always required).
And may be required:
 equals and hashCode methods that identify when two objects refer to same entity.
 be a non-final class

Exercises
1. Create a package named world.domain
2. Implement the City class in the world.domain package with attributes as shown.
3. Use Eclipse's code generation feature to write a default constructor and get/set methods for all
properties:
 Source -> Generate Constructor from Superclass... to generate constructor
 Source -> Generate Getters and Setters... for get/set methods
4. Write a toString() method to return a useful description of the City.
Java Database Access Lab Page 16

13. Implement ConnectionManager

Here's an example ConnectionManager that provides access to a single connection object. It uses
lazy instantiation to create the connection only when it is needed.
Coding values of url, user, and password as String constants is bad design, which we will fix
later.
public class ConnectionManager {
private static String url = "jdbc:mysql://hostname/world";
private static String driver = "com.mysql.jdbc.Driver";
private static String user = "student";
private static String password = "secret";
/** singleton Connection object */
private static Connection connection = null;

/** private constructor to prevent creating objects of this class */

private ConnectionManager( ) { /* nothing to initialize */ }
/**
* Get the database connection.
* @throws SQLException if connection cannot be established
*/
public static Connection getConnection( ) throws SQLException {
if (connection == null) try {
Class.forName( driver );
connection = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException ex) {
throw new SQLException( ex );
}
return connection;
}
}

Questions
1. Why is the ConnectionManager constructor private?

2. What is the purpose of the try/catch block? Since getConnection() throws SQLException, why
do we need try - catch??? Why does catch throw a new SQLException?
Java Database Access Lab Page 17

14. Implement a DAO for City

A data access object for the City class provides the CRUD operations that map City objects into
database rows. It hides database details from the rest of the application. A City Dao might look
like this:

CityDao
find( id: Integer ): City
findByName( String ): City[*]
delete( city: City )
save( city: City ): boolean

What about "update"? There isn't an update method in this CityDao diagram.
You can write a separate update method or use the save method for both "create" and "update".
A DAO can contain many "retrieve" or "find" operations, depending on what is required by the
application. A general findBy( fieldname, value ) is very common.

Exercises
1. Implement the findByName method of CityDao now.
package world.dao;
//TODO add imports and class comment

public class CityDao {

public List<City> findByName(String name) {

List<City> results = new ArrayList<City>();
if (name == null) return cities;
try {
PreparedStatement pstmt = ConnectionManager.getConnection()
.prepareStatement( "SELECT * FROM City WHERE name=?" );
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery( );
while ( rs.next() ) results.add( resultToCity(rs) );
rs.close();
} catch (SQLException sqle) {
throw new RuntimeException( sqle );
}
return results;
}

findByName uses a PreparedStatement instead of Statement to protect against SQL Injection

(but may not prevent other hacks).

resultToCity( ) copies ResultSet data into a City object.

Java Database Access Lab Page 18

/**
* Copy current ResultSet row into a City object.
* @param rs ResultSet with a current row
* @return a City object containing resultSet data for one row
*/
private City resultToCity( ResultSet rs ) {
City city = new City();
try {
city.setName( rs.getString("name") );
city.setDistrict( rs.getString("district") );
city.setPopulation( rs.getInt("population") );
city.setCountrycode( rs.getString("countrycode") );
city.setId( rs.getInt("id") );
} catch (SQLException sqle) {
throw new RuntimeException( sqle );
}
return city;
}

2. Copy your JdbcDemo to a new class named CityBrowser (or just modify JdbcDemo).
Modify the code to use the CityDao to find cities. Here is an example:
Scanner console = new Scanner(System.in);
CityDao dao = new CityDao( );
do {
System.out.print("Name of city to find: ");
String name = console.nextLine().trim();
List<City> cities = dao.findByName( name );

if ( cities.size() > 0 ) {
System.out.println("Matches for "+name);
for( City c: cities) {
System.out.printf("%-20s %-20s %-20s pop %,d\n",
c.getName(), c.getDistrict(), c.getCountry(),
c.getPopulation() );
}
}
else System.out.println("No matches found for "+name);
} while (name.length() > 0);

3. Compare this code to the original JdbcDemo. Which one is simpler?

Which is more object-oriented?
Java Database Access Lab Page 19

15. Using Properties for Database Parameters

The database driver name, url, and login credentials are strings in ConnectionManager.
Exercise: Why this is a bad design.
We'll use a java.util.Properties object to get these values from a text file. Properties is a map of
string keys to values, with methods to read and save the map to/from a file as plain text or XML.
Here is an example properties file with property names used by JDBC drivers.
# World database properties (lines beginning with # are comments)
jdbc.url=jdbc:mysql://se.cpe.ku.ac.th/world
jdbc.drivers=com.mysql.jdbc.Driver
user=student
password=NoWay
useUnicode=true
characterEncoding=UTF-8

Note: The MySQL "Connector/J Reference" section 4.1 lists all the properties for MySQL drivers.
You can use Properties to store configuration information for your application, too.

Exercise
Use the System properties to display info about the operating system. In BlueJ you can do this
using the Codepad. In Eclipse you can use a Scrapbook instead of writing a class.
// Example using java.util.Properties
Properties prop = System.getProperties( );
System.out.println( "Your OS is " + prop.getProperty("os.name") );
// display all the properties
prop.list( System.out );

Exercise: Create a world.config properties file

In your application src directory, create a new text file named world.config.
Enter the property names and values as shown on previous page:
world/
src/ # World database properties
world.config jdbc.url=jdbc:mysql://se.cpe.ku.ac.th/world
jdbc.drivers=com.mysql.jdbc.Driver
user=student
password=NoWay
Java Database Access Lab Page 20

16. Create a PropertyManager for the World App

Providing access to the properties is a new responsibility. Other parts of our application may also
use properties, so we should create a separate class to manage properties. An application has only
one set of properties; hence PropertyManager can be a singleton or use static methods.

Our application will call getProperties() to get the properties object. For convenience, you may
also provide a getProperty(key) method to get the value of a single property.
To read properties from a text file use the method properties.load(InputStream).
Opening files in Java is a problem because the "current directory" may not be what we expect. Or,
the configuration file may be bundled inside a JAR file. A common solution to the problem of
opening input files is this:
Open the file by name as a FileInputStream
(this will work if the file location is relative to the "current" directory
or is an absolute path)
If that fails then
Use the ClassLoader to open the file as a Resource
ClassLoader has a useful method named loadResourceAsStream( String name ) that searches
for resources anywhere on the CLASSPATH, which can include files packed in a JAR file. It
returns an InputStream object.

String filename = "world.config";

// get the classloader for some class in your application
ClassLoader loader = PropertyManager.class.getClassLoader( );
InputStream instream = loader.getResourceAsStream( filename );
Java Database Access Lab Page 21

Exercises

1. Create a new package named world.service.

2. Create a PropertyManager class in world.service with methods as in the UML

diagram. If you don't like static methods, you can modify PropertyManager to be a Singleton
with instance methods.
Here is an example loadProperties method for PropertyManager:

public class PropertyManager {

private static String propertyFilename = "world.config";
private static Properties properties = new Properties(); // empty

static {
loadProperties( propertyFilename );
}
/** Load properties from a file or resource by name.
* @param filename file or resource name
* @return true if successful
*/
private static void loadProperties(String filename) {
InputStream instream = null;
// first open as a file relative to "current" directory
try {
instream = new FileInputStream( filename );
} catch ( Exception e ) { /* try again */ }

if (instream == null) {
ClassLoader loader = PropertyManager.class.getClassLoader();
instream = loader.getResourceAsStream(filename);
}
if (instream == null) return;
// remove old property values first
properties.clear();
try {
properties.load(instream);
} catch (IOException e) {
System.out.println("Error reading properties file "+filename);
}
// close input stream to free resources
try {
instream.close();
} catch (IOException ioe) { /* shouldn't happen */ }
}

3. Write the getProperties( ) and getProperty(String key) methods of PropertyManager. Since

the properties were already loaded by the static block, just return them.
/**
* Get the value of a property.
* @param key is the name of the property to get. Must not be null.
*/
public static String getProperty(String key) {
return properties.getProperty(key);
}
Java Database Access Lab Page 22

17. Use Properties in ConnectionManager

Modify ConnectionManager to get connection information (url, driver, etc) using Properties.
Delete all the string constants (url, user, password) we wrote before.
The DriverManger class has a getConnection method that accepts a Properties object for all
connection parameters except the url. Pass the entire properties object to getConnection:
DriverManager.getConnection( "url", properties );
DriverManager even uses the "jdbc.drivers" property to load the driver class for you.
if (connection == null) {
Properties props = PropertyManager.getProperties( );
// get the url value
String url = props.getProperty( "jdbc.url" );
Connection connection = DriverManager.getConnection( url, props );
}

Now you don't need any string constants in ConnectionManager !

Note: The MySQL Connector/J Reference, section 4.1, lists all the property keys that the MySQL
driver accepts. The user, password, and jdbc.drivers keys are standard and are
recognized by most JDBC drivers.

TODO: Add a table of common JDBC properties.

Java Database Access Lab Page 23

18. Graphical User Interface

Write a graphical user interface using a layered design.
A key point is to separate application logic from UI logic. The UI should not try to invoke DAO
services directly. Write a controller class to perform queries for the UI. This looks like overkill for
a simple app, but it helps for reusing logic in a web application.
One design would be use a JTable with a TableModel to adapt the query results for JTable.

Its easy to write a TableModel by extending AbstractTableModel. Your TableModel need only
implement a few methods that are specific to your data. These are the number of rows and columns,
column names, and the cell values in the JTable.
Here's a TableModel that provides data from a List of City objects. (Some code omitted for
brevity).

public class CityTableModel extends AbstractTableModel {

/** the city data to show in JTable */
private List<City> cities;
/** the column names to show in JTable */
private static final String [] FIELD_NAMES =
{ "Name", "District", "Country", "Population" };

public CityTableModel( ) {
cities = new java.util.ArrayList<City>(); // to avoid NullPointer
}

public String getColumnName( int column ) {

if (column < FIELD_NAMES.length) return FIELD_NAMES[column];
return ""; // unknown column
}

public int getColumnCount( ) {

return FIELD_NAMES.length;
}

public int getRowCount() {

return cities.size( );
}

public Object getValueAt( int row, int col ) {

Java Database Access Lab Page 24

if ( row >= cities.size() ) return "";

City city = cities.get(row);
switch( col ) { // this is hacky. Use Reflection.
case 0: return city.getName();
case 1: return city.getDistrict();
case 2: return city.getCountry();
case 3: return city.getPopulation();
default: return "";
}
}

We need a way to update the city data in the CityTableModel after performing a query, and to
force JTable to update its view of the table. Invoke the fireTableChanged method (inherited from
AbstractTableModel) to force JTable to update the view.
/** Set the list of cities to display in table.
* @param cities list of City data to display. Must not be null.
*/
public void setCities( List<City> cities ) {
assert cities != null;
this.cities = cities;
fireTableDataChanged();
}

This use of TableModel is one situation where our code would be simpler if the view had direct
access to the ResultSet instead of City objects. That's because a TableModel matches the structure
of a database table. Using ResultSet metadata you can write a TableModel that can display data for
any database table! (I have a worksheet for this.)
Java Database Access Lab Page 25

19. Write a save(City) method for CityDao

CityDao needs methods to save a city to the database and delete a city from the database. When
we save a new city to the database, the database will generate an id for the city. A pseudocode to
save a city is:
save(City city)
create a PreparedStatement with an SQL INSERT command
set values of the PreparedStatement parameters using city data.
execute the PreparedStatement
get the id generated by the database for the city we just saved
set the id in the city object

Exercises
Write the save(City city) method using the pseudocode. Your code should handle SQLException.

1. Create a PreparedStatement, with ? as a placeholder for data values.

String sql = "INSERT INTO city (name,district,countrycode,population)"
+ " VALUES(?,?,?,?)";
PreparedStatement pstmt =
connection.prepareStatement( sql, Statement.RETURN_GENERATED_KEYS);

The parameter Statement.RETURN_GENERATED_KEYS is so we can get the City id value

that the database generates and assigns to a new city row.

2. Set values of the PreparedStatement placeholders (?) using values from the City object.
pstmt.setString( 1, city.getName() );
//TODO set the other parameters using data from city

3. Execute the PreparedStatement and save the result count. If successful, the count will be 1.
int count = pstmt.executeUpdate( );

4. Assign the id value to the City object you just saved. This is how our application knows which
City objects have been saved and which are "transient". How can we discover the id value? The
database generates the id itself.
PreparedStatement has a getGeneratedKeys() method that returns auto-generated values, like
the city id. It returns a ResultSet. In this case, we know the ResultSet has only one generated
value, so we can get the field using index number (1).
if ( count > 0 ) {
ResultSet rs = pstmt.getGeneratedKeys( );
rs.first(); // move to first row of ResultSet
int id = rs.getInt( 1 ); // or use column name "GENERATED_KEY"
city.setId( id );
rs.close( );

5. Test your code. Write some code to create a city object and save it in the database.
After saving a city, does the city object have an id assigned?
Java Database Access Lab Page 26

Assignment

Save or Update?
1. We should only save (INSERT) a City object in the database if the City hasn't been saved
already. Modify the save(City) method to check that the id is null before saving the data as a new
city.
If the city id is not null, then UPDATE the data for existing city (use SQL UPDATE instead of
INSERT). In this example,we use a separate update(City) method for this:
public void save(City city) {
if (city.getId() != null) update( city );
2. Write a delete(City) method that deletes a city from the database, using the id. Its an error
to call delete(city) with a city object that has not been saved (id == null). After deleting the
city object from database, set the city object's id to null.

Unique Objects (harder, but really important)

We should only create one object for one row in the database.
City city1 = cityDao.findByName("Bangkok").get(0); // only one Bangkok
Integer id = city1.getId();
City city2 = cityDao.find( id ); // also Bangkok
if ( city1 == city2 ) // should be true! Only one object for one row in database

3. Modify the CityDao so that it always returns the same object for a given City, no matter how you
find the city.
Here is one solution (but not perfect). In CityDao keep a Map of id → city for all the cities that the
CityDao has retrieved (findByName) or saved. This Map is a "cache" of persistent city objects.
Methods that return City objects (find, findByXXX, query) should check the cache before creating
a city object. Similarly, save(City) should add a persisted city to the cache, and delete(City)
should remove a city from the cache.
class CityDao {
private static Map<Integer,City> cache = new HashMap<Integer,City>( );

In the case of creating City objects from a ResultSet, we can modify the resultToCity() method to
check the cache:
private City resultToCity( ResultSet rs ) {
Integer id = rs.getInt("id");
City city = null;
// if object is already in cache, then use id
if ( cache.containsKey(id) ) city = cache.get(id);
// otherwise use a new object
else city = new City();
// otherwise, create a new city from ResultSet
... original code goes here
if ( ! cache.containsKey(id) ) cache.put(id, city);
return city;
}

O-R Mapping frameworks do this for you. Coding it yourself will help you appreciate the issues
involved.
Java Database Access Lab Page 27

Keeping a cache creates a new performance issue: how to free objects from the cache when we are
done using them? If not removed from the cache, unused objects will never be garbage collected.
O-R frameworks have a method named attach( obj ) or merge( obj ) to add an object to the cache,
and detach( obj ) remove an object from the cache without deleting object from the database.

References
Sun Java Tutorial has a trail for JDBC.