/*
 * 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 BD;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author a35943
 */
public class ligacaoBD {
    public final static int NOT_OK=0;
    public final static int OK=1;
    public final static int CHAVE_DUPLICADA=2;
    private String bd;
    private Connection cn;
    private Statement st;
    private int estado;


    public ligacaoBD(String nomeBD){
        this.bd=nomeBD;
    }

    public void executeSQL(String sql){
        try {
            this.st.executeUpdate(sql);
            this.estado=OK;
        } catch (SQLException ex) {
            Logger.getLogger(ligacaoBD.class.getName()).log(Level.SEVERE, null, ex);
            //Verifica se chave está duplicada 1586= chave duplicada
            if(ex.getErrorCode()==1586)
                this.estado=CHAVE_DUPLICADA;
            else
                this.estado=NOT_OK;
        }
    }

    public ResultSet executaConsulta(String sql){

        ResultSet rs=null;
        try {
            rs=this.st.executeQuery(sql);
            this.estado=OK;
        } catch (SQLException ex) {
            Logger.getLogger(ligacaoBD.class.getName()).log(Level.SEVERE, null, ex);
            this.estado=NOT_OK;
        }
        return rs;
    }



    public String getTabelaClientes(){
        String tabela= "<table class=\"table table-striped\">\n" +
"              <thead>\n" +
"                <tr>\n" +
"                  <th>ID</th>\n" +
"                  <th>Nome</th>\n" +
"                  <th>Renda</th>\n" +
"                </tr>\n" +
"              </thead>\n" +
"              <tbody>\n";
        ResultSet rs = this.executaConsulta("Select * from clientes");
        try {
            while(rs.next()){
                tabela+="<tr>\n" +
                        "                    <td>"+rs.getInt("id")+"</td>\n" +
                        "                    <td>"+rs.getString("nome")+"</td>\n" +
                        "                    <td>"+rs.getDouble("renda")+"</td>\n" +
                        "                    </tr>\n";
            }
        } catch (SQLException ex) {
                Logger.getLogger(ligacaoBD.class.getName()).log(Level.SEVERE, null, ex);
            }
        tabela+="</tbody>\n" +
            "</table>";

        return tabela;
    }

    public void desligarBaseDados(){
        if(this.cn!=null){
            try {
                this.cn.close();
                this.estado=OK;
            } catch (SQLException ex) {
                Logger.getLogger(ligacaoBD.class.getName()).log(Level.SEVERE, null, ex);
                this.estado=NOT_OK;
            }
        }
    }


    public void ligarBaseDados(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            this.cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/"+this.bd,"root","root");
            this.st=cn.createStatement();
            this.estado=OK;
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(ligacaoBD.class.getName()).log(Level.SEVERE, null, ex);
            this.estado=NOT_OK;
        }
    }












    public int getEstado() {
        return estado;
    }

    public void setEstado(int estado) {
        this.estado = estado;
    }
}