pstm = connection.prepareStatement("SELECT * FROM usuarios");

package com.gmail.brunodiazmartin5.banco.mysql;

import java.sql.*;
import javax.swing.JOptionPane;

public class Conexion {

    public Connection connection;
    public ResultSet rs;
    public String bd, user, pass;
    public PreparedStatement pstm;

    public Conexion(String bd, String user, String pass){
        this.bd = bd;
        this.user = user;
        this.pass = pass;
    }

    public void conectar(){
        try{
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            connection = DriverManager.getConnection("jdbc:ucanaccess://"+bd, user, pass);
        }catch(ClassNotFoundException | SQLException ex){
            JOptionPane.showMessageDialog(null, ex.getMessage());
        }
    }

    public  boolean consultar(String user, String pass){
        boolean accepted = false;
        conectar();
        try {
            pstm = connection.prepareStatement("SELECT * FROM usuarios");

            rs = pstm.executeQuery();

            while(rs.next()){
                if(rs.getString("Usuario").equals(user) && rs.getString("Pass").equals(pass)){
                    accepted = true;
                }else{
                    accepted = false;
                }
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            e.printStackTrace();
        }

        desconectar();

        return accepted;
    }

    public String getPrivilegio(String user){
        String privilegio = "";

        try {
            pstm = connection.prepareStatement("SELECT Tipo FROM usuarios WHERE Usuario = ?");
            pstm.setString(1, user);
            rs = pstm.executeQuery();

            while(rs.next()){
                String priv = rs.getString("Tipo");

                switch(priv){
                case "admin":
                    privilegio = "admin";
                    break;
                case "user":
                    privilegio = "user";
                    break;
                case "cliente":
                    privilegio = "cliente";
                    break;
                }
            }

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

        return privilegio;
    }

    public ResultSet consultar(String dni){
        conectar();
        try {
            pstm = connection.prepareStatement("SELECT * FROM usuarios");
            rs = pstm.executeQuery();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

        desconectar();

        return rs;
    }

    public void insertar(String nombre, String apellidos, String dni, int saldo, String telefono, String telefono2){
        conectar();
        try {
            pstm  = connection.prepareStatement("INSERT INTO clientes VALUES (?, ?, ?, ?, ?, ?)");
            pstm.setString(1, nombre);
            pstm.setString(2, apellidos);
            pstm.setString(3, dni);
            pstm.setInt(4, saldo);
            pstm.setString(5, telefono);
            pstm.setString(6, telefono2);
            pstm.executeQuery();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

        desconectar();
    }

    public void insertarSaldo(String dni, float saldo){
        conectar();

        desconectar();
    }

    public void retirarSaldo(String dni, String dni2, float saldo){
        String dniOrigen = dni;
        String dniDestino = dni2;
        float saldoR = saldo, saldoA = 0F;

        conectar();

        try {
            pstm = connection.prepareStatement("SELECT * FROM clientes WHERE DNI = ?");
            pstm.setString(1, dniOrigen);
            rs = pstm.executeQuery();
            while(rs.next()){
                saldoA = rs.getInt("Saldo");
            }

            pstm = connection.prepareStatement("UPDATE clientes SET Saldo = ? WHERE DNI = ?");
            pstm.setFloat(1, saldoA - saldoR);
            pstm.setString(2, dniOrigen);
            rs = pstm.executeQuery();

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

        if(!dni2.equals(null)){
            try{
                pstm = connection.prepareStatement("SELECT * FROM clientes WHERE DNI = ?");
                pstm.setString(1, dniDestino);
                rs = pstm.executeQuery();
                while(rs.next()){
                    saldoA = rs.getInt("Saldo");
                }

                pstm = connection.prepareStatement("UPDATE clientes SET Saldo = ? WHERE DNI = ?");
                pstm.setFloat(1, saldoA + saldoR);
                pstm.setString(2, dniDestino);
                rs = pstm.executeQuery();

            }catch(SQLException e){
                JOptionPane.showMessageDialog(null, e.getMessage());
            }
        }

        desconectar();
    }

    public void eliminar(String dni){
        conectar();
        try{
            pstm = connection.prepareStatement("DELETE FROM clientes WHERE DNI = ?");
            pstm.setString(1, dni);
            pstm.executeQuery();
        }catch(SQLException e){
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

        desconectar();
    }

    public void desconectar(){
        try {
            rs.close();
            pstm.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}