From 4435e7d7a96bb921465d77dcf9dd06d6471d482d Mon Sep 17 00:00:00 2001 From: Nino Breuer Date: Thu, 20 Nov 2014 15:25:43 +0100 Subject: • removed commented regular statements which were used before prepared statements. • starting implementing the user authentication, this version is usable but without authentication --- dozentenmodulserver/src/main/java/sql/SQL.java | 454 ++----------------------- 1 file changed, 19 insertions(+), 435 deletions(-) diff --git a/dozentenmodulserver/src/main/java/sql/SQL.java b/dozentenmodulserver/src/main/java/sql/SQL.java index ca15bb8b..9cee34e1 100644 --- a/dozentenmodulserver/src/main/java/sql/SQL.java +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -129,12 +129,6 @@ public class SQL { Connection con = getConnection(); stm = con.createStatement(); - /* - int ret = stm - .executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '" - + user + "';"); - */ - String sql = "DELETE FROM `bwLehrpool`.`FtpUsers` where User like ?;"; PreparedStatement prest = con.prepareStatement(sql); prest.setString(1, user); @@ -183,14 +177,6 @@ public class SQL { 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 - + "';"); - */ String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID= " + "?" @@ -226,14 +212,6 @@ public class SQL { 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 + "';"); - - */ String sql = "SELECT * FROM bwLehrpool.m_institution where name like ?;"; PreparedStatement prest = con.prepareStatement(sql); @@ -245,11 +223,7 @@ public class SQL { { String id = UUID.randomUUID().toString(); - - /* - stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('" - + id + "','" + university + "');"); - */ + sql = "INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`) VALUES" + "(?,?)"; prest = con.prepareStatement(sql); @@ -259,12 +233,6 @@ public class SQL { con.commit(); - /* - ResultSet rs = stm - .executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '" - + university + "';"); - */ - sql = "SELECT institutionID FROM bwLehrpool.m_institution WHERE name like ?;"; prest = con.prepareStatement(sql); @@ -304,16 +272,7 @@ public class SQL { 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 - + "';"); - */ String sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + "?" @@ -330,25 +289,6 @@ public class SQL { if (ret.next() == false) { - // String id = UUID.randomUUID().toString(); - /* - stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('" - + login - + "','" - + login - + "','" - + lastname - + "','" - + firstname - + "','" - + mail - + "','" - + formatter.format(new Date()) - + "','" - + Institution - + "');"); - */ - sql = "INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES(" + "?" + "," @@ -376,15 +316,6 @@ public class SQL { ResultSet rs = prest.executeQuery(); con.commit(); - //con.commit(); - /* - ResultSet rs = stm - .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" - + lastname - + "' and Vorname like '" - + firstname - + "';"); - */ sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + "?" + " and Vorname like " @@ -419,12 +350,7 @@ public class SQL { } - - - - - - + public boolean setImageData(String pk_person, boolean license, boolean internet, long cpu, long ram, String imagename, String desc, String imagePath, long filesize, String shareMode, @@ -443,49 +369,9 @@ public class SQL { try { Connection con = getConnection(); - //Statement stm = con.createStatement(); // create a UUID - //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 - // Ticket #1144 ist hier zu lösen, desc sollte nicht zu lang - // sein, sonst merkert die DB - + "','" + 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 - + "','0'" // image_isTemplate - + ",'" + filesize // image_filesize - + "','" + shareMode // shareMode - + "');"); - */ - 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 @@ -558,18 +444,10 @@ public class SQL { public List getImageListPermissionWrite(String userID) { Connection con = getConnection(); - //Statement stm; List list = new ArrayList(); try { - //stm = con.createStatement(); - - /*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 AND pmi.userID = '" - + userID - + "'AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"); - */ 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;"; @@ -609,20 +487,10 @@ public class SQL { public List getImageListPermissionRead(String userID) { Connection con = getConnection(); - //Statement stm; List list = new ArrayList(); 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 AND pmi.userID = '" - + userID - + "'AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"); - */ 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 = " + "?" @@ -702,34 +570,13 @@ public class SQL { - - - - public List getImageListPermissionLink(String userID) { Connection con = getConnection(); - //Statement stm; List list = new ArrayList(); try { - //stm = con.createStatement(); - - // ResultSet WITHOUT templates - //ResultSet res = stm - // .executeQuery("SELECT DISTINCT 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.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE pmi.userID='" - // + userID - // + "' AND u.userID = pmi.userID AND (pmi.link_allowed=1 OR vl.image_isTemplate=1) AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); - - // ResultSet WITH templates - /* - 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 AND pmi.userID = '" - + userID - + "'AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"); - */ - 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;"; @@ -761,29 +608,14 @@ public class SQL { return list; } - - - - + public List getImageListPermissionAdmin(String userID){ Connection con = getConnection(); - //Statement stm; List list = new ArrayList(); 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 AND pmi.userID = '" - + userID - + "'AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"); - */ - - 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;"; @@ -864,9 +696,6 @@ public class SQL { }//end getImageListAllTemplates - - - public List getLectureListPermissionRead(String userID) { List list = new ArrayList(); @@ -903,10 +732,7 @@ public class SQL { return list; }// end getLectureListPermissionRead - - - - + public List getLectureListPermissionWrite(String userID) { List list = new ArrayList(); @@ -956,15 +782,7 @@ public class SQL { try { Connection con = getConnection(); - //Statement stm = con.createStatement(); - - /*ResultSet res = stm - .executeQuery("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 i.GUID_imageID=l.imageID AND pml.rec_admin=true AND l.lectureID=pml.lectureID AND u.userID=pml.userID AND u.userID='" - + userID + "';"); - */ - + 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;"; @@ -1037,8 +855,8 @@ public class SQL { public List getAllOS() throws InvalidTokenException { - if(authenticated()) - { + //if(authenticated()) + //{ List list = new ArrayList<>(); try { Connection con = getConnection(); @@ -1059,8 +877,8 @@ public class SQL { return null; - } - return null; + //} + //return null; } @@ -1072,16 +890,7 @@ public class SQL { 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;"); - */ + 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 " @@ -1223,41 +1032,7 @@ public class SQL { 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 + "');"); - - */ 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(" + "?" + "," @@ -1302,8 +1077,7 @@ public class SQL { prest.executeUpdate(); con.commit(); - - //con.commit(); + con.close(); } catch (SQLException e) { @@ -1321,13 +1095,7 @@ public class SQL { 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 + "';"); - */ - + String sql = "SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = ?"; PreparedStatement prest = con.prepareStatement(sql); @@ -1359,14 +1127,7 @@ public class SQL { 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 + "' ;"); - */ - + 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); @@ -1458,43 +1219,6 @@ public class SQL { + version + ";"); */ - /* - 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 - + "0'" - + ",`content_operatingSystem` = " - + ospk - + ",`image_filesize` = " - + filesize - + ",`image_syncMode` = '" - + shareMode - + "' WHERE GUID_imageID = '" - + id - + "' AND imageVersion = " - + version + ";"); - - */ - String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = " + "?" + ",image_name = " @@ -1567,10 +1291,8 @@ public class SQL { try { Connection con = getConnection(); - //Statement stm = con.createStatement(); //delete the image record - //stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = '"+ id + "' " + "AND imageVersion = '" + version + "';"); String sql = "DELETE FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = ? AND imageVersion = ?;"; PreparedStatement prest = con.prepareStatement(sql); prest.setString(1, id); @@ -1578,7 +1300,6 @@ public class SQL { prest.executeUpdate(); //delete all permission records for this image - //stm.executeUpdate("DELETE FROM bwLehrpool.pm_VLData_image WHERE GUID_imageID = '"+id+"';"); sql = "DELETE FROM bwLehrpool.pm_VLData_image WHERE GUID_imageID = ?"; prest = con.prepareStatement(sql); prest.setString(1, id); @@ -1616,27 +1337,7 @@ public class SQL { } 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 + "';"); - */ - + String sql = "UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = " + "?" + ",isActive = " @@ -1685,9 +1386,6 @@ public class SQL { 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 + "';"); String sql = "SELECT lectureID FROM "+ "bwLehrpool.m_VLData_lecture WHERE imageID = ? AND imageVersion = ?;"; PreparedStatement prest = con.prepareStatement(sql); @@ -1717,19 +1415,15 @@ public class SQL { try { Connection con = getConnection(); - //Statement stm = con.createStatement(); //delete the lecture record - //stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = '" + id + "';"); 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 - //stm.executeUpdate("DELETE FROM bwLehrpool.pm_VLData_lecture WHERE lectureID = '" + id + "';"); - + //delete all permission records for this lecture con.commit(); con.close(); return true; @@ -1750,14 +1444,6 @@ public class SQL { 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 - + "';"); - * - */ String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = " + "?" + " AND imageVersion = " @@ -1793,9 +1479,7 @@ public class SQL { 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 + "';"); String sql = "SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID = ?;"; PreparedStatement prest = con.prepareStatement(sql); @@ -1878,8 +1562,6 @@ public class SQL { return null; } - - public String getOSpk(String os, String architecture) { @@ -1887,7 +1569,6 @@ public class SQL { Connection con = getConnection(); Statement stm = con.createStatement(); //log.info("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+ "';"); String sql = "SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like ? and architecture like ?;"; PreparedStatement prest = con.prepareStatement(sql); @@ -1921,7 +1602,6 @@ public class SQL { 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 + "';"); String sql = "SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = ?;"; PreparedStatement prest = con.prepareStatement(sql); @@ -1950,8 +1630,6 @@ public class SQL { 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 + "' ;"); 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); @@ -1989,8 +1667,6 @@ public class SQL { boolean ret = false; try { Connection con = getConnection(); - //stm = con.createStatement(); - //ResultSet rs = stm.executeQuery("Select * from m_user where loginName like '"+ username + "'"); String sql = "Select * from m_user where loginName like ?"; PreparedStatement prest = con.prepareStatement(sql); @@ -2026,11 +1702,6 @@ public class SQL { try { Connection con = getConnection(); Statement stm = con.createStatement(); - - /*ResultSet rs = stm - .executeQuery("SELECT roleID FROM bwLehrpool.m_role where name like '" - + role + "';"); - rs.first();*/ String sql = "SELECT roleID FROM bwLehrpool.m_role where name like ?;"; PreparedStatement prest = con.prepareStatement(sql); @@ -2058,7 +1729,7 @@ public class SQL { try { Connection con = getConnection(); Statement stm = con.createStatement(); - //ResultSet lecture = stm.executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like '"+ name + "';"); + String sql = "SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like ?;"; PreparedStatement prest = con.prepareStatement(sql); prest.setString(1, name); @@ -2154,13 +1825,6 @@ public class SQL { } try { - /*res = stm - .executeQuery("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!='" - + userID - + "' AND pmi.GUID_imageID='" - + imageID - + "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;");*/ - 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!= " + "?" @@ -2174,13 +1838,7 @@ public class SQL { res = prest.executeQuery(); con.commit(); - - /*System.out - .println("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!='" - + userID - + "' AND pmi.GUID_imageID='" - + imageID - + "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;");*/ + } catch (SQLException e) { e.printStackTrace(); @@ -2246,10 +1904,7 @@ public class SQL { try { - /*res = stm - .executeQuery("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!='"+userID+"' AND pml.lectureID='"+lectureID+"' AND pml.userID=u.userID ORDER BY u.Nachname ASC;"); - */ + 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;"; @@ -2260,10 +1915,6 @@ public class SQL { res = prest.executeQuery(); con.commit(); - /*System.out - .println("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!='"+userID+"' AND pml.lectureID='"+lectureID+"' AND pml.userID=u.userID ORDER BY u.Nachname ASC;"); - */ } catch (SQLException e) { e.printStackTrace(); @@ -2328,25 +1979,7 @@ public class SQL { //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`,`link_allowed`)VALUES('" - + uid - + "','" - + pk_image - + "','" - + role - + "','" - + pk_person - + "','" - + read - + "','" - + write - + "','" - + admin - + "','" - + linkallowed + "');"); - - */ + 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); @@ -2382,18 +2015,6 @@ public class SQL { Connection con = getConnection(); try { - - /* - log.info("INSERT INTO pm_VLData_image (" + "GUID, " - + "GUID_imageID, " + "roleID, " + "userID, " - + "image_read, " + "image_write, " + "Image_admin, " - + "link_allowed)" + - - "VALUES(" + "'" + uid + "'," + "'" - + imageID + "'," + "1," + "'" + userID + "'," + isRead - + "," + isWrite + "," + isAdmin + "," + isLinkAllowed + "" - + ");"); - */ String sql = "INSERT INTO pm_VLData_image (" + "GUID, " + "GUID_imageID, " + "roleID, " + "userID, " @@ -2450,16 +2071,6 @@ public class SQL { prest.executeUpdate(); - /* - stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES('" - + uid - + "','" - + pk_lecture - + "','" - + pk_person - + "','" - + read + "','" + write + "','" + admin + "');"); - */ con.commit(); log.info("Written lecture rights for lecture '" + pk_lecture+"'."); con.close(); @@ -2478,19 +2089,9 @@ public class SQL { String uid = UUID.randomUUID().toString(); Connection con = getConnection(); - //Statement stm = null; try { - //stm = con.createStatement(); - /* - stm.executeUpdate("INSERT INTO pm_VLData_lecture (" + "GUID, " - + "lectureID, " + "userID, " + "rec_read, " + "rec_write, " - + "rec_admin )" + - "VALUES('" + uid + "'," + "'" + lectureID - + "'," + "'" + userID + "'," + isRead + "," + isWrite + "," - + isAdmin + ");"); - */ String sql = "INSERT INTO pm_VLData_lecture (GUID, lectureID, userID, rec_read, rec_write, rec_admin) VALUES" + ("(?,?,?,?,?,?)"); @@ -2526,9 +2127,6 @@ public class SQL { Statement stm = null; try { - - //stm = con.createStatement(); - //int ret = stm.executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '"+ imageID + "' AND userID != '" + userID + "';"); String sql = "DELETE FROM pm_VLData_image WHERE GUID_imageID = ? AND userID != ?;"; PreparedStatement prest = con.prepareStatement(sql); @@ -2545,9 +2143,6 @@ public class SQL { try { - //stm = con.createStatement(); - //int ret = stm.executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '"+ imageID + "' AND userID != '" + userID + "';"); - con.commit(); } catch (SQLException e) { @@ -2560,12 +2155,9 @@ public class SQL { public void deleteAllAdditionalLecturePermissions(String lectureID, String userID) { Connection con = getConnection(); - //Statement stm = null; try { - //stm = con.createStatement(); - //int ret = stm.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"+ lectureID + "' AND userID != '" + userID + "';"); String sql = "DELETE FROM pm_VLData_lecture WHERE lectureID = ? AND userID != ?;"; PreparedStatement prest = con.prepareStatement(sql); @@ -2579,9 +2171,6 @@ public class SQL { try { - //stm = con.createStatement(); - //int ret = stm.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"+ lectureID + "' AND userID != '" + userID + "';"); - con.commit(); } catch (SQLException e) { @@ -2601,8 +2190,6 @@ public class SQL { Statement stm; try { - //stm = con.createStatement(); - //ResultSet rs = stm.executeQuery("select distinct u.Nachname, u.Vorname, u.mail from bwLehrpool.pm_VLData_image img, bwLehrpool.m_user u where GUID_imageID = '"+imageID+"' and image_write = '1' and img.userID = u.userID;"); 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); @@ -2624,12 +2211,9 @@ public class SQL { return list; - } - - public String getOsNameForGuestOs(String guestOS) { Connection con = getConnection(); -- cgit v1.2.3-55-g7522