Agrupaciones en consultas con javax.persistence.criteria.CriteriaBuilder

Vamos a poner un par de ejemplos de uso del CriteriaBuilder en JPA para poder hacer consultas usando JpaSpecificationExecutor e incluso usar agrupaciones con groupBy.

Se van a ir poniendo las diferentes clases desde el Entity hasta el Service:

Entity de la tabla cuenta con un par de campos.

@Entity(name="cuentaEntity")
@Table(name="CUENTA")
public class CuentaEntity implements Serializable {
     @Id
     private Integer id;
     @Column(name="balance")
     private Integer balance;
     @Column(name="empresa")
     private String empresa;
     @Column(name="fecha")
     @Temporal(TemporalType.TIMESTAMP)
     private Date fecha;

     //getter setter
}

Su correspondiente JPAMetalModelEntityProcessor, la vamos a utilizar para las consultas.

@Generated(value = "org.hibernate.jpamodelgen.JPAMetalModelEntityProcessor")
@StaticMetamodel(CuentaEntity.class)
public abstract class CuentaEntity_ {
	public static volatile SingularAttribute< CuentaEntity, Integer> id;
	public static volatile SingularAttribute< CuentaEntity, Integer> balance;
	public static volatile SingularAttribute< CuentaEntity, String> empresa;
	public static volatile SingularAttribute< CuentaEntity, Date> fecha;
}

Un clase repositorio. Hay que hacer que herede tanto de la JpaRepository como del JpaSpecificationExecutor.

@Repository
public interface CuentaRepository extends JpaRepository< CuentaEntity, Integer>, 
                                         JpaSpecificationExecutor< CuentaEntity> {
	
}

Creamos nuestra clase criteria donde meteremos las especificaciones de las querys. Se añaden unos ejemplos de condicionales

public class CuentaCriteria implements Serializable{
     private Integer id;
     private Integer balance;
     private String empresa;
     private Date fecha;

     ......

     public static Specification< CuentaEntity> search(final CuentaCriteria cri){
	return new Specification< CuentaEntity>(){

	        @Override
		public Predicate toPredicate(Root< CuentaEntity> root, 
                                 CriteriaQuery< ?> criQuery, CriteriaBuilder criBuilder) {
				
			List< Predicate> predicates = new ArrayList< Predicate>();

			//el nombre de la empresa sea igual al que se pasa en CuentaCriteria. Si no está vacia.
			if( !StringUtils.isEmpty( cri.getEmpresa())){
				predicates.add( criBuilder.like(root.get(CuentaEntity_.empresa), cri.getEmpresa()) );
			}

			//la fecha debe ser igual a la que se pasa en CuentaCriteria. Si no está vacia.
			if( !StringUtils.isEmpty( cri.getFecha())){
				predicates.add( criBuilder.equal(root.get(StatementEntity_.fecha),cri.getFecha()) );
			}

			//podemos añadir tambien una condicion de tipo OR ( id = 0 OR id = 1). 
			List< Predicate> predOr = new ArrayList< Predicate>();
			predOr.add( criBuilder.equal(root.get(CuentaEntity_.id), 0) );
			predOr.add( criBuilder.equal(root.get(CuentaEntity_.id), 1) );
			predicates.add( criBuilder.or(predOr.toArray(new Predicate[predOr.size()]) ) );
	
			//Si tubieramos un objeto Embeddable u otro tipo de objeto podriamos acceder 
			//a las propiedades de ese objeto
			//predicates.add( criBuilder.like(root.get("OBJETO").get("CAMPO_OBJETO"), "qqqqqq") );
				
			..............

		        //Concatenamos todos los predicados como ands
			return criBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
		}
	};
     }
}

Interfaz con nuestro servicio con unos métodos de prueba.

public interface CuentaService{
	public Page< CuentaEntity> find(CuentaCriteria criteria, Pageable pageable);
	public List< Object[]> findGroupBy(CuentaCriteria criteria);
	public Long count(CuentaCriteria criteria);
        .........
}

Y su implementación:

@Component("cuentaService")
@Transactional
public class CuentaServiceImpl implements CuentaService {

	@PersistenceContext
        EntityManager em; 
		
	@Autowired
	private CuentaRepository cuentaRepository;
	
	//En este método permite realizar una consulta que devuelve el listado de todas las cuentas que
	//lo cumplen. Las condiciones de la consulta se le pasan con CuentaCriteria.search(criteria).
	//No devolverá todos los elementos al pasarle un objeto Pageable para paginación.
	public Page< CuentaEntity> find(CuentaCriteria criteria, Pageable pageable){
		return cuentaRepository.findAll( CuentaCriteria.search(criteria), pageable);
	}

