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),ACCOUNT_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),CUSTOMER_STREET CHAR(20),CUSTOMER_CITY CHAR(15))",
"CREATE TABLE DEPOSITOR (CUSTOMER_NAME CHAR(10),ACCOUNT_NUMBER CHAR(5))",
"CREATE TABLE LOAN (BRANCH_NAME CHAR(10),LOAN_NUMBER 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",
"'Perryridge', 'A-102', 400",
"'Round Hill', 'A-305', 350",
"'Perryridge', 'A-201', 900",
"'Redwood', 'A-222', 700",
"'Brighton', 'A-217', 750"},
// Branch table data
{"'Downtown','Brooklyn', 9000000",
"'Redwood','Palo Alto', 2100000",
"'Perryridge','Horseneck',1700000",
"'Mianus','Horseneck', 400000",
"'Round Hill','Horseneck',8000000",
"'Pownal','Bennington',300000",
"'North Town','Rye',3700000",
"'Brighton','Brooklyn', 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'",
"'Perryridge' ,'L-15' ,'1500'",
"'Downtown' ,'L-14' ,'1500'",
"'Mianus' ,'L-93' ,'500'",
"'Round Hill' ,'L-11' ,'900'",
"'Perryridge' ,'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.println("Error! can not " + op[0] + "table due to missing table name");
displayValidTable(tableList);
break;
}
tableName = op[1];
curTableCode = getTableCode(tableName.toLowerCase(), tableList);
switch (curTableCode){

case ALL:
for (int i=0;i < tableList.length;i++){
dbase.executeSQLdllQuery(tableSchema[i]);
}
dbase.closeConnection();
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.println("Error! can not load table due to missing table name");
displayValidTable(tableList);
break;
}
tableName = op[1];
curTableCode = getTableCode(tableName.toLowerCase(), tableList);
LoadTable (dbase, tableName, tableRow[curTableCode]);
dbase.closeConnection();
break;
}
private void LoadTable(DbaseBean dbasel, String tableName, String tableRow[]){
dbasel.executeSQLdllQuery("DELETE FROM " + tableName);
for (int i=0; i < tableRow.length; i++)
{
dbasel.executeSQLdllQuery("INSERT INTO " + tableName + " VALUES (" + tableRow[i]+")");
}

DROP TABLE code:
case DROP:
{
if (op.length < 2)
{
System.out.println("Error! can not create table due to missing table name");
displayValidTable(tableList);
break;
}
tableName = op[1];
dropTable (dbase, tableName);
dbase.closeConnection();
break;
}
private void dropTable(DbaseBean dbasel, String tableName){
dbasel.executeSQLdllQuery("DROP TABLE " + tableName);
}

Thank you for any help you can provide.