Merge branch 'master' of ssh://git.openslx.org/openslx-ng/tutor-module

Conflicts:
	Dozentenmodul/bin/gui/intro/Login_GUI.class
	Dozentenmodulserver/bin/server/ServerHandler.class
	Dozentenmodulserver/bin/server/startServer.class
	Dozentenmodulserver/src/server/startServer.java

1 files changed, 285 insertions, 91 deletions

diff --git a/Dozentenmodulserver/src/sql/SQL.java b/Dozentenmodulserver/src/sql/SQL.java
index d85068e8..775b17cb 100644
--- a/Dozentenmodulserver/src/sql/SQL.java
+++ b/Dozentenmodulserver/src/sql/SQL.java
@@ -4,10 +4,20 @@ 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;

+

+import models.Configuration;

+

 import org.apache.log4j.Logger;

 import server.BinaryListener;

+import server.Image;

+import server.Lecture;

 

 public class SQL {

 

@@ -23,21 +33,22 @@ public class SQL {
 		}

 		try {

 			Connection con = DriverManager

-					.getConnection("jdbc:mysql://127.0.0.1/bwLehrpool?user=root&password=slx-ng-open");

+					.getConnection("jdbc:mysql://"+Configuration.config.getSql_connection()+"?user="+Configuration.config.getSql_user()+"&password="+Configuration.config.getSql_pass()+"");

 			con.setAutoCommit(false);

 			log.info(new Date() + " - Connection returned to Client.");

 			return con;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

-			log.info(new Date() + " - Could not return connection to Client.");

+			log.info(new Date() + " - Failed to return connection to Client.");

 			e.printStackTrace();

 		}

 		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

@@ -45,83 +56,95 @@ public class SQL {
 							+ user

 							+ "',SHA1('"

 							+ pass

-							+ "'),'10001','12345','/srv/openslx/nfs/temp');");

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

 			con.commit();

+			con.close();

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

 					+ ".");

 			return ret;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

-			log.info(new Date() + " - FTPUser not created.");

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

 			e.printStackTrace();

 		}

 		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) {

 			// TODO Auto-generated catch block

-			log.info(new Date() + " - Could not delete FTPUser " + user + ".");

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

 			e.printStackTrace();

 		}

 		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.");

 			e.printStackTrace();

 		}

 

 		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

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

 			e.printStackTrace();

 		}

 

 		return null;

 	}

 

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

+	public String setInstitution(String university) {

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

 

 			ResultSet ret = stm

 					.executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'"

 							+ university + "';");

+			

 			if (ret.next() == false) {

-				Random rand = new Random();

-				int id = rand.nextInt();

+				

+				String id = UUID.randomUUID().toString();

 				stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('"

 						+ id + "','" + university + "');");

 				con.commit();

@@ -129,22 +152,29 @@ public class SQL {
 						.executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '"

 								+ university + "';");

 				rs.next();

-				return rs.getInt("institutionID");

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

+				con.close();

+				

+				return institution;

 			} else {

-				return ret.getInt("institutionID");

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

+				con.close();

+				return institution;

 			}

 

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

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

 			e.printStackTrace();

 		}

-		return -1;

+		return "-1";

 	}

 

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

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

+	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

@@ -153,14 +183,15 @@ public class SQL {
 							+ "' and Vorname like '"

 							+ firstname

 							+ "';");

+			

 			if (ret.next() == false) {

-				Random rand = new Random();

-				int id = rand.nextInt();

+				

+				String id = UUID.randomUUID().toString();

 				stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('"

 						+ id

 						+ "','"

-						+login

-						+"','"

+						+ login

+						+ "','"

 						+ lastname

 						+ "','"

 						+ firstname

@@ -179,21 +210,27 @@ public class SQL {
 								+ firstname

 								+ "';");

 				rs.next();

-				return rs.getInt("userID");

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

+				con.close();

+				return userid;

 			} else {

-				return ret.getInt("userID");

+				ret.first();

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

+				con.close();

+				return userid;

 			}

 

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

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

 			e.printStackTrace();

 		}

-		return -1;

+		return "-1";

 	}

 

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

+	public boolean setImageData(String pk_person, boolean license,

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

-			String imagePath) {

+			String imagePath, boolean isTemplate, long filesize) {

 

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

 

@@ -207,10 +244,11 @@ public class SQL {
 		}

 

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-			Random random = new Random();

-			int uid = random.nextInt();

-			stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`)VALUES('"

+			

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

+			stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`)VALUES('"

 					+ uid // GUID_imageID

 					+ "',1,'" // imageVersion

 					+ imagename // image_name

@@ -236,53 +274,97 @@ public class SQL {
 					+ "','" + internet_bol // cond_hasInternetRestriction

 					+ "','" + ram // cond_minRAM

 					+ "','" + cpu // cond_minCPUs

+					+ "','" + isTemplate //image_isTemplate

+					+ "','" + filesize //image_filesize

 					+ "');");

 			con.commit();

-

+			con.close();

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

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

 			e.printStackTrace();

 		}

 		return true;

 

 	}

 

