Mes: julio 2024

package com.trifulcas.testconexion;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestConectar {

	public static void main(String[] args) {
		System.out.println("Hola que tal?");
		try {
			// Registrar la clase jdbc, imprescindible
			Class.forName("com.mysql.cj.jdbc.Driver");
			// Aquí creamos la conexión. Tiene tres parámetros:
			// url o cadena de conexión
			// usuario
			// contraseña
			// Analicemos un poco la cadena: jdbc:mysql://localhost:3306/sakila
			// jdbc es el protocolo de conexión (driver)
			// mysql la base de datos a la que me conecto
			// localhost la url del servidor
			// 3306 el puerto
			// sakila la base de datos a la que me qiero conectar
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "");
			System.out.println("Conexión correcta");
			// A partir de aquí ya podemos lanzar sentencias sql al servidor de la base de
			// datos
			// Lo primero que necesitamos es un statement (java.sql.statement)
			Statement stmt = con.createStatement();
			// Un statement nos permite lanzar sentencias sql
			// Sentencia de inserción, no tenemos datos para recuperar
			stmt.execute("insert into country(country) values ('Beluchistán')");

			// Si queremos acceder a los datos de la base de datos usamos
			// executeQuery y un ResultSet que nos guarda el resultado de la consulta
			// Para poder recorrer los valores que se retornan

			// Ejecuto la sentencia SQL y guardo el resultado en un ResultSet (que tenemos
			// que importar)
			ResultSet rs = stmt.executeQuery("SELECT * FROM country");
			// Recorremos el resultset con next(). Cuando se llega al final next devuelve
			// falso
			// Porque no hay un siguiente registro
			rs.next();
			System.out.println("ID: " + rs.getInt(1));
			System.out.println("País: " + rs.getString(2));
			while (rs.next()) {
				// Accedemos a cada una de las columnas, como Java es un lenguaje tipado
				// Tenemos que escoger el get adecuado al tipo de la columna
				// En este caso la primera columna es el id, un int
				// Y la segunda es el country, un string
				System.out.println("ID: " + rs.getInt(1));
				System.out.println("País: " + rs.getString(2));
				System.out.println("ID: " + rs.getInt("country_id"));
				System.out.println("País: " + rs.getString("country"));
			}

			// cerramos todo
			rs.close();
			stmt.close();
			con.close();
		} catch (Exception e) {

			System.out.println(e.getMessage());
		}

	}

}

Base de datos

