[server] db stuff, new interface, ...

1 files changed, 48 insertions, 1871 deletions

diff --git a/dozentenmodulserver/src/main/java/sql/SQL.java b/dozentenmodulserver/src/main/java/sql/SQL.java
index 925ffb57..af79b521 100644
--- a/dozentenmodulserver/src/main/java/sql/SQL.java
+++ b/dozentenmodulserver/src/main/java/sql/SQL.java
@@ -2,1903 +2,80 @@ package sql;
 

 import java.sql.Connection;

 import java.sql.DriverManager;

-import java.sql.PreparedStatement;

-import java.sql.ResultSet;

 import java.sql.SQLException;

-import java.sql.Statement;

-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.UUID;

+import java.util.Collections;

+import java.util.Queue;

+import java.util.Set;

+import java.util.concurrent.ConcurrentHashMap;

+import java.util.concurrent.ConcurrentLinkedQueue;

 

 import models.Configuration;

 

 import org.apache.log4j.Logger;

-import org.openslx.sat.thrift.iface.Image;

-import org.openslx.sat.thrift.iface.Lecture;

-import org.openslx.sat.thrift.iface.Person;

-

-import util.Util;

 

 public class SQL {

 

-	private static final Logger log = Logger.getLogger(SQL.class);

+	private static final Logger LOGGER = Logger.getLogger(SQL.class);

+	/**

+	 * Pool of available connections.

+	 */

+	private static final Queue<MysqlConnection> pool = new ConcurrentLinkedQueue<>();

+

+	/**

+	 * Set of connections currently handed out.

+	 */

+	private static final Set<MysqlConnection> busyConnections = Collections.newSetFromMap(new ConcurrentHashMap<MysqlConnection, Boolean>());

 

 	static {

 		try {

 			Class.forName("com.mysql.jdbc.Driver").newInstance();

 		} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {

-			log.fatal("Cannot get mysql JDBC driver!", e);

+			LOGGER.fatal("Cannot get mysql JDBC driver!", e);

 			System.exit(1);

 		}

 	}

 

-	public Connection getConnection() {

-		// TODO: Connection pooling, better yet some abstraction layer for mysql, eg. like dalesbred in master-server

-		try {

-			Connection con = DriverManager.getConnection(Configuration.getDbUri(),

-					Configuration.getDbUsername(), Configuration.getDbPassword());

-			con.setAutoCommit(false);

-

-			return con;

-		} catch (SQLException e) {

-

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

-			e.printStackTrace();

-		}

-

-		return null;

-	}

-

-	// no prepared statement to do here

-	public ResultSet getImage() {

-		try {

-			Connection con = getConnection();

-			Statement stm = con.createStatement();

-			ResultSet rs = stm.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;");

-			con.close();

-			return rs;

-

-		} catch (SQLException e) {

-

-			log.info("Failed to getImage.");

-			e.printStackTrace();

-		}

-

-		return null;

-	}

-

-	public String getPathOfImage(String image_id, String version) {

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID= " + "?"

-					+ " and imageVersion= " + "?" + ";";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, image_id);

-			prest.setString(2, version);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-			rs.first();

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

-			con.close();

-			return path;

-

-		} catch (SQLException e) {

-

-			log.info("Failed to getPathOfImage.");

-			e.printStackTrace();

-		}

-

-		return null;

-	}

-

-	public String setInstitution(String university) {

-		// TODO: WTF!?

-		// 1) This method is called set* and has a return value (other than boolean)

-		// 2) This method generates a random ID for an institution name (which is called university in the

-		//    parameter), so every satellite server has a different ID for the same institution

-		// 3) If an institution is renamed, it gets a new id, and the old one remains in the db

-		// 4) In case it inserts a new institution with an ID, it queries the DB for the ID right

-		//    after doing so. So it's expected to be different from what was just inserted!??

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT * FROM bwLehrpool.m_institution where name like ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, university);

-			ResultSet ret = prest.executeQuery();

-			con.commit();

-

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

-

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

-

-				sql = "INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`) VALUES" + "(?,?)";

-

-				prest = con.prepareStatement(sql);

-				prest.setString(1, id);

-				prest.setString(2, university);

-				prest.executeUpdate();

-

-				con.commit();

-

-				sql = "SELECT institutionID FROM bwLehrpool.m_institution WHERE name like ?;";

-

-				prest = con.prepareStatement(sql);

-				prest.setString(1, university);

-

-				ResultSet rs = prest.executeQuery();

-				con.commit();

-				rs.next();

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

-				con.close();

-

-				return institution;

-			} else {

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

-				con.close();

-				return institution;

-			}

-

-		} catch (SQLException e) {

-

-			log.info("Failed to setInstitution.");

-			e.printStackTrace();

-		}

-		return "-1";

-	}

-

-	public String setPerson(String userID, String lastname, String firstname, String mail, Date lastlogin,

-			String Institution) {

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

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + "?"

-					+ " and Vorname like " + "?" + ";";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, lastname);

-			prest.setString(2, firstname);

-

-			ResultSet ret = prest.executeQuery();

-			con.commit();

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

-				log.info("create user statement values are: userID, loginname, lastname, firstname, mail, institution VALUES: "

-						+ userID

-						+ " "

-						+ userID

-						+ " "

-						+ lastname

-						+ " "

-						+ firstname

-						+ " "

-						+ mail

-						+ " "

-						+ Institution);

-

-				sql = "INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES("

-						+ "?"

-						+ ","

-						+ "?"

-						+ ","

-						+ "?"

-						+ ","

-						+ "?"

-						+ ","

-						+ "?"

-						+ ",'"

-						+ formatter.format(new Date()) + "'," + "?" + ");";

-

-				prest = con.prepareStatement(sql);

-				prest.setString(1, userID);

-				prest.setString(2, userID);

-				prest.setString(3, lastname);

-				prest.setString(4, firstname);

-				prest.setString(5, mail);

-				prest.setString(6, Institution);

-

