-
Thiemo Belmega authoredThiemo Belmega authored
DatabaseController.java 10.94 KiB
package de.unipotsdam.cs.toolup.database;
import de.unipotsdam.cs.toolup.exceptions.InvalidIdException;
import de.unipotsdam.cs.toolup.model.*;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
public class DatabaseController {
public static final String TABLE_NAME_FEATURE = "feature";
public static final String TABLE_NAME_CATEGORY = "category";
public static final String TABLE_NAME_APPLICATION = "application";
public static final String TABLE_NAME_APPLICATION_FEATURE = "application_has_feature";
public static final String TABLE_NAME_APPLICATION_CATEGORY = "application_belongs_to_category";
private static final String COLUMN_NAME_UUID = "uuid";
private static final String COLUMN_SUFFIX_UUID = "_" + COLUMN_NAME_UUID;
private static DatabaseController instance;
private SqlStatementFactory sqlStatementFactory;
protected DatabaseController() throws IOException, SQLException {
sqlStatementFactory = new SqlStatementFactory();
}
public static DatabaseController getInstance() throws IOException, SQLException {
if (instance == null) {
instance = new DatabaseController();
}
return instance;
}
/**
* Loads all BusinessObjects from a single table.
* @param tableName name of the table to load from.
* @return all the loaded BusinessObjects in a map from uuid to BusinessObject
* @throws SQLException
*/
public Map<String,BusinessObject> loadAllFrom(String tableName) throws SQLException {
PreparedStatement prepQuery = sqlStatementFactory.getStatementSelectAllFrom(tableName);
ResultSet res = prepQuery.executeQuery();
return BusinessObjectFactory.createSetOfBusinessObjectsFromAllResults(res);
}
/**
* Loads a single business object with the given uuid.
* The table to load from is evaluated from the id.
* @param uuid the uuid of the object to load.
* @return the loaded BusinessObject
* @throws SQLException
* @throws InvalidIdException
*/
public BusinessObject load(String uuid) throws SQLException, InvalidIdException {
String tableName = BusinessObject.getTableNameFromId(uuid);
PreparedStatement prepQuery = sqlStatementFactory.getStatementSelectByUuidFrom(tableName);
prepQuery.setString(1, uuid);
ResultSet res = prepQuery.executeQuery();
return BusinessObjectFactory.createBusinessObjectFromSingleResult(res);
}
public Set<String> loadRelatedBusinessObjectsForId(String tableName, String targetBusinessObjectType, String id) throws SQLException, InvalidIdException {
String criteriaBusinessObjectType = BusinessObject.getTableNameFromId(id);
PreparedStatement prepQuery = sqlStatementFactory.getSelectRelation(tableName, criteriaBusinessObjectType + COLUMN_SUFFIX_UUID);
prepQuery.setString(1, id);
ResultSet res = prepQuery.executeQuery();
return getAllIdsFromResultSet(targetBusinessObjectType + COLUMN_SUFFIX_UUID,
res);
}
private Set<String> getAllIdsFromResultSet(
String resultColumnName, ResultSet res) throws SQLException {
Set<String> relatedIds = new HashSet<>();
while (res.next()) {
relatedIds.add(res.getString(resultColumnName));
}
return relatedIds;
}
public Set<String> loadRelatedCategoriesForApp(String id) throws SQLException, InvalidIdException {
return loadRelatedBusinessObjectsForId(TABLE_NAME_APPLICATION_CATEGORY, TABLE_NAME_CATEGORY, id);
}
public Set<String> loadRelatedApplicationsForCat(String id) throws SQLException, InvalidIdException {
return loadRelatedBusinessObjectsForId(TABLE_NAME_APPLICATION_CATEGORY, TABLE_NAME_APPLICATION, id);
}
public Set<String> loadRelatedFeaturesForApp(String id) throws SQLException, InvalidIdException {
return loadRelatedBusinessObjectsForId(TABLE_NAME_APPLICATION_FEATURE, TABLE_NAME_FEATURE, id);
}
public Set<String> loadRelatedApplicationsForFeat(String id) throws SQLException, InvalidIdException {
return loadRelatedBusinessObjectsForId(TABLE_NAME_APPLICATION_FEATURE, TABLE_NAME_APPLICATION, id);
}
public Collection<String> loadSubCategories(String uuid) throws SQLException {
PreparedStatement prepQuery = sqlStatementFactory.getSelectSubcategories();
prepQuery.setString(1, uuid);
ResultSet res = prepQuery.executeQuery();
return getAllIdsFromResultSet(COLUMN_NAME_UUID, res);
}
public boolean checkIfExistsInDB(BusinessObject aBusinessObject) throws SQLException, InvalidIdException {
return checkIfExistsInDB(aBusinessObject.getUuid());
}
public boolean checkIfExistsInDB(String id) throws SQLException, InvalidIdException {
BusinessObject objectFromDB = load(id);
return !(objectFromDB instanceof NullBusinessObject);
}
public void storeToDatabase(BusinessObject aBusinessObject) throws SQLException, InvalidIdException {
boolean exists = checkIfExistsInDB(aBusinessObject);
if (exists) {
updateDatabase(aBusinessObject);
} else {
insertIntoDatabase(aBusinessObject);
}
}
private void insertIntoDatabase(BusinessObject aBusinessObject)
throws SQLException, InvalidIdException {
String tableName = BusinessObject.getTableNameFromId(aBusinessObject.getUuid());
insertBO(aBusinessObject, tableName);
insertRelations(aBusinessObject, tableName);
}
private void insertRelations(BusinessObject aBusinessObject,
String tableName) throws SQLException {
if (aBusinessObject.getRelatedBOs().isEmpty()) return;
switch (tableName) {
case TABLE_NAME_APPLICATION: {
Application app = (Application) aBusinessObject;
insertSingleRelationInto(TABLE_NAME_APPLICATION_FEATURE, app.getUuid(), 1, app.getRelatedFeatures());
insertSingleRelationInto(TABLE_NAME_APPLICATION_CATEGORY, app.getUuid(), 1, app.getRelatedCategories());
break;
}
case TABLE_NAME_CATEGORY: {
Category cat = (Category) aBusinessObject;
insertSingleRelationInto(TABLE_NAME_APPLICATION_CATEGORY, cat.getUuid(), 2, cat.getRelatedApplications());
break;
}
case TABLE_NAME_FEATURE: {
Feature feat = (Feature) aBusinessObject;
insertSingleRelationInto(TABLE_NAME_APPLICATION_FEATURE, feat.getUuid(), 2, feat.getRelatedApplications());
break;
}
default:
throw new UnsupportedOperationException();
}
}
/**
* @param tableName the name of the relation table
* @param uuid the BO thats relations are to be stored
* @param uuidColumnNumber column number of the BOs id in the table. 1 if BO is Application, 2 otherwise.
* @param relatedIds a collection of the uuid's of related BOs
* @throws SQLException
*/
private void insertSingleRelationInto(String tableName, String uuid,
int uuidColumnNumber, Collection<String> relatedIds) throws SQLException {
for (String foreignKey : relatedIds) {
PreparedStatement prepQuery;
prepQuery = sqlStatementFactory.getInsertRelation(tableName);
prepQuery.setString(uuidColumnNumber, uuid);
prepQuery.setString((uuidColumnNumber % 2) + 1, foreignKey);
prepQuery.executeUpdate();
}
}
//TODO fix duplication
private void updateSingleRelation(
String tableName, String uuid, int uuidColumnNumber,
Collection<String> relatedIds) throws SQLException {
for (String foreignKey : relatedIds) {
PreparedStatement prepQuery;
prepQuery = sqlStatementFactory.getInsertRelation(tableName);
prepQuery.setString(uuidColumnNumber, uuid);
prepQuery.setString((uuidColumnNumber % 2) + 1, foreignKey);
prepQuery.executeUpdate();
}
}
private void insertBO(BusinessObject aBusinessObject,
String tableName) throws SQLException {
PreparedStatement prepQuery; prepQuery = sqlStatementFactory.getInsertInto(tableName);
prepQuery.setString(1, aBusinessObject.getUuid());
prepQuery.setString(2, aBusinessObject.getTitle());
prepQuery.setString(3, aBusinessObject.getDescription());
prepQuery.executeUpdate();
}
private void updateDatabase(BusinessObject aBusinessObject)
throws SQLException, InvalidIdException {
String tableName = BusinessObject.getTableNameFromId(aBusinessObject.getUuid());
updateBO(aBusinessObject, tableName);
updateRelations(aBusinessObject, tableName);
}
private void updateRelations(BusinessObject aBusinessObject,
String tableName) throws SQLException {
if (aBusinessObject.getRelatedBOs().isEmpty()) return;
switch (tableName) {
case TABLE_NAME_APPLICATION: {
Application app = (Application) aBusinessObject;
updateSingleRelation(TABLE_NAME_APPLICATION_FEATURE, app.getUuid(), 1, app.getRelatedFeatures());
updateSingleRelation(TABLE_NAME_APPLICATION_CATEGORY, app.getUuid(), 1, app.getRelatedCategories());
break;
}
case TABLE_NAME_CATEGORY: {
Category cat = (Category) aBusinessObject;
updateSingleRelation(TABLE_NAME_APPLICATION_CATEGORY, cat.getUuid(), 2, cat.getRelatedApplications());
break;
}
case TABLE_NAME_FEATURE: {
Feature feat = (Feature) aBusinessObject;
updateSingleRelation(TABLE_NAME_APPLICATION_FEATURE, feat.getUuid(), 2, feat.getRelatedApplications());
break;
}
default:
throw new UnsupportedOperationException();
}
}
private void updateBO(BusinessObject aBusinessObject,
String tableName) throws SQLException {
PreparedStatement prepQuery;
prepQuery = sqlStatementFactory.getUpdate(tableName);
prepQuery.setString(1, aBusinessObject.getTitle());
prepQuery.setString(2, aBusinessObject.getDescription());
prepQuery.setString(3, aBusinessObject.getUuid());
prepQuery.executeUpdate();
}
public void deleteFromDatabase(String id) throws SQLException, InvalidIdException {
String tableName = BusinessObject.getTableNameFromId(id);
PreparedStatement prepQuery = sqlStatementFactory.getDeleteFrom(tableName);
prepQuery.setString(1, id);
prepQuery.executeUpdate();
}
}