Ada ha asignado un proyecto a María y a Juan. Se trata de un proyecto importante, puede suponer muchas ventas, y por tanto una gran expansión para la empresa.
En concreto, un notario de renombre en el panorama nacional, se dirigió a BK programación para pedirles que les desarrolle un programa para su notaría, de modo que toda la gestión de la misma, incluyendo la emisión de las escrituras, se informatizaran. Además, si el programa es satisfactorio, se encargará de promocionar la aplicación ante el resto de sus conocidos notarios, pudiendo por tanto suponer muchas ventas y por ello, dinero.
Una cuestión vital en la aplicación es el almacenamiento de los datos. Los datos de los clientes, y de las escrituras deberán guardarse en bases de datos, para su tratamiento y recuperación las veces que haga falta.
Como en BK programación trabajan sobre todo con Java, desde el primer momento Juan y María tienen claro que van a tener que utilizar bases de datos relacionales y JDBC y así lo comentan con Ada.
Casi todos los programas hoy día tienen la opción de guardar los datos con los que se trabaja.
Hasta ahora, ya conoces cómo abrir un archivo y utilizarlo como “almacén” para los datos que maneja tu aplicación. Utilizar un archivo para almacenar datos es la forma más sencilla de persistencia, porque en definitiva, la persistencia es hacer que los datos perduren en el tiempo.
Hay muchas formas de hacer los datos de una aplicación persistentes, y muchos niveles de persistencia. Cuando los datos de la aplicación solo están disponibles mientras la aplicación se está ejecutando, tenemos un nivel de persistencia muy bajo.
Es deseable que los datos de nuestra aplicación tengan el mayor nivel de persistencia posible.
Tendremos un mayor nivel de persistencia si los datos “sobreviven” a varias ejecuciones, o lo que es lo mismo, si nuestros datos se guardan y luego son reutilizables con posterioridad. Tendremos un nivel todavía mayor si “sobreviven” a varias versiones de la aplicación, es decir, si guardo los datos con la versión 1.0 de la aplicación y luego puedo utilizarlos cuando esté disponible la versión 2.0.
Ya hemos visto varias formas de hacer los datos de una aplicación persistentes, mediante ficheros, en una unidad anterior. Ahora, en esta unidad, vamos a ver la forma de almacenar los datos en una base de datos relacional.
José Javier Bermúdez Hernández. Uso educativo-nc. Elaboración propia.
Hoy en día, la mayoría de aplicaciones informáticas necesitan almacenar y gestionar gran cantidad de datos.
Esos datos, se suelen guardar en bases de datos relacionales, ya que éstas siguen siendo las más extendidas actualmente, aunque las bases de datos NoSQL están cada vez más presentes en el mercado.
Las bases de datos relacionales permiten organizar los datos en tablas y esas tablas y datos se relacionan mediante campos clave. Además se trabaja con el lenguaje estándar conocido como SQL, para poder realizar las consultas que deseemos a la base de datos.
Una base de datos relacional se puede definir de una manera simple como aquella que presenta la información en tablas con filas y columnas.
Una tabla es una serie de filas y columnas, en la que cada fila es un registro y cada columna es un campo. Un campo representa un dato de los elementos almacenados en la tabla (NSS, nombre, etc.). Cada registro representa un elemento de la tabla (el equipo Real Madrid, el equipo Real Murcia, etc.)
El sistema gestor de bases de datos, en inglés conocido como: Database Management System (DBMS), gestiona el modo en que los datos se almacenan, mantienen y recuperan.
En el caso de una base de datos relacional, el sistema gestor de base de datos se denomina: Relational Database Management System (RDBMS), que podríamos traducir por Sistema Gestor de Bases de Datos Relacionales.
Tradicionalmente, la programación de bases de datos ha sido como una Torre de Babel: gran cantidad de productos de bases de datos en el mercado, y cada uno "hablando" en su lenguaje privado con las aplicaciones.
El desfase objeto-relacional, también conocido como impedancia objeto-relacional, consiste en la diferencia de aspectos que existen entre la programación orientada a objetos y la base de datos. Estos aspectos se puede presentar en cuestiones como:
Lenguaje de programación: el programador debe conocer el lenguaje de programación orientada a objetos (POO) y el lenguaje de acceso a datos.
Tipos de datos: en las bases de datos relacionales siempre hay restricciones en cuanto a los tipos de datos que se pueden usar, que suelen ser sencillos, mientras que la programación orientada a objetos utiliza tipos de datos más complejos.
El modelo relacional trata con relaciones y conjuntos debido a su naturaleza matemática. Sin embargo, el modelo de programación orientada a objetos trata con objetos y las asociaciones entre ellos. Por esta razón, el problema entre estos dos modelos surge en el momento de querer hacer persistentes en la base de datos los objetos de negocio que maneja la aplicación.
La escritura (y de manera similar la lectura) mediante JDBC implica:
abrir una conexión,
crear una sentencia en SQL y
copiar todos los valores de las propiedades o atributos de un objeto en la sentencia,
ejecutarla y así almacenar el objeto.
Esto es sencillo para un caso simple, pero trabajoso si el objeto posee muchas propiedades, o bien si se necesita almacenar un objeto que a su vez posee una colección de otros elementos que también son objetos. Se necesita crear mucho más código, además del tedioso trabajo de creación de sentencias SQL.
Este problema es lo que denominábamos impedancia objeto-relacional, o sea, el conjunto de dificultades técnicas que surgen cuando una base de datos relacional se usa en asociación con un programa escrito en un lenguajes de Programación Orientada a Objetos.
Podemos poner como ejemplo de desfase objeto-relacional, un equipo de fútbol, que tenga un atributo que sea una colección de objetos de la clase Jugador. Cada jugador tiene un atributo "teléfono". Al transformar este caso a relacional se ocuparía más de una tabla para almacenar la información (la tabla de jugadores y la de teléfonos), implicando varias sentencias SQL y bastante código.
Si no has estudiado nunca bases de datos, ni tienes idea de qué es SQL o el modelo relacional, sería conveniente que te familiarizaras con él. A continuación te indicamos un tutorial bastante ameno sobre SQL y que describe brevemente el modelo relacional.
Juan está repasando la arquitectura y protocolos de JDBC, ya que desde que terminó el ciclo no había desempolvado los apuntes, y algunos conceptos los tiene olvidados. Sobre todo la parte teórica, pues la practica la emplea a diario en la empresa.
Así que aprovechando un hueco, está repasando sus apuntes, inmerso en arquitectura, protocolos,...
Inicialmente, cada empresa desarrolladora de un SGBD implementaba soluciones propietarias específicas para su sistema, pero pronto se dieron cuenta de que colaborando conjuntamente podían sacar mayor rendimiento y avanzar mucho más rápidamente.
Cada SGBD tiene su propia conexión y su propio API.
José JavierBermúdez Hernández. Uso educativo-nc.
La llegada de ODBC representó un avance sin precedentes en el camino hacia la interoperabilidad entre bases de datos y lenguajes de programación. La mayoría de empresas desarrolladoras de sistemas gestores de bases de datos incorporaron los drivers de conectividad a las utilidades de sus sistemas y los lenguajes de programación más importantes desarrollaron bibliotecas específicas para soportar el API ODBC.
Aunque la industria aceptó ODBC como medio principal para acceso a bases de datos en Windows, la verdad es queno se introduce bien en el mundo Java, debido a la complejidad que presenta ODBC, y que entre otras cosas ha impedido su transición fuera del entorno Windows.
La siguiente ilustración representa un sistema de conexión ODBC configurado usando diferentes controladores (drivers) y un API estándar.
José Javier Bermúdez Hernández. Uso educativo-nc.
JDBC (Java Database Connectivity) se trata de un API bastante similar a ODBC en cuanto a funcionalidad, implementado específicamente para usar con el lenguaje Java, adaptado a las especificidades de Java. Es decir, la funcionalidad se encuentra encapsulada en clases (ya que Java es un lenguaje orientado a objetos) y además, no depende de ninguna plataforma específica, de acuerdo con la característica multiplataforma defendida por Java. La idea en el desarrollo de JDBC era intentar ser tan sencillo como fuera posible, pero proporcionando a los desarrolladores la máxima flexibilidad.
Java, mediante JDBC, permite simplificar el acceso a bases de datos relacionales, proporcionando un lenguaje mediante el cual las aplicaciones pueden comunicarse con motores de bases de datos.
Sun desarrolló este API (java.sql.*) para el acceso a bases de datos, con tres objetivos principales:
Ser un API con soporte de SQL: poder construir sentencias SQL e insertarlas dentro de llamadas al API de Java.
Aprovechar la experiencia de los API's de bases de datos existentes.
Ser lo más sencillo posible.
Los desarrolladores de los sistemas gestores de bases de datos proporcionan la implementación de esa interfaz (drivers).
JDBC es similar en estructura a ODBC. Una aplicación JDBC está compuesta de varias capas, como se muestra en la figura:
José Javier Bermúdez Hernández. Uso educativo-nc.
Lacapa superioren este modelo es la aplicación Java. Las aplicaciones Java son portátiles: puede ejecutar una aplicación Java sin modificaciones en cualquier sistema que tenga instalado una maquina virtual java (Java Runtime Environment).
Una aplicación Java que utiliza JDBC puede comunicarse con muchas bases de datos con pocas modificaciones, si es que las hay. Al igual que ODBC, JDBC proporciona una manera consistente de conectarse a una base de datos, ejecutar comandos y recuperar los resultados. Al igual que ODBC, JDBC no impone un lenguaje de comando común: puede usar la sintaxis específica de Oracle cuando está conectado a un servidor Oracle y la sintaxis específica de MySQL cuando está conectado a un servidor MySQL.
La clase JDBC DriverManager es responsable de localizar un controlador JDBC que necesita la aplicación. Cuando una aplicación cliente solicita una conexión de base de datos, la solicitud se expresa en forma de una URL (Uniform Resource Locator). Una URL de JDBC es similar a las URL que utiliza con un navegador web. Para conectarnos a la base de datos Oracle, por ejemplo: jdbc:oracle:thin:ejemplo/ejemplo@localhost:1521:XE
A medida que cada controlador se carga en una Máquina Virtual de Java (JVM), se registra con el DriverManager JDBC.
Cuando una aplicación solicita una conexión, el DriverManager pregunta a cada controlador si puede conectarse a la base de datos especificada en la URL dada.
Tan pronto como encuentra un controlador adecuado, la búsqueda se detiene y el controlador intenta establecer una conexión con la base de datos. Si el intento de conexión falla, el controlador lanzará una SQLException a la aplicación. Si la conexión se completa con éxito, el controlador crea un objeto de conexión y lo devuelve a la aplicación.
El API JDBC soporta dos modelos de procesamiento para acceso a bases de datos: de dos y tres capas.
En el modelo de dos capas, una aplicación se comunica directamente a la fuente de datos. Esto necesita un conector JDBC que pueda comunicar con la fuente de datos específica a la que acceder.
Los comandos o instrucciones del usuario se envían a la base de datos y los resultados se devuelven al usuario. La fuente de datos puede estar ubicada en otra máquina a la que el usuario se conecte por red. A esto se denomina configuración cliente/servidor, con la máquina del usuario como cliente y la máquina que aloja los datos como servidor.
En el modelo de tres capas, los comandos se envían a una capa intermedia de servicios, la cual envía los comandos a la fuente de datos. La fuente de datos procesa los comandos y envía los resultados de vuelta la capa intermedia, desde la que luego se le envían al usuario.
La arquitectura o modelo de tres capas de JDBC es un diseño para separar las responsabilidades en el desarrollo de aplicaciones que interactúan con bases de datos. Este diseño mejora la mantenibilidad, escalabilidad y flexibilidad del código. Las tres capas principales en este enfoque son:
Capa de Presentación. Esta capa se encarga de la interacción entre el usuario y la aplicación. Proporciona la interfaz gráfica o de usuario, ya sea en forma de aplicación de escritorio, aplicación web, móvil, etcétera.
Responsabilidad: captura las entradas del usuario y muestra los resultados. Aquí es donde el usuario puede realizar acciones como buscar, insertar, eliminar o actualizar datos en la base de datos a través de la interfaz.
Ejemplo en JDBC: puede estar formada por JSP/Servlets en aplicaciones web, o Java Swing/JavaFX en aplicaciones de escritorio, que toman las solicitudes del usuario y las envían a la capa intermedia.
Capa Lógica de Negocio. Es la capa encargada de procesar las reglas de negocio, la lógica de la aplicación y la interacción entre la capa de presentación y la base de datos. También controla el flujo de los datos y decide cómo interactúan los distintos componentes de la aplicación.
Responsabilidad: aquí se realizan validaciones, cálculos, procesamiento de datos y la gestión de las transacciones. Esta capa recibe las solicitudes de la capa de presentación, las procesa y luego delega las operaciones a la capa de acceso a datos.
Ejemplo en JDBC: una clase de servicio en Java que contiene métodos como registrarCompra(), consultarClientes(), etcétera. Estos métodos se encargan de invocar las operaciones necesarias sobre la base de datos, aplicando las reglas de negocio.
Capa de Acceso a Datos o Persistencia. Esta capa se encarga de la comunicación directa con la base de datos. Aquí es donde se utilizan las funcionalidades de JDBC para realizar las operaciones CRUD (Crear, Leer, Actualizar, Eliminar) sobre la base de datos.
Responsabilidad: gestionar la conexión a la base de datos, ejecutar sentencias SQL y devolver los resultados a la capa de lógica de negocio. Aquí también se maneja la optimización de las consultas, la gestión de conexiones y las transacciones.
Ejemplo en JDBC: las clases que implementan operaciones JDBC para acceder a la base de datos. Estas clases gestionan la conexión a la base de datos, envían consultas SQL y procesan los resultados utilizando las interfaces de JDBC (Connection, Statement, ResultSet, etcétera).
Por tanto, el flujo de la información en el modelo de 3 Capas en JDBC es:
Capa de Presentación: el usuario realiza una acción (por ejemplo, registrar una compra).
Capa Lógica de Negocio: el controlador o la clase de servicio valida la solicitud, aplica las reglas de negocio, y llama a la capa de acceso a datos para interactuar con la base de datos.
Capa de Acceso a Datos: la capa de acceso a datos establece una conexión a la base de datos usando JDBC, ejecuta las consultas SQL necesarias y devuelve los resultados a la capa de lógica de negocio.
Capa de Lógica de Negocio: procesa los resultados y envía la respuesta a la capa de presentación.
Capa de Presentación: Muestra los resultados al usuario (por ejemplo, una confirmación de compra).
Modelo de diseño de software en el que las tareas se reparten entre los proveedores de recursos o servicios, llamados servidores, y los demandantes, llamados clientes. Un cliente realiza peticiones a otro programa, el servidor, quien le da la respuesta. Esta idea también se puede aplicar a programas que se ejecutan sobre una sola computadora, aunque es más ventajosa en un sistema operativo multiusuario distribuido a través de una red de computadoras.
ODBC se desarrolló para ser un estándar para el acceso a bases de datos en entornos Windows, y JDBC pretendió recoger esta idea para construir un API genérico independiente de la plataforma.
Un conector o driver es un conjunto de clases encargadas de implementar las interfaces del API y acceder a la base de datos.
Para poder conectarse a una base de datos y lanzar consultas, una aplicación necesita tener un conector adecuado. Un conector suele ser un fichero .jar que contiene una implementación de todas las interfaces del API JDBC.
Cuando se construye una aplicación de base de datos, JDBC oculta lo específico de cada base de datos, de modo que el programador se ocupe sólo de su aplicación.
El conector lo proporciona el fabricante de la base de datos o bien un tercero.
El API JDBC viene distribuido en dos paquetes:
java.sql, dentro de J2SE
javax.sql, extensión dentro de J2EE
Cuando se construye una aplicación de base de datos, JDBC oculta los detalles específicos de cada base de datos, de modo que al programar nos ocupemos sólo de nuestra aplicación.
El conector lo proporciona el fabricante de la base de datos o bien un tercero.
El código de nuestra aplicación no depende del driver, puesto que trabajamos contra los paquete java.sql y javax.sql.
JDBC ofrece las clases e interfaces para:
Establecer una conexión a una base de datos.
Ejecutar una consulta.
Procesar los resultados.
Ejemplo:
// Establece la conexión
Connection con = DriverManager.getConnection (
"jdbc:odbc:miBD", "miLogin", "miPassword");
// Ejecuta la consulta
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT nombre, edad FROM Jugadores");
// Procesa los resultados
while (rs.next()) {
String nombre = rs.getString("nombre");
int edad = rs.getInt(”edad");
}
En principio, todos los conectores deben ser compatibles con ANSI SQL-2 Entry Level (ANSI SQL-2 se refiere a los estándares adoptados por el American National Standards Institute (ANSI) en 1992. Entry Level se refiere a una lista específica de capacidades de SQL). Los desarrolladores de conectores pueden establecer que sus conectores conocen estos estándares.
Ministerio de Educación. Uso educativo-nc. Elaboración propia.
Existen cuatro tipos principales de conectores JDBC (Java Database Connectivity) que permiten la interacción entre aplicaciones Java y bases de datos:
Controlador JDBC-ODBC (Tipo 1): este usa un puente entre JDBC y ODBC, lo que permite a las aplicaciones Java acceder a bases de datos compatibles con ODBC. Se requiere la instalación de drivers ODBC en cada cliente. Es considerado obsoleto y solo recomendado para pruebas o cuando no hay otra opción disponible. Por ejemplo el Oracle JDBC-ODBC Bridge, este puente se usaba antiguamente para permitir que aplicaciones Java se conectaran a bases de datos usando ODBC, pero ya no se soporta en versiones modernas de Oracle debido a su baja eficiencia.
El middleware es un software con el que las diferentes aplicaciones se comunican entre sí. El middleware actúa como un puente entre tecnologías, herramientas y bases de datos diversas para que pueda integrarlas sin dificultad en un único sistema. Este sistema único provee un servicio unificado a sus usuarios. Por ejemplo, una aplicación frontend de Windows envía y recibe datos desde un servidor backend de Linux, pero los usuarios de la aplicación no están al tanto de la diferencia.
Consiste en gestionar las peticiones al sistema de la manera más equitativa posible, para evitar los denominados cuellos de botella.
Ministerio de Educucación. Uso educativo-nc. Elaboración propia.
Controlador API Nativa (Tipo 2): traduce las llamadas JDBC a API nativas de la base de datos (como C o C++). Este tipo de controlador ofrece mejor rendimiento que el tipo 1, pero es específico de cada base de datos y debe estar instalado en cada cliente, requiere bibliotecas específicas del sistema operativo. Como ejemplos: IBM DB2 Universal JDBC Driver (Tipo 2), este controlador se conecta directamente a bases de datos DB2 usando bibliotecas nativas específicas del sistema operativo, ofreciendo un rendimiento mejor que el Tipo 1 pero con la desventaja de estar "atado" a la plataforma. Otro ejemplo sería Oracle Call Interface (OCI).
Ministerio de Educación. Uso educativo-nc. Elaboración propia.
Controlador JDBC sobre Middleware (Tipo 3): utiliza un enfoque de tres capas, donde las aplicaciones Java se conectan a un servidor de middleware, que luego se comunica con la base de datos. Este controlador es flexible y puede funcionar con múltiples bases de datos, pero requiere configurar el middleware. Un ejemplo de este tipo sería: DataDirect SequeLink. Este driver está basado en servidor, por lo que no se necesita ninguna librería de base de datos en las máquinas clientes. Normalmente, un driver de tipo 3 proporciona soporte para balanceo de carga, funciones avanzadas de administrador de sistemas tales como auditoría, etcétera.
El middleware es un software con el que las diferentes aplicaciones se comunican entre sí. El middleware actúa como un puente entre tecnologías, herramientas y bases de datos diversas para que pueda integrarlas sin dificultad en un único sistema. Este sistema único provee un servicio unificado a sus usuarios. Por ejemplo, una aplicación frontend de Windows envía y recibe datos desde un servidor backend de Linux, pero los usuarios de la aplicación no están al tanto de la diferencia.
Consiste en gestionar las peticiones al sistema de la manera más equitativa posible, para evitar los denominados cuellos de botella.
Ministerio de Educación. Uso educativo-nc. Elaboración propia.
Controlador Java Puro (Tipo 4): este controlador se conecta directamente a la base de datos a través de sockets, sin necesidad de middleware ni software adicional en el cliente. Es el más eficiente y usado actualmente, siendo proporcionado generalmente por los proveedores de bases de datos, como el controlador MySQL Connector/J. Otro ejemplo de este tipo de conector es Oracle Thin. Como ventaja, por tanto, se tiene que no es necesaria traducción adicional o capa middleware, lo que mejora el rendimiento, siendo éste mejor que en el caso de los tipos 1 y 2.
Cada tipo tiene sus ventajas e inconvenientes, pero los controladores de tipo 3 y 4 son los más recomendados para aplicaciones modernas debido a su flexibilidad y rendimiento
Tras valorar prácticamente todas las opciones, comerciales y libres, existentes en el mercado, BK Programación se decanta en la mayoría de sus proyectos por el sistema de base de datos MySQL, que dispone de versiones comercial y gratuita.
MySQL ofrece herramientas gratuitas que permiten desarrollar y distribuir los desarrollos de la empresa, está disponible para Microsoft Windows, Linux e incluso Mac y permite trabajar con diferentes lenguajes de programación.
Juan y María están muy interesados en aprender a manejar este sistema, saben que MySQL es una de las herramientas más potentes en el mundo de las bases de datos y están dispuestos a afrontar el reto.
¿Qué es lo primero que tenemos que hacer, para poder realizar consultas en una base de datos?
Obviamente, instalar la base de datos. Dada la cantidad de productos de este tipo que hay en el mercado, es imposible explicar la instalación de todas. Así que vamos a optar por una, en concreto por MySQL, ya que es un sistema gestor gratuito y que funciona en varias plataformas.
Para instalar MySQL en Windows puedes seguir los pasos que te detallamos en el siguiente vídeo:
Si utilizas Linux, el sitio de descarga es el mismo que el indicado en la presentación anterior, y la instalación la puedes hacer con los pasos que te indican en el enlace:
Sobre PostgreSQL, puedes ver en este artículo que está lanzando su base de datos con prestaciones verdaderamente competentes respecto a los productos de Oracle:
Ministerio de Educación. Uso educativo-nc. Elaboración propia.
María, Ada y Juan han realizado concienzudamente el diseño de las tablas necesarias para la base de datos de una aplicación de notarías.
También se han decantado por el sistema gestor de bases de datos a utilizar. Emplearán un sistema gestor de bases de datos relacional. Una vez instalado el sistema gestor, tendrán que programar los accesos a la base de datos para guardar los datos, recuperarlos, realizar las consultas para los informes y documentos que sean necesarios, etc.
En Java podemos conectarnos y manipular bases de datos utilizando JDBC. Pero la creación en sí de la base de datos habitualmente vamos a hacerla con la herramienta específica para ello. Normalmente será el administrador de la base de datos, a través de las herramientas que proporcionan el sistema gestor, el que creará la base de datos. No todos los drivers JDBC soportan la creación de la base de datos mediante el lenguaje de definición de datos (DDL).
Veamos cómo crear paso a paso un esquema de base de datos con MySQL WorkBench, para usar en la aplicación de notarías, y una tabla, la de clientes. Lo puedes ver en la siguiente presentación:
¿Cómo le pedimos al Sistema Gestor de Bases de Datos Relacional (SGBDR), en concreto en este caso, al de MySQL, que nos proporcione la información que nos interesa de la base de datos?
Se utiliza el lenguaje SQL para interactuar con el SGBDR.
SQL es un lenguaje noprocedimental en el cual se le indica al SGBDR qué queremos obtener y no cómo hacerlo. El SGBDR analiza nuestra orden y si es correcta sintácticamente la ejecuta.
El estudio de SQL nos llevaría mucho más que una unidad, y es objeto de estudio en otros módulos de este ciclo formativo. Pero como resulta imprescindible para poder continuar, haremos una mínima introducción sobre él.
Los comandos SQL se pueden dividir en dos grandes grupos:
Los que se utilizan para definir las estructuras de datos, llamados comandos DDL (Data Definition Language).
Los que se utilizan para operar con los datos almacenados en las estructuras, llamados DML (Data Manipulation Language).
En el siguiente enlace encontrarás algunos de los comandos SQL más utilizados.
José Javier Bermúdez Hernánez. Uso educativo-nc. Elaboración propia.
La primera fase del trabajo con cualquier base de datos comienza con sentencias DDL, puesto que antes de poder almacenar y recuperar información debemos definir las estructuras donde agrupar la información. Las estructuras básicas con las que trabaja SQL son las tablas.
Como hemos visto antes, una tabla es un conjunto de celdas agrupadas en filas y columnas donde se almacenan elementos de información.
Antes de llevar a cabo la creación de una tabla conviene planificar:
nombre de la tabla,
nombre de cada columna,
tipo y tamaño de los datos almacenados en cada columna,
información adicional,
restricciones, etc.
Hay que tener en cuenta también ciertas restricciones en la formación de los nombres de las tablas: longitud. Normalmente, aunque dependen del sistema gestor, suele tener una longitud máxima de 30 caracteres, no puede haber nombres de tabla duplicados, deben comenzar con un carácter alfabético, permitir caracteres alfanuméricos y el guión bajo '_', y normalmente no se distingue entre mayúsculas y minúsculas.
Por ejemplo para crear una tabla de departamentos podríamos hacer:
CREATE TABLE departa (
cod_dep number(3),
nombre varchar2(15) not null,
loc varchar2(10),
constraint dep_pk primary key (cod_dep),
constraint dep_loc check
(loc in ('Madrid', 'Barcelona', 'Murcia'))
);
donde creamos la tabla con cod_dep como clave primaria. Además, se añade una restricción para comprobar que cuando se esté dando de alta un registro, lo que se escriba en el campo loc sea Madrid, Barcelona o Murcia.
Y una tabla de empleados, teniendo en cuenta el departamento en el que trabajen:
Veamos cómo sería un ejemplo de DDL para crear una tabla, concretamente la tabla PRODUCTO del esquema de la imagen, correspondiente a un mini punto de venta:
CREATE TABLE IF NOT EXISTS PRODUCTO(
ID BIGINT NOT NULL PRIMARY KEY auto_increment,
BARCODE VARCHAR(24) NOT NULL,
NOMBRE VARCHAR(200) NOT NULL,
PRECIO DOUBLE NOT NULL
);
En el ejemplo anterior creamos la tabla PRODUCTO, solo si no existe previamente, además el campo ID sería la clave primaria (PRIMARY KEY). El campo ID además es autoincremental, es decir, es una secuencia que cada vez que se inserte un registro se incrementará automáticamente, algo muy habitual en las bases de datos.
Pongamos ahora otro ejemplo, también relacionado con el mini punto de venta. En este caso, sería la tabla que contendría los tickets:
CREATE TABLE IF NOT EXISTS TICKET (
ID BIGINT NOT NULL PRIMARY KEY auto_increment,
FECHA DATE NOT NULL,
HORA TIME NOT NULL,
TICKETCERRADO BOOLEAN NOT NULL
);
En la tabla anterior, la clave primaria es igual que en la tabla PRODUCTO. Aparece información sobre la fecha y la hora del ticket, y un boolean que indicará si el ticket está cerrado o no.
Por último veamos como podría ser la última tabla del mini punto de venta, la tabla LINEATICKET:
CREATE TABLE IF NOT EXISTS LINEATICKET(
ID BIGINT NOT NULL PRIMARY KEY auto_increment,
CANTIDAD INTEGER NOT NULL,
PRECIOVENTA DOUBLE NOT NULL,
PRODUCTO_ID BIGINT,
TICKET_ID BIGINT,
FOREIGN KEY(PRODUCTO_ID) REFERENCES PUBLIC.PRODUCTO(ID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY(TICKET_ID) REFERENCES PUBLIC.TICKET(ID) ON UPDATE CASCADE ON DELETE CASCADE
);
La tabla LINEATICKET contiene cada una de las líneas del ticket; como ya sabes, un ticket puede contener varios productos. Los campos PRODUCTO_ID y TICKET_ID conectarán esta línea de ticket con el ticket al que pertenece y con el producto en cuestión.
Como habrás observado en las tablas anteriores, cada campo tiene asociado un tipo de dato: INTEGER, BIGINT, DATE, TIME, BOOLEAN, VARCHAR, etc. A la hora de almacenar dicha información en Java, tendrás que tener en cuenta las siguientes equivalencias:
El tipo de dato INTEGER en SQL equivaldría al tipo int en Java.
El tipo de dato BIGINT en SQL equivaldría al tipo long en Java.
El tipo de dato DOUBLE en SQL equivaldría al tipo double en Java.
El tipo de dato VARCHAR en SQL equivaldría al tipo String en Java, pero tienes que tener en cuenta que VARCHAR tiene un tamaño limitado, mientras que un String no. Por ejemplo, "VARCHAR(30)" sería una cadena de hasta 30 caracteres. Así, que a la hora de insertar datos en una base de datos relacional, debes tener en cuenta que la longitud del String en Javano debería superar al tamaño límite del VARCHAR en la base de datos.
El tipo de dato DATE en SQL equivaldría al tipo java.sql.Date (no debe usarse java.util.Date ni java.time.LocalDate). java.sql.Date puede convertirse rápidamente a java.time.LocalDate.
El tipo de dato TIME en SQL equivaldría al tipo java.sql.Time (no debe usarse java.util.Time ni java.time.LocalTime). Nuevamente, java.sql.Time puede convertirse rápidamente a java.time.LocalTime.
En la tabla PRODUCTO la clave primaria es el atributo ID, sin embargo, el código de barras parece una buena clave primaria. ¿Por qué crees que se ha decidido usar el atributo ID como clave primaria y no el código de barras?
En la tabla LINEATICKET se almacena el precio de venta del producto, pero dicho precio ya está en la tabla PRODUCTO. ¿Por qué crees que se duplica dicho valor?
En la siguiente página de la documentación de MySQL puedes ver una tabla completa de equivalencias de tipos de datos en la base de datos y tipos de datos Java:
Ministerio de Educación. Uso educativo-nc. Elaboración propia.
Tanto Juan como María saben que trabajar con bases de datos relacionales en Java es tremendamente sencillo, por lo que establecer una conexión desde un programa en Java, a una base de datos, es muy fácil.
Juan le comenta a María:
—Empleando la tecnología sólo necesitamos dos simples sentencias Java para conectar la aplicación a la base de datos. María, prepárate que en un periquete tengo lista la conexión con la base de datos y salimos a tomar un café.
Cuando queremos acceder a una base de datos para operar con ella, lo primero que hay que hacer es conectarse a dicha base de datos.
En Java, para establecer una conexión con una base de datos podemos utilizar el método getConnection() de la clase DriverManager. Este método recibe como parámetro la URL de JDBC que identifica a la base de datos con la que queremos realizar la conexión.
La ejecución de este método devuelve un objeto Connection que representa la conexión con la base de datos.
Cuando se presenta con una URL específica, DriverManager itera sobre la colección de drivers registrados hasta que uno de ellos reconoce la URL especificada. Si no se encuentra ningún driver adecuado, se lanza una SQLException.
Veamos un ejemplo comentado:
private static Statement stmt;
private static Connection con;
public static void main(String[] args) {
try {
// Cargar el driver de mysql
Class.forName("com.mysql.cj.jdbc.Driver");
// Cadena de conexión para conectar con MySQL en localhost,
//seleccionar la base de datos llamada ‘test’
// con usuario y contraseña del servidor de MySQL: root y admin
String connectionUrl = "jdbc:mysql://localhost/test?" +
"user=root&password=admin";
// Obtener la conexión
Connection con = DriverManager.getConnection(connectionUrl);
} catch (SQLException e) {
System.err.println("SQL Exception: "+ e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Excepción: "+ cE.toString());
}
}
Si probamos este ejemplo con NetBeans, o cualquier otro entorno, y no hemos instalado el conector para MySQL, en la consola obtendremos el mensaje: Excepción: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver.
En la siguiente presentación vamos a ver cómo descargarnos el conector o driver que necesitamos para trabajar con MySQL. Como verás, tan sólo consiste en descargar un archivo, descomprimirlo y desde NetBeans añadir el fichero .jar que constituye el conector que necesitamos.
Añadir el driver JDBC para MySQL en un proyecto de NetBeans
Recuerda que el conector JDBC es un componente que se intercala entre el programa Java y el Sistema Gestor de la Base de Datos (SGBD), y que implementa la funcionalidad necesaria para proporcionar comunicación entre la API JDBC y el SGBD.
A la hora de empezar a escribir código, ¿sabes si necesitas registrar el conector JDBC? Como se comentó en apartados anteriores, es necesario registrar el conector JDBC si estamos trabajando con una versión de JDBC anterior a la 4.0.
En la práctica, es fácil saber cuándo tienes que registrar el conector, dado que solo las versiones anteriores a 1.6 del JDK tienen una versión de JDBC inferior a la 4.0.
Solo tienes que registrar el conector si vas a usar un JDK anterior a 1.6. Versiones posteriores como JDK 1.7, JDK 8 y posteriores, no necesitan registrar el conector, pero como se verá a continuación es una buena práctica.
Para registrar el controlador primero, hay que consultar la documentación del conector que vamos a utilizar para conocer el nombre de la clase que hay que emplear, dado que cada conector es diferente.
En el caso del conector para MySQL la clase en cuestión es com.mysql.cj.jdbc.Driver. Se trata de una clase que implementa la interfaz java.sql.Driver definida por JDBC, y que es la interfaz que todo conector debe implementar. Dicha clase ya compilada se encontrará obviamente dentro de la librería .jar que instalamos en el paso anterior.
Las líneas de código necesarias para registrar el conector son tan sencillas como las siguientes:
boolean driverCargado=false;
String driver="com.mysql.cj.jdbc.Driver";try{
Class.forName(driver).newInstance();
driverCargado=true;}catch(ClassNotFoundException e){
err.printf("No se encuentra el driver de la base de datos (%s)\n",driver);}catch(InstantiationException ex){
err.printf("No se ha podido iniciar el driver de la base de datos (%s)\n",driver);}catch(IllegalAccessException ex){
err.printf("No se ha podido iniciar el driver de la base de datos (%s)\n",driver);}if(driverCargado){// podemos continuar, el driver se ha cargado correctamente}
Una vez cargado el conector, es posible hacer una conexión al SGBD. Fíjate que el proceso de carga puede producir hasta tres tipos de excepciones diferentes, hay que tener especial cuidado con eso, dado que el programa no debería continuar en caso de que dichas excepciones se produjeran.
Por último, toca hablar un poco de la distribución de la aplicación al cliente final. Los proyectos que hemos creado hasta ahora, donde hemos incluido la librería del conector al proyecto para poder desarrollar y probar nuestra aplicación, no generan proyectos listos para ejecutar en un cliente final. ¿Por qué? Porque el archivo .jar generado para nuestra aplicación no incluye en su interior el conector.
¿Y esto que significa? Significa que debemos copiar también el conector JDBC en la máquina del cliente como parte del proceso de instalación. Como parte del proceso de instalación debemos, o bien añadir al CLASSPATH la localización del archivo .jar del conector, o bien indicar a la hora de ejecutar la aplicación dónde está el archivo .jar del conector. Un ejemplo de esto último sería el siguiente:
Aunque el proceso de registro del conector no es necesario en muchos casos, es una buena práctica por dos motivos:
El primero es por asegurar compatibilidad con versiones anteriores del JDK, dado que en muchas ocasiones no sabemos qué versión del JDK se usará en el cliente final.
El segundo es para controlar situaciones como la anterior. Si al ejecutar la aplicación en el cliente no se encuentra el conector, porque no se ha configurado bien el CLASSPATH o porque al ejecutar la aplicación no se ha indicado donde está la librería del conector, podremos mostrar por pantalla un mensaje amigable y evitar así una incómoda excepción.
¿Cómo sería el código necesario para registrar el conector JDBC para la base de datos H2? Seguro que sabes hacerlo, consulta primero la documentación de H2 para hacerlo:
Acabamos de ver cómo se realiza una conexión a una base de datos. En ocasiones, sobre todo cuando se trabaja en el ámbito de las aplicaciones distribuidas, en entornos web, es recomendable gestionar las conexiones de otro modo.
Antes de ver en que consiste, veamos cómo funciona la creación de conexiones en una aplicación clásica de escritorio cliente-servidor y luego veamos los problemas de seguir con dicha estructura en una aplicación web.
Problemas en la creación de conexiones
En una aplicación de escritorio se crea una conexión de base de datos al iniciar la aplicación y se cierra al finalizar la aplicación. Es decir que cada usuario que inicia la aplicación tiene una conexión en exclusiva para él. Y obviamente sería imposible compartirlas ya que cada aplicación estará en un ordenador independiente.
En una aplicación webpodríamos seguir un esquema similar, en el que cada usuario nuevo que se conecta a nuestra aplicación se le crea una conexión y al salir de la aplicación que se cierre su conexión. Esto que aparentemente es sencillo tiene unos problemas debido a la diferente naturaleza de las aplicaciones de escritorio y las web.
Si siguiéramos el mismo patrón de creación de conexiones de aplicaciones de escritorio en aplicaciones web, acabaríamos con una cantidad enorme de conexiones activas (debido al gran número de usuarios) y con gran cantidad de conexiones abiertas sin usar (debido a usuarios que abandonan el portal y no lo hemos detectado).
Consecuencia de lo anterior, al tener tantas conexiones a la base de datos, se acabaría cayendo el servidor de base de datos debido a los recursos consumidos por todas las conexiones.
Podemos pensar que nuestro servidor puede soportar todas esas conexiones ya que podemos tener pocos usuarios, pero no suele ser así debido a:
Si no se cierran las conexiones, aun teniendo pocos usuarios, es probable que acabemos saturando al servidor de conexiones sin usar.
Las aplicaciones web pueden tener picos de mucho tráfico, donde sería normal que se excediera la capacidad de nuestro servidor.
Sería muy sencillo hacernos un ataque de denegación de servicio, saturando el servidor, haciendo que se crearan gran cantidad de conexiones que no se usen.
Una solución que nos evitaría las conexiones sin usar sería que se creara la conexión al iniciar cada petición web y se cerrara al finalizar dicha petición web. ¿El problema de eso? Crear y cerrar una conexión es muy costoso. Lo que tendríamos es una aplicación lentísima.
Pool de conexiones
El concepto de pool de conexiones se ha estandarizado desde la versión 3.0 de JDBC.
La solución del pool de conexiones tiene que solucionar los siguientes problemas:
No tener tantas conexiones como usuarios ya que el número de usuarios es demasiado elevado.
El servidor web tiene “n” conexiones ya creadas y conectadas a la base de datos (se llaman conexiones esperando).
Cuando llegan “m” peticiones web, la aplicación pide “m” conexiones al pool de conexiones, quedando esperando en el pool “n-m” conexiones. Esta operación es muy rápida ya que la conexión ya está creada y solo hay que marcarla como que alguien la está usando. Ahora hay “m” conexiones activas que está usando la aplicación.
Cuando las peticiones web finalizan, las conexiones no se cierran sino que se devuelven al pool indicándole que ya se han acabado de usar las conexiones. Ahora vuelve a quedar “n” conexiones esperando en el pool. Esta operación también es muy rápida ya que realmente no se cierra ninguna conexión sino que simplemente se marcan como que ya no las están usando nadie.
Si se piden más conexiones de las que hay esperando en el pool, se crearán en ese instante nuevas conexiones hasta el máximo de conexiones que permita el pool.
Al devolver una conexión al pool, ésta se queda esperando para que otra petición la pueda usar. Si hay ya demasiadas conexiones esperando a ser usadas, se cerrarán para ahorrar recursos en el servidor de base de datos.
¿Qué hemos conseguido con el pool?
Ahora las conexiones ya no se quedarán abiertas cuando el usuario se marcha del portal ya que cada conexión se pseudo-abre y pseudo-cierra con cada petición.
No tenemos tantas conexiones como usuarios usan la aplicación ya que solo se necesitan tantas como usuarios hay haciendo una petición en ese instante. Pensemos por un momento en Facebook. ¿Cuántos usuarios están conectados a Facebook? Supongamos “x”. Pero, ¿cuántos están realmente haciendo una petición y no viendo los datos que se han servido? Supongamos “y”. Obviamente “y” es mucho menor que “x”. Con lo que nos hemos ahorrado “x-y” conexiones.
Al iniciar un servidor Java EE, automáticamente elpool de conexiones crea un número de conexiones físicas iniciales.
Cuando un objeto Java del servidor J2EE necesita una conexión, la solicita a través del método <span>dataSource.getConnection()</span>, la fuente de datos <span>javax.sql.DataSource</span><span> </span>habla con el pool de conexiones y éste le entrega una conexión lógica <span>java.sql.Connection</span>. Esta conexión lógica la recibe por último, el objeto Java.
Cuando un objeto Java del servidor Java EE desea cerrar una conexión a través del método <span>connection.close()</span>, la fuente de datos javax.sql.DataSource habla con el pool de conexiones y le devuelve la conexión lógica en cuestión.
Si hay un pico en la demanda de conexiones a la base de datos, el pool de conexiones de forma transparente crea más conexiones físicas de objetos tipo <span>Connection</span>. Si por el contrario las conexiones a la base de datos disminuyen, el pool de conexiones, también de forma transparente elimina conexiones físicas de objetos de tipo <span>Connection</span>.
Es un conjunto de especificaciones y directrices para crear aplicaciones de nivel empresarial utilizando Java
Si no usamos la misma versión en los .jar nos puede dar un error en tiempo de ejecución.
Tenemos que descargar la versión correcta de jar y añadirlos al proyecto.
En la documentación de soporte de Oracle comenta que antes de la versión 12c (es decir, 12.1.0.1.0), UCP podía funcionar con cualquier versión del controlado JDBC de Oracle. Con el nuevo grupo, UCP 12.1.0.2, depende del controlador Oracle JDBC 12.1.0.2. Ejemplo: 12.2.0.1 ucp.jar requiere ojdbc8.jar con numeración 12.2.0.1.
La recomendación de Oracle es mantener los controladores ucp.jar y JDBC de la misma versión.
Ministerio de Educación. Uso educativo-nc. Elaboración propia.
Ada está echando una mano a Juan y María en la creación de consultas, para los informes que la aplicación de notaría debe aportar a los usuarios de la misma.
Hacer consultas es una de las facetas de la programación que más entretiene a Ada, le resulta muy ameno y fácil. Además, y dada la importancia del proyecto, cuanto antes avancen en él, mucho mejor.
Por suerte, los tres: Ada, María y Juan tienen experiencia en consultas SQL y saben que, cuando se hace una consulta a una base de datos, hay que afinar y hacerla lo más eficiente posible, pues si se descuidan el sistema gestor puede tardar mucho en devolver los resultados. Además, algunas consultas pueden devolver un conjunto de registros bastante grande, que puede resultar difícil de manejar desde el programa, ya que por norma general tendremos que manejar esos datos registro a registro.
Para operar con una base de datos ejecutando las consultas necesarias, nuestra aplicación deberá hacer las operaciones siguientes:
Cargar el conector necesario para comprender el protocolo que usa la base de datos en cuestión.
Establecer una conexión con la base de datos.
Enviar consultas SQL y procesar el resultado.
Liberar los recursos al terminar.
Gestionar los errores que se puedan producir.
Podemos utilizar los siguientes tipos de sentencias:
Statement: para sentencias sencillas en SQL.
PreparedStatement: para consultas preparadas, como por ejemplo las que tienen parámetros.
CallableStatement: para ejecutar procedimientos almacenados en la base de datos.
El API JDBC distingue dos tipos de consultas:
Consultas propiamente dichas: SELECT. Para las sentencias de consulta que obtienen datos de la base de datos, se emplea el método ResultSet executeQuery(String sql). El método de ejecución del comando SQL devuelve un objeto de tipo ResultSet que sirve para contener el resultado del comando SELECT, y que nos permitirá su procesamiento.
Actualizaciones: INSERT, UPDATE, DELETE, sentencias DDL. Para estas sentencias se utiliza el método executeUpdate(String sql).
Es común, cuando se habla de bases de datos y programación en un mismo contexto, escuchar la expresión "operaciones CRUD". Esta expresión equivale en español a "Crear, Leer, Actualizar y Borrar" y hace referencia a las cuatro operaciones básicas que necesita hacer nuestra aplicación para manejar la información:
Creación de datos (consultas tipo INSERT).
Lectura de datos (consultas tipo SELECT).
Actualización de datos (consultas tipo UPDATE).
Borrado de datos (consultas tipo DELETE).
A su vez, otro acrónimo muy común es DAO. Este hace referencia a componentes que usamos en nuestro software, como JDBC, para hacer independiente a nuestra aplicación de la base de datos o sistema de almacenamiento de información usado. La idea detrás de este concepto es que un cambio en el almacenamiento afecte lo mínimo posible a nuestra aplicación, minimizando el número de líneas a modificar.
En esa línea, se recomienda enormemente también crear clases específicas para gestionar las operaciones CRUD, de tal forma que dichas clases concentren las operaciones con la base de datos. Tradicionalmente, es común ver que este tipo de clases tienen la terminación DAO para así expresar su propósito, por ejemplo.
ProductosDAO podría ser una clase que agrupara las operaciones CRUD con productos.
Y ahora, ¿cómo rescatamos la información ya almacenada en la base de datos?
Supongo que ya lo imaginas, tendremos que ejecutar consultas tipo SELECT. Tendremos que preparar una cadena de texto que contenga la consulta SQL, y ejecutar dicha consulta.
Las consultas a la base de datos se realizan con sentencias SQL que van "embebidas" en otras sentencias especiales que son propias de Java. Por tanto, podemos decir que las consultas SQL las escribimos como parámetros de algunos métodos Java que reciben el String con el texto de la consulta SQL.
Las consultas tipo SELECT se lanzarán principalmente con el método executeQuery (aunque también se pueden lanzar con el método execute) de la clase Statement o PreparedStatement. En ambos casos se obtiene un ResultSet, que es una clase Java parecida a una lista en la que se aloja el resultado de la consulta. Cada elemento de la lista es uno de los registros de la base de datos (filas) que cumple con los requisitos de la consulta.
El ResultSet no contiene todos los datos, sino que los va obteniendo de la base de datos según se van pidiendo. La razón de esto es evitar que una consulta que devuelva una cantidad muy elevada de registros tarde mucho tiempo en obtenerse y sature la memoria del programa.
Con el ResultSet hay disponibles una serie de métodos que permiten movernos hacia delante y hacia atrás en las filas, y obtener la información de cada fila.
Por ejemplo, para obtener: nif, nombre, apellidos y telefono de los clientes que están almacenados en la tabla del mismo nombre, de la base de datos notarbd que se creó anteriormente, haríamos la siguiente consulta:
// Preparamos la consulta y la ejecutamos
Statement s = con.createStatement();
ResultSet rs = s.executeQuery ("SELECT NIF, NOMBRE,"
+ "APELLIDOS, TELÉFONO FROM CLIENTE");
El método next() del ResultSet hace que dicho puntero avance al siguiente registro. Si lo consigue, el método next() devuelve true. Si no lo consigue, porque no haya más registros que leer, entonces devuelve false.
Vamos a ver un ejemplo sencillo en el que se obtiene el id, el nombre y el precio de todos los productos almacenados en la base de datos:
public static void mostrarTodosLosProductos(Connection con) {
if (con != null) {
try ( Statement consulta = con.createStatement()) {
ResultSet resultados = consulta.executeQuery("SELECT id,nombre,precio FROM producto");
while (resultados.next()) {
long id = resultados.getLong("id");
String nombre = resultados.getString("nombre");
double precio = resultados.getDouble("precio");
System.out.printf("%5d %-15s %10.2f\n", id, nombre, precio);
}
} catch (SQLException ex) {
System.err.printf("Se ha producido un error al ejecutar la consulta SQL.");
}
}
}
La clase ResultSet usa internamente un cursor que permitirá ir accediendo a cada uno de los registros seleccionados. Inicialmente dicho cursor no apunta a ningún de registro.
En el ejemplo anterior, el método next() del ResultSet hace que dicho cursor avance al siguiente registro (o que se sitúe en el primer registro si es la primera invocación del método). Si lo consigue, el método next() devuelve true. Si no lo consigue, porque no haya más registros que leer, entonces devuelve false.
Para obtener cada una de las columnas de cada registro se utilizan los métodos get. A dichos métodos habrá que pasar el nombre del campo cuyo valor se desea obtener ("id", "nombre", "precio" , etc.) o la posición del campo (empezando su numeración en uno). Veamos algunos de los métodos get más importantes:
getDouble para obtener un campo cuyo valor es de tipo double.
getInt para obtener un campo cuyo valor es de tipo entero.
getString para obtener un campo cuyo valor es de tipo cadena de texto.
getDate para obtener un campo cuyo valor es de tipo fecha (java.sql.Date).
getTime para obtener un campo cuyo valor es de tipohora (java.sql.Time).
Un cursor está formado por un conjunto de registros devueltos por una instrucción SQL de tipo SELECT.
Las consultas preparadas están representadas por la clase PreparedStatement.
Son consultas precompiladas, por lo que son más eficientes, y pueden tener parámetros.
Una consulta se instancia del modo que vemos con un ejemplo:
PreparedStatement pstmt = con.preparedStatement("SELECT * from medicamentos");
Para las consultas que se realizan muy a menudo se aconseja usar este tipo de consultas, de modo que el rendimiento del sistema será mejor así.
Si hay que emplear parámetros en una consulta, se puede hacer usando el carácter ‘?’. Por ejemplo, para realizar una consulta de un medicamento que tenga un código determinado, haríamos la consulta siguiente:
PreparedStatement pstmt = con.preparedStatement("SELECT * from medicamentos WHERE codigo = ? ");
Establecemos los parámetros de una consulta utilizando métodos set que dependen del tipo SQL de la columna.
Así, le decimos que el primer parámetro, que es el único que tiene esta consulta, es “712786”:
pstmt.setString(1, "712786");
El primer argumento de este método es la posición del parámetro dentro de la consulta.
Finalmente, ejecutamos la consulta utilizando el método executeQuery() o executeUpdate(), ambos sin parámetros, dependiendo del tipo de consulta.
Como acabamos de ver, cunado se necesita realizar una consulta en la que se seleccionen solamente aquellos registros que cumplan unas determinadas condiciones, necesitamos una consulta paramétrica.
Supongamos que necesitamos obtener los productos de la base de datos que tienen un precio mayor a uno pasado como parámetro. Habría que emplear una consulta como la siguiente:
String query="SELECT id,nombre,precio FROM producto WHERE precio>=?";
El procedimiento de ejecución de la consulta anterior necesita hacer uso de las consultas tipo PreparedStatement, ya comentadas. Veamos un pequeño ejemplo donde se muestran por pantalla los productos cuyo precio es superior a uno dado por parámetro:
public static void mostrarProductosPrecioMinimo (Connection con, double precioMinimo)
{
String query="SELECT id,nombre,precio FROM producto WHERE precio>=?";
if (con!=null) {
try ( PreparedStatement consulta = con.prepareStatement(query)) {
consulta.setDouble(1, precioMinimo);
if (consulta.execute()) {
ResultSet resultados = consulta.getResultSet();
while (resultados.next()) {
long id = resultados.getLong("id");
String nombre = resultados.getString("nombre");
double precio = resultados.getDouble("precio");
System.out.printf("%5d %-15s %10.2f\n", id, nombre, precio);
}
}
} catch (SQLException ex) {
System.err.printf("Se ha producido un error al ejecutar la consulta SQL.");
}
}
}
Igual que en el caso de inserción de registros en la base de datos, aquí se utilizan los métodos set para establecer el valor de los parámetros de la consulta, en este caso: el método setDouble, puesto que el precio es de tipo DOUBLE en la tabla de la base de datos.
Cuando creamos una tabla podemos indicar que un campo debe ser NOT NULL. Si establecemos esa opción, dicho campo no podrá estar vacío en un registro.
Cuando un campo puede estar vacío, si no tiene valor, almacenará un valor conocido como NULL. Cuando contiene NULL, significará que en dicho campo no hay datos.
Un método interesante que tiene la clase ResultSet es wasNull(). Este método nos informa acerca de si el último valor leído con un método get fue NULL. En la siguiente página tienes un ejemplo de su uso:
¿Te has fijado en la tabla de tickets expuesta en apartados anteriores?
Los tickets tienen un campo BOOLEAN llamado TICKETCERRADO. La idea de ese campo es que sea solo true cuando el ticket se ha cerrado y si el ticket está cerrado, no podrán añadirse líneas de ticket al mismo.
¿Serías capaz de crear un método que muestre la lista de tickets que todavía no han sido cerrados?
En la literatura informática, a veces se habla de objetos contenedores refiriéndose a la definición de estructuras u objetos en el lenguaje de programación que se utilizarán para almacenar y representar los resultados de una consulta en memoria. En lenguajes orientados a objetos como Java, C# o Python, se crean clases y objetos que actúan como contenedores de los datos obtenidos de la consulta.
Ejemplos de objetos contenedores:
Clases POJO (Plain Old Java Object) en Java: al ejecutar una consulta en Java, normalmente se define una clase que representa una tabla o entidad de la base de datos, y se almacenan los datos recuperados en instancias de estas clases.
Listas o Colecciones: una vez obtenidos los datos en objetos individuales, se suelen almacenar en colecciones como List, Set, ArrayList, etc.
Diccionarios o Estructuras de Datos: en lenguajes como Python, se pueden usar diccionarios o listas de diccionarios para representar y almacenar el resultado de una consulta.
DTOs (Data Transfer Objects): clases que se utilizan para encapsular los datos y transferirlos entre capas de la aplicación.
Supón que tienes una tabla Empleado en una base de datos con los siguientes campos: id, nombre, salario y departamento. En Java, puedes definir una clase Empleado para actuar como contenedor de los resultados de una consulta SQL.
Definición de la Clase Contenedora:
public class Empleado {
private int id;
private String nombre;
private double salario;
private String departamento;
// Constructor, Getters y Setters
public Empleado(int id, String nombre, double salario, String departamento) {
this.id = id;
this.nombre = nombre;
this.salario = salario;
this.departamento = departamento;
}
public int getId() {
return id;
}
public String getNombre() {
return nombre;
}
public double getSalario() {
return salario;
}
public String getDepartamento() {
return departamento;
}
@Override
public String toString() {
return "Empleado{id=" + id + ", nombre='" + nombre + '\'' +
", salario=" + salario + ", departamento='" + departamento + '\'' + '}';
}
}
¿serías capaz de realizar la ejecución de la consulta y almacenanmiento del resultado?
José Javier Bermúdez Hernánez. Uso educativo-nc. Elaboración propia.
Respecto a las consultas de actualización, executeUpdate, retornan el número de registros insertados, registros actualizados o eliminados, dependiendo del tipo de consulta que se trate.
Supongamos que tenemos varios registros en una tabla Cliente, de la base de datos notarbd como la que vemos en la imagen. Si quisiéramos actualizar el teléfono del tercer registro, que tiene idCLIENTE=3 y ponerle como nuevo teléfono el 968610009 podríamos hacer:
String connectionUrl = "jdbc:mysql://localhost/notarbd?" +
"user=root&password=admin";
// Obtener la conexión
Connection con = DriverManager.getConnection(connectionUrl);
// Preparamos la consulta y la ejecutamos
Statement s = con.createStatement();
s.executeUpdate("UPDATE CLIENTE SET teléfono='968610009' WHERE idCLIENTE=3");
// Cerramos la conexión a la base de datos.
con.close();
Ya sabes cómo insertar registros en la base de datos y también cómo rescatarlos. Ahora toca otra operación muy común: la actualización de datos. ¿Crees que serías capaz de hacerlo?
Las consultas de actualización son las consultas tipo UPDATE y, tal y como se comentó en apartados anteriores, es necesario ejecutarlas usando el método executeUpdate. Ese método, como ya sabes, retorna el número de registros insertados, actualizados o eliminados dependiendo del tipo de consulta que se trate.
Imagina la siguiente consulta que cambia el precio de un producto concreto:
String query = "UPDATE producto SET precio=? WHERE id=?;";
Ejecutar la consulta anterior es similar a una inserción. Veamos cómo podría ser:
public static void actualizarPrecioProducto(Connection con, long id,double precio) {
String query = "UPDATE producto SET precio=? WHERE id=?;";
if (con != null) {
try ( PreparedStatement consulta = con.prepareStatement(query)) {
consulta.setDouble(1, precio);
consulta.setLong(2, id);
int registrosAfectados = consulta.executeUpdate();
if (registrosAfectados>0)
{
System.out.println("El precio del producto se ha modificado." + precio);
} else {
System.out.println("El precio del producto no se ha modificado, producto no encontrado.");
}
} catch (SQLException ex) {
System.err.printf("Se ha producio un error al ejecutar la consulta SQL.");
}
}
}
Fíjate en el uso del valor retornado por el método executeUpdate, gracias a dicho valor podemos determinar si la inserción se pudo realizar o no, dado que sabríamos el número de registros que han sido modificados.
Te proponemos ahora un pequeño ejercicio. Como ya sabes, los tickets tienen un campo llamado ticketcerrado que indica si al ticket se le pueden añadir más líneas de ticket o si por el contrario, ya no se le pueden añadir más. ¿Sabrías hacer un método que admitiera la conexión y el id del ticket y que pusiera ticketcerrado a true?
¿Cómo añadirías un registro a una tabla en la base de datos?
Por lo que sabemos hasta ahora, tendrás que crear una consulta INSERT INTO de SQL, usar un Statement o un PreparedStatement, y además debemos utilizar el método executeUpdate().
Debido a innumerables motivos, se aconseja usar un PreparedStatement, por lo que vamos a explicar en primer lugar su uso.
Un PreparedStatement necesita una consulta SQL que tenga parámetros, los parámetros se indican con "?":
La consulta anterior tendría 3 parámetros, y cada parámetro sería accesible por la posición que ocupa en la consulta, empezando a numerar el primero en uno (el primer "?" sería el parámetro número 1, el segundo "?" será el parámetro número 2, y así sucesivamente). Después se pueden reemplazar cómodamente con una serie de métodos que proporciona la clase PreparedStatement, veamos algunos de ellos:
setDouble (int pos, double value) para indicar el valor de un parámetro de tipo double.
setInt (int pos, int value) para indicar el valor de un parámetro tipo entero.
setString (int pos, String value) para indicar el valor de un parámeto tipo cadena de texto.
setDate (int pos, Date value) para indicar el valor de un parámetro tipo fecha (java.sql.Date).
setTime (int pos, Time value) para indicar el valor de un parámetro tipo hora (java.sql.Time).
Veamos cómo se podría ejecutar entonces la sentencia paramétrica anterior:
public static void nuevoProducto(Connection con, String nombre, String barcode, double precio) {
String query = "INSERT INTO producto (nombre, barcode, precio) VALUES (?,?,?)";
if (con != null) {
try ( PreparedStatement consulta = con.prepareStatement(query)) {
consulta.setString(1, nombre);
consulta.setString(2, barcode);
consulta.setDouble(3, precio);
int registrosAfectados = consulta.executeUpdate();
if (registrosAfectados > 0) {
System.out.println("Producto insertado correctamente.");
} else {
System.out.println("El producto no ha podido ser insertado.");
}
} catch (SQLException ex) {
System.err.printf("Se ha producido un error al ejecutar la consulta SQL.");
}
}
}
En el ejemplo anterior se muestra un método estático que recibe por parámetro la conexión (con) y los datos necesarios para almacenar el producto.
En muchas tablas, como es el caso de la tabla Producto usada en la consulta anterior, el identificador usado como clave primaria se genera de forma automática. Es algo común en muchas tablas. Si necesitas obtener el identificador asignado al registro recién insertado, puedes hacerlo con el siguiente método:
Siempre da un poco de miedo eliminar datos, pero hay veces que es necesario hacerlo.
Cuando nos interese eliminar registros de una tabla de una base de datos, emplearemos la sentencia SQL DELETE. Para eliminar registros de la base de datos recomendamos utilizar PreparedStatement, dado que lo más habitual es que haya que pasar algún parámetro a la consulta. Imagina que necesitamos eliminar un producto concreto de la base de datos, deberíamos utilizar una consulta similar a la siguiente:
String queryDelete = "DELETE FROM producto WHERE id=?";
En el código anterior se eliminaría un producto con un id interno concreto. En las sentencias de este tipo, normalmente, siempre se usa la clave primaria para especificar qué registro concreto se desea eliminar, y no eliminar otro por error.
Veamos un ejemplo de la ejecución de la sentencia anterior:
public static void borrarProducto(Connection con, long id) {
String queryDelete = "DELETE FROM producto WHERE id=?";
if (con != null) {
try ( PreparedStatement consultaDelete = con.prepareStatement(queryDelete)) {
consultaDelete.setLong(1, id);
int registrosAfectados = consultaDelete.executeUpdate();
if (registrosAfectados > 0) {
System.out.println("El producto ha sido eliminado correctamente");
} else {
System.out.println("El producto no ha sido eliminado, porque no existe.");
}
} catch (SQLException ex) {
System.err.printf("Se ha producio un error al ejecutar la consulta SQL.");
}
}
}
Fíjate que, tal y como ocurría en otros casos anteriores, aprovechamos el valor retornado por el método executeUpdate para saber si el registro se eliminó o no.
En realidad, un producto no debería borrarse si ya está siendo usado en alguna línea de algún ticket. Esto quiere decir, que si el id de producto aparece en algún registro de la tabla LINEATICKET, dicho producto no debería borrarse porque entonces crearía inconsistencia en la base de datos. ¿Sabrías modificar el código anterior para solucionar ese problema?
En apartados anteriores se ha indicado que era muy conveniente utilizar objetos PreparedStatement, es decir, sentencias "preparadas" o "parametrizadas" en lugar de objetos convencionales de tipo Statement. Recuerda que la diferencia fundamental es que en este último caso se construye una cadena de caracteres a partir de los elementos estructurales de la sentencia combinados con los valores que podrían haberse proporcionado desde la interfaz de usuario.
Se aconseja el uso de sentencias "preparadas" por lo siguiente:
las consultas preparadas mejoran el rendimiento, reduciendo el tiempo de análisis, pues la preparación de la consulta se realiza una sola vez;
los parámetros vinculados minimizan el ancho de banda del servidor, pues cada vez que se quiera ejecutar la sentencia solo se necesita enviar los parámetros y no el texto de la consulta completa;
desde el punto de vista de la seguridad, son muy útiles para defendernos frente a "inyecciones SQL".
En este enlace se describe detalladamente el funcionamiento y las ventajas del uso de este tipo de sentencias parametrizadas en lugar de que nuestro código construya la sentencia uniendo los distintos elementos en una cadena de caracteres:
En este otro enlace de la Wikipedia tienes una descripción básica del concepto de "inyección SQL", cuyos efectos debemos evitar por todos los medios en nuestro código si queremos que éste resulte seguro:
Las conexiones a una base de datos consumen muchos recursos en el sistema gestor de bases de datos y también en la misma memoria del sistema donde se ejecuta la aplicación.
Si realizamos un programa que realiza múltiples conexiones a la base de datos, y luego no las cerramos adecuadamente, estaremos ocupando memoria innecesariamente.
Por ello, conviene cerrar las conexiones con el método close() siempre que vayan a dejar de ser utilizadas, en lugar de esperar a que el recolector de basura de Java (garbage collector) las elimine.
Veamos un ejemplo de cómo cerrar una conexión a una base de datos de forma adecuada:
String url="jdbc:mysql://localhost/proyectobase?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8";
String usuario="prueba";
String password="prueba";
Connection con = null
try {
con = DriverManager.getConnection(url,usuario,password); <br />
/* Consultas a la base de datos. */
} catch (SQLException ex) {
err.printf("No se pudo conectar a la base de datos (%s)\n", dbname);
ex.printStackTrace();
} finally {
if (con!=null) con.close();
}
El código anterior puede ser reemplazado por un try-with-resources, tal y como se ha hecho en ejemplos anteriores. Esto tiene una gran ventaja, y es que nos libera de tener que invocar el método .close(), dado que esta estructura garantiza el cierre de recursos de forma automática y segura.
También es necesario cerrar las sentencias (Statement y PreparedStatement). De esa forma también liberamos memoria y recursos del sistema. No es un problema en programas que ejecutan pocas consultas, pero en programas que ejecutan miles o millones de consultas se convierte en un problema grave. Como hemos visto en ejemplos anteriores, con esas sentencias también se puede usar un try-with-resources, por lo que vamos a poner un ejemplo alternativo donde no se utiliza dicha estructura:
Statement consulta=null;
try {
consulta = con.createStatement();
if (consulta.execute("SELECT id,nombre,precio FROM producto")) {
ResultSet resultados = consulta.getResultSet();
while (resultados.next()) {
long id = resultados.getLong("id");
String nombre = resultados.getString("nombre");
double precio = resultados.getDouble("precio");
System.out.printf("%5d %-15s %10.2f\n", id, nombre, precio);
}
}
} catch (SQLException ex) {
System.err.printf("Se ha producio un error al ejecutar la consulta SQL.");
} finally {
if (consulta!=null)
consulta.close();
}
A veces, incluso es conveniente liberar los resultados (ResultSet). No obstante, cuando se cierra la consulta, también se liberan los ResultSet asociados.
En todas las aplicaciones en general, y por tanto en las que acceden a bases de datos en particular, nos puede ocurrir con frecuencia que la aplicación no funciona, no muestra los datos de la base de datos que deseábamos, etc.
Es importante capturar las excepciones que puedan ocurrir para que el programa no aborte de manera abrupta. Además, es conveniente tratarlas para que nos den información sobre si el problema es que se está intentando acceder a una base de datos que no existe, o que el servicio de la base de datos no está arrancado, o que se ha intentado hacer alguna operación no permitida sobre la base de datos, como acceder con un usuario y contraseña no registrados, ...
Cuando se produce un error se lanza una excepción del tipo java.sql.SQLException.
Es importante que las operaciones de acceso a base de datos estén dentro de un bloque try-catch que gestione las excepciones.
Los dos métodos más útiles de SQLException son el método getMessage(), que permite recoger y mostrar el mensaje de error que ha generado la base de datos (generalmente en inglés), y también el método getSQLState(), que permite obtener un código que identifica el error que se ha producido. También es útil el método getErrorCode(), el cual devuelve un número entero que representa el código de error asociado.
Aunque cada base de datos hace sus propias adaptaciones y sigue sus propios criterios, los códigos obtenidos por el método getSQLState() generalmente suelen seguir el convenio de los estándares SQL publicados por organizaciones como ISO y ANSI. En la siguiente página puedes ver los códigos de error que genera MySQL y su significado:
Te proponemos ahora un ejercicio un tanto más complicado que los anteriores. En la tabla LINEATICKET se almacenan las diferentes líneas de ticket de un ticket, y se utilizan dos atributos para determinar a qué ticket pertenece la línea de ticket y el producto al que refiere la línea de ticket.
Échale un vistazo al siguiente método encargado de insertar una nueva línea de ticket en la tabla LINEATICKET:
publicstaticlongnuevaLineaTicket(Connection con,int cantidad,double precio,long idproducto,long idticket){
String query ="INSERT INTO LINEATICKET (CANTIDAD,PRECIOVENTA,PRODUCTO_ID,TICKET_ID) VALUES (?,?,?,?);";long id=-1;if(con != null){try( PreparedStatement consulta = con.prepareStatement(query,
Statement.RETURN_GENERATED_KEYS)){
consulta.setInt(1, cantidad);
consulta.setDouble(2, precio);
consulta.setLong(3, idproducto);
consulta.setLong(4, idticket);int registrosAfectados = consulta.executeUpdate();if(registrosAfectados>0){
ResultSet m=consulta.getGeneratedKeys();if(m.next()){
id=m.getLong(1);
System.out.printf("LineaTicket creada con ID=%d\n",id);}}else{
System.out.println("La línea de ticket no ha podido ser creada.");}}catch(SQLException ex){
System.err.printf("Se ha producio un error al ejecutar la consulta SQL.\n");
System.err.printf("SQLState: %s\n",ex.getSQLState());
System.err.printf("SQLError: %s\n",ex.getMessage());}}return id;}
El código anterior permite insertar una nueva línea de ticket en la base de datos. La consulta anterior puede funcionar, pero puede que falle si el id del ticket o el id del producto al que está asociada la línea de ticket anterior no existe. Esto provocaría un mensaje en consola como el siguiente:
Se ha producio un error al ejecutar la consulta SQL.
SQLState:23506
SQLError:Violación de una restricción de Integridad Referencial: "CONSTRAINT_B5: PUBLIC.LINEATICKET FOREIGN KEY(PRODUCTO_ID) REFERENCES PUBLIC.PRODUCTO(ID) (1000)"
Referential integrity constraint violation: "CONSTRAINT_B5: PUBLIC.LINEATICKET FOREIGN KEY(PRODUCTO_ID) REFERENCES PUBLIC.PRODUCTO(ID) (1000)"; SQL statement:
INSERT INTO LINEATICKET (CANTIDAD,PRECIOVENTA,PRODUCTO_ID,TICKET_ID) VALUES (?,?,?,?); [23506-197]
Si te fijas en el valor generado por getSQLState y lo buscas en la documentación de la base de datos H2, comprobarás que se trata de un error perfectamente tipificado, sobre el cual podemos actuar. ¿Podrías modificar el código anterior para que indique exactamente el error que se ha producido?
7.- Ejecución de procedimientos almacenados en la base de datos
Ministerio de Educación. Uso educativo-nc. Elaboración propia.
Ada está terminando de diseñar unos procedimientos almacenados para un proyecto que está realizando la empresa, para unos grandes almacenes. En esos grandes almacenes utilizan MySQL como base de datos, puesto que ese sistema gestor soporta la ejecución de dichos procedimientos. Ada está pensando en pedir ayuda a alguien más de la empresa, porque se está dando cuenta de que habrá que realizar bastante código para toda la funcionalidad que se necesita.
Un procedimiento almacenado es un procedimiento o subprograma que está almacenado en la base de datos.
Muchos sistemas gestores de bases de datos los soportan, por ejemplo: MySQL, Oracle, etc.
Además, estos procedimientos suelen ser de dos clases:
Procedimientos almacenados.
Funciones, las cuales devuelven un valor que se puede emplear en otras sentencias SQL.
Un procedimiento almacenado típico tiene:
Un nombre.
Una lista de parámetros.
Unas sentencias SQL.
Veamos un ejemplo de sentencia para crear un procedimiento almacenado sencillo para MySQL, aunque sería similar en otros sistemas gestores:
CREATE PROCEDURE procediprueba /* nombre del procedimiento */
(IN par1 INTEGER) /* parámetros */
BEGIN /* Inicio del bloque */
DECLARE var1 CHAR(13); /* Declarar variables */
IF par1 = 24 THEN /* Inicio del IF */
SET var1 = 'perro rabioso'; /* asignar valor a la variable */
ELSE
SET var1 = 'gato persa'; /* asignar valor a la variable */
END IF; /* Fin del IF */
INSERT INTO Animales VALUES (var1); /* insertar registro */
END /* fin de bloque */
Como se ve en los comentarios, este procedimiento admite un parámetro, llamado par1. También se declara una variable a la que llamamos var1 y es de tipo carácter y longitud 13. Si el valor que le llega de parámetro es igual a 24, entonces se asigna a la variable var1, la cadena 'perro rabioso' y en caso contrario se le asignará la cadena: 'gato persa'. Finalmente, se inserta en la tabla “Animales” el valor que se asignó a la variable var1.
En el capítulo 19 del manual de referencia de MySQL, que puedes encontrar en el documento siguiente, puedes familiarizarte con los comandos que puedes necesitar para realizar procedimientos almacenados y funciones:
A continuación, vamos a realizar un procedimiento almacenado en MySQL, que simplemente insertará datos en la tabla clientes. Desde el programa Java que realizamos, llamaremos para ejecutar a ese procedimiento almacenado. Por tanto, ¿cuál sería la secuencia que seguiríamos para realizar esto?
Si no tenemos creado ni el esquema ni la tabla de clientes, la creamos. Crearemos una base de datos empresa, por simplicidad en el usuario root, así, desde MySQL Workbench ejecutaríamos:
CREATE SCHEMA `empresa` ;
CREATE TABLE clientes (
codigo INT PRIMARY KEY, -- Clave primaria, identificador único para cada cliente
nombre VARCHAR(100) NOT NULL, -- Nombre del cliente, campo obligatorio
telefono VARCHAR(15) -- Teléfono del cliente
);
Creamos el procedimiento almacenado en la base de datos. Sería tan fácil como lo que ves aquí:
DELIMITER $$ ;
CREATE PROCEDURE insertarCliente (IN p_nombre VARCHAR(100), IN p_telefono VARCHAR(15), IN p_codigo INT)
BEGIN
INSERT INTO clientes (codigo, nombre, telefono) VALUES (p_codigo, p_nombre, p_telefono) ;
END;
DELIMITER ;
En MySQL, como acabas de ver en el código anterior, el comando DELIMITER $$ se utiliza para cambiar el delimitador de las declaraciones SQL temporales. Por defecto, MySQL usa el punto y coma (;) como delimitador. Sin embargo, cuando se crean procedimientos almacenados, funciones o triggers, es común que estos contengan múltiples declaraciones SQL, cada una terminada con un punto y coma.
Al cambiar el delimitador, como a $$, puedes incluir múltiples declaraciones dentro del cuerpo del procedimiento sin que MySQL interprete cada punto y coma como el final de la instrucción. Después de definir el procedimiento, puedes volver a establecer el delimitador a su valor original.
Crear la clase Java para desde aquí, llamar al procedimiento almacenado:
package mysqlcallable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* Ejemplo de uso de un procedimiento almacenado.
*
* @author IES Aguadulce
*/
public class MySQLCallable {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
try {
// Cargar el driver de mysql
Class.forName("com.mysql.cj.jdbc.Driver");
// Cadena de conexión para conectar con MySQL en localhost,
//seleccionar la base de datos llamada ‘empresa’
// con usuario y contraseña del servidor de MySQL: root y admin
String connectionUrl = "jdbc:mysql://localhost/empresa?" +
"user=root&password=admin";
// Obtener la conexión
Connection con = DriverManager.getConnection(connectionUrl);
// El procedimiento almacenado tendrá tres parámetros
CallableStatement prcProcedimientoAlmacenado =
con.prepareCall("{ call insertarCliente(?, ?,?) }");
// cargar parametros en el procedimiento almacenado
prcProcedimientoAlmacenado.setInt("p_codigo", 765);
prcProcedimientoAlmacenado.setString("p_nombre", "Antonio Pérez") ;
prcProcedimientoAlmacenado.setString("p_telefono", "950121314") ;
// ejecutar el procedimiento
prcProcedimientoAlmacenado.execute();
} catch (SQLException e) {
System.out.println("SQL Exception: "+ e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Exceción: "+ cE.toString());
}
}
}
Si ejecutamos ese código, podremos comprobar en la base de datos que efectivamente se ha insertado el registro:
José Javier Bermúdez Hernández. Uso educativo-nc
Si hemos definido la tabla correctamente, con su clave primaria, y ejecutamos el programa, intentando insertar una fila igual que otra insertada, o sea, con la misma clave primaria, obtendremos un mensaje al capturar la excepción de este tipo:
SQL Exception: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '765' for key 'clientes.PRIMARY'
Te recomendamos que veas la documentación que hay en la siguiente dirección. Tienes ejemplos JDBC para diversas finalidades: listar datos de una base de datos, llamar a funciones en Oracle, etc.
En los procedimientos almacenados encontramos también la estructura condicional.
En el siguiente listado puedes observar el uso de esta estructura. Se trata de un condicional IF para verificar si un cliente ya existe en la base de datos. Si el cliente ya existe, actualiza su nombre y teléfono; si no existe, lo inserta.
En MySQL WorkBench escribiremos y ejecutaremos:
DELIMITER $$
CREATE PROCEDURE guardarCliente (
IN p_codigo INT,
IN p_nombre VARCHAR(100),
IN p_telefono VARCHAR(15)
)
BEGIN
-- Verificar si el cliente ya existe
IF EXISTS (SELECT 1 FROM clientes WHERE codigo = p_codigo) THEN
-- Si el cliente existe, se actualizan sus datos
UPDATE clientes
SET nombre = p_nombre, telefono = p_telefono
WHERE codigo = p_codigo;
ELSE
-- Si el cliente no existe, se inserta uno nuevo
INSERT INTO clientes (codigo, nombre, telefono)
VALUES (p_codigo, p_nombre, p_telefono);
END IF;
END $$
DELIMITER ;
En los procedimientos almacenados de MySQL podemos usar bucles. Por ejemplo, podemos usarlos para recorrer un cursor.
Como recordarás del módulo de bases de datos, un cursor no es más que una estructura que almacena el conjunto de filas devuelto por una consulta a la base de datos. A continuación puedes ver un procedimiento almacenado para listar los clientes de la tabla clientes de nuestra base de datos empresa. Observa cómo se declara un cursor y cómo se recorre mediante un bucle. El siguiente código lo ejecutaríamos en MySQL Workbench.
DELIMITER $$
CREATE PROCEDURE listarClientes()
BEGIN
-- Declarar variables para almacenar los resultados
DECLARE v_codigo INT;
DECLARE v_nombre VARCHAR(100);
DECLARE v_telefono VARCHAR(15);
DECLARE fin INT DEFAULT 0;
-- Declarar el cursor para seleccionar los campos del cliente
DECLARE cur CURSOR FOR
SELECT codigo, nombre, telefono FROM clientes;
-- Manejar el final de los resultados del cursor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1;
-- Abrir el cursor
OPEN cur;
-- Bucle WHILE para iterar sobre los registros de la tabla 'clientes'
WHILE fin = 0 DO
-- Obtener el siguiente valor del cursor
FETCH cur INTO v_codigo, v_nombre, v_telefono;
-- Verificar si se ha llegado al final
IF fin = 0 THEN
-- Aquí imprimimos los detalles
SELECT v_codigo AS Codigo, v_nombre AS Nombre, v_telefono AS Telefono;
END IF;
END WHILE;
-- Cerrar el cursor
CLOSE cur;
END $$
DELIMITER ;
Podríamos crear esta clase para probar el funcionamiento:
package mysqlcallable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Clase que empleando un procedimiento almacenado en MySQL, lista los clientes
* de la tabla clientes de la base de datos empresa.
*
* @author IES Aguadulce
*/
public class ListarProcedimientoMySQL {
public static void main(String[] args) {
// Datos de conexión a la base de datos
String jdbcUrl = "jdbc:mysql://localhost:3306/empresa";
String username = "root";
String password = "admin";
try {
// Establecer conexión con la base de datos
Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
// Preparar la llamada al procedimiento almacenado
CallableStatement stmt = conn.prepareCall("{CALL listarClientes()}");
// Ejecutar el procedimiento
ResultSet rs = stmt.executeQuery() ;
// Procesar los resultados
while (rs.next()) {
// Obtener los datos del registro
String codigo = rs.getString("Codigo");
String nombreCliente = rs.getString("Nombre");
String telefono = rs.getString("Telefono");
// Mostrar por consola los datos del registro actual
System.out.println("Cliente: " + codigo + " - nombre: "+ nombreCliente +
" - Teléfono: " + telefono);
}
// Cerrar el CallableStatement y la conexión
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Y obtendremos una salida similar a la siguiente, suponiendo que tenemos insertados tres registros con esos datos:
Cliente: 761 - nombre: José Cano - Teléfono: 950999888
Cliente: 764 - nombre: Felipe Mesones - Teléfono: 968610023
Cliente: 765 - nombre: Antonio Pérez - Teléfono: 950121314
Fíjate en el siguiente código de PL-SQL en Oracle, observarás que con Oracle podemos hacer un bucle controlando la salida del mismo con %FOUND lo cual no es posible usar en MySQL.
En MySQL podemos crear una función que devuelva un valor basado en una consulta o cálculo. Así, dado el esquema de la tabla clientes, una función podría devolver, por ejemplo, el teléfono del cliente basado en su código o algún valor condicional como "Tlf. no registrado" si el cliente no existe.
En Workbench definimos la función:
DELIMITER $$
CREATE FUNCTION `obtenerTelefonoCliente`(p_codigo INT) RETURNS varchar(20)
DETERMINISTIC
BEGIN
DECLARE v_telefono VARCHAR(20);
-- Buscar el teléfono del cliente
SELECT telefono INTO v_telefono
FROM clientes
WHERE codigo = p_codigo;
-- Si el teléfono es NULL, devolver "Teléfono no registrado"
IF v_telefono IS NULL THEN
RETURN 'Tlf. no registrado';
ELSE
RETURN v_telefono;
END IF;
END $$
DELIMITER ;
En Java, la forma de probar el funcionamiento de la función de este modo:
// Código de cliente para el cual queremos obtener el nombre
int codigoCliente = 765;
// Preparar la llamada a la función con un CallableStatement
CallableStatement stmt = con.prepareCall("{? = CALL obtenerTelefonoCliente(?)}");
// Registrar el primer parámetro como el valor de retorno (el teléfono del cliente)
stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
// Establecer el parámetro de entrada (el código del cliente)
stmt.setInt(2, codigoCliente);
// Ejecutar la llamada a la función
stmt.execute();
// Obtener el valor de retorno (teléfono del cliente)
String tlfCliente = stmt.getString(1);
System.out.println("Teléfono del cliente con código " + codigoCliente + ": " + tlfCliente);
La ejecución nos dirá:
Teléfono del cliente con código 765: 950121314
Si probamos con un código que no exista en la base de datos:
Teléfono del cliente con código 23: Tlf. no registrado
En MySQL, la palabra clave DETERMINISTIC en la definición de una función indica si la función siempre devuelve el mismo resultado para los mismos valores de entrada, lo cual es fundamental para que el motor de MySQL pueda optimizar la ejecución de consultas que utilicen la función.
DETERMINISTIC: significa que la función siempre devolverá el mismo resultado cuando se le den los mismos parámetros de entrada. Es decir, la función no depende de variables externas (como el estado del sistema, la hora actual, o consultas a otras tablas cuyos datos pueden cambiar).
NOT DETERMINISTIC: indica que la función puede devolver resultados diferentes incluso si recibe los mismos parámetros de entrada. Por ejemplo, una función que utiliza la fecha/hora actual o que depende de datos que pueden cambiar entre llamadas, como una consulta a una tabla que se actualiza con frecuencia, sería considerada NOT DETERMINISTIC.
Ejemplo de función NOT DETERMINISTIC, devuelve un valor diferente cada vez que se invoca, ya que depende de la fecha y hora actual:
CREATE FUNCTION obtenerFechaActual ()
RETURNS DATETIME
NOT DETERMINISTIC
BEGIN
RETURN NOW();
END;
En MySQL (desde la versión 5, de 2005), al igual que en Oracle (desde la versión 6, de 1988), podemos definir disparadores. Los disparadores consisten en código que permite automatizar tareas en respuesta a eventos específicos. Así, un trigger desencadena determinadas acciones de forma automática en las tablas de la base de datos cuando se intenta insertar, modificar o añadir nuevos datos.
El objetivo principal de los disparadores es aumentar la seguridad e integridad de la información. Esto se consigue gracias a la programación de restricciones o requerimientos de verificación que permiten minimizar los errores.
Por ejemplo, supongamos una tabla empleado con una columna salario. Si queremos que se controle automáticamente que cuando se vaya a actualizar el salario de un empleado, si se intenta almacenar un número negativo en dicho campo salario, no se permita y en su lugar se almacene un 0, entonces podemos definir un trigger desde MySQL Workbench así:
DELIMITER $$
CREATE TRIGGER trigger_check_salario_before_update
BEFORE UPDATE ON empleado
FOR EACH ROW
BEGIN
IF NEW.salario < 0 THEN
SET NEW.salario = 0;
END IF;
END$$
DELIMITER ;
En el documento que tienes a continuación se ven con más detenimiento los disparadores en MySQL. Es importante notar que en MySQL sólo hay disparadores de fila, mientras que en Oracle PL-SQL se dispone de disparadores tanto de fila como de sentencia.
Ministerio de Educación. Uso educativo-nc. Elaboración propia.
Hay un aspecto sobre bases de datos, que Ana estudió en el ciclo formativo, y que no había tenido ocasión de ver en un caso real, y es el de las transacciones en una base de datos. Es un tema que le apasiona y le pide a María que le muestre alguna que haya realizado ella, para estudiarla a fondo y aprender con sus consejos.
Cuando tenemos una serie de consultas SQL que deben ejecutarse en conjunto, con el uso de transacciones podemos asegurarnos de que nunca nos quedaremos a medio camino de su ejecución.
Las transacciones tienen la característica de poder “deshacer” los cambios efectuados en las tablas, de una transacción dada, si no se han podido realizar todas las operaciones que forman parte de dicha transacción.
Por eso, las bases de datos que soportan transacciones son mucho más seguras y fáciles de recuperar si se produce algún fallo en el servidor que almacena la base de datos, ya que las consultas se ejecutan o no en su totalidad.
Al ejecutar una transacción, el motor de base de datos garantiza: atomicidad, consistencia, aislamiento y durabilidad (ACID) de la transacción (o conjunto de comandos) que se utilice.
El ejemplo típico que se pone para hacer más clara la necesidad de transacciones en algunos casos es el de una transacción bancaria. Por ejemplo, si una cantidad de dinero es transferida de la cuenta de Antonio a la cuenta de Pedro, se necesitarían dos consultas:
En la cuenta de Antonio para quitar de su cuenta ese dinero:
UPDATE cuentas SET saldo = saldo - cantidad WHERE cliente = "Antonio";
En la cuenta de Pedro para añadir ese dinero a su cuenta:
UPDATE cuentas SET saldo = saldo + cantidad WHERE cliente = "Pedro";
Pero, ¿qué ocurre si por algún imprevisto (un apagón de luz, etc.), el sistema “cae” después de que se ejecute la primera consulta, y antes de que se ejecute la segunda? Antonio tendrá una cantidad de dinero menos en su cuenta y creerá que ha realizado la transferencia. Pedro, sin embargo, creerá que todavía no le han realizado la transferencia.
Una transacción tiene dos finales posibles: COMMIT o ROLLBACK. Si se finaliza correctamente y sin problemas se hará con COMMIT, con lo que los cambios se realizan en la base de datos, y si por alguna razón hay un fallo, se deshacen los cambios efectuados hasta ese momento, con la ejecución de ROLLBACK.
En MySQL, por defecto, las conexiones trabajan con el modo autocommit activado (es decir, autocommit = TRUE). Esto significa que, por defecto, cada consulta de modificación (como INSERT, UPDATE, DELETE) se ejecuta y se confirma automáticamente (se hace un COMMIT inmediatamente después de ejecutar la consulta). Si se desea desactivar el autocommit, habría que hacerlo explícitamente con:
SET autocommit = 0;
Cuando el autocommit está desactivado, las transacciones se deben gestionar manualmente utilizando START TRANSACTION, COMMIT y ROLLBACK.
En en, el comportamiento es diferente. Oracleno utiliza el modo autocommit por defecto. Las transacciones en en deben confirmarse explícitamente usando COMMIT o pueden deshacerse usando ROLLBACK. en trata cada conjunto de consultas como parte de una transacción hasta que se emite un COMMIT o ROLLBACK. Sin embargo, algunas herramientas o interfaces de clientes que se conectan a en (como SQL*Plus) pueden tener su propio modo autocommit activado por defecto, lo cual varía según la configuración del cliente.
Imaginemos que tienes dos tablas: cuentas y transacciones. Queremos realizar una transferencia de dinero de una cuenta a otra. Si la transferencia no se puede completar (por ejemplo, si no hay suficiente saldo), debemos deshacer todas las operaciones relacionadas.
Primero, creamos con MySQL Workbench una nueva base de datos, podemos llamarla banco. Creamos las siguientes tablas en la base de datos:
CREATE DATABASE `banco` ;
-- Crear la tabla 'cuentas'
CREATE TABLE IF NOT EXISTS cuentas (
id INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
saldo DECIMAL(10, 2) NOT NULL
);
-- Crear la tabla 'transacciones'
CREATE TABLE IF NOT EXISTS transacciones (
id INT AUTO_INCREMENT PRIMARY KEY,
cuenta_origen INT NOT NULL,
cuenta_destino INT NOT NULL,
monto DECIMAL(10, 2) NOT NULL,
fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (cuenta_origen) REFERENCES cuentas(id),
FOREIGN KEY (cuenta_destino) REFERENCES cuentas(id)
);
Insertar datos de prueba. Insertamos algunos datos en la tabla cuentas para simular las transferencias.
-- Insertar cuentas de ejemplo
INSERT INTO cuentas (nombre, saldo) VALUES ('Juan', 1000.00);
INSERT INTO cuentas (nombre, saldo) VALUES ('María', 500.00);
INSERT INTO cuentas (nombre, saldo) VALUES ('Antonio', 1200.00);
INSERT INTO cuentas (nombre, saldo) VALUES ('Eva', 600.00);
Crear un procedimiento almacenado que realice la transferencia de dinero y utilice transacciones para asegurarse de que todo se complete correctamente.
DELIMITER $$
CREATE PROCEDURE realizarTransferencia(
IN p_cuentaOrigen INT,
IN p_cuentaDestino INT,
IN p_monto DECIMAL(10, 2)
)
BEGIN
-- Declarar variables
DECLARE v_saldoOrigen DECIMAL(10, 2);
-- Iniciar la transacción
START TRANSACTION;
-- Obtener el saldo de la cuenta de origen
SELECT saldo INTO v_saldoOrigen FROM cuentas WHERE id = p_cuentaOrigen;
-- Verificar si hay suficiente saldo
IF v_saldoOrigen < p_monto THEN
-- Si no hay suficiente saldo, deshacer la transacción
ROLLBACK;
SELECT 'Transferencia fallida: saldo insuficiente.' AS resultado;
ELSE
-- Restar el monto de la cuenta de origen
UPDATE cuentas SET saldo = saldo - p_monto WHERE id = p_cuentaOrigen;
-- Sumar el monto a la cuenta de destino
UPDATE cuentas SET saldo = saldo + p_monto WHERE id = p_cuentaDestino;
-- Registrar la transacción
INSERT INTO transacciones (cuenta_origen, cuenta_destino, monto)
VALUES (p_cuentaOrigen, p_cuentaDestino, p_monto);
-- Confirmar la transacción
COMMIT;
SELECT 'Transferencia realizada con éxito.' AS resultado;
END IF;
END $$
DELIMITER ;
Ejecutar la transferencia. Ahora, podemos invocar al procedimiento para realizar una transferencia. Por ejemplo, transferir 300.00 desde la cuenta de Juan (id = 1) a la cuenta de María (id = 2):
CALL realizarTransferencia(1, 2, 300.00);
Comprobar los resultados. Después de ejecutar el procedimiento, podemos consultar las tablas cuentas y transacciones para verificar el resultado:
-- Consultar el saldo de las cuentas
SELECT * FROM cuentas;
-- Consultar las transacciones
SELECT * FROM transacciones;
Comprobación de un saldo insuficiente Para comprobar el comportamiento en caso de que no haya suficiente saldo, puedes intentar transferir más dinero del que tiene Juan:
CALL realizarTransferencia(1, 2, 1200.00);
En este caso, veremos el mensaje de "Transferencia fallida: saldo insuficiente." y el saldo de las cuentas no cambia.
También podríamos probar le funcionamiento desde código Java, invocando al procedimiento almacenado, de manera similar a como vemos:
package transaccionespruebas;
/**
* Clase para probar el procedimiento almacenado de realizar transferencia
* @author IES Aguadulce
*/
import java.sql.*;
public class TransferenciaTest {
public static void main(String[] args) {
// Configuración de conexión a la base de datos
String url = "jdbc:mysql://localhost:3306/banco"; //
String user = "root"; // Cambia por tu usuario de MySQL
String password = "admin"; // Cambia por tu contraseña de MySQL
// Datos de ejemplo para la transferencia
int cuentaOrigen = 1;
int cuentaDestino = 2;
double monto = 1100.50;
try (Connection conn = DriverManager.getConnection(url, user, password)) {
System.out.println("Conexión exitosa a la base de datos.");
// Llamar al procedimiento almacenado
String sql = "{ CALL realizarTransferencia(?, ?, ?) }";
try (CallableStatement stmt = conn.prepareCall(sql)) {
// Establecer los parámetros
stmt.setInt(1, cuentaOrigen);
stmt.setInt(2, cuentaDestino);
stmt.setDouble(3, monto);
// Ejecutar el procedimiento y obtener el resultado
boolean hasResultSet = stmt.execute();
// Procesar el resultado
if (hasResultSet) {
try (ResultSet rs = stmt.getResultSet()) {
while (rs.next()) {
System.out.println(rs.getString("resultado"));
}
}
}
}
} catch (SQLException e) {
System.err.println("Error al realizar la transferencia: " + e.getMessage());
}
}
}
Al ejecutarse y verificarse la falta de saldo, se producirá el ROLLBACK de la transacción:
Materiales desarrollados inicialmente por el Ministerio de Educación, Cultura y Deporte y actualizados por el profesorado de la Junta de Andalucía bajo licencia Creative Commons BY-NC-SA.
Antes de cualquier uso leer detenidamente el siguenteAviso legal
Ubicación: Toda la unidad Mejora (tipo 1): Actualización de algunas fotos y añadir acrónimos
Ubicación: En toda la unidad Mejora (tipo 3): - Actualizar los ejemplos de los descargables a JDK 17 y NetBeans 16, más actuales.
- Actualizar enlaces rotos.
- Los enlaces hay que abrirlos en nueva ventana porque si no en moodle no se abren.
- Cambiar proyectos para que se abran con JDK más actual.
- Eliminar Access para evitar tener que usar UCAnAccess tal y como está ahora, retocar toda esa parte de conexión a la base de datos y registro para la conexión a MySQL
- Poner un ejemplo real y funcionando del pool de conexiones con ORacle Express 21c.
- Retocar todos los apartados de consultas para eliminar Access y hacerlo con MySQL.
- Comentar la inyección SQL.
- Ampliar el apartado de procedimientos almacenados con más subapartados y ejemplos.
- Actualizar el apartado de excepciones con ejemplo.
- Actualizar el apartado de transacciones, poniendo un ejemplo para MySQL.
Ubicación: Puntos modificados en los contenidos Mejora (Mapa conceptual): Retoque por los puntos modificados en los contenidos
Ubicación: Puntos modificados en los contenidos Mejora (Orientaciones del alumnado): Retoque por los puntos modificados en los contenidos
Versión: 01.03.00
Fecha de actualización: 18/10/16
Autoría: María Dolores Amor Gómez
Ubicación: No especificada. Mejora (tipo 1): enlaces y pequeñas erratas.
Ubicación: puntos:2.2, 2.4, 4 y 6.1 Mejora (tipo 2): punto 2.2: en el Para saber más incluir un enlace con información sobre
conectores soportados por MySQL, entre ellos JDBC.
punto 2.4: en el Para saber más, incluire un enlace con información sobre
Conectar con MySQL utilizando un Thin Driver
punto 4. incluir recomendación sobre la creación de una BD en MySQL.
Incluir en punto 5 - información y ejemplos de consultas normales utilizando
una base de datos MySQL, inlcuyendo ejemplos de sentencias preparadas y gestión de
errores en MySQL.
punto 6.1. Ampliar la información incluyendo más ejemplos de ejecución de
procedimientos alamacenados con MySQL.
Versión: 01.02.00
Fecha de actualización: 24/11/14
Autoría: Jesús Manuel Marín Navarro
Actualizado y reestructurado todo el apartado 5 del tema porque JDK8 ya no soporta el driver JDBC-ODBC y se necesita usar UCanAccess. Actualizadas las definiciones de glosario
Versión: 01.01.00
Fecha de actualización: 22/11/14
Autoría: Jesús Manuel Marín Navarro
Actualizado y reestructurado todo el apartado 5 del tema.
Actualizadas las definiciones de glosario
