From 75df905c97bc8bb4def5a3a6a87f301e364bc2ab Mon Sep 17 00:00:00 2001 From: tspitzer Date: Tue, 18 Feb 2014 13:08:48 +0100 Subject: SQL für VL funktioniert --- .../bin/server/ServerInterface.class | Bin 489 -> 839 bytes Dozentenmodulserver/bin/server/ServerMethod.class | Bin 4281 -> 6804 bytes Dozentenmodulserver/bin/sql/SQL.class | Bin 3051 -> 7892 bytes .../src/server/ServerInterface.java | 5 +- Dozentenmodulserver/src/server/ServerMethod.java | 89 ++++++++++++- Dozentenmodulserver/src/sql/SQL.java | 142 +++++++++++++++++++++ 6 files changed, 228 insertions(+), 8 deletions(-) (limited to 'Dozentenmodulserver') diff --git a/Dozentenmodulserver/bin/server/ServerInterface.class b/Dozentenmodulserver/bin/server/ServerInterface.class index 411a676d..146aa871 100644 Binary files a/Dozentenmodulserver/bin/server/ServerInterface.class and b/Dozentenmodulserver/bin/server/ServerInterface.class differ diff --git a/Dozentenmodulserver/bin/server/ServerMethod.class b/Dozentenmodulserver/bin/server/ServerMethod.class index 4b4096d1..01b5288c 100644 Binary files a/Dozentenmodulserver/bin/server/ServerMethod.class and b/Dozentenmodulserver/bin/server/ServerMethod.class differ diff --git a/Dozentenmodulserver/bin/sql/SQL.class b/Dozentenmodulserver/bin/sql/SQL.class index 245013e2..cb55c2de 100644 Binary files a/Dozentenmodulserver/bin/sql/SQL.class and b/Dozentenmodulserver/bin/sql/SQL.class differ diff --git a/Dozentenmodulserver/src/server/ServerInterface.java b/Dozentenmodulserver/src/server/ServerInterface.java index bce713c0..49d7adb9 100644 --- a/Dozentenmodulserver/src/server/ServerInterface.java +++ b/Dozentenmodulserver/src/server/ServerInterface.java @@ -2,6 +2,7 @@ package server; import java.rmi.*; import java.sql.ResultSet; import java.util.ArrayList; +import java.util.List; import Models.User; @@ -11,5 +12,7 @@ public interface ServerInterface extends Remote public int DeleteFtpUser(String user) throws RemoteException; public ArrayList getImages() throws RemoteException; public String getPathOfImage(String name) throws RemoteException; - + public boolean writeVLdata(String imagename, String firstname, String lastname,String university, String Mail, String Tel, String Fak, boolean license, boolean internet, int ram, int cpu) throws RemoteException; + public List getImageList() throws RemoteException; + public List getAllOS() throws RemoteException; } diff --git a/Dozentenmodulserver/src/server/ServerMethod.java b/Dozentenmodulserver/src/server/ServerMethod.java index 1039e238..1f66fb6b 100644 --- a/Dozentenmodulserver/src/server/ServerMethod.java +++ b/Dozentenmodulserver/src/server/ServerMethod.java @@ -7,8 +7,11 @@ import java.security.MessageDigest; import java.security.NoSuchAlgorithmException; import java.sql.Connection; import java.sql.ResultSet; +import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; +import java.util.Date; +import java.util.List; import java.util.UUID; import Models.*; import sql.SQL; @@ -17,7 +20,8 @@ import sql.SQL; public class ServerMethod extends UnicastRemoteObject implements ServerInterface { protected static String m_strName; - + SQL sql=new SQL(); + Connection con=sql.getConnection(); public ServerMethod() throws RemoteException { @@ -63,8 +67,8 @@ public class ServerMethod extends UnicastRemoteObject implements ServerInterface @Override public int DeleteFtpUser(String user) throws RemoteException { - SQL sql=new SQL(); - Connection con=sql.getConnection(); + + int ret = sql.DeleteUser(con, user); return ret; } @@ -86,8 +90,7 @@ public class ServerMethod extends UnicastRemoteObject implements ServerInterface public ArrayList getImages(){ - SQL sql=new SQL(); - Connection con=sql.getConnection(); + ResultSet rs= sql.getImage(con); ArrayList al=new ArrayList(); try { @@ -107,8 +110,7 @@ public class ServerMethod extends UnicastRemoteObject implements ServerInterface public String getPathOfImage(String name){ String path = null; - SQL sql=new SQL(); - Connection con=sql.getConnection(); + ResultSet rs= sql.getPathOfImage(con, name); try { @@ -125,4 +127,77 @@ public class ServerMethod extends UnicastRemoteObject implements ServerInterface return path; } + @Override + public boolean writeVLdata(String imagename, String firstname, + String lastname, String university, String Mail, String Tel, + String Fak, boolean license, boolean internet, int ram, int cpu) + throws RemoteException { + String login="tete"; + System.out.println("1"); + int pk_institution=sql.setInstitution(con, university); + System.out.println("2"); + int pk_person=sql.setPerson(con, login, lastname, firstname, Mail, new Date(), pk_institution); + System.out.println("3"); + sql.setImageData(con, pk_person, license, internet, cpu, ram, imagename); + System.out.println("4"); + // TODO Auto-generated method stub + return true; + } + + + + @Override + public List getImageList() throws RemoteException { + + ResultSet resWith=sql.getImageListWithLectures(con); + ResultSet resWithout=sql.getImageListWithoutLectures(con); + + try { + List listWith = ResSetToObject(resWith); + List listWithout=ResSetToObject(resWithout); + listWith.addAll(listWith.size(), listWithout); + return listWith; + } catch (SQLException e1) { + // TODO Auto-generated catch block + e1.printStackTrace(); + } + + return null; + } + + public List ResSetToObject(ResultSet res) throws SQLException + { + + ResultSetMetaData rsmd = res.getMetaData(); + List list=new ArrayList<>(); + while(res.next()){ + Object[] objects = new Object[rsmd.getColumnCount()]; + // tanks to umit ozkan for the bug fix! + for(int i=0;i getAllOS() throws RemoteException { + List list=new ArrayList<>(); + ResultSet rs=sql.getAllOS(con); + try { + while(rs.next()) + { + list.add(rs.getString("name")); + } + return list; + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return null; + } } \ No newline at end of file diff --git a/Dozentenmodulserver/src/sql/SQL.java b/Dozentenmodulserver/src/sql/SQL.java index 7d990116..af99b36b 100644 --- a/Dozentenmodulserver/src/sql/SQL.java +++ b/Dozentenmodulserver/src/sql/SQL.java @@ -5,6 +5,10 @@ import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.text.DateFormat; +import java.text.SimpleDateFormat; +import java.util.Date; +import java.util.Random; public class SQL { @@ -94,4 +98,142 @@ public class SQL { return null; } + + 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+"');"); + con.commit(); + ResultSet rs=stm.executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '"+university+"';"); + rs.next(); + return rs.getInt("institutionID"); + } + 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) + { + 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 + { + return ret.getInt("userID"); + } + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return -1; + } + + public int setImageData(Connection con, int pk_person, boolean license, boolean internet, int cpu, int ram, String imagename) + { + + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); + + int internet_bol=0; + int license_bol=0; + if(internet==true) + { + internet_bol=1; + } + else 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+"','/srv/openslx/nfs/temp/','"+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+"');"); + con.commit(); + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return ram; + + } + + public ResultSet getImageListWithoutLectures(Connection con){ + try { + Statement stm=con.createStatement(); + + return stm.executeQuery("SELECT distinct vl.image_name, vl.cond_hasLicenseRestriction, os.name, vl.image_update_time, '', Concat(u.Nachname,' ',u.Vorname) 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.lectureID!=vl.GUID_imageID and vl.image_owner=u.userID;"); + + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return null; + } + + public ResultSet getImageListWithLectures(Connection con){ + try { + Statement stm=con.createStatement(); + + return stm.executeQuery("SELECT vl.image_name, vl.cond_hasLicenseRestriction, os.name, vl.image_update_time, lect.name, Concat(u.Nachname,' ',u.Vorname) 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 getAllOS(Connection con) + { + + + try { + 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; + + } } -- cgit v1.2.3-55-g7522