Tuesday, June 8, 2010

Revisiting old code

When I first began this blog, I was pretty down on the official Java Tutorial on the Sun website. I was especially down on the Java Tutorial trail JDBC(TM) Database Access, which describes how to make a connection with a database. But a year later, I find myself scanning my own blog, which I thought comprised the finest pearls of clarity and wisdom, and I can't find any description, let alone a better description, of how to connect to a database without using a GUI app like NetBeans.

There is an entry, entitled "Building a financial mini-app", which describes an application of such a connection, but it is distinctly unhelpful to anyone wanting to build such a tool for themselves. Indeed I found it distinctly unhelpful even to me, trying to re-apply my own code. I shall therefore attempt to re-write the page in a slightly less cryptic fashion.

This has nothing to do with my Rasch-Itembank project at java.net, but for me it is a useful exercise in manipulating data. The purpose of the code is to calculate my capital gains for tax purposes under Australian Law.

I shall begin at the beginning by very beginning, with I couple of short batch files I use to set paths. In the Windows home directory I have a batch file called cd1.bat, which sets the path to the Java binaries directory and relocates us to the home of the planned new database and code intended to create it:

cd My Documents\CodeLocal\Fin
set path=D:\Program Files\Java\jdk1.6.0_03\bin

Some people set the path to the Java binaries directory as a Windows environmental variable, but when I began I was using a ropy old computer with a corrupted OS, and the environmental variable never stuck, so I got into the habit of setting it for every session.

In the \Fin directory I have another batch file called cd3.bat, which sets the class path for both the derby.jar file and my own class file, which is called FindbCreate.class:

>Java -classpath
"D:\Docs\Jonno\My Documents\CodeLocal\Fin\derby.jar";
"D:\Docs\Jonno\My Documents\CodeLocal\Fin"
FindbCreate>

NB This is a one line batch file which has been broken up for display purposes. The java code behind the FindbCreate.class, used to create the database and the first table, is as follows:

import java.sql.*;

