/*
 * 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 banco;

import classes.Fornecedor;
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 javax.swing.JOptionPane;


public class FornecedorBD {
      // Drive de conexão com o MySQL
    private String servidor = "com.mysql.jdbc.Driver";
    // URL que identifica o banco
    private String urlBanco = "jdbc:mysql://localhost:3306/mercado";
    // Login de usuário do Banco
    private String usuarioBanco = "root";
    // Senha de usuário do Banco
    private String senhaBanco = "123456";

    public boolean testaConexao() {
        boolean ret = false;

        Connection con = null;

        try {
            Class.forName(servidor);
            con = DriverManager.getConnection(urlBanco, usuarioBanco, senhaBanco);

            JOptionPane.showMessageDialog(null, "Conectado com sucesso!!", "Conexão", 0);
            ret = true;

        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de SQL", JOptionPane.ERROR_MESSAGE);
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, e.getMessage(), "Erro", JOptionPane.ERROR_MESSAGE);
            }
        }

        return ret;
    }

    public void Consultar(){
        Connection con = null;

        try {
            Class.forName(servidor);
            con = DriverManager.getConnection(urlBanco, usuarioBanco, senhaBanco);

            Statement stmt = con.createStatement();
            String sql = "select * from fornecedor";

            ResultSet rs = stmt.executeQuery(sql);

            String relacao = "Relação de fornecedores cadastrados \n";
            while (rs.next()) {
                relacao = relacao + "\n Cod  : " + rs.getString(1).toString() + " - Nome : " + rs.getString(2) +
                                    "CNPJ: " + rs.getString(3).toString() + " - Telefone : " + rs.getString(4);

            }
            rs.close();

            JOptionPane.showMessageDialog(null, relacao + "\n");

        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de SQL", JOptionPane.ERROR_MESSAGE);
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, e.getMessage(), "Erro", JOptionPane.ERROR_MESSAGE);
            }
        }
    }


public void Incluir(Fornecedor fncd) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName(servidor);
            con = DriverManager.getConnection(urlBanco, usuarioBanco, senhaBanco);

            String sql = "insert into fornecedores (codigo, nome, cnpj, telefone) "
                       + "values (?,?,?,?)";
            ps = con.prepareStatement(sql);
            ps.setInt(1, fncd.getCodigo());
            ps.setString(2, fncd.getNome());
            ps.setString(3, fncd.getCNPJ());
            ps.setString(4, fncd.getTelefone());
            ps.executeUpdate();

        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
        } finally {
            try {
                con.close();
                JOptionPane.showMessageDialog(null, "Inclusão realizada com sucesso!", "Confirmação", JOptionPane.INFORMATION_MESSAGE);
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
            }
        }
    }


public void Alterar(Fornecedor fncd) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName(servidor);
            con = DriverManager.getConnection(urlBanco, usuarioBanco, senhaBanco);
            String sql = "update usuarios set nome = ?,email = ?,tipo = ?,login = ?,senha = ? "
                              + "where codigo = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, fncd.getCodigo());
            ps.setString(2, fncd.getNome());
            ps.setString(3, fncd.getCNPJ());
            ps.setString(4, fncd.getTelefone());
            ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
            }
        }

    }
 public void Excluir (Fornecedor fncd) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName(servidor);
            con = DriverManager.getConnection(urlBanco, usuarioBanco, senhaBanco);

            String sql = "delete from fornecedores where codigo = ?";

            ps = con.prepareStatement(sql);
            ps.setString(1, Integer.toString(fncd.getCodigo()));
            ps.executeUpdate();

        } catch (ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, e.getMessage(), "Erro de Classe", JOptionPane.ERROR_MESSAGE);
            }
        }
    }

}