From 60d32ae1cbc729c664888b1a255845a10fed34b1 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 8 Apr 2014 15:59:46 +0200 Subject: Logging weiter ausgebaut, XML werden nun mit einem eigenen Namen erzeugt und gespeichert --- Dozentenmodulserver/src/sql/SQL.java | 440 ++++++++++++++++++++++------------- 1 file changed, 280 insertions(+), 160 deletions(-) (limited to 'Dozentenmodulserver/src/sql/SQL.java') diff --git a/Dozentenmodulserver/src/sql/SQL.java b/Dozentenmodulserver/src/sql/SQL.java index f28fdd67..0a2f5ab2 100644 --- a/Dozentenmodulserver/src/sql/SQL.java +++ b/Dozentenmodulserver/src/sql/SQL.java @@ -10,314 +10,434 @@ import java.text.SimpleDateFormat; import java.util.Date; import java.util.Random; +import org.apache.log4j.Logger; + +import server.BinaryListener; public class SQL { + + private static Logger log = Logger.getLogger( BinaryListener.class ); - public Connection getConnection() - { + public Connection getConnection() { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); - } + } try { - Connection con=DriverManager.getConnection("jdbc:mysql://127.0.0.1/bwLehrpool?user=root&password=slx-ng-open"); + Connection con = DriverManager + .getConnection("jdbc:mysql://127.0.0.1/bwLehrpool?user=root&password=slx-ng-open"); con.setAutoCommit(false); + log.info(new Date() + " - Connection returned to Client."); return con; } catch (SQLException e) { // TODO Auto-generated catch block + log.info(new Date() + " - Could not return connection to Client."); e.printStackTrace(); } return null; } - - public int writeFTPUser(Connection con,String user, String pass) - { + + public int writeFTPUser(Connection con, String user, String pass) { Statement stm; try { - stm=con.createStatement(); - - int ret=stm.executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('"+user+"',SHA1('"+pass+"'),'10001','12345','/srv/openslx/nfs/temp');"); + stm = con.createStatement(); + + int ret = stm + .executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('" + + user + + "',SHA1('" + + pass + + "'),'10001','12345','/srv/openslx/nfs/temp');"); con.commit(); + log.info(new Date() + " - created FTPUser "+user+" : "+pass+"."); return ret; } catch (SQLException e) { // TODO Auto-generated catch block + log.info(new Date() + " - FTPUser not created."); e.printStackTrace(); } return -1; } - - public int DeleteUser(Connection con, String user) - { + + public int DeleteUser(Connection con, String user) { Statement stm; try { - stm=con.createStatement(); - - int ret=stm.executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '"+user+"';"); + stm = con.createStatement(); + + int ret = stm + .executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '" + + user + "';"); con.commit(); + log.info(new Date() + " - FTPUser "+user+" deleted."); return ret; } catch (SQLException e) { // TODO Auto-generated catch block + log.info(new Date() + " - Could not delete FTPUser "+user+"."); e.printStackTrace(); } return -1; } - - public ResultSet getImage(Connection con) - { + + public ResultSet getImage(Connection con) { try { - Statement stm=con.createStatement(); - - return stm.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;"); - - + Statement stm = con.createStatement(); + + return stm + .executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;"); + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } - - + return null; } - public ResultSet getPathOfImage(Connection con, String image_id, String version) - { + public ResultSet getPathOfImage(Connection con, String image_id, + String version) { try { - Statement stm=con.createStatement(); - - return stm.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='"+image_id+"' and imageVersion='"+version+"';"); - - + Statement stm = con.createStatement(); + + return stm + .executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='" + + image_id + + "' and imageVersion='" + + version + + "';"); + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } - - + return null; } - - public int setInstitution(Connection con,String university) - { + + public int setInstitution(Connection con, String university) { try { - Statement stm=con.createStatement(); - - - - ResultSet ret=stm.executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'"+university+"';"); - if(ret.next()==false) - { - Random rand=new Random(); - int id=rand.nextInt(); - stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('"+id+"','"+university+"');"); + Statement stm = con.createStatement(); + + ResultSet ret = stm + .executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'" + + university + "';"); + if (ret.next() == false) { + Random rand = new Random(); + int id = rand.nextInt(); + stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('" + + id + "','" + university + "');"); con.commit(); - ResultSet rs=stm.executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '"+university+"';"); + ResultSet rs = stm + .executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '" + + university + "';"); rs.next(); return rs.getInt("institutionID"); - } - else - { + } else { return ret.getInt("institutionID"); } - + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return -1; } - - - - public int setPerson(Connection con,String login, String lastname, String firstname, String mail, Date lastlogin,int Institution) - { + + public int setPerson(Connection con, String login, String lastname, + String firstname, String mail, Date lastlogin, int Institution) { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); try { - Statement stm=con.createStatement(); - - ResultSet ret=stm.executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '"+lastname+"' and Vorname like '"+firstname+"';"); - if(ret.next()==false) - { - Random rand=new Random(); - int id=rand.nextInt(); - stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('"+id+"','Platzhalter','"+lastname+"','"+firstname+"','"+mail+"','"+formatter.format(new Date())+"','"+Institution+"');"); - con.commit(); - ResultSet rs=stm.executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '"+lastname+"' and Vorname like '"+firstname+"';"); - rs.next(); - return rs.getInt("userID"); - } - else - { + Statement stm = con.createStatement(); + + ResultSet ret = stm + .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" + + lastname + + "' and Vorname like '" + + firstname + + "';"); + if (ret.next() == false) { + Random rand = new Random(); + int id = rand.nextInt(); + stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('" + + id + + "','Platzhalter','" + + lastname + + "','" + + firstname + + "','" + + mail + + "','" + + formatter.format(new Date()) + + "','" + + Institution + + "');"); + con.commit(); + ResultSet rs = stm + .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" + + lastname + + "' and Vorname like '" + + firstname + + "';"); + rs.next(); + return rs.getInt("userID"); + } else { return ret.getInt("userID"); } - + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return -1; } - - public boolean setImageData(Connection con, int pk_person, boolean license, boolean internet, long cpu, long ram, String imagename, String imagePath) - { - + + public boolean setImageData(Connection con, int pk_person, boolean license, + boolean internet, long cpu, long ram, String imagename, + String imagePath) { + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); - - int internet_bol=0; - int license_bol=0; - if(internet==true) - { - internet_bol=1; + + int internet_bol = 0; + int license_bol = 0; + if (internet == true) { + internet_bol = 1; } - if(license==true) - { - license_bol=1; + if (license == true) { + license_bol = 1; } try { - Statement stm=con.createStatement(); - Random random=new Random(); - int uid=random.nextInt(); - 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`)VALUES('"+uid+"',1,'"+imagename+"','"+imagePath+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"',1,1,1,1,1,1,0,0,'"+license_bol+"','"+internet_bol+"','"+ram+"','"+cpu+"');"); + Statement stm = con.createStatement(); + Random random = new Random(); + int uid = random.nextInt(); + 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`)VALUES('" + + uid //GUID_imageID + + "',1,'" //imageVersion + + imagename //image_name + + "','" + + 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 + + "',0" //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 + + "');"); con.commit(); - + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return true; - + } - - public ResultSet getImageList(Connection con){ + + public ResultSet getImageList(Connection con) { try { - Statement stm=con.createStatement(); - - 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;"); - - + Statement stm = con.createStatement(); + + 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 e.printStackTrace(); } return null; } - - public ResultSet getLectureList(Connection con){ + + public ResultSet getLectureList(Connection con) { try { - Statement stm=con.createStatement(); - - return 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;"); - - + Statement stm = con.createStatement(); + + return 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;"); + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } - - - public ResultSet getAllOS(Connection con) - { - - + + public ResultSet getAllOS(Connection con) { + try { - Statement stm=con.createStatement(); - return stm.executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;"); + Statement stm = con.createStatement(); + return stm + .executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } - - return null; - + } - - public ResultSet getPersonData(Connection con,String Vorname, String Nachname) - { + + public ResultSet getPersonData(Connection con, String Vorname, + String Nachname) { try { - Statement stm=con.createStatement(); - return 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;"); + Statement stm = con.createStatement(); + return 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;"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } - - public int setLectureData(Connection con, int pk_person, int pk_image,int imageversion, String name, String desc, String shortdesc,String start, String end, boolean isactive) - { - + + public int setLectureData(Connection con, int pk_person, int pk_image, + int imageversion, String name, String desc, String shortdesc, + String start, String end, boolean isactive) { + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); - int active_bol=0; + int active_bol = 0; - if(isactive==true) - { - active_bol=1; + if (isactive == true) { + active_bol = 1; } try { - Statement stm=con.createStatement(); - Random random=new Random(); - int uid=random.nextInt(); - 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+"');"); + Statement stm = con.createStatement(); + Random random = new Random(); + int uid = random.nextInt(); + stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_lecture`(`lectureID`,`name`,`isActive`,`startTime`,`endTime`,`lastUsed`,`shortDescription`,`description`,`imageID`,`imageVersion`,`admin_createTime`,`admin_changeTime`,`admin_owner`,`admin_change_by`)VALUES('" + + uid + + "','" + + name + + "','" + + active_bol + + "','" + + start + + "','" + + end + + "','" + + formatter.format(new Date()) + + "','" + + shortdesc + + "','" + + desc + + "','" + + pk_image + + "','" + + imageversion + + "','" + + formatter.format(new Date()) + + "','" + + formatter.format(new Date()) + + "','" + + pk_person + + "','" + + pk_person + "');"); con.commit(); - + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return 0; - + } - - public ResultSet getImageIDandVersion(Connection con,String name) - { + + public ResultSet getImageIDandVersion(Connection con, String name) { try { - Statement stm=con.createStatement(); - return stm.executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '"+name+"';"); + Statement stm = con.createStatement(); + return stm + .executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '" + + name + "';"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } - - public ResultSet getImageData(Connection con,String id, String version){ + + public ResultSet getImageData(Connection con, String id, String version) { try { - Statement stm=con.createStatement(); + Statement stm = con.createStatement(); System.out.println("Test"); - return 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+"' ;"); + return 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 + "' ;"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } - - public int UpdateImageData(Connection con,String name,String newName,boolean license, boolean internet, long cpu, long ram,String id, String version){ + + public int UpdateImageData(Connection con, String name, String newName, + boolean license, boolean internet, long cpu, long ram, String id, + String version) { try { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); - Statement stm=con.createStatement(); - int newVersion=Integer.parseInt(version)+1; - - int internet_bol=0; - int license_bol=0; - if(internet==true) - { - internet_bol=1; + Statement stm = con.createStatement(); + int newVersion = Integer.parseInt(version) + 1; + + int internet_bol = 0; + int license_bol = 0; + if (internet == true) { + internet_bol = 1; } - if(license==true) - { - license_bol=1; + if (license == true) { + license_bol = 1; } System.out.println(internet_bol); System.out.println(license_bol); - stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `imageVersion` = '"+newVersion+"',`image_name` = '"+newName+"',`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+"' WHERE `GUID_imageID` = '"+id+"' AND `imageVersion` = '"+version+"';"); + stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `imageVersion` = '" + + newVersion + + "',`image_name` = '" + + newName + + "',`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 + + "' WHERE `GUID_imageID` = '" + + id + + "' AND `imageVersion` = '" + version + "';"); con.commit(); return 0; } catch (SQLException e) { -- cgit v1.2.3-55-g7522