CREATE TABLE `cliente` (
  `idcliente` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(145) DEFAULT NULL,
  `dni` varchar(45) DEFAULT NULL,
  `mail` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idcliente`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `empleado` (
  `idempleado` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(145) DEFAULT NULL,
  `dni` varchar(45) DEFAULT NULL,
  `mail` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idempleado`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `llamada` (
  `idllamada` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idcliente` int(10) unsigned DEFAULT NULL,
  `idempleado` int(10) unsigned DEFAULT NULL,
  `fechahora` datetime DEFAULT NULL,
  `duracion` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`idllamada`),
  KEY `fk_cliente_idx` (`idcliente`),
  KEY `fk_empleado_idx` (`idempleado`),
  CONSTRAINT `fk_cliente` FOREIGN KEY (`idcliente`) REFERENCES `cliente` (`idcliente`) ON UPDATE NO ACTION,
  CONSTRAINT `fk_empleado` FOREIGN KEY (`idempleado`) REFERENCES `empleado` (`idempleado`) ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Datos de prueba:

INSERT INTO `cliente` (`idcliente`, `nombre`, `dni`, `mail`, `password`) VALUES
(1, 'Juan Perez', '12345678A', 'juan.perez@example.com', 'password123'),
(2, 'Maria Lopez', '23456789B', 'maria.lopez@example.com', 'password456'),
(3, 'Carlos Garcia', '34567890C', 'carlos.garcia@example.com', 'password789'),
(4, 'Ana Fernandez', '45678901D', 'ana.fernandez@example.com', 'password012'),
(5, 'Luis Martinez', '56789012E', 'luis.martinez@example.com', 'password345'),
(6, 'Elena Gomez', '67890123F', 'elena.gomez@example.com', 'password678'),
(7, 'Pedro Sanchez', '78901234G', 'pedro.sanchez@example.com', 'password901'),
(8, 'Sofia Ramirez', '89012345H', 'sofia.ramirez@example.com', 'password234'),
(9, 'Miguel Torres', '90123456I', 'miguel.torres@example.com', 'password567'),
(10, 'Laura Morales', '01234567J', 'laura.morales@example.com', 'password890');

INSERT INTO `empleado` (`idempleado`, `nombre`, `dni`, `mail`) VALUES
(1, 'Alejandro Rodriguez', '11223344A', 'alejandro.rodriguez@example.com'),
(2, 'Beatriz Sanchez', '22334455B', 'beatriz.sanchez@example.com'),
(3, 'Cristina Martinez', '33445566C', 'cristina.martinez@example.com'),
(4, 'David Fernandez', '44556677D', 'david.fernandez@example.com'),
(5, 'Eva Gonzalez', '55667788E', 'eva.gonzalez@example.com'),
(6, 'Francisco Lopez', '66778899F', 'francisco.lopez@example.com'),
(7, 'Gabriela Herrera', '77889900G', 'gabriela.herrera@example.com'),
(8, 'Hector Ruiz', '88990011H', 'hector.ruiz@example.com'),
(9, 'Irene Castillo', '99001122I', 'irene.castillo@example.com'),
(10, 'Javier Ortega', '00112233J', 'javier.ortega@example.com');

INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES
(1, 1, 2, '2024-01-01 09:00:00', 15),
(2, 3, 4, '2024-01-01 10:30:00', 30),
(3, 2, 1, '2024-01-01 11:00:00', 20),
(4, 5, 3, '2024-01-02 14:00:00', 25),
(5, 6, 5, '2024-01-02 15:45:00', 10),
(6, 4, 6, '2024-01-03 08:15:00', 35),
(7, 7, 8, '2024-01-03 09:45:00', 40),
(8, 8, 7, '2024-01-04 12:30:00', 50),
(9, 9, 9, '2024-01-04 13:15:00', 5),
(10, 10, 10, '2024-01-05 16:00:00', 45);

INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES
(11, 1, 3, '2024-01-05 17:30:00', 20),
(12, 2, 4, '2024-01-06 08:45:00', 15),
(13, 3, 5, '2024-01-06 09:15:00', 25),
(14, 4, 6, '2024-01-07 11:30:00', 30),
(15, 5, 7, '2024-01-07 12:00:00', 40),
(16, 6, 8, '2024-01-08 14:45:00', 50),
(17, 7, 9, '2024-01-08 15:00:00', 10),
(18, 8, 10, '2024-01-09 16:15:00', 35),
(19, 9, 1, '2024-01-09 17:00:00', 45),
(20, 10, 2, '2024-01-10 09:00:00', 20),
(21, 1, 3, '2024-01-10 09:45:00', 15),
(22, 2, 4, '2024-01-11 10:30:00', 25),
(23, 3, 5, '2024-01-11 11:00:00', 30),
(24, 4, 6, '2024-01-12 12:30:00', 40),
(25, 5, 7, '2024-01-12 13:15:00', 50),
(26, 6, 8, '2024-01-13 14:00:00', 10),
(27, 7, 9, '2024-01-13 14:45:00', 35),
(28, 8, 10, '2024-01-14 15:30:00', 45),
(29, 9, 1, '2024-01-14 16:15:00', 20),
(30, 10, 2, '2024-01-15 17:00:00', 15),
(31, 1, 3, '2024-01-15 08:45:00', 25),
(32, 2, 4, '2024-01-16 09:15:00', 30),
(33, 3, 5, '2024-01-16 10:00:00', 40),
(34, 4, 6, '2024-01-17 11:00:00', 50),
(35, 5, 7, '2024-01-17 11:45:00', 10),
(36, 6, 8, '2024-01-18 12:30:00', 35),
(37, 7, 9, '2024-01-18 13:15:00', 45),
(38, 8, 10, '2024-01-19 14:00:00', 20),
(39, 9, 1, '2024-01-19 14:45:00', 15),
(40, 10, 2, '2024-01-20 15:30:00', 25);

INSERT INTO `empleado` (`idempleado`, `nombre`, `dni`, `mail`) VALUES
(11, 'Karla Paredes', '10293847K', 'karla.paredes@example.com'),
(12, 'Luis Dominguez', '56473829L', 'luis.dominguez@example.com');


INSERT INTO `cliente` (`idcliente`, `nombre`, `dni`, `mail`, `password`) VALUES
(11, 'Natalia Herrera', '11223344K', 'natalia.herrera@example.com', 'password112'),
(12, 'Roberto Diaz', '22334455L', 'roberto.diaz@example.com', 'password223');

INSERT INTO `llamada` (`idllamada`, `idcliente`, `idempleado`, `fechahora`, `duracion`) VALUES
(41, 1, 2, '2023-12-15 10:30:00', 20),
(42, 2, 3, '2023-11-20 14:00:00', 30),
(43, 3, 4, '2023-10-25 16:45:00', 15),
(44, 4, 5, '2023-09-30 08:00:00', 25),
(45, 5, 6, '2023-08-10 09:15:00', 40),
(46, 6, 7, '2023-07-05 11:30:00', 35),
(47, 7, 8, '2023-06-15 13:45:00', 50),
(48, 8, 9, '2023-05-20 15:00:00', 10),
(49, 9, 10, '2023-04-25 17:15:00', 45),
(50, 10, 1, '2023-03-30 19:30:00', 20),
(51, 11, 2, '2022-12-15 10:30:00', 15),
(52, 12, 3, '2022-11-20 14:00:00', 25),
(53, 1, 4, '2022-10-25 16:45:00', 30),
(54, 2, 5, '2022-09-30 08:00:00', 40),
(55, 3, 6, '2022-08-10 09:15:00', 50),
(56, 4, 7, '2022-07-05 11:30:00', 10),
(57, 5, 8, '2022-06-15 13:45:00', 35),
(58, 6, 9, '2022-05-20 15:00:00', 45),
(59, 7, 10, '2022-04-25 17:15:00', 20),
(60, 8, 1, '2022-03-30 19:30:00', 15);

Consultas

-- Empleados sin llamadas
select empleado.* from empleado left join llamada on empleado.idempleado=llamada.idempleado
where idllamada is null;

select * from empleado where idempleado not in (select idempleado from llamada);

-- Total de llamadas por empleado

select empleado.*, count(idllamada) total
 from empleado left join llamada on empleado.idempleado=llamada.idempleado
 group by idempleado;
 
-- Total de llamadas por cliente

select cliente.*, count(idllamada) total
 from cliente left join llamada on cliente.idcliente=llamada.idcliente
 group by idcliente;
 
-- Cliente con la llamada de mayor duración

select distinct cliente.*, duracion from
cliente join llamada on cliente.idcliente=llamada.idcliente
order by duracion desc
limit 1;

-- bien hecho

select distinct cliente.*, duracion from
cliente join llamada on cliente.idcliente=llamada.idcliente
where duracion=(select max(duracion) from llamada);

-- Total de llamadas por año

select year(fechahora) anyo, count(idllamada) total
from llamada
group by anyo;

-- Dentro de sql lo que tenemos es dos maneras
-- de incorporar programación: funciones y procedimientos
-- ¿Para qué existen? Porque hay veces que queremos hacer cosas
-- Y el SQL normal es insuficiente
-- Para tareas de mantenimiento, de control, de lógica de negocio

-- Los procedimientos almacenados se utilizan normalmente para realizar
-- operaciones que van más allá de devolver un resultado
-- Tienen una sintaxis un poco más compleja que las funciones
-- https://proyectoa.com/anadir-y-usar-procedimiento-almacenado-stored-procedure-en-mysql-server/
-- https://www.dolthub.com/blog/2024-01-17-writing-mysql-procedures/

-- Para empezar tenemos parámetros de entrada (IN) y parámetros de salida (OUT)
-- que se definen en el inicio
-- Ejemplo: (IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
-- Cuando llamo al procedimiento almacenado (call) pasaré 2 parámetros y recibiré uno

call film_in_stock(1,1,@total);
select @total;

-- Luego dentro del procedimiento almacenado tengo las mismas opciones
-- que en las funciones. Podemos programar de verdad
-- Tenemos variables, estructuras de control (if) bucles (while)...

CREATE PROCEDURE fibonacci(n int, out answer int)
BEGIN 
    DECLARE i int default 2;
    DECLARE p, q int default 1;
    SET answer = 1;
    WHILE i < n DO
        SET answer = p + q;
        SET p = q;
        SET q = answer;
        SET i = i + 1;
    END WHILE;
END;

call fibonacci(10,@numero);
select @numero;

-- Ejemplo con utilidad
-- Imaginemos que queremos de dar de alta un cliente
-- Antes del cliente tenemos que tener una dirección
-- Esa dirección tiene que tener una ciudad
-- Esa ciudad tiene que tener un país
-- Spain, logroño, carmen medrano 11, pepito perez
-- Para hacer esto tengo que hacer una serie de comprobaciones
-- Y si no de inserciones
-- Esto con un procedimiento almacenado va muy bien

CREATE DEFINER=`root`@`localhost` PROCEDURE `alta_cliente`(
in p_country varchar(50),
in p_city varchar(50),
in p_address varchar(50),
in p_first_name varchar(50),
in p_last_name varchar(50),
out p_customer_id int
)
BEGIN
declare v_country_id int;
declare v_city_id int;
declare v_address_id int;
declare v_customer_id int;

-- Busco el pais que se llame como el parámetro
select country_id into v_country_id from country
where country=p_country;
-- O existe o no existe. Si no existe, lo inserto
IF v_country_id is null then
 insert into country(country) values (p_country);
 select last_insert_id() into v_country_id;
END IF;
-- Al llegar aquí en v_country_id tengo el id del país que me han
-- pasado como parámetro. Bien porque existía y he recuperado ese valor
-- Bien porque no existía, lo he insertado, y he recuperado el id
-- del registro insertado

-- Con la ciudad hacemos algo parecido

select city_id into v_city_id from city
where city=p_city and country_id=v_country_id;
 
IF v_city_id is null then
 insert into city(city,country_id)
 values (p_city,v_country_id);
 select last_insert_id() into v_city_id;
END IF;

-- Igual que antes, al llegar aquí en v_city-id tengo el id de la ciudad

select address_id into v_address_id from address
where address=p_address and city_id=v_city_id;
 
IF v_address_id is null then
 insert into address(address,city_id)
 values (p_address,v_city_id);
 select last_insert_id() into v_address_id;
END IF;

select customer_id into v_customer_id from customer
where first_name=p_first_name and last_name=p_last_name and address_id=v_address_id;
 
IF v_customer_id is null then
 insert into customer(first_name,last_name,address_id,store_id)
 values (p_first_name,p_last_name, v_address_id,1);
 select last_insert_id() into v_customer_id;
END IF;
 
select v_customer_id into p_customer_id;
END

-- ejemplo de uso

call alta_cliente('Spain','Barcelona','Agla 6','Pepito','Perez',@customer_id);
select @customer_id;

call alta_cliente('Spain','Barcelona','Sant Antoni 3','Ana','Pi',@customer_id);
select @customer_id;

call alta_cliente('Trubulandia','Trubulú','Sant Antoni 3','Ana','Pi',@customer_id);
select @customer_id;

-- Dentro de sql lo que tenemos es dos maneras
-- de incorporar programación: funciones y procedimientos
-- ¿Para qué existen? Porque hay veces que queremos hacer cosas
-- Y el SQL normal es insuficiente
-- Para tareas de mantenimiento, de control, de lógica de negocio

-- Por ejemplo yo puedo tener funciones que me sean de utilidad

CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_in_stock`(p_inventory_id INT) RETURNS tinyint(1)
    READS SQL DATA
BEGIN
    DECLARE v_rentals INT;
    DECLARE v_out     INT;

    #AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
    #FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED

    SELECT COUNT(*) INTO v_rentals
    FROM rental
    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN
      RETURN TRUE;
    END IF;

    SELECT COUNT(rental_id) INTO v_out
    FROM inventory LEFT JOIN rental USING(inventory_id)
    WHERE inventory.inventory_id = p_inventory_id
    AND rental.return_date IS NULL;

    IF v_out > 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END

-- Como utilizaría lo anterior

select title,inventory_in_stock(inventory_id) stock
from film join inventory on film.film_id=inventory.film_id;

-- Otro ejemplo:

CREATE DEFINER=`root`@`localhost` FUNCTION `max_rental`() RETURNS int(11)
BEGIN
  DECLARE maximo INT;
  
select max(total) into maximo from (
select  count(rental_id) total
    from rental
    group by year(rental_date) , month(rental_date) 
    order by total desc
    ) temp;
    
RETURN maximo;
END

-- como lo usaría
select year(rental_date) anyo, month(rental_date) mes, count(rental_id) total
from rental
group by anyo,mes
having total=max_rental();

-- Triggers
-- Los triggers son acciones que se ejecutan cuando modificamos datos
-- en una tabla. Se suelen usar para realizar tareas de mantenimiento
-- de integridad que no permite el sql
-- Ya estamos trabajando con programación T-SQL
-- https://dev.mysql.com/doc/refman/8.4/en/triggers.html

-- Los triggers los tenemos en la pestaña 'triggers'
-- son before o update y para insert, update y delete

-- Ejemplos
-- Este hace que cuando borremos un actor, si su id está entre 1 y 20 no nos deja
-- En caso contrario lo inserta en un log
CREATE TRIGGER `sakila`.`actor_BEFORE_DELETE` BEFORE DELETE ON `actor` FOR EACH ROW
BEGIN

if OLD.actor_id between 1 and 20 then
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No puedes borrar';
else
 insert into actor_log(first_name, last_name) values (OLD.first_name, OLD.last_name);
end if;
END

CREATE DEFINER=`root`@`localhost` TRIGGER `sakila`.`payment_BEFORE_INSERT` BEFORE INSERT ON `payment` FOR EACH ROW
BEGIN
if NEW.amount>=10 then
 set NEW.amount=9.99;
end if;
END

CREATE DEFINER = CURRENT_USER TRIGGER `sakila`.`payment_BEFORE_UPDATE` BEFORE UPDATE ON `payment` FOR EACH ROW
BEGIN
if NEW.amount > OLD.amount then
 set NEW.amount=OLD.amount;
end if;
END

update customer
set password= sha2(concat(customer_id,email,'daleatucuerpoalegriamacarena'),512);

SELECT * FROM customer
where password=sha2(concat(44,'MARIE.TURNER@sakilacustomer.org','daleatucuerpoalegriamacarena'),512);

update customer
set mail_crypt=aes_encrypt(email,'daleatucuerpoalegriamacarena');

SELECT * FROM customer
where cast(aes_decrypt(mail_crypt,'daleatucuerpoalegriamacarena') as char)
 like 'c%';

-- Funciones de información

-- Funciones de información general del entorno
select current_user(), database(), last_insert_id(),version();


-- funciones que nos sirven para conocer como tengo la base de datos

-- Estos son los datos de las tablas
select * from information_schema.tables;

-- Estos son los datos de las columnas
select * from information_schema.columns;

-- Aquí tenemos todda la información que tenemos en el esquema:
-- https://dev.mysql.com/doc/refman/8.4/en/information-schema-table-reference.html

-- También podemos utilizar SHOW
-- https://dev.mysql.com/doc/refman/8.4/en/show.html

-- Ver todas las tablas
show tables;

-- Ver todas columnas de film
show columns from film;

-- Mostrar los índices de film
show index from film;