How to Create Relational Database With Java

The Java Database Connectivity (JDBC) architecture is based on a collection of Java interfaces and classes that together enable you to connect to data sources, to create and execute Structured Query Language (SQL) statements, and to retrieve and modify data in a database. These operations are illustrated in the figure below:

Each of the boxes in the illustration represents a JDBC class or interface that has a fundamental role in accessing a relational database. All your work with JDBC will begin with the DriverManager class, which is responsible for establishing connections to the data sources, accessed through the JDB drivers.

JDBC database drivers are defined by classes that implement the Driver interface. A JDBC driver understands how to convert SQL requests for a particular database. Without an appropriate driver, we cannot connect to the database in the first place, and JDBC is one of the few areas of the Java programming language that depends on vendor-specific products - each Relational Database Management System (RDBMS) has a specific set of drivers that can be used to communicate with that database. Therefore, one of the first things your program must do is to load the appropriate driver that will allow your Java classes to communicate with your particular database.

A database is a collection of related data. By data, we mean known facts that can be recorded and that have implicit meaning. A Relational Database Management System (RDBMS) is a collection of programs that enables users to create and maintain a database.

Driver Manager loads database drivers, and manages the connections between the application and the driver. The driver translates Application Programming Interface (API) calls into operations for specific data source.

Application Programming Interface (API) is an interface that application programs use to access the network subsystem (usually the transport protocol). Usually OS-specific.

http://www.peerdirect.com/products/index.php

Java is a versatile, robust programming language that is adaptable to a range of different environments. The Java Database Connectivity (JDBC) makes it very easy to write code to access relational database by providing a whole range of Java classes and interfaces that encapsulate specific functionality.

Encapsulation is a mechanism that associates data with the code that manipulates it. Encapsulation allows you to more easily modify the form of the data that is used to store information.

We can describe a basic Java Database Connectivity (JDBC) program as involving the following steps:

Import the necessary classes
Load the JDBC driver
Identify the data source
Allocate a Connection object
Allocate a Statement object
Execute a query using the Statement object
Retrieve data from the returned ResultSet object
Close the ResultSet object
Close the Statement object
Close the Connection object

The JDBC Package is the core JDBC Application Programming Interface (API), which contains all of the classes and interfaces we need for accessing data stores, is provided in the java.sql package, which forms part of Java's class library in the same manner as java.io, java.util, and so on. Thus, it is included in the current release of the Java Development Kit (JDK), Standard Edition, which can be downloaded from the Sun Microsystems web site http://wwws.sun.com/software/download/.
Download Java(TM) Web Services Developer Pack 1.2
Frequently Asked Questions about Downloading

Java(TM) Web Services Developer Pack 1.2

Download jwsdp-1_2-windows-i586.exe .
Filesize = 47,335,742 bytes.

Making a Connection

Connection is a session between the application and a database. Statement is a Structured Query Language (SQL) statement , which performs a query or an update operation. The ResultSet is a logical set of columns and rows of data returned by executing a statement.

We will write a minimal Java Database Connectivity (JDBC) program that creates a Connection object. In this instance, the connection will be established using only the Uniform Resource Locator (URL) for the data source. This is the code to make a connection to Oracle Database:

import java.sql.*;

public class MakingTheConnection {
    public static void main (String[ ] args) {

       // load the driver
       try {

          // load the driver class
          Class.forName ("oracle.jdbc.driver.OracleDriver");

          // define the data source for the driver
          String sourceURL = "jdbc:oracle:thin:@server:1521:databasename";
          String user = "beg";
          String password = "java";

          // create a connection through the DriverManager
          Connection databaseConnection =
             DriverManager.getConnection (sourceURL, user, password);

          // we made it!
          System.out.println ("Connection established successfully!");

          // close connection
          dabaseConnection.close( )
          }

          catch (ClassNotFoundException cnfe) {
          System.err.println (cnfe);
          }

          catch (SQLException sqle) {
          System.err.println (sqle);
          }
       }
   }

How it Works

In the Class.forName( ) method, we now load the class for the Oracle driver:
Class.forName("oracle.jdbc.driver.OracleDriver");

We identify the data source as a sourceURL String:

String sourceURL = "jdbc:oracle:thin:@server:1521:databasename";

Uniform Resource Locator (URL) is a text string used to identify the location of Internet resources. A typical URL looks like - http://www.sunmicrosystems.com. In this URL, http is the protocol to use to access the resource located on host www.sunmicrosystems.com.

We are using Oracle's thin JDBC driver. The @ notation is the information that tells the driver how to find the database. Oracle typically resides on a remote server and not on your own machine (unless you are using Personal Oracle) so the driver needs to be told the name ( or Internet Protocol IP address) of the server on which to look for music store, as an example. The port to which the Oracle JDBC drivers "listen" is 1521. The databasename is used to identify the particular database we want to connect to as our data source.

