Запросы к базе данных делаются с помощью Spring JDBC гораздо проще.
Поскольку Spring сам может выполнять SQL скрипты при запуске приложения, для этой статьи я подготовил достаточно развесистую схему данных, чтобы примеры выглядели интереснее.
Удел Java обычно enterprise приложения, поэтому пример самый что ни на есть энтерпрайзный. Предположим, что мы пишем систему отслеживания заказов, в которой есть клиенты, товары и, собственно, заказы. Заказ принадлежит клиенту и связан со списком входящих в него товаров. Итого четыре таблицы, как показано на E/R диаграмме:
По этой диаграмме я подготовил скрипт, который создаёт таблицы и наполняет их данными.
|
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
CREATE TABLE CUSTOMERS (
ID IDENTITY PRIMARY KEY,
EMAIL VARCHAR(128) UNIQUE NOT NULL
);
CREATE TABLE SKUS (
ID IDENTITY PRIMARY KEY,
DESCRIPTION VARCHAR(256) NOT NULL
);
CREATE TABLE ORDERS (
ID IDENTITY PRIMARY KEY,
CUSTOMER_ID BIGINT NOT NULL,
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID)
);
CREATE TABLE ORDER_ITEMS (
ID IDENTITY PRIMARY KEY,
ORDER_ID BIGINT NOT NULL,
SKU_ID BIGINT NOT NULL,
QUANTITY INTEGER NOT NULL,
FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ID),
FOREIGN KEY (SKU_ID) REFERENCES SKUS(ID)
);
ALTER TABLE ORDER_ITEMS ADD UNIQUE (ORDER_ID, SKU_ID);
--
INSERT INTO SKUS(ID, DESCRIPTION) VALUES (1, 'Sample SKU #1');
INSERT INTO SKUS(ID, DESCRIPTION) VALUES (2, 'Sample SKU #2');
INSERT INTO SKUS(ID, DESCRIPTION) VALUES (3, 'Sample SKU #3');
INSERT INTO ORDERS(ID, CUSTOMER_ID) VALUES (1, 1);
INSERT INTO ORDERS(ID, CUSTOMER_ID) VALUES (2, 2);
INSERT INTO ORDERS(ID, CUSTOMER_ID) VALUES (3, 2);
INSERT INTO ORDERS(ID, CUSTOMER_ID) VALUES (4, 1);
INSERT INTO ORDERS(ID, CUSTOMER_ID) VALUES (5, 2);
INSERT INTO ORDERS(ID, CUSTOMER_ID) VALUES (6, 1);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (1, 1, 1);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (2, 2, 2);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (2, 3, 4);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (3, 1, 2);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (4, 1, 1);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (4, 2, 1);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (4, 3, 1);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (5, 2, 3);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (6, 1, 5);
INSERT INTO ORDER_ITEMS(ORDER_ID, SKU_ID, QUANTITY) VALUES (6, 3, 3);
|
Простые запросы
Если запрос возвращает ровным счётом одно значение, то есть одну строку с единственной колонкой, его значение можно получить напрямую из метода queryForObject()
|
1
2
3
4
5
|
private static final String EMAIL_QUERY = "SELECT EMAIL FROM CUSTOMERS WHERE ID=?";
public String getEmail(Integer id) {
return jdbcTemplate.queryForObject(EMAIL_QUERY, String.class, id);
}
|
|
1
2
3
4
|
@Test
public void testGetEmail() {
assertThat(testedObject.getEmail(100), is("TEST"));
}
|
Достаточно лишь передать тип, к которому надо привести результат, и указать параметры запроса.
Если столбец всё ещё один, а строк много, можно запросить список объектов подходящего класса:
|
1
2
3
4
5
6
|
private static final String ALL_QUERY = "SELECT EMAIL FROM CUSTOMERS";
@Override
public List<String> allEmails() {
return jdbcTemplate.queryForList(ALL_QUERY, String.class);
}
|
|
1
2
3
4
|
@Test
public void allEmails() {
assertThat(testedObject.allEmails(), hasItem("TEST"));
}
|
И в этом случае достаточно передать тип результата и указать параметры запроса, если они требуются.
Если же запрос возвращает обычную таблицу, в которой много строк и много столбцов, то можно попросить вернуть список key-value значений по строкам, как я писал раньше:
|
1
2
3
4
|
@Override
public List<Map<String, Object>> all() {
return jdbcTemplate.queryForList(ALL_QUERY);
}
|
|
1
2
3
4
|
@Test
public void testAll() {
assertTrue(testedObject.all().stream().filter(m -> "TEST".equals(m.get("EMAIL"))).findFirst().isPresent());
}
|
Object Mapping
Второй вариант — отображение строки на объект. Например, заказ состоит из номера заказа и заказчика, который, в свою очередь, состоит из номера заказчика и e-mail. Мы можем получить из метода query*() сразу готовый объект заказа, реализовав отображение sql данных в объекты.
Для начала создадим классы заказчика и заказа:
|
1
2
3
4
5
|
@Data
public class Customer {
private Integer id;
private String email;
}
|
|
1
2
3
4
5
|
@Data
public class Order {
private Integer id;
private Customer customer;
}
|
Я использовал project lombok, чтобы сделать их покороче.
Теперь, когда у нас есть готовые классы, напишем класс для отображения данных из SQL. Отображающий класс реализует интерфейс RowMapper из Spring JDBC:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
public class OrderMapper implements RowMapper<Order> {
@Override
public Order mapRow(ResultSet resultSet, int i) throws SQLException {
Customer customer = new Customer();
customer.setId(resultSet.getInt("customer_id"));
customer.setEmail(resultSet.getString("email"));
Order order = new Order();
order.setId(resultSet.getInt("id"));
order.setCustomer(customer);
return order;
}
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
@Test
public void testMapping() throws SQLException {
expect(rs.getString("email")).andStubReturn("TEST");
expect(rs.getInt("customer_id")).andStubReturn(100);
expect(rs.getInt("id")).andStubReturn(500);
replayAll();
Order actual = testedObject.mapRow(rs, 0);
assertThat(actual.getId(), is(500));
assertThat(actual.getCustomer().getId(), is(100));
assertThat(actual.getCustomer().getEmail(), is("TEST"));
}
|
В интерфейсе RowMapper необходимо реализовать метод mapRow(), который принимает в себя текущую строку в JDBC ResultSet и её номер, разбирает эту строку и, основываясь на её данных, собирает и возвращает объект. Готовый маппер используется в query*() методе так же, как раньше использовался конкретный класс:
|
1
2
3
4
5
6
7
|
private static final String ORDER_QUERY = "SELECT O.ID, CUSTOMER_ID, EMAIL FROM ORDERS AS O, CUSTOMERS AS C WHERE C.ID=O.CUSTOMER_ID AND O.ID=?";
@Override
public Order get(Integer id) {
return jdbcTemplate.queryForObject(ORDER_QUERY, new OrderMapper(), id);
}
}
|
|
1
2
3
4
5
6
7
|
@Test
public void testGet() {
Order actual = testedObject.get(100);
assertThat(actual.getId(), is(100));
assertThat(actual.getCustomer().getId(), is(100));
assertThat(actual.getCustomer().getEmail(), is("TEST"));
}
|
Очевидно, что запрос должен возвращать именно те столбцы, которые ожидает получить маппер.
Используя маппер можно получить и коллекцию объектов, так же, как раньше получали коллекции базовых типов:
|
1
2
3
4
|
@Override
public List<Order> all() {
return jdbcTemplate.query(ALL_QUERY, new OrderMapper());
}
|
|
1
2
3
4
|
@Test
public void testAll() {
assertThat(testedObject.all().size(), is(7));
}
|
NamedParameterJDBCTemplate
Параметры не всегда удобно передавать используя лишь порядок. Во-первых легко ошибиться, поставив параметр не на то место, в котором его ожидает запрос, во-вторых, если в запросе один и тот же параметр используется более одного раза, его придётся повторять. Spring JDBC поддерживает именованные параметры во всех базах данных, используя для этого расширенный вариант JdbcTemplate — NamedParameterJdbcTemplate.
Параметры передаются в запрос используя нотацию :имя_параметра
|
1
|
SELECT DESCRIPTION FROM SKUS WHERE ID = :id
|
При исполнении запроса параметры запроса не передаются явно в функцию, а передаются либо в виде Map<String, Object> либо объекта класса SqlParameterSource
|
1
2
3
4
5
6
|
@Override
public String getDescription(Integer id) {
SqlParameterSource namedParameters = new MapSqlParameterSource("id", id);
//Map<String,Object> namedParameters = Collections.singletonMap("id", id);
return jdbcTemplate.queryForObject(DESCRIPTION_QUERY, namedParameters, String.class);
}
|
|
1
2
3
4
|
@Test
public void testGetEmail() {
assertThat(testedObject.getDescription(100), is("TEST"));
}
|
При таком использовании, как показано в примере выше, различий между Map<String, Object> и SqlParametersSource нет. Но, поскольку SqlParametersSource является интерфейсом, существуют более интересные его реализации. Например Sping может сам подставлять значения параметров, выбирая их из Java bean.
|
1
2
3
4
5
6
|
private static final String ITEMS_COUNT = "SELECT SUM(QUANTITY) FROM ORDER_ITEMS WHERE ORDER_ID=:id";
@Override
public Number itemsInOrder(Order o) {
return jdbcTemplate.queryForObject(ITEMS_COUNT, new BeanPropertySqlParameterSource(o), Integer.class);
}
|
|
1
2
3
4
5
|
@Test
public void testItemsCount() {
Order o = orderRepository.get(2);
assertThat(testedObject.itemsInOrder(o), is(6));
}
|
BeanPropertySqlParameterSource анализирует переданный ему объект и для каждого свойства объекта создаёт параметр с именем свойства и его значением.
SqlParametersSource можно реализовывать и самому. Для примера я сделаю реализацию, которая для любого имени параметра возвращает число 3:|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
public class AlwaysThreeParametersSource extends AbstractSqlParameterSource {
@Override
public boolean hasValue(String s) {
return true;
}
@Override
public Object getValue(String s) throws IllegalArgumentException {
return 3;
}
@Override
public int getSqlType(String s) {
return Types.INTEGER;
}
}
|
- hasValue() — который возвращает true, если параметр с таким именем у нас есть.
- getValue() — который возвращает значение параметра с указанным именем.
- getSqlType() — который возвращает SQL тип параметра с указанным именем.
Использовать его как и другие классы параметров:
|
1
2
3
4
|
@Override
public String getThirdSkuDescription() {
return jdbcTemplate.queryForObject(DESCRIPTION_QUERY, new AlwaysThreeParametersSource(), String.class);
}
|
|
1
2
3
4
|
@Test
public void testGetThirdDescription() {
assertThat(testedObject.getThirdSkuDescription(), is("Sample SKU #3"));
}
|
Изменение данных
Данные из базы надо не только запрашивать, но и изменять. Методы update() служат именно для таких запросов и используются аналогично методам query*(): принимают запрос и параметры.
|
1
2
3
4
5
6
|
private static final String CREATE_QUERY = "INSERT INTO CUSTOMERS (EMAIL) VALUES(?)";
@Override
public void add(String email) {
jdbcTemplate.update(CREATE_QUERY, email);
}
|
Параметры для update() могут быть и именованными:
|
1
2
3
4
5
6
|
private static final String ADD_QUERY = "INSERT INTO SKUS(ID, DESCRIPTION) VALUES(:id, :description)";
@Override
public void add(Sku sku) {
jdbcTemplate.update(ADD_QUERY, new BeanPropertySqlParameterSource(sku));
}
|
|
1
2
3
4
5
6
7
8
9
10
|
@Test
public void testCreate() {
Sku expected = new Sku();
expected.setId(500);
expected.setDescription("NEWBIE");
testedObject.add(expected);
assertThat(testedObject.getDescription(500), is("NEWBIE"));
}
|
Prepared statements
Spring JDBC скрывает от конечного пользователя разницу между обычным запросом и prepared запросом. Методы execute() и query*() автоматически создают из запросов PreparedStatement и сами управляют его жизненным циклом.
Но для тех, кто хочет опуститься уровнем ниже и самостоятельно поуправлять созданием PreparedStatement, Spring предоставляет callback интерфейсы для создания PreparedStatement и установки их параметров.
Для задания параметров используется интерфейс PreparedStatementSetter, в метод setValues() которого передаётся PreparedStatement из JDBC и метод должен наполнить его значениями. Экземпляры PreparedStatementSetter можно использовать там, где ожидаются параметры запроса.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
private static final class CustomerSetter implements PreparedStatementSetter {
private Customer customer;
public CustomerSetter(Customer c) {
this.customer = c;
}
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, customer.getEmail());
}
}
|
|
1
2
3
4
|
@Override
public void add(Customer c) {
jdbcTemplate.update(CREATE_QUERY, new CustomerSetter(c));
}
|
Интерфейс PreparedStatementCreator позволяет полностью управлять процессом создания экземпляра PreparedStatement и установкой параметров. Метод createPreparedStatement() должен возвращать готовый к выполнению запрос.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
private final static class OrderCountCreator implements PreparedStatementCreator {
private static final String ORDERS_COUNT = "SELECT COUNT(ID) FROM ORDERS WHERE CUSTOMER_ID=?";
private Customer customer;
public OrderCountCreator(Customer c) {
this.customer = c;
}
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(ORDERS_COUNT);
ps.setInt(1, customer.getId());
return ps;
}
}
|
|
1
2
3
4
5
6
7
8
9
10
|
private final static class OrderCountHandler implements RowCallbackHandler {
@Getter
Integer result=0;
@Override
public void processRow(ResultSet rs) throws SQLException {
result += rs.getInt(1);
}
}
|
|
1
2
3
4
5
6
|
@Override
public Number ordersForCustomer(Customer c) {
OrderCountHandler handler = new OrderCountHandler();
jdbcTemplate.query(new OrderCountCreator(c), handler);
return handler.getResult();
}
|
|
1
2
3
4
5
6
7
|
@Test
public void testOrderCount() {
Customer c = new Customer();
c.setId(2);
Assert.assertThat(testedObject.ordersForCustomer(c), is(3));
}
|
OrderCountHandler показывает использование callback интерфейса RowCallbackHandler для обработки результатов запроса. Его метод processRow() вызывается для каждой строки результата и получает JDBC ResultSet в качестве аргумента.
Код примера доступен на github.