SQL Table Array Data Population for MySQL,DB2 and Oracle

[quantumlady]

Hi, All.

I am working on a project with another student that will be used on Oracle, MySWL and DB2 databases. We have completed all the basic functions, but I would like to see if we can improve on one aspect of the code.

Scenario: There are 5 bank tables
private final String[]
tableList = {"account" , "branch", "customer", "depositor" , "loan", "borrower"} ;
There is a string array for the table schemas

private final String[]
tableSchema = {"CREATE TABLE ACCOUNT( BRANCH_NAME CHAR(10),ACCOUN T_NUMBER CHAR(5) ,BALANCE DECIMAL(3,0))",
"CREATE TABLE BRANCH (BRANCH_NAME CHAR(10),BRANCH _CITY CHAR(15), ASSETS DECIMAL(10,2))" ,
"CREATE TABLE CUSTOMER (CUSTOMER_NAME CHAR(10),CUSTOM ER_STREET CHAR(20),CUSTOM ER_CITY CHAR(15))",
"CREATE TABLE DEPOSITOR (CUSTOMER_NAME CHAR(10),ACCOUN T_NUMBER CHAR(5))",
"CREATE TABLE LOAN (BRANCH_NAME CHAR(10),LOAN_N UMBER CHAR(4) ,AMOUNT DECIMAL(10,2))" ,
"CREATE TABLE BORROWER ( CUSTOMER_NAME CHAR(10), LOAN_NUMBER CHAR(4))" };

And there are string arrays for the information to be inserted in the tables.

private final String[][]
tableRow = {
// Account table data
{"'Downtown' ,'A-101',500",
"'Mianus', 'A-215', 700",
"'Perryridg e', 'A-102', 400",
"'Round Hill', 'A-305', 350",
"'Perryridg e', 'A-201', 900",
"'Redwood', 'A-222', 700",
"'Brighton' , 'A-217', 750"},
// Branch table data
{"'Downtown','B rooklyn', 9000000",
"'Redwood','Pal o Alto', 2100000",
"'Perryridge',' Horseneck',1700 000",
"'Mianus','Hors eneck', 400000",
"'Round Hill','Horsenec k',8000000",
"'Pownal','Benn ington',300000" ,
"'North Town','Rye',370 0000",
"'Brighton','Br ooklyn', 7100000"},
// Customer table data
{"'Jones' ,'Main' ,'Harrison'",
"'Smith' ,'North' ,'Rye'",
"'Hayes' ,'Main' ,'Harrison'",
"'Curry' ,'North' ,'Rye'",
"'Lindsay' ,'Park' ,'Pittsfield'",
"'Turner' ,'Putnam' ,'Stamford'",
"'Williams' ,'Nassau' ,'Princeton'",
"'Adams' ,'Spring' ,'Pittsfield'",
"'Johnson' ,'Alma' ,'Palo Alto'",
"'Glenn' ,'Sand' ,'Hill Woodside'",
"'Brooks' ,'Senator' ,'Brooklyn'",
"'Green' ,'Walnut' ,'Stamford'"},
// Depositor table data
{"'Hayes' ,'A-102'",
"'Johnson' ,'A-101'",
"'Johnson' ,'A-201'",
"'Jones' ,'A-217'",
"'Lindsay' ,'A-222'",
"'Smith' ,'A-215'",
"'Turner' ,'A-305'"},
// Loan table data
{"'Downtown' ,'L-17' ,'1000'",
"'Redwood' ,'L-23' ,'2000'",
"'Perryridg e' ,'L-15' ,'1500'",
"'Downtown' ,'L-14' ,'1500'",
"'Mianus' ,'L-93' ,'500'",
"'Round Hill' ,'L-11' ,'900'",
"'Perryridg e' ,'L-16' ,'1300'"},
// Borrower table data
{"'Adams' ,'L-16'",
"'Curry' ,'L-93'",
"'Hayes' ,'L-15'",
"'Jones' ,'L-17'",
"'Smith' ,'L-11'",
"'Smith' ,'L-23'",
"'Williams' ,'L-17'"}
};

The current code supports the user selecting what they want to do by command line commands. Ex. Create [tablename];Load [tablename];Drop [tablename], etc.

I have the code to create “ALL’ of the tables, which works fine

case CREATE:
{
if (op.length < 2)
{
System.out.prin tln("Error! can not " + op[0] + "table due to missing table name");
displayValidTab le(tableList);
break;
}
tableName = op[1];
curTableCode = getTableCode(ta bleName.toLower Case(), tableList);
switch (curTableCode){

case ALL:
for (int i=0;i < tableList.lengt h;i++){
dbase.executeSQ LdllQuery(table Schema[i]);
}
dbase.closeConn ection();
break;
Now here is my dilemma:

I would like to be able to use the current code structure to add a CASE ALL: to the loading of the information into the tables (see below) and to the dropping of all of the tables, without having the user name them individually.

Any ideas?

Load Table Code:

case LOAD:
{
if (op.length < 2)
{
System.out.prin tln("Error! can not load table due to missing table name");
displayValidTab le(tableList);
break;
}
tableName = op[1];
curTableCode = getTableCode(ta bleName.toLower Case(), tableList);
LoadTable (dbase, tableName, tableRow[curTableCode]);
dbase.closeConn ection();
break;
}
private void LoadTable(Dbase Bean dbasel, String tableName, String tableRow[]){
dbasel.executeS QLdllQuery("DEL ETE FROM " + tableName);
for (int i=0; i < tableRow.length ; i++)
{
dbasel.executeS QLdllQuery("INS ERT INTO " + tableName + " VALUES (" + tableRow[i]+")");
}

DROP TABLE code:
case DROP:
{
if (op.length < 2)
{
System.out.prin tln("Error! can not create table due to missing table name");
displayValidTab le(tableList);
break;
}
tableName = op[1];
dropTable (dbase, tableName);
dbase.closeConn ection();
break;
}
private void dropTable(Dbase Bean dbasel, String tableName){
dbasel.executeS QLdllQuery("DRO P TABLE " + tableName);
}

Thank you for any help you can provide.