Untitled

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import rs.ac.bg.fon.ps.domain.Manufacturer;
import rs.ac.bg.fon.ps.domain.MeasurementUnit;
import rs.ac.bg.fon.ps.domain.Product;
import rs.ac.bg.fon.ps.domain.User;

/**
 *
 * @author Cartman
 */
public class DatabaseBroker {
    //com.mysql.jdbc.Driver
    //com.mysql.cj.jdbc.Driver
    
    private Connection connection;
    
    public void uspostaviKonekciju() throws Exception{
        try {
            if(connection==null || connection.isClosed()){
                
                //Class.forName("com.mysql.cj.jdbc.Driver");
                String url="jdbc:mysql://localhost:3306/psdbd12021";
                String user="root";
                String password="root";
                connection=DriverManager.getConnection(url, user, password);
                System.out.println("Uspesna konekcija!");
            }
        } catch (Exception ex) {
            System.out.println("Neuspesna konekcija na bazu!\n"+ex.getMessage());
            throw ex;
        }
    }
    
    public void raskiniKonekciju() throws SQLException{
        try {
            if(connection!=null){
                connection.close();
                System.out.println("Uspesno raskinuta konekcija!");
            }
        } catch (SQLException ex) {
            System.out.println("Neuspesno raskinuta konekcija na bazu!\n"+ex.getMessage());
            throw ex;
        }
    }
    
    
    
   
    // DOMACI
        //user
    
    public void kreirajUser(User user) throws SQLException{
        try{
            String upit = "INSERT INTO user (id, firstname,lastname,username,password) VALUES (?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(upit);
            statement.setLong(1, user.getId());
            statement.setString(2, user.getFirstName());
            statement.setString(3, user.getLastName());
            statement.setString(4, user.getUsername());
            statement.setString(5, user.getPassword());
            
            statement.executeUpdate();
            statement.close();
            System.out.println("Uspesno keriran user!");
        }catch(SQLException e){
            System.out.println("Neuspesno kreiran user!");
            throw e;
        }
    }
    
    public void updateUser(Long id,String ime,String prezime,String username,String password) throws SQLException{
        
        try {
            String upit = "UPDATE user SET Firstname="+"'"+ime+
                    "', lastname="+"'"+prezime+
                    "', username="+"'"+username+
                    "', password="+"'"+password+
                    "' WHERE id=" + id;
            System.out.println(upit);
            
            Statement statement = connection.createStatement();
            
            System.out.println("1");
            statement.executeUpdate(upit);
            System.out.println("2");
            statement.close();
            System.out.println("Uspesna izmena za user");
            
            
        } catch (SQLException ex) {
            System.out.println("Neuspesna izmena za user!");
            throw ex;
        }
                
        
        
        
        
    }
    
