CONSULTAS AVANZADAS CON JDBC

CONSULTAS AVANZADAS UTILIZANDO VISTAS Y JDBC
En este artículo voy a resolver un caso donde ilustrare como desarrollar consultas avanzadas con VISTAS y JDBC.
PROYECTO PROPUESTO
Base de Datos
Se trata de una base de datos en SQL Server llamada EDUTEC.
En esta base de datos donde se registra los cursos que se programan por ciclo, cada mes corresponde a un nuevo ciclo de cursos de extensión profesional que ofrece EDUTEC.
También se registran las matrículas y sus respectivas notas.
El script para crear la base de datos lo encuentras en:
https://github.com/gcoronelc/databases
Requerimiento
La gerencia de EDUTEC necesita una aplicación que le permita consultar la cantidad de alumnos matriculados, los ingresos y pago al profesor de cada uno de los cursos programados en un determinado ciclo.
La interfaz de usuario sugerida es la siguiente:
ANÁLISIS DEL REQUERIMIENTO
Obtener Resumen de Datos
Para muchos casos en los que se requieren resúmenes de datos es mejor crear una vista, ya que los datos resumidos en ella pueden servir para muchos reportes o consultas.
Otra de las ventajas de las vistas es que ocultan toda la complejidad de la sentencia SELECT y también estandarizan los nombres de las columnas.
Para este caso se ha diseñado la siguiente vista:
CREATE VIEW V_RESUMEN_CURSO(
  PERIODO, CICLO, TARIFA, NOMTARIFA, CURSO, NOMCURSO, 
  HORAS, SECCIONES, VACTOTAL, VACDISP, MATRICULADOS, 
  PRECIO, PAGOHORA, INGRESOS, PAGOPROF, UTILIDAD
) AS
WITH V_PREVIA AS(
  SELECT 
    LEFT(IdCiclo,4) PERIODO,
    IdCiclo, IdCurso,
    COUNT(IDCURSOPROG) SECCIONES,
    SUM(Vacantes + Matriculados) VAC_TOTAL,
    SUM(Vacantes) VAC_DISP,
    SUM(Matriculados) MATRICULADOS,
    SUM(Matriculados * PreCursoProg) INGRESOS
  FROM DBO.CursoProgramado
  WHERE Activo = 1
  GROUP BY IdCiclo, IdCurso)
SELECT 
  V.PERIODO,V.IdCiclo, T.IdTarifa, T.Descripcion, C.IdCurso, C.NomCurso,
  T.Horas, V.SECCIONES, V.VAC_TOTAL, V.VAC_DISP, V.MATRICULADOS,
  T.PrecioVenta, T.PagoHora, V.INGRESOS, 
  (T.Horas * T.PagoHora * V.SECCIONES) PAGOPROF,
  (V.INGRESOS - (T.Horas * T.PagoHora * V.SECCIONES)) UTILIDAD
FROM DBO.Tarifa T
JOIN DBO.Curso C ON T.IdTarifa = C.IdTarifa
JOIN V_PREVIA V ON C.IdCurso = V.IdCurso
JOIN DBO.Ciclo CI ON V.IdCiclo = CI.IdCiclo;
GO
A continuación se tiene una prueba de su ejecución:
SELECT *
 FROM V_RESUMEN_CURSO
 WHERE CICLO = '2017-02'
 ORDER BY CURSO;
GO
Arquitectura de la Aplicación
Se trata de una arquitectura en capas, tal como se ilustra en la siguiente imagen.
Identificación de Servicios
Los servicios que se necesitan son:
  • leerPeriodos: Este servicio permite obtener la lista de todos los periodos.
  • leerCiclos: Este servicio permite obtener todos los ciclos de un período.
  • leerResumenCurso: Este servicio permite obtener el resumen de cada curso de un período.
