public Conexion(){
}

public Connection getConecction(){
    try{
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        System.out.println("Conectando base de datos");
        Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@//localhost:1521/oracledtb", "misofertasdb", "123");
        return connection;
    }catch (Exception e){
        System.out.println("La excepcion es: "+e);
        return null;
    }
}
public CallableStatement conn(String procedure){
    try{
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        System.out.println("Conectando base de datos");
        Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@//localhost:1521/oracledtb", "misofertasdb", "123");
        CallableStatement stm = connection.prepareCall(procedure);
        return stm;
    }catch (Exception e){
        System.out.println("La excepcion es: "+e);
        return null;
    }
}
}

protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    /*Datos*/
    String username = request.getParameter("username");
    String password = request.getParameter("password");
    String nombre = "";
    String apellido = "";
    int idUsuario = 0;
    int idConsumidor = 0;
    Consumidor consumidor = new Consumidor();
    String procedure = "";
    procedure = "{call LOGIN (" + 1 + "," + username + "," + password + ")}";
    Conexion cn = new Conexion();
    CallableStatement cs = null;
    cs = cn.conn(procedure);
    try {
        cs.execute();
    } catch (SQLException ex) {
        Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
    }

    if (cs != null) {
        try {
            if (!cs.getString(1).isEmpty() && !cs.getString(2).isEmpty()
                    && 1 <= Integer.parseInt(cs.getBigDecimal(3).toString())
                    && 1 <= Integer.parseInt(cs.getBigDecimal(4).toString())) {
                nombre = cs.getString("nombre");
                password = cs.getString("ap_pat");
                idUsuario = Integer.parseInt(cs.getString("id_u"));
                idConsumidor = Integer.parseInt(cs.getString("id_c"));
                cn.getConecction().close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex);
        }
    }
        if (nombre != null && apellido != null && idUsuario >= 1 && idConsumidor >= 1) {
            consumidor.setNombres(nombre);
            consumidor.setApellidoPaterno(apellido);
            consumidor.setIdUsuario(usuarioFacade.find(BigDecimal.valueOf(idUsuario)));
            consumidor.setIdConsumidor(BigDecimal.valueOf(idConsumidor));
            HttpSession sesion = request.getSession();
            sesion.setAttribute("consumidor", consumidor);
            request.getRequestDispatcher("inicio.jsp")
                    .forward(request, response);


    } else {
        request.getRequestDispatcher("./login.jsp")
                .forward(request, response);
    }
}