package models;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MySQLConnection {

//mysql parameters
private static final String SERVER = "localhost";
private static final String DATABASE = "logistics";
private static final String USER = "root";
private static final String PASSWORD = "";

//get connection
public static Connection getConnection() {
    //connection
    Connection connection = null;
    try {
        //connection string
        String connectionString = "jdbc:mysql://" + SERVER + "/" + DATABASE + "?user=" + USER + "&serverTimezone=UTC";
        //add password to connection string if needed
        if (PASSWORD != "") {
            connectionString += "&password=" + PASSWORD;
        }
        //MySQL Java Drivers
        //Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        //MySQL connection
        connection = DriverManager.getConnection(connectionString);

    } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException ex) {
        System.out.println(ex.getMessage());

    }
    return connection;
}

public static ArrayList<TrailerBox> getAll() {
    ArrayList<TrailerBox> list = new ArrayList<>();
    PreparedStatement command;
    ResultSet result;
    String query = "select s.id, s.description, s.ipAddress from trailerboxes as s order by s.id";
    try {
        //prepare statement
        command = MySQLConnection.getConnection().prepareStatement(query);
        //execute query
        result = command.executeQuery();
        //read rows
        while (result.next()) {
            //read fields
            int id = result.getInt("id");
            String description = result.getString("description");
            String ipAddress = result.getString("ipAddress");
            //add new order to list
            list.add(new TrailerBox(id, description, ipAddress));
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    //return list
    return list;
}

} finally {
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException ignore) {
        }
    }
}

package Genericos;

 import Preferencias.Preferencias;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 import javax.swing.JOptionPane;

public class ConexionDB {
private static ConexionDB INSTANCE = null;
private static Connection dbcon;
private String host;
private String db;
private String user;
private String pass;

private static String msg;

public static enum TR {INICIAR, FINALIZAR, CANCELAR};

private ConexionDB() {
    if (INSTANCE == null) {
        if(procesar() == false){
            System.out.println(msg);
        }
    }
}

public static Connection getDBcon() {
    return dbcon;
}

public static ConexionDB getInstancia() {
    if (INSTANCE == null) {
        INSTANCE = new ConexionDB();
    }
    return INSTANCE;
}

public String getMsg() {
    return msg;
}

private boolean procesar(){
    if(datosTxt() == false){
        host = Preferencias.GET_IP();
        pass = Preferencias.GET_BD_PASS();
        db = Preferencias.GET_DB_NAME();
        user = Preferencias.GET_BD_USER();
    }
    return conectar();
}

private boolean datosTxt(){
    return false;
}

private boolean conectar(){
    String sCon = "jdbc:mysql://"+ host +"/"+ db;
    try{
       Class.forName("com.mysql.jdbc.Driver");

       dbcon = DriverManager.getConnection(sCon, user, pass);
       System.err.println("Parametros de Conexion....");
       System.err.println("host:"+host);
       System.err.println("db:"+db);
       System.err.println("user:"+user);
       System.err.println("pass:"+pass);
       if (dbcon != null) {
           msg = "Se conectó correctamente a la base de datos.";
           System.out.println(msg);
           return true;
       }else{
           msg = "Error al conectar a la base de datos.";

           JOptionPane.showMessageDialog(null,"Error al conectarse a la base de datos");

           return false;
       }
    } catch(ClassNotFoundException | SQLException e){
        msg = "Error driver! "+ e.getMessage();
        JOptionPane.showMessageDialog(null,"Error Al conectarse con el Sevidor:"+e.getMessage());
        return false;
    }
}

public static void cerrar(){
    try{
        dbcon.close();
    }catch(SQLException e){
        JOptionPane.showMessageDialog(null,"ERROR AL TERMINAR LA CONEXION");
    }
}

public static void Transaccion(TR accion){
    try {
        switch(accion){
            case INICIAR:
                dbcon.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                dbcon.setAutoCommit(false);
                break;
            case FINALIZAR:
                dbcon.commit();
                dbcon.setAutoCommit(true);
                break;
            case CANCELAR:
                dbcon.rollback();
                dbcon.setAutoCommit(true);
                break;
        }
    } catch (SQLException ex) {
    msg = "Error al establecer estado de transacciones.";
    JOptionPane.showMessageDialog(null,"Error al establecer estado de transacciones.:"+ex.getMessage());

    }
}

}

public class AccesoCTR {
private final Acceso acceso;
private PreparedStatement pst;
private ResultSet rst;
private String Sql;

public AccesoCTR(Acceso accesovm) {
  /*Abrimos la conexion en el Contructor de la clase que necesitamos usar*/
    ConexionDB.getInstancia();
}

/*Ejemplo de Select con la clase*/

 public boolean ValidarUsuarios(){
      try {

         Sql ="SELECT cod_usuario, usuario, cod_perfil,cod_funcionario FROM usuarios
             n" +
             "WHERE estado and usuario=? and clave=?";
        pst=ConexionDB.getDBcon().prepareStatement(Sql);
        pst.setString(1, acceso.txtusuario.getText().trim());
        pst.setString(2, acceso.txtclave.getText().trim());
        rst=pst.executeQuery();
        if (rst.next()) {
            Principal.permiso=rst.getInt("cod_perfil");
            VentaCTR.funcionario=rst.getInt("cod_funcionario");

            return true;
        }
      } catch (SQLException ex) {
        JOptionPane.showMessageDialog(null,"Error Al  iniciar Sesion " +ex);
        return false;
      }

      return false;
  }