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