-				prest.executeUpdate();

-

-				con.commit();

-

-				// TODO: why... WHY!? We had the user id from the beginning! We just inserted it!

-				// Also if there are two users with same first and last name we might get

-				// the wrong id! JUST WHY!!!!!?

-				sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + "?"

-						+ " and Vorname like " + "?" + ";";

-

-				prest = con.prepareStatement(sql);

-				prest.setString(1, lastname);

-				prest.setString(2, firstname);

-

-				ResultSet rs = prest.executeQuery();

-				con.commit();

-				rs.next();

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

-				con.close();

-				return userid;

-			} else {

-				ret.first();

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

-				con.close();

-				return userid;

-			}

-

-		} catch (SQLException e) {

-

-			log.info("Failed to setPerson.");

-			e.printStackTrace();

-		}

-		return "-1";

-	}

-

-	public boolean writeNewImageData(String pk_person, boolean license, boolean internet, String imagename,

-			String desc, String imagePath, long filesize, String shareMode, String pk_os, String imageId) {

-

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

-

-		int internet_bol = 0;

-		int license_bol = 0;

-		if (internet == true) {

-			internet_bol = 1;

-		}

-		if (license == true) {

-			license_bol = 1;

-		}

-

-		try {

-			Connection con = getConnection();

-

-			// create a UUID

-

-			String sql = "INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`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`,`image_syncMode`)VALUES("

-					+ "?" // GUID_imageID

-					+ ",1," // imageVersion

-					+ "?" // image_name

-					+ "," + "?" // image_description

-					+ "," + "?" // image_path

-					+ ",'"

-					+ formatter.format(new Date()) // image_lastCall

-					+ "','" + formatter.format(new Date()) // image_create_time

-					+ "','" + formatter.format(new Date()) // image_update_time

-					+ "'," + "?" // image_owner

-					+ "," + "?" // image_change_by

-					+ ",'" + formatter.format(new Date()) // rec_create_time

-					+ "','" + formatter.format(new Date()) // rec_change_time

-					+ "'," + "?" // rec_owner

-					+ "," + "?" // rec_change_by

-					+ "," + "?" // content_operatingSystem

-					+ ",1" // status_isCompressed

-					+ ",1" // status_isSecure

-					+ ",1" // status_isOptimzed

-					+ ",1" // status_isValid

-					+ ",1" // status_isReady

-					+ ",0" // status_isDeleted

-					+ ",0," // status_isLastOfficialVersion

-					+ "?" // cond_hasLicenseRestriction

-					+ "," + "?" // cond_hasInternetRestriction

-					+ "," + "?" // cond_minRAM

-					+ "," + "?" // cond_minCPUs

-					+ ",0" // image_isTemplate

-					+ "," + "?" // image_filesize

-					+ "," + "?" // shareMode

-					+ ");";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, imageId);

-			prest.setString(2, imagename);

-			prest.setString(3, desc);

-			prest.setString(4, imagePath);

-			prest.setString(5, pk_person);

-			prest.setString(6, pk_person);

-			prest.setString(7, pk_person);

-			prest.setString(8, pk_person);

-			prest.setString(9, pk_os);

-			prest.setInt(10, license_bol);

-			prest.setInt(11, internet_bol);

-			prest.setLong(12, 2);

-			prest.setLong(13, 2);

-			prest.setLong(14, filesize);

-			prest.setString(15, shareMode);

-

-			prest.executeUpdate();

-			con.commit();

-			con.close();

-		} catch (SQLException e) {

-			log.info("Failed to insert new image into DB", e);

-			return false;

-		}

-		return true;

-

-	}

-

-	public List<Image> getImageListPermissionWrite(String userID) {

-

-		Connection con = getConnection();

-

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

-		try {

-

-			String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = "

-					+ "?"

-					+ " AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-

-			ResultSet res = prest.executeQuery();

-			con.commit();

-			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("architecture") + " bit",

-						res.getString("lecture"), res.getString("image_update_time"), res.getString("user"),

-						res.getString("image_isTemplate"), res.getString("image_description"),

-						res.getString("image_filesize")));

-			}

-			con.close();

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-			// TODO: Shouldn't we return null or something that gives the client a chance

-			// to detect the failure and notify the user?

-		}

-		return list;

-	}

-

-	public List<Image> getImageListPermissionRead(String userID) {

-

-		Connection con = getConnection();

-

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

-		try {

-

-			String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = "

-					+ "?"

-					+ " AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-

-			ResultSet res = prest.executeQuery();

-			con.commit();

-			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("architecture") + " bit",

-						res.getString("lecture"), res.getString("image_update_time"), res.getString("user"),

-						res.getString("image_isTemplate"), res.getString("image_description"),

-						res.getString("image_filesize")));

-			}

-			con.close();

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return list;

-	}

-

-	// no prepared statement to do here

-	public List<Image> getImageList(String userID) {

-

-		Connection con = getConnection();

-		Statement stm;

-

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

-		try {

-

-			stm = con.createStatement();

-

-			// ResultSet

-			ResultSet res = stm.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID ORDER BY vl.image_name;");

-

-			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("architecture") + " bit",

-						res.getString("lecture"), res.getString("image_update_time"), res.getString("user"),

-						res.getString("image_isTemplate"), res.getString("image_description"),

-						res.getString("image_filesize")));

-			}

-			con.close();

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return list;

-	}

-

-	public List<Image> getImageListPermissionLink(String userID) {

-

-		Connection con = getConnection();

-

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

-		try {

-

-			String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = "

-					+ "?"

-					+ " AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-

-			ResultSet res = prest.executeQuery();

-			con.commit();

-			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("architecture") + " bit",

-						res.getString("lecture"), res.getString("image_update_time"), res.getString("user"),

-						res.getString("image_isTemplate"), res.getString("image_description"),

-						res.getString("image_filesize")));

-			}

-			con.close();

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return list;

-	}

-

-	// TODO: OK this method is pretty much the tenth copy&paste version of the stuff above. Maybe refactor something here?

-	public List<Image> getImageListPermissionAdmin(String userID) {

-		Connection con = getConnection();

-

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

-		try {

-

-			String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = "

-					+ "?"

-					+ " AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-

-			ResultSet res = prest.executeQuery();

-			con.commit();

-			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("architecture") + " bit",

-						res.getString("lecture"), res.getString("image_update_time"), res.getString("user"),

-						res.getString("image_isTemplate"), res.getString("image_description"),

-						res.getString("image_filesize")));

-			}

-			con.close();

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return list;

-	}// end getImageListPermissionAdmin

-

-	// no prepared statement to do here

-	public List<Image> getImageListAllTemplates() {

-		Connection con = getConnection();

-		Statement stm;

-

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

-		try {

-

-			stm = con.createStatement();

-

-			// ResultSet

-			ResultSet res = stm.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.image_description, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE vl.image_isTemplate=1 AND vl.content_operatingSystem=os.operatingSystemID AND vl.image_owner=u.userID ORDER BY vl.image_name;");

-

-			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("architecture") + " bit",

-						res.getString("lecture"), res.getString("image_update_time"), res.getString("user"),

-						res.getString("image_isTemplate"), res.getString("image_description"),

-						res.getString("image_filesize")));

-			}

-			con.close();

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return list;

-	}// end getImageListAllTemplates

-

-	public List<Lecture> getLectureListPermissionRead(String userID) {

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

-

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT DISTINCT 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.pm_VLData_lecture pml, bwLehrpool.m_user u "

-					+ "WHERE u.userID = l.admin_owner AND pml.rec_read=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.userID = ?  ORDER BY l.name;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-

-			ResultSet res = prest.executeQuery();

-			con.commit();

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

-

-			log.info("Failed to getLectureListPermissionRead.");

-			e.printStackTrace();

-		}

-		return list;

-	}// end getLectureListPermissionRead

-

-	public List<Lecture> getLectureListPermissionWrite(String userID) {

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

-

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT DISTINCT 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.pm_VLData_lecture pml, bwLehrpool.m_user u "

-					+ "WHERE pml.userID="

-					+ "?"

-					+ " AND u.userID = l.admin_owner AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID ORDER BY l.name;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-

-			ResultSet res = prest.executeQuery();

-			con.commit();

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

-

-			log.info("Failed to getLectureListPermissionWrite.");

-			e.printStackTrace();

-		}

-		return list;

-	}// end getLectureListPermissionWrite

-

-	public List<Lecture> getLectureListPermissionAdmin(String userID) {

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

-

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT DISTINCT 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.pm_VLData_lecture pml, bwLehrpool.m_user u "

-					+ "WHERE u.userID = l.admin_owner AND pml.rec_admin=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.userID= ?  ORDER BY l.name;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-

-			ResultSet res = prest.executeQuery();

-			con.commit();

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

-

-			log.info("Failed to getLectureListPermissionAdmin.");

-			e.printStackTrace();

-		}

-		return list;

-	}// end getLectureListPermissionAdmin

-

-	// No prepared statement to do here

-	public List<Lecture> getLectureList() {

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

-		try {

-			Connection con = getConnection();

-			Statement stm = con.createStatement();

-			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  ORDER BY l.name;");

-

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

-

-			log.info("Failed to getLectureList.");

-			e.printStackTrace();

-		}

-		return null;

-	}

-

-	// No prepared statement to do here

-	public List<String> getAllOS() {

-

-		// if(authenticated())

-		// {

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

-		try {

-			Connection con = getConnection();

-			Statement stm = con.createStatement();

-			ResultSet rs = stm.executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem ORDER BY name, architecture;");

-			while (rs.next()) {

-				list.add(rs.getString("name") + " " + rs.getString("architecture") + " bit");

-			}

-			return list;

-

-		} catch (SQLException e) {

-

-			log.info("Failed to getAllOS.");

-			e.printStackTrace();

-		}

-

-		return null;

-

-		// }

-		// return null;

-	}

-

-	public Map<String, String> getPersonData(String Vorname, String Nachname) {

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

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like "

-					+ "?" + " and u.Vorname like " + "?" + " and u.institution=i.institutionID;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, Vorname);

-			prest.setString(2, Nachname);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

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

-

-			log.info("Failed to getPersonData.");

-			e.printStackTrace();

-		}

-		return null;

-	}// end getPersonData

-

-	public Map<String, String> getPersonData(String userID) {

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

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT userID, loginName, Nachname, Vorname, mail, m_institution.name as institution FROM bwLehrpool.m_user, m_institution WHERE userID=? AND m_user.institution=m_institution.institutionID;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-			while (rs.next()) {

-				map.put("userID", rs.getString("userID"));

-				map.put("loginName", rs.getString("loginName"));

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

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

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

-				map.put("institution", rs.getString("institution"));

-

-			}

-			con.close();

-			return map;

-		} catch (SQLException e) {

-			log.info("Failed to getPersonData with userID='" + userID + "'.");

-			e.printStackTrace();

-		}

-		return null;

-	}// end getPersonData

-

-	public Map<String, String> getItemOwner(String itemID) {

-

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

-		Connection con = getConnection();

-		PreparedStatement prest;

-		String userID;

-

-		try {

-			// find out if the itemID belongs to an image

-			String sql = "SELECT image_owner FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID=?;";

-			prest = con.prepareStatement(sql);

-			prest.setString(1, itemID);

-

-			ResultSet rs = prest.executeQuery();

-			while (rs.next()) {

-				map.put("userID", rs.getString("image_owner"));

-			}

-

-			// if map is empty, then itemID does not belong to an image -> must

-			// be a lecture

-			// TODO: Errr what? Why? Trial and error until we find the right table?

-			if (map.isEmpty() == true) {

-				// check if itemID belongs to a lecture

-				sql = "SELECT admin_owner FROM bwLehrpool.m_VLData_lecture WHERE lectureID=?;";

-				prest = con.prepareStatement(sql);

-				prest.setString(1, itemID);

-

-				rs = prest.executeQuery();

-				while (rs.next()) {

-					map.put("userID", rs.getString("admin_owner"));

-				}

-			}

-

-			// if map is still empty, then the itemID could not be found

-			if (map.isEmpty() == true) {

-				log.error("Failed to getItemOwner for item '" + itemID + "'.");

-			} else {

-

-				// now that we have the userID for this item, get the user

-				// information and put it in the map

-				userID = map.get("userID"); // extract the userID

-				map = getPersonData(userID); // get all the rest of his

-												// information

-

-				// return this map with all the infos

-				return map;

-

-			}

-

-		} catch (SQLException e) {

-			e.printStackTrace();

-		}

-

-		// something went horribly wrong...

-		return null;

-	}// end getItemOwner

-

-	public int setLectureData(String pk_person, String pk_image, int imageversion, String name, String desc,

-			String shortdesc, String start, String end, boolean isactive, String lectureID) {

-

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

-		int active_bol = 0;

-

-		if (isactive == true) {

-			active_bol = 1;

-		}

-

-		try {

-			Connection con = getConnection();

-

-			String sql = "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("

-					+ "?"

-					+ ","

-					+ "?"

-					+ ","

-					+ "?"

-					+ ","

-					+ "?"

-					+ ","

-					+ "?"

-					+ ",'"

-					+ formatter.format(new Date())

-					+ "',"

-					+ "?"

-					+ ","

-					+ "?"

-					+ ","

-					+ "?"

-					+ ","

-					+ "?"

-					+ ",'"

-					+ formatter.format(new Date())

-					+ "','"

-					+ formatter.format(new Date())

-					+ "',"

-					+ "?"

-					+ ","

-					+ "?" + ");";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, lectureID);

-			prest.setString(2, name);

-			prest.setInt(3, active_bol);

-			prest.setString(4, start);

-			prest.setString(5, end);

-			prest.setString(6, shortdesc);

-			prest.setString(7, desc);

-			prest.setString(8, pk_image);

-			prest.setInt(9, imageversion);

-			prest.setString(10, pk_person);

-			prest.setString(11, pk_person);

-

-			prest.executeUpdate();

-			con.commit();

-

-			con.close();

-		} catch (SQLException e) {

-

-			log.info("Failed to setLectureData.");

-			e.printStackTrace();

-		}

-		return 0;

-

-	}

-

-	public Map<String, String> getImageIDandVersion(String id) {

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

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = ?";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-

-			ResultSet image = prest.executeQuery();

-			con.commit();

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

-

-			log.info("Failed to getImageIDandVersion.");

-			e.printStackTrace();

-		}

-		return null;

-	}

-

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

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

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT u.mail, i.GUID_imageID, i.imageVersion, i.image_name,i.image_description,i.image_update_time,i.cond_hasInternetRestriction,i.cond_hasLicenseRestriction, i.cond_minCPUs,i.cond_minRAM, i.image_syncMode,i.image_filesize,i.image_path, i.image_isTemplate,os.GuestOS, concat(u.Nachname,' ',u.Vorname) as Owner FROM bwLehrpool.m_VLData_imageInfo i,bwLehrpool.m_user u,bwLehrpool.m_operatingSystem os where i.image_owner=u.userID and i.content_operatingSystem=os.operatingSystemID and GUID_imageID = ? and imageVersion = ? ;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-			prest.setString(2, version);

-

-			ResultSet data = prest.executeQuery();

-			con.commit();

-			while (data.next()) {

-				// TODO: This is just a horrible idea. Make a typo anywhere for the key, it still compiles but doesn't work.

-				// Somebody was too lazy to create a proper struct/class here....

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

-				map.put("shareMode", data.getString("image_syncMode"));

-				map.put("filesize", data.getString("image_filesize"));

-				map.put("path", data.getString("image_path"));

-				map.put("template", data.getString("image_isTemplate"));

-				map.put("os", data.getString("GuestOS"));

-				map.put("owner", data.getString("Owner"));

-				map.put("lastupdate", data.getString("image_update_time"));

-				map.put("desc", data.getString("image_description"));

-				map.put("id", data.getString("GUID_imageID"));

-				map.put("version", data.getString("imageVersion"));

-				map.put("mail", data.getString("mail"));

-

-			}

-			con.close();

-			return map;

-		} catch (SQLException e) {

-

-			log.info("Failed to getImageData.");

-			e.printStackTrace();

-		}

-		return null;

-	}

-

-	public boolean updateImageData(String newName, String desc, boolean license, boolean internet,

-			String imageId, String shareMode, String ospk) {

-		try {

-			Connection con = getConnection();

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

-

-			int internet_bol = 0;

-			int license_bol = 0;

-

-			if (internet == true) {

-				internet_bol = 1;

-			}

-

-			if (license == true) {

-				license_bol = 1;

-			}

-

-			String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET " + "imageVersion = " + "?" + ", "

-					+ "image_name = " + "?" + ",image_description = " + "?"

-					+ " ,image_update_time =  '"

-					+ formatter.format(new Date())

-					+ "' ,rec_change_time =  '"

-					+ formatter.format(new Date())

-					+ "' ,cond_hasLicenseRestriction =  "

-					+ "?"

-					+ " ,cond_hasInternetRestriction =  "

-					+ "?"

-					+ " ,cond_minRAM =  "

-					+ "?"

-					+ " ,cond_minCPUs =  "

-					+ "?"

-					+ " ,image_isTemplate =  "

-					+ "0 "

-					+ ",content_operatingSystem = "

-					+ "?"

-					+ ",image_syncMode =  "

-					+ "?"

-					+ "  WHERE GUID_imageID =  " + "?" + "  AND imageVersion = " + "?" + ";";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setInt(1, 1);

-			prest.setString(2, newName);

-			prest.setString(3, desc);

-			prest.setInt(4, license_bol);

-			prest.setInt(5, internet_bol);

-			prest.setLong(6, 2);

-			prest.setLong(7, 2);

-			prest.setString(8, ospk);

-			prest.setString(9, shareMode);

-			prest.setString(10, imageId);

-			prest.setString(11, "1");

-

-			prest.executeUpdate();

-

-			con.commit(); // needed, though executeUpdate()!

-

-			// con.commit();

-			con.close();

-			return true;

-		} catch (SQLException e) {

-

-			log.info("Failed to UpdateImageData.");

-			e.printStackTrace();

-		}

-		return false;

-	}

-

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

-

-		try {

-			Connection con = getConnection();

-

-			// delete the image record

-			String sql = "DELETE FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = ? AND imageVersion = ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-			prest.setString(2, version);

-			prest.executeUpdate();

-

-			// delete all permission records for this image

-			sql = "DELETE FROM bwLehrpool.pm_VLData_image WHERE GUID_imageID = ?";

-			prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-			prest.executeUpdate();

-

-			con.commit();

-			con.close();

-			return true;

-

-		} catch (SQLException e) {

-

-			log.info("Failed to deleteImage and permissions.");

-			e.printStackTrace();

-		}

-

-		return false;

-	}

-

-	public int updateLectureData(String pk_image, String imageversion, String name, String newName,

-			String desc, String shortdesc, String start, String end, boolean isactive, String id) {

-

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

-		int active_bol = 0;

-

-		if (isactive == true) {

-			active_bol = 1;

-		}

-		try {

-			Connection con = getConnection();

-

-			String sql = "UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = " + "?" + ",isActive = " + "?"

-					+ ",startTime = " + "?" + ",endTime = " + "?" + ",description = " + "?" + ",imageID = "

-					+ "?" + ",imageVersion = " + "?" + ",admin_changeTime = '" + formatter.format(new Date())

-					+ "'WHERE `lectureID` = ?;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, newName);

-			prest.setInt(2, active_bol);

-			prest.setString(3, start);

-			prest.setString(4, end);

-			prest.setString(5, desc);

-			prest.setString(6, pk_image);

-			prest.setString(7, imageversion);

-			prest.setString(8, id);

-

-			prest.executeUpdate();

-			con.commit();

-			con.close();

-			// log.info("Succeeded to updateLectureData.");

-

-		} catch (SQLException e) {

-

-			log.info("Failed to updateLectureData.");

-			e.printStackTrace();

-		}

-

-		return 0;

-

-	}

-

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

-

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT lectureID FROM "

-					+ "bwLehrpool.m_VLData_lecture WHERE imageID = ? AND imageVersion = ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-			prest.setString(2, version);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-			boolean ret = rs.first();

-			con.close();

-			return ret;

-

-		} catch (SQLException e) {

-

-			log.info("Failed to execute method connectedToLecture.");

-			e.printStackTrace();

-		}

-

-		return false;

-

-	}

-

-	public boolean deleteLecture(String id) {

-

-		try {

-			Connection con = getConnection();

-

-			// delete the lecture record

-			String sql = "DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-

-			prest.executeUpdate();

-

-			// delete all permission records for this lecture

-			sql = "DELETE FROM bwLehrpool.pm_VLData_lecture WHERE lectureID = ?;";

-			prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-

-			prest.executeUpdate();

-

-			con.commit();

-			con.close();

-			return true;

-		} catch (SQLException e) {

-

-			log.info("Failed to deleteLecture and permissions.");

-			e.printStackTrace();

-		}

-

-		return false;

-	}

-

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

-		String path = null;

-		Connection con = null;

-

-		try {

-			con = getConnection();

-

-			String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = " + "?"

-					+ " AND imageVersion = " + "?" + ";";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, imageid);

-			prest.setString(2, imageversion);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-

-			if (rs.next())

-				path = rs.getString("image_path");

-

-			return path;

-

-		} catch (SQLException e) {

-			log.info("Failed to getFile.", e);

-		} finally {

-			Util.safeClose(con);

-		}

-

-		return null;

-	}

-

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

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

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID = ?;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

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

-

-			log.info("Failed to getDeleteXMLData.");

-			e.printStackTrace();

-		}

-

-		return null;

-	}

-

-	public int UpdateImagePath(String name) {

-		try {

-			Connection con = getConnection();

-

-			String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET image_path = " + "?" // prod/+name

-					+ " WHERE image_path = " + "?" // temp/+name

-					+ ";";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, "prod/" + name);

-			prest.setString(2, "temp/" + name);

-

-			prest.executeUpdate();

-

-			con.commit();

-			con.close();

-			// log.info("Succesfully updated image path");

-			return 0;

-		} catch (SQLException e) {

-

-			log.info("Failed to UpdateImagePath.");

-			e.printStackTrace();

-		}

-		return -1;

-	}

-

-	public List<String> getAllUniversities() {

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

-		try {

-			Connection con = getConnection();

-			Statement stm = con.createStatement();

-			ResultSet rs = stm.executeQuery("SELECT name FROM bwLehrpool.m_institution;");

-			while (rs.next()) {

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

-			}

-			return list;

-

-		} catch (SQLException e) {

-

-			log.info("Failed to getAllUniversities.");

-			e.printStackTrace();

-		}

-

-		return null;

-

-	}

-

-	public String getOSpk(String os, String architecture) {

-		try {

-			Connection con = getConnection();

-			// log.info("Getting PrimaryKey for OS: " + os + " architecture: " +

-			// architecture);

-

-			String sql = "SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like ? and architecture like ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, os);

-			prest.setString(2, architecture);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-			rs.first();

-			String pkos = rs.getString("operatingSystemID");

-			// log.info(" - Key is " + pkos);

-			con.close();

-			return pkos;

-

-		} catch (SQLException e) {

-

-			log.info("Failed to getOSpk.");

-			e.printStackTrace();

-		}

-

-		return null;

-	}

-

-	public String getOS(String pk) {

-		try {

-			Connection con = getConnection();

-			// log.info(new Date() +

-			// " - OS: "+os+" architecture: "+architecture);

-			String sql = "SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = ?;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, pk);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-			rs.first();

-			String os = rs.getString("name") + " " + rs.getString("architecture");

-			con.close();

-			return os;

-

-		} catch (SQLException e) {

-

-			log.info("Failed to getOS.");

-			e.printStackTrace();

-		}

-

-		return null;

-	}

-

-	public Map<String, String> getLectureData(String id) {

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

-		try {

-			Connection con = getConnection();

-

-			String sql = "select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, l.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i  where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= ?";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, id);

-

-			ResultSet data = prest.executeQuery();

-			con.commit();

-			while (data.next()) {

-				map.put("name", data.getString("name"));

-				map.put("isActive", data.getString("isActive"));

-				map.put("startTime", data.getString("startTime"));

-				map.put("endTime", data.getString("endTime"));

-				map.put("lastUsed", data.getString("lastUsed"));

-				map.put("desc", data.getString("description"));

-				map.put("imagepath", data.getString("image_path"));

-				map.put("imageversion", data.getString("imageVersion"));

-				map.put("owner", data.getString("Owner"));

-

-			}

-			con.close();

-			return map;

-		} catch (SQLException e) {

-

-			log.info("Failed to getLectureData.");

-			e.printStackTrace();

-		}

-		return null;

-	}

-

-	public boolean checkUser(String username) {

-

-		boolean ret = false;

-		try {

-			Connection con = getConnection();

-

-			String sql = "Select * from m_user where loginName like ?";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, username);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-			if (rs.next()) {

-				ret = true;

-				log.info("Checking user: " + rs.getString("loginName") + " exists.");

-			} else {

-				ret = false;

-				log.info("Checking user: " + rs.getString("loginName") + " does not exist.");

-			}

-			con.close();

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return ret;

-	}

-

-	public int getRoleID(String role) {

-

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT roleID FROM bwLehrpool.m_role where name like ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, role);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-			rs.first();

-

-			return rs.getInt("roleID");

-

-		} catch (SQLException e) {

-

-			log.info("Failed to getRoleID.");

-			e.printStackTrace();

-		}

-		return -1;

-	}

-

-	public String getLectureID(String name) {

-		String id = null;

-		try {

-			Connection con = getConnection();

-

-			String sql = "SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, name);

-

-			ResultSet lecture = prest.executeQuery();

-			con.commit();

-			while (lecture.next()) {

-				id = lecture.getString("lectureID");

-

-			}

-			con.close();

-			return id;

-		} catch (SQLException e) {

-

-			log.info("Failed to getLectureID.");

-			e.printStackTrace();

-		}

-		return id;

-	}

-

-	// get all users from satellite -- temp, tb replaced by allUsers bwLehrpool

-	// does -NOT- return the user himself

-	// TODO prepared statement

