Skip to content

Instantly share code, notes, and snippets.

@nivir

nivir/MySQLDataLayer.java

Created October 22, 2013 23:27
Show Gist options
  • Save nivir/7109961 to your computer and use it in GitHub Desktop.
Save nivir/7109961 to your computer and use it in GitHub Desktop.
Download ZIP
Project Hospital DB Management
Raw
MySQLDataLayer.java
// TODO: These methods should take objects, not strings, when setting data.
// TODO: These methods should take ints, not strings, when getting data.
import java.sql.*;
import java.util.*;
public class MySQLDataLayer
{
private Connection connection;
private Model model;
/** Our constructor. Sets the `model' field and initializes the database.
*
* @param model the model
*/
public MySQLDataLayer(Model model)
{
this.model = model;
initialize();
}
/*
* MySQLDataLayer(Model model) { this.model = model; initialize(); } /**
* Loads the MySQL driver and retrieve a connection which we store in the
* `connection' field. @return whether or not we succeed
*/
public boolean initialize()
{
try
{
/*
* Loads the MySQL driver. More information about this is available
* at http://java.sun.com/j2se/1.4.2/docs/api/java/lang/Class.html.
*/
Class.forName("com.mysql.jdbc.Driver");
/*
* Set the necessary parameters (url, username and password) and use
* these to get a connection to the database.
*/
String url = "jdbc:mysql://shell.ituniv.org:3306/group43";
String username = "group43";
String password = "c87fcf20ae";
connection = DriverManager.getConnection(url, username, password);
return true;
}
/*
* This catches an exception if the class is not found. If this is the
* case, the user needs to install the MySQL classes.
*/
catch (ClassNotFoundException e)
{
System.err.println("MySQLDataLayer: Class `com.mysql.jdbc.Driver "
+ "is not found.");
System.err.println(e.getMessage());
return false;
}
// If a SQL exception occours, the following code gets executed.
catch (SQLException e)
{
System.err.println("MySQLDataLayer: Unknown SQL error.");
System.err.println(e.getMessage());
return false;
}
}
public List<Patient> getSearchResult(String firstName, String lastName)
{
ArrayList<Patient> list = new ArrayList<Patient>();
try
{
/*
* Prepare a SELECT statement, replace the question mark with the ID
* and create the object.
*/
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM patients WHERE first_name LIKE '%"
+ firstName + "%' AND last_name LIKE '%"+ lastName + "%'");
ResultSet result = statement.executeQuery();
while (result.next())
{
list.add(new Patient(result.getString("patient_id"), result
.getString("first_name"), result.getString("last_name"),null,null,null,null,null));
}
return list;
} catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public List<TreatmentPlan> getPatientTreatmentPlans(Patient patient)
{
ArrayList<TreatmentPlan> list = new ArrayList<TreatmentPlan>();
try
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM treatment_plans WHERE patient_id=? AND deleted=0 ORDER BY start_date DESC");
statement.setString(1, patient.getId() + "");
ResultSet result = statement.executeQuery();
while(result.next())
{
list.add(new TreatmentPlan(Integer.parseInt(result.getString("treatment_plan_id")), result.getString("name"), result.getString("start_date").substring(0,10), result.getString("end_date").substring(0,10), model));
}
return list;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public List<TreatmentCycle> getTreatmentCycles(TreatmentPlan treatmentPlan)
{
ArrayList<TreatmentCycle> list = new ArrayList<TreatmentCycle>();
try
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM treatment_cycles WHERE treatment_plan_id=? AND deleted=0 ORDER BY start_date");
statement.setString(1, treatmentPlan.getId() + "");
ResultSet result = statement.executeQuery();
while(result.next())
{
list.add(new TreatmentCycle(Integer.parseInt(result.getString("treatment_cycle_id")), result.getString("name"), result.getString("start_date").substring(0,10).substring(0,10), result.getString("end_date").substring(0,10), model));
}
return list;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public List<Dose> getDoses(TreatmentCycle treatmentCycle)
{
ArrayList<Dose> list = new ArrayList<Dose>();
try
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM doses WHERE treatment_cycle_id=? AND deleted=0 ORDER BY day_in_cycle");
statement.setString(1, treatmentCycle.getId() + "");
ResultSet result = statement.executeQuery();
while(result.next())
{
list.add(new Dose(Integer.parseInt(result.getString("dose_id")), Integer.parseInt(result.getString("treatment_cycle_id")), Integer.parseInt(result.getString("drug_id")), Integer.parseInt(result.getString("day_in_cycle")), result.getString("status").charAt(0), Float.parseFloat(result.getString("amount")), model));
}
return list;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public List<Drug> getDrugs()
{
ArrayList<Drug> list = new ArrayList<Drug>();
try
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
PreparedStatement statement = connection.prepareStatement("SELECT * FROM drugs");
ResultSet result = statement.executeQuery();
while(result.next())
{
list.add(new Drug(Integer.parseInt(result.getString("drug_id")), result.getString("name"), result.getString("type"), Float.parseFloat(result.getString("cost_per_unit"))));
}
return list;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public List<Test> getTests(TreatmentCycle treatmentCycle)
{
ArrayList<Test> list = new ArrayList<Test>();
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("select * from tests where treatment_cycle_id=? AND deleted=0 ORDER BY day_in_cycle");
statement.setString(1, Integer.toString(treatmentCycle.getId()));
ResultSet result = statement.executeQuery();
while(result.next())
{
PreparedStatement statementType = null;
/*
* Depending on the type of the test, we select from the
* appropriate table.
*/
if(result.getString("type").equals("blood"))
{
statementType = connection
.prepareStatement("select * from blood_tests where test_id = ?");
}
else if(result.getString("type").equals("bsa"))
{
statementType = connection
.prepareStatement("select * from bsa_tests where test_id = ?");
}
if(statementType != null)
{
statementType.setString(1, result.getString("test_id"));
ResultSet resultType = statementType.executeQuery();
if(resultType.next())
{
if(result.getString("type").equals("bsa"))
{
list.add(new BSATest(Integer.parseInt(result.getString("test_id")), Integer.parseInt(result.getString("treatment_cycle_id")),
Integer.parseInt(result.getString("day_in_cycle")), result.getString("status").charAt(0),
Float.parseFloat(result.getString("cost")),Integer.parseInt(resultType.getString("bsa_test_id")), Float.parseFloat(resultType.getString("height")),
Float.parseFloat(resultType.getString("weight")),model));
}
else if(result.getString("type").equals("blood"))
{
list.add(new BloodTest(Integer.parseInt(result.getString("test_id")), Integer.parseInt(result.getString("treatment_cycle_id")),
Integer.parseInt(result.getString("day_in_cycle")), result.getString("status").charAt(0),
Float.parseFloat(result.getString("cost")),Integer.parseInt(resultType.getString("blood_test_id")), Float.parseFloat(resultType.getString("wbc")),
Float.parseFloat(resultType.getString("hemoglobin")), Float.parseFloat(resultType.getString("platelets")), model));
}
else {
// This shouldn't happen, but just in case.
return null;
}
}
else
{
return null;
}
}
else
{
return null;
}
}
return list;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public List<OtherEvent> getOtherEvents(TreatmentPlan treatmentPlan)
{
ArrayList<OtherEvent> list = new ArrayList<OtherEvent>();
try
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM other_events WHERE treatment_plan_id=? AND deleted=0 ORDER BY start_date");
statement.setString(1, treatmentPlan.getId() + "");
ResultSet result = statement.executeQuery();
while(result.next())
{
list.add(new OtherEvent(Integer.parseInt(result.getString("other_event_id")), Integer.parseInt(result.getString("treatment_plan_id")),
result.getString("start_date").substring(0,10), result.getString("end_date").substring(0,10),
result.getString("type"), result.getString("description"),
result.getString("status").charAt(0), Float.parseFloat(result.getString("cost")), model));
}
return list;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public List<Problem> getProblems(TreatmentPlan treatmentPlan)
{
ArrayList<Problem> list = new ArrayList<Problem>();
try
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM problems WHERE treatment_plan_id=? AND deleted=0 ORDER BY date");
statement.setString(1, treatmentPlan.getId() + "");
ResultSet result = statement.executeQuery();
while(result.next())
{
list.add(new Problem(Integer.parseInt(result.getString("problem_id")),
Integer.parseInt(result.getString("treatment_plan_id")),
result.getString("date").substring(0,10),
result.getString("description"),
result.getString("type"), model));
}
return list;
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
/**
* Adds a patient to the database.
*
* @param patient_id the PatientID
* @param first_name the patient's first name, e.g. "John"
* @param last_name the patient's last name, e.g. "Doe"
* @param address the patient's address, e.g. "Doe Road 1"
* @param city the city the patient lives in, e.g. "Gothenburg"
* @param postal_code the patient's postal code, e.g. "12345"
* @param phone_number the patients phone number
* @return whether or not we succeeded
*/
public int addPatient(String patient_id, String first_name,
String last_name, String address, String city, String postal_code,
String phone_number)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into patients (patient_id, first_name, last_name, address, city, postal_code, phone_number) values(?,?,?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
statement.setString(1, patient_id);
statement.setString(2, first_name);
statement.setString(3, last_name);
statement.setString(4, address);
statement.setString(5, city);
statement.setString(6, postal_code);
statement.setString(7, phone_number);
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
/**
* Get the patient with PatientID matching the parameter.
*
* @param patient_id
* @return the `Patient' object, or null if failure
*/
public Patient getPatient(String patient_id)
{
try
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
PreparedStatement statement = connection
.prepareStatement("select * from patients where patient_id = ? LIMIT 1");
statement.setString(1, patient_id);
ResultSet result = statement.executeQuery();
if(result.next())
{
return new Patient(result.getString("patient_id"),
result.getString("first_name"),
result.getString("last_name"),
result.getString("address"),
result.getString("city"),
result.getString("postal_code"),
result.getString("phone_number"),
model);
}
else
{
return null;
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
/**
* Adds a treatment cycle.
*
* @param treatment_plan_id the TreatmentPlanID
* @param name the name of the treatment plan, e.g. <TODO>
* @param start_date the start date, e.g. "2007-11-29 09:00"
* @param end_date, e.g. "2007-12-20 09:00"
* @param status, the status, e.g. 1 (active) (TODO: Right?)
* @return whether or not we succeeded
*/
public int addTreatmentCycle(String treatment_plan_id, String name,
String start_date, String end_date)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into treatment_cycles (treatment_plan_id, name, start_date, end_date) values(?,?,?,?)");
statement.setString(1, treatment_plan_id);
statement.setString(2, name);
statement.setString(3, start_date);
statement.setString(4, end_date);
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
/**
* Get the treatment cycle with the TreatmentCycleID matching the
* parameter.
*
* @param treatment_cycle_id the TreatmentCycleID
* @return the `TreatmentCycle' object, or null if failure
*/
public TreatmentCycle getTreatmentCycle(String treatment_cycle_id)
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("select * from treatment_cycles where treatment_cycle_id = ?");
statement.setString(1, treatment_cycle_id);
ResultSet result = statement.executeQuery();
if(result.next())
{
return new TreatmentCycle(Integer.parseInt(result.getString("treatment_cycle_id")), result.getString("name"), result.getString("start_date").substring(0,10), result.getString("end_date").substring(0,10), model);
}
else
{
return null;
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
/**
* Adds a drug to the database.
*
* @param name the name of the drug
* @param type the drug type, I=Injection, P=Pill (TODO: Right?)
* @param cost_per_unit the cost per unit
* @return whether or not we succeeded
*/
public int addDrug(String name, String type, String cost_per_unit)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into treatment_cycles (name, type, cost_per_unit) values(?,?,?)");
statement.setString(1, name);
statement.setString(2, type);
statement.setString(3, cost_per_unit);
ResultSet rs = statement.getGeneratedKeys();
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
/**
* Get the drug with the DrugID matching the parameter.
*
* @param drug_id the DrugId
* @return the `Drug' object, or null if failure
*/
public Drug getDrug(String drug_id)
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("select * from drugs where drug_id = ?");
statement.setString(1, drug_id);
ResultSet result = statement.executeQuery();
if(result.next())
{
return new Drug(Integer.parseInt(result.getString("drug_id")), result.getString("name"), result.getString("type"), Float.parseFloat(result.getString("cost_per_unit")));
}
else
{
return null;
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
/**
* Adds a dose to the database.
*
* @param treatment_cycle_id the TreatmentCycleID
* @param drug_id the DrugID
* @param day_in_cycle the day in the cycle
* @param status the status (TODO: What?)
* @param amount how much the patient should take of the drug
* @return whether or not we succeeded
*/
public int addDose(String treatment_cycle_id, String drug_id,
String day_in_cycle, String status, String amount)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into doses (treatment_cycle_id, drug_id, day_in_cycle, status, amount) values(?,?,?,?,?)");
statement.setString(1, treatment_cycle_id);
statement.setString(2, drug_id);
statement.setString(3, day_in_cycle);
statement.setString(4, status);
statement.setString(5, amount);
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
/**
* Get a dose from the database.
*
* @param dose_id the DoseID
* @return the `Dose' object, or null if failure
*/
public Dose getDose(String dose_id)
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("select * from doses where dose_id = ?");
statement.setString(1, dose_id);
ResultSet result = statement.executeQuery();
if(result.next())
{
return new Dose(Integer.parseInt(result.getString("dose_id")), Integer.parseInt(result.getString("treatment_cycle_id")), Integer.parseInt(result.getString("drug_id")), Integer.parseInt(result.getString("day_in_cycle")), result.getString("status").charAt(0), Float.parseFloat(result.getString("amount")), model);
}
else
{
return null;
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
/**
* Adds a treatment plan to the database.
*
* @param patient_id the PatientID
* @param name the name of the treatment plan
* @param start_date the start date
* @param end_date the end date
* @return whether or not we succeeded
*/
public int addTreatmentPlan(String patient_id, String name,
String start_date, String end_date)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into treatment_plans (patient_id, name, start_date, end_date) values(?,?,?,?)");
statement.setString(1, patient_id);
statement.setString(2, name);
statement.setString(3, start_date);
statement.setString(4, end_date);
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
/**
* Get a treatment plan from the database.
*
* @param treatment_plan_id the TreatmentPlanID
* @return the `TreatmentPlan' object, or null if failure
*/
public TreatmentPlan getTreatmentPlan(String treatment_plan_id)
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("select * from treatment_plans where treatment_plan_id = ?");
statement.setString(1, treatment_plan_id);
ResultSet result = statement.executeQuery();
if(result.next())
{
return new TreatmentPlan(Integer.parseInt(result.getString("treatment_plan_id")), result.getString("name"), result.getString("start_date").substring(0,10), result.getString("end_date").substring(0,10), model);
}
else
{
return null;
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public int addBSATest(int treatmentCycleID, int dayInCycle, char status, float cost, float height, float weight)
{
int id = this.addTest(treatmentCycleID, dayInCycle, status, cost, "bsa");
if(id > 0)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into bsa_tests (test_id, height, weight) values(?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
statement.setString(1, Integer.toString(id));
statement.setString(2, Float.toString(height));
statement.setString(3, Float.toString(weight));
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next() && rs.getInt(1) > 0)
{
return id;
}
else
{
// Problem: We have crated the Test but not the specific test.
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
return id;
}
public int addBloodTest(int treatmentCycleID, int dayInCycle, char status, float cost, float wbc, float hemoglobin, float platelets)
{
int id = this.addTest(treatmentCycleID, dayInCycle, status, cost, "blood");
if(id > 0)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into blood_tests (test_id, wbc, hemoglobin, platelets) values(?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
statement.setString(1, Integer.toString(id));
statement.setString(2, Float.toString(wbc));
statement.setString(3, Float.toString(hemoglobin));
statement.setString(4, Float.toString(platelets));
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next() && rs.getInt(1) > 0)
{
return id;
}
else
{
// Problem: We have crated the Test but not the specific test.
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
return id;
}
private int addTest(int treatmentCycleID, int dayInCycle, char status, float cost, String type)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into tests (treatment_cycle_id, day_in_cycle, status, cost, type) values(?,?,?,?,?)");
statement.setString(1, Integer.toString(treatmentCycleID));
statement.setString(2, Integer.toString(dayInCycle));
statement.setString(3, Character.toString(status));
statement.setString(4, Float.toString(cost));
statement.setString(5, type);
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
/**
* Get a test from the database.
*
* @param test_id the TestID
* @return the `Test' object, or null if failure
*/
public Test getTest(String test_id)
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("select * from tests where test_id = ?");
statement.setString(1, test_id);
ResultSet result = statement.executeQuery();
if(result.next())
{
PreparedStatement statementType = null;
/*
* Depending on the type of the test, we select from the
* appropriate table.
*/
if(result.getString("type").equals("blood"))
{
statementType = connection
.prepareStatement("select * from blood_tests where test_id = ?");
}
else if(result.getString("type").equals("bsa"))
{
statementType = connection
.prepareStatement("select * from bsa_tests where test_id = ?");
}
if(statementType != null)
{
statementType.setString(1, test_id);
ResultSet resultType = statementType.executeQuery();
if(resultType.next())
{
if(result.getString("type").equals("bsa"))
{
return new BSATest(Integer.parseInt(result.getString("test_id")), Integer.parseInt(result.getString("treatment_cycle_id")),
Integer.parseInt(result.getString("day_in_cycle")), result.getString("status").charAt(0),
Float.parseFloat(result.getString("cost")),Integer.parseInt(resultType.getString("bsa_test_id")), Float.parseFloat(resultType.getString("height")),
Float.parseFloat(resultType.getString("weight")), model);
}
else if(result.getString("type").equals("blood"))
{
return new BloodTest(Integer.parseInt(result.getString("test_id")), Integer.parseInt(result.getString("treatment_cycle_id")),
Integer.parseInt(result.getString("day_in_cycle")), result.getString("status").charAt(0),
Float.parseFloat(result.getString("cost")),Integer.parseInt(resultType.getString("blood_test_id")), Float.parseFloat(resultType.getString("wbc")),
Float.parseFloat(resultType.getString("hemoglobin")), Float.parseFloat(resultType.getString("platelets")), model);
}
// This shouldn't happen, but just in case.
return null;
}
else
{
return null;
}
}
else
{
return null;
}
}
else
{
return null;
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
/**
* Adds an OtherEvent to the database.
*
* @param treatment_plan_id the TreatmentPlanID
* @param start_date the start date
* @param end_date the end date
* @param type the type of event
* @param description the description of the event
* @param status the status of the event (TODO: What?)
* @param cost the eventual cost of the event
* @return whether or not we succeeded
*/
public int addOtherEvent(String treatment_plan_id, String start_date,
String end_date, String type, String description, String status,
String cost)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into other_events (treatment_plan_id, start_date, end_date, type, description, status, cost) values(?,?,?,?,?,?,?)");
statement.setString(1, treatment_plan_id);
statement.setString(2, start_date);
statement.setString(3, end_date);
statement.setString(4, type);
statement.setString(5, description);
statement.setString(6, status);
statement.setString(7, cost);
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
/**
* Get an "other event" from the database.
*
* @param other_event_id the OtherEventID
* @return the `OtherEvent' object, or null if failure
*/
public OtherEvent getOtherEvent(String other_event_id)
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("select * from other_events where other_event_id = ?");
statement.setString(1, other_event_id);
ResultSet result = statement.executeQuery();
if(result.next())
{
return new OtherEvent(Integer.parseInt(result.getString("other_event_id")), Integer.parseInt(result.getString("treatment_plan_id")),
result.getString("start_date").substring(0,10), result.getString("end_date").substring(0,10),
result.getString("type"), result.getString("description"),
result.getString("status").charAt(0), Float.parseFloat(result.getString("cost")), model);
}
else
{
return null;
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
/**
* Adds a Problem to the database.
*
* @param treatment_plan_id the TreatmentPlanID
* @param date the date of the problem
* @param description the description of the problem
* @param type the type of the problem
* @return whether or not we succeeded
*/
public int addProblem(String treatment_plan_id, String date,
String description, String type)
{
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("insert into problems (treatment_plan_id, date, description, type) values(?,?,?,?)");
statement.setString(1, treatment_plan_id);
statement.setString(2, date);
statement.setString(3, description);
statement.setString(4, type);
statement.execute();
ResultSet rs = statement.getGeneratedKeys();
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return 0;
}
}
/**
* Get a problem from the database.
*
* @param problem_id the ProblemID
* @return the `Problem' object, or null if failure
*/
public Problem getProblem(String problem_id)
{
/*
* Prepare a SELECT statement, replace the question mark with the
* ID and create the object.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("select * from problems where problem_id = ?");
statement.setString(1, problem_id);
ResultSet result = statement.executeQuery();
if(result.next())
{
return new Problem(Integer.parseInt(result.getString("problem_id")),
Integer.parseInt(result.getString("treatment_plan_id")),
result.getString("date").substring(0,10),
result.getString("description"),
result.getString("type"),
model);
}
else
{
return null;
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
return null;
}
}
public boolean update(String table, String whereField, Object whereValue, String setField, Object setValue)
{
String whereString, setString;
if(whereValue instanceof String)
{
whereString = "'" + whereValue + "'";
}
else
{
whereString = whereValue + "";
}
if(setValue instanceof String)
{
setString = "'" + setValue + "'";
}
else
{
setString = setValue + "";
}
/*
* We prepare a statement and we put all the data that we want to store
* in the database in it.
*/
try
{
PreparedStatement statement = connection
.prepareStatement("UPDATE " + table + " SET " + setField + "=" + setString + " WHERE " + whereField + "=" + whereString);
if(statement.executeUpdate() > 0) return true;
else return false;
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return false;
}
}
public boolean removeObject(Object object)
{
try
{
PreparedStatement preparedStatement = null;
if(object instanceof Patient)
preparedStatement = connection.prepareStatement("UPDATE patients SET deleted=1 WHERE patient_id=" + ((Patient)object).getId());
else if(object instanceof TreatmentCycle)
preparedStatement = connection.prepareStatement("UPDATE treatment_cycles SET deleted=1 WHERE treatment_cycle_id=" + ((TreatmentCycle)object).getId());
else if(object instanceof Drug)
preparedStatement = connection.prepareStatement("UPDATE drugs SET deleted=1 WHERE drug_id=" + ((Drug)object).getId());
else if(object instanceof Dose)
preparedStatement = connection.prepareStatement("UPDATE doses SET deleted=1 WHERE dose_id=" + ((Dose)object).getId());
else if(object instanceof TreatmentPlan)
preparedStatement = connection.prepareStatement("UPDATE treatment_plans SET deleted=1 WHERE treatment_plan_id=" + ((TreatmentPlan)object).getId());
else if(object instanceof Test)
preparedStatement = connection.prepareStatement("UPDATE tests SET deleted=1 WHERE test_id=" + ((Test)object).getId());
else if(object instanceof OtherEvent)
preparedStatement = connection.prepareStatement("UPDATE other_events SET deleted=1 WHERE other_event_id=" + ((OtherEvent)object).getId());
else if(object instanceof Problem)
preparedStatement = connection.prepareStatement("UPDATE problems SET deleted=1 WHERE problem_id=" + ((Problem)object).getId());
if(preparedStatement != null)
{
if(preparedStatement.executeUpdate() > 0) return true;
else return false;
}
else return false;
}
catch (SQLException e)
{
System.err.println(e.getMessage());
return false;
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment