Base lesson 2,
step 5: Run SQL-commands against the database
This
step of the tutorial
* shows how to create a 'thread' and
*
reads the SQL-commands from the input-file and performs the
SQL-commands against the database.
A 'thread' is a technology
that allows parallel execution of different parts of
applications.
One aim of this step of the tutorial is to show the
executed SQL-commands and the response of the database in the
text-area of the GUI.
Without a 'thread' the text would be
re-displayed after the whole set of SQL-commands was processed.
With
a 'thread' there are two parallel tasks:
* reading the
SQL-commands out of the input-file and performing them against the
database and
* periodically updating the text-area.
So a part
of the SQL-commands and the response of the database can be displayed
while further SQL-commands are in the queue waiting to be processed.
Preface:
The
code written in this tutorial is far away from being
optimized.
Emphasis of this tutorial is to develop the application
in small steps where the completion of each step allows the
application to be run eror-free and showing the result aimed by the
step.
Therefore the code is written to be understandable in favor
of being optimized.
Credits:
I
derived the guideline how to access a database within a
JAVA-application from:
www.developer.com/java/data/article.php/3417381.
Prerequisites:
Adding
an additional class for the 'Thread':
A
thread must be run in its own class.
Therefore a new class must be
created which will hold the code to read the SQL-commands from the
input-file, run the commands against the database and update the
text-area with the response from the database.
Right
click onto the package 'js_base02.application' and select >New>Class
Check
that the Package (js_base02.application)
was filled and enter the (Class-)Name (JS_DBLoad__Thread).
As
Superclass enter Thread.
Make
sure that none of the check-fields for 'Which method stubs would
you like to create ?' is checked and click the [Finish]
button.
Eclipse
has already generated a template and the individual code can be
entered.
First, just the comment is new.
package
js_base02.application;
/**
*
* @author kurt@javascout.biz
*
@date 2006-02-28
*
* @description
*
* execute the SQL-commands against the
database.
*
* A Thread is used to display the SQL-command and
* the response of the database immediately
* @change-log
* when who why
*
--------------------------------------------------------
*
*/
public
class JS_DBLoad__Thread
extends
Thread {
}
To
see a fast result of what the 'Thread' is for, the Thread will
temporarily contain code that counts up from 1 to 60 with pausing
for 1 second after every number.
Please refer to the comment
within the code for an explanation of each step.
*
* @change-log
*
when who why
*
--------------------------------------------------------
*
*/
public
class JS_DBLoad__Thread
extends
Thread
{
/**
*
A variable to hold the reference to the Class with the GUI.
*
This reference
allows to update the text-area periodically.
*
The variable is neccessary as the parameter is not available during
the lifetime of the method 'run'
* as the parameter is
'final' for performance-reasons */
JS_DBLoad
frmCallingFrame;
/**
*
Constructor of the class; 'final' is better for performace as it
'defines' that a change
* of the value of the parameter has
not to be considered. */
public
JS_DBLoad__Thread(final
JS_DBLoad parmCallingFrame) {
/*
Save the reference to the class with the GUI.
*/
frmCallingFrame
=
parmCallingFrame;
}
/*
* The method 'run' is started automatically after the
construction of the class 'Thread'. */
public
void run() {
try
{
/*
Temporary Code to show the effect of the Thread to the GUI
*/
for(int
i=1; i <= 60; i++) {
<=
60; i++) {
/*
Display the count-up on the GUI
*/
frmCallingFrame.get_txt_Report().append(„Loop
in
Thread: „ + Integer.toString(i) + „\n“);
/*
Wait 1 second to see that the GUI is periodically updated
*/
this.sleep(1000);
}
/*
Stop the thread */
this.destroy();
catch
(Exception e) {
/*
Unplanned end; display the information
*/
frmCallingFrame.get_txt_Report().append(„Exception
in Thread: „ + e.toString() + „\n“)
}
}
}
Please
remind to save the code just typed.
To
see a result of the method just coded has to be called.
As a
condition for the 'real' application (read the input-file and
perform the SQL-commands in the next steps), the call is only made
if the connection to the database was established
successfully.
protected
static void processDBLoad(JS_DBLoad
parmCallingFrame){
/*
*
Define an array that holds the parameters for the database-access */
String[]
array_DBParms;
/*
Call the method that extracts the parameters from the XML-file */
array_DBParms
= processGetDBParms(parmCallingFrame);
/*
Perform the connection to the database */
Connection
conToDatabase = null;
conToDatabase
= processConnectToDatabase(parmCallingFrame, array_DBParms,
conToDatabase);
if
(conToDatabase != null)
{
/*
Connection to Database successfully established;
* run the
SQL-commands in a thread that allows to periodically update the
results */
JS_DBLoad__Thread
t = new
JS_DBLoad__Thread(parmCallingFrame);
t.start();
}
}
To
see if the code was typed error-free it is a good time now to see a
result.
Please remind to save the code just typed before running
the application again by selecting
>Run>Run
As>Java Application.
Do not forget to 'Select Input
File' and select the (till now empty) file with the SQL-commands -
otherwise the XML-file with the parameters will not be loaded
!
Click the button 'Start
SQL execution' thereafter.
After connecting to the database, the
count-up should start and increase by 1 every second.
The
result of the shown screenshot should look like this:
top.
Coding
the reading of the input-file:
As
the framework of the 'Thread' is implemented and coded, code for the
business-application follows.
The next task - before running the
SQL-commands against the database - is to read the input-file with
the SQL-commands.
Please
remind to remove the temporary code !
A
prerequisite to access the sequential file is the import of the
package java.io.*
package
js_base02.application;
import
java.io.*;
/**
*
* @author kurt@javascout.biz
*
@date 2006-02-28
*
Here
is the code to read the input-file with the SQL-commands:
/*
* The method 'run' is started automatically after the
construction of the class 'Thread'. */
public
void run() {
try
{
try
{
String
strLineRead;
/*
Get the name of the file to be read from the field of the GUI.
*/
String
strInputFileName = frmCallingFrame.get_txt_InputFile().getText();
/*
Open the file using methods from the package 'java.io.*'.
*/
LineNumberReader
InputStream = new
LineNumberReader(new
FileReader(strInputFileName));
/*
Read the first line of the file. */
strLineRead
= InputStream.readLine();
/*
Check if at least 1 line is in the file; if not report an error.
*/
if
(strLineRead == null)
{
frmCallingFrame.get_txt_Report().append(„Input
file contains no data ! \n“)
}
/*
Use a while-loop to display the content of the line, process it and
read the next line. */
while
(strLineRead != null)
{
frmCallingFrame.get_txt_Report().append(„Input-line:
„ + strLineRead „\n“);
//
Insert the method call to perform database-access
here
strLineRead
= InputStream.readLine();
}
/*
Stop the thread */
this.destroy();
catch
(Exception e) {
/*
Unplanned end; display the information
*/
frmCallingFrame.get_txt_Report().append(„InterruptException
in Thread: „ + e.toString() + „\n“);
}
}
To
see a result, the input-file has to be filled with some meaningful
SQL-commands.
So open the file Commands.txt
in the folder src/Input by double clicking onto it (if
not already opened).
This file was created in JS_Base02,
Step 3 - Read parameters for the connection to the database.
drop
table LANGUAGE;
create table LANGUAGE (IsoCode char(2) primary
key not null);
alter table LANGUAGE add column LongName
varchar(62);
alter table LANGUAGE add column SpokenBy
bigint;
insert into LANGUAGE values ('en', 'English'
1000000000);
insert
into LANGUAGE values ('de', 'Deutsch' 100000000);
insert
into LANGUAGE values ('zh', 'Chinese' 1300000000);
After
saving the changed files it is time again to see an intermediate
result.
Please remind to save the code just typed before running
the application again by selecting
>Run>Run
As>Java Application.
Do not forget to 'Select Input
File' and select the file with the SQL-commands !
Click the
button 'Start
SQL execution' thereafter.
After connecting to the database, the
lines of the input-file are displayed.
The
result of the shown screenshot should look like this:
top.
Coding
the execution of the SQL-commands:
Now
it is time to execute the SQL-commands that were read from the
input-file.
A
prerequisite to perform database-operations is the import of the
package java.sql.*
:
package
js_base02.application;
import
java.io.*;
import
java.sql.*;
/**
*
* @author kurt@javascout.biz
*
@date 2006-02-28
*
Till
now the connection to the database was not passed to the method.
This was done to avoid an overloading of the code with not (yet)
needed text.
As variables that are passed as 'pointer'-parameter
to a Thread (this is because the Thread might live longer than the
calling method), the parameter has to be copied to a local
variable.
public
class JS_DBLoad__Thread
extends
Thread
{
/**
*
A variable to hold the reference to the Class with the GUI.
*
This reference
allows to update the text-area periodically.
*
The variable is neccessary as the parameter is not available during
the lifetime of the method 'run'
* as the parameter is
'final' for performance-reasons */
JS_DBLoad
frmCallingFrame;
Connection
ConToDatabase;
/**
*
Constructor of the class; 'final' is better for performace as it
'defines' that a change
* of the value of the parameter has
not to be considered. */
public
JS_DBLoad__Thread(final
JS_DBLoad parmCallingFrame,
final
Connection parmConToDatabase) {
/*
Save the reference to the class with the GUI.
*/
frmCallingFrame
=
parmCallingFrame;
ConToDatabase
= parmConToDatabase;
}
/*
* The method 'run' is started automatically after the
construction of the class 'Thread'. */
public
void run()
{
try
{
You
might have already noticed the 'error' in the class
JS_DBLoad__Action_Handler -
so also the construction of the class JS_DBLoad__Thread
has to be modified there: .
protected
static void processDBLoad(JS_DBLoad
parmCallingFrame){
/*
*
Define an array that holds the parameters for the database-access */
String[]
array_DBParms;
/*
Call the method that extracts the parameters from the XML-file */
array_DBParms
= processGetDBParms(parmCallingFrame);
/*
Perform the connection to the database */
Connection
conToDatabase = null;
conToDatabase
= processConnectToDatabase(parmCallingFrame, array_DBParms,
conToDatabase);
if
(conToDatabase != null)
{
/*
Connection to Database successfully established;
* run the
SQL-commands in a thread that allows to periodically update the
results */
JS_DBLoad__Thread
t = new
JS_DBLoad__Thread(parmCallingFrame,
conToDatabase);
t.start();
}
}
Now
it is time to code the access to the database-system. It is just a
few lines of code - but the try/catch-logic extends it.
To have a
better structure, the code is put into a seperate method:
.
private
static void processDBOperation(String
parmstrSQLCommand){
/*
*
Try/catch-logic to process errors */
try
{
/*
first, commands with a 'comment' are skipped */
int
intCommentPosition =
parmstrSQLCommand.indexOf(„//“);
if
((intCommentPosition
>= 0) && (intCommentPosition
<= 1))
return;
/*
Command is not a comment, run it against the database */
Statement
SQLStatement =
ConToDatabaseparmstrSQLCommand);
SQLStatement.executeUpdate(parmstrSQLCommand);
/*
Command successful; report it onto the GUI */
frmCallingFrame.get_txt_Report().append(„SQL-command
executed\n“);
catch
(SQLException SQLExc) {
/*
SQL-command was not successful; display reason
*/
frmCallingFrame.get_txt_Report().append(„SQL-command
error: „ + SQLExc.toString() + „\n“);
}
After
saving the changed files it is time again to see an intermediate
result.
Please remind to save the code just typed before running
the application again by selecting
>Run>Run
As>Java Application.
Do not forget to 'Select Input File'
and select the file with the SQL-commands !
Click the button
'Start
SQL execution' thereafter.
After connecting to the database, the
lines of the input-file and the 'answers' from the database are
displayed.
The
result of the shown screenshot should look like this:
top.
Next
Step: