From 1bf162c2febeb48b5343e7c2db24caf4f0c4194b Mon Sep 17 00:00:00 2001
From: tspitzer
Date: Wed, 21 Aug 2013 11:42:51 +0200
Subject: new Version

---
 Dozentenmodul_V1/src/db/MySQL.java | 79 +++++++++++++++++++++++++++++++++++---
 1 file changed, 74 insertions(+), 5 deletions(-)

(limited to 'Dozentenmodul_V1/src/db/MySQL.java')

diff --git a/Dozentenmodul_V1/src/db/MySQL.java b/Dozentenmodul_V1/src/db/MySQL.java
index ac02d8c4..45e11f36 100644
--- a/Dozentenmodul_V1/src/db/MySQL.java
+++ b/Dozentenmodul_V1/src/db/MySQL.java
@@ -19,7 +19,7 @@ public class MySQL {
 		}
 		
 		try {
-			Connection con=DriverManager.getConnection("jdbc:mysql://141.79.128.121/bwLehrpool?user=root&password=!N4ye,04u.");
+			Connection con=DriverManager.getConnection("jdbc:mysql://127.0.0.1/bwLehrpool?user=root&password=root");
 			return con;
 		} catch (SQLException e) {
 			// TODO Auto-generated catch block
@@ -35,7 +35,7 @@ public class MySQL {
 	{
 		try {
 			Statement stm=con.createStatement();
-			return stm.executeQuery("SELECT Name FROM bwLehrpool.SWHersteller order by Name asc;");
+			return stm.executeQuery("SELECT pk,Name FROM bwLehrpool.SWHersteller order by Name asc;");
 			
 			
 		} catch (SQLException e) {
@@ -105,12 +105,12 @@ public class MySQL {
 				
 	}
 	
-	public ResultSet getInactiveVLs(Connection con, String Mail)
+	public ResultSet getInactiveVLs(Connection con, String VMpk)
 	{
 		Statement stm;
 		try {
 			stm = con.createStatement();
-			return stm.executeQuery("Select Laborname from bwLehrpool.VM where IstAktiv=0 and pk=(SELECT VM_pk FROM bwLehrpool.Benutzer_has_VM where Benutzer_pk=(SELECT pk FROM bwLehrpool.Benutzer where EMail like '"+Mail+"'));");
+			return stm.executeQuery("Select Laborname from bwLehrpool.VM where IstAktiv=0 and pk="+VMpk+";");
 		} catch (SQLException e) {
 			// TODO Auto-generated catch block
 			e.printStackTrace();
@@ -123,12 +123,81 @@ public class MySQL {
 		Statement stm;
 		try {
 			stm = con.createStatement();
-			return stm.executeQuery("SELECT distinct OS FROM bwLehrpool.VM;");
+			return stm.executeQuery("SELECT distinct Produktname, Version, Architektur FROM bwlehrpool.betriebssystem order by Produktname asc;");
 		} catch (SQLException e) {
 			// TODO Auto-generated catch block
 			e.printStackTrace();
 		}
 		return null;
 	}
+	
+	public int AddOS(Connection con, String name, String Version, String Architektur,int Kosten, String Hersteller)
+	{
+		Statement stm;
+		
+		
+		try {
+			stm=con.createStatement();
+			
+			int pk_Hersteller=HerstellerExists(con, Hersteller);
+			
+			if(pk_Hersteller==-1)
+			{
+				addHersteller(con, Hersteller);
+			}
+			
+			
+			int ret=stm.executeUpdate("INSERT INTO `bwlehrpool`.`betriebssystem`(`Produktname`,`Version`,`Architektur`,`IstKostenlos`,`SWHersteller_pk`)VALUES('"+name+"','"+Version+"','"+Architektur+"',"+Kosten+","+pk_Hersteller+");");
+			return ret;
+		} catch (SQLException e) {
+			// TODO Auto-generated catch block
+			e.printStackTrace();
+		}
+		return -1;
+		
+	}
+	
+	public int HerstellerExists(Connection con, String Hersteller)
+	{
+		Statement stm;
+		int pk_Hersteller=-1;
+		
+		
+			try {
+				stm=con.createStatement();
+				ResultSet rs=getHersteller(con);
+				
+				while(rs.next())
+				{
+					if(rs.getString("Name").equals(Hersteller))
+					{
+						pk_Hersteller=Integer.parseInt(rs.getString("pk"));
+					}
+				}
+			} catch (SQLException e) {
+				// TODO Auto-generated catch block
+				e.printStackTrace();
+			}
+			
+			return pk_Hersteller;
+		
+	}
+	
+	public int addHersteller(Connection con, String Name){
+		
+		Statement stm;
+		int ret=-1;
+		try {
+			stm=con.createStatement();
+			ret=stm.executeUpdate("INSERT INTO `bwlehrpool`.`swhersteller`(`Name`)VALUES('"+Name+"');");
+			con.commit();
+		} catch (SQLException e) {
+			// TODO Auto-generated catch block
+			
+			e.printStackTrace();
+		}
+		return ret;
+		
+	}
 
 }
-- 
cgit v1.2.3-55-g7522