8 - Отображение SQL- и Java-типов данных

8.1    Введение

Этот механизм состоит из методов getXXX и setXXX, метода registerOutParameter и класса Types.

В этом разделе обсуждается все, что касается типов данных и их преобразование, а также приведены соответствующие таблицы преобразования типов.

8.2    Отображение типов данных SQL в типы данных Java

К счастью, программистам не обязательно отягощать себя именами типов SQL, используемых в таблицах БД. Большую часть времени прораммисты создают программы над уже созданной структурой БД, и им не приходится заботиться о названиях типов данных, используемых при создании таблиц.

JDBC объявляет набор базовых типов SQL в классе java.sql.Types. Эти типы отвечают наиболее типично используемые в БД типы данных. При программировании с использованием JDBC API программисты могут использовать эти JDBC-типы для обращения к базовым типам SQL незаботясь о том, какие имена типов данных использовались при создании БД. Эти типы полностью описаны в следующем разделе.

Наиболее типичный запрос, в котором существует необходимость использовать имена типов данных, - это SQL-запрос создания таблицы CREATE TABLE. Тут-то программист и должен позаботиться о правильном именовании типов данных. Если Вам нужно описание типов данных, поддерживаемых Вашей СУБД, то справьтесь об этом в документации к данной СУБД.

Если Вы хотите, чтобы Ваше приложение легко портировалось на различные СУБД, Вы должны выбрать одно из двух: либо используйте широко распространенные имена типов, такие как INTEGER, NUMERIC или VARCHAR, которые будут работать с любой СУБД, либо используйте метод java.sql.DatabaseMetaData.getTypeInfo для получения информации о SQL-типах, поддерживаемых данной СУБД и выбора имени SQL-типа, соответствующего определенному JDBC-типу.

JDBC определяет стандартное отображение JDBC-типов в Java-типы. Например, INTEGER соответствует int. Отображение позволяет манипулировать SQL-значениями с помощью типов данных языка Java.

Совсем не обязательно, что Java-типы должны полностью совпадать с JDBC-типами; они лишь должны быть способны без потерь сохранять и считывать параметры и считывать результаты SQL-запросов. Например, Java-объект String не совпадает в точности с JDBC-типами CHAR, однако достаточен для сохранения полной информации о значениях типа CHAR, VARCHAR и LONGVARCHAR.

8.3    Типы данных JDBC

8.3.1     CHAR, VARCHAR и LONGVARCHAR

Тип данных SQL CHAR, соответствующий JDBC-типу CHAR, определен в SQL-92 и поддерживается всеми СУБД. Он также принимает параметр, равный длине сроки. Так, CHAR(12) определяет 12-символьную строку. Все основные СУБД поддерживают длину CHAR до 254 символов.

Тип данных VARCHAR тоже определен в SQL-92 и поддерживается подавляющим большинством СУБД. Он принимает параметр, указывающий максимальную длину строки. Так, VARCHAR(12) описывает строку, длина которой может быть до 12 символов. В большинстве СУБД максимальная длина VARCHAR составляет 254 символов. При присвоении переменной типа VARCHAR строкового значения, БД запоминает длину этой строки и при выборке значения возвратит в точности первоначальную строку.

К сожалению, в SQL не существует устоявшегося эквивалента для типа LONGVARCHAR. Большинство СУБД поддерживают очень большие строки длиной аж до гигабайта, но имена таких SQL-типов различаются.

Программист не обязан различать три типа строк JDBC - CHAR, VARCHAR и LONGVARCHAR. Каждая строка может быть выражена объектом Java String, и существует возможность читать и писать SQL-операторы привильно не зная о том, какой именно из типов данных используется.

CHAR, VARCHAR и LONGVARCHAR должны отображаться либо в String, либо в char[], но обычно именно String. Конвертирование из String в char[] выполняется коструктором new String с параметром char[]. Обратное преобразование выполняется методом String.getChars().

Стоит остановиться на обработке строк фиксированной длины CHAR(n). Эта обработка заклчается в том, что СУБД (или драйвер) дополняет строку до нужной длины пробелами. Т.е. когда поле CHAR(n) извлекается из БД, драйвер сконвертирует его в объект String с длиной ровно n и пробелами в конце. И наоборот, когда объект String записывается в поле CHAR(n), драйвер или СУБД преоразует его, дополнив с конца пробелами до длины n.

