JDBC и отображение типов SQL в Java

Типы данных в Java и в SQL базах данных немного отличаются. Я говорю не о том, что Java работает с объектами, в то время как SQL работает с таблицами, а о примитивных типах, таких как String или long. JDBC автоматически отображает Java типы на SQL типы и наоборот.

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

  • String обычно соответствует SQL типам CHAR, VARCHAR.
  • Integer отображается в SQL типы INT, BIGINT или SMALLINT.
  • Boolean в SQL типы BOOLEAN или CHAR.
  • Long в SQL тип BIGINT
  • BigDecimal хранят в SQL типе DECIMAL
  • Float и Double чаще всего соответствуют одному SQL типу FLOAT.
  • Для хранения даты и времени в JDBC есть собственные типы java.sql.Date, java.sql.Time и java.sql.Timestamp, которые отображаются в соответствующий тип базы данных.

Кроме вышеперечисленных «обычных» типов данных, большинство баз данных умеет работать с расширенными типами данных и пользовательскими типами данных: бинарные объекты, массивы, составные типы и т. д.

Подготовка

Код из примера ниже использует PostgreSQL. Переда запуском примера необходимо установить сервер PostgreSQL и выполнить следующий скрипт:

CREATE ROLE types WITH PASSWORD 'types';
ALTER ROLE types WITH LOGIN;


CREATE DATABASE types OWNER types;


\c types


CREATE DOMAIN ZIPCODE AS VARCHAR(6) CHECK( VALUE ~ '^\d{6}$' );


CREATE TYPE STREETADDRESS AS (city TEXT,  street TEXT, building INT);


CREATE TABLE POSTOFFICE ( id SERIAL PRIMARY KEY, photo bytea, employees varchar(64)[], address STREETADDRESS, code ZIPCODE);


INSERT INTO POSTOFFICE (employees, address, code) VALUES ('{John Doe, Jane Doe}', ('Nowhere', 'Main st.', '17'), '127001');


GRANT SELECT,UPDATE ON TABLE postoffice TO types;

В результате выполнения скрипта должна создаться база types с таблицей postoffice и следующим содержимым:

SELECT * FROM POSTOFFICE;
id | photo |        employees        |         address         |  code
----+-------+-------------------------+-------------------------+--------
  1 |       | {"John Doe","Jane Doe"} | (Nowhere,"Main st.",17) | 127001

Кроме того, следует в файле pg_hba.conf разрешить доступ пользователю types и в исходном коде примера заменить адрес сервера с ‘127.0.0.1’ на адрес вашего PostgreSQL сервера, если он установлен не на локальной машине.

Именованные типы

В разных базах данных именованные типы называются по разному. Кто-то называет их distinct type, кто-то domain type, но суть одна: существующему в базе данных типу можно присвоить другое имя и наложить какие-либо ограничения. Пример такого типа в скрипте выше — ZIPCODE. Работа с такими типами в JDBC не отличается от обычной — они отображаются по правилам своих базовых типов и используются аналогично:

try (Statement zipSt = db.createStatement()) {
  try (ResultSet rs = zipSt.executeQuery("SELECT code FROM postoffice WHERE id=1")) {
    rs.next();
    System.out.println("Zip code is:" + rs.getString("code"));
  }
}
Zip code is:127001

Массивы

Некоторые базы данных позволяют хранить массивы непосредственно в массивах. То есть в каждой строке для какой-либо колонки может храниться не одно значение, а сразу несколько. Пример из таблицы выше — колонка employees. В JDBC для работы с массивами предусмотрен специальный метод:

try (Statement arraySt = db.createStatement()) {
  try (ResultSet rs = arraySt.executeQuery("SELECT employees FROM postoffice WHERE id=1")) {
  rs.next();
  Stream.of((String[])rs.getArray("employees").getArray())
    .forEach(System.out::println);
  }
}

Как и в случае обычных типов данных, вместо имени столбца можно использовать его номер. Массивы можно и обновлять, как обычные поля, используя метод updateArray(), которые принимает или номер или имя столбца и новый массив.

Блобы

Большинство баз данных поддерживает хранение в таблицах бинарных объектов неограниченного размера. Чаще всего ограничение конечно есть, но оно достаточно большое, так что можно говорить о неограниченном размере. Такие объекты (BLOB — Binary Large OBject) конечно не могут быть использованы в запросах напрямую или проиндексированы, что уменьшает их полезность. Да и вообще, обычно лучше хранить такие объекты в файловой системе, а в базе оставить только ссылки на них. Но если надо, можно и базе хранить.

В JDBC для работы с блобами используют IO streams, так что можно рассматривать их как файлы, хранящиеся в необычном месте. Для сохранения блоба в базу используется заранее открытый поток с данными. Для чтения наоборот, от JDBC получают открытый поток и обрабатывают его.

try (InputStream blobSource = App.class.getClassLoader().getResourceAsStream("Blob.jpg")) {
  System.out.println("Uploaded file md5 is: " + DigestUtils.md5Hex(blobSource));
}
try (InputStream blobSource = App.class.getClassLoader().getResourceAsStream("Blob.jpg")) {
  try (PreparedStatement uploadSt = db.prepareStatement(ADD_BLOB)) {
    uploadSt.setBinaryStream(1, blobSource);
    uploadSt.executeUpdate();
  }


  try (Statement downloadSt = db.createStatement()) {
    try (ResultSet rs = downloadSt.executeQuery("SELECT photo FROM postoffice WHERE id=1")) {
      rs.next();
      System.out.println("Downloaded file md5 is: " + DigestUtils.md5Hex(rs.getBinaryStream("photo")));
    }
  }
}
Uploaded file md5 is: 8603196f9126d8783e6b52834ddf482c
Downloaded file md5 is: 8603196f9126d8783e6b52834ddf482c

Составные типы

Составные типы это другой метод засунуть в один столбец множество значений. Проще всего представлять их как key-value объект, хранящийся в колонках или таблицы внутри таблицы. Тип STREETADDRESS из скрипта выше даёт хорошее представление, что это такое. К сожалению JDBC драйвер PostgreSQL не реализует стандартного механизма отображения таких типов, поэтому данный код в примере закомментирован.

Итак, для составных типов в JDBC предусмотрен механизм отображения таких типов в объекты. Объект должен реализовывать интерфейс SQLData и, следовательно, уметь строить себя самого из данных составного типа и уметь себя преобразовывать в составной тип.

public class Address implements SQLData {
  public String city;
  public String street;
  public Integer building;
  private String sql_type;


  @Override
  public String getSQLTypeName() throws SQLException {
    return sql_type;
  }


  @Override
  public void readSQL(SQLInput stream, String typeName) throws SQLException {
    sql_type = typeName;
    city = stream.readString();
    street = stream.readString();
    building = stream.readInt();
  }


  @Override
  public void writeSQL(SQLOutput stream) throws SQLException {
    stream.writeString(city);
    stream.writeString(street);
    stream.writeInt(building);
  }
}

Этот объект регистрируется в таблице соответствий типов:

Map<String, Class<?>> typeMap = db.getTypeMap;
typeMap.put("STREETADDRESS", Address.class);
db.setTypeMap(typeMap);

И в дальнейшем можно напрямую получать его из базы данных:

try (Statement typeSt = db.createStatement()) {
  try (ResultSet rs = typeSt.executeQuery("SELECT address FROM postoffice WHERE id=1")) {
    rs.next();
    Address address = (Address)rs.getObject("address");
    System.out.println("City: " + address.city);
    System.out.println("Street: " + address.street);
    System.out.println("Building: " + address.building.toString());
  }
}

Скачать код примера

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *