From 61e8e600dd1c9d6066711dfb9874e0117b87baf6 Mon Sep 17 00:00:00 2001 From: Jonathan Bauer Date: Wed, 10 Sep 2014 14:54:40 +0200 Subject: mavenization v2 --- Dozentenmodulserver/src/main/java/sql/SQL.java | 279 +++++++++++++++++++++++-- 1 file changed, 258 insertions(+), 21 deletions(-) (limited to 'Dozentenmodulserver/src/main/java/sql') diff --git a/Dozentenmodulserver/src/main/java/sql/SQL.java b/Dozentenmodulserver/src/main/java/sql/SQL.java index ec768a40..9a115f09 100644 --- a/Dozentenmodulserver/src/main/java/sql/SQL.java +++ b/Dozentenmodulserver/src/main/java/sql/SQL.java @@ -3,8 +3,10 @@ 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; @@ -230,8 +232,8 @@ public class SQL { } public boolean setImageData(String pk_person, boolean license, - boolean internet, long cpu, long ram, String imagename, - String imagePath, boolean isTemplate, long filesize, String shareMode) { + 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"); @@ -249,10 +251,11 @@ public class SQL { Statement stm = con.createStatement(); String uid = UUID.randomUUID().toString(); - stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES('" + 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 @@ -263,8 +266,8 @@ public class SQL { + "','" + formatter.format(new Date()) // rec_change_time + "','" + pk_person // rec_owner + "','" + pk_person // rec_change_by - + "',0" // content_operatingSystem - + ",1" // status_isCompressed + + "','" + pk_os // content_operatingSystem + + "',1" // status_isCompressed + ",1" // status_isSecure + ",1" // status_isOptimzed + ",1" // status_isValid @@ -296,22 +299,23 @@ public class SQL { 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, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); + .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("lecture"), + .getString("name")+" "+res.getString("architecture")+" bit", res.getString("lecture"), res.getString("image_update_time"), res - .getString("user"))); + .getString("user"), res.getString("image_isTemplate"))); } @@ -319,7 +323,7 @@ public class SQL { } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); - } + } return list; @@ -358,9 +362,9 @@ public class SQL { Connection con=getConnection(); Statement stm = con.createStatement(); ResultSet rs=stm - .executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;"); + .executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem;"); while (rs.next()) { - list.add(rs.getString("name")); + list.add(rs.getString("name")+" "+rs.getString("architecture")+" bit"); } return list; @@ -487,8 +491,7 @@ public class SQL { Connection con=getConnection(); Statement stm = con.createStatement(); ResultSet data=stm - .executeQuery("SELECT image_name,cond_hasInternetRestriction,cond_hasLicenseRestriction, cond_minCPUs, cond_minRAM, image_syncMode,image_filesize,image_path, image_isTemplate FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '" - + id + "' and imageVersion = '" + version + "' ;"); + .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", @@ -500,6 +503,14 @@ public class SQL { 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; @@ -511,9 +522,9 @@ public class SQL { return null; } - public int UpdateImageData(String name, String newName,String image_path, + 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 version, boolean isTemplate, long filesize, String shareMode,String ospk) { try { Connection con=getConnection(); DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); @@ -534,6 +545,8 @@ public class SQL { + newVersion + "',`image_name` = '" + newName + + "',`image_description` = '" + + desc + "',`image_path` = '" + image_path + "',`image_update_time` = '" @@ -550,6 +563,8 @@ public class SQL { + cpu + "',`image_isTemplate` = '" + isTemplate + + "',`content_operatingSystem` = '" + + ospk + "',`image_filesize` = '" + filesize + "',`image_syncMode` = '" @@ -591,7 +606,7 @@ public class SQL { } public int updateLectureData(String pk_image, - int imageversion, String name, String newName, String desc, + String imageversion, String name, String newName, String desc, String shortdesc, String start, String end, boolean isactive, String id) { @@ -644,8 +659,9 @@ public class SQL { ResultSet rs = stm.executeQuery("SELECT lectureID FROM " + "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id + "' AND imageVersion = '" + version + "';"); + boolean ret=rs.first(); con.close(); - return rs.first(); + return ret; } catch (SQLException e) { // TODO Auto-generated catch block @@ -746,7 +762,7 @@ public class SQL { return 0; } catch (SQLException e) { // TODO Auto-generated catch block - log.info(new Date() + " - Failed to UpdateImageData."); + log.info(new Date() + " - Failed to UpdateImagePath."); e.printStackTrace(); } return -1; @@ -773,4 +789,225 @@ public class SQL { 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