From e90286005f1cbedc77bfff91f867cb6aec25a595 Mon Sep 17 00:00:00 2001 From: Jonathan Bauer Date: Thu, 11 Sep 2014 15:04:50 +0200 Subject: oops --- .../Dozentenmodulserver/src/main/java/sql/SQL.java | 1013 -------------------- 1 file changed, 1013 deletions(-) delete mode 100644 dozentenmodulserver/Dozentenmodulserver/src/main/java/sql/SQL.java (limited to 'dozentenmodulserver/Dozentenmodulserver/src/main/java/sql') diff --git a/dozentenmodulserver/Dozentenmodulserver/src/main/java/sql/SQL.java b/dozentenmodulserver/Dozentenmodulserver/src/main/java/sql/SQL.java deleted file mode 100644 index 9a115f09..00000000 --- a/dozentenmodulserver/Dozentenmodulserver/src/main/java/sql/SQL.java +++ /dev/null @@ -1,1013 +0,0 @@ -package sql; - -import java.sql.*; - -import java.text.DateFormat; -import java.text.ParseException; -import java.text.SimpleDateFormat; -import java.util.ArrayList; -import java.util.Calendar; -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 { - - private static Logger log = Logger.getLogger(BinaryListener.class); - - public Connection getConnection() { - try { - Class.forName("com.mysql.jdbc.Driver").newInstance(); - } catch (InstantiationException | IllegalAccessException - | ClassNotFoundException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } - try { - Connection con = DriverManager - .getConnection("jdbc:mysql://"+Configuration.config.getSql_connection()+"?user="+Configuration.config.getSql_user()+"&password="+Configuration.config.getSql_pass()+""); - con.setAutoCommit(false); - - return con; - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to return connection to Client."); - e.printStackTrace(); - } - return null; - } - - public int writeFTPUser(String user, String pass) { - Statement stm; - try { - Connection con=getConnection(); - stm = con.createStatement(); - - int ret = stm - .executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('" - + user - + "',SHA1('" - + pass - + "'),'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() + " - Failed to writeFTPUser."); - e.printStackTrace(); - } - return -1; - } - - 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() + " - Failed to DeleteUser."); - e.printStackTrace(); - } - return -1; - } - - 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) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getImage."); - e.printStackTrace(); - } - - return null; - } - - public String getPathOfImage(String image_id, - String version) { - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - ResultSet rs=stm - .executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='" - + image_id - + "' and imageVersion='" - + version - + "';"); - rs.first(); - 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 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) { - - String id = UUID.randomUUID().toString(); - stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('" - + id + "','" + university + "');"); - con.commit(); - ResultSet rs = stm - .executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '" - + university + "';"); - rs.next(); - String institution=rs.getString("institutionID"); - con.close(); - - return institution; - } else { - 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"; - } - - 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 - .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" - + lastname - + "' and Vorname like '" - + firstname - + "';"); - - if (ret.next() == false) { - - String id = UUID.randomUUID().toString(); - stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('" - + id - + "','" - + login - + "','" - + lastname - + "','" - + firstname - + "','" - + mail - + "','" - + formatter.format(new Date()) - + "','" - + Institution - + "');"); - con.commit(); - ResultSet rs = stm - .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" - + lastname - + "' and Vorname like '" - + firstname - + "';"); - 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) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setPerson."); - e.printStackTrace(); - } - return "-1"; - } - - public boolean setImageData(String pk_person, boolean license, - boolean internet, long cpu, long ram, String imagename,String desc, - String imagePath, boolean isTemplate, long filesize, String shareMode, String pk_os) { - - 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(); - Statement stm = con.createStatement(); - - String uid = UUID.randomUUID().toString(); - stm.executeUpdate("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('" - + uid // GUID_imageID - + "',1,'" // imageVersion - + imagename // image_name - + "','" +desc //image_description - + "','" + imagePath // image_path - + "','" + formatter.format(new Date()) // image_lastCall - + "','" + formatter.format(new Date()) // image_create_time - + "','" + formatter.format(new Date()) // image_update_time - + "','" + pk_person // image_owner - + "','" + pk_person // image_change_by - + "','" + formatter.format(new Date()) // rec_create_time - + "','" + formatter.format(new Date()) // rec_change_time - + "','" + pk_person // rec_owner - + "','" + pk_person // rec_change_by - + "','" + pk_os // content_operatingSystem - + "',1" // status_isCompressed - + ",1" // status_isSecure - + ",1" // status_isOptimzed - + ",1" // status_isValid - + ",1" // status_isReady - + ",0" // status_isDeleted - + ",0,'" // status_isLastOfficialVersion - + license_bol // cond_hasLicenseRestriction - + "','" + internet_bol // cond_hasInternetRestriction - + "','" + ram // cond_minRAM - + "','" + cpu // cond_minCPUs - + "','" + isTemplate //image_isTemplate - + "','" + filesize //image_filesize - + "','" + shareMode //shareMode - + "');"); - con.commit(); - con.close(); - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setImageData."); - e.printStackTrace(); - } - return true; - - } - - public List getImageList() { - - Connection con=getConnection(); - Statement stm; - List list = new ArrayList(); - try { - - stm = con.createStatement(); - ResultSet res=stm - .executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate 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,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate 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("architecture")+" bit", res.getString("lecture"), - res.getString("image_update_time"), res - .getString("user"), res.getString("image_isTemplate"))); - - } - - con.close(); - } catch (SQLException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } - - - return list; - - } - - public List getLectureList() { - List list = new ArrayList(); - 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;"); - 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 List getAllOS() { - List list = new ArrayList<>(); - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - ResultSet rs=stm - .executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem;"); - while (rs.next()) { - list.add(rs.getString("name")+" "+rs.getString("architecture")+" bit"); - } - return list; - - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getAllOS."); - e.printStackTrace(); - } - - return null; - - } - - public Map getPersonData(String Vorname, - String Nachname) { - Map map = new HashMap(); - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - 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(String pk_person, String pk_image, - int imageversion, String name, String desc, String shortdesc, - String start, String end, boolean isactive) { - - DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); - int active_bol = 0; - - if (isactive == true) { - active_bol = 1; - } - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - - 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 - + "','" - + name - + "','" - + active_bol - + "','" - + start - + "','" - + end - + "','" - + formatter.format(new Date()) - + "','" - + shortdesc - + "','" - + desc - + "','" - + pk_image - + "','" - + imageversion - + "','" - + formatter.format(new Date()) - + "','" - + formatter.format(new Date()) - + "','" - + pk_person - + "','" - + 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 Map getImageIDandVersion(String name) { - Map map = new HashMap(); - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - 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 Map getImageData(String id, String version) { - Map map = new HashMap(); - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - ResultSet data=stm - .executeQuery("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 = '" + id + "' and imageVersion = '" + version + "' ;"); - while (data.next()) { - 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) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getImageData."); - e.printStackTrace(); - } - return null; - } - - public int UpdateImageData(String name, String newName,String desc,String image_path, - boolean license, boolean internet, long cpu, long ram, String id, - String version, boolean isTemplate, long filesize, String shareMode,String ospk) { - try { - Connection con=getConnection(); - DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); - Statement stm = con.createStatement(); - int newVersion = Integer.parseInt(version) + 1; - - int internet_bol = 0; - int license_bol = 0; - if (internet == true) { - internet_bol = 1; - } - if (license == true) { - license_bol = 1; - } - 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_description` = '" - + desc - + "',`image_path` = '" - + image_path - + "',`image_update_time` = '" - + formatter.format(new Date()) - + "',`rec_change_time` = '" - + formatter.format(new Date()) - + "',`cond_hasLicenseRestriction` = '" - + license_bol - + "',`cond_hasInternetRestriction` = '" - + internet_bol - + "',`cond_minRAM` = '" - + ram - + "',`cond_minCPUs` = '" - + cpu - + "',`image_isTemplate` = '" - + isTemplate - + "',`content_operatingSystem` = '" - + ospk - + "',`image_filesize` = '" - + filesize - + "',`image_syncMode` = '" - + shareMode - + "' 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; - } - - 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(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(); - Statement stm = con.createStatement(); - stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '" - + newName - + "',`isActive` = '" - + active_bol - + "',`startTime` = '" - + start - + "',`endTime` = '" - + end - + "',`description` = '" - + desc - + "',`imageID` = '" - + pk_image - + "',`imageVersion` = '" - + imageversion - + "',`admin_changeTime` = '" - + 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(); - } - - return 0; - - } - - 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 + "';"); - boolean ret=rs.first(); - con.close(); - return ret; - - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() - + " - Failed to execute method connectedToLecture."); - e.printStackTrace(); - } - - return false; - - } - - 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 - log.info(new Date() + " - Failed to deleteLecture."); - e.printStackTrace(); - } - - return false; - } - - public String getFile(String imageid, String imageversion) { - - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - - 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 getDeleteXMLData(String id) { - Map map = new HashMap(); - 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 UpdateImagePath."); - e.printStackTrace(); - } - return -1; - } - - public List getAllUniversities() { - List 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) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getAllUniversities."); - e.printStackTrace(); - } - - return null; - - } - - public String getOSpk(String os, - String architecture) { - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - log.info(new Date() + " - Getting PrimaryKey for OS: "+os+" architecture: "+architecture); - ResultSet rs=stm - .executeQuery("SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like '" - + os - + "' and architecture like '" - + architecture - + "';"); - rs.first(); - String pkos=rs.getString("operatingSystemID"); - log.info(new Date() + " - Key is "+pkos); - con.close(); - return pkos; - - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getOSpk."); - e.printStackTrace(); - } - - return null; - } - - public String getOS(String pk) { - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - //log.info(new Date() + " - OS: "+os+" architecture: "+architecture); - ResultSet rs=stm - .executeQuery("SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = '" - + pk - + "';"); - rs.first(); - String os=rs.getString("name")+" "+rs.getString("architecture"); - con.close(); - return os; - - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getOS."); - e.printStackTrace(); - } - - return null; - } - - public Map getLectureData(String id) { - Map map = new HashMap(); - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - ResultSet data=stm - .executeQuery("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= '" + id + "' ;"); - 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) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getLectureData."); - e.printStackTrace(); - } - return null; - } - - public boolean checkUser(String username) { - Statement stm; - boolean ret=false; - try { - Connection con=getConnection(); - stm = con.createStatement(); - ResultSet rs=stm.executeQuery("Select * from m_user where loginName like '"+username+"'"); - if(rs.next()) - { - ret=true; - log.info(new Date() + " - User " + rs.getString("loginName") + " exist."); - } - else{ - ret=false; - log.info(new Date() + " - User " + rs.getString("loginName") + " not exist."); - } - con.close(); - - - } catch (SQLException e) { - // TODO Auto-generated catch block - - e.printStackTrace(); - } - return ret; - } - - public int setImageRights(String pk_person, String pk_image,int role ,int read, int write, int changePermission, int admin,int linkallowed) { - - - - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - - String uid = UUID.randomUUID().toString(); - stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_image`(`GUID`,`GUID_imageID`,`roleID`,`userID`,`image_read`,`image_write`,`image_admin`,`image_changePermission`,`link_allowed`)VALUES('" - + uid - + "','" - + pk_image - + "','" - + role - + "','" - + pk_person - + "','" - + read - + "','" - + write - + "','" - + admin - + "','" - + changePermission - + "','" - + linkallowed+ "');"); - con.commit(); - con.close(); - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setImageRights."); - e.printStackTrace(); - } - return 0; - - } - public int getRoleID(String role) { - - - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - - ResultSet rs=stm - .executeQuery("SELECT roleID FROM bwLehrpool.m_role where name like '"+ role+ "';"); - rs.first(); - - return rs.getInt("roleID"); - - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getRoleID."); - e.printStackTrace(); - } - return -1; - } - public int setLectureRights(String pk_person, String pk_lecture,int role ,int read, int write, int changePermission, int admin) { - - - - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - - String uid = UUID.randomUUID().toString(); - stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`roleID`,`userID`,`rec_read`,`rec_write`,`rec_admin`,`rec_changePermission`)VALUES('" - + uid - + "','" - + pk_lecture - + "','" - + role - + "','" - + pk_person - + "','" - + read - + "','" - + write - + "','" - + admin - + "','" - + changePermission+ "');"); - con.commit(); - con.close(); - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to setLectureRights."); - e.printStackTrace(); - } - return 0; - - } - public String getLectureID(String name) { - String id=null; - try { - Connection con=getConnection(); - Statement stm = con.createStatement(); - ResultSet lecture= stm - .executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like '" - + name + "';"); - while (lecture.next()) { - id=lecture.getString("lectureID"); - - } - con.close(); - return id; - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to LectureID."); - e.printStackTrace(); - } - return id; - } -} -- cgit v1.2.3-55-g7522