Author: brmeyer Date: 2013-06-29 13:52:40 -0400 (Sat, 29 Jun 2013) New Revision: 21135 Added: annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/ annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/DestinationEntity.java annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/FromEntity.java annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/NamedNativeQueryTest.java Modified: annotations/branches/v3_4_0_GA_CP/src/main/java/org/hibernate/cfg/annotations/QueryBinder.java Log: HHH-4412 JBPAPP-10799 bulk update with native sql queries Modified: annotations/branches/v3_4_0_GA_CP/src/main/java/org/hibernate/cfg/annotations/QueryBinder.java =================================================================== --- annotations/branches/v3_4_0_GA_CP/src/main/java/org/hibernate/cfg/annotations/QueryBinder.java 2013-06-29 16:15:18 UTC (rev 21134) +++ annotations/branches/v3_4_0_GA_CP/src/main/java/org/hibernate/cfg/annotations/QueryBinder.java 2013-06-29 17:52:40 UTC (rev 21135) @@ -48,7 +48,7 @@ queryName, getBoolean( queryName, "org.hibernate.cacheable", hints ), getString( queryName, "org.hibernate.cacheRegion", hints ), - getInteger( queryName, "org.hibernate.timeout", hints ), + getTimeout( queryName, hints ), getInteger( queryName, "org.hibernate.fetchSize", hints ), getFlushMode( queryName, hints ), getCacheMode( queryName, hints ), @@ -84,7 +84,7 @@ null, getBoolean( queryName, "org.hibernate.cacheable", hints ), getString( queryName, "org.hibernate.cacheRegion", hints ), - getInteger( queryName, "org.hibernate.timeout", hints ), + getTimeout( queryName, hints ), getInteger( queryName, "org.hibernate.fetchSize", hints ), getFlushMode( queryName, hints ), getCacheMode( queryName, hints ), @@ -105,7 +105,7 @@ null, getBoolean( queryName, "org.hibernate.cacheable", hints ), getString( queryName, "org.hibernate.cacheRegion", hints ), - getInteger( queryName, "org.hibernate.timeout", hints ), + getTimeout( queryName, hints ), getInteger( queryName, "org.hibernate.fetchSize", hints ), getFlushMode( queryName, hints ), getCacheMode( queryName, hints ), @@ -116,7 +116,21 @@ ); } else { - throw new NotYetImplementedException( "Pure native scalar queries are not yet supported" ); + query = new NamedSQLQueryDefinition( + queryName, + new NativeSQLQueryReturn[0], + null, + getBoolean( queryName, "org.hibernate.cacheable", hints ), + getString( queryName, "org.hibernate.cacheRegion", hints ), + getTimeout( queryName, hints ), + getInteger( queryName, "org.hibernate.fetchSize", hints ), + getFlushMode( queryName, hints ), + getCacheMode( queryName, hints ), + getBoolean( queryName, "org.hibernate.readOnly", hints ), + getString( queryName, "org.hibernate.comment", hints ), + null, + getBoolean( queryName, "org.hibernate.callable", hints ) + ); } if ( isDefault ) { mappings.addDefaultSQLQuery( queryAnn.name(), query ); @@ -386,4 +400,18 @@ } return null; } + + private static Integer getTimeout(String queryName, QueryHint[] hints) { + Integer timeout = getInteger(queryName, + "javax.persistence.query.timeout", hints); + + if (timeout != null) { + // convert milliseconds to seconds + timeout = (int) Math.round(timeout.doubleValue() / 1000.0); + } else { + // timeout is already in seconds + timeout = getInteger(queryName, "org.hibernate.timeout", hints); + } + return timeout; + } } Added: annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/DestinationEntity.java =================================================================== --- annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/DestinationEntity.java (rev 0) +++ annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/DestinationEntity.java 2013-06-29 17:52:40 UTC (rev 21135) @@ -0,0 +1,99 @@ +/* + * Hibernate, Relational Persistence for Idiomatic Java + * + * JBoss, Home of Professional Open Source + * Copyright 2013 Red Hat Inc. and/or its affiliates and other contributors + * as indicated by the @authors tag. All rights reserved. + * See the copyright.txt in the distribution for a + * full listing of individual contributors. + * + * This copyrighted material is made available to anyone wishing to use, + * modify, copy, or redistribute it subject to the terms and conditions + * of the GNU Lesser General Public License, v. 2.1. + * This program is distributed in the hope that it will be useful, but WITHOUT A + * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A + * PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. + * You should have received a copy of the GNU Lesser General Public License, + * v.2.1 along with this distribution; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, + * MA 02110-1301, USA. + */ +package org.hibernate.test.jpa.ql; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.GeneratedValue; +import javax.persistence.Id; +import javax.persistence.JoinColumn; +import javax.persistence.ManyToOne; +import javax.persistence.NamedNativeQueries; +import javax.persistence.NamedNativeQuery; +import javax.persistence.Table; + +/** + * @author Janario Oliveira + */ +@Entity +@Table(name = "destination_entity") +@NamedNativeQueries({ + @NamedNativeQuery(name = "DestinationEntity.insertSelect", query = "insert into destination_entity(id, from_id, fullNameFrom) " + + " select fe.id, fe.id, fe.name||fe.lastName from from_entity fe where fe.id in (:ids)"), + @NamedNativeQuery(name = "DestinationEntity.insert", query = "insert into destination_entity(id, from_id, fullNameFrom) " + + "values (:generatedId, :fromId, :fullName)"), + @NamedNativeQuery(name = "DestinationEntity.update", query = "update destination_entity set from_id=:idFrom, fullNameFrom=:fullName" + + " where id in (:ids)"), + @NamedNativeQuery(name = "DestinationEntity.delete", query = "delete from destination_entity where id in (:ids)"), + @NamedNativeQuery(name = "DestinationEntity.selectIds", query = "select id, from_id, fullNameFrom from destination_entity where id in (:ids)") }) +public class DestinationEntity { + + @Id + @GeneratedValue + Integer id; + @ManyToOne(optional = false) + @JoinColumn(name = "from_id") + FromEntity from; + @Column(nullable = false) + String fullNameFrom; + + public DestinationEntity() { + } + + public DestinationEntity(FromEntity from, String fullNameFrom) { + this.from = from; + this.fullNameFrom = fullNameFrom; + } + + @Override + public int hashCode() { + final int prime = 31; + int result = 1; + result = prime * result + ( ( from == null ) ? 0 : from.hashCode() ); + result = prime * result + ( ( fullNameFrom == null ) ? 0 : fullNameFrom.hashCode() ); + return result; + } + + @Override + public boolean equals(Object obj) { + if ( this == obj ) + return true; + if ( obj == null ) + return false; + if ( getClass() != obj.getClass() ) + return false; + DestinationEntity other = (DestinationEntity) obj; + if ( from == null ) { + if ( other.from != null ) + return false; + } + else if ( !from.equals( other.from ) ) + return false; + if ( fullNameFrom == null ) { + if ( other.fullNameFrom != null ) + return false; + } + else if ( !fullNameFrom.equals( other.fullNameFrom ) ) + return false; + return true; + } + +} Added: annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/FromEntity.java =================================================================== --- annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/FromEntity.java (rev 0) +++ annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/FromEntity.java 2013-06-29 17:52:40 UTC (rev 21135) @@ -0,0 +1,77 @@ +/* + * Hibernate, Relational Persistence for Idiomatic Java + * + * JBoss, Home of Professional Open Source + * Copyright 2013 Red Hat Inc. and/or its affiliates and other contributors + * as indicated by the @authors tag. All rights reserved. + * See the copyright.txt in the distribution for a + * full listing of individual contributors. + * + * This copyrighted material is made available to anyone wishing to use, + * modify, copy, or redistribute it subject to the terms and conditions + * of the GNU Lesser General Public License, v. 2.1. + * This program is distributed in the hope that it will be useful, but WITHOUT A + * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A + * PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. + * You should have received a copy of the GNU Lesser General Public License, + * v.2.1 along with this distribution; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, + * MA 02110-1301, USA. + */ +package org.hibernate.test.jpa.ql; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.GeneratedValue; +import javax.persistence.Id; +import javax.persistence.Table; + +/** + * @author Janario Oliveira + */ +@Entity +@Table(name = "from_entity") +public class FromEntity { + + @Id + @GeneratedValue + Integer id; + @Column(nullable = false) + String name; + @Column(nullable = false) + String lastName; + + public FromEntity() { + } + + public FromEntity(String name, String lastName) { + this.name = name; + this.lastName = lastName; + } + + @Override + public int hashCode() { + int hash = 5; + hash = 53 * hash + ( this.name != null ? this.name.hashCode() : 0 ); + hash = 53 * hash + ( this.lastName != null ? this.lastName.hashCode() : 0 ); + return hash; + } + + @Override + public boolean equals(Object obj) { + if ( obj == null ) { + return false; + } + if ( getClass() != obj.getClass() ) { + return false; + } + final FromEntity other = (FromEntity) obj; + if ( ( this.name == null ) ? ( other.name != null ) : !this.name.equals( other.name ) ) { + return false; + } + if ( ( this.lastName == null ) ? ( other.lastName != null ) : !this.lastName.equals( other.lastName ) ) { + return false; + } + return true; + } +} Added: annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/NamedNativeQueryTest.java =================================================================== --- annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/NamedNativeQueryTest.java (rev 0) +++ annotations/branches/v3_4_0_GA_CP/src/test/java/org/hibernate/test/jpa/ql/NamedNativeQueryTest.java 2013-06-29 17:52:40 UTC (rev 21135) @@ -0,0 +1,312 @@ +/* + * Hibernate, Relational Persistence for Idiomatic Java + * + * JBoss, Home of Professional Open Source + * Copyright 2013 Red Hat Inc. and/or its affiliates and other contributors + * as indicated by the @authors tag. All rights reserved. + * See the copyright.txt in the distribution for a + * full listing of individual contributors. + * + * This copyrighted material is made available to anyone wishing to use, + * modify, copy, or redistribute it subject to the terms and conditions + * of the GNU Lesser General Public License, v. 2.1. + * This program is distributed in the hope that it will be useful, but WITHOUT A + * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A + * PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. + * You should have received a copy of the GNU Lesser General Public License, + * v.2.1 along with this distribution; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, + * MA 02110-1301, USA. + */ +package org.hibernate.test.jpa.ql; + +import java.util.ArrayList; +import java.util.Collections; +import java.util.List; + +import org.hibernate.Query; +import org.hibernate.Session; +import org.hibernate.dialect.MySQLDialect; +import org.hibernate.dialect.SQLServerDialect; +import org.hibernate.junit.SkipForDialect; +import org.hibernate.test.annotations.TestCase; + +/** + * @author Janario Oliveira + */ +public class NamedNativeQueryTest extends TestCase { + + private FromEntity createFrom(String name, String lastName) { + Session session = openSession(); + session.getTransaction().begin(); + FromEntity fromEntity = new FromEntity( name, lastName ); + session.save( fromEntity ); + session.getTransaction().commit(); + session.close(); + return fromEntity; + } + + private DestinationEntity createDestination(FromEntity fromEntity, String fullName) { + final DestinationEntity destinationEntity = new DestinationEntity( fromEntity, fullName ); + + Session session = openSession(); + session.getTransaction().begin(); + session.save( destinationEntity ); + session.getTransaction().commit(); + session.close(); + return destinationEntity; + } + + @SuppressWarnings("unchecked") + private List<DestinationEntity> findDestinationByIds(List<Integer> ids) { + Session session = openSession(); + List<DestinationEntity> list = session + .createQuery( "from DestinationEntity de where de.id in (:ids) order by id" ) + .setParameterList( "ids", ids ).list(); + session.close(); + return list; + } + + public void testSingleSelect() { + final String name = "Name"; + final String lastName = "LastName"; + final String fullName = name + " " + lastName; + final DestinationEntity destination = createDestination( createFrom( name, lastName ), fullName ); + + Session session = openSession(); + Query select = session.getNamedQuery( "DestinationEntity.selectIds" ); + select.setParameterList( "ids", Collections.singletonList( destination.id ) ); + Object[] unique = (Object[]) select.uniqueResult(); + session.close(); + + // Compare the Strings, not the actual IDs. Can come back as, for ex, + // a BigDecimal in Oracle. + assertEquals( destination.id + "", unique[0] + "" ); + assertEquals( destination.from.id + "", unique[1] + "" ); + assertEquals( destination.fullNameFrom, unique[2] ); + } + + public void testMultipleSelect() { + final String name = "Name"; + final String lastName = "LastName"; + final List<Integer> ids = new ArrayList<Integer>(); + final int quantity = 10; + final List<DestinationEntity> destinations = new ArrayList<DestinationEntity>(); + for ( int i = 0; i < quantity; i++ ) { + DestinationEntity createDestination = createDestination( createFrom( name + i, lastName + i ), name + i + + lastName + i ); + ids.add( createDestination.id ); + destinations.add( createDestination ); + } + + Session session = openSession(); + Query select = session.getNamedQuery( "DestinationEntity.selectIds" ); + select.setParameterList( "ids", ids ); + List list = select.list(); + session.close(); + + assertEquals( quantity, list.size() ); + for ( int i = 0; i < list.size(); i++ ) { + Object[] object = (Object[]) list.get( i ); + DestinationEntity destination = destinations.get( i ); + // Compare the Strings, not the actual IDs. Can come back as, for ex, + // a BigDecimal in Oracle. + assertEquals( destination.id + "", object[0] + "" ); + assertEquals( destination.from.id + "", object[1] + "" ); + assertEquals( destination.fullNameFrom, object[2] ); + } + } + + public void testInsertSingleValue() { + final String name = "Name"; + final String lastName = "LastName"; + final String fullName = name + " " + lastName; + final FromEntity fromEntity = createFrom( name, lastName ); + final int id = 10000;// id fake + + Session session = openSession(); + session.getTransaction().begin(); + Query insert = session.getNamedQuery( "DestinationEntity.insert" ); + insert.setParameter( "generatedId", id ); + insert.setParameter( "fromId", fromEntity.id ); + insert.setParameter( "fullName", fullName ); + int executeUpdate = insert.executeUpdate(); + assertEquals( 1, executeUpdate ); + session.getTransaction().commit(); + session.close(); + + session = openSession(); + DestinationEntity get = (DestinationEntity) session.get( DestinationEntity.class, id ); + session.close(); + + assertEquals( fromEntity, get.from ); + assertEquals( fullName, get.fullNameFrom ); + } + + @SkipForDialect( value = { MySQLDialect.class, SQLServerDialect.class }, + comment = "MySQL appears to have trouble with fe.id selected twice in one statement. SQL Server does not support the || operator.") + // TODO: Re-form DestinationEntity.insertSelect to something more supported? + public void testInsertMultipleValues() { + final String name = "Name"; + final String lastName = "LastName"; + final List<Integer> ids = new ArrayList<Integer>(); + final int quantity = 10; + final List<FromEntity> froms = new ArrayList<FromEntity>(); + for ( int i = 0; i < quantity; i++ ) { + FromEntity fe = createFrom( name + i, lastName + i ); + froms.add( fe ); + ids.add( fe.id ); + } + + Session session = openSession(); + session.getTransaction().begin(); + Query insertSelect = session.getNamedQuery( "DestinationEntity.insertSelect" ); + insertSelect.setParameterList( "ids", ids ); + int executeUpdate = insertSelect.executeUpdate(); + assertEquals( quantity, executeUpdate ); + + session.getTransaction().commit(); + session.close(); + + List<DestinationEntity> list = findDestinationByIds( ids ); + assertEquals( quantity, list.size() ); + + for ( int i = 0; i < quantity; i++ ) { + DestinationEntity de = (DestinationEntity) list.get( i ); + FromEntity from = froms.get( i ); + assertEquals( from, de.from ); + assertEquals( from.name + from.lastName, de.fullNameFrom ); + } + } + + public void testUpdateSingleValue() { + final String name = "Name"; + final String lastName = "LastName"; + final String fullName = name + " " + lastName; + + final FromEntity fromEntity = createFrom( name, lastName ); + final DestinationEntity destinationEntity = createDestination( fromEntity, fullName ); + + final String inverseFullName = lastName + " " + name; + final FromEntity anotherFrom = createFrom( lastName, name ); + + Session session = openSession(); + session.getTransaction().begin(); + Query update = session.getNamedQuery( "DestinationEntity.update" ); + update.setParameter( "idFrom", anotherFrom.id ); + update.setParameter( "fullName", inverseFullName ); + update.setParameterList( "ids", Collections.singletonList( destinationEntity.id ) ); + + int executeUpdate = update.executeUpdate(); + assertEquals( 1, executeUpdate ); + + session.getTransaction().commit(); + session.close(); + + session = openSession(); + DestinationEntity get = (DestinationEntity) session.get( DestinationEntity.class, destinationEntity.id ); + + assertEquals( anotherFrom, get.from ); + assertEquals( inverseFullName, get.fullNameFrom ); + session.close(); + } + + public void testUpdateMultipleValues() { + final String name = "Name"; + final String lastName = "LastName"; + final List<Integer> ids = new ArrayList<Integer>(); + final int quantity = 10; + final List<DestinationEntity> destinations = new ArrayList<DestinationEntity>(); + for ( int i = 0; i < quantity; i++ ) { + FromEntity fe = createFrom( name + i, lastName + i ); + DestinationEntity destination = createDestination( fe, fe.name + fe.lastName ); + destinations.add( destination ); + ids.add( destination.id ); + } + + final String inverseFullName = lastName + " " + name; + final FromEntity anotherFrom = createFrom( lastName, name ); + + Session session = openSession(); + session.getTransaction().begin(); + Query update = session.getNamedQuery( "DestinationEntity.update" ); + update.setParameter( "idFrom", anotherFrom.id ); + update.setParameter( "fullName", inverseFullName ); + update.setParameterList( "ids", ids ); + + int executeUpdate = update.executeUpdate(); + assertEquals( quantity, executeUpdate ); + + session.getTransaction().commit(); + session.close(); + + List<DestinationEntity> list = findDestinationByIds( ids ); + assertEquals( quantity, list.size() ); + + for ( int i = 0; i < quantity; i++ ) { + DestinationEntity updated = (DestinationEntity) list.get( i ); + + assertEquals( anotherFrom, updated.from ); + assertEquals( inverseFullName, updated.fullNameFrom ); + } + } + + public void testDeleteSingleValue() { + final String name = "Name"; + final String lastName = "LastName"; + final String fullName = name + " " + lastName; + + final FromEntity fromEntity = createFrom( name, lastName ); + final DestinationEntity destinationEntity = createDestination( fromEntity, fullName ); + + Session session = openSession(); + session.getTransaction().begin(); + Query delete = session.getNamedQuery( "DestinationEntity.delete" ); + delete.setParameterList( "ids", Collections.singletonList( destinationEntity.id ) ); + + int executeUpdate = delete.executeUpdate(); + assertEquals( 1, executeUpdate ); + + session.getTransaction().commit(); + session.close(); + + session = openSession(); + DestinationEntity get = (DestinationEntity) session.get( DestinationEntity.class, destinationEntity.id ); + session.close(); + + assertNull( get ); + } + + public void testDeleteMultipleValues() { + final String name = "Name"; + final String lastName = "LastName"; + final List<Integer> ids = new ArrayList<Integer>(); + final int quantity = 10; + final List<DestinationEntity> destinations = new ArrayList<DestinationEntity>(); + for ( int i = 0; i < quantity; i++ ) { + FromEntity fe = createFrom( name + i, lastName + i ); + DestinationEntity destination = createDestination( fe, fe.name + fe.lastName ); + destinations.add( destination ); + ids.add( destination.id ); + } + + Session session = openSession(); + session.getTransaction().begin(); + Query delete = session.getNamedQuery( "DestinationEntity.delete" ); + delete.setParameterList( "ids", ids ); + + int executeUpdate = delete.executeUpdate(); + assertEquals( quantity, executeUpdate ); + + session.getTransaction().commit(); + session.close(); + + List<DestinationEntity> list = findDestinationByIds( ids ); + assertTrue( list.isEmpty() ); + } + + @Override + protected Class[] getAnnotatedClasses() { + return new Class[] { FromEntity.class, DestinationEntity.class }; + } +}