	//En este método deveulve la cuenta de la consulta anterior. Las condiciones de la consulta
	//se le pasan con CuentaCriteria.search(criteria).
	public Long count(StatementCriteria criteria){
		return this.statementRepository.count( StatementCriteria.search(criteria) );
	}
	
	//El groupBy no funciona bien si se mete en StatementCriteria.search(criteria), eso es porque
	//aunque hagas un multiselect() con sólo los elementos que quieres sacar en la consulta te lo va 
	//a sobreescribir por el select().
	//Una posible solución sería hacerlo como se hace en este método.
	public List< Object[]> findGroupBy(CuentaCriteria criteria){
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery cq = cb.createQuery();
		Root root = cq.from(StatementEntity.class);
				
		//declaramos el multiselect con las columnas a devolver
		cq.multiselect(
                       //fecha mayor
                       cb.greatest( root.get(CuentaEntity_.fecha).as(java.util.Date.class) ),
                       //fecha menor
                       cb.least( root.get(CuentaEntity_.fecha).as(java.util.Date.class) ),
                       //balance mayor
                       cb.max( root.get(CuentaEntity_.balance) ),
                       //balance menor
                       cb.min( root.get(CuentaEntity_.balance) ),
                       //media del balance
                       cb.avg( root.get(CuentaEntity_.balance) ),
                       //suma del balance total
                       cb.sum( root.get(CuentaEntity_.balance).as(java.lang.Integer.class) ),
                       //suma del balance siempre que sea mayor de cero, es decir, positivo
                       cb.sum( cb.selectCase()  
				.when(cb.greaterThan(root.get(CuentaEntity_.balance), 0), 
                                           root.get(CuentaEntity_.balance)    ).otherwise(0)),
     
                       //contador con todas las filas agrupadas
                       cb.count( root.get(CuentaEntity_.id) )
		);
                //aplicamos todas la condiciones de consulta al where
		cq.where(  CuentaCriteria.search(criteria).toPredicate(root, cq, cb)  );

                //le decimos que agrupe por el nombre de las empresas
		cq.groupBy( root.get( CuentaEntity_.empresa) );
		  
		Query query = em.createQuery(cq);

                //Por último lo podemos retornar como una lista de objetos
		return (List< Object[]>)query.getResultList();
	}	
}

@ElementCollection vs @OneToMany

Estas dos anotaciones nos permiten elaborar relaciones 1 a N entre dos objetos con JPA. A continuación se va a exponer su funcionamiento:

@ElementCollection
Te permite de una forma muy simple definir una relación de una entidad con otros objetos. Se puede por ejemplo, dado un empleado, definir una lista de strings que se corresponden con los telefonos del empleado. Sería así:

@Entity(name="Employee")
@Table(name="EMPLOYEE")
public class Employee {

	@Id
	@Column(name = "_id")
	private String id;

	@Column(name="name")
	private String name;

	@ElementCollection
	@CollectionTable(name="PHONE", joinColumns=@JoinColumn(name="phone_id"))
	@Column(name="phone_number")
	private List< String> phones;
        .....
}

Teniendo en cuenta este ejemplo tendremos en BD:
– Una tabla EMPLOYEE con las columnas -> _id y name.
– Una tabla PHONE con las columnas -> phone_id y phone_number. (el campo phone_id hara de foreign key, mientras que phone_number contendrá el String del número de teléfono)

En el ejemplo anterior usábamos una lista de String pero podríamos usar un objeto @Embeddable, tal que así:

@Entity
public class Employee {
	@Id
	@Column(name = "_id")
	private String id;

	@Column(name="name")
	private String name;

	@ElementCollection
	@CollectionTable(name="PHONE",joinColumns=@JoinColumn(name="phone_id"))
        private List< Phone> phones;
        ...
}

@Embeddable
public class Phone {
        @Column(name="phone_number")
        private String number;
        ...
}

El resultado de hacerlo así en la base de datos sería exactamente el mismo pero nos va a permitir añadir mas campos en el objeto PHONE.

@OneToMany
Te permite relacionar dos objetos Entity.

@Entity
public class Employee {
	@Id
	@Column(name = "_id")
	private String id;

	@Column(name="name")
	private String name;

