0
# QueryDSL SQL
1

2
QueryDSL SQL provides a fluent, type-safe API for building and executing SQL queries in Java. It eliminates SQL string concatenation by offering a programmatic query construction interface that supports complex queries, joins, subqueries, and database-specific optimizations while maintaining compile-time type safety.
3

4
## Package Information
5

6
- **Package Name**: com.querydsl:querydsl-sql
7
- **Package Type**: maven
8
- **Language**: Java
9
- **Installation**: 
10
  ```xml
11
  <dependency>
12
    <groupId>com.querydsl</groupId>
13
    <artifactId>querydsl-sql</artifactId>
14
    <version>5.1.0</version>
15
  </dependency>
16
  ```
17

18
## Core Imports
19

20
```java
21
import com.querydsl.sql.SQLQueryFactory;
22
import com.querydsl.sql.SQLQuery;
23
import com.querydsl.sql.Configuration;
24
import com.querydsl.sql.SQLTemplates;
25
import com.querydsl.sql.PostgreSQLTemplates;
26
import com.querydsl.core.Tuple;
27
```
28

29
## Basic Usage
30

31
```java
32
import com.querydsl.sql.*;
33
import com.querydsl.sql.postgresql.PostgreSQLTemplates;
34
import javax.sql.DataSource;
35

36
// Setup configuration with database-specific templates
37
SQLTemplates templates = PostgreSQLTemplates.builder().build();
38
Configuration configuration = new Configuration(templates);
39

40
// Create query factory
41
SQLQueryFactory queryFactory = new SQLQueryFactory(configuration, dataSource);
42

43
// Build and execute queries
44
List<String> names = queryFactory
45
    .select(qUser.name)
46
    .from(qUser)
47
    .where(qUser.active.isTrue())
48
    .fetch();
49

50
// DML operations
51
long count = queryFactory
52
    .update(qUser)
53
    .set(qUser.lastLogin, LocalDateTime.now())
54
    .where(qUser.id.eq(userId))
55
    .execute();
56
```
57

58
## Architecture
59

60
QueryDSL SQL is built around several key components:
61

62
- **Query Factory**: Central entry point (`SQLQueryFactory`) for creating queries and DML operations
63
- **Query Builders**: Fluent interfaces (`SQLQuery`, `AbstractSQLQuery`) for constructing type-safe queries
64
- **SQL Templates**: Database-specific dialect handling for 17+ databases with optimized SQL generation
65
- **Type System**: Comprehensive type mapping between Java and SQL types with custom type support
66
- **DML Operations**: Type-safe insert, update, delete, and merge operations with batch support
67
- **Configuration System**: Flexible configuration for listeners, type mappings, and schema customization
68

69
## Capabilities
70

71
### Query Construction
72

73
Core query building functionality for SELECT statements with type-safe joins, conditions, grouping, and ordering. Supports complex queries including subqueries, CTEs, and window functions.
74

75
```java { .api }
76
public SQLQuery<?> query();
77
public <T> SQLQuery<T> select(Expression<T> expr);
78
public SQLQuery<Tuple> select(Expression<?>... exprs);
79
public <T> SQLQuery<T> selectFrom(RelationalPath<T> expr);
80
```
81

82
[Query Construction](./query-construction.md)
83

84
### Database Templates
85

86
Database-specific SQL dialect support with optimized templates for PostgreSQL, MySQL, Oracle, SQL Server, and 13+ other databases. Templates handle SQL syntax differences, function mappings, and database-specific features.
87

88
```java { .api }
89
public abstract class SQLTemplates {
90
    public String getTypeNameForCode(int code);
91
    public boolean isArraysSupported();
92
    public boolean isLimitOffsetSupported();
93
}
94

95
public class PostgreSQLTemplates extends SQLTemplates;
96
public class MySQLTemplates extends SQLTemplates;
97
public class OracleTemplates extends SQLTemplates;
98
```
99

100
[Database Templates](./database-templates.md)
101

102
### DML Operations
103

104
Type-safe Data Manipulation Language operations including INSERT, UPDATE, DELETE, and MERGE with batch processing support and automatic key generation.
105

106
```java { .api }
107
public SQLInsertClause insert(RelationalPath<?> path);
108
public SQLUpdateClause update(RelationalPath<?> path);
109
public SQLDeleteClause delete(RelationalPath<?> path);
110
public SQLMergeClause merge(RelationalPath<?> path);
111
```
112

113
[DML Operations](./dml-operations.md)
114

115
### Type System
116

117
Comprehensive type mapping system between Java and SQL types with support for custom types, enums, arrays, and database-specific types like UUID, JSON, and XML.
118

119
```java { .api }
120
public interface Type<T> {
121
    T getValue(ResultSet rs, int startIndex) throws SQLException;
122
    void setValue(PreparedStatement st, int startIndex, T value) throws SQLException;
123
    int[] getSQLTypes();
124
    Class<T> getReturnedClass();
125
}
126
```
127

128
[Type System](./type-system.md)
129

130
### Configuration and Customization
131

132
Flexible configuration system for customizing SQL generation, type mappings, naming strategies, schema mappings, and execution listeners.
133

134
```java { .api }
135
public class Configuration {
136
    public Configuration(SQLTemplates templates);
137
    public void register(String table, String property, Path<?> path);
138
    public void registerType(String typeName, Type<?> type);
139
    public void addListener(SQLListener listener);
140
}
141
```
142

143
[Configuration](./configuration.md)
144

145
### SQL Expressions and Functions
146

147
Comprehensive expression builder for SQL functions, operators, and database-specific features including window functions, aggregate functions, and conditional expressions.
148

149
```java { .api }
150
public class SQLExpressions {
151
    public static <T> SimpleExpression<T> constant(T value);
152
    public static StringExpression concat(Expression<String>... args);
153
    public static DateTimeExpression<Date> currentTimestamp();
154
    public static WindowFunction<Long> rowNumber();
155
}
156
```
157

158
[SQL Expressions](./sql-expressions.md)
159

160
## Types
161

162
### Core Query Interfaces
163

164
```java { .api }
165
public interface SQLCommonQuery<Q> {
166
    List<Tuple> fetch();
167
    <T> List<T> fetch();
168
    Tuple fetchOne();
169
    <T> T fetchOne();
170
    long fetchCount();
171
}
172

173
public interface SQLCommonQueryFactory<Q, D, U, I, M> {
174
    Q query();
175
    D delete(RelationalPath<?> path);
176
    U update(RelationalPath<?> path);
177
    I insert(RelationalPath<?> path);
178
    M merge(RelationalPath<?> path);
179
}
180
```
181

182
### Path and Metadata
183

184
```java { .api }
185
public interface RelationalPath<T> extends Path<T> {
186
    SchemaAndTable getSchemaAndTable();
187
    Collection<? extends Path<?>> getColumns();
188
    PrimaryKey<T> getPrimaryKey();
189
}
190

191
public class SchemaAndTable {
192
    public SchemaAndTable(String schema, String table);
193
    public String getSchema();
194
    public String getTable();
195
}
196
```
197

198
### Query Result Handling
199

200
```java { .api }
201
public class SQLBindings {
202
    public String getSQL();
203
    public List<Object> getBindings();
204
}
205

206
public interface Union<T> {
207
    List<T> fetch();
208
    T fetchOne();
209
    Union<T> orderBy(OrderSpecifier<?>... o);
210
}
211
```