diff options
| -rw-r--r-- | dozentenmodulserver/src/main/java/sql/SQL.java | 454 |
1 files 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<Image> getImageListPermissionWrite(String userID) {
Connection con = getConnection();
- //Statement stm;
List<Image> list = new ArrayList<Image>();
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<Image> getImageListPermissionRead(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 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<Image> getImageListPermissionLink(String userID) {
Connection con = getConnection();
- //Statement stm;
List<Image> list = new ArrayList<Image>();
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<Image> getImageListPermissionAdmin(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 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<Lecture> getLectureListPermissionRead(String userID) {
List<Lecture> list = new ArrayList<Lecture>();
@@ -903,10 +732,7 @@ public class SQL { return list;
}// end getLectureListPermissionRead
-
-
-
-
+
public List<Lecture> getLectureListPermissionWrite(String userID) {
List<Lecture> list = new ArrayList<Lecture>();
@@ -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<String> getAllOS() throws InvalidTokenException
{
- if(authenticated())
- {
+ //if(authenticated())
+ //{
List<String> 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<String, String> map = new HashMap<String, String>();
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<String, String> map = new HashMap<String, String>();
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<String, String> map = new HashMap<String, String>();
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<String, String> map = new HashMap<String, String>();
try {
Connection con = getConnection();
- //Statement stm = con.createStatement();
- //ResultSet rs = stm.executeQuery("SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID='"+ id + "';");
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<String, String> map = new HashMap<String, String>();
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();
|