Типы данных в 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;

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

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"));
  }
}

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

1	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);
  }
}

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")));
    }
  }
}

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

1 2	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);
  }
}

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);

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());
  }
}

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());

}

Код примера доступен на github.