Запросы к базе данных делаются с помощью Spring JDBC гораздо проще.
Поскольку Spring сам может выполнять SQL скрипты при запуске приложения, для этой статьи я подготовил достаточно развесистую схему данных, чтобы примеры выглядели интереснее.
Удел Java обычно enterprise приложения, поэтому пример самый что ни на есть энтерпрайзный. Предположим, что мы пишем систему отслеживания заказов, в которой есть клиенты, товары и, собственно, заказы. Заказ принадлежит клиенту и связан со списком входящих в него товаров. Итого четыре таблицы.
Я подготовил скрипт, который создаёт таблицы и наполняет их данными.
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 CUSTOMERS(ID, EMAIL) VALUES (1, 'test@example.org'); INSERT INTO CUSTOMERS(ID, EMAIL) VALUES (2, 'example@example.org'); 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()
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);
}
@Test
public void testGetEmail() {
assertThat(testedObject.getEmail(100), is("TEST"));
}
Достаточно лишь передать тип, к которому надо привести результат, и указать параметры запроса.
Если столбец всё ещё один, а строк много, можно запросить список объектов подходящего класса:
private static final String ALL_QUERY = "SELECT EMAIL FROM CUSTOMERS";
@Override
public List<String> allEmails() {
return jdbcTemplate.queryForList(ALL_QUERY, String.class);
}
И в этом случае достаточно передать тип результата и указать параметры запроса, если они требуются.
Если же запрос возвращает обычную таблицу, в которой много строк и много столбцов, то можно попросить вернуть список key-value значений по строкам, как я писал раньше:
@Override
public List<Map<String, Object>> all() {
return jdbcTemplate.queryForList(ALL_QUERY);
}
@Test
public void testAll() {
assertTrue(testedObject.all().stream().filter(m -> "TEST".equals(m.get("EMAIL"))).findFirst().isPresent());
}
Object Mapping
Второй вариант — отображение строки на объект. Например, заказ состоит из номера заказа и заказчика, который, в свою очередь, состоит из номера заказчика и e-mail. Мы можем получить из метода query*() сразу готовый объект заказа, реализовав отображение sql данных в объекты.
Для начала создадим классы заказчика и заказа:
@Data
public class Customer {
private Integer id;
private String email;
}
@Data
public class Order {
private Integer id;
private Customer customer;
}
Я использовал project lombok, чтобы сделать их покороче.
Теперь, когда у нас есть готовые классы, напишем класс для отображения данных из SQL. Отображающий класс реализует интерфейс RowMapper из Spring JDBC:
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;
}
}
@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*() методе так же, как раньше использовался конкретный класс:
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);
}
}
@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"));
}
Очевидно, что запрос должен возвращать именно те столбцы, которые ожидает получить маппер.
Используя маппер можно получить и коллекцию объектов, так же, как раньше получали коллекции базовых типов:
@Override
public List<Order> all() {
return jdbcTemplate.query(ALL_QUERY, new OrderMapper());
}
@Test
public void testAll() {
assertThat(testedObject.all().size(), is(7));
}
NamedParameterJDBCTemplate
Параметры не всегда удобно передавать используя лишь порядок. Во-первых легко ошибиться, поставив параметр не на то место, в котором его ожидает запрос, во-вторых, если в запросе один и тот же параметр используется более одного раза, его придётся повторять. Spring JDBC поддерживает именованные параметры во всех базах данных, используя для этого расширенный вариант JdbcTemplate — NamedParameterJdbcTemplate.
Параметры передаются в запрос используя нотацию :имя_параметра
SELECT DESCRIPTION FROM SKUS WHERE ID = :id
При исполнении запроса параметры запроса не передаются явно в функцию, а передаются либо в виде Map<String, Object> либо объекта класса SqlParameterSource
@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);
}
@Test
public void testGetEmail() {
assertThat(testedObject.getDescription(100), is("TEST"));
}
При таком использовании, как показано в примере выше, различий между Map<String, Object> и SqlParametersSource нет. Но, поскольку SqlParametersSource является интерфейсом, существуют более интересные его реализации. Например Sping может сам подставлять значения параметров, выбирая их из Java bean.
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);
}
@Test
public void testItemsCount() {
Order o = orderRepository.get(2);
assertThat(testedObject.itemsInOrder(o), is(6));
}
BeanPropertySqlParameterSource анализирует переданный ему объект и для каждого свойства объекта создаёт параметр с именем свойства и его значением.
SqlParametersSource можно реализовывать и самому. Для примера я сделаю реализацию, которая для любого имени параметра возвращает число 3:
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;
}
}
Для реализации я использовал абстрактный класс AbstractSqlParameterSource, который реализует SqlParametersSource и пару вспомогательных методов. Для реализации собственного SqlParametersSource необходимо написать три метода:
- hasValue() — который возвращает true, если параметр с таким именем у нас есть.
- getValue() — который возвращает значение параметра с указанным именем.
- getSqlType() — который возвращает SQL тип параметра с указанным именем.
Использовать его как и другие классы параметров:
@Override
public String getThirdSkuDescription() {
return jdbcTemplate.queryForObject(DESCRIPTION_QUERY, new AlwaysThreeParametersSource(), String.class);
}
@Test
public void testGetThirdDescription() {
assertThat(testedObject.getThirdSkuDescription(), is("Sample SKU #3"));
}
Изменение данных
Данные из базы надо не только запрашивать, но и изменять. Методы update() служат именно для таких запросов и используются аналогично методам query*(): принимают запрос и параметры.
private static final String CREATE_QUERY = "INSERT INTO CUSTOMERS (EMAIL) VALUES(?)";
@Override
public void add(String email) {
jdbcTemplate.update(CREATE_QUERY, email);
}
Параметры для update() могут быть и именованными:
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));
}
@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 можно использовать там, где ожидаются параметры запроса.
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());
}
}
@Override
public void add(Customer c) {
jdbcTemplate.update(CREATE_QUERY, new CustomerSetter(c));
}
Интерфейс PreparedStatementCreator позволяет полностью управлять процессом создания экземпляра PreparedStatement и установкой параметров. Метод createPreparedStatement() должен возвращать готовый к выполнению запрос.
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;
}
}
private final static class OrderCountHandler implements RowCallbackHandler {
@Getter
Integer result=0;
@Override
public void processRow(ResultSet rs) throws SQLException {
result += rs.getInt(1);
}
}
@Override
public Number ordersForCustomer(Customer c) {
OrderCountHandler handler = new OrderCountHandler();
jdbcTemplate.query(new OrderCountCreator(c), handler);
return handler.getResult();
}
@Test
public void testOrderCount() {
Customer c = new Customer();
c.setId(2);
Assert.assertThat(testedObject.ordersForCustomer(c), is(3));
}
OrderCountHandler показывает использование callback интерфейса RowCallbackHandler для обработки результатов запроса. Его метод processRow() вызывается для каждой строки результата и получает JDBC ResultSet в качестве аргумента.