CSCI213/ITCS907/MCS9213
Autumn Session, 2007
Assignment 4: Demonstration code
This page contains example code, and descriptions of tasks that you should perform before you attempt assignment 4. Before you start assignment 4, you will need to have:
Assignment 4 involves writing an application program that accesses a database using JDBC. Code using JDBC to access databases follows regular patterns. Once you are used to these patterns, database usage becomes quite simple. The code is highly standardized; fragments that you used in one program can often be adapted to a subsequent application. You very rarely need to start again from scratch, you cut and paste from your own private library of code fragments.
Your database programs will ultimately have to work with the Oracle database on Unix. (The assignment does allow you to use a different database on your home machine, but really it is preferable if you do complete at least this exercise with Oracle.) This requires a little bit of preparatory work.
All students should have accounts that permit use of the Oracle database on our Unix system. You can create tables in your partition of the database, and access these tables using systems that submit SQL queries.
Actual access to the database depends on certain environment variables being set correctly as you login. Your ".profile" file should contain the line:
. /share/etc/csoraenvThat is "dot,space,slash,s,h,a,r,e ....".
This rather strange Unix command should achieve two things. First it will define some environment variables that Oracle code needs to operate (eg variables defining the location of various files and directories). Second, it should modify your Java CLASSPATH environment variable so that it includes the specialized libraries needed for Java to communicate with Oracle. This modification applies when your run Java applications from the command line on Unix. For NetBeans, you will have to explicitly configure your projects to add a library with the JDBC driver code.
The ".jar" file with the driver code to access Oracle is in /share/cs-pub/csci213/Lab4. It should also be installed on the PCs in the laboratory in C:\DBDrivers. When defining a NetBeans project, you will use the "add library" pop-up menu that you get by right-clicking the project's libraries folder - you use the file-open dialog to navigate to the location of the .jar file and "open" the .jar file.
Apart from JDBC code in Java, you will also use the "sqlplus" program.
sqlplus can be used to submit SQL statements that are typed in directly.
You use the sqlplus program
to create your tables and maybe also to populate them with data.
You can also test out SQL queries quite easily in sqlplus before
you incorporate them into your Java programs (remember, omit the
final semi-colon when you copy a SQL string into a Java program).
You should modify your .profile file to contain the Oracle initialization command and then (after logging back in to use the new profile) you should try using sqlplus. (Get your lab tutor to help if you have problems). You will have received mail from yuan (the software systems officer responsible for your Oracle accounts) with details of your Oracle login identifier and initial password. (Accounts are normally created with the login name the same as your Unix login, and often have your Unix login as your initial password). The first thing that you should do with sqlplus is change your password. If you find you cannot use the database, and neither the tutor nor clued-up colleagues can help you, you should email yuan@uow.edu.au with details of your problem. Make sure that there really are problems, in the past most requests to Yuan have come from students who aren't using sqlplus properly.
Before you can run the demonstration program, you must define the table it is to use to store its data. The data used in this exercise supposedly represent students' marks (this file is also available in /share/cs-pub/csci213/Lab4). The data in the file are like the following:
u75972222 Adrian YT 4 5 6 7 8 26 u09234752 Akbar JJ 2 0 3 7 4 19
Each line has a student-identifier, name, initials, five assignment marks and one exame mark.
The datatable that must exist in Oracle is to have fields for student identifier, name, initials, and the six numeric values. The text data are represented as "varchar" strings (e.g. the name is a string of up to 32 characters).
The SQL definition of the required table is:
create table demomarkstable ( id varchar(12), name varchar(32), initials varchar(6), a1 float, a2 float, a3 float, a4 float, a5 float, exam float );
You can define your table by typing in the above definition in response
to a prompt from sqlplus. Alternatively (and better) you can place the
definition in a file ("table.sql") and then have Oracle process the
commands in the file by entering @table.sql; as a response to a
prompt from sqlplus.
Alternatively, you can use the NetBeans SQL console:
For these exercises you will use (database-type/Java-type) varchar/String, float, integer/int, and number(,)/BigDecimal data types. Database number types specify the total number of digits and the number after the decimal point - e.g. number(10,2) is suitable for a currency ammount. Oracle actually prefers you to use Number(n) (for integers) and Number(n,m) for floats, doubles and DecimalNumber types. The Oracle type integer is Number(38). Oracle also really prefers varchar2 to varchar. Other databases will have different conventions!
The first example illustrates code for connecting to a database and then inserting data into the existing demomarkstable table.
The code is made up from two classes - DBInfo and
Demo1.
Class DBInfo is a simple helper class that makes it easier to switch between an ODBC database on a PC and an Oracle database on Unix. The only difference in the code is the string constants that define the names of the database driver class, and the name of the actual database. When used with Oracle, the username and password should be the same as you use with sqlplus.
import java.sql.*;
public class DBInfo {
public static final String userName = "HSimpson"; // Change if not HSimpson
public static final String userPassword = "DUH"; // Change to current password
// Settings for Unix Oracle system.
public static final String dbDriverName =
"oracle.jdbc.driver.OracleDriver";
// Check at Uni as to whether this URL should be changed
// (sometimes Oracle updates result in change of server URL)
public static final String dbURL =
"jdbc:oracle:thin:@wraith:1521:csci";
// Settings for ODBC system on PC
// private static final String dbDriverName =
// "sun.jdbc.odbc.JdbcOdbcDriver";
// private static final String dbURL =
// "jdbc:odbc:MyODBCDB";
//
public static final Connection connectToDatabase() {
Connection dbConnection = null;
try {
Class.forName (dbDriverName);
dbConnection = DriverManager.getConnection(
dbURL,
userName, userPassword);
}
catch(Exception e) {
System.out.println("Failed to get a database connection");
System.out.println(e);
System.exit(1);
}
return dbConnection;
}
}
(You are not allowed to use HSimpson's Oracle account, anyway he has changed his password. Substitute your own name and Oracle password before trying to use this code on Unix.) To switch to using an ODBC database just comment out the Oracle related declarations and uncomment the ODBC related declarations. In Windows/Control Panel/Administrative Tools you will find a "Data Sources (ODBC)" tool; you use this to define a mapping of an ODBC name - such as MyODBCDB - to an actual database file, such as an Access .mdb file.
(It is possible to adapt the code to work with Microsoft Access as your ODBC database. However, older versions of Access do not support the "PreparedStatements" that are used in the example code below, relying instead on simple java.sql.Statements. The code can be modified to use java.sql.Statements. It will then work with Access and Oracle. The use of PreparedStatements is slightly more efficient and slightly less error prone than the use of Statements.)
The Demo1 class opens a text file containing student records, and passes this text file to a "process" function. The process function has a loop in which each student record is read from the text file. In this program, the data are written to records in the database. Demo1 is defined as follows:
public class Demo1 {
public static void main(String[] args) {
if(args.length!=1){
System.out.println("An input filename must be specified as a command line argument");
System.exit(1);
}
String filename = args[0];
FileReader fileInput = null;
try {
fileInput = new FileReader(filename);
}
catch(Exception e){
System.out.println("Failed to get file because" + e);
System.exit(1);
}
process(fileInput);
}
private static void process(FileReader finput){
BufferedReader input = new BufferedReader(finput);
Connection conn = DBInfo.connectToDatabase();
PreparedStatement pstmt = null;
try {
pstmt =
conn.prepareStatement(
"insert into demomarkstable values(?,?,?,?,?,?,?,?,?)");
}
catch(SQLException badSQL) {
System.out.print("Couldn't prepare statement because ");
System.out.println(badSQL);
System.exit(1);
}
for(;;) {
String line = null;
try{
line = input.readLine();
}
catch(IOException ioe) { break; }
if(line==null)break;
if(line.equals("")) break;
String[] items = line.split("\\s+");
String identifier = items[0];
String familyName = items[1];
String initials = items[2];
String markA1 = items[3];
String markA2 = items[4];
String markA3 = items[5];
String markA4 = items[6];
String markA5 = items[7];
String markExam = items[8];
double a1 = 0.0, a2 = 0.0, a3 = 0.0, a4 = 0.0, a5 = 0.0, exam = 0.0;
try {
a1 = Double.parseDouble(markA1);
a2 = Double.parseDouble(markA2);
a3 = Double.parseDouble(markA3);
a4 = Double.parseDouble(markA4);
a5 = Double.parseDouble(markA5);
exam = Double.parseDouble(markExam);
}
catch(NumberFormatException nfe) {
System.out.println("Unable to convert numeric data for " + identifier);
System.exit(1);
}
System.out.println(identifier);
try {
pstmt.setString(1,identifier);
pstmt.setString(2,familyName);
pstmt.setString(3,initials);
pstmt.setDouble(4,a1);
pstmt.setDouble(5,a2);
pstmt.setDouble(6,a3);
pstmt.setDouble(7,a4);
pstmt.setDouble(8,a5);
pstmt.setDouble(9, exam);
pstmt.executeUpdate();
}
catch(SQLException sqle) {
System.out.print("Unable to update record because ");
System.out.println(sqle);
System.exit(1);
}
}
try {
input.close();
}
catch(IOException ioe) {}
try {
conn.close();
}
catch(SQLException sqle){}
}
}
The "process" function uses the DBInfo helper class to obtain a connection to the appropriate database. It then prepares an SQL statement. The actual SQL says that the demomarkstable is to be updated with a new row containing the nine data elements that will be supplied at run-time. The preparation step involves a little negotiation with Oracle (in which the program will learn that the first three data elements should be strings with defined maximum sizes and the other arguments should be double numbers). The prepare step also builds private data structures that will later be used in the SQL request submission.
After the data have been read from a line in the file, a series requests is made to the "prepared statement
object" - pstmt - telling it the values of the various input parameters.
Finally, the statement is executed and an insert operation is done on the database.
If you were using Access and couldn't employ PreparedStatements, the code needed would be:
String insertStr = "insert into demomarkstable values ("
+ "'" + identifier + "',"
+ "'" + familyName + "',"
+ "'" + initials + "',"
+ a1 + ","
+ a2 + ","
...
+ exam +")");
...
Statement stmt = conn.createStatement();
...
stmt.executeUpdate(insertStr);
...
(You build the query as a long string; string data, like a student's name, must be enclosed within single quote marks.)
Task
You should get this program to run as a NetBeans project.
You should then use sqlplus to inspect the
populated table. You can give sqlplus queries such as select * from demomarkstable;,
select count(*) from demomarkstable where exam>40;.
You can also run checks from the NetBeans SQL console:
When you have created and populated the data table, you should write a simple (procedural, console-oriented) program that allows you to submit a query that retrieves a set of records from the database. The database usage reamins quite simple:
If you have not encountered SQL, there are a number of simple introductory tutorials available, such as those at www.wdvl.com. The SQL needed for these exercises, and for your assignment, is quite limited. Most of the time you will simply be retrieving a subset of rows from a table. Generally, you will want the data from all columns of the rows that you select. Consequently, you will have queries like (leave out semi-colon statement terminators in Java, use them in SQLPlus and similar tools):
select * from mytable;You want everything!
select * from mytable where field1>100;Get those records where value in column named field1 exceeds 100. Other numeric operators are < less, = equals, less-than-or-equals, etc.
select * from mytable where name='smith';Strings used in text comparisons must be in single quotes. This causes problems with names like O'Brien. If you use Prepared statements, the problems disappear - you define a query such as
PreparedStatement pstmt = myConnection.prepareStatement("select * from mytable where name=?");
You bind a value to the ? place-holder pstmt.setString(1,"O'Brien") and
execute the statment.select * from mytable where name like '%horn%'will match records with names like Thornley (and any other names with horn as a substring).
select * from mytable where field1>100 and field2='F';
The lecture notes on java.net contain an illustrative example of a simple server that handles getDate, getFortune, and Ping requests.
Implement the code as a NetBeans project:
Get it to run - starting both server and client processes on the same machine. You should be able tot test with really networked configurations by having your client on one lab machine access your server, or someone else's server, on another machine.
The lecture notes contain an almost complete version of the code for a serial server, and an outline of how it could be adapted to be a multithreaded server capable of handling concurrent clients. Once you have the serial server working, adapt the code to threaded "client handler" architecture. Test that you modified program really does support multiple concurrent clients.