Databases: SQL, NoSQL and Java developer tools

# Databases in Modern Development: SQL, NoSQL, and Tools for Java

Programming is about managing data flows. At any stage of application development, storing and processing information plays a key role. Databases act as the central hub where data isn't just stored, but structured for efficient access. In this article, we'll break down the fundamental principles of working with DBMSs, compare relational and non-relational systems, and explore modern tools for integrating with Java applications.

Relational and Non-Relational Databases: When to Use What

Relational DBMSs (PostgreSQL, MySQL) provide a strict data schema and ACID support (atomicity, consistency, isolation, durability). This makes them ideal for financial systems, where every transaction must be reliably recorded. For example, when transferring money between accounts, it's guaranteed that both changes (debit and credit) will execute or neither will.

Non-relational databases (MongoDB, Redis) offer an on-write schema, simplifying adaptation to changing requirements. Document DBs are effective for storing hierarchical data (e.g., user profiles with dynamic fields), while key-value stores (Redis) are indispensable for caching and session handling.

Criteria for choice:

• Need complex queries with JOIN? → SQL
• Expect horizontal scaling? → NoSQL
• Require strict integrity? → SQL
• High write speed? → NoSQL

SQL: Query Language and Its Implementation in DBMSs

SQL is a declarative language divided into sublanguages:

• DDL (Data Definition Language): CREATE TABLE, ALTER INDEX
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE
• DCL (Data Control Language): GRANT, REVOKE

A DBMS processes a query through several stages:

• Parsing and validation
• Optimization (building the execution plan)
• Execution
• Returning the result

Optimization example: for a query with WHERE user_id = 100, the DBMS uses the index on user_id, avoiding a full table scan.

Evolution of Java Tools for Working with DBs

JDBC: Low-Level Control

JDBC provides direct access to the DB via drivers. Advantages include minimal overhead and full control over queries. Disadvantages include the need for manual resource management and boilerplate code.

JPA and Hibernate: Object-Relational Mapping

JPA abstracts DB work through annotations (@Entity, @Id). Hibernate implements JPA, adding:

• First-level caching (at the session level)
• Lazy loading of related entities
• Automatic DDL generation

However, improper use of lazy loading leads to the N+1 problem.

Spring Data JPA: Productivity Through Conventions

Spring Data JPA extends JPA, providing:

• Ready-made repository methods (CRUD operations)
• Support for Pageable and Sort for pagination
• Derived queries via method names

Example of a custom query:

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByEmailEndingWithAndActiveTrue(String domain);
}

This method is automatically converted to SQL with the condition WHERE email LIKE '%@domain' AND active = true.

Data Access Optimization

Indexes: Structures for Speeding Up Searches

• B-tree: standard index for most DBMSs. Effective for range queries (BETWEEN, >, <). Requires O(log N) operations.
• Hash: used in Redis and MySQL MEMORY tables. Ideal for exact matches (=), but doesn't support ranges. Complexity O(1).
• Inverted index: used in search engines (Elasticsearch). Allows searching by words in text.

In-Memory Stores: Redis in Action

Redis stores data in RAM, providing microsecond latency. Typical scenarios:

• Caching results of heavy queries
• Storing user sessions
• Implementing queues (via lists)

For data persistence, Redis uses RDB (snapshots) and AOF (operation logs).

Key Takeaways: What's Important

• Data Model Fit: relational DBs are irreplaceable for transactional systems, NoSQL for scalable scenarios with unstructured data.
• Balance of Abstraction and Control: using Spring Data JPA speeds up development, but requires understanding generated queries for profiling.
• Performance is a Comprehensive Task: combining proper indexes, in-memory caching, and query optimization reduces latency by 100-1000 times.

— Editorial Team