NULL-Fehler behoben

6 files changed, 225 insertions, 229 deletions

diff --git a/Dozentenmodulserver/bin/server/ServerHandler.class b/Dozentenmodulserver/bin/server/ServerHandler.class
index cf70402f..0f7cee59 100644
--- a/Dozentenmodulserver/bin/server/ServerHandler.class
+++ b/Dozentenmodulserver/bin/server/ServerHandler.class
diff --git a/Dozentenmodulserver/bin/sql/SQL.class b/Dozentenmodulserver/bin/sql/SQL.class
index fb2abbc3..9392d9ff 100644
--- a/Dozentenmodulserver/bin/sql/SQL.class
+++ b/Dozentenmodulserver/bin/sql/SQL.class
diff --git a/Dozentenmodulserver/bin/util/XMLCreator.class b/Dozentenmodulserver/bin/util/XMLCreator.class
index fd8a3c2a..cfdf6004 100644
--- a/Dozentenmodulserver/bin/util/XMLCreator.class
+++ b/Dozentenmodulserver/bin/util/XMLCreator.class
diff --git a/Dozentenmodulserver/src/server/ServerHandler.java b/Dozentenmodulserver/src/server/ServerHandler.java
index 5744003a..0cafd182 100644
--- a/Dozentenmodulserver/src/server/ServerHandler.java
+++ b/Dozentenmodulserver/src/server/ServerHandler.java
@@ -28,7 +28,7 @@ import util.XMLCreator;
 
 public class ServerHandler implements Server.Iface {
 	static SQL sql = new SQL();
-	static Connection con = sql.getConnection();
+	
 	private static Logger log = Logger.getLogger(ServerHandler.class);
 
 	@Override
@@ -45,11 +45,11 @@ public class ServerHandler implements Server.Iface {
 		else{
 			user.setPath(Configuration.config.getAbsolute_path()+"/");
 		}
-		SQL sql = new SQL();
-		Connection con = sql.getConnection();
+		
+		
 		// check if folder temp exists
 		if (folderTempExists() == true) {
-			sql.writeFTPUser(con, user.getUserName(), user.getPassword());
+			sql.writeFTPUser(user.getUserName(), user.getPassword());
 			return user;
 		} else {
 			log.info(new Date() + " - returning null user");
@@ -112,26 +112,16 @@ public class ServerHandler implements Server.Iface {
 
 	@Override
 	public long DeleteFtpUser(String user) throws TException {
-		int ret = sql.DeleteUser(con, user);
-		return ret;
+		return sql.DeleteUser(user);
 	}
 
 	@Override
 	public String getPathOfImage(String image_id, String version)
-			throws TException {
+			throws TException {		
+		log.info(new Date() + " - successfully returned PathOfImage: "
+				+ sql.getPathOfImage(image_id, version));
+		return sql.getPathOfImage(image_id, version);
 
-		try {
-			ResultSet rs = sql.getPathOfImage(con, image_id, version);
-			rs.next();
-			log.info(new Date() + " - successfully returned PathOfImage: "
-					+ rs.getString("image_path"));
-			return rs.getString("image_path");
-		} catch (SQLException e) {
-			// TODO Auto-generated catch block
-			e.printStackTrace();
-		}
-		log.info(new Date() + " - returning PathOfImage: null");
-		return null;
 	}
 
 	@Override
@@ -141,12 +131,12 @@ public class ServerHandler implements Server.Iface {
 			long ram, long cpu, String imagePath, boolean isTemplate,
 			long filesize) throws TException {
 
-		String pk_institution = sql.setInstitution(con, university);
+		String pk_institution = sql.setInstitution(university);
 
-		String pk_person = sql.setPerson(con, login, lastname, firstname, Mail,
+		String pk_person = sql.setPerson(login, lastname, firstname, Mail,
 				new Date(), pk_institution);
 
-		sql.setImageData(con, pk_person, license, internet, cpu, ram,
+		sql.setImageData(pk_person, license, internet, cpu, ram,
 				imagename, imagePath, isTemplate, filesize);
 
 		log.info(new Date() + " - written VLdata");
@@ -156,73 +146,21 @@ public class ServerHandler implements Server.Iface {
 
 	@Override
 	public List<Image> getImageList() throws TException {
-		ResultSet res = sql.getImageList(con);
-
-		List<Image> list = new ArrayList<Image>();
-
-		try {
+			return sql.getImageList();
 
-			while (res.next()) {
-
-				list.add(new Image(res.getString("GUID_imageID"), res
-						.getString("imageVersion"),
-						res.getString("image_name"), res
-								.getString("cond_hasLicenseRestriction"), res
-								.getString("name"), res.getString("lecture"),
-						res.getString("image_update_time"), res
-								.getString("user")));
-
-			}
-
-			return list;
-		} catch (SQLException e1) {
-			// TODO Auto-generated catch block
-			e1.printStackTrace();
-		}
-		log.info(new Date() + " - returning ImageList: " + list.size()
-				+ " items.");
-		return null;
 	}
 
 	@Override
 	public List<String> getAllOS() throws TException {
-		List<String> list = new ArrayList<>();
-		ResultSet rs = sql.getAllOS(con);
-		try {
-			while (rs.next()) {
-				list.add(rs.getString("name"));
-			}
-			return list;
-		} catch (SQLException e) {
-			// TODO Auto-generated catch block
-			e.printStackTrace();
-		}
-		return null;
+		return sql.getAllOS();
+
 	}
 
 	@Override
 	public Map<String, String> getPersonData(String Vorname, String Nachname)
 			throws TException {
-		Map<String, String> map = new HashMap<String, String>();
-
-		ResultSet rs = sql.getPersonData(con, Vorname, Nachname);
-		try {
-			while (rs.next()) {
-
-				map.put("mail", rs.getString("mail"));
-				map.put("Nachname", rs.getString("Nachname"));
-				map.put("Vorname", rs.getString("Vorname"));
-				map.put("Hochschule", rs.getString("name"));
+		return sql.getPersonData(Vorname, Nachname);
 
-			}
-			return map;
-		} catch (SQLException e) {
-			// TODO Auto-generated catch block
-			e.printStackTrace();
-		}
-		log.info(new Date() + " - returning PersonData: " + map.size()
-				+ " items.");
-		return null;
 	}
 
 	@Override
@@ -232,27 +170,19 @@ public class ServerHandler implements Server.Iface {
 			String university, String Mail, String Tel, String Fak)
 			throws TException {
 		String pk_image = null;
-
+		Map<String, String> map = new HashMap<String, String>();
 		int imageversion = 0;
-		String pk_institution = sql.setInstitution(con, university);
-		String pk_person = sql.setPerson(con, login, lastname, firstname, Mail,
+		String pk_institution = sql.setInstitution(university);
+		String pk_person = sql.setPerson(login, lastname, firstname, Mail,
 				new Date(), pk_institution);
 
-		ResultSet image = sql.getImageIDandVersion(con, imagename);
+		map = sql.getImageIDandVersion(imagename);
 
-		try {
-			while (image.next()) {
-				pk_image = image.getString("GUID_imageID");
-				imageversion = image.getInt("imageVersion");
-			}
-		} catch (SQLException e) {
-			// TODO Auto-generated catch block
-			e.printStackTrace();
-		}
-
-		sql.setLectureData(con, pk_person, pk_image, imageversion, name, desc,
+		pk_image=map.get("GUID");
+		imageversion=Integer.parseInt(map.get("version"));
+		sql.setLectureData(pk_person, pk_image, imageversion, name, desc,
 				shortdesc, startDate, endDate, isActive);
-		XMLCreator xml = new XMLCreator(con, name);
+		XMLCreator xml = new XMLCreator(sql.getConnection(),name);
 		try {
 			xml.create(name);
 			log.info(new Date() + " - XML created.");
@@ -287,7 +217,7 @@ public class ServerHandler implements Server.Iface {
 			FileUtils.moveFile(tmpFile,
 					new File(Configuration.config.getAbsolute_path() + "prod/"
 							+ filename));
-			int ret = sql.UpdateImagePath(con, filename);
+			int ret = sql.UpdateImagePath(filename);
 			log.info(new Date() + " - update " + ret);
 			log.info(new Date() + " - file moved");
 
@@ -302,34 +232,9 @@ public class ServerHandler implements Server.Iface {
 	@Override
 	public Map<String, String> getImageData(String imageid, String imageversion)
 			throws TException {
-
-		ResultSet data = sql.getImageData(con, imageid, imageversion);
-
-		Map<String, String> map = new HashMap<String, String>();
-		try {
-			while (data.next()) {
-				System.out.println(data
-						.getString("cond_hasInternetRestriction")
-						+ data.getString("cond_hasLicenseRestriction")
-						+ data.getString("cond_minCPUs")
-						+ data.getString("cond_minRAM"));
-				map.put("name", data.getString("image_name"));
-				map.put("internet",
-						data.getString("cond_hasInternetRestriction"));
-				map.put("license", data.getString("cond_hasLicenseRestriction"));
-				map.put("cpu", data.getString("cond_minCPUs"));
-				map.put("ram", data.getString("cond_minRAM"));
-
-			}
-			return map;
-		} catch (SQLException e) {
-			// TODO Auto-generated catch block
-			e.printStackTrace();
-		}
-		log.info(new Date() + " - returning ImageData: " + map.size()
+		log.info(new Date() + " - returning ImageData: " + sql.getImageData(imageid, imageversion).size()
 				+ "items.");
-		return null;
-
+		return sql.getImageData(imageid, imageversion);
 	}
 
 	@Override
@@ -337,7 +242,7 @@ public class ServerHandler implements Server.Iface {
 			String image_path, boolean license, boolean internet, long ram,
 			long cpu, String id, String version, boolean isTemplate,
 			long filesize) throws TException {
-		sql.UpdateImageData(con, name, newName, image_path, license, internet,
+		sql.UpdateImageData(name, newName, image_path, license, internet,
 				cpu, ram, id, version, isTemplate, filesize);
 		return false;
 	}
@@ -351,29 +256,10 @@ public class ServerHandler implements Server.Iface {
 
 	@Override
 	public List<Lecture> getLectureList() throws TException {
-		ResultSet res = sql.getLectureList(con);
-
-		List<Lecture> list = new ArrayList<Lecture>();
 
-		try {
-
-			while (res.next()) {
-
-				list.add(new Lecture(res.getString("lectureID"), res
-						.getString("name"), res.getString("isActive"), res
-						.getString("startTime"), res.getString("endTime"), res
-						.getString("lastUsed"), res.getString("description"),
-						res.getString("image_name"), res.getString("user")));
-
-			}
-			return list;
-		} catch (SQLException e1) {
-			// TODO Auto-generated catch block
-			e1.printStackTrace();
-		}
-		log.info(new Date() + " - returning LectureList: " + list.size()
+		log.info(new Date() + " - returning LectureList: " + getLectureList().size()
 				+ " items.");
-		return null;
+		return sql.getLectureList();
 	}
 
 	@Override
@@ -384,21 +270,14 @@ public class ServerHandler implements Server.Iface {
 			String Fak, String id) throws TException {
 		String pk_image = "";
 		int imageversion = 0;
-		ResultSet image = sql.getImageIDandVersion(con, imagename);
+		Map<String, String> image = sql.getImageIDandVersion(imagename);
 
-		try {
-			while (image.next()) {
-				pk_image = image.getString("GUID_imageID");
-				imageversion = image.getInt("imageVersion");
-			}
-		} catch (SQLException e) {
-			// TODO Auto-generated catch block
-			e.printStackTrace();
-		}
-		sql.updateLectureData(con, pk_image, imageversion, lastname, newName,
+		pk_image=image.get("GUID");
+		imageversion=Integer.parseInt(image.get("version"));
+		sql.updateLectureData(pk_image, imageversion, lastname, newName,
 				desc, shortdesc, startDate, endDate, isActive, id);
-
-		XMLCreator xml = new XMLCreator(con, newName);
+		
+		XMLCreator xml = new XMLCreator(sql.getConnection(),newName);
 		try {
 			xml.create(newName);
 		} catch (SQLException | ParserConfigurationException
@@ -416,7 +295,7 @@ public class ServerHandler implements Server.Iface {
 	public boolean deleteImageServer(String imageid, String imageversion)
 			throws TException {
 
-		String stringFile = sql.getFile(con, imageid, imageversion);
+		String stringFile = sql.getFile(imageid, imageversion);
 		log.info(new Date() + " - File to Delete: " + stringFile);
 
 		File tmpFile = new File(Configuration.config.getAbsolute_path()
@@ -439,36 +318,31 @@ public class ServerHandler implements Server.Iface {
 	@Override
 	public boolean deleteImageData(String id, String version) throws TException {
 
-		return sql.deleteImage(con, id, version);
+		return sql.deleteImage(id, version);
 	}
 
 	@Override
 	public boolean connectedToLecture(String id, String version)
 			throws TException {
 		// TODO Auto-generated method stub
-		return sql.connectedToLecture(con, id, version);
+		return sql.connectedToLecture(id, version);
 	}
 
 	public boolean deleteLecture(String id, String hs, String user) {
-		ResultSet rs = sql.getDeleteXMLData(con, id);
+		Map<String, String> map = new HashMap<String, String>();
+		map = sql.getDeleteXMLData(id);
 		try {
-			rs.next();
-			String date = rs.getString("admin_createTime").replace(" ", "")
-					.replace("-", "").replace(":", "");
-			String name = rs.getString("name");
+			
 			String path = Configuration.config.getAbsolute_path() + "temp/"
-					+ date.substring(0, date.length() - 2) + "_" + hs + "_"
-					+ user + "_" + name + ".xml";
+					+ map.get("date").substring(0, map.get("date").length() - 2) + "_" + hs + "_"
+					+ user + "_" + map.get("name") + ".xml";
 			File xmlFile = new File(path);
 			FileUtils.forceDelete(xmlFile);
-		} catch (SQLException e) {
-			log.info(new Date() + " - Failed to execute deleteLecture");
-			e.printStackTrace();
 		} catch (IOException e) {
 			log.info(new Date() + " - Failed to execute deleteLecture");
 			e.printStackTrace();
 		}
-		return sql.deleteLecture(con, id);
+		return sql.deleteLecture(id);
 
 	}
 
diff --git a/Dozentenmodulserver/src/sql/SQL.java b/Dozentenmodulserver/src/sql/SQL.java
index 69843988..6d8bb8b7 100644
--- a/Dozentenmodulserver/src/sql/SQL.java
+++ b/Dozentenmodulserver/src/sql/SQL.java
@@ -4,7 +4,11 @@ import java.sql.*;
 

 import java.text.DateFormat;

 import java.text.SimpleDateFormat;

+import java.util.ArrayList;

 import java.util.Date;

+import java.util.HashMap;

+import java.util.List;

+import java.util.Map;

 import java.util.Random;

 import java.util.UUID;

 

@@ -12,6 +16,8 @@ import models.Configuration;
 

 import org.apache.log4j.Logger;

 import server.BinaryListener;

+import server.Image;

+import server.Lecture;

 

 public class SQL {

 

@@ -39,9 +45,10 @@ public class SQL {
 		return null;

 	}

 

-	public int writeFTPUser(Connection con, String user, String pass) {

+	public int writeFTPUser(String user, String pass) {

 		Statement stm;

 		try {

+			Connection con=getConnection();

 			stm = con.createStatement();

 

 			int ret = stm

@@ -51,6 +58,7 @@ public class SQL {
 							+ pass

 							+ "'),'10001','12345','"+Configuration.config.getAbsolute_path()+"temp/');");

 			con.commit();

+			con.close();

 			log.info(new Date() + " - created FTPUser " + user + " : " + pass

 					+ ".");

 			return ret;

@@ -62,15 +70,17 @@ public class SQL {
 		return -1;

 	}

 

-	public int DeleteUser(Connection con, String user) {

+	public int DeleteUser(String user) {

 		Statement stm;

 		try {

+			Connection con=getConnection();

 			stm = con.createStatement();

 

 			int ret = stm

 					.executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '"

 							+ user + "';");

 			con.commit();

+			con.close();

 			log.info(new Date() + " - FTPUser " + user + " deleted.");

 			return ret;

 		} catch (SQLException e) {

@@ -81,13 +91,15 @@ public class SQL {
 		return -1;

 	}

 

-	public ResultSet getImage(Connection con) {

+	public ResultSet getImage() {

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-

-			return stm

+			ResultSet rs=stm

 					.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;");

-

+			con.close();

+			return  rs;

+			

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			log.info(new Date() + " - Failed to getImage.");

@@ -97,17 +109,20 @@ public class SQL {
 		return null;

 	}

 

-	public ResultSet getPathOfImage(Connection con, String image_id,

+	public String getPathOfImage(String image_id,

 			String version) {

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-

-			return stm

+			ResultSet rs=stm

 					.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='"

 							+ image_id

 							+ "' and imageVersion='"

 							+ version

 							+ "';");

+			String path=rs.getString("image_path");

+			con.close();

+			return  path;

 

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

@@ -118,8 +133,9 @@ public class SQL {
 		return null;

 	}

 

-	public String setInstitution(Connection con, String university) {

+	public String setInstitution(String university) {

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 

 			ResultSet ret = stm

@@ -135,9 +151,14 @@ public class SQL {
 						.executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '"

 								+ university + "';");

 				rs.next();

-				return rs.getString("institutionID");

+				String institution=rs.getString("institutionID");

+				con.close();

+				

+				return institution;

 			} else {

-				return ret.getString("institutionID");

+				String institution=ret.getString("institutionID");

+				con.close();

+				return institution;

 			}

 

 		} catch (SQLException e) {

@@ -148,10 +169,11 @@ public class SQL {
 		return "-1";

 	}

 

-	public String setPerson(Connection con, String login, String lastname,

+	public String setPerson(String login, String lastname,

 			String firstname, String mail, Date lastlogin, String Institution) {

 		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 

 			ResultSet ret = stm

@@ -187,10 +209,14 @@ public class SQL {
 								+ firstname

 								+ "';");

 				rs.next();

-				return rs.getString("userID");

+				String userid=rs.getString("userID");

+				con.close();

+				return userid;

 			} else {

 				ret.first();

-				return ret.getString("userID");

+				String userid=ret.getString("userID");

+				con.close();

+				return userid;

 			}

 

 		} catch (SQLException e) {

@@ -201,7 +227,7 @@ public class SQL {
 		return "-1";

 	}

 

-	public boolean setImageData(Connection con, String pk_person, boolean license,

+	public boolean setImageData(String pk_person, boolean license,

 			boolean internet, long cpu, long ram, String imagename,

 			String imagePath, boolean isTemplate, long filesize) {

 

@@ -217,6 +243,7 @@ public class SQL {
 		}

 

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 			

 			String uid = UUID.randomUUID().toString();

@@ -250,7 +277,7 @@ public class SQL {
 					+ "','" + filesize //image_filesize

 					+ "');");

 			con.commit();

-

+			con.close();

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			log.info(new Date() + " - Failed to setImageData.");

@@ -260,28 +287,60 @@ public class SQL {
 

 	}

 

-	public ResultSet getImageList(Connection con) {

-		try {

-			Statement stm = con.createStatement();

+	public List<Image> getImageList() {

+		

+			Connection con=getConnection();

+			Statement stm;

+			List<Image> list = new ArrayList<Image>();

+			try {

+				stm = con.createStatement();

+				ResultSet res=stm

+						.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name, '' as lecture,vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name, lect.name as lecture,  vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;");

+				

 

-			return stm

-					.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name, '' as lecture,vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name, lect.name as lecture,  vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;");

+			

 

-		} catch (SQLException e) {

-			// TODO Auto-generated catch block

-			log.info(new Date() + " - Failed to getImageList.");

-			e.printStackTrace();

-		}

-		return null;

+					while (res.next()) {

+

+						list.add(new Image(res.getString("GUID_imageID"), res

+								.getString("imageVersion"),

+								res.getString("image_name"), res

+										.getString("cond_hasLicenseRestriction"), res

+										.getString("name"), res.getString("lecture"),

+								res.getString("image_update_time"), res

+										.getString("user")));

+

+					}

+				

+				con.close();

+			} catch (SQLException e) {

+				// TODO Auto-generated catch block

+				e.printStackTrace();

+			}

+

+		

+			return list;

+	

 	}

 

-	public ResultSet getLectureList(Connection con) {

+	public List<Lecture> getLectureList() {

+		List<Lecture> list = new ArrayList<Lecture>();

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-

-			return stm

+			ResultSet res=stm

 					.executeQuery("SELECT l.lectureID, l.name, l.isActive,l.startTime,l.endTime,l.lastUsed,l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.m_user u where i.GUID_imageID=l.imageID and l.admin_owner=u.userID;");

+			while (res.next()) {

 

+				list.add(new Lecture(res.getString("lectureID"), res

+						.getString("name"), res.getString("isActive"), res

+						.getString("startTime"), res.getString("endTime"), res

+						.getString("lastUsed"), res.getString("description"),

+						res.getString("image_name"), res.getString("user")));

+

+			}

+			con.close();

+			return list;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			log.info(new Date() + " - Failed to getLectureList.");

@@ -290,12 +349,18 @@ public class SQL {
 		return null;

 	}

 

-	public ResultSet getAllOS(Connection con) {

-

+	public List<String> getAllOS() {

+		List<String> list = new ArrayList<>();

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-			return stm

+			ResultSet rs=stm

 					.executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;");

+			while (rs.next()) {

+				list.add(rs.getString("name"));

+			}

+			return list;

+			

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			log.info(new Date() + " - Failed to getAllOS.");

@@ -306,16 +371,28 @@ public class SQL {
 

 	}

 

-	public ResultSet getPersonData(Connection con, String Vorname,

+	public Map<String,String> getPersonData(String Vorname,

 			String Nachname) {

+		Map<String, String> map = new HashMap<String, String>();

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-			return stm

+			ResultSet rs= stm

 					.executeQuery("SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like '"

 							+ Nachname

 							+ "' and u.Vorname like '"

 							+ Vorname

 							+ "' and u.institution=i.institutionID;");

+			while (rs.next()) {

+

+				map.put("mail", rs.getString("mail"));

+				map.put("Nachname", rs.getString("Nachname"));

+				map.put("Vorname", rs.getString("Vorname"));

+				map.put("Hochschule", rs.getString("name"));

+

+			}

+			con.close();

+			return map;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			log.info(new Date() + " - Failed to getPersonData.");

@@ -324,7 +401,7 @@ public class SQL {
 		return null;

 	}

 

-	public int setLectureData(Connection con, String pk_person, String pk_image,

+	public int setLectureData(String pk_person, String pk_image,

 			int imageversion, String name, String desc, String shortdesc,

 			String start, String end, boolean isactive) {

 

@@ -335,6 +412,7 @@ public class SQL {
 			active_bol = 1;

 		}

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 			

 			String uid = UUID.randomUUID().toString();

@@ -367,7 +445,7 @@ public class SQL {
 					+ "','"

 					+ pk_person + "');");

 			con.commit();

-

+			con.close();

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			log.info(new Date() + " - Failed to setLectureData.");

@@ -377,12 +455,21 @@ public class SQL {
 

 	}

 

-	public ResultSet getImageIDandVersion(Connection con, String name) {

+	public Map<String,String> getImageIDandVersion(String name) {

+		Map<String, String> map = new HashMap<String, String>();

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-			return stm

+			ResultSet image= stm

 					.executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '"

 							+ name + "';");

+			while (image.next()) {

+				map.put("GUID", image.getString("GUID_imageID"));

+				map.put("version", String.valueOf(image.getString("imageVersion")));

+

+			}

+			con.close();

+			return map;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			log.info(new Date() + " - Failed to getImageIDandVersion.");

@@ -391,12 +478,29 @@ public class SQL {
 		return null;

 	}

 

-	public ResultSet getImageData(Connection con, String id, String version) {

+	public Map<String,String> getImageData(String id, String version) {

+		Map<String, String> map = new HashMap<String, String>();

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-			return stm

+			ResultSet data=stm

 					.executeQuery("SELECT image_name,cond_hasInternetRestriction,cond_hasLicenseRestriction, cond_minCPUs, cond_minRAM FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '"

 							+ id + "' and imageVersion = '" + version + "' ;");

+			while (data.next()) {

+				System.out.println(data

+						.getString("cond_hasInternetRestriction")

+						+ data.getString("cond_hasLicenseRestriction")

+						+ data.getString("cond_minCPUs")

+						+ data.getString("cond_minRAM"));

+				map.put("name", data.getString("image_name"));

+				map.put("internet",

+						data.getString("cond_hasInternetRestriction"));

+				map.put("license", data.getString("cond_hasLicenseRestriction"));

+				map.put("cpu", data.getString("cond_minCPUs"));

+				map.put("ram", data.getString("cond_minRAM"));

+			}

+			con.close();

+			return map;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			log.info(new Date() + " - Failed to getImageData.");

@@ -405,10 +509,11 @@ public class SQL {
 		return null;

 	}

 

-	public int UpdateImageData(Connection con, String name, String newName,String image_path,

+	public int UpdateImageData(String name, String newName,String image_path,

 			boolean license, boolean internet, long cpu, long ram, String id,

 			String version, boolean isTemplate, long filesize) {

 		try {

+			Connection con=getConnection();

 			DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

 			Statement stm = con.createStatement();

 			int newVersion = Integer.parseInt(version) + 1;

@@ -449,6 +554,7 @@ public class SQL {
 					+ id

 					+ "' AND `imageVersion` = '" + version + "';");

 			con.commit();

+			con.close();

 			return 0;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

@@ -458,17 +564,17 @@ public class SQL {
 		return -1;

 	}

 

-	public boolean deleteImage(Connection con, String id, String version) {

+	public boolean deleteImage(String id, String version) {

 

 		try {

-

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 

 			stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '"

 					+ id + "' " + "AND imageVersion = '" + version + "';");

 

 			con.commit();

-

+			con.close();

 			return true;

 

 		} catch (SQLException e) {

@@ -480,7 +586,7 @@ public class SQL {
 		return false;

 	}

 

-	public int updateLectureData(Connection con, String pk_image,

+	public int updateLectureData(String pk_image,

 			int imageversion, String name, String newName, String desc,

 			String shortdesc, String start, String end, boolean isactive,

 			String id) {

@@ -492,6 +598,7 @@ public class SQL {
 			active_bol = 1;

 		}

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 			stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '"

 					+ newName

@@ -511,6 +618,7 @@ public class SQL {
 					+ formatter.format(new Date())

 					+ "'WHERE `lectureID` = '" + id + "';");

 			con.commit();

+			con.close();

 			log.info(new Date() + " - Succeeded to updateLectureData.");

 

 		} catch (SQLException e) {

@@ -523,16 +631,16 @@ public class SQL {
 

 	}

 

-	public boolean connectedToLecture(Connection con, String id, String version) {

+	public boolean connectedToLecture(String id, String version) {

 

 		try {

-

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 

 			ResultSet rs = stm.executeQuery("SELECT lectureID FROM "

 					+ "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id

 					+ "' AND imageVersion = '" + version + "';");

-

+			con.close();

 			return rs.first();

 

 		} catch (SQLException e) {

@@ -546,15 +654,16 @@ public class SQL {
 

 	}

 

-	public boolean deleteLecture(Connection con, String id) {

+	public boolean deleteLecture(String id) {

 

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 			stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = '"

 					+ id + "';");

-

+			

 			con.commit();

-

+			con.close();

 			return true;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

@@ -565,9 +674,10 @@ public class SQL {
 		return false;

 	}

 

-	public String getFile(Connection con, String imageid, String imageversion) {

+	public String getFile(String imageid, String imageversion) {

 

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 

 			ResultSet rs = stm

@@ -577,6 +687,7 @@ public class SQL {
 							+ imageversion

 							+ "';");

 			rs.next();

+			con.close();

 			return rs.getString("image_path");

 

 		} catch (SQLException e) {

@@ -588,15 +699,23 @@ public class SQL {
 		return null;

 	}

 

-	public ResultSet getDeleteXMLData(Connection con, String id) {

+	public Map<String,String> getDeleteXMLData(String id) {

+		Map<String, String> map = new HashMap<String, String>();

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 

 			ResultSet rs = stm

 					.executeQuery("SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID='"

 							+ id + "';");

+			rs.next();

+			map.put("date", rs.getString("admin_createTime").replace(" ", "")

+					.replace("-", "").replace(":", ""));

+			map.put("name", rs.getString("name"));

+			

 

-			return rs;

+			con.close();

+			return map;

 

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

@@ -607,9 +726,9 @@ public class SQL {
 		return null;

 	}

 	

-	public int UpdateImagePath(Connection con, String name) {

+	public int UpdateImagePath(String name) {

 		try {

-

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 			String image_path="prod/"+name;

 			

@@ -618,6 +737,7 @@ public class SQL {
 					+ "' WHERE `image_path` = '"

 					+ "temp/"+name + "';");

 			con.commit();

+			con.close();

 			return 0;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

diff --git a/Dozentenmodulserver/src/util/XMLCreator.java b/Dozentenmodulserver/src/util/XMLCreator.java
index bf49dfe3..564543fa 100644
--- a/Dozentenmodulserver/src/util/XMLCreator.java
+++ b/Dozentenmodulserver/src/util/XMLCreator.java
@@ -26,6 +26,7 @@ import org.w3c.dom.Document;
 import org.w3c.dom.Element;
 
 import server.BinaryListener;
+import sql.SQL;
 
 public class XMLCreator {
 
@@ -42,6 +43,7 @@ public class XMLCreator {
 	private String imageName;
 	private String xmlName;
 	private static Logger log = Logger.getLogger( BinaryListener.class );
+	
 
 	// phone param not existing
 	private String query = "SELECT m_VLData_imageInfo.image_path ,vorname, nachname, mail, shortdescription, description, m_operatingSystem.name as os, m_VLData_lecture.admin_changeTime as time, m_VLData_lecture.name as lectureName, m_user.institution as userInstitution, m_user.loginName as loginName,  m_institution.name as institutionName "