-	public List<Person> getAllOtherSatelliteUsers(List<String> userID) {

-		Connection con = getConnection();

-		Statement stm = null;

-		ResultSet res = null;

-

-		List<Person> list = new ArrayList<Person>();

-

-		try {

-			stm = con.createStatement();

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		String query = "SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID NOT IN ('"

-				+ userID.get(0) + "";

-

-		for (int i = 1; i < userID.size(); i++) {

-			query = query + "','" + userID.get(i);

-		}

-		query = query + "') ORDER BY Nachname ASC;";

-

-		try {

-			res = stm.executeQuery(query);

-		} catch (SQLException e1) {

-

-			e1.printStackTrace();

-		}

-

-		try {

-			while (res.next()) {

-				// fill the list with users - permissions are all false because

-				// the image is new

-				list.add(new Person(res.getString("userID"), res.getString("Nachname"),

-						res.getString("Vorname"), res.getString("mail"), false, false, false, false, false,

-						false, false));

-			}

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return list;

-	}// end getAllOtherSatelliteUsers()

-

-	// get the permissions of an image for all other registered users -EXCEPT-

-	// the logged on user

-	public List<Person> getPermissionForUserAndImage(String userID, String imageID) {

-

-		Connection con = getConnection();

-		ResultSet res = null;

-		List<Person> list = new ArrayList<Person>();

-

-		try {

-

-			String sql = "SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, u.mail, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!= "

-					+ "?"

-					+ " AND pmi.GUID_imageID= "

-					+ "?"

-					+ " AND pmi.userID=u.userID ORDER BY u.Nachname ASC;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-			prest.setString(2, imageID);

-

-			res = prest.executeQuery();

-			con.commit();

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		try {

-			boolean image_read, image_write, link_allowed, image_admin;

-

-			while (res.next()) {

-				// reset old values and set the correct new values

-				image_read = false;

-				image_write = false;

-				link_allowed = false;

-				image_admin = false;

-

-				// TODO: Are those really non-numeric columns? If yes, keep in mind parseInt can throw an exception...

-				if (Integer.parseInt(res.getString("image_read")) != 0) {

-					image_read = true;

-				}

-				if (Integer.parseInt(res.getString("image_write")) != 0) {

-					image_write = true;

-				}

-				if (Integer.parseInt(res.getString("link_allowed")) != 0) {

-					link_allowed = true;

-				}

-				if (Integer.parseInt(res.getString("image_admin")) != 0) {

-					image_admin = true;

-				}

-

-				// fill the list with users - permissions are all false because

-				// the image is new

-				list.add(new Person(res.getString("userID"), res.getString("Nachname"),

-						res.getString("Vorname"), res.getString("mail"), image_read, image_write,

-						link_allowed, image_admin, false, // lecture_read

-						false, // lecture_write

-						false) // lecture_admin

-				);

-				// System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin")));

-			}

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return list;

-	}// end getPermissionFOrUserAndImage()

-

-	// get the permissions of a lecture for all other registered users -EXCEPT-

-	// the logged on user

-	public List<Person> getPermissionForUserAndLecture(String userID, String lectureID) {

-

-		Connection con = getConnection();

-		// Statement stm = null;

-		ResultSet res = null;

-		List<Person> list = new ArrayList<Person>();

-

-		try {

-

-			String sql = "SELECT DISTINCT pml.lectureID, pml.userID, u.userID, u.Nachname,	u.Vorname, u.mail, pml.rec_read, pml.rec_write,	pml.rec_admin "

-					+ "FROM bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u WHERE pml.userID!= ? AND pml.lectureID= ? AND pml.userID=u.userID ORDER BY u.Nachname ASC;";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-			prest.setString(2, lectureID);

-

-			res = prest.executeQuery();

-			con.commit();

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		try {

-			boolean lecture_read, lecture_write, lecture_admin;

-

-			while (res.next()) {

-				// reset old values and set the correct new values

-				lecture_read = false;

-				lecture_write = false;

-				lecture_admin = false;

-

-				if (Integer.parseInt(res.getString("rec_read")) != 0) {

-					lecture_read = true;

-				}

-				if (Integer.parseInt(res.getString("rec_write")) != 0) {

-					lecture_write = true;

-				}

-				if (Integer.parseInt(res.getString("rec_admin")) != 0) {

-					lecture_admin = true;

-				}

-

-				// fill the list with users - permissions are all false because

-				// the image is new

-				list.add(new Person(res.getString("userID"), res.getString("Nachname"),

-						res.getString("Vorname"), res.getString("mail"), false, // image read

-						false, // image write

-						false, // image link

-						false, // image admin

-						lecture_read, // lecture_read

-						lecture_write, // lecture_write

-						lecture_admin) // lecture_admin

-				);

-				// System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin")));

+	public static MysqlConnection getConnection() {

+		MysqlConnection con;

+		for (;;) {

+			con = pool.poll();

+			if (con == null)

+				break;

+			if (!con.isValid()) {

+				con.release();

+				continue;

 			}

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return list;

-	}// end getPermissionForUserAndLecture

-

-	public int setImageRights(String pk_person, String pk_image, int role, int read, int write,

-			int linkallowed, int admin) {

-

-		try {

-			Connection con = getConnection();

-			// Statement stm = con.createStatement();

-

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

-

-			String sql = "INSERT INTO bwLehrpool.pm_VLData_image(GUID,GUID_imageID,roleID,userID,image_read,image_write,image_admin,link_allowed)VALUES"

-					+ "(?,?,?,?,?,?,?,?)";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-

-			prest.setString(1, uid);

-			prest.setString(2, pk_image);

-			prest.setInt(3, role);

-			prest.setString(4, pk_person);

-			prest.setInt(5, read);

-			prest.setInt(6, write);

-			prest.setInt(7, admin);

-			prest.setInt(8, linkallowed);

-

-			prest.executeUpdate();

-

-			con.commit();

-			con.close();

-

-		} catch (SQLException e) {

-

-			log.info("Failed to setImageRights.");

-			e.printStackTrace();

-		}

-		return 0;

-	}// end setImageRights

-

-	public boolean writeAdditionalImageRights(String imageID, String userID, boolean isRead, boolean isWrite,

-			boolean isLinkAllowed, boolean isAdmin) {

-		boolean success = true;

-

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

-

-		Connection con = getConnection();

-

-		try {

-

-			String sql = "INSERT INTO pm_VLData_image (" + "GUID, " + "GUID_imageID, " + "roleID, "

-					+ "userID, " + "image_read, " + "image_write, " + "link_allowed," + "Image_admin)"

-					+ "VALUES" + "(?,?,?,?,?,?,?,?)";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-

-			prest.setString(1, uid);

-			prest.setString(2, imageID);

-			prest.setInt(3, 1);

-			prest.setString(4, userID);

-			prest.setBoolean(5, isRead);

-			prest.setBoolean(6, isWrite);

-			prest.setBoolean(7, isLinkAllowed);

-			prest.setBoolean(8, isAdmin);

-

-			prest.executeUpdate();

-

-			con.commit();

-			con.close();

-

-			// log.info("Written additional image rights.");

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-			log.info("Failed to write additional image rights.");

-		}

-		return success;

-	}// end writeAdditionalImageRights

-

-	public int setLectureRights(String pk_person, String pk_lecture, int role, int read, int write, int admin) {

-

-		try {

-			Connection con = getConnection();

-			// Statement stm = con.createStatement();

-

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

-			String sql = "INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES"

-					+ "(?,?,?,?,?,?)";

-

-			PreparedStatement prest = con.prepareStatement(sql);

-

-			prest.setString(1, uid);

-			prest.setString(2, pk_lecture);

-			prest.setString(3, pk_person);

-			prest.setInt(4, read);

-			prest.setInt(5, write);

-			prest.setInt(6, admin);

-

-			prest.executeUpdate();

-

-			con.commit();

-			log.info("Written lecture rights for lecture '" + pk_lecture + "'.");

-			con.close();

-		} catch (SQLException e) {

-

-			log.info("Failed to setLectureRights.");

-			e.printStackTrace();

-		}

-		return 0;

-	} // end setLectureRights

-

-	public boolean writeAdditionalLectureRights(String lectureID, String userID, boolean isRead,

-			boolean isWrite, boolean isAdmin) {

-		boolean success = true;

-

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

-		Connection con = getConnection();

-

-		try {

-

-			String sql = "INSERT INTO pm_VLData_lecture (GUID, lectureID, userID, rec_read, rec_write, rec_admin) VALUES"

-					+ ("(?,?,?,?,?,?)");

-

-			PreparedStatement prest = con.prepareStatement(sql);

-

-			prest.setString(1, uid);

-			prest.setString(2, lectureID);

-			prest.setString(3, userID);

-			prest.setBoolean(4, isRead);

-			prest.setBoolean(5, isWrite);

-			prest.setBoolean(6, isAdmin);

-

-			// log.info("userdata: "+uid+" "+lectureID + " " + userID + " " +

-			// isRead + isWrite + isAdmin);

-

-			prest.executeUpdate();

-

-			con.commit();

-			con.close();

-			// log.info("Written additional lecture rights for '"+ userID +

-			// "'.");

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-			log.info("Failed to write additional lecture rights.");

-		}

-		return success;

-	}// end writeAdditionalLectureRights

-

-	public void deleteAllAdditionalImagePermissions(String imageID, String userID) {

-		Connection con = getConnection();

-

-		try {

-

-			String sql = "DELETE FROM pm_VLData_image WHERE GUID_imageID = ? AND userID != ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-

-			prest.setString(1, imageID);

-			prest.setString(2, userID);

-

-			prest.executeUpdate();

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-

-		try {

-

-			con.commit();

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-	}// end deleteAllAdditionalImagePermissions

-

-	public void deleteAllAdditionalLecturePermissions(String lectureID, String userID) {

-		Connection con = getConnection();

-

-		try {

-

-			String sql = "DELETE FROM pm_VLData_lecture WHERE lectureID = ? AND userID != ?;";

-			PreparedStatement prest = con.prepareStatement(sql);

-

-			prest.setString(1, lectureID);

-			prest.setString(2, userID);

-

-			prest.executeUpdate();

-		} catch (SQLException e) {

-			e.printStackTrace();

-		}

-

-		try {

-

-			con.commit();

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

+			if (!busyConnections.add(con))

+				throw new RuntimeException("Tried to hand out a busy connection!");

+			return con;

 		}

-	}// end deleteAllAdditionalImagePermissions

-

-	public List<String> getAdditionalImageContacts(String imageID) {

-

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

-

-		Connection con = getConnection();

-		try {

-

-			String sql = "select distinct u.Nachname, u.Vorname, u.mail from bwLehrpool.pm_VLData_image img, bwLehrpool.m_user u where GUID_imageID = ? and image_write = '1' and img.userID = u.userID;";

-			PreparedStatement prest = con.prepareStatement(sql);

-

-			prest.setString(1, imageID);

-

-			ResultSet rs = prest.executeQuery();

-			con.commit();

-

-			while (rs.next()) {

-				list.add(rs.getString("Nachname") + "," + rs.getString("Vorname") + " ");

-			}

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

+		// No pooled connection

+		if (busyConnections.size() > 20) {

+			LOGGER.warn("Too many open MySQL connections. Possible connection leak!");

+			return null;

 		}

-

-		return list;

-

-	}

-

-	public String getOsNameForGuestOs(String guestOS) {

-

-		Connection con = getConnection();

-		ResultSet rs = null;

-		String sql = "Select name, architecture from bwLehrpool.m_operatingSystem where guestOS = ?";

-

 		try {

-

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, guestOS);

-			rs = prest.executeQuery();

-			con.commit();

-			rs.next();

-

-			return rs.getString("name") + " " + rs.getString("architecture") + " bit";

-

+			// Create fresh connection

+			Connection rawConnection = DriverManager.getConnection(Configuration.getDbUri(),

+					Configuration.getDbUsername(), Configuration.getDbPassword());

+			// By convention in our program we don't want auto commit

+			rawConnection.setAutoCommit(false);

+			// Wrap into our proxy

+			con = new MysqlConnection(rawConnection);

+			// Keep track of busy mysql connection

+			if (!busyConnections.add(con))

+				throw new RuntimeException("Tried to hand out a busy connection!");

+			return con;

 		} catch (SQLException e) {

-

-			e.printStackTrace();

+			LOGGER.info("Failed to connect to local mysql server", e);

 		}

-

 		return null;

-

-	}

-

-	public String createRandomUUID() {

-		return UUID.randomUUID().toString();

-	}

-

-	public boolean userIsImageAdmin(String userID, String imageID) {

-		Connection con = getConnection();

-		ResultSet rs = null;

-		String sql = "SELECT image_admin FROM bwLehrpool.pm_VLData_image WHERE userID= ? AND GUID_imageID=?";

-

-		log.info("stmt user is image admin: "

-				+ "SELECT image_admin FROM bwLehrpool.pm_VLData_image WHERE userID= '" + userID

-				+ "' AND GUID_imageID='" + imageID + "'");

-

-		try {

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-			prest.setString(2, imageID);

-			rs = prest.executeQuery();

-			con.commit();

-			rs.next();

-

-			return rs.getBoolean("image_admin");

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return false;

 	}

 

-	public boolean userIsLectureAdmin(String userID, String lectureID) {

-		Connection con = getConnection();

-		ResultSet rs = null;

-		String sql = "SELECT rec_admin FROM bwLehrpool.pm_VLData_lecture WHERE userID= ? AND lectureID=?";

-

-		try {

-			PreparedStatement prest = con.prepareStatement(sql);

-			prest.setString(1, userID);

-			prest.setString(2, lectureID);

-			rs = prest.executeQuery();

-			con.commit();

-			rs.next();

-

-			return rs.getBoolean("rec_admin");

-

-		} catch (SQLException e) {

-

-			e.printStackTrace();

-		}

-		return false;

-	}

-

-	public String getInstitutionByID(String institutionID, String errorParam) {

-

-		Connection con = getConnection();

-		ResultSet rs = null;

-		String sql = "SELECT name FROM bwLehrpool.m_institution WHERE institutionID = ?";

-

-		log.info("intitution ID is: " + institutionID);

-

-		PreparedStatement prest;

-		try {

-

-			prest = con.prepareStatement(sql);

-			prest.setString(1, institutionID);

-

-			rs = prest.executeQuery();

-			// con.commit();

-			rs.next();

-			return rs.getString("name");

-

-		} catch (SQLException e) {

-			// TODO Auto-generated catch block

-			e.printStackTrace();

-		}

-

-		return null;

-

+	static void returnConnection(MysqlConnection connection) {

+		if (!busyConnections.remove(connection))

+			throw new RuntimeException("Tried to return a mysql connection to the pool that was not taken!");

+		pool.add(connection);

 	}

 

 }// end class