	@OneToMany(cascade={CascadeType.ALL}, mappedBy="empl", 
                   fetch=FetchType.LAZY, targetEntity = Phone.class)
	@OnDelete(action = OnDeleteAction.CASCADE)
	private List< Phone> phones;
  ...
}

@Entity
public class Phone {
	@Id
	private long id;

	@Column(name="phone_number")
	private String number;

	@ManyToOne
	@JoinColumn(name="phone_id")
	private Employee empl;
	...
}

Como se puede ver hay que marcar la relacion OneToMany en la entidad Employee, marcándole el campo donde debe apuntar (en este caso «empl»). En ese campo de la entidad Phone se debe marcar la ManyToOne indicandole el nombre de la columna (phone_id) que hará de foreign key.

Además, se ha añadido la anotación @OnDelete para indicarle que haga un borrado en cascada de la lista de teléfonos al eliminar al empleado. Esto es significativo por el hecho de que en el caso del ElementCollection no se permite indicar el tipo de borrado.
Nota: Hay que decir que en el borrado en cascada se eliminan de golpe todos los relacionados mientras que sin él, va haciendo un borrado de cada uno de sus relacionados (es decir, si un empleado tiene 10 teléfonos al hacerlo en cascada se lanzaría un único delete mientras que en el otro caso se lanzan 10 sentencias delete).

Resumen final
– BD: el esquema de tablas y relaciones que va a construir JPA en nuestra BD va a ser muy similar en ambos casos.
– Código: Se puede decir que es mucho más cómodo utilizar @ElementCollection al simplificar el código.
– Potencia: Si queremos poder utilizar toda la potencia de las relaciones optimizando nuestros recursos es mejor decantarse por @OneToMany.

Fuente
– @ElementCollection
– @OneToMany

Generar modelo entidad relación a partir de una base de datos MySQL

Para obtener automáticamente un modelo entidad relación a partir de una base de datos MySQL se puede utilizar la herramienta MySQL Workbench.

Modo de uso:
1 – Hay que descargarsela e instalarla.

2 – Nos vamos al menú superior Database y seleccionamos la opción de Reverse Engineer (Ingeniería inversa).

3 – Rellenamos los datos de conexión a la BD en la ventana que se nos abrirá.

4 – Se va siguiendo las distintas ventanas que van saliendo dando a next. Ellas nos irá pidiendo la bd de la que se quieren extraer los datos, los objetos a extraer,..

5 – Si todo va bien al final nos mostrará el modelado con las tablas pedidas.

Crear una aplicación con gvNIX

gvNIX está basado en Spring Roo y te permite generar aplicaciones web java de manera muy rápida a partir de un esquema de base de datos. http://www.gvnix.org/

Instalación de gvNIX

Comandos a ejecutar para la creación de un proyecto

//Primero se configura la BD de la que luego se hará ingeniería inversa...
jpa setup --database MYSQL --provider HIBERNATE --databaseName mi_base_datos --hostName mi_ip --userName mi_usuario --password mi_contraseña

//Luego se puede comprobar si se ha realizaco la conexión bien
database introspect --schema nombre_de_mi_esquema

// si te dice que no encuentra el driver puedes instalarlo así
addon search mysql
//te deberia salir una lista con los drivers que puedes instalar para a continuación
addon install id --searchResultId xx    
//xx sería el codigo del driver mostrado en el listado que saca el anterior comando...

//En mi caso me dió problemas y el de mysql no me lo instaló bien así que lo instale a mano.
//Para ello me lo descargue y lo instalé con el siguiente comando:
osgi start --url file:///C:\\mysql-connector-java-5.1.18.jar

//Ahora ya se puede crear las entidades a partir de la BD.
database reverse engineer --schema nombre_de_mi_esquema --package ~.domain --includeTables "tab_*"

//se crea el negocio y la presentación para spring mvc
web mvc setup
web mvc all --package ~.web
web mvc finder all

//añadimos la internacionalización
web mvc language --code en
web mvc language --code es

//Seleccionamos el nivel de log
logging setup --level INFO

Hasta aquí ya tendriamos un el proyecto funcionando, podriamos ejecutarlo y ver como se pueden hacer las tareas crud con las pantallas con los estilos por defecto de gvNIX. Ver guia de estilos gvNIX.

Si quisiéramos cambiar la presentación para usar por ejemplo bootstrap deberiamos seguir a continuación los siguientes pasos:

// Añadimos jQuery al proyecto
web mvc jquery setup

// Instalamos Bootstrap
web mvc bootstrap setup

