package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Product;
import ModelLayer.Supplier;
public class DBSupplier implements IFDBSupplier
{
private Connection con;
/** Creates a new instance of DBEmployee */
public DBSupplier() {
con = DBConnection.getInstance().getDBcon();
}
public int insertSupplier(Supplier sup) throws Exception
{
int nextSupplierID = GetMax.getMaxID("Select max(suplierID) from supliers");
nextSupplierID = nextSupplierID + 1;
System.out.println("next supplierID = " + nextSupplierID);
int rc = -1;
String query="INSERT INTO Supliers(suplierID, name, address ,country ,phoneNumber, email) VALUES("+
nextSupplierID + ",'" +
sup.getName() + "','" +
sup.getAddress()+"','" +
sup.getCountry()+"','"+
sup.getPhoneNumber()+"','"+
sup.getEmail() + "')";
System.out.println("insert : " + query);
try{ //
Statement stmt = con.createStatement();
stmt.setQueryTimeout(5);
rc = stmt.executeUpdate(query);
stmt.close();
}//end try
catch(SQLException ex){
System.out.println("Suplier can't be added");
throw new Exception ("Employee is not inserted correct");
}
return(rc);
}
public int updateSupplier(Supplier sup)
{
Supplier supObj = sup;
int rc=-1;
String query="UPDATE supliers SET "+
"Name='"+ supObj.getName()+"', "+
"Address ='"+ supObj.getAddress() + "', " +
"Country ='"+ supObj.getCountry() + "', " +
"PhoneNumber ='"+ supObj.getPhoneNumber()+ "', " +
"Email ='"+ supObj.getEmail()+ "' " +
" WHERE Name= '"+ supObj.getName()+"'";
System.out.println("Update query:" + query);
try{ // update employee
Statement stmt = con.createStatement();
stmt.setQueryTimeout(5);
rc = stmt.executeUpdate(query);
stmt.close();
}//slut try
catch(Exception ex){
System.out.println("Update exception in supliers db: "+ex);
}
return(rc);
}
public int deleteSupplier(String Name)
{
int rc=-1;
String query="DELETE FROM supliers WHERE name = '" +
Name + "'";
System.out.println(query);
try{ // delete from employee
Statement stmt = con.createStatement();
stmt.setQueryTimeout(5);
rc = stmt.executeUpdate(query);
stmt.close();
}//slut try
catch(Exception ex){
System.out.println("Delete exception in supplier db: "+ex);
}
return(rc);
}
//Singelwhere is used when we only select one employee
private Supplier singleWhere(String wClause, boolean retrieveAssociation)
{
ResultSet results = null;
Supplier supObj = new Supplier();
String query = buildQuery(wClause);
System.out.println(query);
try{ // read the employee from the database
Statement stmt = con.createStatement();
stmt.setQueryTimeout(5);
results = stmt.executeQuery(query);
}
catch(Exception exp)
{
System.out.println("error");
}
try {
if( results.next() ){
supObj = buildSupplier(results);
return supObj;}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return supObj;
}
//method to build the query
private String buildQuery(String wClause)
{
String query="SELECT * FROM Supliers";
if (wClause.length()>0)
query=query+" WHERE "+ wClause;
return query;
}
private Supplier buildSupplier(ResultSet results)
{ Supplier supObj = new Supplier();
try{ // the columns from the table emplayee are used
supObj.setSupplierID(results.getInt("SuplierID"));
supObj.setName(results.getString("Name"));
supObj.setAddress(results.getString("Address"));
supObj.setCountry(results.getString("Country"));
supObj.setPhoneNumber(results.getString("phoneNumber"));
supObj.setEmail(results.getString("Email"));
}
catch(Exception e)
{
System.out.println("error in building the supplier object");
}
return supObj;
}
public Supplier searchSupplierName(String name, boolean retrieveAssociation)
{
String wClause = "name = '" + name+"'";
return singleWhere(wClause, retrieveAssociation);
}
public Supplier findSupplier(int id, boolean retriveAssociation) {
String wClause = "SuplierID = '" + id + "'";
System.out.println("SearchProduct " + wClause);
return singleWhere(wClause, retriveAssociation);
}
}