A Java application that stores users in a relational database needs to define tables, change rows, and retrieve the right records. SQL is the language for those operations. Even when a persistence framework handles routine data access, SQL knowledge remains important for understanding the database and tuning performance. section 1.1.2 section 1.1.3
SQL has several roles:
- Data definition language (DDL) creates, alters, and drops tables and constraints. Changing an existing schema as requirements evolve is schema evolution. section 1.1.2
- Data manipulation language (DML) inserts, updates, and deletes data. section 1.1.2
- Data query language (DQL) retrieves data; queries can restrict, project, join, aggregate, group, or use subselects. section 1.1.2
- Data control language (DCL) grants or revokes database access. section 1.1.2
In Java, JDBC sends SQL to the database and handles parameter binding, execution, and result retrieval. Writing those steps directly gives control but adds low-level service code. Hibernate and Spring Data can reduce repetitive work; they do not remove the need to understand relational data or the SQL behind application performance. section 1.1.3 section 1.1.2
Listing 15.17 Methods annotated with @Query in the UserRepository interface
Path: Ch15/spring-data-jdbc2/src/main/java/com/manning/javapersistence/ch15 /repositories/UserRepository.java @Query("SELECT COUNT(*) FROM USERS WHERE ACTIVE = :ACTIVE") #1 int findNumberOfUsersByActivity(@Param("ACTIVE") boolean active); #1 @Query("SELECT * FROM USERS WHERE LEVEL = :LEVEL AND ACTIVE = :ACTIVE") #2 List<User> findByLevelAndActive(@Param("LEVEL") int level, @Param("ACTIVE") #2 boolean active); #2 #1 The <kbd>findNumberOfUsersByActivity</kbd> method will return the number of active users. #2 The <kbd>findByLevelAndActive</kbd> method will return users with the <kbd>level</kbd> and <kbd>active</kbd> status given as named parameters. The <kbd>@Param</kbd> annotation will match the <kbd>:LEVEL</kbd> parameter of the query with the <kbd>level</kbd> argument of the method and the <kbd>:ACTIVE</kbd> parameter of the query with the <kbd>active</kbd> argument of the method.
These Spring Data JDBC repository methods put SQL in @Query annotations. Named placeholders such as :ACTIVE are matched by @Param; one query counts users, while the other selects users by level and active status. section 15.2.4
Spring Data JDBC accepts plain SQL rather than JPQL for custom queries. That makes a database change consequential: vendor-specific queries may need rewriting. Choose this approach when its simpler repository model fits, while accounting for the SQL you must maintain. section 15.0 section 15.2.4