-	public ResultSet getImageList(Connection con) {

-		try {

-			Statement stm = con.createStatement();

-

-			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;");

+	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;");

+				

+

+			

+

+					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();

+			}

 

-		} catch (SQLException e) {

-			// TODO Auto-generated catch block

-			e.printStackTrace();

-		}

-		return null;

+		

+			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.");

 			e.printStackTrace();

 		}

 		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.");

 			e.printStackTrace();

 		}

 

@@ -290,24 +372,37 @@ 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.");

 			e.printStackTrace();

 		}

 		return null;

 	}

 

-	public int setLectureData(Connection con, int pk_person, int 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) {

 

@@ -318,9 +413,10 @@ public class SQL {
 			active_bol = 1;

 		}

 		try {

+			Connection con=getConnection();

 			Statement stm = con.createStatement();

-			Random random = new Random();

-			int uid = random.nextInt();

+			

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

 			stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_lecture`(`lectureID`,`name`,`isActive`,`startTime`,`endTime`,`lastUsed`,`shortDescription`,`description`,`imageID`,`imageVersion`,`admin_createTime`,`admin_changeTime`,`admin_owner`,`admin_change_by`)VALUES('"

 					+ uid

 					+ "','"

@@ -350,46 +446,75 @@ public class SQL {
 					+ "','"

 					+ pk_person + "');");

 			con.commit();

-

+			con.close();

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

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

 			e.printStackTrace();

 		}

 		return 0;

 

 	}

 

-	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.");

 			e.printStackTrace();

 		}

 		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();

-			System.out.println("Test");

-			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.");

 			e.printStackTrace();

 		}

 		return null;

 	}

 

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

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

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

-			String version) {

+			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;

@@ -402,12 +527,14 @@ public class SQL {
 			if (license == true) {

 				license_bol = 1;

 			}

-			System.out.println(internet_bol);

-			System.out.println(license_bol);

+			log.info(new Date() + " - 'image_name` = '"	+ newName+ "\n length of name="+newName.length());

+			

 			stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `imageVersion` = '"

 					+ newVersion

 					+ "',`image_name` = '"

 					+ newName

+					+ "',`image_path` = '"

+					+ image_path

 					+ "',`image_update_time` = '"

 					+ formatter.format(new Date())

 					+ "',`rec_change_time` = '"

@@ -420,47 +547,47 @@ public class SQL {
 					+ ram

 					+ "',`cond_minCPUs` = '"

 					+ cpu

+					+ "',`image_isTemplate` = '"

+					+ isTemplate

+					+ "',`image_filesize` = '"

+					+ filesize

 					+ "' WHERE `GUID_imageID` = '"

 					+ id

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

 			con.commit();

+			con.close();

 			return 0;

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

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

 			e.printStackTrace();

 		}

 		return -1;

 	}

 

-	/**

-	 * 

-	 * @param id

-	 * @param version

-	 * @return

-	 */

-

-	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) {

 			// TODO Auto-generated catch block

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

 			e.printStackTrace();

 		}

 

 		return false;

 	}

 

-	public int updateLectureData(Connection con, int 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) {

@@ -472,6 +599,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

@@ -491,9 +619,12 @@ public class SQL {
 					+ formatter.format(new Date())

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

 			con.commit();

+			con.close();

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

 

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

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

 			e.printStackTrace();

 		}

 

@@ -501,20 +632,22 @@ 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) {

 			// TODO Auto-generated catch block

+			log.info(new Date()

+					+ " - Failed to execute method connectedToLecture.");

 			e.printStackTrace();

 		}

 

@@ -522,36 +655,97 @@ 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 + "';");

-

+			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

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

 			e.printStackTrace();

 		}

-		

+

 		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();

 

-			return stm.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE imageID = '" + imageid

-					+ "' AND imageVersion = '" + imageversion + "';").toString();

+			ResultSet rs = stm

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

+							+ imageid

+							+ "' AND imageVersion = '"

+							+ imageversion

+							+ "';");

+			rs.next();

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

+			con.close();

+			return path;

 

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

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

 			e.printStackTrace();

 		}

 

 		return null;

 	}

+

+	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"));

+			

+

+			con.close();

+			return map;

+

+		} catch (SQLException e) {

+			// TODO Auto-generated catch block

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

+			e.printStackTrace();

+		}

+

+		return null;

+	}

+	

+	public int UpdateImagePath(String name) {

+		try {

+			Connection con=getConnection();

+			Statement stm = con.createStatement();

+			String image_path="prod/"+name;

+			

+			stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '"

+					+ image_path

+					+ "' WHERE `image_path` = '"

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

+			con.commit();

+			con.close();

+			return 0;

+		} catch (SQLException e) {

+			// TODO Auto-generated catch block

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

+			e.printStackTrace();

+		}

+		return -1;

+	}

 }