    public List<User> vratiSveUser() throws SQLException{
        try {
            List<User> users=new ArrayList<>();
            String upit="SELECT id,Firstname,lastname,username,password FROM user";
            System.out.println(upit);
            Statement statement=connection.createStatement();
            ResultSet rs=statement.executeQuery(upit);
            User user;
            while(rs.next()){
                user = new User();
                user.setId(rs.getLong("id"));
                user.setFirstName(rs.getString("Firstname"));
                user.setLastName(rs.getString("lastname"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                users.add(user);
            }
            rs.close();
            statement.close();
            System.out.println("Uspesno ucitana lista Usera!");
            return users;
        } catch (SQLException ex) {
            System.out.println("Neuspesno ucitana lista Usera!\n"+ex.getMessage());
            throw ex;
        }
    }
    
    public void obrisiUser(Long id) throws SQLException{
         
         try {
            String upit = "DELETE FROM user WHERE id=" + id;
            System.out.println(upit);
            
            Statement statement = connection.createStatement();
            
            System.out.println("1");
            statement.executeUpdate(upit);
            System.out.println("2");
            statement.close();
            System.out.println("Uspesno obrisan user");
            
            
        } catch (SQLException ex) {
            System.out.println("Neuspesno obrisan user!");
            throw ex;
        }
        
    }
    
        //manufacturer
    
    public void kreirajManufacturer(Manufacturer manufacturer) throws SQLException{
        try {
            String upit="INSERT INTO Manufacturer (id, name) VALUES ("
                    + ""+manufacturer.getId()+", "
                    + "'"+manufacturer.getName()+"'"
                    + ")";
            System.out.println(upit);
            
            Statement statement=connection.createStatement();
            
            statement.executeUpdate(upit);
            statement.close();
            System.out.println("Uspesno kreiran Manufacturer!");
        } catch (SQLException ex) {
            System.out.println("Neuspesno kreiran Manufacturer!\n"+ex.getMessage());
            throw ex;
        }
        
    }
    
    public void updateManufacturer(Long id,String name) throws SQLException{
        
         try {
            String upit = "UPDATE manufacturer SET name="+"'"+name+
                    "' WHERE id=" + id;
            System.out.println(upit);
            
            Statement statement = connection.createStatement();
            
            System.out.println("1");
            statement.executeUpdate(upit);
            System.out.println("2");
            statement.close();
            System.out.println("Uspesna izmena za manufacturer");
            
            
        } catch (SQLException ex) {
            System.out.println("Neuspesna izmena za manufacturer!");
            throw ex;
        }
        
    }
    
    public List<Manufacturer> vratiSveManufacturer() throws SQLException{
        try {
            List<Manufacturer> manufacturers=new ArrayList<>();
            String upit="SELECT id AS sifra, name FROM Manufacturer";
            System.out.println(upit);
            Statement statement=connection.createStatement();
            ResultSet rs=statement.executeQuery(upit);
            while(rs.next()){
                Manufacturer manufacturer=new Manufacturer();
                manufacturer.setId(rs.getLong("sifra"));
                manufacturer.setName(rs.getString("name"));
                manufacturers.add(manufacturer);
            }
            rs.close();
            statement.close();
            System.out.println("Uspesno ucitana lista Manufacturer!");
            return manufacturers;
        } catch (SQLException ex) {
            System.out.println("Neuspesno ucitana lista Manufacturer!\n"+ex.getMessage());
            throw ex;
        }
    }
    
    public void obrisiManufacturer(Long id) throws SQLException{
         try {
            String upit = "DELETE FROM manufacturer WHERE id="+id;
            System.out.println(upit);
            
            Statement statement = connection.createStatement();
            
            System.out.println("1");
            statement.executeUpdate(upit);
            System.out.println("2");
            statement.close();
            System.out.println("Uspesno obrisan manufacturer");
            
            
        } catch (SQLException ex) {
            System.out.println("Neuspesno obrisan manufacturer!");
            throw ex;
        }
    }
    
        //product
    
     public void kreirajProduct(Product product) throws SQLException{
        try {
            String upit="INSERT INTO Product (id, name, description, price, measurementunit, manufacturer, savedByUser) VALUES ("
                    + ""+product.getId()+", "
                    + "'"+product.getName()+"',"
                    + "'"+product.getDescription()+"',"
                    + ""+product.getPrice()+","
                    + "'"+product.getMeasurementUnit().toString()+"',"
                    + ""+product.getManufacturer().getId()+","
                    + ""+product.getSavedByUser().getId()+""
                    + ")";
            System.out.println(upit);
            
            Statement statement=connection.createStatement();
            
            statement.executeUpdate(upit);
            statement.close();
            System.out.println("Uspesno kreiran Product!");
        } catch (SQLException ex) {
            System.out.println("Neuspesno kreiran Product! aaa\n"+ex.getMessage());
            throw ex;
        }
        
    }
    
    public void kreirajProductBoljiNacin(Product product) throws SQLException{
        try {
            String upit="INSERT INTO Product (id, name, description, price, measurementunit, manufacturer, savedByUser) VALUES (?,?,?,?,?,?,?)";
            System.out.println(upit);
            
            PreparedStatement statement=connection.prepareStatement(upit);
            statement.setLong(1, product.getId());
            statement.setString(2, product.getName());
            statement.setString(3, product.getDescription());
            statement.setBigDecimal(4, product.getPrice());
            statement.setString(5, product.getMeasurementUnit().toString());
            statement.setLong(6, product.getManufacturer().getId());
            statement.setLong(7, product.getSavedByUser().getId());
            
            statement.executeUpdate();
            statement.close();
            System.out.println("Uspesno kreiran Product!");
        } catch (SQLException ex) {
            System.out.println("Neuspesno kreiran Product!\n"+ex.getMessage());
            throw ex;
        }
        
    }
    
    public void updateProduct(Long id,String name,String description,Long price,MeasurementUnit measurmentunit, Manufacturer manufacturer,User savedByUser) throws SQLException{
        try {
            String upit = "UPDATE product SET name="+"'"+name+
                    "', description="+"'"+description+
                    "', price="+price+
                    ", measurementunit='"+measurmentunit+
                    "',manufacturer='"+manufacturer.getId()+
                    "',savedbyuser='"+savedByUser.getId()+
                    "' WHERE id=" + id;
            System.out.println(upit);
            
            Statement statement = connection.createStatement();
            
            System.out.println("1");
            statement.executeUpdate(upit);
            System.out.println("2");
            statement.close();
            System.out.println("Uspesna izmena za product");
            
            
        } catch (SQLException ex) {
            System.out.println("Neuspesna izmena za product!");
            throw ex;
        }
    }
    
    public List<Product> vratiSveProduct1() throws SQLException{
        //preko donje dve metode da ukljucim user i manufacturer
     
        try {
            List<Product> products=new ArrayList<>();
            String upit="SELECT id, name,description,price,measurementunit,manufacturer,savedbyuser FROM Product";
            System.out.println(upit);
            Statement statement=connection.createStatement();
            ResultSet rs=statement.executeQuery(upit);
            Product product;
            while(rs.next()){
                product = new Product();
                product.setId(rs.getLong("id"));
                product.setName(rs.getString("name"));
                product.setDescription(rs.getString("description"));
                product.setPrice(rs.getBigDecimal("price"));
                // Kako drugacije uraditi ovo ?
                product.setMeasurementUnit(stringToMeasurementUnit(rs.getString("measurementunit")));
                product.setManufacturer(vratiManufacturerPoSifri((rs.getLong("manufacturer"))));
                product.setSavedByUser(vratiKorisnikaPoSifri(rs.getLong("savedbyuser")));
                
                products.add(product);
            }
            rs.close();
            statement.close();
            System.out.println("Uspesno ucitana lista Producta!");
            return products;
        } catch (SQLException ex) {
            System.out.println("Neuspesno ucitana lista Producta!\n"+ex.getMessage());
            throw ex;
        }
    }
    
     public List<Product> vratiSveProduct2() throws SQLException{
        //preko inner joina da ukljucim user i manufacturer
        try {
            List<Product> products=new ArrayList<>();
            String upit="SELECT p.id, p.name,p.description,p.price,p.measurementunit,p.manufacturer,p.savedbyuser FROM Product AS p "
                    + "INNER JOIN manufacturer AS m ON (p.manufacturer=m.id) INNER JOIN user AS u ON p.savedbyuser=u.id";
            System.out.println(upit);
            Statement statement=connection.createStatement();
            ResultSet rs=statement.executeQuery(upit);
            Product product;
            while(rs.next()){
                product = new Product();
                product.setId(rs.getLong("p.id"));
                product.setName(rs.getString("p.name"));
                product.setDescription(rs.getString("p.description"));
                product.setPrice(rs.getBigDecimal("p.price"));
                // Kako drugacije uraditi ovo ?
                product.setMeasurementUnit(stringToMeasurementUnit(rs.getString("p.measurementunit")));
                product.setManufacturer(vratiManufacturerPoSifri((rs.getLong("p.manufacturer"))));
                product.setSavedByUser(vratiKorisnikaPoSifri(rs.getLong("p.savedbyuser")));
                
                products.add(product);
            }
            rs.close();
            statement.close();
            System.out.println("Uspesno ucitana lista Producta!");
            return products;
        } catch (SQLException ex) {
            System.out.println("Neuspesno ucitana lista Producta!\n"+ex.getMessage());
            throw ex;
        }
    }
    
    public void obrisiProduct(Long id) throws SQLException{
        try {
            String upit = "DELETE FROM product WHERE id="+id;
            System.out.println(upit);
            
            Statement statement = connection.createStatement();
            
            System.out.println("1");
            statement.executeUpdate(upit);
            System.out.println("2");
            statement.close();
            System.out.println("Uspesno obrisan product");
            
            
        } catch (SQLException ex) {
            System.out.println("Neuspesno obrisan product!");
            throw ex;
        }
    }
    
    
        //dopunske
    public User vratiKorisnikaPoSifri(Long id) throws SQLException{
        try {
            //User user = new User();
            List<User> users = vratiSveUser();
            
            for(User user: users){
                if(user.getId() == id){
                    System.out.println("User vracen!");
                    return user;
                }
            }
            
        } catch (SQLException ex) {
           System.out.println("User nije vracen!");
            throw ex;
        }
        //System.out.println("User nije vracen!");
        return null;
    }
    public Manufacturer vratiManufacturerPoSifri(Long id) throws SQLException{
        try {
            //User user = new User();
            
            List<Manufacturer> manufacturers = vratiSveManufacturer();
            
            for(Manufacturer manufacturer: manufacturers){
                if(manufacturer.getId() == id){
                    System.out.println("Manufacturer vracen!");
                    return manufacturer;
                }
            }
            
        } catch (SQLException ex) {
            System.out.println("Manufacturer nije vracen!");
            throw ex;
        }
        
        return null;
    }
    public MeasurementUnit stringToMeasurementUnit(String str){
        if(str.trim().equals("G")){
            return MeasurementUnit.G;
        }else if(str.trim().equals("KG")){
            return MeasurementUnit.KG;
        }else if(str.trim().equals("L")){
            return MeasurementUnit.L;
        }else if(str.trim().equals("PCS")){
            return MeasurementUnit.PCS;
        }
        return null;
    }
}