From 9a7ba7cfaf9e9f6f166246b95756ec2929e00ac4 Mon Sep 17 00:00:00 2001 From: Tobias Spitzer Date: Thu, 22 May 2014 12:09:36 +0200 Subject: NULL-Fehler behoben --- Dozentenmodulserver/src/sql/SQL.java | 236 ++++++++++++++++++++++++++--------- 1 file changed, 178 insertions(+), 58 deletions(-) (limited to 'Dozentenmodulserver/src/sql') diff --git a/Dozentenmodulserver/src/sql/SQL.java b/Dozentenmodulserver/src/sql/SQL.java index 69843988..6d8bb8b7 100644 --- a/Dozentenmodulserver/src/sql/SQL.java +++ b/Dozentenmodulserver/src/sql/SQL.java @@ -4,7 +4,11 @@ import java.sql.*; import java.text.DateFormat; import java.text.SimpleDateFormat; +import java.util.ArrayList; import java.util.Date; +import java.util.HashMap; +import java.util.List; +import java.util.Map; import java.util.Random; import java.util.UUID; @@ -12,6 +16,8 @@ import models.Configuration; import org.apache.log4j.Logger; import server.BinaryListener; +import server.Image; +import server.Lecture; public class SQL { @@ -39,9 +45,10 @@ public class SQL { return null; } - public int writeFTPUser(Connection con, String user, String pass) { + public int writeFTPUser(String user, String pass) { Statement stm; try { + Connection con=getConnection(); stm = con.createStatement(); int ret = stm @@ -51,6 +58,7 @@ public class SQL { + pass + "'),'10001','12345','"+Configuration.config.getAbsolute_path()+"temp/');"); con.commit(); + con.close(); log.info(new Date() + " - created FTPUser " + user + " : " + pass + "."); return ret; @@ -62,15 +70,17 @@ public class SQL { return -1; } - public int DeleteUser(Connection con, String user) { + 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) { @@ -81,13 +91,15 @@ public class SQL { return -1; } - public ResultSet getImage(Connection con) { + public ResultSet getImage() { try { + Connection con=getConnection(); Statement stm = con.createStatement(); - - return stm + 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."); @@ -97,17 +109,20 @@ public class SQL { return null; } - public ResultSet getPathOfImage(Connection con, String image_id, + public String getPathOfImage(String image_id, String version) { try { + Connection con=getConnection(); Statement stm = con.createStatement(); - - return stm + ResultSet rs=stm .executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='" + image_id + "' and imageVersion='" + version + "';"); + String path=rs.getString("image_path"); + con.close(); + return path; } catch (SQLException e) { // TODO Auto-generated catch block @@ -118,8 +133,9 @@ public class SQL { return null; } - public String setInstitution(Connection con, String university) { + public String setInstitution(String university) { try { + Connection con=getConnection(); Statement stm = con.createStatement(); ResultSet ret = stm @@ -135,9 +151,14 @@ public class SQL { .executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '" + university + "';"); rs.next(); - return rs.getString("institutionID"); + String institution=rs.getString("institutionID"); + con.close(); + + return institution; } else { - return ret.getString("institutionID"); + String institution=ret.getString("institutionID"); + con.close(); + return institution; } } catch (SQLException e) { @@ -148,10 +169,11 @@ public class SQL { return "-1"; } - public String setPerson(Connection con, String login, String lastname, + 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 @@ -187,10 +209,14 @@ public class SQL { + firstname + "';"); rs.next(); - return rs.getString("userID"); + String userid=rs.getString("userID"); + con.close(); + return userid; } else { ret.first(); - return ret.getString("userID"); + String userid=ret.getString("userID"); + con.close(); + return userid; } } catch (SQLException e) { @@ -201,7 +227,7 @@ public class SQL { return "-1"; } - public boolean setImageData(Connection con, String pk_person, boolean license, + public boolean setImageData(String pk_person, boolean license, boolean internet, long cpu, long ram, String imagename, String imagePath, boolean isTemplate, long filesize) { @@ -217,6 +243,7 @@ public class SQL { } try { + Connection con=getConnection(); Statement stm = con.createStatement(); String uid = UUID.randomUUID().toString(); @@ -250,7 +277,7 @@ public class SQL { + "','" + filesize //image_filesize + "');"); con.commit(); - + con.close(); } catch (SQLException e) { // TODO Auto-generated catch block log.info(new Date() + " - Failed to setImageData."); @@ -260,28 +287,60 @@ public class SQL { } - public ResultSet getImageList(Connection con) { - try { - Statement stm = con.createStatement(); + 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, '' 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;"); + - return 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;"); + - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info(new Date() + " - Failed to getImageList."); - e.printStackTrace(); - } - return null; + 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"), + res.getString("image_update_time"), res + .getString("user"))); + + } + + con.close(); + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + + + return list; + } - public ResultSet getLectureList(Connection con) { + public List getLectureList() { + List list = new ArrayList(); try { + Connection con=getConnection(); Statement stm = con.createStatement(); - - return stm + 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."); @@ -290,12 +349,18 @@ public class SQL { return null; } - public ResultSet getAllOS(Connection con) { - + public List getAllOS() { + List list = new ArrayList<>(); try { + Connection con=getConnection(); Statement stm = con.createStatement(); - return stm + ResultSet rs=stm .executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;"); + while (rs.next()) { + list.add(rs.getString("name")); + } + return list; + } catch (SQLException e) { // TODO Auto-generated catch block log.info(new Date() + " - Failed to getAllOS."); @@ -306,16 +371,28 @@ public class SQL { } - public ResultSet getPersonData(Connection con, String Vorname, + public Map getPersonData(String Vorname, String Nachname) { + Map map = new HashMap(); try { + Connection con=getConnection(); Statement stm = con.createStatement(); - return stm + 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."); @@ -324,7 +401,7 @@ public class SQL { return null; } - public int setLectureData(Connection con, String pk_person, String pk_image, + public int setLectureData(String pk_person, String pk_image, int imageversion, String name, String desc, String shortdesc, String start, String end, boolean isactive) { @@ -335,6 +412,7 @@ public class SQL { active_bol = 1; } try { + Connection con=getConnection(); Statement stm = con.createStatement(); String uid = UUID.randomUUID().toString(); @@ -367,7 +445,7 @@ public class SQL { + "','" + pk_person + "');"); con.commit(); - + con.close(); } catch (SQLException e) { // TODO Auto-generated catch block log.info(new Date() + " - Failed to setLectureData."); @@ -377,12 +455,21 @@ public class SQL { } - public ResultSet getImageIDandVersion(Connection con, String name) { + public Map getImageIDandVersion(String name) { + Map map = new HashMap(); try { + Connection con=getConnection(); Statement stm = con.createStatement(); - return stm + 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."); @@ -391,12 +478,29 @@ public class SQL { return null; } - public ResultSet getImageData(Connection con, String id, String version) { + public Map getImageData(String id, String version) { + Map map = new HashMap(); try { + Connection con=getConnection(); Statement stm = con.createStatement(); - return stm + ResultSet data=stm .executeQuery("SELECT image_name,cond_hasInternetRestriction,cond_hasLicenseRestriction, cond_minCPUs, cond_minRAM FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '" + id + "' and imageVersion = '" + version + "' ;"); + while (data.next()) { + System.out.println(data + .getString("cond_hasInternetRestriction") + + data.getString("cond_hasLicenseRestriction") + + data.getString("cond_minCPUs") + + data.getString("cond_minRAM")); + 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")); + } + con.close(); + return map; } catch (SQLException e) { // TODO Auto-generated catch block log.info(new Date() + " - Failed to getImageData."); @@ -405,10 +509,11 @@ public class SQL { return null; } - public int UpdateImageData(Connection con, String name, String newName,String image_path, + public int UpdateImageData(String name, String newName,String image_path, boolean license, boolean internet, long cpu, long ram, String id, String version, boolean isTemplate, long filesize) { try { + Connection con=getConnection(); DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); Statement stm = con.createStatement(); int newVersion = Integer.parseInt(version) + 1; @@ -449,6 +554,7 @@ public class SQL { + id + "' AND `imageVersion` = '" + version + "';"); con.commit(); + con.close(); return 0; } catch (SQLException e) { // TODO Auto-generated catch block @@ -458,17 +564,17 @@ public class SQL { return -1; } - public boolean deleteImage(Connection con, String id, String version) { + 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) { @@ -480,7 +586,7 @@ public class SQL { return false; } - public int updateLectureData(Connection con, String pk_image, + public int updateLectureData(String pk_image, int imageversion, String name, String newName, String desc, String shortdesc, String start, String end, boolean isactive, String id) { @@ -492,6 +598,7 @@ public class SQL { active_bol = 1; } try { + Connection con=getConnection(); Statement stm = con.createStatement(); stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '" + newName @@ -511,6 +618,7 @@ public class SQL { + formatter.format(new Date()) + "'WHERE `lectureID` = '" + id + "';"); con.commit(); + con.close(); log.info(new Date() + " - Succeeded to updateLectureData."); } catch (SQLException e) { @@ -523,16 +631,16 @@ public class SQL { } - public boolean connectedToLecture(Connection con, String id, String version) { + 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 + "';"); - + con.close(); return rs.first(); } catch (SQLException e) { @@ -546,15 +654,16 @@ public class SQL { } - public boolean deleteLecture(Connection con, String id) { + 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 @@ -565,9 +674,10 @@ public class SQL { return false; } - public String getFile(Connection con, String imageid, String imageversion) { + public String getFile(String imageid, String imageversion) { try { + Connection con=getConnection(); Statement stm = con.createStatement(); ResultSet rs = stm @@ -577,6 +687,7 @@ public class SQL { + imageversion + "';"); rs.next(); + con.close(); return rs.getString("image_path"); } catch (SQLException e) { @@ -588,15 +699,23 @@ public class SQL { return null; } - public ResultSet getDeleteXMLData(Connection con, String id) { + 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")); + - return rs; + con.close(); + return map; } catch (SQLException e) { // TODO Auto-generated catch block @@ -607,9 +726,9 @@ public class SQL { return null; } - public int UpdateImagePath(Connection con, String name) { + public int UpdateImagePath(String name) { try { - + Connection con=getConnection(); Statement stm = con.createStatement(); String image_path="prod/"+name; @@ -618,6 +737,7 @@ public class SQL { + "' WHERE `image_path` = '" + "temp/"+name + "';"); con.commit(); + con.close(); return 0; } catch (SQLException e) { // TODO Auto-generated catch block -- cgit v1.2.3-55-g7522