Статьи с тэгом «PostgreSQL»

CrudRepository

1 мая 2019

Тэги: Spring Boot Spring Data SQL PostgreSQL Kotlin rest

Ранее я уже писал статью CrudRepository в Spring Data, в которой рассматривался пример rest-сервиса, работающего с базой данных. Теперь хочу показать аналогичный пример, но вместо Java написать его на Kotlin, который стремительно набирает популярность. Rest-сервис состоит из трёх слоёв: слой работы с БД, сервисный слой и контроллер. Мы пойдём последовательно по слоям, начиная с нижнего.

В качестве примера возьмём сервис, работающий с музыкальными группами. У группы есть три основных параметра: название, количество участников и дата основания. Структура таблицы в postgres может выглядеть следующим образом:

create table band
(
  id serial,
  name character varying(50) not null,
  players_count integer not null,
  created date not null,
  constraint band_pk primary key (id)
);

Тип данных serial означает поле, значение которого автоматически увеличивается на 1 с каждой новой записью.

Заготовку проекта удобно сгенерить через start.spring.io. Там достаточно выбрать тип проекта - maven project, язык - kotlin. В качестве dependency добавить Web и JPA. Затем нажимаем Generate Project и вы уже скачали архив с заготовкой вашего проекта. Помимо указанных dependency для kotlin будут добавлены ещё несколько служебных, а также maven-плагины для его компиляции.

В секцию dependencies нам нужно добавить драйвер postgres, т.к. мы планируем работать именно с этой базой:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.5</version>
</dependency>

Итоговый pom-файл проекта вы можете посмотреть на github.

Читать полностью...

CrudRepository в Spring Data

27 мая 2018

Тэги: Java 8 PostgreSQL Spring Boot rest Spring Hibernate Spring Data

В статье Hibernate и Spring Boot мы рассматривали использование Hibernate для того, чтобы не писать sql-запросы в слое доступа к данным. Сегодня мы пойдём ещё дальше и рассмотрим, как Spring Data может генерировать за вас сам слой доступа к данным со всеми методами, которые вам нужны в сервисном слое.

В качестве примера возьмём сущность «Страна» с её названием в качестве единственного параметра и на примере этой сущности шаг за шагом создадим все необходимые операции для поиска, добавления, редактирования и удаления этой сущности. В СУБД postgres надо создать следующую таблицу:

CREATE TABLE country
(
  id serial,
  name character varying(50) NOT NULL,
  CONSTRAINT country_id_pk PRIMARY KEY (id)
);

Теперь создадим типовой maven-проект и добавим в pom.xml необходимые зависимости. Полную версию файла можно посмотреть на github.

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.12.RELEASE</version>
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.2.jre7</version>
    </dependency>
    <dependency>
        <groupId>javax.xml.bind</groupId>
        <artifactId>jaxb-api</artifactId>
        <version>2.3.0</version>
    </dependency>
</dependencies>

spring-boot-starter-web отвечает за обработку http-запросов, а spring-boot-starter-data-jpa предоставляет функционал доступа к данным. Также мы добавляем драйвер для работы с целевой СУБД.

Читать полностью...

Hibernate и Spring Boot

20 мая 2018

Тэги: Spring Boot maven PostgreSQL rest Spring Java Hibernate

Ранее мы уже рассматривали, как работать с базой данных через jdbc в статье Работа с БД в Spring Boot на примере postgresql. А сегодня возьмём Hibernate - самый популярный фреймворк для работы с БД - и убедимся, что он значительно облегчает реализацию типовых операций над сущностями.

Предположим, в БД у нас есть две сущности: страна и город. В одной стране может быть несколько городов (отношение «один-ко-многим»). Структура таблиц выглядит примерно так:

CREATE SEQUENCE country_id_seq;

CREATE TABLE country
(
  id integer NOT NULL DEFAULT nextval('country_id_seq'::regclass),
  name character varying(50) NOT NULL,
  CONSTRAINT country_id_pk PRIMARY KEY (id)
);

CREATE SEQUENCE city_id_seq;