Servicio: leerPeriodos
Este servicio retorna todos los periodos registrados en la base de datos, no recibe ningún parámetro.
Servicio: leerCiclos
Este servicio retorna todos los ciclos de un periodo.
Servicio: leerResumenCurso
Para este caso, el dato de entrada del servicio debe ser el ciclo, por ejemplo 2017-01, por cada mes se tiene un ciclo, quiere decir que en un año se tienen 12 ciclos.
El servicio se encarga de consultar la base de datos, en este caso debe hacer uso de la vista V_RESUMEN_CURSO.
El resultado lo retorna en una lista de objetos Map, en caso de que el ciclo no sea correcto, la lista estará vacía.
CODIGO FUENTE - EUREKA-CS-ORACLE-JDBC
En esta sección te presento un video de una aplicación CLIENTE-SERVIDOR desarrollada con Java y Oracle.
Tú tienes acceso al código fuente de esta aplicación, después del video tienes el enlace.
PROGRAMACIÓN
Acceso a la Base de Datos
La clase AccesoDB es la que proporciona el método getConnection que permite obtener un objeto de tipo Connection que permite el acceso a la base de datos.
package pe.egcc.app.db;

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

/**
 *
 * @author Gustavo Coronel
 * @blog gcoronelc.blogspot.com
 */
public final class AccesoDB {

  private AccesoDB() {
  }

  public static Connection getConnection() throws SQLException {
    Connection cn = null;
    
    try {
      // Datos Oracle
      String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
      String url = "jdbc:sqlserver://localhost:1433;databaseName=edutec";
      String user = "eureka";
      String pass = "admin";
      // Cargar el driver a memoria
      Class.forName(driver).newInstance();
      // Obtener el objeto Connection
      cn = DriverManager.getConnection(url, user, pass);
    } catch (SQLException e) {
      throw e;
    } catch (ClassNotFoundException e) {
      throw new SQLException("ERROR, no se encuentra el driver.");
    } catch (Exception e) {
      throw new SQLException("ERROR, no se tiene acceso al servidor.");
    }
    return cn;
  }
  
}
Capa Service
A continuación tienes la clase CursoService.
package pe.egcc.app.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import pe.egcc.app.db.AccesoDB;

public class CursoService {

  public List<Map<String, Object>> leerResumenCurso(String ciclo) {
    List<Map<String, Object>> lista = new ArrayList<>();
    Connection cn = null;
    try {
      cn = AccesoDB.getConnection();
      // Consulta
      String sql = "select ciclo, curso, nomcurso, secciones, "
              + "matriculados, ingresos, pagoprof, utilidad "
              + "from V_RESUMEN_CURSO "
              + "where ciclo = ?";
      PreparedStatement pstm = cn.prepareStatement(sql);
      pstm.setString(1, ciclo);
      ResultSet rs = pstm.executeQuery();
      // Convertir el rs en una lista
      while (rs.next()) {
        Map<String, Object> rec = new HashMap<>();
        rec.put("ciclo", rs.getString("ciclo"));
        rec.put("curso", rs.getString("curso"));
        rec.put("nomcurso", rs.getString("nomcurso"));
        rec.put("secciones", rs.getString("secciones"));
        rec.put("matriculados", rs.getDouble("matriculados"));
        rec.put("ingresos", rs.getInt("ingresos"));
        rec.put("pagoprof", rs.getDouble("pagoprof"));
        rec.put("utilidad", rs.getDouble("utilidad"));
        lista.add(rec);
      }
      rs.close();
      pstm.close();
    } catch (SQLException e) {
      throw new RuntimeException(e.getMessage());
    } catch (Exception e) {
      throw new RuntimeException("No se puede ejecutar la consulta");
    } finally {
      try {
        cn.close();
      } catch (Exception e) {
      }
    }
    return lista;
  }

  public List<String> leerPeriodos() {
    List<String> lista = new ArrayList<>();
    // Inicio de Proceso
    Connection cn = null;
    try {
      cn = AccesoDB.getConnection();
      String sql = "select distinct "
              + "left(idciclo,4) periodo "
              + "from ciclo order by 1 desc ";
      PreparedStatement pstm = cn.prepareStatement(sql);
      ResultSet rs = pstm.executeQuery();
      while (rs.next()) {
        lista.add(rs.getString("periodo"));
      }
      rs.close();
      pstm.close();
    } catch (SQLException e) {
      throw new RuntimeException(e.getMessage());
    } catch (Exception e) {
      throw new RuntimeException("No se puede ejecutar la consulta");
    } finally {
      try {
        cn.close();
      } catch (Exception e) {
      }
    }
    // Fin de Proceso
    return lista;
  }

  public List<String> leerCiclos(String periodo) {
    List<String> lista = new ArrayList<>();
    // Inicio de proceso
    Connection cn = null;
    try {
      cn = AccesoDB.getConnection();
      String sql = "select idciclo "
              + "from ciclo "
              + "where idciclo like concat(?,'%') "
              + "order by 1 desc";
      PreparedStatement pstm = cn.prepareStatement(sql);
      pstm.setString(1, periodo);
      ResultSet rs = pstm.executeQuery();
      while (rs.next()) {
        lista.add(rs.getString("idciclo"));
      }
      rs.close();
      pstm.close();
    } catch (SQLException e) {
      throw new RuntimeException(e.getMessage());
    } catch (Exception e) {
      throw new RuntimeException("No se puede ejecutar la consulta");
    } finally {
      try {
        cn.close();
      } catch (Exception e) {
      }
    }
    // Fin de proceso
    return lista;
  }

} 
Capa Controller
A continuación tienes la clase CursoController.
package pe.egcc.app.controller;

import java.util.List;
import java.util.Map;
import pe.egcc.app.service.CursoService;

/**
 *
 * @author Gustavo Coronel
 * @blog gcoronelc.blogspot.com
 * @email gcoronelc@gmail.com
 */
public class CursoController {

  private CursoService cursoService;

  public CursoController() {
    cursoService = new CursoService();
  }

  public List<String> leerPeriodos() {
    return cursoService.leerPeriodos();
  }

  public List<String> leerCiclos(String periodo) {
    return cursoService.leerCiclos(periodo);
  }

  public List<Map<String, Object>> leerResumenCurso(String ciclo) {
    return cursoService.leerResumenCurso(ciclo);
  }

}

Capa View
A continuación tienes el formulario:
Constructor del Formulario
A continuación se tiene el constructor del formulario:
public ConResumenCursoView() {
  initComponents();
  llenarPeriodos();
}

private void llenarPeriodos(){
  
  // Obtener periodos
  CursoController cursoController = new CursoController();
  List periodos = cursoController.leerPeriodos();
  
  // llenar el combo
  cboPeriodo.removeAllItems();
  for(String periodo: periodos){
    cboPeriodo.addItem(periodo);
  }
  cboPeriodo.setSelectedIndex(-1);
  
}
Control: cboPeriodo
A continuación se tiene la programación del combo cboPeriodo, cada vez que se selecciona un nuevo elemento se debe actualizar el combo cboCiclo:
private void cboPeriodoActionPerformed(java.awt.event.ActionEvent evt) {   
                                        
  // Limpiar combo de ciclos
  cboCiclo.removeAllItems();
  // Verificar periodo seleccionado
  int index = cboPeriodo.getSelectedIndex();
  if( index == -1 ) {
    return;
  }
  // Obtener periodo seleccionado
  String periodo = cboPeriodo.getSelectedItem().toString();
  // Traer Ciclos
  CursoController cursoController = new CursoController();
  List lista = cursoController.leerCiclos(periodo);
  // LLEnar combo de ciclos
  for(String ciclo: lista){
    cboCiclo.addItem(ciclo);
  }
  cboCiclo.setSelectedIndex(-1);

}
Control: btnConsultar
A continuación se tiene la programación del botón btnConsultar, en este caso se debe recoger el ciclo seleccionado en cboCiclo para poder ejecutar la consulta.
private void btnConsultarActionPerformed(java.awt.event.ActionEvent evt) {                                             
  // Limpiamos la tabla
  DefaultTableModel tabla;
  tabla = (DefaultTableModel) tblRepo.getModel();
  tabla.setRowCount(0);
  // Se verifica si hay un ciclo seleccionado
  if( cboCiclo.getSelectedIndex() == -1 ){
    return;
  }
  try {
    // Datos
    String ciclo = cboCiclo.getSelectedItem().toString();
    // Realizar consulta
    CursoController cursoController = new CursoController();
    List<Map<String,Object>> lista = cursoController.leerResumenCurso(ciclo);
    // Mostrar resultado
    for(Map<String,Object> rec: lista){
      Object[] rowData = {
        rec.get("ciclo"), rec.get("curso"), rec.get("nomcurso"), 
        rec.get("secciones"), rec.get("matriculados"),
        rec.get("ingresos"), rec.get("pagoprof"),rec.get("utilidad")
      };
      tabla.addRow(rowData);
    }
  } catch (Exception e) {
    JOptionPane.showMessageDialog(rootPane, e.getMessage(),
            "ERROR", JOptionPane.ERROR_MESSAGE);
  }
}
Finalmente, espero que este ejemplo te sea útil para entender la programación en capas y programación orientada a servicios.
CODIGO FUENTE - EUREKA-WEB-MYSQL-SPRING
En esta sección te presento un video de una aplicación web desarrollada SPRING FRAMEWORK y MYSQL.
Tú tienes acceso al código fuente de esta aplicación, después del video tienes el enlace.



