SQL Server type and Java Type conversion problem resolution

The ResultSet interface provides a GET method for getting column values from the current row (Getboolean, Getlong, and so on). You can use the index number of a column or the name of a column to get a value. In general, the use of column indexes is more efficient. The column is numbered starting at 1. For maximum portability, the result set columns in each row should be read in left-to-right order, and each column can be read only once.
For the Fetch method, the JDBC driver attempts to convert the underlying data to the Java type specified in the Get method and returns the appropriate Java value. The JDBC specification has a table that shows the mappings of the Java types that are allowed from the SQL type to the ResultSet fetch method.

SQL Server type	JDBC type (java.sql. Type)	Java language Type
bigint	BIGINT	Long
Timestamp Binary	BINARY	Byte[]
Bit	BIT	Boolean
Charnchar	CHAR	String
Decimal Money SmallMoney	DECIMAL	Java.math.BigDecimal
Float	DOUBLE	Double
Int	INTEGER	Int
Image	LongVarBinary	Byte[]
Text ntext	LongVarChar	String
Numeric	NUMERIC	Java.math.BigDecimal
Real	REAL	Float
smallint tinyint	SMALLINT	Short
Datetime smalldatetime	TIMESTAMP	Java.sql.Timestamp
varbinary	VARBINARY	Byte[]
varchar nvarchar uniqueidentifier	VARCHAR	String

Note: The JDBC driver currently does not support SQL Server sqlvariant data types. If you use a query to retrieve data from a table that contains Sqlvariant data type columns, an exception occurs.

Getter Method Conversion

Based on the SQL Server 2005 data type, the table contains the JDBC driver transformation diagram for the get<type> method of the Sqlserverresultset class, and Sqlservercallablestateme The get<type> method of the NT class supports conversions.

The getter method of the JDBC driver supports three basic types of conversions:

• Non-lossy (x) : Performs the conversion in cases where the getter type is equal to or less than the underlying server type. For example, when you call getbigdecimal on the base server's decimal column, you do not need to convert.
• converted (y) : Conversion from a digital server type to a Java language type, at which time the conversion is a regular conversion and follows the Java language translation rules. For these conversions, there is always a direct intercept of the significant digits (never rounded), and the overflow is modeled on the target type, whichever is smaller. For example, calling getInt for the underlying decimal sequence containing "1.9999" will return "1", or the int value will overflow to "1294967296" if the underlying decimal value is "3000000000".
• Data Dependent (z) : When converting from an underlying character type to a numeric type, it is required that the value contained in the character type be converted to a numeric type. No other conversions are performed. If the value is too large for the getter type, the value is not valid. For example, if you call getInt on a varchar (50) column that contains "53", the value is returned as an int, but an error occurs if the underlying value is "xyz" or "3000000000".

(Excerpt from Microsoft's SQLJDBC help document)

1. For the Getdouble,getfloat,getint method in resultset, NULL becomes 0 (if the value is SQL null, the return value is 0) because the base type data is returned. This has brought us a lot of trouble. To avoid this problem, it is necessary to use the Resultset#wasnull () method

2. Resultset#getdate. I haven't noticed this guy before. The returned type is java.sql.Date, and the data returned when the database field type is DateTime is only a month and a day, with Resultset#gettimestamp there is no problem, depressed, until now do not know why.

3. Related resources:

Http://java.sun.com/products/jdbc/overview.html

http://msdn.microsoft.com/zh-cn/data/aa937724 (en-us). aspx

SQL Server type and Java Type conversion problem resolution