Now, assuming the playerCount variable has the value of 4, let’s see how the randomized result set produced by CROSS JOIN will work on various relational database systems. With the List of Card objects that were generated randomly, we just have to assign the poker hands to each player: The value we passed to the setMaxResults method represents the size of the result set, which in our case is given by multiplying the number of players with the number of cards in a poker hand (e.g., five cards for each player).įor more details about limiting a SQL query result set to the first Top-N records, check out this article. The last thing to explain for our JPQL projection query is the use of setMaxResults method. The Card DTO is designed to hold the rank and suit generated by the CROSS JOIN of the ranks and suits tables. This is because we are using the ClassImportIntegrator provided by the hibernate-types project, as explained in this article. Notice that the previous JPQL query did not specify the fully-qualified name of the Card DTO we are using to store the result set. Now, the random() JPQL function will default to random() unless Oracle, SQL Server, or MySQL are being used. New StandardSQLFunction(randomFunctionName) (MetadataBuilderContributor) metadataBuilder -> RandomFunctionName = "DBMS_RANDOM.VALUE" Private String randomFunctionName = void additionalProperties( In our case, we just have to include both Rank and Suit entities in the from clause of the JPQL query, and Hibernate will use a CROSS JOIN between the associated ranks and suits database tables.īecause Java Persistence does not define a random() function, we can easily add that using the Hibernate MetadataBuilderContributor, as follows: While the JPQL uses the theta-style syntax, the underlying SQL query generated by Hibernate is going to use the SQL:92 CROSS JOIN instead. To do that, we need a way to randomize the deck of cards.Īs I explained in this article, depending on the underlying database system, you can randomize a given result set using either DBMS_RANDOM.VALUE (e.g., Oracle), NEWID() (e.g., SQL Server), random() (e.g., PostgreSQL), RAND() (e.g., MySQL).Īfter we randomize the result set, we must extract 5 cards for each player, so we need to use a Top-N clause on the randomized deck of cards.Īll this can be done using the following JPQL query: Now that we know how to generate the poker game deck of cards, we need a way to generate random hands for each player. In our case, to generate all possible poker cards, we can also use the following theta-style join query:Īlthough you can use the theta-style join to generate a Cartesian product, it’s recommended to use the SQL:92 CROSS JOIN syntax. To generate a Cartesian product, the WHERE clause can simply omit filtering the result set produced by executing the FROM clause operation. Prior to the SQL:92 standard, joins could only be expressed via the theta-style syntax, which requires the FROM clause to list all tables that need to be joined. When executing the SQL query above, the database will generate all possible permutations of ranks and suits pairs, giving us the poker game deck of cards: In our case, to generate all possible poker cards, we can use the following CROSS JOIN query: The preferred way to generate a Cartesian product is to use the SQL:92 CROSS JOIN syntax. SQL defines two ways of generating a Cartesian product: In our case, the poker game playing cards are represented by the Cartesian product of our ranks and suits database tables. Basically, the Cartesian product represents all possible permutations of a and b pairs from two given sets of data. In the set theory, the Cartesian product of two sets (e.g., A and B), denoted by the A × B notation, is the set of all ordered pairs (e.g., a and b) where a is from the A set and b is from the B set. The suits table describes the four possible categories used by the French playing cards: The ranks table defines the ranking of cards, as well as the name and symbol used for each card rank: In this article, we are going to see how a CROSS JOIN works, and we will also make use of this SQL join type to build a poker card game.įor our poker card game application, we have created the ranks and suits database tables: So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night! Well, Hypersistence Optimizer is that tool!Īnd it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework. Follow having a tool that can automatically detect JPA and Hibernate performance issues.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |