Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как работает ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ, и мы также будем использовать этот тип соединения SQL для создания карточной игры в покер.
Модель таблицы базы данных
Для нашего приложения для карточной игры в покер мы создали таблицы базы данных рангов и мастей :
Таблица ранги определяет ранжирование карт, а также название и символ, используемые для каждого ранга карты:
| name | symbol | rank_value | |-------|--------|------------| | Ace | A | 14 | | King | K | 13 | | Queen | Q | 12 | | Jack | J | 11 | | Ten | 10 | 10 | | Nine | 9 | 9 |
В таблице масти описаны четыре возможные категории, используемые французскими игральными картами:
| name | symbol | |---------|--------| | Club | ♣ | | Diamond | ♦ | | Heart | ♥ | | Spade | ♠ |
Декартово произведение
В теории множеств декартово произведение двух множеств (например, A и B ), обозначаемое обозначением A × B , представляет собой множество всех упорядоченных пар (например, a и b ), где a из A набора и b из B набора. В принципе, декартово произведение представляет все возможные перестановки пар a и b из двух заданных наборов данных.
В нашем случае игральные карты для игры в покер представлены декартовым произведением наших таблиц рангов и мастей базы данных.
ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ SQL
SQL определяет два способа создания декартова произведения:
- SQL:92, синтаксис ПЕРЕКРЕСТНОГО СОЕДИНЕНИЯ
- SQL:89, синтаксис в стиле Тета
SQL:92 ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ
Предпочтительным способом создания декартова произведения является использование синтаксиса ПЕРЕКРЕСТНОГО СОЕДИНЕНИЯ SQL:92.
В нашем случае, чтобы сгенерировать все возможные покерные карты, мы можем использовать следующий запрос на ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ:
SELECT r.symbol AS card_rank, s.symbol AS card_suit FROM ranks r CROSS JOIN suits s
При выполнении приведенного выше SQL-запроса база данных сгенерирует все возможные перестановки рангов и мастей пар, предоставляя нам колоду карт для игры в покер:
| card_rank | card_suit | |-----------|-----------| | A | ♣ | | A | ♦ | | A | ♥ | | A | ♠ | | K | ♣ | | K | ♦ | | K | ♥ | | K | ♠ | | Q | ♣ | | Q | ♦ | | Q | ♥ | | Q | ♠ | | J | ♣ | | J | ♦ | | J | ♥ | | J | ♠ | | 10 | ♣ | | 10 | ♦ | | 10 | ♥ | | 10 | ♠ | | 9 | ♣ | | 9 | ♦ | | 9 | ♥ | | 9 | ♠ |
Соединение в стиле тета
До стандарта SQL:92 объединения могли быть выражены только с помощью синтаксиса в стиле тета, который требует, чтобы предложение FROM перечисляло все таблицы, которые необходимо объединить. Чтобы сгенерировать декартово произведение, предложение WHERE может просто опустить фильтрацию результирующего набора, полученного при выполнении операции предложения FROM.
В нашем случае, чтобы сгенерировать все возможные покерные карты, мы также можем использовать следующий запрос на соединение в стиле тета:
SELECT r.symbol AS card_rank, s.symbol AS card_suit FROM ranks r, suits s
Хотя вы можете использовать соединение в стиле тета для создания декартова произведения, рекомендуется использовать синтаксис ПЕРЕКРЕСТНОГО СОЕДИНЕНИЯ SQL:92.
Игра в покер
Теперь, когда мы знаем, как генерировать колоду карт для игры в покер, нам нужен способ генерировать случайные руки для каждого игрока. Для этого нам нужен способ рандомизации колоды карт.
Как я объяснил в этой статье , в зависимости от базовой системы баз данных, вы можете рандомизировать заданный набор результатов с помощью DBMS_RANDOM.VALUE (например, Oracle), NEWID() (например, SQL Server), random() (например, PostgreSQL), RAND() (например, MySQL).
После рандомизации результирующего набора мы должны извлечь по 5 карт для каждого игрока, поэтому нам нужно использовать предложение Top-N в рандомизированной колоде карт.
Все это можно сделать с помощью следующего JPQL-запроса:
Listcards = entityManager .createQuery( "select new Card(r.symbol, s.symbol) " + "from " + " Rank r, " + " Suit s " + "order by " + " random()", Card.class ) .setMaxResults( playerCount * POKER_HAND_CARD_COUNT ) .getResultList();
В то время как JPQL использует синтаксис в стиле тета, базовый SQL-запрос, сгенерированный Hibernate, будет использовать вместо этого ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ SQL:92. В нашем случае нам просто нужно включить сущности Rank и Suit в предложение from запроса JPQL, и Hibernate будет использовать ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ между связанными таблицами базы данных rank/| и suits .
Поскольку сохраняемость Java не определяет функцию random () , мы можем легко добавить ее , используя атрибут Hibernate MetadataBuilderContributor , следующим образом:
private String randomFunctionName = "random";
@Override
protected void additionalProperties(
Properties properties) {
switch (database()) {
case ORACLE:
randomFunctionName = "DBMS_RANDOM.VALUE";
break;
case SQLSERVER:
randomFunctionName = "NEWID";
break;
case MYSQL:
randomFunctionName = "rand";
break;
}
properties.put(
"hibernate.metadata_builder_contributor",
(MetadataBuilderContributor) metadataBuilder ->
metadataBuilder.applySqlFunction(
"random",
new StandardSQLFunction(randomFunctionName)
)
);
}
Теперь функция random() JPQL по умолчанию будет иметь значение random () , если не используются Oracle, SQL Server или MySQL.
Обратите внимание, что в предыдущем запросе JPQL не было указано полное имя карты , которое мы используем для хранения результирующего набора. Это связано с тем, что мы используем интегратор импорта классов , предоставляемый проектом hibernate-types , как описано в этой статье .
Карта TO предназначена для хранения ранга и масти, сгенерированных ПЕРЕКРЕСТНЫМ СОЕДИНЕНИЕМ таблиц рангов и мастей . Класс Card выглядит следующим образом:
public class Card {
private String rank;
private String suit;
public Card(
String rank,
String suit) {
this.rank = rank;
this.suit = suit;
}
public String getRank() {
return rank;
}
public String getSuit() {
return suit;
}
@Override
public String toString() {
return rank + suit;
}
}
Последнее, что нужно объяснить для нашего запроса на проекцию JPQL, – это использование метода setMaxResults . Значение, которое мы передали методу setMaxResults , представляет размер результирующего набора, который в нашем случае задается путем умножения числа игроков на количество карт в покерной руке (например, пять карт для каждого игрока).
Для получения более подробной информации об ограничении набора результатов SQL-запроса первыми записями Top-N ознакомьтесь с этой статьей .
Время тестирования
С помощью Списка объектов Карт , которые были сгенерированы случайным образом, нам просто нужно назначить покерные руки каждому игроку:
for(int i = 0; i < playerCount; i++) {
int offset = i * POKER_HAND_CARD_COUNT;
LOGGER.info(
"Player {} cards: {}",
i + 1,
cards.subList(
offset,
offset + POKER_HAND_CARD_COUNT
)
);
}
Теперь предположим, что переменная количество игроков имеет значение 4 , давайте посмотрим, как рандомизированный набор результатов, полученный ПЕРЕКРЕСТНЫМ СОЕДИНЕНИЕМ, будет работать в различных системах реляционных баз данных.
Оракул
При запуске этой игры в покер на Oracle выполняется следующий запрос ПЕРЕКРЕСТНОГО СОЕДИНЕНИЯ:
SELECT
r.symbol AS col_0_0_,
s.symbol AS col_1_0_
FROM
ranks r
CROSS JOIN
suits s
ORDER BY
DBMS_RANDOM.VALUE()
FETCH FIRST 20 ROWS ONLY
-- Player 1 cards: [ J♣, A♦, 10♠, 9♥, Q♠]
-- Player 2 cards: [ J♥, J♦, K♦, K♠, A♥]
-- Player 3 cards: [10♥, 9♣, A♣, Q♣, A♠]
-- Player 4 cards: [ Q♥, K♣, Q♦, 10♣, 10♦]
ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ между рангами и мастями генерирует декартово произведение. После этого предложение ORDER BY рандомизирует результирующий набор, а синтаксис SQL:2008 ТОЛЬКО ДЛЯ ВЫБОРКИ ПЕРВЫХ 20 СТРОК ограничение запроса ограничит размер результирующего набора.
SQL Server
При запуске этой игры в покер на SQL Server сгенерированный SQL-запрос почти идентичен запросу, выполняемому в Oracle, единственным исключением является функция рандомизации результирующего набора:
SELECT
r.symbol AS col_0_0_,
s.symbol AS col_1_0_
FROM
ranks r
CROSS JOIN
suits s
ORDER BY
NEWID()
FETCH FIRST 20 ROWS ONLY
-- Player 1 cards: [J♠, Q♦, A♣, A♦, A♥]
-- Player 2 cards: [K♠, Q♠, Q♣, 9♥, A♠]
-- Player 3 cards: [9♣, 10♦, J♥, K♥, 10♥]
-- Player 4 cards: [9♦, Q♥, K♦, J♣, 10♣]
PostgreSQL
При запуске этой игры в покер на PostgreSQL выполняемый SQL – запрос также использует синтаксис ПЕРЕКРЕСТНОГО СОЕДИНЕНИЯ. На этот раз результирующий набор ограничен с помощью предложения LIMIT, хотя ИЗВЛЕКАЕТ ТОЛЬКО ПЕРВЫЕ 20 СТРОК также будет отлично работать в PostgreSQL:
SELECT
r.symbol AS col_0_0_,
s.symbol AS col_1_0_
FROM
ranks r
CROSS JOIN
suits s
ORDER BY
random()
LIMIT 20
-- Player 1 cards: [K♥, K♦, Q♠, 9♥, A♥]
-- Player 2 cards: [9♣, A♦, J♦, K♣, A♣]
-- Player 3 cards: [J♣, A♠, Q♦, 9♠, Q♥]
-- Player 4 cards: [K♠, J♥, 10♦, 10♣, Q♣]
MySQL
При запуске этой игры в покер на MySQL выполняемый SQL-запрос также использует синтаксис ПЕРЕКРЕСТНОГО СОЕДИНЕНИЯ, а также предложение LIMIT, поскольку MySQL еще не поддерживает синтаксис FETCH ТОЛЬКО ДЛЯ ПЕРВЫХ 20 СТРОК SQL:2008:
SELECT
r.symbol AS col_0_0_,
s.symbol AS col_1_0_
FROM
ranks r
CROSS JOIN
suits s
ORDER BY
rand()
LIMIT 20
-- Player 1 cards: [ J♣, K♦, A♣, K♣, Q♣]
-- Player 2 cards: [10♣, Q♠, K♠, Q♦, J♥]
-- Player 3 cards: [ J♦, 9♦, A♠, 10♦, A♦]
-- Player 4 cards: [10♥, 9♥, K♥, 10♠, 9♣]
Вывод
ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ SQL позволяет создавать декартово произведение для двух заданных наборов данных. Когда базовый вариант использования требует создания декартова произведения, как это было в случае с нашей игрой в покер, то использование ПЕРЕКРЕСТНОГО СОЕДИНЕНИЯ является идиоматическим способом решения этой задачи.
Обратите внимание, что декартово произведение также может быть создано непреднамеренно, и в этом случае оно будет указывать на недостаток в условиях соединения. Например, это может произойти при соединении двух или более несвязанных отношений таблицы “один ко многим”. Для получения более подробной информации об этих непреднамеренных проблемах с декартовым продуктом и о том, как их можно устранить, ознакомьтесь с этой статьей .