Package Java

Source Code of Java.DatabaseKoppeling

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;
        }
    }
}
TOP

Related Classes of Java.DatabaseKoppeling

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.