Last modified:
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
While doing my High-Performance Java Persistence training, I realized that many Java developers lag behind when it comes to SQL skills.
For way too many application developers, SQL equates that SQL-92 they learned in college. However, SQL has seen many additions over the past 25 years.
Even if the NoSQL movement stormed onto the development scene 10 years ago, it never got to overthrow relational database systems.
In fact, the latest trend is called NewSQL, and it embraces both SQL and ACID guarantees.
In these tutorials, we are going to see what present-day SQL looks like and learn new SQL tricks that even the DBAs will be impressed with.
Basic concepts
- SQL Operation Order
- How does a relational database execute SQL statements and prepared statements
- Single-Primary Database Replication
- Time to break free from the SQL-92 mindset
Database modeling
- A beginner’s guide to database table relationships
- A beginner’s guide to natural and surrogate database keys
- The best UUID type for a database Primary Key
- MySQL 8 support for custom SQL CHECK constraints simplifies SINGLE_TABLE inheritance data integrity validation rules
- MariaDB 10.3 supports database sequences
- The best way to use one-to-one table relationships
JOIN
Join Algorithms
Subqueries
- SQL EXISTS and NOT EXISTS
- SQL ANY Operator
- How to query parent rows when all children must match the filtering criteria using SQL
Derived Tables and CTE
- SQL Derived Table or Inline View
- SQL CTE – Common Table Expression
- SQL Recursive WITH CTE (Common Table Expression) queries
ORDER BY
Window Functions
- Why you should definitely learn SQL Window Functions
- How to calculate percentiles with SQL PERCENTILE_CONT
Upsert
Pivot
Execution Plans
- How to get the SQL execution plan on Oracle
- How to get the query execution plan on SQL Server
- How to get the actual execution plan for an Oracle SQL query using Hibernate query hints
- PostgreSQL Auto Explain
Indexing
- Default Database Primary, Foreign, and Unique Key Indexing
- Index Selectivity
- PostgreSQL Index Types
- PostgreSQL Heap-Only-Tuple or HOT Update Optimization
- Clustered Index
- Why you should disable the SQL Server JDBC sendStringParametersAsUnicode property
Batching
- How to enable multi-row inserts with the PostgreSQL
reWriteBatchedInsertsconfiguration property - How to enable multi-row inserts with the MySQL
rewriteBatchedStatementsconfiguration property - How to enable multi-row inserts with SQL Server
useBulkCopyForBatchInsertconfiguration property
Fetching
- Pagination best practices
- How to limit the SQL query result set to Top-N rows
- SQL Seek Method or Keyset Pagination
- How to fetch multiple to-many relationships with jOOQ MULTISET
JSON
- MySQL JSON_TABLE – Map a JSON object to a relational database table
- SQL Server OPENJSON – Map JSON to a relational database table
- How to get a JSON property value using PostgreSQL
- How to index JSON columns using MySQL
Security
Transactions and Concurrency control
- Maximum number of database connections
- The race condition that led to Flexcoin bankruptcy
- A beginner’s guide to ACID and database transactions
- Optimistic vs. Pessimistic Locking
- A beginner’s guide to database deadlock
- How does the 2PL (Two-Phase Locking) algorithm work
- How does MVCC (Multi-Version Concurrency Control) work
- How to get the current database transaction id
- How do PostgreSQL advisory locks work
- How to implement a database job queue using SKIP LOCKED
- The best way to use SQL NOWAIT
- PostgreSQL FOR UPDATE vs FOR NO KEY UPDATE
- A beginner’s guide to database locking and the lost update phenomena
- How to prevent lost updates in long conversations
- A beginner’s guide to Dirty Read anomaly
- A beginner’s guide to Non-Repeatable Read anomaly
- A beginner’s guide to Phantom Read anomaly
- A beginner’s guide to Read and Write Skew phenomena
- A beginner’s guide to Serializability
- A beginner’s guide to the Write Skew anomaly, and how it differs between 2PL and MVCC
- How does database pessimistic locking interact with INSERT, UPDATE, and DELETE SQL statements
- How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL, and MySQL
- Logical vs physical clock optimistic locking
- PostgreSQL trigger consistency check
- PostgreSQL triggers and isolation levels
- SQL Server deadlock trace flags
- SQL Server Foreign Key Locking
Stored procedures
- The best way to call a stored procedure with JPA and Hibernate
- The best way to call SQL Server stored procedures with jOOQ
- Cache synchronization using jOOQ and PostgreSQL functions
- How to call Oracle stored procedures and functions with JPA and Hibernate
- How to call SQL Server stored procedures and functions with JPA and Hibernate
- How to call PostgreSQL functions (stored procedures) with JPA and Hibernate
- How to call MySQL stored procedures and functions with JPA and Hibernate
Audit logging
- A beginner’s guide to CDC (Change Data Capture)
- PostgreSQL audit logging using triggers
- MySQL audit logging using triggers
- SQL Server audit logging using triggers
- How to extract change data events from MySQL to Kafka using Debezium
Monitoring, Tuning, and Profiling
- How to run monitor a PostgreSQL or MySQL database using Percona PMM
- MySQL Query Profiling Using Performance Schema
- PostgreSQL Performance Tuning Settings
Statement Caching
- How does a relational database execute SQL statements and prepared statements
- PostgreSQL JDBC Statement Caching
- MySQL JDBC Statement Caching
Logging
- How to format SQL using the command line
- How to log the database transaction id using MDC
- The best way to log SQL statements with Spring Boot
Distributed SQL
- YugabyteDB Architecture
- A beginner’s guide to YugabyteDB
- Strong Consistency with YugabyteDB
- YugabyteDB column-level locking
- Building a Distributed Audit Log with YugabyteDB
- YugabyteDB Connection Pooling
- Scaling a Spring application with a YugabyteDB cluster
- Fault Tolerance with Spring Data and YugabyteDB
Testing
- The best way to test the data access layer
- Testcontainers Database Integration Testing
- How to run database integration tests 20 times faster
- How to run integration tests at warp-speed using Docker and tmpfs
Importing and Exporting data
