Author: gbadner Date: 2009-06-17 16:28:59 -0400 (Wed, 17 Jun 2009) New Revision: 16823 Added: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/ImageHolder.java core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/TextHolder.java core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/Mappings.hbm.xml core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/SQLServerCustomSQLTest.java Modified: core/trunk/core/src/main/java/org/hibernate/dialect/Dialect.java core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/CustomSQLTestSupport.java core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/db2/Mappings.hbm.xml core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/mysql/Mappings.hbm.xml core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/oracle/Mappings.hbm.xml core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sybase/Mappings.hbm.xml core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sybase/SybaseCustomSQLTest.java core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/query/NativeSQLQueries.hbm.xml core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/query/NativeSQLQueriesTest.java Log: HHH-3892 : Add support for "text" and "image" Hibernate types for native SQL queries Modified: core/trunk/core/src/main/java/org/hibernate/dialect/Dialect.java =================================================================== --- core/trunk/core/src/main/java/org/hibernate/dialect/Dialect.java 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/core/src/main/java/org/hibernate/dialect/Dialect.java 2009-06-17 20:28:59 UTC (rev 16823) @@ -216,6 +216,8 @@ registerHibernateType( Types.TIMESTAMP, Hibernate.TIMESTAMP.getName() ); registerHibernateType( Types.VARCHAR, Hibernate.STRING.getName() ); registerHibernateType( Types.VARBINARY, Hibernate.BINARY.getName() ); + registerHibernateType( Types.LONGVARCHAR, Hibernate.TEXT.getName() ); + registerHibernateType( Types.LONGVARBINARY, Hibernate.IMAGE.getName() ); registerHibernateType( Types.NUMERIC, Hibernate.BIG_DECIMAL.getName() ); registerHibernateType( Types.DECIMAL, Hibernate.BIG_DECIMAL.getName() ); registerHibernateType( Types.BLOB, Hibernate.BLOB.getName() ); Added: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/ImageHolder.java =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/ImageHolder.java (rev 0) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/ImageHolder.java 2009-06-17 20:28:59 UTC (rev 16823) @@ -0,0 +1,65 @@ +/* + * Copyright (c) 2009, Red Hat Middleware LLC or third-party contributors as + * indicated by the @author tags or express copyright attribution + * statements applied by the authors. All third-party contributions are + * distributed under license by Red Hat Middleware LLC. + * + * 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, as published by the Free Software Foundation. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY 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 + * along with this distribution; if not, write to: + * Free Software Foundation, Inc. + * 51 Franklin Street, Fifth Floor + * Boston, MA 02110-1301 USA + */ +package org.hibernate.test.sql.hand; + +/** + * @author Gail Badner + */ +public class ImageHolder { + private Long id; + private byte[] photo; + + public ImageHolder(byte[] photo) { + this.photo = photo; + } + + public ImageHolder() { + } + + /** + * @return Returns the id. + */ + public Long getId() { + return id; + } + + /** + * @param id The id to set. + */ + public void setId(Long id) { + this.id = id; + } + + /** + * @return Returns the photo. + */ + public byte[] getPhoto() { + return photo; + } + + /** + * @param photo The photo to set. + */ + public void setPhoto(byte[] photo) { + this.photo = photo; + } +} \ No newline at end of file Added: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/TextHolder.java =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/TextHolder.java (rev 0) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/TextHolder.java 2009-06-17 20:28:59 UTC (rev 16823) @@ -0,0 +1,65 @@ +/* + * Copyright (c) 2009, Red Hat Middleware LLC or third-party contributors as + * indicated by the @author tags or express copyright attribution + * statements applied by the authors. All third-party contributions are + * distributed under license by Red Hat Middleware LLC. + * + * 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, as published by the Free Software Foundation. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY 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 + * along with this distribution; if not, write to: + * Free Software Foundation, Inc. + * 51 Franklin Street, Fifth Floor + * Boston, MA 02110-1301 USA + */ +package org.hibernate.test.sql.hand; + +/** + * @author Gail Badner + */ +public class TextHolder { + private Long id; + private String description; + + public TextHolder(String description) { + this.description = description; + } + + public TextHolder() { + } + + /** + * @return Returns the id. + */ + public Long getId() { + return id; + } + + /** + * @param id The id to set. + */ + public void setId(Long id) { + this.id = id; + } + + /** + * @return Returns the description. + */ + public String getDescription() { + return description; + } + + /** + * @param description The description to set. + */ + public void setDescription(String description) { + this.description = description; + } +} \ No newline at end of file Modified: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/CustomSQLTestSupport.java =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/CustomSQLTestSupport.java 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/CustomSQLTestSupport.java 2009-06-17 20:28:59 UTC (rev 16823) @@ -7,9 +7,12 @@ import org.hibernate.LockMode; import org.hibernate.Session; import org.hibernate.Transaction; +import org.hibernate.util.ArrayHelper; import org.hibernate.test.sql.hand.Employment; import org.hibernate.test.sql.hand.Organization; import org.hibernate.test.sql.hand.Person; +import org.hibernate.test.sql.hand.TextHolder; +import org.hibernate.test.sql.hand.ImageHolder; import org.hibernate.junit.functional.DatabaseSpecificFunctionalTestCase; /** @@ -91,4 +94,81 @@ s.close(); } + public void testTextProperty() { + Session s = openSession(); + Transaction t = s.beginTransaction(); + String description = buildLongString( 15000, 'a' ); + TextHolder holder = new TextHolder( description ); + s.save( holder ); + t.commit(); + s.close(); + + s = openSession(); + t = s.beginTransaction(); + holder = ( TextHolder ) s.get( TextHolder.class, holder.getId() ); + assertEquals( description, holder.getDescription() ); + description = buildLongString( 15000, 'b' ); + holder.setDescription( description ); + s.save( holder ); + t.commit(); + s.close(); + + s = openSession(); + t = s.beginTransaction(); + holder = ( TextHolder ) s.get( TextHolder.class, holder.getId() ); + assertEquals( description, holder.getDescription() ); + s.delete( holder ); + t.commit(); + s.close(); + } + + public void testImageProperty() { + Session s = openSession(); + Transaction t = s.beginTransaction(); + byte[] photo = buildLongByteArray( 15000, true ); + ImageHolder holder = new ImageHolder( photo ); + s.save( holder ); + t.commit(); + s.close(); + + s = openSession(); + t = s.beginTransaction(); + holder = ( ImageHolder ) s.get( ImageHolder.class, holder.getId() ); + assertTrue( ArrayHelper.isEquals( photo, holder.getPhoto() ) ); + photo = buildLongByteArray( 15000, false ); + holder.setPhoto( photo ); + s.save( holder ); + t.commit(); + s.close(); + + s = openSession(); + t = s.beginTransaction(); + holder = ( ImageHolder ) s.get( ImageHolder.class, holder.getId() ); + assertTrue( ArrayHelper.isEquals( photo, holder.getPhoto() ) ); + s.delete( holder ); + t.commit(); + s.close(); + } + + private String buildLongString(int size, char baseChar) { + StringBuffer buff = new StringBuffer(); + for( int i = 0; i < size; i++ ) { + buff.append( baseChar ); + } + return buff.toString(); + } + + private byte[] buildLongByteArray(int size, boolean on) { + byte[] data = new byte[size]; + data[0] = mask( on ); + for ( int i = 0; i < size; i++ ) { + data[i] = mask( on ); + on = !on; + } + return data; + } + + private byte mask(boolean on) { + return on ? ( byte ) 1 : ( byte ) 0; + } } Modified: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/db2/Mappings.hbm.xml =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/db2/Mappings.hbm.xml 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/db2/Mappings.hbm.xml 2009-06-17 20:28:59 UTC (rev 16823) @@ -65,6 +65,36 @@ <sql-delete>DELETE FROM EMPLOYMENT WHERE EMPID=?</sql-delete> </class> + <class name="TextHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="description" type="text" length="15000"/> + <loader query-ref="textholder"/> + <sql-insert> + INSERT INTO TEXTHOLDER + (DESCRIPTION, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE TEXTHOLDER SET DESCRIPTION=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM TEXTHOLDER WHERE ID=?</sql-delete> + </class> + + <class name="ImageHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="photo" type="image" length="15000"/> + <loader query-ref="imageholder"/> + <sql-insert> + INSERT INTO IMAGEHOLDER + (PHOTO, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE IMAGEHOLDER SET PHOTO=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM IMAGEHOLDER WHERE ID=?</sql-delete> + </class> + <resultset name="org-emp-regionCode"> <return-scalar column="regionCode" type="string"/> <return alias="org" class="Organization"/> @@ -183,6 +213,16 @@ { call HIBDB2TST.selectAllEmployments() } </sql-query> + <sql-query name="textholder"> + <return alias="h" class="TextHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, DESCRIPTION AS {h.description} FROM TEXTHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + + <sql-query name="imageholder"> + <return alias="h" class="ImageHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, PHOTO AS {h.photo} FROM IMAGEHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + <!-- DB2 seem to *require* users to specify explicit schema/location when executing a stored procedure *stupid* --> <database-object> <create>CREATE SCHEMA HIBDB2TST</create> Modified: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/mysql/Mappings.hbm.xml =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/mysql/Mappings.hbm.xml 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/mysql/Mappings.hbm.xml 2009-06-17 20:28:59 UTC (rev 16823) @@ -65,6 +65,36 @@ <sql-delete>DELETE FROM EMPLOYMENT WHERE EMPID=?</sql-delete> </class> + <class name="TextHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="description" type="text" length="15000"/> + <loader query-ref="textholder"/> + <sql-insert> + INSERT INTO TEXTHOLDER + (DESCRIPTION, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE TEXTHOLDER SET DESCRIPTION=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM TEXTHOLDER WHERE ID=?</sql-delete> + </class> + + <class name="ImageHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="photo" type="image" length="15000"/> + <loader query-ref="imageholder"/> + <sql-insert> + INSERT INTO IMAGEHOLDER + (PHOTO, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE IMAGEHOLDER SET PHOTO=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM IMAGEHOLDER WHERE ID=?</sql-delete> + </class> + <resultset name="org-emp-regionCode"> <return-scalar column="regionCode" type="string"/> <return alias="org" class="Organization"/> @@ -180,7 +210,17 @@ </return> { call selectAllEmployments() } </sql-query> - + + <sql-query name="textholder"> + <return alias="h" class="TextHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, DESCRIPTION AS {h.description} FROM TEXTHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + + <sql-query name="imageholder"> + <return alias="h" class="ImageHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, PHOTO AS {h.photo} FROM IMAGEHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + <database-object> <create> CREATE PROCEDURE selectAllEmployments () Modified: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/oracle/Mappings.hbm.xml =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/oracle/Mappings.hbm.xml 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/oracle/Mappings.hbm.xml 2009-06-17 20:28:59 UTC (rev 16823) @@ -65,6 +65,36 @@ <sql-delete>DELETE FROM EMPLOYMENT WHERE EMPID=?</sql-delete> </class> + <class name="TextHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="description" type="text" length="15000"/> + <loader query-ref="textholder"/> + <sql-insert> + INSERT INTO TEXTHOLDER + (DESCRIPTION, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE TEXTHOLDER SET DESCRIPTION=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM TEXTHOLDER WHERE ID=?</sql-delete> + </class> + + <class name="ImageHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="photo" type="image" length="15000"/> + <loader query-ref="imageholder"/> + <sql-insert> + INSERT INTO IMAGEHOLDER + (PHOTO, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE IMAGEHOLDER SET PHOTO=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM IMAGEHOLDER WHERE ID=?</sql-delete> + </class> + <resultset name="org-emp-regionCode"> <return-scalar column="regionCode" type="string"/> <return alias="org" class="Organization"/> @@ -139,6 +169,16 @@ ORDER BY STARTDATE ASC </sql-query> + <sql-query name="textholder"> + <return alias="h" class="TextHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, DESCRIPTION AS {h.description} FROM TEXTHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + + <sql-query name="imageholder"> + <return alias="h" class="ImageHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, PHOTO AS {h.photo} FROM IMAGEHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + <database-object> <create> CREATE OR REPLACE FUNCTION testParamHandling (j number, i number) @@ -202,7 +242,7 @@ END; </create> <drop> - DROP PROCEDURE createPerson; + DROP PROCEDURE createPerson </drop> </database-object> Added: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/Mappings.hbm.xml =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/Mappings.hbm.xml (rev 0) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/Mappings.hbm.xml 2009-06-17 20:28:59 UTC (rev 16823) @@ -0,0 +1,287 @@ +<?xml version="1.0"?> +<!DOCTYPE hibernate-mapping PUBLIC + "-//Hibernate/Hibernate Mapping DTD 3.0//EN" + "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> + +<!-- + This mapping demonstrates the use of Hibernate with all-handwritten SQL! + + NOTE: this version is for SQLServer +--> +<hibernate-mapping package="org.hibernate.test.sql.hand" default-access="field"> + + <class name="Organization" table="ORGANIZATION"> + <id name="id" unsaved-value="0" column="ORGID"> + <generator class="increment"/> + </id> + <property name="name" not-null="true" column="NAME"/> + <set name="employments" + inverse="true" + order-by="DUMMY"> + <key column="EMPLOYER"/> <!-- only needed for DDL generation --> + <one-to-many class="Employment"/> + <loader query-ref="organizationEmployments"/> + </set> + <!-- query-list name="currentEmployments" + query-ref="organizationCurrentEmployments"--> + <loader query-ref="organization"/> + <sql-insert>INSERT INTO ORGANIZATION (NAME, ORGID) VALUES ( UPPER(?), ? )</sql-insert> + <sql-update>UPDATE ORGANIZATION SET NAME=UPPER(?) WHERE ORGID=?</sql-update> + <sql-delete>DELETE FROM ORGANIZATION WHERE ORGID=?</sql-delete> + </class> + + <class name="Person" table="PERSON"> + <id name="id" unsaved-value="0" column="PERID"> + <generator class="increment"/> + </id> + <property name="name" not-null="true" column="NAME"/> + <loader query-ref="person"/> + <sql-insert>INSERT INTO PERSON (NAME, PERID) VALUES ( UPPER(?), ? )</sql-insert> + <sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE PERID=?</sql-update> + <sql-delete>DELETE FROM PERSON WHERE PERID=?</sql-delete> + </class> + + <class name="Employment" table="EMPLOYMENT"> + <id name="employmentId" unsaved-value="0" column="EMPID"> + <generator class="increment"/> + </id> + <many-to-one name="employee" column="EMPLOYEE" not-null="true" update="false"/> + <many-to-one name="employer" column="EMPLOYER" not-null="true" update="false"/> + <property name="startDate" column="STARTDATE" not-null="true" update="false" insert="false"/> + <property name="endDate" column="ENDDATE" insert="false"/> + <property name="regionCode" column="REGIONCODE" update="false"/> + <property name="salary" type="org.hibernate.test.sql.hand.MonetaryAmountUserType"> + <column name="VALUE" sql-type="float"/> + <column name="CURRENCY"/> + </property> + <loader query-ref="employment"/> + <sql-insert> + INSERT INTO EMPLOYMENT + (EMPLOYEE, EMPLOYER, STARTDATE, REGIONCODE, VALUE, CURRENCY, EMPID) + VALUES (?, ?, getdate(), UPPER(?), ?, ?, ?) + </sql-insert> + <sql-update>UPDATE EMPLOYMENT SET ENDDATE=?, VALUE=?, CURRENCY=? WHERE EMPID=?</sql-update> + <sql-delete>DELETE FROM EMPLOYMENT WHERE EMPID=?</sql-delete> + </class> + + <class name="TextHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="description" type="text" length="15000"/> + <loader query-ref="textholder"/> + <sql-insert> + INSERT INTO TEXTHOLDER + (DESCRIPTION, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE TEXTHOLDER SET DESCRIPTION=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM TEXTHOLDER WHERE ID=?</sql-delete> + </class> + + <class name="ImageHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="photo" type="image" length="15000"/> + <loader query-ref="imageholder"/> + <sql-insert> + INSERT INTO IMAGEHOLDER + (PHOTO, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE IMAGEHOLDER SET PHOTO=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM IMAGEHOLDER WHERE ID=?</sql-delete> + </class> + + <resultset name="org-emp-regionCode"> + <return-scalar column="regionCode" type="string"/> + <return alias="org" class="Organization"/> + <return-join alias="emp" property="org.employments"/> + </resultset> + + <resultset name="org-emp-person"> + <return alias="org" class="Organization"/> + <return-join alias="emp" property="org.employments"/> + <return-join alias="pers" property="emp.employee"/> + </resultset> + + <sql-query name="person"> + <return alias="p" class="Person" lock-mode="upgrade"/> + SELECT NAME AS {p.name}, PERID AS {p.id} FROM PERSON WHERE PERID=? /*FOR UPDATE*/ + </sql-query> + + <sql-query name="organization"> + <return alias="org" class="Organization"/> + <return-join alias="emp" property="org.employments"/> + SELECT {org.*}, {emp.*} + FROM ORGANIZATION org + LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER + WHERE org.ORGID=? + </sql-query> + + + <!--sql-query name="organization"> + <return alias="org" class="Organization"/> + SELECT NAME AS {org.name}, ORGID AS {org.id} FROM ORGANIZATION + WHERE ORGID=? + </sql-query--> + + <sql-query name="allOrganizationsWithEmployees" flush-mode="never"> + <return alias="org" class="Organization"/> + SELECT DISTINCT org.NAME AS {org.name}, org.ORGID AS {org.id} + FROM ORGANIZATION org + INNER JOIN EMPLOYMENT e ON e.EMPLOYER = org.ORGID + </sql-query> + + + + + + <sql-query name="employment"> + <return alias="emp" class="Employment"/> + SELECT EMPLOYEE AS {emp.employee}, EMPLOYER AS {emp.employer}, + STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate}, + REGIONCODE as {emp.regionCode}, EMPID AS {emp.id} + FROM EMPLOYMENT + WHERE EMPID = ? + </sql-query> + + <sql-query name="organizationEmployments"> + <load-collection alias="empcol" role="Organization.employments"/> + SELECT {empcol.*} + FROM EMPLOYMENT empcol + WHERE EMPLOYER = :id + ORDER BY STARTDATE ASC, EMPLOYEE ASC + </sql-query> + + + <sql-query name="organizationCurrentEmployments"> + <return alias="emp" class="Employment"> + <return-property name="salary"> + <!-- as multi column properties are not supported via the + {}-syntax, we need to provide an explicit column list for salary via <return-property> --> + <return-column name="VALUE"/> + <return-column name="CURRENCY"/> + </return-property> + <!-- Here we are remapping endDate. Notice that we can still use {emp.endDate} in the SQL. --> + <return-property name="endDate" column="myEndDate"/> + </return> + <synchronize table="EMPLOYMENT"/> + SELECT EMPLOYEE AS {emp.employee}, EMPLOYER AS {emp.employer}, + STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate}, + REGIONCODE as {emp.regionCode}, EMPID AS {emp.id}, VALUE, CURRENCY + FROM EMPLOYMENT + WHERE EMPLOYER = :id AND ENDDATE IS NULL + ORDER BY STARTDATE ASC + </sql-query> + + <sql-query name="simpleScalar" callable="true"> + <return-scalar column="name" type="string"/> + <return-scalar column="value" type="long"/> + { call simpleScalar(:number) } + </sql-query> + + <sql-query name="paramhandling" callable="true"> + <return-scalar column="value" type="long"/> + <return-scalar column="value2" type="long"/> + { call paramHandling(?,?) } + </sql-query> + + <sql-query name="paramhandling_mixed" callable="true"> + <return-scalar column="value" type="long" /> + <return-scalar column="value2" type="long" /> + { call paramHandling(?,:second) } + </sql-query> + + <sql-query name="selectAllEmployments" callable="true"> + <return alias="" class="Employment"> + <return-property name="employee" column="EMPLOYEE"/> + <return-property name="employer" column="EMPLOYER"/> + <return-property name="startDate" column="STARTDATE"/> + <return-property name="endDate" column="ENDDATE"/> + <return-property name="regionCode" column="REGIONCODE"/> + <return-property name="id" column="EMPID"/> + <return-property name="salary"> + <!-- as multi column properties are not supported via the + {}-syntax, we need to provide an explicit column list for salary via <return-property> --> + <return-column name="VALUE"/> + <return-column name="CURRENCY"/> + </return-property> + </return> + { call selectAllEmployments() } + </sql-query> + + <sql-query name="textholder"> + <return alias="h" class="TextHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, DESCRIPTION AS {h.description} FROM TEXTHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + + <sql-query name="imageholder"> + <return alias="h" class="ImageHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, PHOTO AS {h.photo} FROM IMAGEHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + + <database-object> + <create> + CREATE PROCEDURE selectAllEmployments AS + SELECT EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE, + REGIONCODE, EMPID, VALUE, CURRENCY + FROM EMPLOYMENT + </create> + <drop> + DROP PROCEDURE selectAllEmployments + </drop> + </database-object> + + <!-- The following is a workaround for HHH-3685 so that SchemaExport --> + <!-- changes the transaction mode of selectAllEmployments to "chained" --> + <database-object> + <create> + sp_procxmode selectAllEmployments, 'chained' + </create> + <!-- no drop command corresponding to "sp_procxmode ..." --> + <drop/> + </database-object> + + <database-object> + <create> + CREATE PROCEDURE paramHandling @j int, @i int AS + SELECT @j as value, @i as value2 + </create> + <drop> + DROP PROCEDURE paramHandling + </drop> + </database-object> + + <!-- The following is a workaround for HHH-3685 so that SchemaExport --> + <!-- changes the transaction mode of paramHandling to "chained" --> + <database-object> + <create> + sp_procxmode paramHandling, 'chained' + </create> + <!-- no drop command corresponding to "sp_procxmode ..." --> + <drop/> + </database-object> + + <database-object> + <create> + CREATE PROCEDURE simpleScalar @number int AS + SELECT @number as value, 'getAll' as name + </create> + <drop> + DROP PROCEDURE simpleScalar + </drop> + </database-object> + + <!-- The following is a workaround for HHH-3685 so that SchemaExport --> + <!-- changes the transaction mode of simpleScalar to "chained" --> + <database-object> + <create> + sp_procxmode simpleScalar, 'chained' + </create> + <!-- no drop command corresponding to "sp_procxmode ..." --> + <drop/> + </database-object> + +</hibernate-mapping> Added: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/SQLServerCustomSQLTest.java =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/SQLServerCustomSQLTest.java (rev 0) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sqlserver/SQLServerCustomSQLTest.java 2009-06-17 20:28:59 UTC (rev 16823) @@ -0,0 +1,53 @@ +/* + * Copyright (c) 2009, Red Hat Middleware LLC or third-party contributors as + * indicated by the @author tags or express copyright attribution + * statements applied by the authors. All third-party contributions are + * distributed under license by Red Hat Middleware LLC. + * + * 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, as published by the Free Software Foundation. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY 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 + * along with this distribution; if not, write to: + * Free Software Foundation, Inc. + * 51 Franklin Street, Fifth Floor + * Boston, MA 02110-1301 USA + */ +package org.hibernate.test.sql.hand.custom.sqlserver; + +import junit.framework.Test; + +import org.hibernate.dialect.Dialect; +import org.hibernate.dialect.SQLServerDialect; +import org.hibernate.junit.functional.FunctionalTestClassTestSuite; +import org.hibernate.test.sql.hand.custom.CustomStoredProcTestSupport; + +/** + * Custom SQL tests for SQLServer + * + * @author Gail Badner + */ +public class SQLServerCustomSQLTest extends CustomStoredProcTestSupport { + + public SQLServerCustomSQLTest(String str) { + super( str ); + } + + public String[] getMappings() { + return new String[] { "sql/hand/custom/sqlserver/Mappings.hbm.xml" }; + } + + public static Test suite() { + return new FunctionalTestClassTestSuite( SQLServerCustomSQLTest.class ); + } + + public boolean appliesTo(Dialect dialect) { + return ( dialect instanceof SQLServerDialect ); + } +} \ No newline at end of file Modified: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sybase/Mappings.hbm.xml =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sybase/Mappings.hbm.xml 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sybase/Mappings.hbm.xml 2009-06-17 20:28:59 UTC (rev 16823) @@ -6,7 +6,7 @@ <!-- This mapping demonstrates the use of Hibernate with all-handwritten SQL! - NOTE: this version is for Sybase/SQLServer + NOTE: this version is for Sybase --> <hibernate-mapping package="org.hibernate.test.sql.hand" default-access="field"> @@ -64,6 +64,36 @@ <sql-delete>DELETE FROM EMPLOYMENT WHERE EMPID=?</sql-delete> </class> + <class name="TextHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="description" type="text" length="15000"/> + <loader query-ref="textholder"/> + <sql-insert> + INSERT INTO TEXTHOLDER + (DESCRIPTION, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE TEXTHOLDER SET DESCRIPTION=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM TEXTHOLDER WHERE ID=?</sql-delete> + </class> + + <class name="ImageHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="photo" type="image" length="15000"/> + <loader query-ref="imageholder"/> + <sql-insert> + INSERT INTO IMAGEHOLDER + (PHOTO, ID) + VALUES (?, ?) + </sql-insert> + <sql-update>UPDATE IMAGEHOLDER SET PHOTO=? WHERE ID=?</sql-update> + <sql-delete>DELETE FROM IMAGEHOLDER WHERE ID=?</sql-delete> + </class> + <resultset name="org-emp-regionCode"> <return-scalar column="regionCode" type="string"/> <return alias="org" class="Organization"/> @@ -181,7 +211,17 @@ </return> { call selectAllEmployments() } </sql-query> - + + <sql-query name="textholder"> + <return alias="h" class="TextHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, DESCRIPTION AS {h.description} FROM TEXTHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + + <sql-query name="imageholder"> + <return alias="h" class="ImageHolder" lock-mode="upgrade"/> + SELECT ID AS {h.id}, PHOTO AS {h.photo} FROM IMAGEHOLDER WHERE ID=? /*FOR UPDATE*/ + </sql-query> + <database-object> <create> CREATE PROCEDURE selectAllEmployments AS Modified: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sybase/SybaseCustomSQLTest.java =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sybase/SybaseCustomSQLTest.java 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/custom/sybase/SybaseCustomSQLTest.java 2009-06-17 20:28:59 UTC (rev 16823) @@ -5,11 +5,14 @@ import org.hibernate.dialect.Dialect; import org.hibernate.dialect.SybaseDialect; +import org.hibernate.dialect.SybaseASE15Dialect; +import org.hibernate.dialect.Sybase11Dialect; +import org.hibernate.dialect.SybaseAnywhereDialect; import org.hibernate.junit.functional.FunctionalTestClassTestSuite; import org.hibernate.test.sql.hand.custom.CustomStoredProcTestSupport; /** - * Custom SQL tests for Sybase/SQLServer (Transact-SQL) + * Custom SQL tests for Sybase dialects * * @author Gavin King */ @@ -28,7 +31,10 @@ } public boolean appliesTo(Dialect dialect) { - return ( dialect instanceof SybaseDialect ); + return ( dialect instanceof SybaseDialect || + dialect instanceof SybaseASE15Dialect || + dialect instanceof Sybase11Dialect || + dialect instanceof SybaseAnywhereDialect ); } } Modified: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/query/NativeSQLQueries.hbm.xml =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/query/NativeSQLQueries.hbm.xml 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/query/NativeSQLQueries.hbm.xml 2009-06-17 20:28:59 UTC (rev 16823) @@ -117,6 +117,20 @@ <property name="length" column="flength"/> </class> + <class name="TextHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="description" type="text" length="15000"/> + </class> + + <class name="ImageHolder"> + <id name="id" column="id"> + <generator class="increment"/> + </id> + <property name="photo" type="image" length="15000"/> + </class> + <resultset name="org-emp-regionCode"> <return-scalar column="regionCode" type="string"/> <return alias="org" class="Organization"/> Modified: core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/query/NativeSQLQueriesTest.java =================================================================== --- core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/query/NativeSQLQueriesTest.java 2009-06-17 15:17:31 UTC (rev 16822) +++ core/trunk/testsuite/src/test/java/org/hibernate/test/sql/hand/query/NativeSQLQueriesTest.java 2009-06-17 20:28:59 UTC (rev 16823) @@ -15,6 +15,7 @@ import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.Transaction; +import org.hibernate.util.ArrayHelper; import org.hibernate.test.sql.hand.Organization; import org.hibernate.test.sql.hand.Person; import org.hibernate.test.sql.hand.Employment; @@ -24,6 +25,8 @@ import org.hibernate.test.sql.hand.SpaceShip; import org.hibernate.test.sql.hand.Speech; import org.hibernate.test.sql.hand.Group; +import org.hibernate.test.sql.hand.TextHolder; +import org.hibernate.test.sql.hand.ImageHolder; import org.hibernate.cfg.Configuration; import org.hibernate.cfg.Environment; import org.hibernate.junit.functional.FunctionalTestCase; @@ -96,6 +99,14 @@ " join PERSON pers on pers.PERID = emp.EMPLOYEE "; } + protected String getDescriptionsSQL() { + return "select DESCRIPTION from TEXTHOLDER"; + } + + protected String getPhotosSQL() { + return "select PHOTO from IMAGEHOLDER"; + } + public void testFailOnNoAddEntityOrScalar() { // Note: this passes, but for the wrong reason. // there is actually an exception thrown, but it is the database @@ -668,6 +679,66 @@ s.close(); } + public void testTextTypeInSQLQuery() { + Session s = openSession(); + Transaction t = s.beginTransaction(); + String description = buildLongString( 15000, 'a' ); + TextHolder holder = new TextHolder( description ); + s.persist( holder ); + t.commit(); + s.close(); + + s = openSession(); + t = s.beginTransaction(); + String descriptionRead = ( String ) s.createSQLQuery( getDescriptionsSQL() ) + .uniqueResult(); + assertEquals( description, descriptionRead ); + s.delete( holder ); + t.commit(); + s.close(); + } + + public void testImageTypeInSQLQuery() { + Session s = openSession(); + Transaction t = s.beginTransaction(); + byte[] photo = buildLongByteArray( 15000, true ); + ImageHolder holder = new ImageHolder( photo ); + s.persist( holder ); + t.commit(); + s.close(); + + s = openSession(); + t = s.beginTransaction(); + byte[] photoRead = ( byte[] ) s.createSQLQuery( getPhotosSQL() ) + .uniqueResult(); + assertTrue( ArrayHelper.isEquals( photo, photoRead ) ); + s.delete( holder ); + t.commit(); + s.close(); + } + + private String buildLongString(int size, char baseChar) { + StringBuffer buff = new StringBuffer(); + for( int i = 0; i < size; i++ ) { + buff.append( baseChar ); + } + return buff.toString(); + } + + private byte[] buildLongByteArray(int size, boolean on) { + byte[] data = new byte[size]; + data[0] = mask( on ); + for ( int i = 0; i < size; i++ ) { + data[i] = mask( on ); + on = !on; + } + return data; + } + + private byte mask(boolean on) { + return on ? ( byte ) 1 : ( byte ) 0; + } + private static class UpperCasedAliasToEntityMapResultTransformer extends BasicTransformerAdapter implements Serializable { public Object transformTuple(Object[] tuple, String[] aliases) { Map result = new HashMap( tuple.length );