/** @param args the command line arguments */
public static void main(String[] args) {
// TODO code application logic here
DataBaseConnection dbc = new DataBaseConnection();
dbc.getConnection();
DatabaseOps ops = new DatabaseOps();
int id = ops.insertCompetitionRecord("rach");
System.out.println("id: " + id);
List<String> l = ops.getAllCompetitionRecords();
for (String s : l) {
System.out.println(s);
}
}
public boolean addMarketingOfficer(MarketingOfficer marketingOfficer)
throws SQLException, ClassNotFoundException {
Connection connection = DataBaseConnection.getConnection();
Statement createStatement = connection.createStatement();
String sql =
"INSERT INTO MARKETINGOFFICER (marketingofficerid,name,nic,address,telno) "
+ "VALUES ('"
+ marketingOfficer.getMarketingOfficerId()
+ "','"
+ marketingOfficer.getName()
+ "','"
+ marketingOfficer.getNic()
+ "','"
+ marketingOfficer.getAddress()
+ "','"
+ marketingOfficer.getTelNo()
+ "')";
int res = createStatement.executeUpdate(sql);
if (res > 0) {
return true;
} else {
return false;
}
}
public LeasedVehicle searchClientAndLeasedVehicleByEngineNO(String searchText)
throws SQLException, ClassNotFoundException {
Connection connection = DataBaseConnection.getConnection();
Statement createStatement = connection.createStatement();
String sql = "";
ResultSet executeQuery = createStatement.executeQuery(sql);
LeasedVehicle leasedVehicle = new LeasedVehicle();
while (executeQuery.next()) {
leasedVehicle.setLeasingNo(executeQuery.getString("leasingNo"));
leasedVehicle.setChassisNo(executeQuery.getString("chassisno"));
leasedVehicle.setColour(executeQuery.getString("colour"));
leasedVehicle.setCylinderCapacity(executeQuery.getDouble("cylindercapacity"));
leasedVehicle.setEngineNo(executeQuery.getString("engineno"));
leasedVehicle.setEstimatedPrice(executeQuery.getDouble("estimatedprice"));
leasedVehicle.setFuelUsed(executeQuery.getDouble("fuelused"));
leasedVehicle.setLicensedDate(executeQuery.getString("licenseddate"));
leasedVehicle.setManufacturedYear(executeQuery.getInt("manufacturedyear"));
leasedVehicle.setModelAndName(executeQuery.getString("modelandname"));
leasedVehicle.setRegistrationNo(executeQuery.getString("registrationo"));
leasedVehicle.setVehicleCategoryId(executeQuery.getString("vehiclecategoryid"));
leasedVehicle.setWheelBase(executeQuery.getDouble("wheelbase"));
}
Client client = leasingDBAccess.searchClientByLeasingNo(leasedVehicle.getLeasingNo());
leasedVehicle.setClientDetails(client);
return leasedVehicle;
}
public boolean addLeasedVehicle(LeasedVehicle leasedVehicle)
throws SQLException, ClassNotFoundException, FileNotFoundException {
Connection connection = DataBaseConnection.getConnection();
PreparedStatement statement =
connection.prepareStatement(
"INSERT INTO LEASEDVEHICLE(leasingno,vehiclecategoryid,modelandname,chassisno,registrationno,engineno,manufacturedyear,estimatedprice,colour,fuelused,cylindercapacity,wheelbase,licenseddate,vehiclephoto) "
+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
statement.setString(1, leasedVehicle.getLeasingNo());
statement.setString(2, leasedVehicle.getVehicleCategoryId());
statement.setString(3, leasedVehicle.getModelAndName());
statement.setString(4, leasedVehicle.getChassisNo());
statement.setString(5, leasedVehicle.getRegistrationNo());
statement.setString(6, leasedVehicle.getEngineNo());
statement.setInt(7, leasedVehicle.getManufacturedYear());
statement.setDouble(8, leasedVehicle.getEstimatedPrice());
statement.setString(9, leasedVehicle.getColour());
statement.setDouble(10, leasedVehicle.getFuelUsed());
statement.setDouble(11, leasedVehicle.getCylinderCapacity());
statement.setDouble(12, leasedVehicle.getWheelBase());
statement.setString(13, leasedVehicle.getLicensedDate());
File f = leasedVehicle.getVehiclePhoto();
FileInputStream fis = new FileInputStream(f);
statement.setBinaryStream(14, (InputStream) fis, (int) (f.length()));
int res = statement.executeUpdate();
if (res == 1) {
return true;
} else {
return false;
}
}
/** Test of delete method, of class ClientHDao. */
@Test
public void testDelete() {
System.out.println("delete");
dao.delete("1");
String sql = "SELECT * FROM audit";
int numberOfRecords = dbConn.getNumberOfRecords(sql);
assertEquals(numberOfRecords, 0);
}
public ArrayList<String> getAllCategoryIds() throws SQLException, ClassNotFoundException {
Connection connection = DataBaseConnection.getConnection();
Statement createStatement = connection.createStatement();
String sql = "SELECT vehiclecategoryid FROM VEHICLECATEGORY ORDER BY vehiclecategoryid;";
ResultSet set = createStatement.executeQuery(sql);
ArrayList<String> categories = new ArrayList<String>();
while (set.next()) {
categories.add(set.getString("vehiclecategoryid"));
}
return categories;
}
public ArrayList<String> getMarketingOfficerIds() throws SQLException, ClassNotFoundException {
Connection connection = DataBaseConnection.getConnection();
Statement createStatement = connection.createStatement();
String sql = "select marketingofficerid from marketingofficer";
ResultSet executeQuery = createStatement.executeQuery(sql);
ArrayList<String> moi = new ArrayList<>();
while (executeQuery.next()) {
moi.add(executeQuery.getString("marketingofficerid"));
}
return moi;
}
public boolean updateLesaedVehicleWithSearch(String searchColumn, String searchKey)
throws SQLException, ClassNotFoundException {
Connection connection = DataBaseConnection.getConnection();
Statement createStatement = connection.createStatement();
String sql = "";
int res = createStatement.executeUpdate(sql);
if (res == 1) {
return true;
} else {
return false;
}
}
public VehicleCategory searchCategory(String searchText)
throws SQLException, ClassNotFoundException {
Connection connection = DataBaseConnection.getConnection();
Statement createStatement = connection.createStatement();
String sql =
"SELECT description FROM VEHICLECATEGORY where vehiclecategoryid = '" + searchText + "';";
ResultSet set = createStatement.executeQuery(sql);
VehicleCategory category = new VehicleCategory();
while (set.next()) {
category.setDescription(set.getString("description"));
}
return category;
}
/** Test of insert method, of class ClientHDao. */
@Test
public void testInsert() {
System.out.println("insert");
Audit c = new Audit();
c.setIdaudit("2");
c.setDateaudit(new Date(2000 - 1 - 1));
c.setHeuraudit(200);
c.setTypemodification("tt");
c.setTypeoperation("ddd");
c.setUserid("1");
c.setUsername("ddd");
dao.insert(c);
String sql = "SELECT * FROM audit";
int numberOfRecords = dbConn.getNumberOfRecords(sql);
assertEquals(numberOfRecords, 2);
}
public MarketingOfficer searchMarketingOfficer(String marketingOfficer)
throws SQLException, ClassNotFoundException {
Connection connection = DataBaseConnection.getConnection();
Statement createStatement = connection.createStatement();
String sql =
"select * from marketingofficer where marketingofficerid='" + marketingOfficer + "'";
System.out.println(sql);
ResultSet executeQuery = createStatement.executeQuery(sql);
MarketingOfficer mo = new MarketingOfficer();
while (executeQuery.next()) {
mo.setName(executeQuery.getString("name"));
mo.setNic(executeQuery.getString("nic"));
mo.setAddress(executeQuery.getString("address"));
mo.setTelNo(executeQuery.getString("telNo"));
}
return mo;
}
public boolean addVehicleCAtegory(VehicleCategory vehicleCategory)
throws SQLException, ClassNotFoundException {
Connection connection = DataBaseConnection.getConnection();
Statement createStatement = connection.createStatement();
String sql =
"INSERT INTO VEHICLECATEGORY(vehiclecategoryid, description) values('"
+ vehicleCategory.getVehicleCategoryId()
+ "','"
+ vehicleCategory.getDescription()
+ "');";
int res = createStatement.executeUpdate(sql);
if (res == 1) {
return true;
} else {
return false;
}
}
private void setUpRecord() {
String sql =
"INSERT INTO audit VALUES ('1','XX','*****@*****.**','0677889988','0677889988','2000-12-2',12)";
dbConn.execute(sql);
}
private void deleteAllAudits() {
String sql = "DELETE FROM audit";
dbConn.execute(sql);
}
