Pasos para crear Hibernate

Crear un proyecto Maven

Escogemos maven-archetype-quickstart
En principio el de org.apache

Añadimos las dependencias de ‘Hibernate’ y ‘Mysql’. Os las pongo aquí.

 <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.32</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-core</artifactId>
			<version>5.6.15.Final</version>
		</dependency>
  </dependencies>

Después dentro de src/main creamos la carpeta ‘resources’: New folder

Dentro de resources creamos el archivo hibernate.cfg.xml, y dentro colocamos lo siguiente:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "https://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>
		<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
		<property name="connection.url">jdbc:mysql://localhost/sakila</property>
		<property name="connection.username">root</property>
		<property name="connection.password">root</property>
		<property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>
		<property name="hibernate.show_sql">true</property>

		<mapping class="com.trifulcas.models.Actor" />
	
	</session-factory>
</hibernate-configuration>

Creamos un paquete com.trifulcas.models y dentro metemos nuestro POJO:

package com.trifulcas.models;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;


@Entity  
@Table(name= "actor")   
public class Actor {

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private int actor_id;
	
	private String first_name;
	private String last_name;
	
	public Actor() {
		
	}
	/**
	 * @return the actor_id
	 */
	public int getActor_id() {
		return actor_id;
	}
	/**
	 * @param actor_id the actor_id to set
	 */
	public void setActor_id(int actor_id) {
		this.actor_id = actor_id;
	}
	/**
	 * @return the first_name
	 */
	public String getFirst_name() {
		return first_name;
	}
	/**
	 * @param first_name the first_name to set
	 */
	public void setFirst_name(String first_name) {
		this.first_name = first_name;
	}
	/**
	 * @return the last_name
	 */
	public String getLast_name() {
		return last_name;
	}
	/**
	 * @param last_name the last_name to set
	 */
	public void setLast_name(String last_name) {
		this.last_name = last_name;
	}
	@Override
	public String toString() {
	return "Actor id=" + actor_id + ",nombre=" + first_name+" "+last_name + "]";
	}
}

Y por último probaríamos que todo va bien, este código debería funcionar:

	StandardServiceRegistry ssr = new StandardServiceRegistryBuilder().configure("hibernate.cfg.xml").build();
		Metadata meta = new MetadataSources(ssr).getMetadataBuilder().build();

		SessionFactory factory = meta.getSessionFactoryBuilder().build();
		Session session = factory.openSession();
		try {
			Actor penelope = session.get(Actor.class, 1);
			
			System.out.println(penelope.getFirst_name() + " " + penelope.getLast_name());
			List<Actor> lista = session.createQuery("from Actor a where a.first_name like '%ar%' ").getResultList();
			for (Actor a : lista) {
				System.out.println(a);
			}
			
			
		} catch (Exception ex) {
			System.out.println(ex);
		}
		factory.close();
		session.close();

DAO comienzo

public class ActorDAO {
	private Connection con;