The above figure represents an Oracle Connection Manager Multiprotocol Support. The Transmission Control Protocol (TCP) is a connection-oriented transport protocol of the Internet architecture. TCP provides a reliable, byte-stream delivery service, which is probably the most widely used protocol. The TCP/IP pair of protocols provides connection-oriented services for higher-layer applications and relies on Internet Protocol (IP) to route packets through the network in order to make those connections. Reliability is one of the most important functions that a network can provide.

Client is a device or program used by computers and based on a client/server model that sends requests to the server. Server is a complementary network device whose function is to respond to requests from the network users recognized as clients.

In Oracle's terminology, a database is simply a collection of files, including the files that actually store the data. In order to get the database up and running it must be mounted and opened by an instance - a set of Oracle process and memory structures.

A class is a template from which objects are created. That is, objects are instances of a class. The mechanism to create a new object is called instantiation. An object is a region of storage that defines both state and behavior.
The Store Class

The Music Store application and database will:

Store details of all recordings in stock and the quantity in stock.
Provide the ability to search the database by a variety of criteria (by artist, song title, album name, and so on). Ultimately, this search facility will be self-service in the store, with a facility for customers to be able to post reviews of recordings.
Store minimal customer information in order that customers may be notified by e-mail or phone when their order arrives.
Provide the ability to keep track of stock in a central database and be able to tell which recordings were in which store.
Provide the ability to track customer information for a customer loyalty discount scheme.

As a developer, your first assigned task is to develop a simple class that will allow the owner to maintain the details of his/her retail outlets.

Implementing the Store Class create( ) Method

// BegJavaDB/connections/Store.java
package connections;

import java.sql.*;

/**
* A class to represent a retail store in the Music system.
*/
public class Store {
int storeId;
String storeDescription = "";
int storeTypeId;
String storeAddress1 = "";
String storeAddress2 = "";
String storeCity = "";
String storePostalCode = "";

Connection connection;
Statement statement;
ResultSet resultSet;

// constructor
public Store() {}

// constructor
public Store(String description, int typeId, String address1,
                 String address2, String city, String postalCode) {
    storeDescription = description;
    storeTypeId = typeId;
    storeAddress1 = address1;
    storeAddress2 = address2;
    storeCity = city;
    storePostalCode = postalCode;
}

private Statement getStatement() throws SQLException {
    connection = ConnectionFactory.getConnection();
    return connection.createStatement();
}

/**
   * Create an entry in the database for this object.
   */
public boolean create() {
    int result = 0;

    try {
      statement = getStatement();
      String sql = "insert into stores " +
                   "(StoreDescription, StoreTypeID, StoreAddress1, " +
                   "StoreAddress2, StoreCity, StorePostalCode) " +
                   "values ('" + storeDescription + "', " + storeTypeId +
                   ", " + "'" + storeAddress1 + "', " + "'" +
                   storeAddress2 + "', " + "'" + storeCity + "', " + "'" +
                   storePostalCode + "')";
      result = statement.executeUpdate(sql);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      close();
    }

    return (result == 1);
}

/**
   * Retrieve the entry for the given store id from the database. Return
   * true if the entry is found. Return false if the entry is not
   * found in the database.
   */
public boolean findByPrimaryKey(String id) {
    if (id == null || id.equals("")) {
      return false;
    }

    boolean result = false;

    try {
      statement = getStatement();
      String sql = "select * from stores " + "where StoreID=" + id;
      resultSet = statement.executeQuery(sql);
      if (resultSet.next()) {
        result = true;
        setStoreId(resultSet.getInt("StoreID"));
        storeDescription = resultSet.getString("StoreDescription");
        storeTypeId = resultSet.getInt("StoreTypeID");
        storeAddress1 = resultSet.getString("StoreAddress1");
        storeAddress2 = resultSet.getString("StoreAddress2");
        storeCity = resultSet.getString("StoreCity");
        storePostalCode = resultSet.getString("StorePostalCode");
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      close();
    }
    return result;
}

void close() {
    ConnectionFactory.close(resultSet);
    ConnectionFactory.close(statement);
    ConnectionFactory.close(connection);
}

public String toString() {
    return "Name     : " + storeDescription + "\n" + "Store Id : " +
           storeId + "\n" + "Address : " + storeAddress1 + "\n" +
           "         : " + storeAddress2 + "\n" + "         : " +
           storeCity + ", " + storePostalCode;
}

public int getStoreId() {
    return storeId;
}
private void setStoreId(int v) {
    this.storeId = v;
}

public String getStoreDescription() {
    return storeDescription;
}

public void setStoreDescription(String v) {
    storeDescription = v;

}

public String getStoreAddress1() {
    return storeAddress1;
}

public void setStoreAddress1(String v) {
    storeAddress1 = v;
}

public String getStoreAddress2() {
    return storeAddress2;
}

public void setStoreAddress2(String v) {
    storeAddress2 = v;
}

public int getStoreTypeId() {
    return storeTypeId;
}

public void setStoreTypeId(int v) {
    storeTypeId = v;
}

public String getStoreCity() {
    return storeCity;
}

public void setStoreCity(String v) {
    storeCity = v;
}

public String getStorePostalCode() {
    return storePostalCode;
}

public void setStorePostalCode(String v) {
    storePostalCode = v;
}
}

How it Works

The class implemented in this program is purely static class. All of these methods are static and so no instance of the class is ever needed. Every method can be referenced by using just the class name as compared to calling a method using a variable:

       // call method using class name
       connection DBConnection = ConnectionFactory.getConnection( );

Even if a caller wanted an instance of the class, no instance can be created because the constructor is private. A static variable is shared by all objects of its class and thus relates to the class itself. A static variable may be declared by using the static keyword as a modifier. When a constructor is declared as private it is with the condition of prevention of access by another class.

When the class is loaded, the virtual machine initializes the static member variable. This variable is initialized by calling the private constructor, and assigning the reference to the variable:

    private static ConnectionFactory ref = new ConnectionFactory( );

Since no instance of the class is actually needed (all the methods are static), the only reason for the static variable is to call the constructor to load the driver; this technique was chosen because it is straightforward and easily comprehended.

Users of the class will call the getConnection( ) method to get a connection to the database. When a class needs to release a resource, it will call one of the close( ) methods. Since the ConnectionFactory class handles all the details of getting the connection, users of the class do not need to know any of the connection parameters. Note also, that we have hard coded the driver name and connection URL. A method is essentially a set of program statements. It forms the fundamental unit of execution in Java. Each method exists as part of a class. During the execution of a program, methods may invoke other methods in the same or a different class.

Using the ConnectionFactory class instead of having connection code in every data class we create, all of our classes will now get a connection from the ConnectionFactory class.

Small JDBC

Java Database Connectivity (JDBC) type 3 drivers are implemented in a three-tiered approach whereby the JDBC database requests are translated into a database-independent network protocol and forwarded to the middle-tier server. The middle-tier server then translates the request to the database-specific native-connectivity interface and passes the request to the database server. If the middle-tier server is written in Java, it can use a type 1 or a type 2 JDBC driver to do this, which means that is very flexible architecturally.

The overall architecture consists of three tiers: the JDBC client and driver, middleware, and the database being accessed: The small JDBC driver (usually few hundred KB in size) executes on the client and implements the logic needed to pass Structured Query Language (SQL) commands over the network to the JDBC server, receive the data back from the server, and manage the connection. Type 3 drivers allow for client deployment on the Internet.

Structured Query Language (SQL) is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, etc.

Resources:

Chang, Ben & Scardina, Mark. (2000). Oracle XML Handbook. The McGraw-Hill Companies.

Graves, Mark. (2002). Designing XML Databases. Prentice Hall PTR, Inc.

Hall, Marty and Brown, Larry. (2001). Core Web Programming (Second Edition). The Sun Microsystems Press Java Series.

Harold, Elliotte R. (2000). Java Network Programming (Second Edition). O’Reilly & Associates, Inc.

Johnson, Celia. (August 2003). Best Free Stuff on the Web. PCWorld Magazine. Volume 21, Number 8.

Lewis and Loftus. (2003). Java Software Solutions (foundations of program design). Third Edition. Addison Wesley.

Navathe, Elmasri. (2000). Elementals of Database Systems (Third Edition). Addison Wesley.

O’Neil, Joseph. (1999). Java. Osborne/McGraw-Hill.

Peterson, Larry & Davie, Bruce. (2000). Computer Networks (Second Edition). Morgan Kaufmann

Siyan, Karanjit S. (1996). Inside Visual J++. New Riders Publishing.

Spell, Brett. (2000). Java Programming. Wrox Press.

From the Web:

http://www-instruct.wccnet.org/~mgalea/

http://java.sun.com/products/jdbc/

http://www.peerdirect.com/products/

http://otn.oracle.com/software/content.html

Biography

I am Guillermo Moreno. I was born in Mexico City, and received my bachelor in Earth Science in 1980 at The National Polytechnic Institute. I completed a second bachelor in Computer Science in 2002 at Eastern Michigan University. Now, I am working on my Secondary Teaching Certificate in the School of Education at EMU, which makes me feel wonderful because I am learning how to teach effectively.

I like swimming, soccer, and tennis. I enjoy playing with my daughters, sharing quality time with my wife. I like dancing Argentine tango, and latin dances in general, as well as reading books and articles related to history, computers, and current affairs. I recently discovered that teaching is something that I love to do and I would like to teach for the rest of my life. Thank you very much.