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.

Comments

Post a Comment

Popular posts from this blog

A few notes on JavaScript

I am one the 99% of the world who has long nursed the misconception that JavaScript has something to do with Java. And why not? The name sort of implies it. It was only after I searched without success on the Java Tutorial site for some mention of JavaScript that I stumbled across the knowledge that JavaScript was in fact developed by Netscape, under the name LiveScript. Armed with that knowledge, it is not surprising to find the best reference information on Mozilla site. According to Wikipedia , the nonsensical name choice had something to do with Netscape wanting to cash in on the popularity and cachet of Java, and Sun wanting support for Java technology in the then popular Netscape browser. For a while only the Netscape family of browsers supported JavaScript. Microsoft later produced what it calls JScript to run in its browser. JScript is similar but not identical to Javascript. According to Foldoc JavaScript runs a hundred times slower than C , and ten
Read more

Forum Comments on Java Applets

It is so long since I last posted on the once was Sun/Java now is Oracle developer forum my account seems to have been ditched. While I wait to have it resurrected I've done a little searching in the Java Database Connectivity (JDBC) forum using the search term "Applet". My first search, using the default 3 month period, yielded nothing. No one had posted anything about or even mentioned Applets in the last three months. So either they are very easy to use and work very well, so hardly anyone has any problems with them, or maybe ... not very many people use them at all. My stomach was beginning to churn a bit, when I extended the search period to the current year - and with good reason. Somebody, a little bit like me I think, had written a game in an applet, and wanted to record top scores in a database. He described his connection problem in this thread , and the first reply began: Doing JDBC calls from an Applet is such a bad idea that I am glad y
Read more

Creating a Custom Swing Component

Image
I always return to my blog when I'm stuck, and I'm stuck right now. I want to create a custom Swing component. Specifically, I want something like a JProgressBar , with the following changes: In place of a flat Foreground color, I want a rainbow spectrum, showing only the red range for low Values, and the whole spectrum for values close to the Maximum value; Instead of a rectangular box, I want the progress "bar" to take the shape of a normal, or Gaussian, distribution curve; I want progress to be displayed by the area under the curve, rather than by a simple linear scale along the x axis. It's not that there isn't stuff out there. I have five tabs open in my browser, specifically addressing the creation of custom components in Swing, as well as the source code for the JProgressBar . It is that, like everything to do with Java, it is bloody difficult to read. I shall begin from the horse's mouth as it were, with an article on the Java
Read more