Метод ResultSet.getString, используемый для типов данных CHAR, VARCHAR и LONGVARCHAR, подходит для извлечения нормальных данных, но будет нехорошо, если значение LONGVARCHAR, занимающее несколько мегабайт, окажется в объекте String. Поэтому интерфейс ResultSet позволяет программисту извлекать значение LONGVARCHAR в виде потока ввода, из которого можно последовательно считывать данные кусками любого размера. Методы getAsciiStream и getUnicodeStream позволяют доставлять данные, сохраненные в колонке типа LONGVARCHAR, в виде потока Ascii- или Unicode-символов.

8.3.2     BINARY, VARBINARY и LONGVARBINARY

К сожалению, использование этих типов не было стандартизовано, и различные СУБД поддерживают их в разнобой.

SQL-тип данных BINARY, соответствующий JDBC-типу BINARY - это нестандартное расширение SQL и реализовано только в некоторых СУБД. Этот тип принимает параметр, указывающий количество байтов. Например, BINARY(12) описывает 12-байтный двоичный тип данных. Обычно значения BINARY ограничены 254 байтами.

Тип SQL VARBINARY, соответствующий типу JDBC VARBINARY - это тоже нестандартное расширение SQL, реализованное только в некоторых СУБД. Он принимает параметр - максимальную длину последовательности байтов. Т.о. VARBINARY(12) описывает двоичные данные, чья длина может быть до 12-и байтов. Обычно значения VARBINARY ограничены 254 байтами. При присвоении переменной VARBINARY значения БД запоминает его длину, поэтому при выборке значения получается в точности оригинальное значение.

К сожалению, для JDBC-типа LONGVARBINARY не существует устойчивого SQL-эквивалента. Все основные СУБД поддерживают несколько видов типов очень больших двоичных данных, поддерживая максимальную длину как минимум гигабайт. При этом имена SQL-типов различаются.

BINARY, VARBINARY и LONGVARBINARY могут быть выражены в виде массивов byte[] в Java.

Для извлечении значений BINARY и VARBINARY рекомендуется использовать метод ResultSet.getBytes. Однако, если LONGVARBINARY хранит много мегабайтов, то лучше использовать getBinaryStream, как и случае с LONGVARCHAR. С помощью него можно считывать результат покусочно.

8.3.3     BIT

SQL-92 определяет тип данных BIT. В отличие от JDBC, BIT в SQL-92 может использоваться как параметризованный тип данных в виде двоичной строки с фиксированной длиной. SQL-92 также разрешает использовать бит автономно, не в составе битовой строки - как раз то, что и подразумевается под битом в JDBC. К сожалению, тип BIT требуется только в полной спецификации SQL-92 и отсутствует во многих СУБД. В переносимом коде предпочитается использовать тип JDBC SMALLINT, который поддерживается всеми.

Рекомендуемое отображение для типа JDBC BIT - это boolean в языке Java.

8.3.4     TINYINT

Соответствующий тип в SQL, тоже TINYINT, в настоящее время существует не во всех СУБД, поэтому лучше использовать SMALLINT.

Рекомендуемое отображение для типа JDBC TINYINT - это либо byte, либо short. 8-битный byte является знаковым целым от -128 до 127, поэтому нам не совсем подходит. Java-тип short умещает в себе весь диапазон значений TINYINT, поэтому лучше использовать именно его.

8.3.5     SMALLINT

Соответствующий тип в SQL, SMALLINT, объявленный в SQL-92, широко используется практически всеми СУБД.

Предпочтительным эквивалентом этого типа в языке Java является short.

8.3.6     INTEGER

Соответствующий SQL-тип INTEGER определен в SQL-92 и широко используется всеми СУБД.

Предпочтительным эквивалентом типа INTEGER в Javе является int.

8.3.7     BIGINT

Соответствующий ему тип SQL BIGINT является нестандартным расширением языка SQL. На практике этот тип данных не реализован в СУБД, поэтому в портируемом коде рекомендуется избегать его использования.

Эквивалентом типа BIGINT в языке Java служит long.

8.3.8     REAL

Соответствующий тип в SQL REAL определен в SQL-92 и широко, если не универсально, используется всеми СУБД.

Рекомендуемый эквивалент для типа REAL в Java - это float.

8.3.9     DOUBLE

Соответствующий ему SQL-тип, DOUBLE PRECISION, определен в SQL-92 и широко используется во всех основных СУБД.

DOUBLE отображается на тип Java double.

8.3.10     FLOAT

Рекомендуемое отображение типа FLOAT - это тип Java double. Вследствие потенциальной угрозы путаницы между числами двойной точности в SQL FLOAT и числами одинарной точности в Java float лучше использовать тип JDBC DOUBLE.

8.3.11     DECIMAL and NUMERIC

Соответствующие типы данных SQL, DECIMAL и NUMERIC, определены в стандарте SQL-92 и очень распространены. Эти типы принимают два параметра - точность (precision) и масштаб (scale). Точность - это общее количество десятичных цифр, а масштаб - число цифр после точки. Масштаб должен быть всегда не больше точности. Например, "12.345" имеет точность 5 и масштаб 3, а значение ".11" - точность 2 и такой же масштаб. JDBC требует, чтобы как DECIMAL, так и NUMERIC поддерживали максимальное значение точности и масштаба как минимум 15.

Единственное отличие DECIMAL отd NUMERIC в спецификации SQL-92 - в том, что NUMERIC именно указанную точность, в то время как для типов данных DECIMAL возможно увеличение точности за пределы того значения, которое было задано при создании типа. Таким образом, колонка, созданная как NUMERIC(12,4) всегда будет представлена 12-ю цифрами, в то время как колонка, определенная как DECIMAL(12,4), может хранить немного более точные значения.

Рекомендуемое отображение типов DECIMAL и NUMERIC - это объект java.math.BigDecimal, который также представляет собой числа с фиксированной точкой. Над типами java.math.BigDecimal можно выполнять арифметические операции сложения, вычитания, умножения и деления как с другими объектами java.math.BigDecimal, так и с целыми числами и числами с плавающей запятой.

Метод, рекомендуемый для извлечения значений типа DECIMAL и NUMERIC - это ResultSet.getBigDecimal. JDBC также позволяет трактовать эти значения как обычные строки или массивы символов. Т.е. программист может использовать метод getString для чтения DECIMAL или NUMERIC.

8.3.12     DATE, TIME и TIMESTAMP

• Тип DATE представляет дату, содержащую день, месяц и год. Соответствующий SQL-тип DATE определен в SQL-92, но реализован не во всех СУБД. Некоторые БД предлагают альтернативные типы SQL, поддерживающие ту же семантику.
  
  
• JDBC-тип TIME представляет собой время, состоящее из часов, минут и секунд. Соответствующий тип в SQL, тоже TIME, определен в SQL-92, но реализован только в нескольких СУБД. Как и с датой, некоторые БД предлагают альтернативы типу TIME.
  
  
• JDBC-тип TIMESTAMP представляет DATE плюс TIME плюс поле наносекунды. Соответствующий тип в SQL, TIMESTAMP, определен в SQL-92, но реализован не во всех СУБД.
  
  

• java.sql.Date для информации о DATE. Поля часа, минуты, секунды и миллисекунды базового класса java.util.Date устанавливаются в 0.
  
  
• java.sql.Time для TIME. Поля года, месяца и дня базового класса java.util.Date устанавливаются в 1970, январь и 1 соответственно. Это "нулевая" дата для Java.
  
  
• java.sql.Timestamp для TIMESTAMP. Этот класс расширяет java.util.Date добавлением наносекундного поля.
  
  

Объекты Timestamp состоят из унаследованных компонентов даты и времени, а также отдельной компоненты - наносекунды. Если объект java.sql.Timestamp используется так, где ожидается java.util.Date, то наносекундная составляющая теряется. Тем не менее, поскольку объект java.util.Date хранится с точностью до миллисекунды, эту степень точности можно сохранить и при конвертировании объекта из java.sql.Timestamp в java.util.Date. Это делается преобразованием наносекунд в миллисекунды (делением на 1000000) и прибавлением результата к объекту java.util.Date object. При таком преобразовании может быть потеряно до 999,999 наносекунд, но точность полученного объекта java.util.Date будет до одной миллисекундды.

В этом фрагменте кода демонстрируется проеобразование объекта java.sql.Timestamp в java.util.Date с точностью до миллисекунды:

    Timestamp t = new Timestamp(100, 0, 1, 15, 45, 29, 987245732);
    java.util.Date d;
    d = new java.util.Date(t.getTime() + (t.getNanos() / 1000000));

8.4    Примеры отображения

8.4.1     Простой SQL-запрос

    String query = "select * from Table1";
    ResultSet rs = stmt.executeQuery(query);
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    for (int i = 1; i <= columnCount; i++)  {
      String s = rsmd.getColumnTypeName(i);
      System.out.println ("Column " + i + " is type " + s);
    }

8.4.2     SQL-выражение с входными (IN) параметрами

8.4.3     SQL-выражение с параметрами INOUT

В этом сюжете первое, что необходимо сделать, это присвоить значения параметрам INOUT с помощью методов PreparedStatement.setXXX. К тому же, так как параметры также использоваться и для вывода, программист должен зарегистрировать тип данных каждого параметра. Это делается методом CallableStatement.registerOutParameter, который принимает в виде аргумента один из JDBC-типов, объявленных в классе Types. Программист считывает результаты, возвращенные в объект ResultSet, с помощью методов ResultSet.getXXX, а значения выходных параметров - с помощью CallableStatement.getXXX. Таблица в разделе 8.6.6 показывает, какие из методов ResultSet.getXXX использовать для считывания каких JDBC-типов.

Тип XXX в CallableStatement.getXXX должен соответствовать JDBC-типу, зарегистрированному для данного параметра. Например, если от БД ожидается значение REAL, то параметр должен быть зарегистрирован как java.sql.Types.REAL. Для получения значения типа REAL используется метод CallableStatement.getFloat.

Следующий пример демонстрирует вызов хранимой процедуры с названием getTestData с двумя INOUT параметрами. Сначала объект Connection con создает объект CallableStatement cstmt. Потом метод setByte устанавливает первый параметр в 25 (типа byte). Драйвер сконвертирует 25 в TINYINT. Метод setBigDecimal устанавливает второй параметр в значение 83.75. Драйвер сконвертирует его из java.math.BigDecimal в значение JDBC NUMERIC. Далее регистрируются параметры: первый - как TINYINT, второй - DECIMAL. После выполнения cstmt значения считываются из объекта ResultSet с помощью методов ResultSet.getXXX. Метод getString получает значение первой колонки в виде Java-объекта String, getInt возвращает значение второй колонки в виде int.

Потом методы CallableStatement.getXXX извлекают выходные значения параметров. Метод getByte возвращает TINYINT в виде byte, а getBigDecimal возвращает DECIMAL в виде java.math.BigDecimal.

    CallableStatement cstmt = con.prepareCall(
          "{call getTestData(?, ?)}");
    cstmt.setByte(1, 25);
    cstmt.setBigDecimal(2, 83.75);
    // зарегистрировать первый параметр как JDBC TINYINT, а второй-
    // как JDBC DECIMAL с двумя цифрами после запятой
    cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
    cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 2);
    ResultSet rs = cstmt.executeUpdate();
    // извлечь и напечатать значения из набора данных
    while(rs.next()) {   
      String name = rs.getString(1);
      int score = rs.getInt(2);
      int percentile = rs.getInt(3);
      System.out.print("name = " + name + ", score = " + score + ", "
      System.out.println("percentile = " + percentile);          
    // извлечь значения из выходных параметров
    byte x = cstmt.getByte(1); 
    java.math.BigDecimal n = cstmt.getBigDecimal(2, 2); 

8.5    Динамический доступ к данным

Следующие три метода и одна константа упрощают доступ к таким данным, чьи типы неизвестны на этапе компиляции:

• ResultSet.getObject
  
  
• PreparedStatement.setObject
  
  
• CallableStatement.getObject
  
  
• java.sql.Types.OTHER (используется в виде аргумента метода CallableStatement.registerOutParameter)
  
  

Методы ResultSet.getObject и CallableStatement.getObject возвращают значения в виде объекта Java Object. Так как Object является базовым классом для всех объектов Java, то экземпляр любого Java-класса может быть получен как экземпляр типа Object. Исключением являются встроенные примитивные типы данных, не являющиеся объектами: boolean, char, byte, short, int, long, float и double. Эти типы данных не могут быть считаны методом getObject. Тем не менее каждый примитивный класс имеет свою объектную "оболочку", то есть представление в виде объекта.

8.6    Таблицы отображения типов данных

8.6.1     Отображение JDBC-типов в Java-типы

JDBC type	Java type
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	boolean
TINYINT	byte
SMALLINT	short
INTEGER	int
BIGINT	long
REAL	float
FLOAT	double
DOUBLE	double
BINARY	byte[]
VARBINARY	byte[]
LONGVARBINARY	byte[]
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp

8.6.2     Отображение Java-типов в JDBC-типы

Отображение String обычно осуществляется в VARCHAR, но может и в LONGVARCHAR, если длина строки превысит максимально допустимый предел для VARVHAR. Это же касается и отображения byte[] на VARBINARY и LONGVARBINARY.

8.6.3     Отображение JDBC-типов на объектные типы Java

JDBC Type	Java Object Type
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	Boolean
TINYINT	Integer
SMALLINT	Integer
INTEGER	Integer
BIGINT	Long
REAL	Float
FLOAT	Double
DOUBLE	Double
BINARY	byte[]
VARBINARY	byte[]
LONGVARBINARY	byte[]
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp

8.6.4     Отображение объектных типов Java в типы JDBC

Java Object Type	JDBC Type
String	VARCHAR or LONGVARCHAR
java.math.BigDecimal	NUMERIC
Boolean	BIT
Integer	INTEGER
Long	BIGINT
Float	REAL
Double	DOUBLE
byte[]	VARBINARY or LONGVARBINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

Отображение String обычно осуществляется в VARCHAR, но может и в LONGVARCHAR, если длина строки превысит максимально допустимый предел для VARVHAR. Это же касается и отображения byte[] на VARBINARY и LONGVARBINARY.

8.6.5     Преобразование в методе setObject

	T I N Y I N T	S M A L L I N T	I N T E G E R	B I G I N T	R E A L	F L O A T	D O U B L E	D E C I M A L	N U M E R I C	B I T	C H A R	V A R C H A R	L O N G V A R C H A R	B I N A R Y	V A R B I N A R Y	L O N G V A R B I N A R Y	D A T E	T I M E	T I M E S T A M P
String	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x
java.math.BigDecimal	x	x	x	x	x	x	x	x	x	x	x	x	x
Boolean	x	x	x	x	x	x	x	x	x	x	x	x	x
Integer	x	x	x	x	x	x	x	x	x	x	x	x	x
Long	x	x	x	x	x	x	x	x	x	x	x	x	x
Float	x	x	x	x	x	x	x	x	x	x	x	x	x
Double	x	x	x	x	x	x	x	x	x	x	x	x	x
byte[]														x	x	x
java.sql.Date											x	x	x				x		x
java.sql.Time											x	x	x					x
java.sql.Time- stamp											x	x	x				x	x	x

8.6.6     JDBC-типы, возвращаемые методами ResultSet.getXXX

	T I N Y I N T	S M A L L I N T	I N T E G E R	B I G I N T	R E A L	F L O A T	D O U B L E	D E C I M A L	N U M E R I C	B I T	C H A R	V A R C H A R	L O N G V A R C H A R	B I N A R Y	V A R B I N A R Y	L O N G V A R B I N A R Y	D A T E	T I M E	T I M E S T A M P
getByte	X	x	x	x	x	x	x	x	x	x	x	x	x
getShort	x	X	x	x	x	x	x	x	x	x	x	x	x
getInt	x	x	X	x	x	x	x	x	x	x	x	x	x
getLong	x	x	x	X	x	x	x	x	x	x	x	x	x
getFloat	x	x	x	x	X	x	x	x	x	x	x	x	x
getDouble	x	x	x	x	x	X	X	x	x	x	x	x	x
getBigDecimal	x	x	x	x	x	x	x	X	X	x	x	x	x
getBoolean	x	x	x	x	x	x	x	x	x	X	x	x	x
getString	x	x	x	x	x	x	x	x	x	x	X	X	x	x	x	x	x	x	x
getBytes														X	X	x
getDate											x	x	x				X		x
getTime											x	x	x					X	x
getTimestamp											x	x	x				x		X
getAsciiStream											x	x	X	x	x	x
getUnicodeStream											x	x	X	x	x	x
getBinaryStream														x	x	X
getObject	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x