CREATE TABLE city
(
  id integer NOT NULL DEFAULT nextval('city_id_seq'::regclass),
  name character varying(50) NOT NULL,
  country_id integer NOT NULL
);

И мы хотим совершать типовые действия над этими сущностями: просмотр всего списка, поиск по id, добавление, обновление и удаление записей. Для этого создадим типовой Spring Boot проект. В pom-файле нужно прописать следующий parent:

Читать полностью...

Работа с датой в Spring Boot

12 мая 2018

Тэги: Spring Boot Java PostgreSQL

В предыдущих статьях мы уже создавали rest-приложение (Spring Boot Restful Service, Работа с БД в Spring Boot на примере postgresql). А теперь давайте рассмотрим, как работать с датой и временем в Spring Boot на уровне rest-запросов и на уровне БД.

Предположим, перед нами стоит задача фиксировать в специальной таблице все действия пользователя (регистрация, вход, выход и т.п.) Таблица для СУБД Postgres в самом простом случае будет выглядеть так:

CREATE TABLE user_action
(
   id serial NOT NULL,
   action_date timestamp without time zone NOT NULL,
   user_id integer NOT NULL,
   action_type integer NOT NULL,
   CONSTRAINT user_action_pk PRIMARY KEY (id)
)

Тип serial представляет собой поле, которое автоматически увеличивается на единицу для каждой новой записи, поэтому его удобно использовать в качестве первичного ключа для записи.

Тип timestamp without time zone позволяет хранить метку времени без привязки к часовому поясу.

user_id и action_type представляют собой числовые id пользователя и тип действия соответственно. В реальном приложении каждое из них должно быть внешним ключом на соответствующие таблицы, но в нашем примере для простоты такой привязки нет.

Читать полностью...

Настройка postgres для доступа по сети

6 мая 2018

Тэги: PostgreSQL

Для того, чтобы открыть доступ по локальной сети с других машин к БД, которая развёрнута на данной, нужно отредактировать два файла: postgresql.conf и pg_hba. Привожу пример для своей операционной системы, основанной на Linux (Ubuntu) и postgresql 9.5.

В файле /etc/postgresql/9.5/main/postgresql.conf находим строку

listen_addresses = '*'

и раскомментируем её (убираем решётку в начале строки) или добавляем, если такой строки в этом файле нет.

Данный параметр говорит о том, чтобы обрабатывать все запросы, приходящие извне. В противном случае будут обрабатываться только локальные запросы.

Затем в файле /etc/postgresql/9.5/main/pg_hba.conf с правами администратора нужно указать, какие хосты имеют право подключаться к указанной БД и каким образом обеспечивается безопасность подключения.

Читать полностью...

Рекурсивный запрос на postgres

5 мая 2018

Тэги: PostgreSQL

Рассмотрим составление рекурсивных запросов на PostgreSQL для иерархических данных на примере следующей таблицы:

create table hierarchy_example (
    id serial not null,
    name character varying(100),
    parent_id integer,
    constraint id_pk primary key (id)
)

Здесь поле parent_id содержит номер записи, которая является родительской по отношению к данной. Если parent_id = null, считаем, что это - корневой элемент иерархии.

Заполним таблицу данными:

insert into hierarchy_example (name, parent_id) values ('root', null);
insert into hierarchy_example (name, parent_id) values ('item1', 1);
insert into hierarchy_example (name, parent_id) values ('item2', 1);
insert into hierarchy_example (name, parent_id) values ('subitem1', 2);

Теперь составим запрос для прохода по этой иерархии, от элемента с именем subitem1 до root. На каждой итерации будем добавлять новую строку во временную таблицу temp1.

Читать полностью...

Как сделать полный бэкап базы на postgres

30 апреля 2018

Тэги: PostgreSQL

Для создания полного бэкапа базы на postgres воспользуемся утилитой pg_dump. Бэкап представляет собой текстовый файл с sql-синтаксисом. При этом данные вставляются в более компактном виде.

Перейдём в целевой каталог, в котором планируется сохранить файл бэкапа. Затем выполняем команду:

pg_dump имя_базы -h хост -p порт -U пользователь > имя_файла_бэкапа.sql

Разумеется, подключиться можно как к локальной базе, так и к базе, расположенной на сервере. После того, как файл создался, можем приступить к созданию копии.

Для начала создайте базу (её имя может быть любым), а также пользователя, имя которого должно совпадать с именем пользователя, который работает с исходной базой. Скорее всего, это имя, которое вы использовали для параметра -U в команде, указанной выше. Но точнее лучше посмотреть в полученном файле бэкапа. В скрипте создания таблиц можно увидеть строчку вида:

ALTER TABLE имя_базы OWNER TO имя_пользователя;

Читать полностью...

RowMapper и ResultSetExtractor в Spring Boot

27 апреля 2018

Тэги: Spring Boot rest SQL PostgreSQL Java 8

Spring Boot предоставляет два интерфейса для обработки выборки из БД: RowMapper и ResultSetExtractor. Давайте разберём их назначение, а также выясним, чем они различаются на примере справочника городов и стран.

RowMapper

Чаще всего при работе со списками в restful-сервисах, построенных на Spring Boot, вы будете использовать RowMapper. Этот класс обрабатывает отдельно каждую запись, полученную из БД, и возвращает уже готовый объект - модель данных. В большинстве случаев его вполне хватает.

Создадим простенький rest-контроллер, который будет возвращать список всех стран, которые заведены у нас в БД. Определение таблицы в СУБД postgres выглядит следующим образом:

CREATE TABLE public.country
(
  id serial,
  name character varying(50) NOT NULL,
  CONSTRAINT country_pk PRIMARY KEY (id)
)

Здесь тип serial представляет собой обычный integer, который автоматически увеличивается на 1 при добавлении каждой новой записи. То есть нет нужды при вставке явно указывать id.

Добавим туда несколько стран для примера:

insert into country (name) values ('Германия'); -- id = 1
insert into country (name) values ('Франция'); -- id = 2
insert into country (name) values ('Италия'); -- id = 3

Читать полностью...

Работа с БД в Spring Boot на примере postgresql

6 января 2018

Тэги: Spring Boot rest SQL PostgreSQL Java 8

Данная статья является продолжением Spring Boot Restful Service, где была бы раскрыта тема работы с БД в Spring Boot. Давайте рассмотрим эту тему подробнее на примере СУБД postgresql, а в качестве основы возьмём проект, который мы делали в той статье.

Напомню, что проект представляет из себя простой restful-service, который принимает GET-запрос по HTTP и возвращает профиль пользователя по его id. Сам профиль содержит кроме id также имя, фамилию и возраст. Поэтому создадим таблицу profiles в базе данных.

CREATE TABLE public.profiles
(
  id serial,
  first_name character varying(50) NOT NULL,
  last_name character varying(50) NOT NULL,
  age integer NOT NULL,
  CONSTRAINT profile_id_pk PRIMARY KEY (id)
);

insert into profiles (first_name, last_name, age) values ('Иван', 'Петров', 23);

Для поля id можно использовать тип serial. Он представляет собой целое число, которое инкрементируется (увеличивается на 1) автоматически при вставке новой записи в таблицу.

Читать полностью...

Произведение всех чисел в столбце таблицы одним SQL запросом

3 января 2018

Тэги: SQL PostgreSQL головоломки

Я думаю, каждый знает такую функцию SQL, как SUM(). Она позволяет посчитать сумму всех значений по определённому столбцу. Но что, если нам вдруг понадобится посчитать не сумму, а произведение всех значений?

Как ни странно, функция SUM() нам тоже пригодится. Как нам от произведения перейти к сумме? Вспомним начала матанализа: логарифм произведения равен сумме логарифмов. Будем использовать натуральный логарифм LN() в паре с функцией возведения экспоненты в степень EXP()

Привожу пример для postgresql, но должно работать и в других СУБД:

select exp(sum(ln(field))) from tab

Читать полностью...

Далее ❯

Облако тэгов

Kotlin, Java, Java 11, Java 10, Java 9, Java 8, Spring, Spring Boot, Spring Data, SQL, PostgreSQL, Oracle, Hibernate, Collections, Stream API, многопоточность, Apache, maven, gradle, JUnit, ООП, алгоритмы, головоломки, rest