	public ActorDAO() {
		try {
			// Registrar el driver mysql
			Class.forName("com.mysql.cj.jdbc.Driver");
			// Conectarme a la base de datos
			 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public ArrayList<Actor> getActorAll() {
		try {
			String sql="select * from actor";
			PreparedStatement psmt=con.prepareStatement(sql);
			ResultSet res=psmt.executeQuery();
			ArrayList<Actor> actores= new ArrayList<Actor>();
			while (res.next()) {
				actores.add( new Actor(res.getInt("actor_id"),
						res.getString("first_name"),
						res.getString("last_name")));
			}
			return actores;
		}catch(Exception ex) {
			ex.printStackTrace();
			return null;
		}
	}

	public Actor getActor(int id) {
		try {
			String sql="select * from actor where actor_id=?";
			PreparedStatement psmt=con.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet res=psmt.executeQuery();
			if (res.next()) {
				return new Actor(res.getInt("actor_id"),
						res.getString("first_name"),
						res.getString("last_name"));
			}
			else {
				return null;
			}
		}catch(Exception ex) {
			ex.printStackTrace();
			return null;
		}
	}
	public boolean addActor(String first_name, String last_name) {
		try {
			String sql="insert into actor (first_name,last_name) values (?,?)";
			PreparedStatement psmt=con.prepareStatement(sql);
			psmt.setString(1, first_name);
			psmt.setString(2, last_name);
			int res=psmt.executeUpdate();
			return res==1;
		}catch(Exception ex) {
			ex.printStackTrace();
			return false;
		}
	}
	public boolean addActor(Actor actor) {
		try {
			String sql="insert into actor (first_name,last_name) values (?,?)";
			PreparedStatement psmt=con.prepareStatement(sql);
			psmt.setString(1, actor.getFirst_name());
			psmt.setString(2, actor.getLast_name());
			int res=psmt.executeUpdate();
			return res==1;
		}catch(Exception ex) {
			ex.printStackTrace();
			return false;
		}
	}

}
	public static void main(String[] args) {
		try {
			// El objeto DAO que me permite trabajar con la BD
			ActorDAO dao=new ActorDAO();
			// Utilizar  una de las propiedades: add
			for(int i=0;i<10000;i++) {
				dao.addActor("John"+i, "Travolta");
			}
			//dao.addActor("John", "Travolta");
			
			Actor eva=new Actor(0,"Eva","Wuig");
		//	dao.addActor(eva);
			
			System.out.println(dao.getActor(1));
			System.out.println(dao.getActorAll());
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

Ejemplos JDBC

try {
			// Registrar el driver mysql
			Class.forName("com.mysql.cj.jdbc.Driver");
			// Conectarme a la base de datos
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
			String nombre="Ana";
			// Creo a partir de la conexión un comando
			Statement stmt = con.createStatement();
			stmt.execute("insert into actor(first_name,last_name) values ('"+nombre+"','pi')");
			
			 // Los interrogantes son los parámetros
            PreparedStatement psmt=con.prepareStatement("insert into actor (first_name,last_name) values (?,?)");
            psmt.setString(1, "Pep");
            psmt.setString(2, "Pérez");
            psmt.executeUpdate();
            psmt.setString(1, "Pepa");
            psmt.setString(2, "Pérez");
            psmt.executeUpdate();
			// Ejecuto un select y lo guardo en un resultset
            ResultSet rs = stmt.executeQuery("select * from actor where actor_id>20");
            
            // Recorro el resultset y con get obtengo los valores
            while (rs.next()) {
                System.out.println(rs.getInt("actor_id") + "  " + rs.getString(2) + "  " + rs.getString(3));
            }
			
           
            stmt.close();
            con.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

Ejemplos funciones

SELECT concat(SUBSTRING(first_name,1,2),SUBSTRING(last_name,1,2)) ref from actor;
SELECT LENGTH('MySQL') AS longitud;
SELECT UPPER('mysql') AS mayusculas, LOWER('MySQL') AS minusculas;
select concat(upper(substring(first_name,1,1)),
lower(substring(first_name,2))) nombre from actor;
SELECT REPLACE('Aprender SQL', 'SQL', 'MySQL') AS resultado;

update film set description= replace(description,'Crocodile','Cocodrilo') ;

SELECT TRIM('   Hola    que   ') AS resultado;

SELECT CONCAT_WS('-', '2023', '10', '26') AS fecha;

select * from actor
where length(first_name)>8;

select count(*) from actor;

select concat('hola',5), '5'+3 foo;

select cast('2021-1-1' as date) fecha;
SELECT STR_TO_DATE('1/1/2023', '%d/%m/%Y');

-- SImilar al if... elseif... else
select first_name,
 CASE
        WHEN length(first_name)<5 THEN 'Nombre corto'
        WHEN length(first_name)<8 THEN 'Nombre medio'
        ELSE 'Nombre largo'
    
END tipo
    from actor;

-- Similar al switch
select rating,
case rating
when 'r' then 'Para adultos'
else  'Todos los publicos'
end tipo
from film;

-- Similar al operador ternario
select if(length(first_name)<5,'corto','largo') longitud from actor;

select coalesce(first_name,'') from actor;

select concat(coalesce(first_name,'.'),coalesce(last_name,''))nombre from actor;

select now(),CURDATE() AS fecha_actual, CURTIME() AS hora_actual;

SELECT EXTRACT(YEAR FROM NOW()) AS año_actual, EXTRACT(MONTH FROM NOW()) AS mes_actual;

-- Imprescindible
select month(now()),year(now()),day(now());

select month(payment_date) from payment;

select * from payment
where month(payment_date) =5;

select year(payment_date) anyo, month(payment_date) mes,sum(amount) total from payment
group by anyo,mes;

select month('1-1-2023');


SELECT month(STR_TO_DATE('1/1/2023', '%d/%m/%Y'));

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i %w') AS fecha_formateada;

-- Alquileres en fin de semana

select count(rental_id) from rental
where date_format(rental_date,'%w') in (0,6);

SELECT ADDDATE(NOW(), 7) AS fecha_en_7_dias, SUBDATE(NOW(), 3) p;

SELECT DATEDIFF(NOW(), '2023-01-01') AS dias_desde_inicio_de_ano;

SELECT ROUND(3.14159) AS redondeo_entero, ROUND(3.14159, 3) AS redondeo_dos_decimales;

select * from actor order by rand() limit 10;

SELECT SCHEMA() AS nombre_de_base_de_datos;

SELECT concat(address,coalesce(address2,'')) dir FROM sakila.address;

insert into user (name,tarjeta)
values ('tarjeta',AES_ENCRYPT('1112222333434', 'mi_clave_secreta') )

SELECT *, cast(AES_deCRYPT(tarjeta, 'mi_clave_secreta') as char) tarjeta FROM sakila.user

Soluciones ejercicios division y vistas

-- Actores con más películas que el actor de id 1

select first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 group by actor.actor_id
 having total>(select count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 where actor.actor_id=1
 group by actor.actor_id);

-- con vistas

create view totalfilmsbyactor as
select actor.actor_id, first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 group by actor.actor_id;

select first_name, last_name, total from totalfilmsbyactor
where total>(select total from totalfilmsbyactor where actor_id=1);
 
 -- Media global del total de películas por categoría. Si tengo dos categorías,
 -- una con 100 películas y otra con 50 la media sería 75
 
 select avg(total) total from (
 select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id) as temp;
 
 -- Saber las categorías que tienen peliculas por enciima de la media
 
select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id
 having total>( select avg(total) total from (
 select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id) as temp);
 
 -- COn vistas
 
 create view filmsbycategory as
  select category.category_id, name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id;
 
 select name, total from filmsbycategory
 where total>(select avg(total) from filmsbycategory);
 
 
-- Clientes que no han alquilado  películas de rating ‘R’
-- Primero busco los clientes que si hayan alquilado
-- películas de rating r
-- Y después busco los que no estén en esa lista

select first_name, last_name from customer
where customer_id not in(
select customer.customer_id from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join customer on rental.customer_id=customer.customer_id
where rating='r');

-- Clientes que no han alquilado películas del actor con id 1
-- Pasar a positivo y luego los que no
-- Clientes que si han alquilado películas del actor 1
select first_name, last_name from customer
where customer_id not in (
select customer.customer_id from customer join rental on customer.customer_id=rental.customer_id
join inventory on rental.inventory_id=inventory.inventory_id
join film on inventory.film_id=film.film_id
join film_actor on film.film_id=film_actor.film_id
where film_actor.actor_id=1);

-- Actores que no hayan trabajado en películas de robots
-- ni en películas de cocodrilos
-- Actores que sí

select * from actor where actor_id not in (
select actor.actor_id from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where description like '%robot%' or description like '%crocodile%');

-- Crear una vista 'customer_info' que para cada cliente me muestre el
-- total de alquileres y el importe total de los mismos

create view customer_info as
select customer.*, count(rental.rental_id) total, sum(amount) importe from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group  by customer.customer_id;

-- Ahora ciertas consultas son más sencillas

SELECT sum(total) total FROM customer_info join address
on address.address_id=customer_info.address_id
where district='ontario';

-- Crear una vista 'customer_films que para cada cliente me muestre 
-- en un campo los títulos de las películas que ha alquilado


create view customer_films as
select customer.*, group_concat(title) films from 
customer join rental on customer.customer_id=rental.customer_id
join inventory on rental.inventory_id=inventory.inventory_id
join film on inventory.film_id=film.film_id
group by customer.customer_id;

-- Hay consultas que se simplifican

SELECT * FROM sakila.customer_films
where films like '%dinosaur%';

Solución Ejercicios sql

-- Películas ‘Épicas’ (Epic) o ‘Brillantes’ (brilliant) (description)
-- que duren más de 180 minutos (length)

SELECT * FROM sakila.film
where (description like '%epic%' or description like '%brilliant%')
and `length`>180;

-- Películas que duren entre 100 y 120 minutos o entre 50 y 70 minutos

SELECT * FROM sakila.film
where length between 100 and 120 or length between 50 and 70;

-- Películas que cuesten 0.99, 2.99 y tengan un rating ‘g’ o ‘r’
-- y que hablen de cocodrilos (crocodile)

SELECT * FROM film
where rental_rate between 0.99 and 2.99 and rating in ('g','r')
and description like '%crocodile%';

-- Direcciones de ontario o de punjab o que su código postal acabe en 5
--  o que su teléfono acabe en 5

SELECT * FROM sakila.address
where district in ('ontario', 'punjab') or postal_code like '%5'
or phone like '%5'
order by district;

-- Ventas totales por empleado

select first_name, last_name, count(payment_id) total, sum(amount) importe
from staff join payment on staff.staff_id=payment.staff_id
group by staff.staff_id;

-- Películas en las que han trabajado más de 10 actores

select title, count(actor.actor_id) total, group_concat(concat(first_name,' ',last_name)) actores from film join film_actor on film.film_id=film_actor.film_id
join actor on actor.actor_id=film_actor.actor_id
group by film.film_id
having total>10
order by title;

-- El título de la película que más se ha alquilado (en número de alquileres)

select title, count(rental_id) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
order by total desc
limit 1;

-- El título de la película que más se ha alquilado de rating 'r'

select title, count(rental_id) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
where rating='r'
group by film.film_id
order by total desc
limit 1;

-- El título de la película que más dinero ha dado (en suma de importe)

select title, sum(amount) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join payment on rental.rental_id=payment.rental_id
group by film.film_id
order by total desc
limit 1;

-- Los 5 actores que han trabajado en menos películas

select first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 group by actor.actor_id
 order by total
 limit 5;
 
 -- Los 5 actores que han trabajado en menos películas de rating 'r'

select first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 join film on film.film_id=film_actor.film_id
 where rating='r'
 group by actor.actor_id
 order by total
 limit 5;
 
 

Consultas división


-- Quiero los actores que NO hayan trabajado en una película de rating 'R'
-- Con subconsultas
-- Cambio la pregunta a positivo: Los que Sí han trabajado en 'R'
-- Con una subconsulta seleccion los que no están en esa lista

select first_name, last_name from actor
where actor_id not in (
select actor.actor_id from actor left join film_actor on actor.actor_id=film_actor.actor_id
left join film on film_actor.film_id=film.film_id
where rating='R');

-- películas que no sean de la categoría 'children'
-- Cambio a positivo: Que si sean de children
select title from film
where film_id not in
(select film.film_id from film join film_category on film.film_id=film_category.film_id
join category on film_category.category_id=category.category_id
where name='children');

select * from actor
where actor_id not in (
select actor_id from actor join film_actor using(actor_id)
join film using(film_id) join film_category using(film_id)
join category using(category_id)
where name='Music');

-- actores que no hayan trabajado en children (con vistas)

select * from actor
where actor_id not in 
(select actor_id from filmografia where name='children')

Consultas crear vistas

create view customer_over_avg as
SELECT first_name, last_name, COUNT(*) AS rental_count
FROM rental join customer on rental.customer_id=customer.customer_id
GROUP BY customer.customer_id
HAVING rental_count > (SELECT AVG(rental_count) FROM rental_client);

create view filmografia as
select name, film.*,first_name, last_name from category join film_category on category.category_id=film_category.category_id
join film on film.film_id=film_category.film_id
join film_actor on film.film_id=film_actor.film_id
join actor on film_actor.actor_id=actor.actor_id

Soluciones sql

insert into actor (first_name, last_name)
values ('juan','perez');

insert into actor (first_name, last_name)
values ('rosa','pi');

update actor set first_name='PEPE'
where actor_id=10;

update actor set first_name=concat('Actor',actor_id)
where actor_id>200;

delete from actor where actor_id>200;

select country, customer.* from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
where country like 'A%';

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country like 'a%';

select distinct first_name,last_name from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where length>140;

select distinct name from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
where rating='r';

select title, count(rental_id) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
having total>20;

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country ='spain' or country='argentina';

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country in ('spain','argentina');

select title, name from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
where name in ('children','family');

select first_name, last_name from actor
where first_name like '%x%' or last_name like '%x%';

select * from address where district='california' and phone like '%274%';

Ejemplos agregados

-- El total de ciudades: contar 

select country,count(city) total
from country join city on country.country_id=city.country_id
group by country;

-- Total de importe de clientes

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group by customer.customer_id;

-- en la consulta anterior los clientes que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group by customer.customer_id
having total>100;

-- Los clientes cuyo nombre empieza por 'a' que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
where first_name like 'a%'
group by customer.customer_id
having total>100 and media>4;

-- ¿Cuantos países tienen más de 50 clientes?
-- Nombre del país y número de clientes (count)

select country, count(customer_id) total from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
group by country.country_id
having total>50