Working Through This Course
In order to have a thorough understanding of the course units, you will need to read and understand the contents, practice the steps by designing an Information system of your own, and be committed to learning and implementing your knowledge.This course is designed to cover approximately seventeen weeks, and it will require your devoted attention.
MODULE ONE
UNIT 1: Introduction to Information SystemsUNIT 2: Introduction to Basic SQL
UNIT 3: SQL Syntax I
UNIT 4: SQL Syntax II
UNIT 5: More SQL Statements
UNIT 6: Database Programming and JBDC
UNIT 6 Database Programming USING JDBC
Table of Contents1.0 Introduction
2.0 Objectives
3.0 Main Content
3.1 INTRODUCTION TO DATABASE
3.1.1 Definition of Database3.1.2 Classification of Database
3.1.3 Database Management Systems
3.1.4 Relational Database Model
3.2 DATABASE OBJECTS AND CONSTRAINTS
3.2.1 Definition of SQL
3.2.2 SQL Statements
3.2.3 Database Objects
3.2.4 Constraints
3.3 DATABASE PROGRAMMING
3.3.1 Database Programming in Java using JDBC
3.3.2 Accessing the database using JDBC step by step
3.3.3 Using JDBC in the real world
4.0 Conclusion
5.0 Summary
6.0 Tutor Marked Assignment
7.0 Further Reading and Other Resources
1.0 INTRODUCTION
In this unit, you will be introduced to the concept of database programming, which involves knowingwhat a database is, how to construct a simple database. The unit then teaches you how to query the
database using SQL learnt in previous units then writing programs in form of procedures which carries
out specific assignments on the database.
2.0 OBJECTIVES
By the end this unit, you should be able to:• Explain the term database and Database Management Systems (DBMS) in use
• Construct entity relationship diagrams
• Identify the basic database objects
• Program a database using JDBC
• Use JDBC in real world applications.
3.0 MAIN CONTENT3.1 INTRODUCTION TO DATABASE3.1.1 DEFINITION OF DATABASE
A databaseis a structured collection of records or data that is stored in a computer system. A databaseis an organized body of related information that is organized so that it can be easily accessed, managed
and updated. In one view, databases can be easily classified according to types of content bibliographic,
full-text, numeric and images. The structure is achieved by organizing the data according to a database
model. The model in most common use today is the relational model. Other models such as the
hierarchical model and the network model use a more explicit representation of relationships.
3.1.2 Classification of database
In computing, databases are sometimes classified according to their organizational approach and these
can be described as follows;
• Relational database: The most prevalent approach is the relational database. A tabular
database in which data is defined so that it can be reorganized and accessed in number of
different ways.
• Distributed database: A Distributed database is one that can be dispersed or replicated
among points in a network.
• Object-oriented programming database: An Object-oriented programming database is
one that is congruent with the data defined in object classes and subclasses. Computer
databases typically contain aggregations of data records or files, such as sales transaction,
product catalogs and inventories, and customer profiles. Typically, a database manager provides
the capabilities of controlling read/write access specifying report generation, and analyzing
usage. Databases and database managers are prevalent in large mainframe systems, but are also
present in smaller distributed workstations and mid-range systems such as the AS/400 and on
personal computers.
• Structural Query Language (SQL): This is a language for making interactive queries
from and updating a database such as IBM’s DB2, Microsoft Access and database products
from oracle, Sybase and computer associates.
3.1.3 DATABASE MANAGEMENT SYSTEM
A computer database relies upon software to organize the storage of data. This software is known as a
database management system (DBMS). Database management systems are categorized according to the
database model that they support. The model tends to determine the query languages that are available
to access the database. A great deal of the internal engineering of a DBMS, however, is independent of
the data model, and is concerned with managing factors such as performance, concurrency, integrity,
and recovery from hardware failures. In these areas there are large differences between products.
3.1.4 RELATIONAL DATABASE MODEL
The relational database model is a logical representation of data that allows relationship among data to
be considered without concern for the physical structure of the data. A relational database uses relations
or two-dimension tables to store information. A table is the basic storage structure of a Relational
Database Model. A table holds all the data necessary about something in the real world, such as
employees, invoices, or customers. For example, one might want to store information about employees
in a company. In relational database, one creates several tables to store different pieces of information
about a company’s employees, such as an employee table, a department table and a salary table.
A relational database:
• Can be accessed and modified by executing structured SQL statements.
• Contains a collection of tables with no physical pointers
• Uses a set of operators
3.2 DATABASE OBJECTS AND CONSTRAINTS
3.2.1 DATABASE OBJECTS
The commonly used database objects are;
• Table: This is the basic unit of storage; composed of rows
• View: This logically represents subsets of data from one or more tables
• Sequence: Generates numeric values
• Index: This Improves the performance of some queries
• Synonym: Gives alternative names to objects
A tableis the basic storage structure of a Relational Database Model. With views,one can present and
hide data from tables. Many applications require the use of unique numbers as primary key values. One
can either build code into the application to handle this requirement or use a sequence to generate
unique numbers. If the performance of some queries is to be improved, one should consider creating an
index. Indexes can also be used to enforce uniqueness on a column or a collection of columns.
Alternative names can be given to database objects by using synonyms.
3.2.3 CONSTRAINTS
Constraints enforce rules at the table level whenever a row is inserted, updated, or deleted from thattable. The constraint must be satisfied for the operation to succeed. Constraints prevent the deletion of a
table if there are dependencies. The following constraint types are valid;
- NOT NULL: This specifies that the column cannot contain a null value
- UNIQUE: This specifies that a column or combination of columns whose values must be unique
for all rows in the table.
- PRIMARY KEY: This uniquely identifies each row of the table
- FOREIGN KEY: This establishes and enforces a foreign key relationship between the column
and a column of the referenced table.
- CHECK: This specifies a condition that must be true.
3.3 DATABASE PROGRAMMING
It is very essential to pay special attention to database programming because Databases are the heart
and soul of many of the recent enterprise applications. For a better performing database a Database
Administrator (DBA) and a specialist database programmer is needed. If database specialists are not
used during a program development cycle, database often ends up becoming the performance
bottleneck.
An application that does not collate its data in a database is at stake. Programming languages reflect
this trend. That's why most languages provide a robust and flexible library for database access.
Databases can be managed in a programming context by using a database engine called Relational
Database Management Systems. This engine allows an interconnection between programming codes
and the database that keeps the data that is reserved to be used by the codes. Database connections
within the context of programming are of two forms;
• Using a built-in (or an internal) Database Management System: Since most new high level
programming languages come with a built-in DBMS, programmers most times prefer designing the
database and connecting to a database in the programming environment. This reduces the stress the
programmer would have passed through if done otherwise. Some examples of databases that allows
internal database connections are; Java, C#, Visual Basic, etc.
• Using an external Database Management System: External RDBMS are not resident in the
programming environment, thus an external connection is needed in order to connect to them from a
programming environment.
In this section, one of the commonly used Database Management systems will be considered. It is
called the Java Database Connectivity (JDBC).
3.3.1 Database Programming in Java using JDBC
Java provides the Java Database Connectivity (JDBC) API to access different databases. JDBC, which
is short for Java Database Connectivity, can be defined as "An API for the Java programming language
that defines how a client may access a database." In other words, JDBC specifies the ways and methods
that are needed to access a database, more specifically a relational database such as Oracle, MySQL,
and others. The main aspect of JDBC is that it is a specification and not a product. So different vendors
(here RDBMS vendors) provide their own implementation for the specification.
For example, the JDBC implementation for Oracle database is provided by Oracle itself and the same is
the case for all others. The implementations provided by the vendors are known as JDBC Drivers. The
most important point to be kept in mind is that JDBC Drivers are installed at client-side and not at
server-side.
JDBC has been with the Java Standard Edition (JSE) from version 1.1. The latest version is 4 and is
being shipped with JSE 6. Regardless of the version, JDBC supports four types of implementations or
drivers. They are:
1. Type I or JDBC-ODBC Bridge
2. Type II or Partly Java Partly Native
3. Type III or Network Protocol Driver
4. Type IV or Pure Java Driver
The types are defined by how the driver provides communication between the application and the
database server. Here are the details.
A Type I Driver is also called a JDBC-ODBC Bridge. The reason is that in this case JDBC internally
makes calls to the ODBC. It is the ODBC that communicates with the database server. The job of
JDBC is to provide the queries to the ODBC in a form understandable by ODBC and to deliver the
result provided by ODBC to the application in a form that is understandable by the application. This
Driver works mainly with the Windows platform. This is the only type of Driver that is shipped with a
Java installation.
A Type II Driver uses the native API of the target database server to communicate with the server.
Hence it is known as a Native Protocol Driver as well as a Partly Java Partly Native Driver. This Driver
doesn't contain pure Java code as it uses the client-side API provided by the target database server. To
call the client-side API of the database, it uses JNI. However, since it doesn't have the overhead of
calling ODBC, a Type II Driver is faster than a Type I. Also, by using a Type II Driver, one can access
functionalities that are specific to the database server which is being used.
A Type III Driver is also known as Network Protocol Driver. Type III Drivers target the middleware.
The middleware then communicates with the database server. In essence, Type III Drivers are like Type
I with the exception that Type III Drivers are completely written in Java and use the network protocol
of the middleware instead of ODBC API. Type III Drivers are more secure since middleware is in the
picture. In a nutshell, in Type III Drivers the conversion logic is at the middleware level and not at the
client-side.
The Type IV Driveris known as a Pure Java Driver. It is comparable to Type II as it directly interacts
with the database server. Unlike Type II, Type IV doesn't use native API calls. Instead the API has been
written in Java by the vendor. Apart from being a pure Java implementation of database client API, a
Type IV Driver delegates the processing to the database server. That means at client side no processing
related to the database or SQL translation occurs. The only job the client does is connecting to the
server, passing the queries and input to the database server and getting the result back through the same
connection. Due to the delegation of all the processes to the server, the Type IV Driver is also known as
the Thin Client Driver.
The choice of which driver to use depends on the type of application that is being developed. For
example, if the application is web-based, the best option is Type IV as it releases the application server
from being a part of database transactions. In this case the application server would only have to
provide services to look up the name of the connection pool and maintain the pool. All other datarelated operations would be delegated to the database server. Next we will discuss the steps involved in
using JDBC.
3.3.2 ACCESSING THE DATABASE USING JDBC STEP BY STEP
The best part of using JDBC for database programming is that if one has the required type of driver,
regardless of the database server, the steps to connect and query the database remain more or less the
same. The steps to access database for a typical relational database server include:
1. Loading the driver.
2. Creating a connection.
3. Instantiating a statement object.
4. Retrieving a result set object.
5. Accessing the data from the result set object.
All of these steps are the same for any database, be it Oracle or MySQL. The only change comes in the
query to be passed in step four. Here are the details.
Loading the Driver
The driver, regardless of type, can be loaded in one of two ways: using the Class loader, or explicitly
creating the instance. The difference between them, apart from how the driver is instantiated,
is whether the Driver has to be registered explicitly or not.
Using the Class loader
A class can be loaded at runtime using the forName() method of the Class class. The method accepts a
String having the name of the class to be loaded. Also once the class is loaded, calling the
newInstance() method of Class will create a new object of the loaded class. When the forName()
method is used, the Driver need not be registered explicitly. For example, to instantiate a driver of Type
I using the forName method, the statement would be:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Explicitly creating the instance
The second way to load a driver is to instantiate it explicitly using the new operator. This is similar to
that of creating a new instance of any class. However, when the driver is being explicitly instantiated,
one will have to register the driver with the runtime environment using the register() method of the
DriverManager class. For example to load Type I Driver, the statements would be:
Driver driver=new sun.jdbc.odbc.JdbcOdbcDriver();
DriverManager.register(driver);
Or the statements can be merged as:
DriverManager.register(new sun.jdbc.odbc.JdbcOdbcDriver());
Once the driver is loaded and registered, the next step is to get a connection.
Creating a Connection
Once the driver has been loaded and registered, the next step is creating a connection with the database
server. The connection is created when one creates an instance of Connection. To get an instance of
Connection, the getConnection() method of the DriverManager class has to be called. In reality,
Connection is an interface and when getConnection() is called, the DriverManger provides an instance
of the proper implementing class to a reference variable of Connection. There are three forms of the
getConnection() method which are:
getConnection(String url)- the URL contains all the necessary information including the URL
of the database server, user name and password.
getConnection(String url, Properties info) - the URL contains only the URL to the server.
The user name and password are passed as part of the Properties instance.
getConnection(String url, String user, String password) - as with the previous form, this
form also contains the URL to the server in the URL parameter. The user name and password
are passed as separate parameters.
The URL is of the form jdbc:<subprotocol>:subname where subprotocol refers to the protocol used by
the database server and subname refers to the database to which the connection has to be made. For
example, an Oracle subname refers to the tablespace within the database server. So in order to create a
connection using the ODBC data source name or DSN, the statement would be:
Connection connection = DriverManager.getConnection ("jdbc:odbc:test",
"test1", "test123");
where odbc is the subprotocol and test is the DSN which points to the database to connect to. The next
step is to create a statement object.
Instantiating a statement object
A statement represents a query to be executed at the database server against a database. In other words,
a statement object is responsible for executing a SQL query as well as retrieving the result of the
executed query. JDBC provides three types of statements based on the type of query to be executed.
They are: Statement, PreparedStatement, and CallableStatement. They are based on the type of query to
be executed.
Statement is the simplest type that represents a simple query. Its object can be instantiated using any of
the following forms of the createStatement() method of the Connection interface:
·createStatement() -
Returns a Statement object with default concurrency conditions.
·createStatement(int resultSetType, int resultSetConcurrency)-Returns a Statement object with concurrency condition and type of ResultSet according to the values
passed as values. The most commonly used resulSetTypes include
ResultSet.TYPE_FORWARD_ONLY (indicating that the data can be read only in forward direction and
once read cannot be moved back to a previous data) and ResultSet.TYPE_SCROLL_SENSITIVE
(indicating that the data can be read in both forward and backward directions, and that the changes
done by any other operation are visible instantly). The commonly used values for resultSetConcurrency
are CONCUR_READ_ONLY (indicating that ResultSet may not be updated) and
CONCUR_UPDATABLE (indicating that object may be updated).
For example, to create a Statement object that would provide a ResultSet object which is scrollable and
updatable, the statement would be
Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
PreparedStatement conserves resources. Whenever a query is sent to the database server, it goes
through four steps: parsing the query, compiling the query, linking and executing the query. When a
statement object is used to execute a query all four steps are repeated again and again. This can create
resource hogging. The alternative to it is a PreparedStatement object. If a PreparedStatement object is
used, the first three steps are performed only once at the start and in successive calls; the values are
then passed to the linked query and it is executed. To create an object of PreparedStatement, any of the
following forms of prepareStatement can be used:
·prepareStatement(String query)-This form accepts a parameterized SQL query as a parameter and returns an object of
PreparedStatement. "Select * from user where user_id=?" is an example of a parameterized query.
·prepareStatement(String query, int resultSetType, int resultSetConcurrency)-This form is similar to the first form with the added options of specifying whether ResultSets are
scrollable and updatable or not. The values for the two parameters are the same as those described in
the Statement section.
For example, to create an instance of PreparedStatement which provides an updatable and scrollable
ResultSet, the statements would be:
String query= "Select * from user where user_id=?";
PreparedStatement pStatement = connection.prepareStatement(
query,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
To call procedures and functions within a database, one can use CallableStatement. However, if the
underlying database doesn't support procedures and functions, then the CallableStatement object won't
work. For example, versions of MySQL database prior to 5.0 didn't support functions and procedures.
To create an object of CallableStatement, use any of the following forms of the prepareCall() method of
Connection:
·prepareCall(String query)-This returns a CallableStatement object that can be used to execute a procedure or function, which is
passed as the query. The query is of the form "{sum(?,?)}" where sum is the function/procedure to be
called.
·prepareCall(String sql, int resultSetType, int resultSetConcurrency)-To get a ResultSet which is both updatable and scrollable, this form can be used. The resultSetType and
resultSetConcurrency are same as that used with prepareStatement().
For example, to call a procedure whose name is sum, the statement would be
CallableStatement cStatement = connection.prepareCall(
"{sum(?,?)}");
The next step is to retrieve the ResultSet.
Retrieving the ResultSet object
The rows retrieved by the execution of a SQL query are given back by JDBC in the form of a ResultSet
object. A ResultSet contains all the rows retrieved by a query. To retrieve a ResultSet object, one can
call the executeQuery() method of the Statement object. If the Statement object is of the type
PreparedStatement, then executeQuery() without any argument needs to be called. If it is of the type
Statement, then a SQL query will have to be passed to the method. For example, to retrieve a ResultSet
from a Statement for the query "Select * from user", the code would be
ResultSet result = statement.executeQuery("Select * from user");
The next step is to get data from the ResultSet.
Accessing the data from the Resultset object
The specialty of ResultSet is that it can be iterated over as a collection and for each iteration, the data
can be accessed as it is an array using an index. A ResultSet object can be iterated over using its next()
method. During each iteration, one row is retrieved from the number of rows returned by the execution
of the SQL query. The columns within the row can be accessed using different forms of the get()
method of the ResultSet object. The forms depend upon the data-type of the column to be accessed
such as getString() if the column type is varchar, getInt() if the column type is int, and so on. Mostly
getString() is used to retrieve data from the columns. The argument that needs to be passed to the
method is either a string containing the column name or the integer value representing the index. The
index starts from 1 and not 0.
For example, if the user table has a column named "name," then the statements to retrieve the values
for the "name" column would be
while(result.next){
System.out.println(result.getString("name));
}
3.3.3 USING JDBC IN THE REAL WORLD
It is now time to learn how to develop a practical application. An application that implements what has
been explained will be considered in this section;
GenericDAO- Connects to the database and provides a Statement object. It is generic in the
sense that it accepts a driver name and URL as an argument of constructor.
DataOp - Implements database operations.
DAOTest- Tests the DAO and DataOp classes.
So here is the GenericDAO class. It accepts the driver class and URL to connect to as constructor
arguments along with the user name and password.
package jdbctest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class GenericDAO
{
Connection connection;
Statement statement;
public GenericDAO()
{
connection=null;
statement=null;
}
public GenericDAO(String driverClass,String connectionURL,String user,String password)
{
try
{
Class.forName(driverClass).newInstance();
connection=DriverManager.getConnection(connectionURL,user,password);
statement=connection.createStatement();
}
catch (InstantiationException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
catch (IllegalAccessException e)
{
e.printStackTrace();
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
}
public void setStatement(Statement statement)
{
this.statement = statement;
}
public Statement getStatement()
{
return statement;
}
}
Next is the DataOp class. It has one method that operates on the user table. This class is not generic.
package jdbctest;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class DataOp
{
Statement statement;
public DataOp(Statement statement)
{
this.statement=statement;
}
public List getUserList(String user)
{
List list=new ArrayList();
try
{
ResultSet result=statement.executeQuery("Select * from user where user_id='"+user+"'");
while(result.next())
{
list.add(result.getString(1));
}
}
catch (SQLException e)
{
e.printStackTrace();
list=null;
}
return list;
}
}
Last is the class that tests the GenericDAO and DataOp classes. Here we are passing the driver name
corresponding to Type IV of MySQL JDBC driver and the corresponding URL.
package jdbctest;
public class DAOTest
{
public static void main(String args[])
{
//create instance of DAO class. Here we are using MySQL Type IV Driver
DAO dao=new
DAO("com.mysql.jdbc.Driver","jdbc:mysql://localhost/test","r
oot","root123");
//Creating instance of DataOp class
DataOp dataOp=new DataOp();
//calling the getUserList method for user whose id is 23
System.out.println(dataOp.getUserList("23"));
}
}
That completes a basic application.
4.0 CONCLUSION
In this unit you have been introduced to the fundamental concepts of Database and Database
management systems. You have also learnt the different types of SQL statements, constraints and
database programming.
5.0 SUMMARY
What you have learnt in this unit concerns
• Introduction to Information Systems which refers to a systemof persons, datarecords and
activities that process the data and informationin an organization.
• The study of information systems originated as a sub-discipline of computer science in an
attempt to understand and rationalize the management of technology within organizations.
• Areas of application or work which includes:
v Information Systems Strategy
v Information Systems Management and
v Information Systems Development.
• Types of Information Systems which Management Information Systems (MIS) or Reporting
Systems, Decision Support Systems, Transaction Information System (TIS) and Expert Systems.
6.0 TUTOR MARKED ASSIGNMENT
• List and explain the various SQL statements
• Write a short note on Database Management System and JBDC
7.0 FURTHER READING AND OTHER RESOURCES
SQL – A practical introduction by Akeel I. Din
An Introduction to Database Systems, Eighth Edition, C. J. Date, Addison Wesley, 2004, ISBN:
0-321-19784-4.
