/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package paqueteDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import paqueteBL.Host;
import paqueteBL.HostExt;

public class HostDAO extends BaseDAO{

    public void readFromRS(Host host,ResultSet rs) throws Exception
    {
        host.setId(rs.getInt("idHost"));
        host.setIp(rs.getString("ip"));
        host.setNombre(rs.getString("nombre"));
        host.setPassword(rs.getString("password"));
        host.setUser(rs.getString("user"));
        host.setEstado(rs.getInt("estado"));
    }

    public HostExt read(int id) throws Exception
    {
        String sql="SELECT * FROM host WHERE idHost='" + id + "'";

        Statement scSQL = null;
        ResultSet rsListaRegistros;
        Connection co=null;
        try
        {
        co=getConexion();
        scSQL=co.createStatement();
        rsListaRegistros = scSQL.executeQuery(sql);
        if(rsListaRegistros.next())
        {
            HostExt host = new HostExt();
            readFromRS(host,rsListaRegistros);
            return host;
        }
        else
           return null;
        } finally
        {
            if(scSQL!=null) scSQL.close();
            if (co!=null) liberaConexion(co);
        }
    }

    public void updateEstado(int id,int estado) throws Exception
    {
        Statement scSQL = null;
        Connection co=null;
        try
        {
            co=getConexion();
            scSQL=co.createStatement();
            scSQL.executeUpdate("UPDATE host SET estado="+estado+" WHERE idhost="+id);
        } finally
        {
            if(scSQL!=null) scSQL.close();
            if (co!=null) liberaConexion(co);
        }
    }

    public List<HostExt> findByQuery(String sql) throws Exception
    {
        List<HostExt> lista=new ArrayList<HostExt>();
        ResultSet rsListaRegistros;
        Statement scSQL = null;
        Connection co=null;
        try
        {
            co=getConexion();
            scSQL=co.createStatement();

        rsListaRegistros = scSQL.executeQuery(sql);

        while(rsListaRegistros.next())
        {
            HostExt materia=new HostExt();
            readFromRS(materia,rsListaRegistros);
            lista.add(materia);
        }
        return lista;
        } finally
        {
            if(scSQL!=null) scSQL.close();
            if (co!=null) liberaConexion(co);
        }
    }

    public void update(Host host) throws Exception
    {
        Statement scSQL = null;
        Connection co=null;
        try
        {
            co=getConexion();
            scSQL=co.createStatement();
            String s=sets("","ip",host.getIp());
            s=sets(s,"nombre",host.getNombre());
            s=sets(s,"user",host.getUser());
            s=sets(s,"password",host.getPassword());
            System.out.println("UPDATE host SET "+s+" WHERE idhost="+host.getId());
            scSQL.executeUpdate("UPDATE host SET "+s+" WHERE idhost="+host.getId());
        } finally
        {
            if(scSQL!=null) scSQL.close();
            if (co!=null) liberaConexion(co);
        }
    }

   public void insert(Host host) throws Exception
    {
        Statement scSQL = null;
        ResultSet rsListaRegistros;
        Connection co=null;
        try
        {
            co=getConexion();
            scSQL=co.createStatement();
            String valores="(null,'"+host.getNombre()+"','"+host.getIp()+"','"+host.getUser()+"','"+host.getPassword()+"',"+Host.ESTADO_DISPONIBLE +")";
            System.out.println("INSERT INTO host (idhost,nombre,ip,user,password,estado) values "+valores);
            scSQL.executeUpdate("INSERT INTO host (idhost,nombre,ip,user,password,estado) values "+valores);
            rsListaRegistros = scSQL.executeQuery("SELECT LAST_INSERT_ID();");
            rsListaRegistros.next();
            host.setId(rsListaRegistros.getInt(1));
            System.out.println("ID en INSERT:"+host.getId());
        } finally
        {
            if(scSQL!=null) scSQL.close();
            if (co!=null) liberaConexion(co);
        }
    }

}