// Instalamos el componente datatables
web mvc datatables setup 

// Añadimos JQuery a todos los archivos
web mvc jquery all

// Se crea los datatables de todos los listados
web mvc datatables all

// Se actualiza bootstrap para que importe los ficheros datatables
web mvc bootstrap update

//Instalamos el security
security setup

// Seactualiza bootstrap para que importe los ficheros del security
web mvc bootstrap update

// Instalamos reports en el proyecto
web report setup

// Creamos un report 
web report add --controller ~.web.SampleController --reportName samplereport

Notas:
web mvc bootstrap setup
Este comando importará e instalará todos los recursos necesarios para utilizar bootstrap. Además, modificará la estructura de algunos tagx existentes para que funcionen correctamente con Bootstrap y modificará todas las vistas de la aplicación para que utilicen los tags de JQuery.

web mvc bootstrap update
Este comando permite regenerar de forma correcta todos los componentes importados por Bootstrap. En caso de una modificación incorrecta de estos componentes, podremos regenerarlos para que vuelvan a su estado inicial.



Documentación oficial versión 1.4.1

Crear aplicación a partir de la Base de Datos con Spring Roo

Paso 1: Instalar Spring Roo
– En primer lugar necesitas tener instalado Java y Maven.
– A continuación te descargas Spring Roo de la página oficial.
– Se descomprime en un directorio y se añade al path. En windows seria añadir en el path $ROO_HOME\bin siendo $ROO_HOME el directorio donde lo has descomprimido.
– Para comprobar que se ha instalado correctamente podemos ejecutar directamente en la consola el siguiente comando.
roo quit
Si todo va bien debería mostrarnos el logo de Spring Roo.

Paso 2: Crear un proyecto de Spring Roo
Se puede crear de distintas formas:
– Con el STS se puede crear un proyecto de Spring Roo como tal.
– Con el eclipse también se podría añadiendo el plugin de Spring Roo.
– Y por último también podriamos utilizar directamente la consola para su creación.

De esta forma, los comandos para la creación de un proyecto a partir de una BD Mysql serían:

//Primero se configura la BD de la que luego se hará ingeniería inversa...
jpa setup --database MYSQL --provider HIBERNATE --databaseName nombre_de_mi_esquema --hostName ip_de_mi_mysql --userName mi_usuario --password mi_contraseña

//Luego se puede comprobar si se ha realizaco la conexión bien
database introspect --schema nombre_de_mi_esquema

// si te dice que no encuentra el driver puedes instalarlo así
addon search mysql
//te deberia salir una lista con los drivers que puedes instalar para a continuación
addon install id --searchResultId xx    
//xx sería el codigo del driver mostrado en el listado que saca el anterior comando...

//En mi caso me dió problemas y el de mysql no me lo instaló bien así que lo instale a mano.
//Para ello me lo descargue y lo instalé con el siguiente comando:
osgi start --url file:///C:\\mysql-connector-java-5.1.18.jar

//Ahora ya se puede crear las entidades a partir de la BD.
database reverse engineer --schema nombre_de_mi_esquema --package ~.domain --includeTables "tab_*"

Por último sería añadir el resto del negocio y la presentación. Para ello pongo dos ejemplos: MVC y JSF.

//Usando Spring MVC
web mvc setup
web mvc all --package ~.web

Usando JSF
web jsf setup --implementation --library --theme
web jsf all --package ~.web

Como nota decir que se lía algo con las tablas con clave primaria múltiple (hay que quitar unos errores que salen en los ficheros *.aj que crea).

Puedes probar a ejecutar el proyecto simplemente con la instrucción de manve
mvn clean install jetty:run

Documentación:
Ejemplo de creación de un proyecto con STS. Ver
Ejemplo de creación de un proyecto con el plugin de eclipse para Spring Roo. Ver

Error 1130 (HY000): Host » is not allowed to connect to this MySQL server

Cuando al intentar conectar a un MySQL desde otro servidor te puede dar este error. Esto es debido a que no tiener permisos para acceder con el usuario que estas intentándolo desde tu host.

-
- Error 1130 (HY000): Host '' is not allowed to connect to this MySQL server
-

Por ejemplo si el usuario que estas usando es root, puedes comprobar desde que sitios se puede acceder utilizando esta select:

SELECT host FROM mysql.user WHERE User = 'root';

Si esa query no devuelve ningún resultado con tu ip, deberás añadirla. Para ello:

CREATE USER 'root'@'tu_ip' IDENTIFIED BY 'contraseña';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'tu_ip';
FLUSH PRIVILEGES;

Nota: si deseas dar permisos a todas las ips, en lugar de ‘tu_ip’ puedes poner ‘%’.

Base de datos NoSQL con PostgreSQL 9.4

PostgreSQL te permite crear base de datos NoSQL, para ello te facilita varias soluciones:

Tipos de datos como HSTORE
Consiste en un almacenamiento tipo clave valor. De tal forma que por ejemplo tendriamos:

create table ejemploHstore(id SERIAL, nombre TEXT, ejemploHstore HSTORE, primary key (id));

insert into ejemploHstore (nombre, ejemploHstore) values ('dato1', 'subdat01=>sss, subdat02=>qwe, subdat03=>prueba');

select * from ejemploHstore;
select ejemploHstore->'subdat01' from ejemploHstore;

Tiene soporte GIN y GIST para generación de índices en columnasHSTORE.
GIN es tres veces más rápido buscando, tarda tres veces más en construirse y es más lento en actualizaciones. Además, ocupa entre dos y tres veces más que GIST
Lo normal es utilizar para datos estáticos, GIN, y para datos dinámicos GIST.

Documentación

Soporte para JSON
Json para representación en texto.

create table ejemploJson(id SERIAL, ejemploJson json, primary key (id));

insert into ejemploJson (ejemploJson) values ('{"subdat01":"sss", "subdat02":"qwe", "subdat03":"prueba"}');

select * from ejemploJson;
select ejemploJson->'subdat01' from ejemploJson;

El almacenamiento en JSON permite validación de documentos y su acceso es similar al HSTORE

Documentación

Soporte para JSONB
Jsonb para representación binario. No se debe confundir jsonb con bson de mongodb, ya que no es lo mismo.
Tiene un formato compacto y eficiente, permite acceso avanzado y operadores de comparación. Así como GIN,GIST, hash y btree

Tiene un tamaño reducido en disco, muy inferior al tamaño JSON. Se organiza como un diccionario (una tabla hash), cuyo acceso es
tremendamente rápido, sin embargo, el orden no es preservado.
JSONB es compatible con las operaciones JSON

Tutorial jsonb

Codemotion ES: Feliz 15 aniversario, SQL Injection

Charla impartida por Chema Alonso en el Codemotion del 2013. Habla sobre el SQL Injection.

Concatenar múltiples filas de una columna en una sentencia SQL para Oracle

Ejemplo:

Tenemos 1 tabla llamada ciudades, con los campos: id, nombreCiudad y nombrePais. Y queremos sacar un listado de todos los paises con sus ciudades concatenadas separadas por una coma. Para ello bastaria con ejecutar esta sentencia:

Select nombrePais, rtrim(xmlagg(xmlelement(ciudades, nombreCiudad ||’,’)).extract(‘//text()’), ‘,’)
from ciudades group by nombrePais;

Funciones utilizadas
rtrim – Se usa para quitar la la última coma de la cadena formada.
xmlagg – Retorna una colección de nodos en formato XMLFormat object, con todas las ciudades de cada pais (ya que hemos agrupado por pais en el group by).
xmlelement – Se utiliza para generar el formato que van a tener los nodos. El primer campo el es identificador del tag que se le asigna dentro del xml y el segundo campo el contenido de ese tag.
extract – En este caso se usa para extraer del docuemtno xml generado el texto.

Nota: Si en lugar de campos separados por comas se deseara separar las ciudades con saltos de línea se podría hacer lo siguiente:

Select nombrePais, rtrim(xmlagg(xmlelement(ciudades, nombreCiudad || CHR(13) || CHR(10))).extract(‘//text()’), CHR(13) || CHR(10))
from ciudades group by nombrePais;

Ejecutar archivos de scripts T-SQL mediante sqlcmd

En SQLServer Si se desea se pueden ejecutar scripts mediante el comando SQLCMD. Es tan sencillo como ejecutar este comando desde la consola de MS-DOS:

sqlcmd -S host_ip,port\instancia -P usuario -U contraseña 
                                 -i C:\my_script_data.sql  -o C:\out.txt

-S — Nos indica el host y la instancia de la BD.
-P — Se corresponde con el usuario con el que deseas conectarte.
-U — Se corresponde con la contraseña de ese usuario.
-i — La ruta completa al script que deseas ejecutar.
-o — La ruta completa al fichero que deseas volcar la salida de la ejecución del script.

Más info