package sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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.UUID;
import models.Configuration;
import org.apache.log4j.Logger;
import server.BinaryListener;
import server.generated.Image;
import server.generated.Lecture;
import server.generated.Person;
public class SQL {
private static Logger log = Logger.getLogger(BinaryListener.class);
public Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (InstantiationException | IllegalAccessException
| ClassNotFoundException e) {
e.printStackTrace();
}
try {
Connection con = DriverManager.getConnection("jdbc:mysql://"
+ Configuration.config.getSql_connection() + "?user="
+ Configuration.config.getSql_user() + "&password="
+ Configuration.config.getSql_pass() + "");
con.setAutoCommit(false);
return con;
} catch (SQLException e) {
log.info("Failed to return connection to Client.");
e.printStackTrace();
}
return null;
}
public int writeFTPUser(String user, String pass) {
Statement stm;
try {
Connection con = getConnection();
stm = con.createStatement();
int ret = stm
.executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('"
+ user
+ "',SHA1('"
+ pass
+ "'),'10001','12345','"
+ Configuration.config.getAbsolute_path()
+ "temp/');");
con.commit();
con.close();
log.info("Created FTPUser " + user + " : " + pass
+ ".");
return ret;
} catch (SQLException e) {
log.info("Failed to writeFTPUser.");
e.printStackTrace();
}
return -1;
}
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 + "';");
*/
String sql = "DELETE FROM `bwLehrpool`.`FtpUsers` where User like ?;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, user);
int ret = prest.executeUpdate();
con.commit();
con.close();
log.info("FTPUser " + user + " deleted.");
return ret;
} catch (SQLException e) {
log.info("Failed to DeleteUser "+user+".");
e.printStackTrace();
}
return -1;
}
//no prepared statement to do here
public ResultSet getImage() {
try {
Connection con = getConnection();
Statement stm = con.createStatement();
ResultSet rs = stm
.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;");
con.close();
return rs;
} catch (SQLException e) {
log.info("Failed to getImage.");
e.printStackTrace();
}
return null;
}
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= "
+ "?"
+ " and imageVersion= "
+ "?"
+ ";";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, image_id);
prest.setString(2, version);
ResultSet rs = prest.executeQuery();
con.commit();
rs.first();
String path = rs.getString("image_path");
con.close();
return path;
} catch (SQLException e) {
log.info("Failed to getPathOfImage.");
e.printStackTrace();
}
return null;
}
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);
prest.setString(1, university);
ResultSet ret = prest.executeQuery();
con.commit();
if (ret.next() == false)
{
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);
prest.setString(1, id);
prest.setString(2, university);
prest.executeUpdate();
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);
prest.setString(1, university);
ResultSet rs = prest.executeQuery();
con.commit();
rs.next();
String institution = rs.getString("institutionID");
con.close();
return institution;
} else {
String institution = ret.getString("institutionID");
con.close();
return institution;
}
} catch (SQLException e) {
log.info("Failed to setInstitution.");
e.printStackTrace();
}
return "-1";
}
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
.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 "
+ "?"
+ " and Vorname like "
+ "?"
+ ";";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, lastname);
prest.setString(2, firstname);
ResultSet ret = prest.executeQuery();
con.commit();
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("
+ "?"
+ ","
+ "?"
+ ","
+ "?"
+ ","
+ "?"
+ ","
+ "?"
+ ",'"
+ formatter.format(new Date())
+ "',"
+ "?"
+ ");";
prest = con.prepareStatement(sql);
prest.setString(1, login);
prest.setString(2, login);
prest.setString(3, lastname);
prest.setString(4, firstname);
prest.setString(5, mail);
prest.setString(6, Institution);
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 "
+ "?"
+ ";";
prest = con.prepareStatement(sql);
prest.setString(1, lastname);
prest.setString(2, firstname);
rs = prest.executeQuery();
con.commit();
rs.next();
String userid = rs.getString("userID");
con.close();
return userid;
}
else
{
ret.first();
String userid = ret.getString("userID");
con.close();
return userid;
}
} catch (SQLException e) {
log.info("Failed to setPerson.");
e.printStackTrace();
}
return "-1";
}
public boolean setImageData(String pk_person, boolean license,
boolean internet, long cpu, long ram, String imagename,
String desc, String imagePath, long filesize, String shareMode,
String pk_os, String uid) {
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int internet_bol = 0;
int license_bol = 0;
if (internet == true) {
internet_bol = 1;
}
if (license == true) {
license_bol = 1;
}
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
+ "?" // image_name
+ "," + "?" // image_description
+ "," + "?" // image_path
+ ",'" + formatter.format(new Date()) // image_lastCall
+ "','" + formatter.format(new Date()) // image_create_time
+ "','" + formatter.format(new Date()) // image_update_time
+ "'," + "?" // image_owner
+ "," + "?" // image_change_by
+ ",'" + formatter.format(new Date()) // rec_create_time
+ "','" + formatter.format(new Date()) // rec_change_time
+ "'," + "?" // rec_owner
+ "," + "?" // rec_change_by
+ "," + "?" // content_operatingSystem
+ ",1" // status_isCompressed
+ ",1" // status_isSecure
+ ",1" // status_isOptimzed
+ ",1" // status_isValid
+ ",1" // status_isReady
+ ",0" // status_isDeleted
+ ",0," // status_isLastOfficialVersion
+ "?" // cond_hasLicenseRestriction
+ "," + "?" // cond_hasInternetRestriction
+ "," + "?" // cond_minRAM
+ "," + "?" // cond_minCPUs
+ ",0" // image_isTemplate
+ "," + "?" // image_filesize
+ "," + "?" // shareMode
+ ");";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, uid);
prest.setString(2, imagename);
prest.setString(3, desc);
prest.setString(4, imagePath);
prest.setString(5, pk_person);
prest.setString(6, pk_person);
prest.setString(7, pk_person);
prest.setString(8, pk_person);
prest.setString(9, pk_os);
prest.setInt(10, license_bol);
prest.setInt(11, internet_bol);
prest.setLong(12, ram);
prest.setLong(13, cpu);
prest.setLong(14, filesize);
prest.setString(15, shareMode);
prest.executeUpdate();
con.commit();
//con.commit();
con.close();
} catch (SQLException e) {
log.info("Failed to setImageData.");
e.printStackTrace();
}
return true;
}
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;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
ResultSet res = prest.executeQuery();
con.commit();
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("architecture") + " bit", res
.getString("lecture"), res
.getString("image_update_time"), res
.getString("user"), res
.getString("image_isTemplate"),
res.getString("image_description"),
res.getString("image_filesize")));
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
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 = "
+ "?"
+ " AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
ResultSet res = prest.executeQuery();
con.commit();
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("architecture") + " bit", res
.getString("lecture"), res
.getString("image_update_time"), res
.getString("user"), res
.getString("image_isTemplate"),
res.getString("image_description"),
res.getString("image_filesize")));
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//no prepared statement to do here
public List<Image> getImageList(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 ORDER BY vl.image_name;");
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("architecture") + " bit", res
.getString("lecture"), res
.getString("image_update_time"), res
.getString("user"), res
.getString("image_isTemplate"),
res.getString("image_description"),
res.getString("image_filesize")));
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
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;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
ResultSet res = prest.executeQuery();
con.commit();
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("architecture") + " bit",
res.getString("lecture"),
res.getString("image_update_time"),
res.getString("user"),
res.getString("image_isTemplate"),
res.getString("image_description"),
res.getString("image_filesize")));
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
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;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
ResultSet res = prest.executeQuery();
con.commit();
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("architecture") + " bit",
res.getString("lecture"),
res.getString("image_update_time"),
res.getString("user"),
res.getString("image_isTemplate"),
res.getString("image_description"),
res.getString("image_filesize")
)
);
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}//end getImageListPermissionAdmin
//no prepared statement to do here
public List<Image> getImageListAllTemplates(){
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.image_description, 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 FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE vl.image_isTemplate=1 AND vl.content_operatingSystem=os.operatingSystemID AND vl.image_owner=u.userID ORDER BY vl.image_name;");
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("architecture") + " bit",
res.getString("lecture"),
res.getString("image_update_time"),
res.getString("user"),
res.getString("image_isTemplate"),
res.getString("image_description"),
res.getString("image_filesize")
)
);
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}//end getImageListAllTemplates
public List<Lecture> getLectureListPermissionRead(String userID) {
List<Lecture> list = new ArrayList<Lecture>();
try {
Connection con = getConnection();
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_read=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.userID = ? ORDER BY l.name;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
ResultSet res = prest.executeQuery();
con.commit();
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) {
log.info("Failed to getLectureListPermissionRead.");
e.printStackTrace();
}
return list;
}// end getLectureListPermissionRead
public List<Lecture> getLectureListPermissionWrite(String userID) {
List<Lecture> list = new ArrayList<Lecture>();
try {
Connection con = getConnection();
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 pml.userID="
+ "?"
+ " AND u.userID = l.admin_owner AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID ORDER BY l.name;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
ResultSet res = prest.executeQuery();
con.commit();
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) {
log.info("Failed to getLectureListPermissionWrite.");
e.printStackTrace();
}
return list;
}// end getLectureListPermissionWrite
public List<Lecture> getLectureListPermissionAdmin(String userID)
{
List<Lecture> list = new ArrayList<Lecture>();
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;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
ResultSet res = prest.executeQuery();
con.commit();
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) {
log.info("Failed to getLectureListPermissionAdmin.");
e.printStackTrace();
}
return list;
}// end getLectureListPermissionAdmin
//No prepared statement to do here
public List<Lecture> getLectureList() {
List<Lecture> list = new ArrayList<Lecture>();
try
{
Connection con = getConnection();
Statement stm = con.createStatement();
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 ORDER BY l.name;");
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) {
log.info("Failed to getLectureList.");
e.printStackTrace();
}
return null;
}
//No prepared statement to do here
public List<String> getAllOS() {
List<String> list = new ArrayList<>();
try {
Connection con = getConnection();
Statement stm = con.createStatement();
ResultSet rs = stm
.executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem;");
while (rs.next()) {
list.add(rs.getString("name") + " "
+ rs.getString("architecture") + " bit");
}
return list;
} catch (SQLException e) {
log.info("Failed to getAllOS.");
e.printStackTrace();
}
return null;
}
public Map<String, String> getPersonData(String Vorname, String Nachname) {
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 "
+ "?"
+ " and u.institution=i.institutionID;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, Vorname);
prest.setString(2, Nachname);
ResultSet rs = prest.executeQuery();
con.commit();
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) {
log.info("Failed to getPersonData.");
e.printStackTrace();
}
return null;
}//end getPersonData
public Map<String, String> getPersonData(String userID) {
Map<String, String> map = new HashMap<String, String>();
try {
Connection con = getConnection();
String sql = "SELECT userID, loginName, Nachname, Vorname, mail, m_institution.name as institution FROM bwLehrpool.m_user, m_institution WHERE userID=? AND m_user.institution=m_institution.institutionID;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
ResultSet rs = prest.executeQuery();
con.commit();
while (rs.next())
{
map.put("userID", rs.getString("userID"));
map.put("loginName", rs.getString("loginName"));
map.put("Nachname", rs.getString("Nachname"));
map.put("Vorname", rs.getString("Vorname"));
map.put("mail", rs.getString("mail"));
map.put("institution", rs.getString("institution"));
}
con.close();
return map;
} catch (SQLException e) {
log.info("Failed to getPersonData with userID='"+userID+"'.");
e.printStackTrace();
}
return null;
}//end getPersonData
public Map<String, String> getItemOwner(String itemID) {
Map<String, String> map = new HashMap<String, String>();
Connection con = getConnection();
PreparedStatement prest;
String userID;
try {
//find out if the itemID belongs to an image
String sql = "SELECT image_owner FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID=?;";
prest = con.prepareStatement(sql);
prest.setString(1, itemID);
ResultSet rs = prest.executeQuery();
while (rs.next()){
map.put("userID",rs.getString("image_owner"));
}
//if map is empty, then itemID does not belong to an image -> must be a lecture
if(map.isEmpty()==true){
//check if itemID belongs to a lecture
sql = "SELECT admin_owner FROM bwLehrpool.m_VLData_lecture WHERE lectureID=?;";
prest = con.prepareStatement(sql);
prest.setString(1, itemID);
rs = prest.executeQuery();
while(rs.next()){
map.put("userID", rs.getString("admin_owner"));
}
}
//if map is still empty, then the itemID could not be found
if(map.isEmpty()==true){
log.error("Failed to getItemOwner for item '"+itemID+"'.");
} else {
//now that we have the userID for this item, get the user information and put it in the map
userID = map.get("userID"); //extract the userID
map = getPersonData(userID); //get all the rest of his information
//return this map with all the infos
return map;
}
} catch (SQLException e) {
e.printStackTrace();
}
//something went horribly wrong...
return null;
}//end getItemOwner
public int setLectureData(String pk_person, String pk_image,
int imageversion, String name, String desc, String shortdesc,
String start, String end, boolean isactive, String lectureID)
{
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int active_bol = 0;
if (isactive == true)
{
active_bol = 1;
}
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("
+ "?"
+ ","
+ "?"
+ ","
+ "?"
+ ","
+ "?"
+ ","
+ "?"
+ ",'"
+ formatter.format(new Date())
+ "',"
+ "?"
+ ","
+ "?"
+ ","
+ "?"
+ ","
+ "?"
+ ",'"
+ formatter.format(new Date())
+ "','"
+ formatter.format(new Date())
+ "',"
+ "?"
+ ","
+ "?" + ");";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, lectureID);
prest.setString(2, name);
prest.setInt(3, active_bol);
prest.setString(4, start);
prest.setString(5, end);
prest.setString(6, shortdesc);
prest.setString(7, desc);
prest.setString(8, pk_image);
prest.setInt(9, imageversion);
prest.setString(10, pk_person);
prest.setString(11, pk_person);
prest.executeUpdate();
con.commit();
//con.commit();
con.close();
} catch (SQLException e) {
log.info("Failed to setLectureData.");
e.printStackTrace();
}
return 0;
}
public Map<String, String> getImageIDandVersion(String id) {
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);
prest.setString(1, id);
ResultSet image = prest.executeQuery();
con.commit();
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) {
log.info("Failed to getImageIDandVersion.");
e.printStackTrace();
}
return null;
}
public Map<String, String> getImageData(String id, String version) {
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);
prest.setString(1, id);
prest.setString(2, version);
ResultSet data = prest.executeQuery();
con.commit();
while (data.next())
{
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"));
map.put("shareMode", data.getString("image_syncMode"));
map.put("filesize", data.getString("image_filesize"));
map.put("path", data.getString("image_path"));
map.put("template", data.getString("image_isTemplate"));
map.put("os", data.getString("GuestOS"));
map.put("owner", data.getString("Owner"));
map.put("lastupdate", data.getString("image_update_time"));
map.put("desc", data.getString("image_description"));
map.put("id", data.getString("GUID_imageID"));
map.put("version", data.getString("imageVersion"));
map.put("mail", data.getString("mail"));
}
con.close();
return map;
} catch (SQLException e) {
log.info("Failed to getImageData.");
e.printStackTrace();
}
return null;
}
public int UpdateImageData(String name, String newName, String desc,
String image_path, boolean license, boolean internet, long cpu,
long ram, String id, String version, boolean isTemplate,
long filesize, String shareMode, String ospk)
{
try {
Connection con = getConnection();
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;
}
if (license == true)
{
license_bol = 1;
}
/*
log.info("'image_name` = '" + newName
+ "\n length of name=" + newName.length());
log.info("now in UpdateImageData()");
log.info("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 + ";");
*/
/*
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 = "
+ "?"
+ ",image_description = "
+ "?"
+ " ,image_path = "
+ "?"
+ " ,image_update_time = '"
+ formatter.format(new Date())
+ "' ,rec_change_time = '"
+ formatter.format(new Date())
+ "' ,cond_hasLicenseRestriction = "
+ "?"
+ " ,cond_hasInternetRestriction = "
+ "?"
+ " ,cond_minRAM = "
+ "?"
+ " ,cond_minCPUs = "
+ "?"
+ " ,image_isTemplate = "
// + isTemplate
+ "0 "
+ ",content_operatingSystem = "
+ "?"
+ ",image_filesize = "
+ "?"
+ ",image_syncMode = "
+ "?"
+ " WHERE GUID_imageID = "
+ "?"
+ " AND imageVersion = "
+ "?" + ";";
PreparedStatement prest = con.prepareStatement(sql);
prest.setInt(1, newVersion);
prest.setString(2, newName);
prest.setString(3, desc);
prest.setString(4, image_path);
prest.setInt(5, license_bol);
prest.setInt(6, internet_bol);
prest.setLong(7, ram);
prest.setLong(8, cpu);
prest.setString(9, ospk);
prest.setLong(10, filesize);
prest.setString(11, shareMode);
prest.setString(12, id);
prest.setString(13, version);
prest.executeUpdate();
con.commit(); //needed, though executeUpdate()!
//con.commit();
con.close();
return 0;
} catch (SQLException e) {
log.info("Failed to UpdateImageData.");
e.printStackTrace();
}
return -1;
}
public boolean deleteImage(String id, String version)
{
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);
prest.setString(2, version);
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);
prest.executeUpdate();
con.commit();
con.close();
return true;
} catch (SQLException e) {
log.info("Failed to deleteImage and permissions.");
e.printStackTrace();
}
return false;
}
public int updateLectureData(String pk_image, String imageversion,
String name, String newName, String desc, String shortdesc,
String start, String end, boolean isactive, String id)
{
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
int active_bol = 0;
if (isactive == true)
{
active_bol = 1;
}
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 = "
+ "?"
+ ",startTime = "
+ "?"
+ ",endTime = "
+ "?"
+ ",description = "
+ "?"
+ ",imageID = "
+ "?"
+ ",imageVersion = "
+ "?"
+ ",admin_changeTime = '"
+ formatter.format(new Date())
+ "'WHERE `lectureID` = ?;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, newName);
prest.setInt(2, active_bol);
prest.setString(3, start);
prest.setString(4, end);
prest.setString(5, desc);
prest.setString(6, pk_image);
prest.setString(7, imageversion);
prest.setString(8, id);
prest.executeUpdate();
con.commit();
con.close();
//log.info("Succeeded to updateLectureData.");
} catch (SQLException e) {
log.info("Failed to updateLectureData.");
e.printStackTrace();
}
return 0;
}
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 + "';");
String sql = "SELECT lectureID FROM "+ "bwLehrpool.m_VLData_lecture WHERE imageID = ? AND imageVersion = ?;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, id);
prest.setString(2, version);
ResultSet rs = prest.executeQuery();
con.commit();
boolean ret = rs.first();
con.close();
return ret;
} catch (SQLException e) {
log.info("Failed to execute method connectedToLecture.");
e.printStackTrace();
}
return false;
}
public boolean deleteLecture(String id) {
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 + "';");
con.commit();
con.close();
return true;
} catch (SQLException e) {
log.info("Failed to deleteLecture and permissions.");
e.printStackTrace();
}
return false;
}
public String getFile(String imageid, String imageversion) {
try {
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 = "
+ "?"
+ ";";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, imageid);
prest.setString(2, imageversion);
ResultSet rs = prest.executeQuery();
con.commit();
rs.next();
String path = rs.getString("image_path");
con.close();
return path;
} catch (SQLException e) {
log.info("Failed to getFile.");
e.printStackTrace();
}
return null;
}
public Map<String, String> getDeleteXMLData(String id) {
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);
prest.setString(1, id);
ResultSet rs = prest.executeQuery();
con.commit();
rs.next();
map.put("date", rs.getString("admin_createTime").replace(" ", "")
.replace("-", "").replace(":", ""));
map.put("name", rs.getString("name"));
con.close();
return map;
} catch (SQLException e) {
log.info("Failed to getDeleteXMLData.");
e.printStackTrace();
}
return null;
}
public int UpdateImagePath(String name) {
try {
Connection con = getConnection();
Statement stm = con.createStatement();
String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET image_path = "
+ "?" //prod/+name
+ " WHERE image_path = "
+ "?" //temp/+name
+ ";";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, "prod/"+name);
prest.setString(2, "temp/"+name);
prest.executeUpdate();
con.commit();
con.close();
//log.info("Succesfully updated image path");
return 0;
} catch (SQLException e) {
log.info("Failed to UpdateImagePath.");
e.printStackTrace();
}
return -1;
}
public List<String> getAllUniversities() {
List<String> list = new ArrayList<>();
try {
Connection con = getConnection();
Statement stm = con.createStatement();
ResultSet rs = stm
.executeQuery("SELECT name FROM bwLehrpool.m_institution;");
while (rs.next()) {
list.add(rs.getString("name"));
}
return list;
} catch (SQLException e) {
log.info("Failed to getAllUniversities.");
e.printStackTrace();
}
return null;
}
public String getOSpk(String os, String architecture) {
try {
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);
prest.setString(1, os);
prest.setString(2, architecture);
ResultSet rs = prest.executeQuery();
con.commit();
rs.first();
String pkos = rs.getString("operatingSystemID");
//log.info(" - Key is " + pkos);
con.close();
return pkos;
} catch (SQLException e) {
log.info("Failed to getOSpk.");
e.printStackTrace();
}
return null;
}
public String getOS(String pk) {
try {
Connection con = getConnection();
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);
prest.setString(1, pk);
ResultSet rs = prest.executeQuery();
con.commit();
rs.first();
String os = rs.getString("name") + " "
+ rs.getString("architecture");
con.close();
return os;
} catch (SQLException e) {
log.info("Failed to getOS.");
e.printStackTrace();
}
return null;
}
public Map<String, String> getLectureData(String id) {
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);
prest.setString(1, id);
ResultSet data = prest.executeQuery();
con.commit();
while (data.next()) {
map.put("name", data.getString("name"));
map.put("isActive", data.getString("isActive"));
map.put("startTime", data.getString("startTime"));
map.put("endTime", data.getString("endTime"));
map.put("lastUsed", data.getString("lastUsed"));
map.put("desc", data.getString("description"));
map.put("imagepath", data.getString("image_path"));
map.put("imageversion", data.getString("imageVersion"));
map.put("owner", data.getString("Owner"));
}
con.close();
return map;
} catch (SQLException e) {
log.info("Failed to getLectureData.");
e.printStackTrace();
}
return null;
}
public boolean checkUser(String username) {
Statement stm;
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);
prest.setString(1, username);
ResultSet rs = prest.executeQuery();
con.commit();
if (rs.next())
{
ret = true;
log.info("Checking user: " + rs.getString("loginName")
+ " exists.");
} else {
ret = false;
log.info("Checking user: " + rs.getString("loginName")
+ " does not exist.");
}
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
return ret;
}
public int getRoleID(String role) {
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);
prest.setString(1, role);
ResultSet rs = prest.executeQuery();
con.commit();
rs.first();
return rs.getInt("roleID");
} catch (SQLException e) {
log.info("Failed to getRoleID.");
e.printStackTrace();
}
return -1;
}
public String getLectureID(String name) {
String id = null;
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);
ResultSet lecture = prest.executeQuery();
con.commit();
while (lecture.next())
{
id = lecture.getString("lectureID");
}
con.close();
return id;
} catch (SQLException e) {
log.info("Failed to getLectureID.");
e.printStackTrace();
}
return id;
}
// get all users from satellite -- temp, tb replaced by allUsers bwLehrpool
// does -NOT- return the user himself
//TODO prepared statement
public List<Person> getAllOtherSatelliteUsers(List<String> userID) {
Connection con = getConnection();
Statement stm = null;
ResultSet res = null;
List<Person> list = new ArrayList<Person>();
try {
stm = con.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
String query = "SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID NOT IN ('"
+ userID.get(0) + "";
for (int i = 1; i < userID.size(); i++) {
query = query + "','" + userID.get(i);
}
query = query + "') ORDER BY Nachname ASC;";
try {
res = stm.executeQuery(query);
} catch (SQLException e1) {
e1.printStackTrace();
}
try {
while (res.next()) {
// fill the list with users - permissions are all false because
// the image is new
list.add(new Person(res.getString("userID"), res
.getString("Nachname"), res.getString("Vorname"), res
.getString("mail"), false, false, false, false, false,
false, false));
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}// end getAllOtherSatelliteUsers()
// get the permissions of an image for all other registered users -EXCEPT-
// the logged on user
public List<Person> getPermissionForUserAndImage(String userID,
String imageID) {
Connection con = getConnection();
Statement stm = null;
ResultSet res = null;
List<Person> list = new ArrayList<Person>();
try {
stm = con.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
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!= "
+ "?"
+ " AND pmi.GUID_imageID= "
+ "?"
+ " AND pmi.userID=u.userID ORDER BY u.Nachname ASC;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
prest.setString(2, imageID);
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();
}
try {
boolean image_read, image_write, link_allowed, image_admin, lecture_read, lecture_write, lecture_admin;
while (res.next()) {
// reset old values and set the correct new values
image_read = false;
image_write = false;
link_allowed = false;
image_admin = false;
lecture_read = false;
lecture_write = false;
lecture_admin = false;
if (Integer.parseInt(res.getString("image_read")) != 0) {
image_read = true;
}
if (Integer.parseInt(res.getString("image_write")) != 0) {
image_write = true;
}
if (Integer.parseInt(res.getString("link_allowed")) != 0) {
link_allowed = true;
}
if (Integer.parseInt(res.getString("image_admin")) != 0) {
image_admin = true;
}
// fill the list with users - permissions are all false because
// the image is new
list.add(new Person(res.getString("userID"), res
.getString("Nachname"), res.getString("Vorname"), res
.getString("mail"), image_read, image_write,
link_allowed, image_admin, false, // lecture_read
false, // lecture_write
false) // lecture_admin
);
// System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin")));
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}// end getPermissionFOrUserAndImage()
// get the permissions of a lecture for all other registered users -EXCEPT- the logged on user
public List<Person> getPermissionForUserAndLecture(String userID,
String lectureID) {
Connection con = getConnection();
//Statement stm = null;
ResultSet res = null;
List<Person> list = new ArrayList<Person>();
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;";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, userID);
prest.setString(2, lectureID);
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();
}
try {
boolean image_read, image_write, link_allowed, image_admin, lecture_read, lecture_write, lecture_admin;
while (res.next())
{
// reset old values and set the correct new values
image_read = false;
image_write = false;
link_allowed = false;
image_admin = false;
lecture_read = false;
lecture_write = false;
lecture_admin = false;
if (Integer.parseInt(res.getString("rec_read")) != 0) {
lecture_read = true;
}
if (Integer.parseInt(res.getString("rec_write")) != 0) {
lecture_write = true;
}
if (Integer.parseInt(res.getString("rec_admin")) != 0) {
lecture_admin = true;
}
// fill the list with users - permissions are all false because
// the image is new
list.add(new Person(
res.getString("userID"),
res.getString("Nachname"),
res.getString("Vorname"),
res.getString("mail"),
false, //image read
false, //image write
false, //image link
false, //image admin
lecture_read, // lecture_read
lecture_write, // lecture_write
lecture_admin) // lecture_admin
);
// System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin")));
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}// end getPermissionForUserAndLecture
public int setImageRights(String pk_person, String pk_image, int role,
int read, int write, int linkallowed, int admin) {
try {
Connection con = getConnection();
//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);
prest.setString(1, uid);
prest.setString(2, pk_image);
prest.setInt(3, role);
prest.setString(4, pk_person);
prest.setInt(5, read);
prest.setInt(6, write);
prest.setInt(7, admin);
prest.setInt(8, linkallowed);
prest.executeUpdate();
con.commit();
con.close();
} catch (SQLException e) {
log.info("Failed to setImageRights.");
e.printStackTrace();
}
return 0;
}// end setImageRights
public boolean writeAdditionalImageRights(String imageID, String userID,boolean isRead, boolean isWrite, boolean isLinkAllowed,boolean isAdmin)
{
boolean success = true;
String uid = UUID.randomUUID().toString();
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, "
+ "image_read, " + "image_write, " + "Image_admin, "
+ "link_allowed)" + "VALUES" + "(?,?,?,?,?,?,?,?)";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, uid);
prest.setString(2, imageID);
prest.setInt(3, 1);
prest.setString(4, userID);
prest.setBoolean(5, isRead);
prest.setBoolean(6, isWrite);
prest.setBoolean(7, isAdmin);
prest.setBoolean(8, isLinkAllowed);
prest.executeUpdate();
con.commit();
con.close();
//log.info("Written additional image rights.");
} catch (SQLException e) {
e.printStackTrace();
log.info("Failed to write additional image rights.");
}
return success;
}// end writeAdditionalImageRights
public int setLectureRights(String pk_person, String pk_lecture, int role,
int read, int write, int admin) {
try {
Connection con = getConnection();
//Statement stm = con.createStatement();
String uid = UUID.randomUUID().toString();
String sql = "INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES" + "(?,?,?,?,?,?)";
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, uid);
prest.setString(2, pk_lecture);
prest.setString(3, pk_person);
prest.setInt(4, read);
prest.setInt(5, write);
prest.setInt(6, admin);
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();
} catch (SQLException e) {
log.info("Failed to setLectureRights.");
e.printStackTrace();
}
return 0;
} // end setLectureRights
public boolean writeAdditionalLectureRights(String lectureID,
String userID, boolean isRead, boolean isWrite, boolean isAdmin) {
boolean success = true;
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"
+ ("(?,?,?,?,?,?)");
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, uid);
prest.setString(2, lectureID);
prest.setString(3, userID);
prest.setBoolean(4, isRead);
prest.setBoolean(5, isWrite);
prest.setBoolean(6, isAdmin);
//log.info("userdata: "+uid+" "+lectureID + " " + userID + " " + isRead + isWrite + isAdmin);
prest.executeUpdate();
con.commit();
con.close();
//log.info("Written additional lecture rights for '"+ userID + "'.");
} catch (SQLException e) {
e.printStackTrace();
log.info("Failed to write additional lecture rights.");
}
return success;
}// end writeAdditionalLectureRights
public void deleteAllAdditionalImagePermissions(String imageID,
String userID) {
Connection con = getConnection();
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);
prest.setString(1, imageID);
prest.setString(2, userID);
prest.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
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) {
e.printStackTrace();
}
}// end deleteAllAdditionalImagePermissions
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);
prest.setString(1, lectureID);
prest.setString(2, userID);
prest.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
try {
//stm = con.createStatement();
//int ret = stm.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"+ lectureID + "' AND userID != '" + userID + "';");
con.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}// end deleteAllAdditionalImagePermissions
public List<String> getAdditionalImageContacts(String imageID)
{
List<String> list = new ArrayList<>();
Connection con = getConnection();
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);
prest.setString(1, imageID);
ResultSet rs = prest.executeQuery();
con.commit();
while (rs.next())
{
list.add(rs.getString("Nachname") + "," + rs.getString("Vorname")+" ");
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public String getOsNameForGuestOs(String guestOS) {
Connection con = getConnection();
ResultSet rs = null;
String sql = "Select name, architecture from bwLehrpool.m_operatingSystem where guestOS = ?";
try
{
PreparedStatement prest = con.prepareStatement(sql);
prest.setString(1, guestOS);
rs = prest.executeQuery();
con.commit();
rs.next();
return rs.getString("name") + " " + rs.getString("architecture") + " bit";
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public String createRandomUUID()
{
return UUID.randomUUID().toString();
}
}// end class