Transcript lecture notes

EXAMPLE I
An application showing JDBC
access to Cloudscape.
http://www-306.ibm.com/software/data/cloudscape/
package example1;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import com.ibm.db2j.drda.DB2jServer;
public class SimpleApp {
/* the default framework is embedded*/
public String framework = "embedded";
public String driver = "com.ibm.db2j.jdbc.DB2jDriver";
public String protocol = "jdbc:db2j:";
public static void main (String[] args) {
new SimpleApp().go( args);
}
void go (String args[]) {
/* parse the arguments to determine which framework is desired*/
parseArguments( args);
System.out.println ("SimpleApp starting in " + framework + "
mode.");
try {
/* The driver is installed by loading its class. In an embedded
environment, this will start up Cloudscape, since it is not already
running. */
Class.forName(driver).newInstance();
System.out.println ("Loaded the appropriate driver.");
Connection conn = null;
Properties props = new Properties();
props.put ("user", "user1");
props.put ("password", "user1");
/* The connection specifies create=true to cause the database to be
created. To remove the database, remove the directory db2jDB and its
contents. The directory db2jDB will be created under the directory that
the system property db2j.system.home points to, or the current directory
if db2j.system.home is not set. */
if (framework.equals ("embedded")) {
conn = DriverManager.getConnection (protocol +
"db2jDB;create=true", props);
}
else { // jccjdbc framework
DB2jServer server = new DB2jServer(null);
server.testConnection("db2jDB;create=true", null, null, "localhost",
1527);
conn = DriverManager.getConnection(protocol +
"//localhost:1527/db2jDB", props);
}
System.out.println ("Connected to and created database db2jDB");
conn.setAutoCommit(false);
/* Creating a statement lets us issue commands against
the connection. */
Statement s = conn.createStatement();
// We create a table, add a few rows, and update one.
s.execute ("create table db2jDB(num int, addr varchar(40))");
System.out.println ("Created table db2jDB");
s.execute ("insert into db2jDB values (1956,'Webster St.')");
System.out.println ("Inserted 1956 Webster");
s.execute ("insert into db2jDB values (1910,'Union St.')");
System.out.println ("Inserted 1910 Union");
s.execute( "update db2jDB set num=180, addr='Grand Ave.'
where num=1956");
System.out.println ("Updated 1956 Webster to 180 Grand");
s.execute( "update db2jDB set num=300, addr='Lakeshore Ave.'
where num=180");
System.out.println ("Updated 180 Grand to 300 Lakeshore");
// We select the rows and verify the results.
ResultSet rs = s.executeQuery ("SELECT num, addr
FROM db2jDB ORDER BY num");
if (!rs.next())
throw new Exception ("Wrong number of rows");
if (rs.getInt(1)!=300)
throw new Exception ("Wrong row returned");
if (!rs.next())
throw new Exception ("Wrong number of rows");
if (rs.getInt(1)!=1910)
throw new Exception ("Wrong row returned")
if (rs.next())
throw new Exception ("Wrong number of rows");
System.out.println ("Verified the rows");
s.execute ("drop table db2jDB");
System.out.println ("Dropped table db2jDB");
// We release the result and statement resource
rs.close();
s.close();
System.out.println ("Closed result set and statement");
// We end the transaction and the connection.
conn.commit();
conn.close();
System.out.println ("Committed transaction and closed
connection");
/* In embedded mode, an application should shut down Cloudscape.
If the application fails to shut down Cloudscape explicitly, the
Cloudscape does not perform a checkpoint when the JVM shuts down,
which means that the next connection will be slower.
Explicitly shutting down Cloudscape with the URL is preferred. This style
of shutdown will always throw an "exception". */
boolean gotSQLExc = false;
if (framework.equals ("embedded")) {
try {
DriverManager.getConnection("jdbc:db2j:;shutdown=true");
} catch (SQLException se) {
gotSQLExc = true;
}
if (!gotSQLExc)
System.out.println ("Database did not shut down normally");
else
System.out.println( "Database shut down normally");
}
}
catch (Throwable e) {
System.out.println ("exception thrown:");
if (e instanceof SQLException)
printSQLError( (SQLException) e);
else
e.printStackTrace();
}
System.out.println ("SimpleApp finished");
}
static void printSQLError (SQLException e) {
while (e != null) {
System.out.println (e.toString())
e = e.getNextException();
}
}
private void parseArguments(String[] args) {
int length = args.length;
for (int index = 0; index < length; index++)
{
if (args[index]. equalsIgnoreCase ("jccjdbcclient"))
{
framework = "jccjdbc";
driver = "com.ibm.db2.jcc.DB2Driver";
protocol = "jdbc:db2j:net:"; // localhost:1527
}
}
}
}
This program accomplishes the
following tasks:









starts up the Cloudscape engine, if necessary
creates and connects to a database
creates a table
inserts data
updates data
selects data
drops a table
disconnects
shuts down Cloudscape, if necessary
Run in the Embedded Environment
 The simplest Cloudscape environment.
 The application starts up an instance of
Cloudscape within the current JVM and shuts
down the instance before it completes.
 No network access is involved. In an
embedded environment, only one application
at a time can access a database.
New Files and Directories: after
running the application
 db2jDB (directory) The directory that makes up the db2jDB
database.
We must not modify anything in this directory, or we will corrupt
the database.
The directory was created when the application connected with
Cloudscape, using the attribute create=true in the database
connection URL.
The database name, db2jDB, was also set in the database
connection URL.
 db2jDB\log (directory) The directory that holds the database
log for the db2jDB database.
 db2jDB\seg0 (directory) The directory that holds the data for
the db2jDB database.
 db2jDB\service.properties An internal file that holds boottime configuration parameters for the db2jDB database; do
not edit.
 db2j.LOG The log file with Cloudscape progress and error
messages.
Setting Class Path for an
Embedded Environment
 Cloudscape provides a script to help us
get started setting class path in
%DB2J_INSTALL%bin
 This script is called setCP and
comes for Windows environment with .bat
For users working in this environment, copying the
commands in this file will help us get started
setting the class path.
Running the Application in an
Embedded Environment
 Open a command window and change directory in
which the application files are copied
For example: C:\Cloudscape_5.1\bin
 DB2J_INSTALL environment variable is set to the
the directory we installed the Cloudscape software
(C:\Cloudscape_5.1)
 Run Cloudscape's utility for testing the class path
for an embedded environment
java com.ibm.db2j.tools.sysinfo -cp arguments
or
java com.ibm.db2j.tools.sysinfo -cp embedded
SimpleApp.class
com.ibm.db2j.tools
public class sysinfo extends java.lang.Object
This class (sysinfo) displays system information to system
out.
 This class displays system information to system out. To
run from the command-line, enter the following:
java com.ibm.db2j.tools.sysinfo
 Also available on this class are methods which allow us
to determine the version of the code for the system
without actually booting a database.
 This is the Cloudscape version of the .jar files, not of our databases.
If the environment is set up
correctly
 The utility shows output indicating success.
 It looks like the following:
FOUND IN CLASS PATH:
Cloudscape primary library (db2j.jar)
Valid Cloudscape license (Cloudscape primary
library, or for evaluation copies of the software
only, license.jar)
user-specified class (SimpleApp)
SUCCESS: All Cloudscape-Related classes for
embedded environment found in class path.
If something is missing from class
path environment
 The utility indicates what is missing
 For example, if we neglected to add the directory containing
the SimpleApp class to our class path, the utility would
indicate as such:
Testing for presence of Cloudscape-related libraries for
embedded environment.
FOUND IN CLASS PATH:
Cloudscape primary library (db2j.jar)
Valid Cloudscape license (Cloudscape primary library, or for
evaluation copies of the software only, license.jar)
NOT FOUND IN CLASS PATH:
user-specified class (SimpleApp)
(SimpleApp not found.)
If we have our environment set
up correctly
 we can execute the application from the same directory
java SimpleApp
 A successful run produces the following output:
SimpleApp starting in embedded mode.
Loaded the appropriate driver.
Connected to and created database db2jDB
Created table db2jDB
Inserted 1956 Webster
Inserted 1910 Union
Updated 1956 Webster to 180 Grand
Updated 180 Grand to 300 Lakeshore
Verified the rows
Dropped table db2jDB
Closed result set and statement
Committed transaction and closed connection
Database shut down normally
Be careful while running the
example SimpleApp
 To run the example SimpleApp,firstly open the
following directory:
C:\Cloudscape_5.1\frameworks\NetworkServer\bin
 Then run the following file:
"setNetworkServerCP.bat"
 Finally: you can run your example
create=true
function
 Creates the standard database specified
within the database connection URL
 Cloudscape system and then connects to
it.
 If the database cannot be created, the
error appears in the error log and the
connection attempt fails with an
SQLException indicating that the database
cannot be found.
EXAMPLE II
An application showing JDBC
access to MySQL
package example2;
import java.sql.*;
public class JdbcExam2 {
public static void main(String args[]) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
Class.forName ("com.mysql.jdbc.Driver"). newInstance();
con = DriverManager.getConnection ("jdbc:mysql:///examples", "root", "secret");
st = con.createStatement();
rs = st. executeQuery ("SELECT user_id, first_name, last_name,
country_code FROM users");
while (rs.next()) {
int userId = rs.getInt(1);
String firstName = rs.getString(2);
String lastName = rs.getString(3);
String countryCode = rs.getString(4);
System.out.println(userId + ". " + lastName + ", " +
firstName + " (" + countryCode + ")");
}
} catch (Exception e) {
System.err.println ("Exception: " + e.getMessage());
} finally {
try {
if(rs != null)
rs.close();
if(st != null)
st.close();
if(con != null)
con.close();
} catch (SQLException e) {
}
}
}
}
Step by Step Explanations
import java.sql.*;
 The required JDBC classes are imported from
java.sql package.
public class JdbcExam2 {
... }
 given a name to the class, i.e. JdbcExam2
public static void main (String args[]) {
... }
 created a main() method for the class.
Step by Step Explanations
cont’d
 We create 3 variables to hold Connection,
Statement and ResultSet objects for us
which we'll create later in a try/catch/finally
block.
Connection con = null;
Statement st = null;
ResultSet rs = null;
 Then, we enter a try/catch/finally block.
Our data access code will reside in the try block
Exception notification code in the catch block
Code to close the connection in the finally block
Step by Step Explanations
cont’d
 First thing we do to display records is to obtain a connection
to MySQL database
 we do that my first loading it's JDBC (Connector/J) driver and then
using DriverManager to obtain a connection to our "examples"
database.
 We might want to change the password from
"secret" to whatever is our password for 'root' account.
Class.forName ("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection
( "jdbc:mysql:///examples", "root", "secret");
Step by Step Explanations
cont’d
 Once we are connected, we create a new
SQL Statement object.
st = con.createStatement();
 We then execute this statement to obtain a
ResultSet which contains all the records from the
"users" table and we do that by executing a
SELECT SQL statement.
rs = st.executeQuery("SELECT user_id,
first_name, last_name, " + "country_code FROM
users");
Step by Step Explanations
cont’d
 We then iterate through that ResultSet to obtain values for
each field of the "users" table. We then print these values on
the user console
while(rs.next()) {
int userId = rs.getInt(1);
String firstName = rs.getString(2);
String lastName = rs.getString(3);
String countryCode = rs.getString(4);
System.out.println(userId + ". " + lastName + ", " +
firstName + " (" + countryCode + ")");
}
Step by Step Explanations
cont’d
In the finally block we close off the ResultSet, Statement and Connection
objects
try {
if (rs != null) rs.close();
if (st != null) st.close();
if (con != null) con.close(); }
catch (SQLException e) {}
Finally:
 Compile the example: javac JdbcExam2.java
 Before running the Java program, make sure that MySQL server is
running that we created and setup "examples" database and "users"
table.
Now to run the Java program, we execute following command at the
command prompt from the folder from where we compiled
JdbcExam2.java:
java example2.JdbcExam2
Creating databases and tables in
MySQL
 To create a new database in MySQL:
CREATE DATABASE [IF NOT EXISTS] db_name;
 Create a new database with the name of
"examples" in our MySQL database server.
Start the MySQL server if it is not already
running and log in using the mysql client like
this:
mysql -u root -p
MySQL Server Logon
execute
CREATE DATABASE examples;
statement at mysql command prompt to create a new database
with the name of "examples":
Removing an existing Database in
MySQL
 DROP DATABASE [IF EXISTS] db_name;
Execute following command at mysql
command prompt to drop "examples"
database that we just created (don't worry
we'll recreate it):
DROP DATABASE examples;
Dropping Existing Database
 DROP DATABASE [IF EXISTS] db_name;
 We executed
DROP DATABASE examples
command at mysql command prompt to
drop "examples" database that we just
created
Creating a new Table in MySQL
 CREATE TABLE tbl_name [(create_definition, ...)]
[table_options]
CREATE TABLE users (
user_id
INT NOT NULL PRIMARY KEY
AUTO_INCREMENT,
first_name TINYTEXT NOT NULL,
last_name
TINYTEXT NOT NULL,
country_code CHAR(2) NULL );
With CREATE TABLE command we are going to use to create
a "users" table with 4 fields in the "examples" database we
just created
 To execute the CREATE TABLE command from the
create_tables.sql file.
Login (if not already loginned ) to MySQL server using the 'mysql'
client and execute this command:
SOURCE create_tables.sql
 The argument to the 'SOURCE' command is the relative
or absolute address to create_tables.sql file.
 we have a table with the name of "users" in the database
"examples".
To see if it has been created successfully, we execute
following command at mysql command prompt:
SHOW TABLES;
Viewing Existing Tables
FINALLY
 We have created a new database with the
name of "examples" which contains a
table "users".
We can move on to add some records in this table
&
We can display those records using JDBC in a
Java program.
Displaying the records from a table
in MySQL?
 Displaying records from our "users" table in
the "examples" database,
we will be making use of Connection, Statement and
ResultSet classes.
 Before we do that we'll add some records in
the "users" tables.
Inserting Records in "users" Table
We create a new file and save it as
populate_tables.sql in the same folder
 where we kept create_tables.sql file.
USE examples;
INSERT INTO users (first_name, last_name, country_code)
VALUES ('Shoaib', 'Akhtar', 'PK');
INSERT INTO users (first_name, last_name, country_code)
VALUES ('Brett', 'Lee', 'AU');
INSERT INTO users (first_name, last_name, country_code)
VALUES ('Shawn', 'Pollock', 'ZA');
INSERT INTO users (first_name, last_name, country_code)
VALUES ('Jimmy', 'Anderson', 'UK');
INSERT INTO users (first_name, last_name, country_code)
VALUES ('Chaminda', 'Vaas', 'LK');
 populate_tables.sql
statements
file contains 5 INSERT SQL
Added 5 new records in the "users" table
 Executing the command:
SOURCE populate_tables.sql;
Another Basic Example
package example3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdbcExam3 {
public static void main(String args[]) {
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection ("jdbc:mysql:///test",
"root", "secret");
if (!con.isClosed())
System.out.println ("Successfully connected to " + "MySQL
server using TCP/IP...");
}
catch(Exception e) {
System.err.println ("Exception: " + e.getMessage());
}
finally { try { if(con != null)
con.close();
}
catch (SQLException e) {}
}
}
}
Database URLs in JDBC
 JDBC requires that all database connection strings should
be represented by URLs.
 The URLs used in JDBC have following structure:
jdbc:subprotocol:subname
jdbc:mysql://host_name:port/dbname
MySQL Connector/J JDBC Driver
 In HTTP we begin a URL with the protocol name i.e. http:,
similarly in JDBC driver URLs,
 start the URL with protocol name i.e. jdbc:.
 Next subprotocol represents the database we want to connect to e.g.
mysql, odbc etc.
 subname provides additional information on how and where to
connect.
To specify a JDBC Driver name
 We need to know, besides the database
URL, the full class name of your JDBC driver
com.mysql.jdbc.Driver
in case of MySQL Connector/J JDBC driver.
 The name of the driver is a requirement and
is not optional.
 We can tell JVM about what driver/s to use by
using one of the following methods:
To load the the driver/s at JVM startup, specify the
driver/s in jdbc.drivers system property like this:
java -Djdbc.drivers= com.mysql.jdbc.Driver ourjavaprogram
To explicitly load the driver, use Class.forName()
method in code like this:
Class.forName ("com.mysql.jdbc.Driver"). newInstance();
com.mysql.jdbc.Driver" is the name of the
JDBC driver that we want to load.
How to create a connection to a
Database?
 To create a connection to a database, we
have to use java.sql.DriverManager's
getConnection() method.
 This method takes as an argument the
database URL we want to connect to.
 It then internally finds the appropriate
driver which has been loaded in the JVM
and
delegates the work of creating the connection to
that driver.