<property name="byteValue" type="byte" column="BYTE_VALUE" />
The byte type here, indicates that when a record is retrieved from the table, the value in the BYTE_VALUE column, is converted to an appropriate Java type that is equivalent to a sql byte type mapping.
I created a Java class that includes all the different data types supported in Hibernate by default. The XML mapping document created includes all the supported types
I created a Java class that includes all the different data types supported in Hibernate by default. The XML mapping document created includes all the supported types
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping package="com.model"> <class name="AllTypes" table="ALL_TYPES"> <id name="longValue" type="long"> <column name="LONG_VALUE" /> <generator class="identity" /> </id> <property name="byteValue" type="byte" column="BYTE_VALUE" /> <property name="shortValue" type="short" column="SHORT_VALUE" /> <property name="intValue" type="integer" column="INT_VALUE" /> <property name="floatValue" type="float" column="FLOAT_VALUE" /> <property name="doubleValue" type="double" column="DOUBLE_VALUE" /> <property name="bigDecimalValue" type="big_decimal" column="BIG_DECIMAL_VALUE" /> <property name="booleanValue" type="boolean" column="BOOLEAN_VALUE" /> <property name="trueFalseValue" type="true_false" column="TRUE_FALSE_VALUE" /> <property name="yesNoValue" type="yes_no" column="YES_NO_VALUE" /> <property name="characterValue" type="character" column="CHARACTER_VALUE" /> <property name="stringValue" type="string" column="STRING_VALUE" /> <property name="textValue" type="text" column="TEXT_VALUE" /> <property name="blobValue" type="blob" column="BLOB_VALUE" /> <property name="clobValue" type="clob" column="CLOB_VALUE" /> <property name="binaryValue" type="binary" column="BINARY_VALUE" /> <property name="timeValue" type="time" column="TIME_VALUE" /> <property name="dateValue" type="date" column="DATE_VALUE" /> <property name="timestampValue" type="timestamp" column="TIMESTAMP_VALUE" /> <property name="calendarValue" type="calendar" column="CALENDAR_VALUE" /> <property name="calendarDateValue" type="calendar_date" column="CALENDAR_DATE_VALUE" /> <property name="timeZoneValue" type="timezone" column="TIMEZONE_VALUE" /> <property name="localeValue" type="locale" column="LOCALE_VALUE" /> <property name="classValue" type="class" column="CLASS_VALUE" /> </class> </hibernate-mapping>
The java class is as below:
package com.model; import java.math.BigDecimal; import java.sql.Blob; import java.sql.Clob; import java.util.Calendar; import java.util.Date; import java.util.Locale; import java.util.TimeZone; @SuppressWarnings("rawtypes") public class AllTypes { private Character characterValue;// A type that maps an SQL CHAR(1) to a Java Character. private String stringValue;// A type that maps an SQL VARCHAR to a Java String. private String textValue;// A type that maps an SQL CLOB to a Java String. private Byte byteValue;// A type that maps an SQL TINYINT to a Java Byte. private Short shortValue;// A type that maps an SQL SMALLINT to a Java Short. private Integer intValue;// A type that maps an SQL INT to a Java Integer. private Long longValue;// A type that maps an SQL BIGINT to a Java Long. private Double doubleValue;// A type that maps an SQL DOUBLE to a Java Double. private Float floatValue; // A type that maps an SQL FLOAT to a Java Float. private BigDecimalbigDecimalValue;// A type that maps an SQL NUMERIC to a java.math.BigDecimal private Boolean booleanValue;// A type that maps an SQL BIT to a Java Boolean. private Boolean trueFalseValue;// A type that maps an SQL CHAR(1) to a Java Boolean. private Boolean yesNoValue; // A type that maps an SQL CHAR(1) to a Java Boolean. private byte[] binaryValue;// A type that maps an SQL VARBINARY to a Java byte[]. private Blob blobValue; // A type that maps an SQL BLOB to a java.sql.Blob. private Clob clobValue; // A type that maps an SQL CLOB to a java.sql.Clob private Date timeValue;// A type that maps an SQL TIME to a java.util.Date or java.sql.Time. private Date dateValue;// A type that maps an SQL DATE to a Java Date. private Date timestampValue;// A type that maps an SQL TIMESTAMP to a java.util.Date or java.sql.Timestamp. private Calendar calendarValue;// A type mapping for a Calendar object that represents a date-time. private Calendar calendarDateValue;// A type mapping for a Calendar object that represents a date private TimeZone timeZoneValue;// A type that maps an SQL VARCHAR to a java.util.TimeZone; private Locale localeValue;// A type that maps an SQL VARCHAR to a Java Locale private Class classValue;// A type that maps an SQL VARCHAR to a Java Class public Boolean getYesNoValue() { return yesNoValue; } public void setYesNoValue(Boolean yesNoValue) { this.yesNoValue = yesNoValue; } public Character getCharacterValue() { return characterValue; }
public void setCharacterValue(Character characterValue) { this.characterValue = characterValue; } //remaining setter-getter methods..... @Override public String toString() { StringBuilder builder = new StringBuilder(); builder.append("[ " + AllTypes.class + " { ") .append("LONG VALUE : " + getLongValue() + ", ") .append("STRING VALUE : " + getStringValue() + ", ") .append("TEXT VALUE : " + getTextValue() + ", ") .append("BYTE VALUE : " + getByteValue() + ", ") .append("CHARACTER VALUE : " + getCharacterValue() + ", ") .append("DOUBLE VALUE : " + getDoubleValue() + ", ") .append("FLOAT VALUE : " + getFloatValue() + ", ") .append("INT VALUE : " + getIntValue() + ", ") .append("SHORT VALUE : " + getShortValue() + ", ") .append("BIGDECIMAL VALUE : " + getBigDecimalValue() + ", ") .append("BINARY VALUE : " + getBinaryValue() + ", ") .append("BLOB VALUE : " + getBlobValue() + ", ") .append("BOOLEAN VALUE : " + getBooleanValue() + ", ") .append("CALENDAR_DATE VALUE : " + getCalendarDateValue() + ", ") .append("CALENDAR VALUE : " + getCalendarValue() + ", ") .append("CLASS VALUE : " + getClassValue() + ", ") .append("CLOB VALUE : " + getClobValue() + ", ") .append("DATE VALUE : " + getDateValue() + ", ") .append("LOCALE VALUE : " + getLocaleValue() + ", ") .append("TIMESTAMP VALUE : " + getTimestampValue() + ", ") .append("TIME VALUE : " + getTimeValue() + ", ") .append("TIMEZONE VALUE : " + getTimeZoneValue() + ", ") .append("TRUE_FALSE VALUE : " + getTrueFalseValue() + ", ") .append("YES_NO VALUE : " + getYesNoValue()) .append(" }]"); return builder.toString(); } }
I did not create a table and instead used Hibernates auto-create option. The logs(cleaned) generated on start up is as below:
829 [main] INFO org.hibernate.cfg.HbmBinder - Mapping class: com.model.AllTyp es -> ALL_TYPES 844 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: longValue -> L ONG_VALUE 860 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: byteValue -> B YTE_VALUE 860 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: shortValue -> SHORT_VALUE 860 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: intValue -> IN T_VALUE 860 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: floatValue -> FLOAT_VALUE 860 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: doubleValue -> DOUBLE_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: bigDecimalValu e -> BIG_DECIMAL_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: booleanValue - > BOOLEAN_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: trueFalseValue -> TRUE_FALSE_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: yesNoValue -> YES_NO_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: characterValue -> CHARACTER_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: stringValue -> STRING_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: textValue -> T EXT_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: blobValue -> B LOB_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: clobValue -> C LOB_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: binaryValue -> BINARY_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: timeValue -> T IME_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: dateValue -> D ATE_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: timestampValue -> TIMESTAMP_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: calendarValue -> CALENDAR_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: calendarDateVa lue -> CALENDAR_DATE_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: timeZoneValue -> TIMEZONE_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: localeValue -> LOCALE_VALUE 891 [main] DEBUG org.hibernate.cfg.HbmBinder - Mapped property: classValue -> CLASS_VALUE ... 2641 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Stat ic SQL for entity: com.model.AllTypes 2641 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Ver sion select: select LONG_VALUE from ALL_TYPES where LONG_VALUE =? 2641 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Sna pshot select: select alltypes_.LONG_VALUE, alltypes_.BYTE_VALUE as BYTE2_0_, all types_.SHORT_VALUE as SHORT3_0_, alltypes_.INT_VALUE as INT4_0_, alltypes_.FLOAT _VALUE as FLOAT5_0_, alltypes_.DOUBLE_VALUE as DOUBLE6_0_, alltypes_.BIG_DECIMAL _VALUE as BIG7_0_, alltypes_.BOOLEAN_VALUE as BOOLEAN8_0_, alltypes_.TRUE_FALSE_ VALUE as TRUE9_0_, alltypes_.YES_NO_VALUE as YES10_0_, alltypes_.CHARACTER_VALUE as CHARACTER11_0_, alltypes_.STRING_VALUE as STRING12_0_, alltypes_.TEXT_VALUE as TEXT13_0_, alltypes_.BLOB_VALUE as BLOB14_0_, alltypes_.CLOB_VALUE as CLOB15_ 0_, alltypes_.BINARY_VALUE as BINARY16_0_, alltypes_.TIME_VALUE as TIME17_0_, al ltypes_.DATE_VALUE as DATE18_0_, alltypes_.TIMESTAMP_VALUE as TIMESTAMP19_0_, al ltypes_.CALENDAR_VALUE as CALENDAR20_0_, alltypes_.CALENDAR_DATE_VALUE as CALEND AR21_0_, alltypes_.TIMEZONE_VALUE as TIMEZONE22_0_, alltypes_.LOCALE_VALUE as LO CALE23_0_, alltypes_.CLASS_VALUE as CLASS24_0_ from ALL_TYPES alltypes_ where al ltypes_.LONG_VALUE=? 2641 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Ins ert 0: insert into ALL_TYPES (BYTE_VALUE, SHORT_VALUE, INT_VALUE, FLOAT_VALUE, D OUBLE_VALUE, BIG_DECIMAL_VALUE, BOOLEAN_VALUE, TRUE_FALSE_VALUE, YES_NO_VALUE, C HARACTER_VALUE, STRING_VALUE, TEXT_VALUE, BLOB_VALUE, CLOB_VALUE, BINARY_VALUE, TIME_VALUE, DATE_VALUE, TIMESTAMP_VALUE, CALENDAR_VALUE, CALENDAR_DATE_VALUE, TI MEZONE_VALUE, LOCALE_VALUE, CLASS_VALUE, LONG_VALUE) values (?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ... 2813 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport - drop table if exists ALL_TYPES 2875 [main] DEBUG org.hibernate.tool.hbm2ddl.SchemaExport - create table ALL_TYPES ( LONG_VALUE bigint not null auto_increment, BYTE_VALUE tinyint, SHORT_VALUE smallint, INT_VALUE integer, FLOAT_VALUE float, DOUBLE_VALUE double precision, BIG_DECIMAL_VALUE numeric(19,2), BOOLEAN_VALUE bit, TRUE_FALSE_VALUE char(1), YES_NO_VALUE char(1), CHARACTER_VALUE char(1), STRING_VALUE varchar(255), TEXT_VALUE text, BLOB_VALUE blob, CLOB_VALUE text, BINARY_VALUE tinyblob, TIME_VALUE time, DATE_VALUE date, TIMESTAMP_VALUE datetime, CALENDAR_VALUE datetime, CALENDAR_DATE_VALUE date, TIMEZONE_VALUE varchar(255), LOCALE_VALUE varchar(255), CLASS_VALUE varchar(255), primary key (LONG_VALUE) )
2938 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - schema export compl ete
Hibernate created the table using the appropriate data-types available. I executed the code to create a record .
public static void testAllTypes() { AllTypes allTypes = new AllTypes(); allTypes.setBigDecimalValue(new BigDecimal("123.80")); allTypes.setBinaryValue("Stored as a Binary array".getBytes()); allTypes.setBlobValue(Hibernate.createBlob("This is a blob field".getBytes())); allTypes.setBooleanValue(Boolean.TRUE); allTypes.setByteValue(new Byte("4")); allTypes.setCalendarDateValue(Calendar.getInstance(Locale.ENGLISH)); allTypes.setCalendarValue(Calendar.getInstance(Locale.ENGLISH)); allTypes.setCharacterValue(new Character('a')); allTypes.setClassValue(AllTypes.class); allTypes.setDateValue(new Date()); allTypes.setDoubleValue(new Double("125.90")); allTypes.setFloatValue(123.987F); allTypes.setIntValue(123); allTypes.setLocaleValue(Locale.UK); allTypes.setLongValue(12896L); allTypes.setShortValue(new Short("12")); allTypes.setStringValue("This is a string"); allTypes.setTextValue("This is a huge text but not a clob"); allTypes.setTimestampValue(new Date()); allTypes.setTimeValue(new Date()); allTypes.setTimeZoneValue(TimeZone.getDefault()); allTypes.setTrueFalseValue(Boolean.TRUE); allTypes.setYesNoValue(Boolean.TRUE); Session session = sessionFactory.openSession(); Transaction transaction = session.beginTransaction(); session.save(allTypes); transaction.commit(); System.out.println("The Object saved in database is " + allTypes); }
I created an object setting all the fields with appropriate values. The logs(cleaned) show the save was performed.
2578 [main] DEBUG org.hibernate.event.def.DefaultSaveOrUpdateEventListener - sa ving transient instance 2578 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - saving [c om.model.AllTypes#<null>] 2578 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - executing insertions 2703 [main] DEBUG org.hibernate.event.def.AbstractSaveEventListener - executing identity-insert immediately 2703 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Inse rting entity: com.model.AllTypes (native id) 2703 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedSt atement (open PreparedStatements: 0, globally: 0) 2703 [main] DEBUG org.hibernate.SQL - insert into ALL_TYPES (BYTE_VALUE, SHORT_VALUE, INT_VALUE, FLOAT_VALUE, DOUBLE_VALUE, BIG_DECI MAL_VALUE, BOOLEAN_VALUE, TRUE_FALSE_VALUE, YES_NO_VALUE, CHARACTER_VALUE, STRIN G_VALUE, TEXT_VALUE, BLOB_VALUE, CLOB_VALUE, BINARY_VALUE, TIME_VALUE, DATE_VALU E, TIMESTAMP_VALUE, CALENDAR_VALUE, CALENDAR_DATE_VALUE, TIMEZONE_VALUE, LOCALE_ VALUE, CLASS_VALUE) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ... 3000 [main] DEBUG org.hibernate.id.IdentifierGeneratorFactory - Natively genera ted identity: 2 ... 3047 [main] DEBUG org.hibernate.pretty.Printer - com.model.AllTypes{timeZoneVal ue=Asia/Calcutta, localeValue=en_GB, clobValue=org.hibernate.lob.SerializableClo b@18825b3, timeValue=07:55:04, calendarDateValue=04 September 2011, binaryValue= d3f4eff2e5e4a0e1f3a0e1a0c2e9eee1f2f9a0e1f2f2e1f9, stringValue=This is a string, doubleValue=125.9, byteValue=4, yesNoValue=true, blobValue=org.hibernate.lob.Ser ializableBlob@4f9fdc, booleanValue=true, characterValue=a, calendarValue=2011-09 -04 07:55:04, shortValue=12, longValue=2, trueFalseValue=true, timestampValue=20 11-09-04 07:55:04, floatValue=123.987, classValue=com.model.AllTypes, bigDecimal Value=123.80, dateValue=04 September 2011, intValue=123, textValue=This is a hug e text but not a clob} ... 3047 [main] DEBUG org.hibernate.impl.SessionImpl - after transaction completion The Object saved in database is [ class com.model.AllTypes { LONG VALUE : 2, ST RING VALUE : This is a string, TEXT VALUE : This is a huge text but not a clob , BYTE VALUE : 4, CHARACTER VALUE : a, DOUBLE VALUE : 125.9, FLOAT VALUE : 1 23.987, INT VALUE : 123, SHORT VALUE : 12, BIGDECIMAL VALUE : 123.80, BINARY VALUE : [B@e95a56, BLOB VALUE : org.hibernate.lob.SerializableBlob@4f9fdc, BOO LEAN VALUE : true, CALENDAR_DATE VALUE : java.util.GregorianCalendar[time=1315 103104484,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.cale ndar.ZoneInfo[id="Asia/Calcutta",offset=19800000,dstSavings=0,useDaylight=false, transitions=6,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1, YEAR=2011,MONTH=8,WEEK_OF_YEAR=37,WEEK_OF_MONTH=2,DAY_OF_MONTH=4,DAY_OF_YEAR=247, DAY_OF_WEEK=1,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=7,HOUR_OF_DAY=7,MINUTE=55,SECOND =4,MILLISECOND=484,ZONE_OFFSET=19800000,DST_OFFSET=0], CALENDAR VALUE : java.ut il.GregorianCalendar[time=1315103104484,areFieldsSet=true,areAllFieldsSet=true,l enient=true,zone=sun.util.calendar.ZoneInfo[id="Asia/Calcutta",offset=19800000,d stSavings=0,useDaylight=false,transitions=6,lastRule=null],firstDayOfWeek=1,mini malDaysInFirstWeek=1,ERA=1,YEAR=2011,MONTH=8,WEEK_OF_YEAR=37,WEEK_OF_MONTH=2, DAY_OF_MONTH=4,DAY_OF_YEAR=247,DAY_OF_WEEK=1,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=7, HOUR_OF_DAY=7,MINUTE=55,SECOND=4,MILLISECOND=484,ZONE_OFFSET=19800000,DST_OFFSET=0], CLASS VALUE : class com.model.AllTypes, CLOB VALUE : org.hibernate.lob.Se rializableClob@18825b3, DATE VALUE : Sun Sep 04 07:55:04 IST 2011, LOCALE VALUE : en_GB, TIMESTAMP VALUE : Sun Sep 04 07:55:04 IST 2011, TIME VALUE : Sun Se p 04 07:55:04 IST 2011, TIMEZONE VALUE : sun.util.calendar.ZoneInfo[id="Asia/Ca lcutta",offset=19800000,dstSavings=0,useDaylight=false,transitions=6,lastRule=nu ll], TRUE_FALSE VALUE : true, YES_NO VALUE : true }]
The data in the table can be seen in the image:
Thank you!
ReplyDeleteAlthough I'd spent a few hours in the exercise, with your files I was able to map all such types from Hibernate to SQL Server 2012 Express. The main needed adjustments were related ro Hibernate 4.3.5 and adding setters and getters.
The only differences in SQL Server fields were regarding BLOB and TINYBLOB fields. The create table statement above worked fine with some minor changes:
create table ALL_TYPES (
LONG_VALUE bigint identity(1,1) not null,
...
BLOB_VALUE VARBINARY(MAX), -- "blob" in SQL Server --
BINARY_VALUE VARBINARY(255), -- "tinyblob" in SQL Server --
...
primary key (LONG_VALUE)
)
Thanks once again!
Most welcome, Thanks for sharing your experience with Microsoft databases !
Deletehow to update a boolean data type using hql???
ReplyDeletefuck it
ReplyDelete