package Java;
import Java.ORM.Account;
import Java.ORM.DatabaseORM;
import static Java.ORM.DatabaseORM.entityManagerFactoryString;
import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.TimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.Persistence;
import sun.misc.BASE64Encoder;
/**
* @author Mitch
*/
public class DatabaseKoppeling {
// <editor-fold defaultstate="collapsed" desc="Properties">
private static Connection con;
private static String serverAdress = "127.0.0.1";
private static String portNumber = "3306";
private static String sid = "PTS4";
private static String url = "jdbc:mysql://" + serverAdress + ":" + portNumber + "/" + sid;
private static String username = "root"; // root";
private static String password = "test";
// </editor-fold>
private static void openConnection(boolean autoCommit) throws SQLException {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (ClassNotFoundException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} catch (InstantiationException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
con = DriverManager.getConnection(url, username, password);
con.setAutoCommit(autoCommit);
}
/**
* @deprecated This method is replaced by DatabaseORM.insertAccount();
*/
@Deprecated
public static boolean register(String username, String password, String name, String address, String zip, String city, String email, String phonenumber, boolean photographer, boolean bGrapher) {
if(!checkUsername(username)) {
int isPhoto;
if(photographer) {
isPhoto = 1;
} else {
// if(bGrapher) {
// isPhoto = 2;
// } else {
// isPhoto = 0;
// }
isPhoto = 0;
}
boolean newconnection = false;
try {
if (con == null || con.isClosed()) {
openConnection(true);
newconnection = true;
}
PreparedStatement ps = con.prepareCall("INSERT INTO ACCOUNT (username, password, name, address, zip, city, email, phonenumber, photographer, registerDate) values(?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate())");
ps.setString(1, username);
ps.setString(2, encrypt(password));
ps.setString(3, name);
ps.setString(4, address);
ps.setString(5, zip.replace(" ", ""));
ps.setString(6, city);
ps.setString(7, email);
ps.setString(8, phonenumber);
ps.setInt(9, isPhoto);
ps.executeUpdate();
Email.sendRegistrationMail(email, username, password);
if(photographer || bGrapher) {
PreparedStatement psCategory1 = con.prepareCall("INSERT INTO CATEGORY (name, photographerName, price) values('Groepsfoto', ?, 0.5)");
psCategory1.setString(1,username);
psCategory1.executeUpdate();
PreparedStatement psCategory2 = con.prepareCall("INSERT INTO CATEGORY (name, photographerName, price) values('Portret', ?, 0.5)");
psCategory2.setString(1,username);
psCategory2.executeUpdate();
}
return true;
} catch(SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
return false;
} finally {
if(newconnection) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
} else {
return false;
}
}
/**
*
* @deprecated This method is replaced by DatabaseORM.findAccount();
*/
@Deprecated
public static boolean checkUsername(String username) {
boolean succeeded = true;
boolean newconnection = false;
try {
if (con == null || con.isClosed()) {
openConnection(true);
newconnection = true;
}
PreparedStatement ps = con.prepareStatement("SELECT username FROM ACCOUNT WHERE username = ? LIMIT 1;");
ps.setString(1, username);
succeeded = ps.executeQuery().next();
} catch(SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if(newconnection) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return succeeded;
}
/**
* Login a user
* @param username
* @param password
* @return fail = null, success = UserType
*/
public static UserType login(String username, String password) {
if(username == null || password == null) return null;
boolean result = false;
UserType usertype = null;
try {
openConnection(true);
PreparedStatement ps = con.prepareStatement("SELECT username, photographer FROM ACCOUNT WHERE username = ? AND password = ?");
ps.setString(1, username);
ps.setString(2, encrypt(password));
ResultSet rs = ps.executeQuery();
if(rs.first()) {
int isPhotographer = rs.getInt("photographer");
usertype = UserType.fromInteger(isPhotographer);
result = true;
}
} catch(SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
if(result)
return usertype;
return null;
}
/**
* @deprecated This method is replaced by DatabaseORM.accountIsPhotographer();
*/
@Deprecated
public static boolean isPhotographer(String username) {
if (username == null)
return false;
try {
openConnection(true);
PreparedStatement ps = con.prepareStatement("SELECT username FROM ACCOUNT WHERE username = ? AND photographer = 1");
ps.setString(1, username);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
return true;
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return false;
}
/**
* @deprecated This method is replaced by DatabaseORM.accountIsPhotographer();
*/
@Deprecated
public static boolean isAPhotographer(String username) {
if (username == null)
return false;
boolean closeConnection = false;
try {
if (con == null || con.isClosed()) {
openConnection(true);
closeConnection = true;
}
PreparedStatement ps = con.prepareStatement("SELECT username FROM ACCOUNT WHERE username = ? AND photographer = 1;");
ps.setString(1, username);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
return true;
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null && closeConnection)
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return false;
}
public static Integer getNextPhotoName() {
String query = "SELECT MAX(photoNo) FROM PHOTO;";
try {
openConnection(true);
ResultSet rs = con.createStatement().executeQuery(query);
if (rs.next()) {
Integer photoName = rs.getInt(1);
if (rs.wasNull()) {
return 1;
} else {
photoName = photoName + 1;
return photoName;
}
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return null;
}
public static boolean processPhoto(String curUser, String category, String relativePathPreview, String relativePathPhoto, Integer photoName, String extension, boolean publicPhoto) {
try {
openConnection(false);
PreparedStatement ps = con.prepareStatement("INSERT INTO PHOTO (photoNo, extension, price, category, relativePathPreview, relativePathPhoto, uploadDate, photographer, public) VALUES(?, ?, 0.00, ?, ?, ?, sysdate(), ?, ?);");
ps.setInt(1, photoName);
ps.setString(2, extension);
ps.setString(3, category);
ps.setString(4, relativePathPreview);
ps.setString(5, relativePathPhoto);
ps.setString(6, curUser);
ps.setString(7, publicPhoto ? "1" : "0");
ps.executeUpdate();
con.commit();
return true;
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex1);
}
}
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return false;
}
public static boolean addPersonToPicture(Person p, String photoName) {
return addPersonToPicture(p, toInteger(photoName));
}
public static boolean addPersonToPicture(Person p, Integer photoName) {
if(photoName == null)
return false;
try {
DatabaseKoppeling.openConnection(true);
String personUsername;
if (p.getUsername() != null && !p.getUsername().equals("")) {
personUsername = p.getUsername();
} else if(!p.isValid()) {
return false;
} else {
personUsername = p.getNewUsername();
EntityManager em = Persistence.createEntityManagerFactory(entityManagerFactoryString).createEntityManager();
//if (!DatabaseKoppeling.register(personUsername, p.getZipcode().replace(" ", ""), p.getName(), p.getAddress(), p.getZipcode(), p.getCity(), p.getEmail(), p.getPhone(), false, false))
if (!DatabaseORM.insertAccount(em, new Account(personUsername, p.getZipcode().replace(" ", ""), p.getName(), p.getAddress(), p.getZipcode(), p.getCity(), p.getEmail(), p.getPhone(), 0, new Timestamp(new Date().getTime()))))
return false;
}
PreparedStatement ps = con.prepareStatement("INSERT INTO PHOTO_OWNER VALUES(?, ?);");
ps.setInt(1, photoName);
ps.setString(2, personUsername);
ps.executeUpdate();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
return false;
}
return true;
}
public static boolean removePersonFromPicture(String photoname, String username) {
return removePersonFromPicture(toInteger(photoname), username);
}
public static boolean removePersonFromPicture(Integer photoname, String username) {
if(username == null || photoname == null)
return false;
try {
openConnection(true);
PreparedStatement ps = con.prepareStatement("DELETE FROM PHOTO_OWNER WHERE photoNo = ? AND customer = ?;");
ps.setInt(1, photoname);
ps.setString(2, username);
ps.executeUpdate();
return true;
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return false;
}
public static Iterator<Person> getPeopleOnPhoto(Integer photoname) {
List<Person> peopleOnPhoto = new ArrayList<Person>();
if(photoname == null)
return peopleOnPhoto.iterator();
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT username, name, address, zip, city, email, phonenumber "
+ "FROM ACCOUNT WHERE username IN(SELECT customer FROM PHOTO_OWNER WHERE photoNo = ?);");
ps.setInt(1, photoname);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String customer = rs.getString(1);
String name = rs.getString(2);
String address = rs.getString(3);
String zip = rs.getString(4);
String city = rs.getString(5);
String email = rs.getString(6);
String phonenumber = rs.getString(7);
peopleOnPhoto.add(new Person(customer, name, email, address, zip, city, phonenumber));
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return peopleOnPhoto.iterator();
}
public static Iterator<String> getPhotoTypes() {
ArrayList<String> photoTypes = new ArrayList<String>();
try {
openConnection(true);
PreparedStatement ps = con.prepareStatement("SELECT name FROM PHOTO_TYPE;");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
photoTypes.add(rs.getString("name"));
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null)
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
Collections.sort(photoTypes);
return photoTypes.iterator();
}
public static Iterator<String> getProducts() {
ArrayList<String> products = new ArrayList<String>();
try {
openConnection(true);
PreparedStatement ps = con.prepareStatement("SELECT name FROM PRODUCT;");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
products.add(rs.getString("name"));
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null)
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
Collections.sort(products);
return products.iterator();
}
public static String getPhotoCustomer(Integer photoname, String username) {
if(photoname == null || username == null)
return null;
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT photoNo, relativePathPreview, extension FROM PHOTO WHERE (SELECT customer FROM PHOTO_OWNER WHERE photoNo = ?) = ?;");
ps.setInt(1, photoname);
ps.setString(2, username);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
String webPath = rs.getString("relativePathPreview");
webPath = webPath.substring(webPath.indexOf("/previews/") + 1);
String photo = String.format("%s%s.%s", webPath, rs.getString("photoNo"), rs.getString("extension"));
return photo;
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return null;
}
public static String getPhoto(String photoname, String username) {
if(photoname == null || username == null)
return null;
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT * FROM PHOTO WHERE photoNo = ? AND photographer = ?;");
ps.setString(1, photoname);
ps.setString(2, username);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
String webPath = rs.getString("relativePathPreview");
webPath = webPath.substring(webPath.indexOf("/previews/") + 1);
String photo = String.format("%s%s.%s", webPath, rs.getString("photoNo"), rs.getString("extension"));
return photo;
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return null;
}
public static synchronized PhotoReturned getDisplayPhoto(String photoname) {
if(photoname == null)
return null;
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT * FROM PHOTO WHERE photoNo = ?;");
ps.setString(1, photoname);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
String previewPath = rs.getString("relativePathPreview");
String extension = rs.getString("extension");
String isPublicString = rs.getString("public");
return new PhotoReturned(previewPath, photoname, extension, ("1".equals(isPublicString) ? true : false));
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return null;
}
public static boolean accesPermitted(String username, String photo) {
if(photo != null) {
try {
int photonr = Integer.parseInt(photo);
accesPermitted(username, photonr);
} catch (IllegalArgumentException iae) { }
}
return false;
}
public static synchronized boolean accesPermitted(String username, Integer photo) {
if(username == null || photo == null) return false;
boolean acces = false;
try {
openConnection(true);
PreparedStatement ps = con.prepareStatement("SELECT * FROM PHOTO_OWNER WHERE photoNo = ? AND customer = ?;");
ps.setInt(1, photo);
ps.setString(2, username);
acces = ps.executeQuery().first();
// Check if it's the photographer.
if (!acces) {
ps = con.prepareStatement("SELECT * FROM PHOTO WHERE photoNo = ? AND photographer = ?;");
ps.setInt(1, photo);
ps.setString(2, username);
acces = ps.executeQuery().first();
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return acces;
}
public static List<String> getPhotos(String username) {
if(username == null)
return null;
List<String> photos = new ArrayList<String>();
try {
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT * FROM PHOTO WHERE photoNo IN(SELECT photoNO FROM PHOTO_OWNER WHERE customer = (?));");
ps.setString(1, username);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
//String webPath = rs.getString("relativePathPreview");
//webPath = webPath.substring(webPath.indexOf("/previews/") + 1);
//String photo = String.format("%s%s.%s", webPath, rs.getString("photoNo"), rs.getString("extension"));
photos.add(rs.getString("photoNo"));
}
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return photos;
}
public static List<Photo> getPublicPhotos() {
List<Photo> photos = new ArrayList<Photo>();
try {
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT * FROM PHOTO WHERE public = 1 ORDER BY photoNo DESC");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String webPath = rs.getString("relativePathPreview");
webPath = webPath.substring(webPath.indexOf("/previews/") + 1);
int photoNo = rs.getInt("photoNo");
String photo = String.format("%s%s.%s", webPath, photoNo, rs.getString("extension"));
PreparedStatement ps2 = con.prepareCall("SELECT (SELECT COUNT(*)*3 FROM PHOTO_RATING WHERE photoNo = ? AND voter IN(SELECT username FROM ACCOUNT WHERE photographer = 1)) + (SELECT COUNT(*) FROM PHOTO_RATING WHERE photoNo = ? AND voter IN(SELECT username FROM ACCOUNT WHERE photographer = 0)) AS rating");
ps2.setInt(1, photoNo);
ps2.setInt(2, photoNo);
ResultSet rs2 = ps2.executeQuery();
if(rs2.next()) {
photos.add(new Photo(photo, rs.getString("photographer"),rs.getDate("uploadDate"), rs2.getInt("rating")));
}
}
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return photos;
}
public static List<String[]> getPhotosFromPhotographer(String pgName){
List<String[]> photos = new ArrayList<String[]>();
try{
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT * FROM PHOTO WHERE photographer = ?");
ps.setString(1, pgName);
ResultSet rs = ps.executeQuery();
while(rs.next()){
String[] photoAtts = new String[3];
int nr = rs.getInt("photoNo");
String path = String.format("%s%s.%s", rs.getString("relativePathPreview"), rs.getString("photoNo"), rs.getString("extension"));
double price = rs.getDouble("price");
photoAtts[0] = String.valueOf(nr);
photoAtts[1] = String.valueOf(price);
photoAtts[2] = path;
photos.add(photoAtts);
}
} catch (Exception exc){
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, exc);
} finally {
try{
con.close();
} catch (SQLException ex){
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return photos;
}
public static List<Photo> getPhotographerPhotos(String pgName) {
if(pgName == null)
return null;
List<Photo> photos = new ArrayList<Photo>();
try {
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT * FROM PHOTO WHERE photographer = ?");
ps.setString(1, pgName);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String webPath = rs.getString("relativePathPreview");
webPath = webPath.substring(webPath.indexOf("/previews/") + 1);
int photoNo = rs.getInt("photoNo");
String photo = String.format("%s%s.%s", webPath, photoNo, rs.getString("extension"));
int rating = getRating(photoNo);
photos.add(new Photo(photo, rs.getString("photographer"), rs.getDate("uploadDate"), rating));
}
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return photos;
}
/* De prijs van een foto kan door een fotograaf worden aangepast.
** int photoNr - het nummer van de foto (pk in database)
** double newPrice - de nieuwe prijs van de foto
*/
public static boolean changePhotoPrice(int photoNr, double newPrice){
if(photoNr < 0 || newPrice < 0)
return false;
boolean succes = false;
try{
// Open verbinding zonder auto-commit
openConnection(false);
// Initialiseer ps met query
PreparedStatement ps =
con.prepareCall("UPDATE PHOTO SET price=? WHERE photoNo=?");
// Zet de juiste waarden in de query
ps.setDouble(1, newPrice);
ps.setInt(2, photoNr);
// Voer update uit
ps.executeUpdate();
// maak een tweede SQL-query
// om de verandering te verifiëren
PreparedStatement ps2 =
con.prepareCall("SELECT price FROM PHOTO WHERE photoNo=?");
// Zet de juiste waarden in de query
ps2.setInt(1, photoNr);
double toCheck = 0;
// Voer query uit en zet de opgevangen data in een RS
ResultSet rs = ps2.executeQuery();
// Loop door de RS om de (nieuwe) prijs aan een var te koppelen
while(rs.next()){
toCheck = rs.getDouble("price");
}
// De opgevraagde waarde (ps2) moet gelijk zijn aan de doorgegeven
// waarde (ps1) om de prijsverandering een succes te noemen. Voer
// een commit door als dit klopt.
if(toCheck == newPrice){
succes = true;
con.commit();
System.out.println("Waarden kloppen en zijn doorgevoerd");
} else {
succes = false;
System.out.println("Waarden kloppen niet");
}
} catch (Exception exc){
succes = false;
System.out.println(exc);
} finally {
try {
// Sluit de verbinding en geef het resultaat terug
con.close();
return succes;
} catch (SQLException ex) {
// Er is iets misgegaan met het sluiten van de verbinding...
succes = false;
System.out.println("Finally -> catch");
} finally {
return succes;
}
}
}
/**
* @deprecated This method is replaced by DatabaseORM.updateCategory();
*/
@Deprecated
public static boolean changeCategoryPrice(String categoryName, double newPrice){
if(categoryName == null || newPrice < 0)
return false;
boolean succes = false;
try{
// Open verbinding zonder auto-commit
openConnection(false);
// Initialiseer ps met query
PreparedStatement ps =
con.prepareCall("UPDATE CATEGORY SET price=? WHERE name=?");
// Zet de juiste waarden in de query
ps.setDouble(1, newPrice);
ps.setString(2, categoryName);
// Voer update uit
ps.executeUpdate();
// maak een tweede SQL-query
// om de verandering te verifiëren
PreparedStatement ps2 =
con.prepareCall("SELECT price FROM CATEGORY WHERE name=?");
// Zet de juiste waarden in de query
ps2.setString(1, categoryName);
double toCheck = 0;
// Voer query uit en zet de opgevangen data in een RS
ResultSet rs = ps2.executeQuery();
// Loop door de RS om de (nieuwe) prijs aan een var te koppelen
while(rs.next()){
toCheck = rs.getDouble("price");
}
// De opgevraagde waarde (ps2) moet gelijk zijn aan de doorgegeven
// waarde (ps1) om de prijsverandering een succes te noemen. Voer
// een commit door als dit klopt.
if(toCheck == newPrice){
succes = true;
con.commit();
System.out.println("Waarden kloppen en zijn doorgevoerd");
} else {
succes = false;
System.out.println("Waarden kloppen niet");
}
} catch (Exception exc){
succes = false;
System.out.println(exc);
} finally {
try {
// Sluit de verbinding en geef het resultaat terug
con.close();
return succes;
} catch (SQLException ex) {
// Er is iets misgegaan met het sluiten van de verbinding...
succes = false;
System.out.println("Finally -> catch");
} finally {
return succes;
}
}
}
/**
* @deprecated This method is replaced by DatabaseORM.getCategories();
*/
@Deprecated
public static List<String[]> getCategories(String username){
List<String[]> categories = new ArrayList<String[]>();
if(username == null) return categories;
try{
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT * FROM CATEGORY WHERE photographerName = ?");
ps.setString(1, username);
ResultSet rs = ps.executeQuery();
while(rs.next()){
String[] categoryAtts = new String[3];
String naam = rs.getString("name");
double price = rs.getDouble("price");
int photoCount = 0;
PreparedStatement psCount = con.prepareCall("SELECT COUNT(*) AS pc FROM PHOTO WHERE photographer=? AND category=?");
psCount.setString(1, username);
psCount.setString(2, naam);
ResultSet rsCount = psCount.executeQuery();
while(rsCount.next()){
photoCount = rsCount.getInt("pc");
}
categoryAtts[0] = naam;
categoryAtts[1] = String.valueOf(price);
categoryAtts[2] = String.valueOf(photoCount);
categories.add(categoryAtts);
}
} catch (Exception exc){
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, exc);
} finally {
try{
con.close();
} catch (SQLException ex){
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return categories;
}
/**
* @deprecated This method has been replaced by DatabaseORM.getOrders();
*/
@Deprecated
public static List<Order> getOrders(boolean handled) {
List<Order> orders = new ArrayList<Order>();
try {
int handle;
if(handled) {
handle = 1;
} else {
handle = 0;
}
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT * FROM BUY_ORDER WHERE handled = ? ORDER BY orderDate");
ps.setInt(1, handle);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
orders.add(new Order(rs.getInt("orderNo"), rs.getString("customer"), rs.getDate("orderDate"), rs.getDate("paymentDate")));
}
for(Order order : orders) {
ps = con.prepareCall("SELECT SUM(productPrice*quantity) AS price FROM ORDER_ITEM WHERE orderNo = ?");
ps.setInt(1, order.orderNo);
rs = ps.executeQuery();
while (rs.next()) {
order.setPrice(rs.getDouble("price"));
}
}
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return orders;
}
@Deprecated
public static boolean handleOrder(int orderNo) {
return handleOrder(orderNo, 1);
}
@Deprecated
public static boolean handleOrder(int orderNo, int newstatus) {
boolean success = false;
try{
// Open verbinding zonder auto-commit
openConnection(false);
// Initialiseer ps met query
PreparedStatement ps =
con.prepareCall("UPDATE BUY_ORDER SET handled=? WHERE orderNo=?");
// Zet de juiste waarden in de query
ps.setInt(1, newstatus);
ps.setInt(2, orderNo);
// Voer update uit
if(ps.executeUpdate() == 1) {
con.commit();
success = true;
}else{
con.rollback();
}
} catch (Exception ex){
} finally {
try {
con.close();
} catch (SQLException ex) { }
}
return success;
}
/**
* @deprecated This method has been replaced by DatabaseORM.getOrders();
*/
@Deprecated
public static List<Order> getOrders(String user) {
List<Order> orders = new ArrayList<Order>();
if(user == null) return orders;
try {
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT * FROM BUY_ORDER WHERE handled = 1 AND customer = ?");
ps.setString(1, user);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
orders.add(new Order(rs.getInt("orderNo"), rs.getString("customer"), rs.getDate("orderDate"), rs.getDate("paymentDate")));
}
for(Order order : orders) {
ps = con.prepareCall("SELECT SUM(productPrice*quantity) AS price FROM ORDER_ITEM WHERE orderNo = ?");
ps.setInt(1, order.orderNo);
rs = ps.executeQuery();
while (rs.next()) {
order.isCancellable = isOrderCancellable(order.orderNo);
order.setPrice(rs.getDouble("price"));
}
}
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return orders;
}
public static double getItemPrice(Integer photo, String product) {
if(photo == null || product == null) return 0;
double price = 0;
try {
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT price FROM CATEGORY WHERE name = (SELECT category FROM PHOTO WHERE photoNo = ?) AND photographerName = (SELECT photographer FROM PHOTO WHERE photoNo = ?)");
ps.setInt(1, photo);
ps.setInt(2, photo);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
price += rs.getDouble("price");
}
ps = con.prepareCall("SELECT price FROM PHOTO WHERE photoNo = ?");
ps.setInt(1, photo);
rs = ps.executeQuery();
if(rs.next()) {
price += rs.getDouble("price");
}
ps = con.prepareCall("SELECT price FROM PRODUCT WHERE name = ?");
ps.setString(1, product);
rs = ps.executeQuery();
if(rs.next()) {
price += rs.getDouble("price");
}
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return price;
}
public static double getPhotoPrice(Integer photoNo){
if(photoNo == null)
return 0.0;
try{
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT price FROM PHOTO WHERE photoNo = ?");
ps.setInt(1, photoNo);
ResultSet rs = ps.executeQuery();
if(rs.next()){
return rs.getDouble("price");
}
} catch (Exception exc){
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, exc);
} finally {
try{
con.close();
} catch (SQLException ex){
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return 0;
}
public static double getCategoryPrice(String category){
if(category == null) return 0;
try{
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT price FROM CATEGORY WHERE name = ?");
ps.setString(1, category);
ResultSet rs = ps.executeQuery();
if(rs.next()){
return rs.getDouble("price");
}
} catch (Exception exc){
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, exc);
} finally {
try{
con.close();
} catch (SQLException ex){
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return 0;
}
public static boolean categoryExists(String category, String username) {
if(category == null || username == null)
return false;
boolean succes = false;
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT name FROM CATEGORY WHERE name = ? AND photographerName = ?;");
ps.setString(1, category);
ps.setString(2, username);
succes = ps.execute();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return succes;
}
/**
* @deprecated This method is replaced by DatabaseORM.getAccountRegistrationDate();
*/
@Deprecated
public static Date getRegisterdate(String username) {
if(username == null)
return null;
try
{
openConnection(false);
PreparedStatement ps2 =
con.prepareCall("SELECT registerDate FROM ACCOUNT WHERE username=?");
// Zet de juiste waarden in de query
ps2.setString(1, username);
ResultSet rs = ps2.executeQuery();
if (rs.next())
{
return rs.getDate("registerDate");
}
}
catch (SQLException ex)
{
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
finally
{
if(con != null)
{
try
{
con.close();
}
catch (SQLException ex)
{
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return null;
}
/**
* @deprecated This method has been replaced by DatabaseORM.getOrderItems();
*/
@Deprecated
public static List<Item> getOrderItems(Integer orderNo) {
List<Item> orderedItems = new ArrayList<Item>();
if(orderNo == null)
return orderedItems;
try {
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT * FROM ORDER_ITEM WHERE orderNo = ?");
ps.setInt(1, orderNo);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
int photoNo = rs.getInt("photoNo");
String product = rs.getString("product");
String photoType = rs.getString("photoType");
int quantity = rs.getInt("quantity");
double price = rs.getDouble("productPrice");
orderedItems.add(new Item(orderNo, photoNo, quantity, product, photoType, price));
}
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return orderedItems;
}
/**
* Test if a order can be cancelled.
* @param ordernr
* @param username
* @return true if order creation is less than 3 hrs ago.
* @deprecated This method has been replaced by DatabaseORM.isOrderCancellable();
*/
@Deprecated
public static boolean isOrderCancellable(Integer ordernr) {
if(ordernr == null)
return false;
boolean result = false;
boolean database_connection_opened = false;
try {
database_connection_opened = (con != null && con.isValid(0));
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
try {
if(!database_connection_opened)
openConnection(false);
PreparedStatement ps = con.prepareCall("SELECT orderDate, paymentDate FROM BUY_ORDER WHERE orderNo = ?");
ps.setInt(1, ordernr);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
Timestamp orderDate = rs.getTimestamp("orderDate");
Calendar localCalendar = Calendar.getInstance(TimeZone.getDefault());
localCalendar.add(GregorianCalendar.HOUR_OF_DAY, -3);
Calendar orderDate_calendar = Calendar.getInstance();
orderDate_calendar.setTime(orderDate);
result = localCalendar.before(orderDate_calendar);
}
} catch (Exception ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if(!database_connection_opened) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return result;
}
/**
* Cancel a order
* this is only possible when the order was created less than 3 hours ago.
* @param ordernr the order number.
* @param username the user that created the order.
* @return true if operation succeeds.
*
* TODO: check if order created less than 3 hrs ago.
* @deprecated This method has been replaced by DatabaseORM.cancelOrder();
*/
@Deprecated
public static boolean cancelUserOrder(Integer ordernr, String username) {
if(username == null || ordernr == null)
return false;
boolean success = false;
if(!isOrderCancellable(ordernr)) {
return false;
}
try {
openConnection(false);
PreparedStatement ps;
if (!checkOrderDate(ordernr)) {
return success;
}
ps = con.prepareStatement("DELETE FROM ORDER_ITEM WHERE orderNo = ?");
ps.setInt(1, ordernr);
ps.executeUpdate();
ps = con.prepareStatement("DELETE FROM BUY_ORDER WHERE orderNo = ? AND customer = ?");
ps.setString(1, "" + ordernr);
ps.setString(2, username);
int rows_affected = ps.executeUpdate();
if(rows_affected != 1) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null,
rows_affected + " rows affected, but 1 row should be affected in DatabaseKoppeling.cancelUserOrder");
success = false;
}else {
con.commit();
success = true;
}
} catch (SQLException ex) {
success = false;
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if(!success)
con.rollback();
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
return success;
}
/*
* @deprecated This method has been replaced by DatabaseORM.isOrderCancellable();
*/
@Deprecated
private static boolean checkOrderDate(Integer orderNo) {
if(orderNo == null) return false;
boolean closeConnection = false;
boolean result = false;
try {
if (con == null || con.isClosed()) {
openConnection(true);
closeConnection = true;
}
PreparedStatement ps = con.prepareCall("SELECT paymentDate FROM BUY_ORDER WHERE orderNo = ?;");
ps.setInt(1, orderNo);
ResultSet rs = ps.executeQuery();
if(rs.first()) {
Date now = new Date();
Timestamp ts = rs.getTimestamp(1);
long timepassed = now.getTime() / 1000 - ts.getTime() / 1000;
result = timepassed <= 60 * 60 * 3;
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null && closeConnection) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return result;
}
static boolean changePublic(String username, Integer photo) {
if(username == null || photo == null)
return false;
boolean result = false;
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT public FROM PHOTO WHERE photoNo = ? AND photographer = ?;");
ps.setInt(1, photo);
ps.setString(2, username);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
Integer isPublicInt = rs.getInt(1);
rs.close();
if (isPublicInt.equals(0) || isPublicInt.equals(1)) {
ps = con.prepareCall("UPDATE PHOTO SET public = ? WHERE photoNo = ? AND photographer = ?;");
ps.setInt(1, isPublicInt.equals(0) ? 1 : 0);
ps.setInt(2, photo);
ps.setString(3, username);
ps.executeUpdate();
result = isPublicInt.equals(0) ? false : true;
}
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return result;
}
public static boolean isPublic(String photo) {
if(photo != null) {
try {
int photonr = Integer.parseInt(photo);
return isPublic(photonr);
} catch (IllegalArgumentException iae) {}
}
return false;
}
public static boolean isPublic(Integer photo) {
if(photo == null) return false;
boolean result = false;
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT public FROM PHOTO WHERE photoNo = ?;");
ps.setInt(1, photo);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
Integer isPublicInt = rs.getInt(1);
if (isPublicInt.equals(0) || isPublicInt.equals(1)) {
result = isPublicInt.equals(0) ? false : true;
}
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return result;
}
public static Boolean like(String username, Integer photo) {
if(username == null || photo == null)
return null;
Boolean result = null;
try {
openConnection(true);
String photographer = "";
PreparedStatement ps = con.prepareStatement("SELECT photographer FROM PHOTO WHERE photoNo = ?;");
ps.setInt(1, photo);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
photographer = rs.getString(1);
}
// Can't vote on your own photo!
if (!photographer.equals(username)) {
ps = con.prepareCall("SELECT photographer FROM ACCOUNT WHERE username = (SELECT voter FROM PHOTO_RATING WHERE photoNo = ? AND voter = ?)");
ps.setInt(1, photo);
ps.setString(2, username);
rs = ps.executeQuery();
result = rs.first();
if (result) {
//ps = con.prepareCall("DELETE FROM PHOTO_RATING WHERE photoNo = ? AND voter = ?");
//ps.setString(1, photo);
//ps.setString(2, username);
//ps.executeUpdate();
} else {
ps = con.prepareCall("INSERT INTO PHOTO_RATING values(?, ?)");
ps.setInt(1, photo);
ps.setString(2, username);
ps.executeUpdate();
// Update the account to A-Photographer if the votes of A-Photographers are >= 100
if (isAPhotographer(username)) {
ps = con.prepareStatement("SELECT COUNT(*)*3 FROM PHOTO_RATING WHERE photoNo = ? AND voter IN(SELECT username FROM ACCOUNT WHERE photographer = 1);");
ps.setInt(1, photo);
rs = ps.executeQuery();
if (rs.first()) {
int a_photographerVotes = rs.getInt(1);
if (a_photographerVotes >= 3) {
if (!isAPhotographer(photographer)) {
ps = con.prepareCall("UPDATE ACCOUNT SET photographer = 1 WHERE username = ?;");
ps.setString(1, photographer);
ps.executeUpdate();
}
}
}
}
}
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return result;
}
public static boolean getLiked(String username, Integer photo) {
if(username == null || photo == null)
return false;
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT voter FROM PHOTO_RATING WHERE photoNo = ? AND voter = ?");
ps.setInt(1, photo);
ps.setString(2, username);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
return true;
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return false;
}
public static boolean isPhotographerOf(String username, Integer photo) {
if(username == null || photo == null)
return false;
try {
openConnection(true);
PreparedStatement ps = con.prepareCall("SELECT photoNo FROM PHOTO WHERE photoNo = ? AND photographer = ?");
ps.setInt(1, photo);
ps.setString(2, username);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
return true;
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return false;
}
public static int getRating(Integer photo) {
if(photo == null) return 0;
boolean closeConnection = false;
try {
if (con == null || con.isClosed()) {
openConnection(true);
closeConnection = true;
}
PreparedStatement ps = con.prepareCall("SELECT (SELECT COUNT(*)*3 FROM PHOTO_RATING WHERE photoNo = ? AND voter IN(SELECT username FROM ACCOUNT WHERE photographer = 1)) + (SELECT COUNT(*) FROM PHOTO_RATING WHERE photoNo = ? AND voter IN(SELECT username FROM ACCOUNT WHERE photographer = 0)) AS rating");
ps.setInt(1, photo);
ps.setInt(2, photo);
ResultSet rs = ps.executeQuery();
if(rs.first()) {
return rs.getInt("rating");
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null && closeConnection) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return 0;
}
public static List<String[]> getTop10BestSellingPhotos(String username) {
boolean closeConnection = false;
List<String[]> top10 = new ArrayList<String[]>();
if(username == null)
return top10;
try {
if (con == null || con.isClosed()) {
openConnection(true);
closeConnection = true;
}
PreparedStatement ps = con.prepareCall("SELECT photoNo, SUM(quantity) FROM ORDER_ITEM WHERE photoNo IN("
+ "SELECT photoNo FROM PHOTO WHERE photographer = ?) "
+ "GROUP BY photoNo "
+ "LIMIT 10;");
ps.setString(1, username);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
top10.add(new String[] { rs.getString(1), rs.getString(2) });
}
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (con != null && closeConnection) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
return top10;
}
public static boolean insertOrder(Shoppingcart cart) {
if(cart == null)
return false;
try {
openConnection(true);
int orderNo = 1;
PreparedStatement ps = con.prepareCall("SELECT MAX(orderNo) AS orderNo");
ResultSet rs = ps.executeQuery();
if (rs.first()) {
orderNo += rs.getInt("orderNo");
}
ps = con.prepareCall("INSERT INTO BUY_ORDER VALUES(?, ?, sysdate(), sysdate(), 0)");
ps.setInt(1, orderNo);
ps.setString(2, username);
for(int i = 0; i < cart.size(); i++) {
ps = con.prepareCall("INSERT INTO FROM ORDER_ITEM VALUES(?, ?, ?, ?, ?, ?)");
ps.setInt(1, orderNo);
ps.setInt(2, cart.get(i).getPhoto());
ps.setString(3, cart.get(i).getProduct());
ps.setString(4, cart.get(i).getColor());
ps.setInt(5, cart.get(i).getAmount());
ps.setDouble(6, getItemPrice(cart.get(i).getPhoto(), cart.get(i).getProduct()));
}
return true;
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
return false;
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
public static String encrypt(String password) {
if(password == null) return null;
try {
BASE64Encoder enc = new BASE64Encoder();
return byteArrayToHexString(MessageDigest.getInstance("MD5").digest(enc.encode(password.getBytes("UTF-8")).getBytes("UTF-8")));
} catch (NoSuchAlgorithmException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
} catch (UnsupportedEncodingException ex) {
Logger.getLogger(DatabaseKoppeling.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
private static String byteArrayToHexString(byte[] b) {
String result = "";
for (int i=0; i < b.length; i++) {
result += Integer.toString( ( b[i] & 0xff ) + 0x100, 16).substring( 1 );
}
return result;
}
public static Integer toInteger(String string) {
Integer integer = Integer.valueOf(string);
if(integer == null)
return null;
try {
Integer i = Integer.valueOf(string);
return i;
} catch(IllegalArgumentException iae) {
return null;
}
}
}