package database;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import org.primefaces.model.TreeNode;
import authentification.User;
import de.klug_is.utils.database.ConnectionPool;
import de.klug_is.utils.database.KlugConnection;
import de.klug_is.utils.database.RowNotFoundException;
import de.klug_is.utils.database.SimpleSQLException;
import de.klug_is.utils.database.TransactionInfo;
/**
*
* @author dominik.schmucker
*
*/
public class DatabaseConnection {
ConnectionPool connectionPool = null;
TransactionInfo defaultTransactionInfo = null;
User user = new User();
DatabaseProperties dbProp = new DatabaseProperties();
/**
* Hier bauen wir uns die Connection zur DB auf, die Daten hierzu werden aus einem File geladen
* @return
*/
public Connection getConnection() {
if (defaultTransactionInfo == null) {
try {
defaultTransactionInfo = new TransactionInfo(
"", user.getHostName(), "Zugangsdatenverwaltung", "1.0");
} catch (UnknownHostException e) {
e.printStackTrace();
}
}
if (connectionPool == null) {
connectionPool = new ConnectionPool(dbProp.getConnectionString(), dbProp.getUser(),
dbProp.getPassword(), dbProp.getConnectionCount());
}
KlugConnection connection = connectionPool.getConnection();
connection.setTransactionInfo(defaultTransactionInfo);
return connection;
}
/**
* Um die Connection wieder zu releasen
* @param connection
*/
public void releaseConnection(Connection connection) {
if (connection != null) {
connectionPool.releaseConnection(connection);
}
}
/**
* zum committen
* @param connection
*/
public void commit(Connection connection) {
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* falls was schief läuft und wir rollbacken müssen
* @param connection
*/
public void rollback(Connection connection) {
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* wenn der User ein Theme wählt wird das in die DB eingetragen
* @param theme
* @param connection
* @throws SQLException
*/
public void setThemeInDB(String theme, Connection connection) throws SQLException {
UserThemePreferences themePreferences = new UserThemePreferences();
themePreferences.setThemeName(theme);
try {
themePreferences.setHostName(user.getHostName());
} catch (UnknownHostException e) {
e.printStackTrace();
}
themePreferences.insertNewKey(connection);
}
/**
* wenn der User sein Theme ändert, updaten wir nur seinen schon vorhandenen Datensatz
* @param theme
* @param connection
* @throws SimpleSQLException
*/
public void updateThemeInDB(String theme, Connection connection) throws SimpleSQLException {
UserThemePreferences themePreferences = new UserThemePreferences();
try {
themePreferences.selectByHostName(user.getHostName(), connection);
} catch (UnknownHostException e) {
e.printStackTrace();
}
themePreferences.setThemeName(theme);
themePreferences.update(connection);
}
/**
* hier schauen wir ob es den hostName in der DB schon gibt
* @param theme
* @throws UnknownHostException
*/
public void checkUpdateType(String theme) throws UnknownHostException {
UserThemePreferences themePreferences = new UserThemePreferences();
Connection connection = null;
try {
connection = getConnection();
long countByHostName = themePreferences.selectCountByHostName(
user.getHostName(), connection);
if (countByHostName == 0) {
setThemeInDB(theme, connection);
} else {
updateThemeInDB(theme, connection);
}
commit(connection);
} catch (SQLException e) {
e.printStackTrace();
rollback(connection);
} finally {
releaseConnection(connection);
}
}
/**
* beim Aufruf der Login-Seite holen wir uns das Theme aus der DB, danach wird es in die Session gespeichert
* um unnötigen Traffic zu vermeiden
* @return
*/
public String loadThemeFromDatabase() {
String theme = "";
Connection connection = null;
UserThemePreferences themePreferences = new UserThemePreferences();
try {
connection = getConnection();
themePreferences.selectByHostName(user.getHostName(), connection);
theme = themePreferences.getThemeName();
} catch (UnknownHostException e) {
e.printStackTrace();
} catch (SimpleSQLException e) {
e.printStackTrace();
theme = "cupertino";
} finally {
releaseConnection(connection);
}
return theme;
}
/**
* Methode die prüft ob es den hostNamen in der DB gibt
* @return
*/
public boolean checkIfHostNameExistsInDB() {
Connection connection = null;
UserThemePreferences themePreferences = new UserThemePreferences();
long hostNameCount = 0;
try {
connection = getConnection();
hostNameCount = themePreferences.selectCountByHostName(
user.getHostName(), connection);
} catch (UnknownHostException e) {
e.printStackTrace();
} catch (SimpleSQLException e) {
e.printStackTrace();
} finally {
releaseConnection(connection);
}
if (hostNameCount == 0) {
return false;
} else {
return true;
}
}
/**
* Die Methode für den DB-Insert, wenn die Gruppe eine Untergruppe ist
* @param description
* @param treeNode
*/
public Groups insertComponentInDatabase(String description, TreeNode treeNode) {
Groups groups = new Groups();
Groups newGroup = new Groups();
Groups parentGroup = new Groups();
Connection connection= null;
try {
connection = getConnection();
if (treeNode.getParent().toString().equals("Root")) {
parentGroup.selectByGroupsDescription(treeNode.toString(), connection);
} else {
parentGroup.selectByGroupsDescriptionAndParentGroup(treeNode.toString(),
treeNode.getParent().toString(), connection);
}
groups.selectByGroupsDescriptionAndParentGroup(
description, parentGroup.getDescription(), connection);
} catch (RowNotFoundException ex) {
try {
newGroup.setDescription(description);
newGroup.setParentgroupId(parentGroup.getGroupId());
newGroup.insertNewKey(connection);
commit(connection);
} catch (SimpleSQLException e) {
e.printStackTrace();
}
} catch (SimpleSQLException e) {
rollback(connection);
e.printStackTrace();
} finally {
releaseConnection(connection);
}
return newGroup;
}
public void getLeafsFromParentGroup(Groups groups) {
LeafsGroupsAssignment assign = new LeafsGroupsAssignment();
Connection connection = null;
try {
connection = getConnection();
LeafsGroupsAssignment[] assigns = assign.selectLeafIdByParentGroupId(
groups, connection);
for (LeafsGroupsAssignment leafsGroupsAssignment : assigns) {
leafsGroupsAssignment.setGroupId(groups.getGroupId());
leafsGroupsAssignment.setLeafId(leafsGroupsAssignment.getLeafId());
leafsGroupsAssignment.insert(connection);
commit(connection);
}
} catch (SimpleSQLException e) {
rollback(connection);
} finally {
releaseConnection(connection);
}
}
/**
* Methode für die Obergruppen, hier wird nämlich keine ParentGroupID gesetzt
* @param description
*/
public boolean insertComponentInDatabase(String description) {
boolean rootExists = false;
Groups groups = new Groups();
Connection connection = null;
try {
connection = getConnection();
groups.selectByGroupsDescription(description, connection);
if (groups.getGroupId() > 0) {
rootExists = true;
}
} catch (RowNotFoundException e) {
try {
groups.setDescription(description);
groups.insertNewKey(connection);
commit(connection);
} catch (SimpleSQLException e1) {
e1.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
rollback(connection);
} finally {
releaseConnection(connection);
}
return rootExists;
}
/**
* hier holen wir uns die Roots aus der DB
* @return
*/
public Groups[] loadTreeRootsFromDatabase() {
Groups groups = new Groups();
Groups[] rootGroups = null;
Connection connection = null;
try {
connection = getConnection();
rootGroups = groups.selectRootGroups(connection);
} catch (SimpleSQLException e) {
} finally {
releaseConnection(connection);
}
return rootGroups;
}
/**
* wenn wir die Roots haben holen wir uns dazu dann die Leafs
* @param groups
* @return
*/
public Groups[] loadTreeLeafsFromDatabase(Groups groups) {
Groups[] leafGroups = null;
Connection connection = null;
try {
connection = getConnection();
leafGroups = groups.selectLeafGroups(groups ,connection);
} catch (RowNotFoundException e) {
} catch (SimpleSQLException ex) {
ex.printStackTrace();
} finally {
releaseConnection(connection);
}
return leafGroups;
}
/**
* zum Umbenennen einer Komponente in der DB
* @param description
* @param selectedNode
*/
public void renameComponent(String description, TreeNode selectedNode) {
Groups groups = new Groups();
Connection connection = null;
try {
connection = getConnection();
if (!selectedNode.getParent().toString().equals("Root")) {
groups.selectByGroupsDescriptionAndParentGroup(description,
selectedNode.getParent().toString(), connection);
} else {
groups.selectByGroupsDescription(description, connection);
}
groups.setDescription(selectedNode.getData().toString());
groups.update(connection);
commit(connection);
} catch (SQLException e) {
rollback(connection);
e.printStackTrace();
} finally {
releaseConnection(connection);
}
}
/**
* wenn wir eine Komponente löschen soll diese zwar aus der Ansicht verschwinden, aber nicht wirklich
* in der DB weg sein, deshlab verschieben wir den Datensatz in eine Archiv-Tabelle
* @param selectedNode
*/
public void deleteComponentFromDatabase(TreeNode selectedNode) {
Groups groups = new Groups();
Groups_Archive archive = new Groups_Archive();
Connection connection = null;
try {
connection = getConnection();
if (!selectedNode.getParent().toString().equals("Root")) {
groups.selectByGroupsDescriptionAndParentGroup(selectedNode.toString(),
selectedNode.getParent().toString(), connection);
} else {
groups.selectByGroupsDescription(selectedNode.toString(), connection);
}
archive.setGroupId(groups.getGroupId());
archive.setParentGroupId(groups.getParentgroupId());
archive.setDescription(groups.getDescription());
archive.insertNewKey(connection);
groups.delete(connection);
commit(connection);
} catch (SQLException e) {
rollback(connection);
e.printStackTrace();
} finally {
releaseConnection(connection);
}
}
/**
* holt die vorhandenden Rechte aus der Datenbank
* @return
*/
public Rights[] selectRights() {
Rights rights = new Rights();
Connection connection = null;
Rights[] availableRights = null;
try {
connection = getConnection();
availableRights = rights.selectAllRightsTypes(connection);
} catch (SimpleSQLException e) {
e.printStackTrace();
} finally {
releaseConnection(connection);
}
return availableRights;
}
/**
* zum Laden der Programme für die Classpath-Konfiguration
* @return
*/
public Programs[] loadProgramsFromDB() {
Programs programs = new Programs();
Connection connection = null;
Programs[] allPrograms = null;
try {
connection = getConnection();
allPrograms = programs.selectAllPrograms(connection);
} catch (SimpleSQLException e) {
e.printStackTrace();
} finally {
releaseConnection(connection);
}
return allPrograms;
}
/**
* zum Erstellen einer neuen Classpath-Zuordnung
* @param type
* @param path
* @return
*/
public Programs addClassPathToDB(String type, String path) {
Programs programs = new Programs();
Connection connection = null;
try {
connection = getConnection();
//erst einmal schauen wir nach ob es diesen Eintrag mit der
//Typbezeichnung schon einmal gibt
programs.selectByProgramType(type, connection);
sendErrorMessage("typeExistsMessage");
} catch (RowNotFoundException e) {
programs.setType(type);
programs.setPath(path);
try {
programs.insertNewKey(connection);
} catch (SimpleSQLException e1) {
e1.printStackTrace();
}
commit(connection);
} catch (SQLException e) {
sendErrorMessage(e.getMessage());
rollback(connection);
} finally {
releaseConnection(connection);
}
return programs;
}
/**
* Beim Bearbeiten des Classpaths wird die Änderung in der DB übernommen
* @param selectedProgram
*/
public void editClassPathInDB(IconToProgramView selectedProgram) {
Programs programs = new Programs();
Connection connection = null;
try {
connection = getConnection();
programs.setType(selectedProgram.getType());
programs.setPath(selectedProgram.getPath());
programs.update(connection);
commit(connection);
} catch (SQLException e) {
rollback(connection);
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
}
/**
* Programm wird gelöscht und in eine Archiv-Tabelle verschoben
* @param program
*/
public void deleteProgramFromDatabase(Programs program) {
Programs programs = new Programs();
Programs_Archive archive = new Programs_Archive();
Connection connection = null;
try {
connection = getConnection();
programs.selectByProgramType(program.getType(), connection);
archive.setProgramId(programs.getProgramId());
archive.setType(programs.getType());
archive.setPath(programs.getPath());
archive.insertNewKey(connection);
commit(connection);
programs.delete(connection);
commit(connection);
} catch (SQLException e) {
sendErrorMessage(e.getMessage());
e.printStackTrace();
rollback(connection);
} finally {
releaseConnection(connection);
}
}
public List<Leafs> selectLeafsFromDB(TreeNode selectedNode) {
List<Leafs> leafs = new ArrayList<Leafs>();
Leafs leaf;
LeafsGroupsAssignment[] allLeafAssigns = null;
LeafsGroupsAssignment assign = new LeafsGroupsAssignment();
Groups group = new Groups();
Connection connection = null;
try {
connection = getConnection();
//holen uns die GroupID
if (selectedNode.getParent().toString().equals("Root")) {
group.selectByGroupsDescription(selectedNode.toString(), connection);
} else {
group.selectByGroupsDescriptionAndParentGroup(
selectedNode.toString(), selectedNode.getParent().toString(), connection);
}
//in der Zuordnungstabelle laden wir die LeafIDs anhand der GroupIDs
allLeafAssigns = assign.selectLeafIdByGroupId(group, connection);
for (LeafsGroupsAssignment leafsGroupsAssignment : allLeafAssigns) {
//und dann füllen wir unsere Liste mit den Leafs
leaf = new Leafs();
leaf.selectLeafByLeafId(leafsGroupsAssignment, connection);
leafs.add(leaf);
}
} catch (SimpleSQLException e) {
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
return leafs;
}
public Groups selectGroupId(TreeNode selectedNodeDia) {
Groups groups = new Groups();
Connection connection = null;
try {
connection = getConnection();
if (selectedNodeDia.getParent().toString().equals("Root")) {
groups.selectByGroupsDescription(selectedNodeDia.toString(), connection);
} else {
groups.selectByGroupsDescriptionAndParentGroup(selectedNodeDia.toString(),
selectedNodeDia.getParent().toString(), connection);
}
} catch (SimpleSQLException e) {
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
return groups;
}
public Programs selectProgramByType(String type) {
Programs programs = new Programs();
Connection connection = null;
try {
connection = getConnection();
programs.selectByProgramType(type, connection);
} catch (SimpleSQLException e) {
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
return programs;
}
public Leafs insertLeafIntoDatabase(Programs program,
String descriptionDia, String userName, String password,
String url, String comment) {
Leafs leafs = new Leafs();
Connection connection = null;
try {
connection = getConnection();
leafs.setProgramId(program.getProgramId());
leafs.setDescription(descriptionDia);
leafs.setUserName(userName);
leafs.setPassword(password);
leafs.setUrl(url);
leafs.setCommentary(comment);
leafs.insertNewKey(connection);
commit(connection);
} catch (SimpleSQLException e) {
rollback(connection);
sendErrorMessage(e.getMessage());
e.printStackTrace();
} finally {
releaseConnection(connection);
}
return leafs;
}
public void insertIntoLeafsGroupAssignment(Groups group, Leafs leaf) {
LeafsGroupsAssignment assign = new LeafsGroupsAssignment();
Connection connection = null;
try {
connection = getConnection();
assign.setGroupId(group.getGroupId());
assign.setLeafId(leaf.getLeafId());
assign.insert(connection);
commit(connection);
} catch (SimpleSQLException e) {
rollback(connection);
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
}
public Rights selectRightByType(String selectedRight) {
Rights rights = new Rights();
Connection connection = null;
try {
connection = getConnection();
rights.selectByRightType(selectedRight, connection);
} catch (SimpleSQLException e) {
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
return rights;
}
public void insertIntoRightsAssignment(Rights right, Leafs leaf,
String ldapType, String selectedADContent) {
RightsAssignment rightsAssign = new RightsAssignment();
Connection connection = null;
try {
connection = getConnection();
rightsAssign.setRightId(right.getRightId());
rightsAssign.setRefId(leaf.getLeafId());
rightsAssign.setRefType("leaf");
if (ldapType.equals("Benutzer") || ldapType.equals("User")) {
rightsAssign.setActiveDirectoryType("user");
} else {
rightsAssign.setActiveDirectoryType("group");
}
rightsAssign.setActivedirectoryName(selectedADContent);
rightsAssign.insertNewKey(connection);
commit(connection);
} catch (SimpleSQLException e) {
rollback(connection);
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
}
private void sendErrorMessage(String errorMessage) {
FacesContext facesContext = FacesContext.getCurrentInstance();
ResourceBundle resource = ResourceBundle.getBundle("messages", facesContext.getViewRoot(
).getLocale());
FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_WARN, resource.getString("error"),
errorMessage);
FacesContext.getCurrentInstance().addMessage(null, message);
facesContext.getExternalContext().getFlash().setKeepMessages(true);
}
public Icon[] loadIconsFromDatabase() {
Connection connection = null;
Icon icon = new Icon();
Icon[] icons = null;
try {
connection = getConnection();
icons = icon.selectAllIcons(connection);
} catch (SimpleSQLException e) {
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
return icons;
}
public IconToProgramView[] loadProgramsAndIconsFromDB() {
Connection connection = null;
IconToProgramView[] programs = null;
IconToProgramView program = new IconToProgramView();
try {
connection = getConnection();
programs = program.selectAll(connection);
} catch (SimpleSQLException e) {
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
return programs;
}
public void addIconToDB(String description, Programs program) {
Connection connection = null;
Icon icon = new Icon();
try {
connection = getConnection();
icon.setDescription(description);
icon.setProgramId(program.getProgramId());
icon.insertNewKey(connection);
commit(connection);
} catch (SimpleSQLException e) {
rollback(connection);
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
}
public void deleteIconFromDB(Programs iconToProgram) {
Connection connection = null;
Icon icon = new Icon();
try {
connection = getConnection();
icon.selectByProgramId(iconToProgram, connection);
icon.delete(connection);
commit(connection);
} catch (SimpleSQLException e) {
rollback(connection);
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
}
public void editIconInDB(IconToProgramView selectedProgram) {
Connection connection = null;
Icon icon = new Icon();
Programs program = new Programs();
try {
connection = getConnection();
program.setProgramId(selectedProgram.getProgramId());
icon.selectByProgramId(program, connection);
icon.setDescription(selectedProgram.getDescription());
icon.update(connection);
commit(connection);
} catch (SimpleSQLException e) {
e.printStackTrace();
sendErrorMessage(e.getMessage());
} finally {
releaseConnection(connection);
}
}
}
