Page Comparison

MSSQL

type-mapping und EntityGeneratorCustomizer

JAVA Beispiel

DECIMAL(p*,s*)

Numeric data types that have

fixed precision and scale.

Fixed precision and scale numbers.

When

When maximum precision is used, valid values

are from - 10^38 +1 through 10^38 - 1.

Code Block

language	xml

<sql-type jdbc-type="DECIMAL" hibernate-type="java.math.BigDecimal"></sql-type>

-

Code Block

language	java

private BigDecimal decimalTest;

Code Block

language	java

@Column(name = "DecimalTest", precision = 20)
public BigDecimal getDecimalTest() {
	return this.decimalTest;
}

Code Block

language	java

private Double decimalTest;

Code Block

language	java

@Column(name = "DecimalTest", precision = 20)
public Double getDecimalTest() {
	return this.decimalTest;
}

NUMERIC(p*,s*)

Numeric data types that have

fixed precision and scale.

Fixed precision and scale numbers.

When

When maximum precision is used, valid values

are from - 10^38 +1 through 10^38 - 1.

Code Block

language	xml

<sql-type jdbc-type="NUMERIC" hibernate-type="java.math.BigDecimal"></sql-type>

-

Code Block

language	java

private BigDecimal numericTest;

Code Block

language	java

@Column(name = "NumericTest", precision = 20)
public BigDecimal getNumericTest() {
	return this.numericTest;
}

MONEY

Data types that represent monetary

or currency values.

The money and smallmoney data

The money and smallmoney data types

are accurate to a ten-thousandth of the

monetary units that they represent.

Range: -922,337,203,685,477.5808

to 922,337,203,685,477.5807

Storage: 8 Bytes

Code Block

language	xml

<sql-type jdbc-type="DECIMAL" name="money" hibernate-type="java.math.BigDecimal"></sql-type>

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(
	new PropertyColumnDefinitionFix(new String[]{"MONEY"},
		new String[]{"java.math.BigDecimal"},"money"));

Code Block

language	java

private BigDecimal moneyTest;

Code Block

language	java

@Column(name = "MoneyTest", scale = 4, 
			columnDefinition = "money")
public BigDecimal getMoneyTest() {
	return this.moneyTest;
}

SMALLMONEY

Data types that represent monetary

or currency values.

The money and smallmoney data

The money and smallmoney data types

are accurate to a ten-thousandth of the

monetary units that they represent.

Range: - 214,748.3648 to 214,748.3647

Storage: 4 Bytes

Code Block

language	xml

<sql-type jdbc-type="DECIMAL" name="smallmoney" hibernate-type="java.math.BigDecimal"></sql-type>

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(
	new PropertyColumnDefinitionFix(new String[]{"SMALLMONEY"},
		new String[]{"java.math.BigDecimal"},"smallmoney"));

Code Block

language	java

private BigDecimal smallmoneyTest;

Code Block

language	java

@Column(name = "SmallmoneyTest", precision = 10, 
			scale = 4, columnDefinition = "smallmoney")
public BigDecimal getSmallmoneyTest() {
	return this.smallmoneyTest;
}

INT

Exact-number data types that use integer data.

The int data

The int data type is the primary integer data

type in SQL Server.

Range: -2^31 (-2,147,483,648)

to 2^31-1 (2,147,483,647)

Storage: 4 Bytes

-

Hibernate default mapping

-

data

Code Block

language	java

private int intTest;

Code Block

language	java

@Column(name = "IntTest", unique = true, nullable = false)
public int getIntTest() {
	return this.intTest;
}

BIGINT

Exact-number data types that use integer data.

The bigint

The bigint data type is intended for use

when integer values might exceed the

range that is supported by

the int data

the int data type.

Range: -2^63 (-9,223,372,036,854,775,808)

to 2^63-1 (9,223,372,036,854,775,807)

-

Storage: 8 Bytes

-

Hibernate default mapping

Code Block

language	java

private Long bigintTest;

Code Block

language	java

@Column(name = "BigintTest")
public Long getBigintTest() {
	return this.bigintTest;
}

BINARY(n)

Binary data type of fixed length.

Fixed-length binary data with a length

of n bytes, where n is

of n bytes, where n is a value from 1 through

8,000.

The

The storage size

is n bytes

is n bytes.

Use binary when

Use binary when the sizes of the

column data entries are consistent.

-

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"BINARY"},new String[]{"byte[]"},"binary"));

Code Block

language	java

private byte[] binaryTest;

Code Block

language	java

@Column(name = "BinaryTest", columnDefinition = "binary")
public byte[] getBinaryTest() {
	return this.binaryTest;
}

BIT

An integer data type that can take a value of

1, 0, or NULL.

The SQL Server Database Engine optimizes

storage of bit columns. If there are 8 or less

bit columns in a table, the columns are stored

as 1 byte. If there are from 9 up to 16 bit

columns, the columns are stored as 2 bytes,

and so on. The string values TRUE and

FALSE can be converted to bit values:

TRUE is converted to 1 and FALSE is

converted to 0.Converting to bit promotes

any nonzero value to 1.

-

Hibernate default mapping

-

Code Block

language	java

private Boolean bitTest;

Code Block

language	java

@Column(name = "BitTest")
public Boolean getBitTest() {
	return this.bitTest;
}

CHAR(n)

Are string data type of fixed length.

Fixed-length, non-Unicode string data.

n defines the string length and must be a

value from 1 through 8,000.

The storage size

is n bytes

is n bytes.

Use char when

Use char when the sizes of the column

data entries are consistent.

-

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"CHAR"},new String[]{"java.lang.String"},"char"));

Code Block

language	java

private String charTest;

Code Block

language	java

@Column(name = "CharTest", length = 10, 
			columnDefinition = "char")
public String getCharTest() {
	return this.charTest;
}

DATE

Defines a date in SQL Server.

Default string literal format: YYYY-MM-DD

Range: 0001-01-01 through 9999-12-31

Storage: 3 bytes, fixed

Accuracy: one Day

-

Default Value: 1900-01-01

Calendar: Gregorian

-

Hibernate default mapping

Code Block

language	java

private Date dateTest;

Code Block

language	java

@Temporal(TemporalType.DATE)
@Column(name = "DateTest", length = 10)
public Date getDateTest() {
	return this.dateTest;
}

DATETIME

Defines a date that is combined with a time

of day with fractional seconds that is

based on a 24-hour clock.

Date Range: 1753-01-01 through 9999-12-31

Time Range: 00:00:00 through 23:59:59.997

Storage: 8 bytes

Accuracy: Rounded to increments of

-

.000, .003, or .007 seconds

Default Value: 1900-01-01 00:00:00

Calendar: Gregorian

-

Hibernate default mapping

Code Block

language	java

private Date datetimeTest;

Code Block

language	java

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "DatetimeTest", length = 23)
public Date getDatetimeTest() {
	return this.datetimeTest;
}

DATETIME2

Defines a date that is combined with a time

of day that is based on 24-hour clock.

datetime2 can

datetime2 can be considered as an

extension of the

existingdatetime type

existingdatetime type that has a larger date range, a larger default

fractional precision, and optional

user-specified precision.

Date Range: 0001-01-01 through 9999-12-31

Time Range: 00:00:00 through

23:59:59.9999999

Stroage

Storage: 8 Bytes

Accuracy: 100 nanoseconds

Default Value: 1900-01-01 00:00:00

Calendar: Gregorian

-

Hibernate default mapping

-

Code Block

language	java

private Date datetime2test;

Code Block

language	java

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "Datetime2Test", length = 27)
public Date getDatetime2test() {
	return this.datetime2test;
}

FLOAT(n)

Approximate-number data types

for use with floating point numeric data.

Floating point data is approximate;

therefore, not all values in the data type

range can be represented exactly.

Where n

is

is the number of bits that are used

to store the mantissa of

the float number

the float number in

scientific notation and, therefore, dictates the

precision and storage size.

If n is

If n is specified,

it must be a value between 1 and 53.

The default value

of n is 53

of n is 53.

Range: - 1.79E+308 to -2.23E-308, 0

and 2.23E-308 to 1.79E+308

Storage with

nvalue

nvalue of 1-24: 4 Bytes

Storage with

nvalue

nvalue of 25-53: 8 Bytes

-

Hibernate default mapping

-

Code Block

language	java

private Double floatTest;

Code Block

language	java

@Column(name = "FloatTest", precision = 53, 
			scale = 0)
public Double getFloatTest() {
	return this.floatTest;
}

IMAGE

Variable-length data type for storing large

binary data.

Range: Variable-length binary data from

0 through 2^31-1 (2,147,483,647) bytes.

-

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"IMAGE"},new String[]{"byte[]"},"image"));

Code Block

language	java

private byte[] imageTest;

Code Block

language	java

@Column(name = "ImageTest", columnDefinition = "image")
public byte[] getImageTest() {
	return this.imageTest;
}

NCHAR(n)

Character data type that is fixed-length.

Unicode data and use the

UNICODE UCS-2 character set.

Fixed-length Unicode string data. n

defines

defines the string length and must be a value

from

from 1

through 4,000.

The

The storage size is two times n

bytes. When

bytes. When the collation code page uses

double-byte characters, the storage size is

still n

bytes

bytes.

Depending

Depending on the string, the

storage size of n bytes can be less than

the value specified for n.

Use nchar when

Use nchar when the sizes of the column

data entries are probably going to be similar.

Code Block

language	xml

<sql-type jdbc-type="NCHAR" hibernate-type="java.lang.String" />

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"NCHAR"},new String[]{"java.lang.String"},"nchar"));

Code Block

language	java

private String ncharTest;

Code Block

language	java

@Column(name = "NcharTest", columnDefinition = "nchar")
public String getNcharTest() {
	return this.ncharTest;
}

NTEXT

Variable-length data type for storing

large Unicode character.

Unicode

Unicode data

uses the UNICODE UCS-2 character set.

Variable-length Unicode data with a

maximum string length of

2^30 - 1 (1,073,741,823) bytes.

Storage size, in bytes, is two times

the string length that is entered.

-

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"NTEXT"},new String[]{"java.lang.String"},"ntext").addLobAnnotation());

Code Block

language	java

private String ntextTest;

Code Block

language	java

@Lob
@Column(name = "NtextTest", columnDefinition = "ntext")
public String getNtextTest() {
	return this.ntextTest;
}

NVARCHAR(n|max)

Character data type that is variable-length.

Unicode data and use the

UNICODE UCS-2 character set.

Variable-length Unicode string data. n

defines

defines the string length and can be a value from 1

through 4,000.

max

max indicates that the

maximum storage size is 2^31-1 bytes (2 GB).

The storage size, in bytes, is two times the

actual length of data entered + 2 bytes.

Use nvarchar when

Use nvarchar when the sizes of the

column data entries are probably going

to vary considerably.

Use nvarchar when

Use nvarchar when the sizes of the

column

column data entries are probably going to

vary considerably.

Code Block

language	xml

<sql-type jdbc-type="NVARCHAR" hibernate-type="java.lang.String" />

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"NVARCHAR"},new String[]{"java.lang.String"},"nvarchar"));

Code Block

language	java

private String nvarcharTest;

Code Block

language	java

@Column(name = "NvarcharTest", columnDefinition = "nvarchar")
public String getNvarcharTest() {
	return this.nvarcharTest;
}

REAL

Approximate-number data types

for use with floating point numeric data.

Floating point data is approximate;

therefore, not all values in the data type

range can be represented exactly.

Range: - 3.40E + 38 to -1.18E - 38, 0

and 1.18E - 38 to 3.40E + 38

Storage: 4 Bytes

-

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"REAL"},new String[]{"java.lang.Float","float"},"real"));

Code Block

language	java

private Float realTest;

Code Block

language	java

@Column(name = "RealTest", precision = 24, 
			scale = 0, columnDefinition = "real")
public Float getRealTest() {
	return this.realTest;
}

SMALLDATETIME

Defines a date that is combined with a

time of day.

The

The time is based on a

24-hour day, with seconds always

-

zero (:00) and without fractional seconds.

Date Range: 1900-01-01 through 2079-06-06

Time Range: 00:00:00 through 23:59:59

Storage: 4 Bytes, fixed.

Accuracy: One minute

Default Value: 1900-01-01 00:00:00

Calendar: Gregorian

-

Hibernate default mapping

Code Block

language	java

private Date smalldatetimeTest;

Code Block

language	java

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "SmalldatetimeTest", length = 16)
public Date getSmalldatetimeTest() {
	return this.smalldatetimeTest;
}

SMALLINT

Exact-number data types that use integer data.

Range: -2^15 (-32,768) to 2^15-1 (32,767)

Storage: 2 Bytes

-

Hibernate default mapping

Code Block

language	java

private Short smallintTest;

Code Block

language	java

@Column(name = "SmallintTest")
public Short getSmallintTest() {
	return this.smallintTest;
}

TEXT

Variable-length data types for storing

large non-Unicode.

Variable-length non-Unicode data in

the code page of the server and with

a maximum string length of

2^31-1 (2,147,483,647).

When

When the

server code page uses double-byte

characters, the storage is still

2,147,483,647 bytes.

Depending

Depending on

the character string, the storage size

may be less than 2,147,483,647 bytes.

-

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"TEXT"},new String[]{"java.lang.String"},"text"));

Code Block

language	java

private String textTest;

Code Block

language	java

@Column(name = "TextTest", columnDefinition = "text")
public String getTextTest() {
	return this.textTest;
}

TIME

Defines a time of a day.

The

The time is

without time zone awareness and is based

on a 24-hour clock.

Default string literal format:

hh:mm:ss[.

nnnnnnn

nnnnnnn]

Range: 00:00:00.0000000

through 23:59:59.9999999

Storage: 5 bytes, fixed, is the default with

the default of 100ns fractional

second -

second precision.

Accuracy: 100 nanoseconds

Default Value: 00:00:00

-

Hibernate default mapping

Code Block

language	java

private Date timeTest;

Code Block

language	java

@Temporal(TemporalType.TIME)
@Column(name = "TimeTest", length = 16)
public Date getTimeTest() {
	return this.timeTest;
}

TINYINT

Exact-number data types that use integer data.

Range: 0 to 255

Storage: 1 Byte

Code Block

language	xml

<sql-type jdbc-type="TINYINT" hibernate-type="java.lang.Short"></sql-type>

Code Block

language	java

visitor.addPropertyColumnDefinitionFix(new PropertyColumnDefinitionFix(
	new String[]{"TINYINT"},new String[]{"java.lang.Short","short"},"tinyint"));

Code Block

language	java

private Short tinyintTest;

Code Block

language	java

@Column(name = "TinyintTest", columnDefinition = "tinyint")
public Short getTinyintTest() {
	return this.tinyintTest;
}

UNIQUEIDENTIFIER

Is a 16-byte GUID.

-

Hibernate default mapping

-

Code Block

language	java

private String uniqueidentifierTest;

Code Block

language	java

@Column(name = "UniqueidentifierTest", length = 36)
public String getUniqueidentifierTest() {
	return this.uniqueidentifierTest;
}

HIERARCHYID

The hierarchyid data

The hierarchyid data type is a variable length,

system data type.

Use hierarchyid to

Use hierarchyid to represent

position in a hierarchy.

A

A column of

type hierarchyid

type hierarchyid does not automatically

represent a tree.

It is up to the application to generate

and

assign hierarchyid values

assign hierarchyid values in such

a way that the desired relationship

-

between rows is reflected in the values.

-

Hibernate default mapping

Code Block

language	java

private byte[] hierarchyidTest;

Code Block

language	java

@Column(name = "HierarchyidTest")
public byte[] getHierarchyidTest() {
	return this.hierarchyidTest;
}

VARBINARY (n|max)

Binary data type of variable length.

Variable-length binary data. n

can

can be a value from 1 through 8,000.

max

max indicates that the maximum storage

size is 2^31-1 bytes.

The

The storage size is

the actual length of the data entered + 2

bytes.

The

The data that is entered can be 0

bytes in length.

Use

varbinary when

varbinary when the sizes of the

column data entries vary considerably.

Use varbinary(max) when the column

-

data entries exceed 8,000 bytes.

-

Hibernate default mapping

Code Block

language	java

private byte[] varbinaryTest;

Code Block

language	java

@Column(name = "VarbinaryTest")
public byte[] getVarbinaryTest() {
	return this.varbinaryTest;
}

VCHAR (n|max)

Is a string data type of variable length.

Variable-length, non-Unicode string data.

n

defines

defines the string length and can be a value

from 1 through 8,000.

max

indicates

indicates that the

maximum storage size is 2^31-1 bytes (2 GB).

The storage size is the actual length of

the data entered + 2 bytes.

Use

varchar when

varchar when the sizes of the column

data entries vary considerably.

Use varchar(max) when the sizes of the

column data entries vary considerably,

-

and the size might exceed 8,000 bytes.

-

Hibernate default mapping

Code Block

language	java

private String vcharTest;

Code Block

language	java

@Column(name = "VcharTest", length = 50)
public String getVcharTest() {
	return this.vcharTest;
}

*p (precision)

The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38.

The default precision is 18.

Precision	Storage bytes
1 - 9	5
10-19	9
20-28	13
29-38	17

...

Versions Compared

Old Version 2

New Version Current

Key