public class FindbCreate {
public static void main(String args[])
throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
String dCreate = "CREATE TABLE TransRaw " +
"(TRidx INTEGER, " +
"dDate DATE, TrType CHAR(5), StCode CHAR(3), " +
"TrPrice REAL, TrQ INTEGER, primary key (TRidx))";
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin;create=true";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are the maketable lines
*/
System.out.println("try maketable");
try {
stat.executeUpdate(dCreate);
System.out.println("maketable succeeds");
} catch(Exception ex) {
System.out.println("maketable fails");
System.out.println(dCreate);
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

And if all goes well, the following should appear in the command screen:

Java -classpath
"D:\Docs\Jonno\My Documents\CodeLocal\Fin\derby.jar";
"D:\Docs\Jonno\My Documents\CodeLocal\Fin"
FindbCreate
try connection
connection succeeds
try maketable
maketable succeeds
disconnection succeeds

The database needs a second table. This could have been included in the code which created the first table, but when I am feeling my way, I like to take things one step at a time. The code I used to create the second table is shown below. Note the connect string no longer includes the create=true switch, because when you run this code, the database already exists.

import java.sql.*;

public class FindbCreateT2 {
public static void main(String args[])
throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
String dCreate = "CREATE TABLE TransComp " +
"(Sidx INTEGER, " +
"dDate DATE, TrType CHAR(5), StCode CHAR(3), " +
"TrPrice REAL, TrQ INTEGER)";
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are the maketable lines
*/
System.out.println("try maketable");
try {
stat.executeUpdate(dCreate);
System.out.println("maketable succeeds");
} catch(Exception ex) {
System.out.println("maketable fails");
System.out.println(dCreate);
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

To run this code you can create a new batch file or edit cd3.bat to call the class FindbCreateT2.

And if all goes well, the following should appear in the command screen:

Java -classpath
"D:\Docs\Jonno\My Documents\CodeLocal\Fin\derby.jar";
"D:\Docs\Jonno\My Documents\CodeLocal\Fin"
FindbCreateT2
try connection
connection succeeds
try maketable
maketable succeeds
disconnection succeeds

The first table, now needs to be filled. Once again, the code to do so could have been included in either of the previously shown classes, but I have broken it out into a separate class for the time being:

import java.sql.*;
import java.util.*;
import java.io.*;

public class FindbFill {
public static void main(String args[])
throws ClassNotFoundException, SQLException, IOException {
/**
* These are shared variable declarations
*/
BufferedReader reader = null;
String strContents = new String();
String strTridx = new String();
float fTridx = 0;
int iTridx = 0;
String strDate = new String();
String strTrType = new String();
String strCode = new String();
String strPrice = new String();
float fPrice = 0;
String strTrQ = new String();
float fTrQ = 0;
int iTrQ = 0;
int iCount = 0;
Connection connect = null;
Statement stat = null;
String dInsert = "";
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are optional delete lines
*/
System.out.println("try delete");
try {
stat.executeUpdate("DELETE FROM TransRaw where TRidx > 0");
System.out.println("delete succeeds");
} catch(Exception ex) {
System.out.println("delete fails");
System.out.println(dInsert);
System.err.println("SQLException: " + ex.getMessage());
}
/**
* This code reads the text file.
*/
try {
reader = new BufferedReader(new FileReader("Tr100608.txt"));
String l;
while ((l = reader.readLine()) != null) {
StringTokenizer strToken = new StringTokenizer(l, ",", false);
strTridx = strToken.nextToken();
strDate = strToken.nextToken();
strTrType = strToken.nextToken();
strCode = strToken.nextToken();
strPrice = strToken.nextToken();
strTrQ = strToken.nextToken();
dInsert = "INSERT INTO TransRaw " +
"VALUES (" + strTridx +
", '" + strDate +
"', '" + strTrType +
"', '" + strCode +
"', " + strPrice +
", " + strTrQ + ")";
/**
* These are the insert lines
*/
// System.out.println("try insert");
// System.out.println(dInsert);
try {
stat.executeUpdate(dInsert);
iCount = iCount + 1;
// System.out.println("insert succeeds");
} catch(Exception ex) {
System.out.println("insert fails");
System.err.println("SQLException: " + ex.getMessage());
}
}
} catch(Exception ex) {
System.out.println("Error: " + ex.getMessage());
}
/**
* These are the closing statements
*/
finally {
/**
* Close input stream
*/
if (reader != null) {
reader.close();
}
System.out.println(iCount + " records successfully inserted");
/**
* Close connection
*/
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

Prior to running this code it is essential to have the source text file ready and in the same directory as the java class file. I should have a query written so that I could quote the SQL, but instead I just exported a table from "MS Access" and tweaked the text file a bit so that it looked like this:

1,05.09.2006,Buy,BSL,6.86,200
2,12.09.2006,Buy,TFC,0.38,2500
3,12.09.2006,Buy,ZFX,10.65,100
4,12.09.2006,Buy,TLS,3.51,300
5,13.09.2006,Buy,CSR,2.85,400 ...

When you run the FindbFill class from the command line, if all goes well the output messages should be as follows:

Java -classpath
"D:\Docs\Jonno\My Documents\CodeLocal\Fin\derby.jar";
"D:\Docs\Jonno\My Documents\CodeLocal\Fin"
FindbFill
try connection
connection succeeds
try delete
delete succeeds
378 records successfully inserted
disconnection succeeds

If you get the format of the text file wrong, you might get a few error messages like these:

insert fails SQLException:
Syntax error: Encountered "," at line 1, column 33.
insert fails SQLException:
Lexical error at line 1, column 64.
Encountered: "$" (36), after: "".

After filling the TransRaw table, the penultimate step is to transform the data and fill the TransComp table. Once again, I have written the code for this in a class of its own. The code is as follows:

import java.sql.*;
import java.util.*;
import java.io.*;

public class FindbQ1 {
public static void main(String args[])
throws ClassNotFoundException, SQLException, IOException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement statS = null;
Statement statB = null;
Statement statI = null;
ResultSet resultBuy = null;
String Sidx = new String();
String Bidx = new String();
String strDate = new String();
String strTrType = new String();
String strCode = new String();
String strPrice = new String();
String strTrQ = new String();
String dInsert = new String();
String dDelete = new String();
float fSPrice = 0;
float fSQ = 0;
float fSProc = 0;
float fBCumQ = 0;
float fBCurQ = 0;
float fBEQ = 0;
int iBEQ = 0;
float fBPrice = 0;
float fBCurCost = 0;
float fBCumCost = 0;
float fProfit = 0;
float fComm = 22;
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );
statS = connect.createStatement();
statB = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
statI = connect.createStatement();
System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are optional delete lines
*/
System.out.println("try delete");
try {
statI.executeUpdate("DELETE FROM TransComp");
System.out.println("delete succeeds");
} catch(Exception ex) {
System.out.println("delete fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are query execution lines
*/
System.out.println("try query");
try {
String aquery = "SELECT * FROM TransRaw" +
" where TrType = 'Sell'" +
" order by dDate, TRidx";
ResultSet resultSell = statS.executeQuery(aquery);
/**
* Display sell results.
*/
while(resultSell.next()) {
Sidx = resultSell.getString(1);
strDate = resultSell.getString(2);
strTrType = resultSell.getString(3);
strCode = resultSell.getString(4);
strPrice = resultSell.getString(5);
strTrQ = resultSell.getString(6);
fSPrice = resultSell.getFloat(5);
fSQ = -resultSell.getFloat(6);
fSProc = fSPrice * fSQ;
fSProc = fSProc - fComm;
dInsert = "INSERT INTO TransComp " +
"VALUES (" + Sidx +
", '" + strDate +
"', '" + strTrType +
"', '" + strCode +
"', " + strPrice +
", " + strTrQ + ")";
System.out.println(dInsert);
/**
* These are the insert lines
*/
System.out.println("try insert");
try {
statI.executeUpdate(dInsert);
System.out.println("insert succeeds");
} catch(Exception ex) {
System.out.println("insert fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* Display buy results.
*/
String bquery = "SELECT * FROM TransRaw" +
" where TrType = 'Buy'" +
" and StCode = '" +
strCode +
"' for update of TrQ";
resultBuy = statB.executeQuery(bquery);
fBCumQ = 0;
fBCumCost = 0;
fProfit = 0;
while(fBCumQ < bidx =" resultBuy.getString(1);" strdate =" resultBuy.getString(2);" strtrtype =" resultBuy.getString(3);" strcode =" resultBuy.getString(4);" strprice =" resultBuy.getString(5);" strtrq =" resultBuy.getString(6);" fbprice =" resultBuy.getFloat(5);" fbcurq =" resultBuy.getFloat(6);" fbcumq =" fBCumQ"> fSQ){
fBEQ = fBCumQ - fSQ;
fBCurQ = fBCurQ - fBEQ;
fBCumQ = fBCumQ - fBEQ;
iBEQ = (int)(fBEQ);
resultBuy.updateInt("TrQ", iBEQ);
} else {
dDelete = "DELETE FROM TransRaw " +
"where TRidx = " + Bidx;
statI.executeUpdate(dDelete);
}
fBCurCost = fBCurQ * fBPrice;
fBCurCost = fBCurCost + fComm;
fBCumCost = fBCumCost + fBCurCost;
dInsert = "INSERT INTO TransComp " +
"VALUES (" + Sidx +
", '" + strDate +
"', '" + strTrType +
"', '" + strCode +
"', " + strPrice +
", " + fBCurQ + ")";
System.out.println(dInsert);
/**
* These are the insert lines
*/
System.out.println("try insert");
try {
statI.executeUpdate(dInsert);
System.out.println("insert succeeds");
} catch(Exception ex) {
System.out.println("insert fails");
System.err.println("SQLException: " + ex.getMessage());
}
}
}
fProfit = fSProc - fBCumCost;
System.out.println("Proceeds = " + fSProc);
System.out.println("Costs = " + fBCumCost);
System.out.println("Profit = " + fProfit);
resultBuy.close();
}
resultSell.close();
statS.close();
statB.close();
statI.close();
System.out.println("query succeeds");
} catch(Exception ex) {
System.out.println("query fails");
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

The output from this one gives all the information I need to complete my tax return, but the command window is a less than ideal place to store it, and the format is not suitable for cutting and pasting into a data table. I therefore have a final chunk of code, which queries the TransComp table, and dumps the data cleanly into the command window. The code is as follows:

import java.sql.*;
import java.util.*;
import java.io.*;

public class FindbQSim2 {
public static void main(String args[])
throws ClassNotFoundException, SQLException, IOException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
// Make connection
String connn = "jdbc:derby:dbFin";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
/**
* These are query execution lines
*/
System.out.println("try query");
try {
String aquery = "SELECT * FROM TransComp";
ResultSet resultSet = stat.executeQuery(aquery);
// Display results
while(resultSet.next()) {
System.out.println(resultSet.getString(1) + ", " +
resultSet.getString(2) + ", " +
resultSet.getString(3) + ", " +
resultSet.getString(4) + ", " +
resultSet.getString(5) + ", " +
resultSet.getString(6));
}
resultSet.close();
stat.close();
System.out.println("query succeeds");
} catch(Exception ex) {
System.out.println("query fails");
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

Had time permitted I could have output the data to a text file, but it was just as easy to copy and paste from the command line. I imported this to a table in "MS Access" and used the GUI to make a nice WYSIWYG report.

No comments: