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.
