Типы данных в 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 и выполнить следующий скрипт:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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 и следующим содержимым:
|
1
2
3
4
|
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 не отличается от обычной — они отображаются по правилам своих базовых типов и используются аналогично:
|
1
2
3
4
5
6
|
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"));
}
}
|
|
1
|
Zip code is:127001
|
Массивы
Некоторые базы данных позволяют хранить массивы непосредственно в массивах. То есть в каждой строке для какой-либо колонки может храниться не одно значение, а сразу несколько. Пример из таблицы выше — колонка employees. В JDBC для работы с массивами предусмотрен специальный метод:
|
1
2
3
4
5
6
7
|
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 получают открытый поток и обрабатывают его.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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")));
}
}
}
|
|
1
2
|
Uploaded file md5 is: 8603196f9126d8783e6b52834ddf482c
Downloaded file md5 is: 8603196f9126d8783e6b52834ddf482c
|
Составные типы
Составные типы это другой метод засунуть в один столбец множество значений. Проще всего представлять их как key-value объект, хранящийся в колонках или таблицы внутри таблицы. Тип STREETADDRESS из скрипта выше даёт хорошее представление, что это такое. К сожалению JDBC драйвер PostgreSQL не реализует стандартного механизма отображения таких типов, поэтому данный код в примере закомментирован.
Итак, для составных типов в JDBC предусмотрен механизм отображения таких типов в объекты. Объект должен реализовывать интерфейс SQLData и, следовательно, уметь строить себя самого из данных составного типа и уметь себя преобразовывать в составной тип.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
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);
}
}
|
Этот объект регистрируется в таблице соответствий типов:
|
1
2
3
|
Map<String, Class<?>> typeMap = db.getTypeMap;
typeMap.put("STREETADDRESS", Address.class);
db.setTypeMap(typeMap);
|
И в дальнейшем можно напрямую получать его из базы данных:
|
1
2
3
4
5
6
7
8
9
|
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.