ADOO - CASO ILUSTRATIVO

ANÁLISIS Y DISEÑO ORIENTADO A OBJETOS - CASO ILUSTRATIVO
En este artículo voy a resolver un caso donde explicare como aplicar el análisis orientado a objetos y la programación orientada a servicios.
PROYECTO PROPUESTO
La empresa STORE FOR MODELS necesita una aplicación para calcular el precio de venta de un producto, teniendo en cuenta el precio de costo, los gastos administrativos y la ganancia que se espera obtener.
Cuando la empresa compra un producto, lo que realiza es comprar un lote, y el precio que tiene es el precio del lote. Se estima que en un lote el 10% de productos se encuentre defectuoso, por lo tanto, solo el 90% podrá ser comercializado.
ANÁLISIS DEL REQUERIMIENTO
Julio Contreras, que se desempeña como analista funcional en la empresa, ha realizado el siguiente análisis de lo que debe ser el INPUT y OUTPUT del requerimiento. El análisis se ilustra con un ejemplo.
Los datos correspondientes al INPUT serían:
  • Nombre del producto: Pantalón de verano para damas
  • Tamaño del lote: 1000 Unidades
  • Precio del lote (Soles): 50,000.00
  • Porcentaje de gastos administrativos (%): 25
  • Porcentaje de ganancia por unidad (%): 200
Con estos datos se obtiene el siguiente OUTPUT:
  • Unidades a comercializar: 900
  • Gastos administrativos (Soles): 12,500.00
  • Costo total del lote (Soles): 62,500.00
  • Costo por unidad (Soles): 69.44 Soles
  • Ganancia por unidad (Soles): 138.88 Soles
  • Precio de Venta (Soles): 208.32 Soles
ARQUITECTURA DE LA APLICACIÓN
La arquitectura para desarrollar la aplicación será un desarrollo en capas:
Capa Service: Donde se tendrá el o los servicios necesarios para solucionar el requerimiento.
Capa View: La capa view es la interfaz de la aplicación con el usuario, sirve para mostrar datos al usuario y para solicitar datos al usuario.
Capa Controller: Esta capa es la responsable de gestionar las peticiones del usuario. Responde enviando a la vista los datos solicitados.
ANÁLISIS Y DISEÑO DEL SERVICIO
La mejor alternativa es encapsular los datos del INPUT y OUTPUT en un objeto, de esta manera se desarrolla un solo servicio para procesar todos los datos y obtener los resultados esperados.
A la clase que encapsule todos los datos le llamaremos LoteModel.
A continuación se tiene el diseño del servicio:
DIAGRAMA DE SECUENCIA
A continuación tienes en diagrama de secuencia del proceso normal:
CODIGO FUENTE - EUREKA-CS-ORACLE-JDBC
En esta sección te presento un video de una aplicación CLIENTE-SERVIDOR desarrollada con Java y Oracle.
Tú tienes acceso al código fuente de esta aplicación, después del video tienes el enlace.
ANÁLISIS DE LA CLASE MODEL
Se trata de una clase que encapsula los datos del INPUT y el OUTPUT.
A continuación se tiene el script de esta clase:
package pe.egcc.preciodeventa.model;

public class LoteModel {

  //  INPUT
  private String producto;
  private Integer tamanio;
  private Double precio;
  private Double porcGasAdm;
  private Double porcGanancia;

  // OUTPUT
  private Integer unidades;
  private Double gasAdm;
  private Double cosLote;
  private Double cosUni;
  private Double ganUni;
  private double preVenta;

  public LoteModel() {
  }

  public LoteModel(String producto, Integer tamanio, 
  Double precio, Double porcGasAdm, Double porcGanancia) {
    this.producto = producto;
    this.tamanio = tamanio;
    this.precio = precio;
    this.porcGasAdm = porcGasAdm;
    this.porcGanancia = porcGanancia;
  }

  public String getProducto() {
    return producto;
  }

  public void setProducto(String producto) {
    this.producto = producto;
  }

  public Integer getTamanio() {
    return tamanio;
  }

  public void setTamanio(Integer tamanio) {
    this.tamanio = tamanio;
  }

  public Double getPrecio() {
    return precio;
  }

  public void setPrecio(Double precio) {
    this.precio = precio;
  }

  public Double getPorcGasAdm() {
    return porcGasAdm;
  }

  public void setPorcGasAdm(Double porcGasAdm) {
    this.porcGasAdm = porcGasAdm;
  }

  public Double getPorcGanancia() {
    return porcGanancia;
  }

  public void setPorcGanancia(Double porcGanancia) {
    this.porcGanancia = porcGanancia;
  }

  public Integer getUnidades() {
    return unidades;
  }

  public void setUnidades(Integer unidades) {
    this.unidades = unidades;
  }

  public Double getGasAdm() {
    return gasAdm;
  }

  public void setGasAdm(Double gasAdm) {
    this.gasAdm = gasAdm;
  }

  public Double getCosLote() {
    return cosLote;
  }

  public void setCosLote(Double cosLote) {
    this.cosLote = cosLote;
  }

  public Double getCosUni() {
    return cosUni;
  }

  public void setCosUni(Double cosUni) {
    this.cosUni = cosUni;
  }

  public Double getGanUni() {
    return ganUni;
  }

  public void setGanUni(Double ganUni) {
    this.ganUni = ganUni;
  }

  public double getPreVenta() {
    return preVenta;
  }

  public void setPreVenta(double preVenta) {
    this.preVenta = preVenta;
  }

}
CAPA SERVICE
A continuación tienes la implementación de clase correspondiente a la capa de servicio:
package pe.egcc.preciodeventa.service;

import pe.egcc.preciodeventa.model.LoteModel;

public class LoteService {

  public LoteModel procesarLote(LoteModel bean) {
    // Variables
    Integer unidades;
    Double gasAdm, costoLote, cosUni, ganUni, preVenta;
    // Proceso
    unidades = (int) Math.round(bean.getTamanio() * 0.90) ;
    gasAdm = bean.getPrecio() * ( bean.getPorcGasAdm() / 100 );
    costoLote = bean.getPrecio() + gasAdm;
    cosUni = costoLote / unidades;
    ganUni = cosUni * (bean.getPorcGanancia() / 100.0);
    preVenta = cosUni + ganUni;
    // Preparar salida
    bean.setUnidades(unidades);
    bean.setGasAdm(dosDec(gasAdm));
    bean.setCosLote(dosDec(costoLote));
    bean.setCosUni(dosDec(cosUni));
    bean.setGanUni(dosDec(ganUni));
    bean.setPreVenta(dosDec(preVenta));
    // Retorno
    return bean;
  }
  
  private double dosDec(double valor){
    valor = Math.round( valor * 100.0 );
    valor = valor / 100.0;
    return valor;
  }
  
}
PRUEBA DEL SERVICIO
Es muy importante probar el servicio, de esta manera podemos hacer las correcciones si encontramos algún error.
La clase de prueba que he elaborado es la siguiente:
package pe.egcc.preciodeventa.prueba;

import pe.egcc.preciodeventa.model.LoteModel;
import pe.egcc.preciodeventa.service.LoteService;

public class Prueba01 {

  public static void main(String[] args) {
    
    // datos
    String producto = "Pantalón de verano para damas";
    int tamanio = 1000;
    double precio = 50000.0;
    double porcGasAdm = 25.0;
    double porcGanancia = 200.0;
    LoteModel bean = new LoteModel(producto, tamanio, precio, porcGasAdm, porcGanancia);
    
    // Proceso
    LoteService service = new LoteService();
    bean = service.procesarLote(bean);
    
    // Reporte
    System.out.println("Unidades a comercializar: " + bean.getUnidades());
    System.out.println("Gastos administrativos: " + bean.getGasAdm());
    System.out.println("Costo del lote: " + bean.getCosLote());
    System.out.println("Costo por unidad: " + bean.getCosUni());
    System.out.println("Ganancia por unidad: " + bean.getGanUni());
    System.out.println("Precio de venta: " + bean.getPreVenta());
    
  }

}
El resultado es el siguiente:
run-single:
Unidades a comercializar: 900
Gastos administrativos: 12500.0
Costo del lote: 62500.0
Costo por unidad: 69.44
Ganancia por unidad: 138.89
Precio de venta: 208.33
CAPA CONTROLLER
A continuación tienes el script de la clase controladora:
package pe.egcc.preciodeventa.controller;

import pe.egcc.preciodeventa.model.LoteModel;
import pe.egcc.preciodeventa.service.LoteService;

public class LoteController {

  public LoteModel procesarLote(LoteModel bean) {
    LoteService service = new LoteService();
    return service.procesarLote(bean);
  }
  
}
CAPA VIEW
A continuación tienes el formulario que se ha implementado:
Para programar el formulario ha sido necesario la implementación de dos métodos que sirven de soporte para su funcionamiento:
private void activarControles(boolean estado) {
  // Botones
  btnProcesar.setEnabled(estado);
  btnLimpiar.setEnabled(!estado);
  // Cuadros de texto
  txtPorcGana.setEnabled(estado);
  txtPorcGasAdm.setEnabled(estado);
  txtPrecio.setEnabled(estado);
  txtProducto.setEnabled(estado);
  txtTamanio.setEnabled(estado);
}

private void limpiarForm() {
  txtCosLote.setText("");
  txtCosUni.setText("");
  txtPorcGana.setText("");
  txtPorcGasAdm.setText("");
  txtPrecio.setText("");
  txtPreVenta.setText("");
  txtProducto.setText("");
  txtTamanio.setText("");
  txtUnidades.setText("");
  txtGanUni.setText("");
  txtGasAdm.setText("");
  txtProducto.requestFocus();
}
Programación del botón Procesar:
// datos
String producto = txtProducto.getText();
int tamanio = Integer.parseInt(txtTamanio.getText());
double precio = Double.parseDouble(txtPrecio.getText());
double porcGasAdm = Double.parseDouble(txtPorcGasAdm.getText());
double porcGanancia = Double.parseDouble(txtPorcGana.getText());
LoteModel bean = new LoteModel(producto, tamanio, precio, porcGasAdm, porcGanancia);
// Proceso
LoteController control = new LoteController();
bean = control.procesarLote(bean);
// Reporte
txtUnidades.setText("" + bean.getUnidades());
txtGasAdm.setText("" + bean.getGasAdm());
txtCosLote.setText("" + bean.getCosLote());
txtCosUni.setText("" + bean.getCosUni());
txtGanUni.setText("" + bean.getGanUni());
txtPreVenta.setText("" + bean.getPreVenta());
activarControles(false);
Programación del botón Limpiar:
activarControles(true);
limpiarForm();
Finalmente, espero que este ejemplo te sea útil para entender la programación en capas y programación orientada a servicios.
CODIGO FUENTE - EUREKA-WEB-MYSQL-SPRING
En esta sección te presento un video de una aplicación web desarrollada SPRING FRAMEWORK y MYSQL.
Tú tienes acceso al código fuente de esta aplicación, después del video tienes el enlace.