This project will involve improving the structure of this basic database: testDB
ID: 3748425 • Letter: T
Question
This project will involve improving the structure of this basic database: testDB.zip and automating the creation of a database table by extracting the table layout from attributes of a class. To do this, we will utilize the Java reflection API. The example program is poorly structured since all functionality is in main(). Also, the program must be run from a command prompt, which is inconvenient.Your first task will be to refactor this example into a more modularized program with reusable methods to create a table in the database, to add records to the table, and to issue basic queries on the database and to set up the program so that it does not have to run from the command line. YOu may use the derby.jar provided. Your user's manual must be very clear on how to set up your program so that it will work. You will create a class Vehicle. The class should have five instance fields: make (Chevy, Ford, Toyota, Nissan, Hyundai) model (compact, intermediate, fullSized, SUV) weight (double), engineSize (double) and import (boolean). You should implement this class utilizing the knowledge you gained in Intermediate Programming, such as setting instance fields with mutators (which, for example will ensure that the numeric values are not negative numbers). You will use the Reflection interface to analyze all the instance fields in class Vehicle and to issue a command to create a table with the proper fields and proper data types for all fields. Your program will create a log file named "dbOperations.log" that notes all major database events in this process (all table creation, addition of values, queries, table deletions, etc). Note that the object oriented concept of composition, in which one class is composed of instances of another class, and classes that have containers quickly complicate the process of performing this type of task. Furthermore, the larger the set of data types we employ, the larger the overall problem we are addressing becomes. Consequently, we will limit data types our program can manage to use of ints, booleans, doubles and Strings. However, if your program encounters a field type that is none of the ones your program must handle, it must generate a log entry providing details of the field in the class that was not created in the database. Your program will also automate creation of a number of instances of vehicles. Your program should create ten vehicles by randomly selecting makes and models from the above enumerations, and randomly select weights between 1500 and 4000 pounds. Weights must be such that compact cars (1500-2000) weigh less than intermediate cars (2000-2500) and intermediate cars weigh less than the rest. SUVs, full-sized cars, pickups and vans are all in the same weight range (2500-4000). Toyota, Nissan, and Hyundai are imports. When the project runs: When your program runs, it will analyze class Vehicle using the reflection interface to extract the instance fields and it will use this information to create a SQL command to create the database table. It will issue that command. After creating the table, it will create the 10 vehicle instances. It will then go through the vehicle instances, extracting the values of the fields (again using reflection), create SQL commands to add values to the table, and issue the SQL commands to insert the data. Finally, the program will issue three SQL queries to display: all information about all the vehicles that have been stored in the database. all vehicles that weigh more than 2500 pounds all imported SUVs Your program will create a log entry for each SQL operation performed. At the end, your program will read and display the log file. Your program should run by executing a batch file. You may have to "unpackage" your source files for everything to work. Be sure you properly locate any input files.
Explanation / Answer
Database.java
import java.io.IOException;
import java.lang.reflect.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
public class Database
{
Log log = new Log();
Reflection reflect;
GenerateVehicle genVehicle = new GenerateVehicle();
ArrayList<Vehicle> vehicles = new ArrayList();
ArrayList<Field> fields;
Field[] field;
Statement stat;
ResultSet result;
Connection conn;
private String sqlStatement;
private String tableName;
private String paramName;
private String paramType;
private static int colCount;
private boolean isBadVar;
public Database()
{
this.sqlStatement = "";
this.tableName = "";
stat = null;
conn = null;
colCount = 0;
}
public Database(String tableName)
{
this.sqlStatement = "";
setTableName(tableName);
stat = null;
conn = null;
colCount = 0;
for(int i = 0; i < 10; i++)
{
vehicles.add(new Vehicle(genVehicle));
}
reflect = new Reflection(vehicles.get(0));
field = reflect.getFieldType();
fields = new ArrayList<Field>(Arrays.asList(field));
}
public Statement openDB()
{
try
{
SimpleDataSource.init("database.properties");
conn = SimpleDataSource.getConnection();
stat = conn.createStatement();
}
catch (ClassNotFoundException | IOException | SQLException e)
{
System.out.println("Could not open Database");
e.printStackTrace();
}
return stat;
}
public void closeDB()
{
try
{
conn.close();
log.closeFile();
}
catch (SQLException e)
{
System.out.println("Error closing Database");
e.printStackTrace();
}
}
public void createTable()
{
setSqlStatement("CREATE TABLE " + getTableName());
}
public void dropTable()
{
setSqlStatement("DROP TABLE " + getTableName());
log.writeToFile(getSqlStatement());
try
{
stat.execute(getSqlStatement());
}
catch (SQLException e) {
System.out.println(" Initial drop failed ");
log.writeToFile("ERROR: Initial drop failed: " + getSqlStatement());
}
}
public void buildTable()
{
for(int i = 0; i < fields.size(); i++)
{
fields.get(i).setAccessible(true);
isBadVar = false;
if(fields.get(i).toString().contains("String"))
{
paramName = fields.get(i).getName();
paramType = "CHAR(20)";
}
else if(fields.get(i).toString().contains("double"))
{
paramName = fields.get(i).getName();
paramType = "DECIMAL(7, 2)";
}
else if(fields.get(i).toString().contains("boolean"))
{
paramName = fields.get(i).getName();
paramType = "CHAR(10)";
}
else
{
paramName = fields.get(i).getName();
paramType = fields.get(i).toString();
fields.remove(i);
isBadVar = true;
i--;
log.writeToFile("ERROR: " + paramName + " of is of an invalid data type (" + paramType + ")");
}
if(!isBadVar)
{
if(colCount == 0)
{
setSqlStatement(getSqlStatement() + " (" + paramName + " " + paramType + ")");
}
else
{
setSqlStatement("ALTER TABLE " + getTableName() + " ADD " + paramName + " " + paramType);
}
try
{
stat.execute(getSqlStatement());
log.writeToFile(getSqlStatement());
}
catch (SQLException e)
{
System.out.println("Error Adding Column");
log.writeToFile("ERROR: " + getSqlStatement());
e.printStackTrace();
}
finally
{
colCount++;
}
}
}
}
public void insert()
{
for(Vehicle vehicle : vehicles)
{
String value = "";
String values ="";
String colNames = "";
for(int i = 0; i < fields.size(); i++)
{
paramName = fields.get(i).getName();
if(i < (fields.size() - 1))
{
colNames += paramName + ", ";
}
else
{
colNames += paramName;
}
value = reflect.getValue(paramName, vehicle);
if(fields.get(i).toString().contains("String") || fields.get(i).toString().contains("boolean"))
{
if(i < (fields.size() - 1))
{
values += "'" + value + "', ";
}
else
{
values += "'" + value + "'";
}
}
else if(fields.get(i).toString().contains("double"))
{
double numb = Double.parseDouble(value);
value = String.format("%.2f", numb);
if(i < (fields.size() - 1))
{
values += value + ", ";
}
else
{
values += value;
}
}
}
setSqlStatement("INSERT INTO "+ getTableName() + " (" + colNames + ") VALUES (" + values + ")");
try
{
stat.execute(getSqlStatement());
log.writeToFile(getSqlStatement());
}
catch (SQLException e)
{
System.out.println("Error Inserting Row");
log.writeToFile("ERROR: " + getSqlStatement());
e.printStackTrace();
}
}
}
public void getResults() throws SQLException
{
setSqlStatement("SELECT * FROM " + getTableName());
log.writeToFile(getSqlStatement());
try
{
result = stat.executeQuery(getSqlStatement());
}
catch (SQLException e)
{
System.out.println("Error getting result set");
log.writeToFile("ERROR: " + getSqlStatement());
e.printStackTrace();
}
ResultSetMetaData rsm = result.getMetaData();
int cols = rsm.getColumnCount();
while(result.next())
{
for(int i = 1; i <= cols; i++)
{
System.out.print(result.getString(i)+" ");
}
System.out.println("");
}
}
public String getSqlStatement()
{
return sqlStatement;
}
public void setSqlStatement(String sqlStatement)
{
this.sqlStatement = sqlStatement;
}
public String getTableName()
{
return tableName;
}
public void setTableName(String tableName)
{
this.tableName = tableName;
}
}
GenerateVehicle.java
import java.util.Random;
public class GenerateVehicle
{
private int randNum;
private double randDouble;
private Random rand;
private String make;
private String model;
private double weight;
private double engineSize;
public GenerateVehicle()
{
randNum = 0;
randDouble = 0;
rand = new Random();
make = "";
model = "";
weight = 0;
engineSize = 0;
}
public String genMake()
{
randNum = rand.nextInt(5) + 1;
switch(randNum)
{
case 1:
make = "Chevy";
break;
case 2:
make = "Ford";
break;
case 3:
make = "Toyota";
break;
case 4:
make = "Nissan";
break;
case 5:
make = "Hyundai";
break;
default:
System.out.println(" Invalid Make ");
break;
}
return make;
}
public String genModel()
{
randNum = rand.nextInt(6) + 1;
switch(randNum)
{
case 1:
model = "compact";
break;
case 2:
model = "intermediate";
break;
case 3:
model = "fullSized";
break;
case 4:
model = "van";
break;
case 5:
model = "suv";
break;
case 6:
model = "pickup";
break;
default:
System.out.println(" Invalid Model ");
break;
}
return model;
}
public double genWeight()
{
double weight = 0;
double min = 0;
double max = 0;
if( model.compareTo("compact") == 0)
{
min = 1500;
max = 2000;
}
else if(model.compareTo("intermediate") == 0)
{
min = 2000;
max = 2500;
}
else if(model.compareTo("fullSized") == 0 || model.compareTo("van") == 0 || model.compareTo("pickup") == 0 || model.compareTo("suv") == 0)
{
min = 2500;
max = 4000;
}
else
{
System.out.println("Error creating weight for vehicle " + model);
}
randDouble = (rand.nextDouble() * (max - min));
weight = randDouble + min;
return weight;
}
public double genEngineSize()
{
randDouble = rand.nextDouble() * (6 - 1);
engineSize = randDouble + 1;
return engineSize;
}
public boolean genIsImport()
{
if(make.compareTo("Toyota") == 0 || make.compareTo("Nissan") == 0 || make.compareTo("Hyundai") == 0)
{
return true;
}
else
{
return false;
}
}
}
Log.java
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class Log {
private File fname;
private BufferedWriter file;
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");
LocalDateTime time = LocalDateTime.now();
public Log()
{
try
{
fname = new File("dbOperations.log");
if(fname == null)
{
System.out.println("Fail");
}
file = new BufferedWriter(new FileWriter(fname));
}
catch (IOException e)
{
e.printStackTrace();
}
}
public void writeToFile(String dbStatement)
{
try
{
file.write(time.now() + " " + dbStatement);
file.newLine();
}
catch (IOException e)
{
e.printStackTrace();
}
}
public void closeFile()
{
try
{
file.close();
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
Reflection.java
import java.lang.reflect.*;
import java.util.ArrayList;
public class Reflection<T>
{
private Class inputClass;
private ArrayList<String> stringArgs = new ArrayList();
public Reflection()
{
inputClass = null;
}
public Reflection(Object className)
{
inputClass = className.getClass();
}
public Field[] getFieldType()
{
Field[] fields = inputClass.getDeclaredFields();
for(int i = 0; i < fields.length; i++)
{
stringArgs.add(fields[i].toString());
}
return fields;
}
public String getValue(String paramName, Object object)
{
String value = "";
try
{
Field field = inputClass.getDeclaredField(paramName);
field.setAccessible(true);
try
{
value = field.get(object).toString();
}
catch (IllegalArgumentException | IllegalAccessException e)
{
e.printStackTrace();
}
}
catch (NoSuchFieldException | SecurityException e)
{
e.printStackTrace();
}
return value;
}
}
SimpleDataSource.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;
public class SimpleDataSource
{
private static String url;
private static String username;
private static String password;
public static void init(String fileName)
throws IOException, ClassNotFoundException
{
Properties props = new Properties();
FileInputStream in = new FileInputStream(fileName);
props.load(in);
String driver = props.getProperty("jdbc.driver");
url = props.getProperty("jdbc.url");
username = props.getProperty("jdbc.username");
if (username == null) username = "";
password = props.getProperty("jdbc.password");
if (password == null) password = "";
if (driver != null)
Class.forName(driver);
}
public static Connection getConnection() throws SQLException
{
return DriverManager.getConnection(url, username, password);
}
}
TestDB.java
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class TestDB
{
public static void main(String[] args) throws Exception
{
Database db = new Database("Vehicle");
ResultSet result;
db.openDB();
db.dropTable();
try
{
db.createTable();
db.buildTable();
db.insert();
System.out.println("After inserts ");
db.getResults();
}
finally
{
db.dropTable();
db.closeDB();
System.out.println(" Dropped Table " + db.getTableName() + ", closed connection and ending program");
}
}
}
Vehicle.java
import java.util.Random;
public class Vehicle {
private String make;
private String model;
private double weight;
private double engineSize;
private boolean isImport;
public Vehicle()
{
this.make = "";
this.model = "";
this.weight = 0;
this.engineSize = 0;
this.isImport = false;
}
public Vehicle(GenerateVehicle genVehicle)
{
this.make = genVehicle.genMake();
this.model = genVehicle.genModel();
this.weight = genVehicle.genWeight();
this.engineSize = genVehicle.genEngineSize();
this.isImport = genVehicle.genIsImport();
}
public String getMake()
{
return make;
}
public void setMake(String make)
{
this.make = make;
}
public String getModel()
{
return model;
}
public void setModel(String model)
{
this.model = model;
}
public double getWeight()
{
return weight;
}
public void setWeight(double weight)
{
this.weight = weight;
}
public double getEngineSize()
{
return engineSize;
}
public void setEngineSize(double engineSize)
{
this.engineSize = engineSize;
}
public boolean getIsImport()
{
return isImport;
}
public void setIsImport(boolean isImport)
{
this.isImport = isImport;
}
}
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.