FastQuery

https://github.com/xixifeng/fastquery
Admin
FastQuery is a Java data persistence framework that simplifies database operations through...
Tokens:34,065
Snippets:231
Trust Score:5.5
License:Apache-2.0
Update:6 months ago
Show doc for...
Tokens:
Raw
### Example Usage of @QueryByNamed

Source: https://context7.com/xixifeng/fastquery/llms.txt

Demonstrates how to obtain and use a repository with named queries defined via @QueryByNamed.

```java
QueryByNamedDBService db = FQuery.getRepository(QueryByNamedDBService.class);

// 简单查询
JSONArray allUsers = db.findUserInfoAll();

// 条件查询
List<UserInfo> users = db.findUserInfoByCondition("张%", 20);

// 分页查询
Page<UserInfo> page = db.findUserPage(new PageableImpl(1, 10), "李%");
```

--------------------------------

### FastQuery Usage Examples

Source: https://context7.com/xixifeng/fastquery/llms.txt

Demonstrates how to obtain a repository instance and perform basic, conditional, and paginated queries using the FastQuery framework.

```java
// 使用示例
UserInfoDBService db = FQuery.getRepository(UserInfoDBService.class);

// 基本查询
UserInfo user = db.findById(1);

// 条件查询
List<UserInfo> users = db.findByNameAndAge("张三", 18);

// 分页查询
Pageable pageable = new PageableImpl(1, 10); // 第1页，每页10条
Page<UserInfo> page = db.findPageByAge(20, pageable);
List<UserInfo> content = page.getContent();      // 当前页数据
long total = page.getTotalElements();            // 总记录数
int totalPages = page.getTotalPages();           // 总页数
boolean hasNext = page.isHasNext();              // 是否有下一页
```

--------------------------------

### Configuration Files

Source: https://context7.com/xixifeng/fastquery/llms.txt

Configuration details for FastQuery, including the main fastquery.json file and examples for connection pool configurations.

```APIDOC
## 配置文件

### fastquery.json 配置

`fastquery.json` 用于配置数据源的作用范围和全局设置。支持配置多数据源、基准目录、调试模式、SQL 模板目录和慢查询时间等。

```json
{
  "basedir": "/data/sql/",
  "debug": false,
  "queries": ["queries/", "sql/templates/"],
  "slowQueryTime": 100,
  "scope": [
    {
      "config": "c3p0",
      "dataSourceName": "master-db",
      "basePackages": [
        "org.example.dao",
        "org.example.repository.UserInfoDBService"
      ]
    },
    {
      "config": "druid",
      "dataSourceName": "slave-db",
      "basePackages": [
        "org.example.dao.readonly"
      ]
    },
    {
      "config": "hikari",
      "dataSourceName": "analytics-db",
      "basePackages": [
        "org.example.analytics"
      ]
    }
  ]
}
```

### 连接池配置示例

c3p0-config.xml:
```xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="master-db">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb?useSSL=false</property>
        <property name="user">root</property>
        <property name="password">password</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">100</property>
    </named-config>
</c3p0-config>
```

hikari.xml:
```xml
<beans>
    <bean name="analytics-db">
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/analytics" />
        <property name="dataSource.user" value="root" />
        <property name="dataSource.password" value="password" />
        <property name="dataSource.cachePrepStmts" value="true" />
        <property name="dataSource.prepStmtCacheSize" value="250" />
    </bean>
</beans>
```
```

--------------------------------

### SQL IN Clause Behavior Examples

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Illustrates how null values behave within SQL IN and NOT IN clauses.

```sql
id not in (1,2,null) -- 查不出
id in (null)         -- 并不会把id为null的记录查出来,id是null与否,最终查不出.
id in(1,2,null)      -- id为1或为2的结果会被查询出来
```

--------------------------------

### Perform Basic CRUD Operations

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Examples of saving and updating entities. Note that null fields in an entity are ignored during update operations.

```java
UserInfo u1 = new UserInfo(36,"Dick", 23);

// 保存实体
studentDBService.save(u1)

Integer id = 36;
String name = "Dick";
Integer age = null;
UserInfo u2 = new UserInfo(id,name,age);
// age是null值, age就不会参与修改运算了.
studentDBService.update(u2); // 更新语句为: update UserInfo set name = ? where id = ?

// 保存或更新实体
studentDBService.saveOrUpdate(u1);
```

--------------------------------

### c3p0 Connection Pool Configuration

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Example configuration for the c3p0 connection pool. Supports multiple named configurations for different data sources.

```xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="xk-c3p0">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://192.168.1.1:3306/xk?useSSL=false</property>
        <property name="user">xk</property>
        <property name="password">abc123456</property>
        <property name="acquireIncrement">50</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
     <!-- 可以配置多个named-config节点,多个数据源 -->
    <named-config name="name-x"> ... ... </named-config>
</c3p0-config>
```

--------------------------------

### Druid Connection Pool Configuration

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Example configuration for the Druid connection pool. Supports multiple bean definitions for different data sources.

```xml
<beans>
	 <bean name="xkdb1" id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
                                        init-method="init" destroy-method="close">
	     <property name="url" value="jdbc:mysql://db.fastquery.org:3305/xk" />
	     <property name="username" value="xk" />
	     <property name="password" value="abc123456" />
	     <property name="filters" value="stat" />
	     <property name="maxActive" value="20" />
	     <property name="initialSize" value="1" />
	     <property name="maxWait" value="60000" />
	     <property name="minIdle" value="1" />
	     <property name="timeBetweenEvictionRunsMillis" value="60000" />
	     <property name="minEvictableIdleTimeMillis" value="300000" />
	     <property name="testWhileIdle" value="true" />
	     <property name="testOnBorrow" value="false" />
	     <property name="testOnReturn" value="false" />
	     <property name="poolPreparedStatements" value="true" />
	     <property name="maxOpenPreparedStatements" value="20" />
	 </bean>
	 <!-- 再配置一个数据源 -->
	 <bean name="xkdb2" id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
                                        init-method="init" destroy-method="close">
	     <property name="url" value="jdbc:mysql://db.fastquery.org:3305/xk" />
	     <property name="username" value="xk" />
	     <property name="password" value="abc123456" />
	 </bean>
</beans>
```

--------------------------------

### HikariCP Connection Pool Configuration

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Example configuration for the HikariCP connection pool, optimized for MySQL. Supports multiple bean definitions for different data sources.

```xml
<beans>
	<bean name="xkdb2">
		<property name="jdbcUrl" value="jdbc:mysql://192.168.1.1:3306/xk" />
		<property name="dataSource.user" value="xk" />
		<property name="dataSource.password" value="abc123456" />
		<property name="dataSource.cachePrepStmts" value="true" />
		<property name="dataSource.prepStmtCacheSize" value="250" />
		<property name="dataSource.prepStmtCacheSqlLimit" value="2048" />
		<property name="dataSource.useServerPrepStmts" value="true" />
		<property name="dataSource.useLocalSessionState" value="true" />
		<property name="dataSource.rewriteBatchedStatements" value="false" />
		<property name="dataSource.cacheResultSetMetadata" value="true" />
		<property name="dataSource.cacheServerConfiguration" value="true" />
		<property name="dataSource.elideSetAutoCommits" value="true" />
		<property name="dataSource.maintainTimeStats" value="false" />
	</bean>
	<!-- 可以配置多个bean节点,提供多个数据源 -->
	<bean name="name-x"> ... ... </bean>
</beans>
```

--------------------------------

### @Modifying Usage Examples

Source: https://context7.com/xixifeng/fastquery/llms.txt

Demonstrates how to perform update, delete, and insert operations using the @Modifying annotation, including returning affected rows, success status, and newly inserted entities.

```java
// 使用示例
UserInfoDBService db = FQuery.getRepository(UserInfoDBService.class);

// 更新操作
int affected = db.updateById(1, "李四", 25);
System.out.println("影响行数: " + affected);

// 删除操作
boolean deleted = db.deleteById(100);
System.out.println("删除成功: " + deleted);

// 插入并返回实体
UserInfo newUser = db.insertAndReturn("王五", 30);
System.out.println("新用户ID: " + newUser.getId());
```

--------------------------------

### Define DAO Interface Extending QueryRepository

Source: https://context7.com/xixifeng/fastquery/llms.txt

An example of defining a Data Access Object (DAO) interface that extends QueryRepository. Custom methods can be added alongside the built-in ones.

```java
// 定义 DAO 接口
public interface UserInfoDBService extends QueryRepository {
    // 可以添加自定义方法
}
```

--------------------------------

### Configure a Service Data Source

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Configure a data source that acts as a service, usable by repositories that do not explicitly specify a data source. 'basePackages' is optional here.

```json
{
    "config": "c3p0",           
    "dataSourceName": "rex-db"
}

```

--------------------------------

### Modification Operations

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Covers various modification operations including update, delete, and insert, with examples of return types and configurations.

```APIDOC
## Modification Operations

```java
// Returns the number of rows affected after modification
@Query("update student s set s.age=?3,s.name=?2 where  s.no=?1")
@Modifying
int update(String no,String name,int age);

// Returns true if modification is successful, false otherwise
@Modifying
@Query("delete from `userinfo` where id=?1")
boolean deleteUserinfoById(int id);

// Returns the current saved data in entity bean format
@Query("insert into student (no, name, sex, age, dept) values (?1, ?2, ?3, ?4, ?5)")
@Modifying(table="student",id="no")
// Note: // Note: The primary key of student is a string, so it will not auto-increment. You need to use @Id here to identify which field is the primary key.
Student addStudent(@Id String no,String name,String sex,int age,String dept);

// Returns the current saved data in JSON format
@Modifying(id="id",table="userinfo")
@Query("insert into #{#table} (name,age) values (?1, ?2)")
JSONObject saveUserInfo2(String name,Integer age);

// Returns the primary key information of the currently saved data
@Modifying(id="id",table="userinfo")
@Query("insert into #{#table} (name,age) values (?1, ?2)")
Primarykey saveUserInfo(String name,Integer age);
```

Insert a new record and return the entity. You can specify the fields to be queried using the `selectFields` configuration item in `@Modifying`. For example:

```java
// Returns the current saved data in Map format
@Modifying(id="id",table="userinfo",selectFields="name,age")
@Query("insert into #{#table} (name,age) values (?1, ?2)")
Map<String, Object> addUserInfo(String name,Integer age);
```

The `selectFields` defaults to **<code>*</code>**. Separate fields with English commas.

**Note**:
- Modification operations returning an `int` type indicate the number of affected rows. If no matching record is found for modification, the affected row count is 0, which does not indicate a failed modification.
- Modification operations returning a `boolean` type indicate whether the modification was successful. If the number of affected rows is greater than or equal to 0, it returns `true`; otherwise, it returns `false`.
```

--------------------------------

### 类属性与表字段映射

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

当类属性名称与数据库表字段不一致时，可以使用SQL别名（AS）进行映射，确保查询结果能正确映射到实体对象。

```java
// 把查询到的结果映射给UserInformation
@Query("select id as uid,name as myname,age as myage from UserInfo u where u.id = ?1")
UserInformation findUserInfoById(Integer id);
```

--------------------------------

### Execute dynamic QueryBuilder

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Instantiate a QueryBuilder with custom SQL and conditions, then execute it via the repository.

```java
// 获取Repository实例
UserInfoDBService db = FQuery.getRepository(UserInfoDBService.class);

String query = "select id,name,age from userinfo #{#where}";
String countQuery = "select count(name) from userinfo #{#where}";
ConditionList conditions = ConditionList.of("age > :age","and id < :id");
Map<String, Object> parameters = new HashMap<>();
parameters.put("age", 18);
parameters.put("id", 50);

QueryBuilder queryBuilder = new QueryBuilder(query, countQuery, conditions, parameters);
Page<Map<String, Object>> page = db.pageByQueryBuilder(queryBuilder,new PageableImpl(1, 3));
List<Map<String, Object>> content = page.getContent();
content.forEach(map -> {
	Integer age = (Integer) map.get("age");
	Integer id = (Integer) map.get("id");
	assertThat(age, greaterThan(18));
	assertThat(id, lessThan(50));
});

List<String> executedSQLs = rule.getExecutedSQLs();
assertThat("断言：执行过的sql有两条",executedSQLs.size(), is(2));
assertThat(executedSQLs.get(0), equalTo("select id,name,age from userinfo where age > ? and id < ? limit 0,3"));
assertThat(executedSQLs.get(1), equalTo("select count(name) from userinfo where age > ? and id < ?"));
```

--------------------------------

### Dynamic Data Source Routing with @Source

Source: https://context7.com/xixifeng/fastquery/llms.txt

Employ @Source to dynamically switch data sources, ideal for multi-tenant systems. Allows runtime selection of data sources based on parameters for isolated database operations.

```java
public interface UserInfoDBService extends QueryRepository {

    // 根据参数动态选择数据源
    @Query("select id, name, age from userinfo where age > ?1")
    List<UserInfo> findByAge(Integer age, @Source String dataSourceName);

    // 动态数据源 + 分页
    @Query("select * from userinfo #{#where}")
    @Condition("name like :name")
    Page<UserInfo> findPage(@Param("name") String name,
                            Pageable pageable,
                            @Source String dataSourceName);
}
```

```java
// 使用示例

// 1. 动态创建数据源
Properties props = new Properties();
props.setProperty("driverClass", "com.mysql.cj.jdbc.Driver");
props.setProperty("jdbcUrl", "jdbc:mysql://192.168.1.100:3306/tenant_a");
props.setProperty("user", "root");
props.setProperty("password", "password");
FQuery.createDataSource("tenant_a", props);

// 2. 创建另一个租户的数据源
Properties props2 = new Properties();
props2.setProperty("driverClass", "com.mysql.cj.jdbc.Driver");
props2.setProperty("jdbcUrl", "jdbc:mysql://192.168.1.100:3306/tenant_b");
props2.setProperty("user", "root");
props2.setProperty("password", "password");
FQuery.createDataSource("tenant_b", props2);

// 3. 使用动态数据源查询
UserInfoDBService db = FQuery.getRepository(UserInfoDBService.class);

// 查询租户 A 的数据
List<UserInfo> tenantAUsers = db.findByAge(20, "tenant_a");

// 查询租户 B 的数据
List<UserInfo> tenantBUsers = db.findByAge(20, "tenant_b");

// 分页查询指定租户
Page<UserInfo> page = db.findPage("张%", new PageableImpl(1, 10), "tenant_a");
```

--------------------------------

### XML Configuration with Global Parts and Method-Specific Logic

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Defines global parts and uses Velocity templating to conditionally select SQL based on the method name. `${_method.getName()}` references the current method's name.

```xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xml>
<queries>
	<!-- 定义全局 parts -->
	<parts>
		<part name="feids">id,name,age</part>
	</parts>
	<query id="findUAll">
		<value>select #{#feids} from UserInfo limit 3</value>
	</query>

	<query id="findSome">
		<![CDATA[
		## 如果当前方法的名称等于 "findLittle" 
		#if( ${_method.getName()} == "findLittle" ) 
			## 查3条  
			select #{#feids} from UserInfo limit 3
		#else 
		   select `no`, `name` from Student limit 5
		#end  
		]]>
	</query>	
</queries>
```

--------------------------------

### Java Interface for Modifying Named Queries

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Example of using @QueryByNamed for a modifying operation (e.g., UPDATE). The @Modifying annotation indicates that the query will change data.

```java
@Modifying
@QueryByNamed("updateUserInfoById")
int updateUserInfoById(@Param("id") int id,@Param("name") String name,@Param("age") int age);
```

--------------------------------

### Manage Dynamic Data Sources

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Create data sources at runtime and map repository methods to specific data sources using @Source.

```java
// 数据源名称
String dataSourceName = "xk1";

// 连接池配置
Properties properties = new Properties();
properties.setProperty("driverClass", "com.mysql.cj.jdbc.Driver");
properties.setProperty("jdbcUrl", "jdbc:mysql://db.fastquery.org:3306/xk1");
properties.setProperty("user", "xk1");
properties.setProperty("password", "abc1");

// 创建数据源
FQuery.createDataSource(dataSourceName, properties);
```

```java
@Query("select id,name,age from `userinfo` as u where u.age>?1")
Map<String, Object> findOne(Integer age,@Source String dataSourceName);
```

--------------------------------

### Execute Batch SQL Files

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Execute SQL statements from a file and support dynamic table names using parameter substitution.

```java
String sqlName = "update.sql";
int[] effects = studentDBService.executeBatch(sqlName);
```

```sql
drop table if exists $[0].demo_table;
```

--------------------------------

### Executing SQL Files

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

This section covers executing SQL files, including batch execution and handling dynamic parameters.

```APIDOC
## Executing SQL Files

### Batch Execution

Execute SQL files using `executeBatch`.

```java
String sqlName = "update.sql";
int[] effects = studentDBService.executeBatch(sqlName);
```

- `sqlName`: Specifies the SQL file. It can be a path relative to the base directory configured in `fastquery.json` or an absolute path.
- Returns `int[]`: An array where each element represents the number of rows affected by the corresponding SQL statement in the file.
- Statement Separation: SQL statements are separated by semicolons.
- Comments: Only full-line comments starting with `#` or `--` are supported.

### Dynamic Parameters in SQL Files

Use `executeBatch(String sqlName, String[] quotes)` to pass dynamic parameters to SQL files. These parameters can be referenced in the SQL file using `$[N]`, where N is the index in the `quotes` array.

```sql
drop table if exists $[0].demo_table;
```
```

--------------------------------

### 带条件查询返回不同格式

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

使用@Query注解进行带条件查询，支持返回数组、JSON、List Map、List实体等多种格式。参数可通过?N或:name引用。

```java
// sql中的?1 表示对应当前方法的第1个参数
// sql中的?2 表示对应当前方法的第2个参数
//       ?N 表示对应当前方法的第N个参数	
// 查询返回数组格式
@Query("select no,name,sex,age,dept from student s where s.sex=:sex and s.age > ?1")
Student[] find(Integer age,@Param("sex")String sex);
 
// 查询返回JSON格式
@Query("select no, name, sex from student s where s.sex=:sex and s.age > ?2")
JSONArray find(@Param("sex")String sex,Integer age);

// 查询返回List Map
@Query("select no, name, sex from student s where s.sex=?1 and s.age > :age")
List<Map<String, Object>> findBy(String sex,@Param("age")Integer age);

// 查询返回List 实体
@Query("select id,name,age from `userinfo` as u where u.id>?1")
List<UserInfo> findSome(@Param("id")Integer id);
```

--------------------------------

### Execute SQL Batch from File

Source: https://context7.com/xixifeng/fastquery/llms.txt

Executes a batch of SQL statements defined in a specified SQL file.

```java
// 15. 执行 SQL 文件
int[] results = db.executeBatch("init-data.sql");
```

--------------------------------

### Dynamic Data Source Adaptation

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

This section details how to dynamically create and adapt data sources within the FastQuery framework.

```APIDOC
## Dynamic Data Source Adaptation

### Creating Data Sources

Dynamically create new data sources at runtime using `FQuery.createDataSource`.

```java
// Data source name
String dataSourceName = "xk1";

// Connection pool configuration
Properties properties = new Properties();
properties.setProperty("driverClass", "com.mysql.cj.jdbc.Driver");
properties.setProperty("jdbcUrl", "jdbc:mysql://db.fastquery.org:3306/xk1");
properties.setProperty("user", "xk1");
properties.setProperty("password", "abc1");

// Create data source
FQuery.createDataSource(dataSourceName, properties);
```

### Adapting Data Sources

Use the `@Source` annotation to specify which data source a repository method should use. This is particularly useful in multi-tenant systems.

**Note**: If `@Source` is applied to a parameter, that parameter must be of type `String`.

```java
@Query("select id,name,age from `userinfo` as u where u.age>?1")
Map<String, Object> findOne(Integer age,@Source String dataSourceName);
```

### Data Source Adaptation Priority

If a data source scope is explicitly defined in `fastquery.json` and the interface method also uses `@Source`, the data source specified by `@Source` takes precedence. Otherwise, the configuration in `fastquery.json` is used.
```

--------------------------------

### Implement Programmatic Pagination

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Use PageableImpl to handle pagination manually and retrieve page content or metadata.

```java
int p = 1;    // 指定访问的是第几页(不是从0开始计数)
int size = 3; // 设定每一页最多显示几条记录
Integer age=10,id = 50;
Pageable pageable = new PageableImpl(p, size);
Page<UserInfo> page  = userInfoDBService.findSome(age, id,pageable);
List<UserInfo> userInfos = page.getContent(); // 获取这页的数据
Slice slice = page.getNextPageable();         // 下一页
int number = page.getNumber();                // 当前页数(当前是第几页)
// 更多 page.? 不妨亲自去试试看
```

--------------------------------

### Parameter Handling with Default Values and SQL Injection Prevention

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

This section details how to manage parameter values, including setting default values when null is passed and preventing SQL injection vulnerabilities.

```APIDOC
## Parameter Handling Considerations

When using parameters in FastQuery, keep the following in mind:

- Passing `null` values for template variables will result in an empty string (`""`) by default.
- Parameter templates are aids for constructing SQL statements and should be used cautiously.
- Improper usage can lead to SQL injection vulnerabilities.
- Avoid deriving parameter values solely from user input.
- Ensure parameter values are controllable.

### Default Values

The `defaultVal` attribute can be used to specify a default value if a parameter receives `null`. This attribute is optional and defaults to an empty string.

```java
@Query("select * from `userinfo` ${orderby}")
// If orderby is null, ${orderby} will take the value of defaultVal.
JSONArray findUserInfo(@Param(value="orderby",defaultVal="order by age desc") String orderby);
```

### SQL Injection Prevention

If a parameter's value, especially within a `$expression`, cannot be fully controlled and originates from user input, use the `@Safe` annotation to prevent SQL injection.

```java
@Query("select * from `userinfo` ${orderby}")
JSONArray findUserInfo(@Param(value="orderby") @Safe String orderby);
```

When `@Safe` is used, the framework strictly checks the provided argument for potential injection risks and rejects the request if any are detected.
```

--------------------------------

### Implement pagination with @Query

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Define repository methods using @Query and @Condition for automatic pagination.

```java
public interface UserInfoDBService extends QueryRepository {

	// Pageable 用做描述当前页的索引和每页条数.
    
	// countField : 明确指定用来统计总行数的字段,count(countField)中的countField默认值是"id"
	@Query(value="select id,name,age from `userinfo` where 1",countField="id")
	Page<Map<String, Object>> findAll(Pageable pageable);
	
	// 如果没有指定count语句,那么由fastquery分析出最优的count语句
	@Query("select id,name,age from `userinfo` #{#where}")
	@Condition("age > ?1")     // 若age的值传递null,该条件将不参与运算
	@Condition("and id < ?2")  // 若id的值传递null,该条件将不参与运算
	Page<UserInfo> find(Integer age,Integer id,Pageable pageable);
	
	// countQuery : 指定自定义count语句
	@Query(value = "select id,name,age from `userinfo` #{#where}", 
	       countQuery = "select count(id) from `userinfo` #{#where}")
	@Condition("age > ?1")        // 若age的值传递null,该条件将不参与运算
	@Condition("and id < ?2")     // 若id的值传递null,该条件将不参与运算
	Page<UserInfo> findSome(Integer age,Integer id,Pageable pageable);
}
```

--------------------------------

### 查询单个字段的返回类型

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

查询单个字段支持返回List<String>、String[]、String等多种类型。基本类型的包装类可返回null表示未查到或值为null。

```java
// 查询单个字段,若没有查到,就返回空List<String>(非null)
@Query("select name from Student limit 3")
List<String> findNames();
```

--------------------------------

### Count Operation

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Demonstrates how to perform a count query to retrieve the number of rows.

```APIDOC
## Count

Counts the number of query rows.

```java
@Query("select count(no) from student")
long count();
```
```

--------------------------------

### Configure Data Source Scopes in fastquery.json

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Define the scope and configuration for different data sources. 'config' specifies the connection pool type (e.g., c3p0, mySQLDriver), 'dataSourceName' is optional but must be correct if provided, and 'basePackages' defines the packages or classes that will use this data source.

```json
// @author xixifeng (fastquery@126.com)
// 配置必须遵循标准的json语法.
{
  "scope":[
		    // config 用于指定由谁来提供数据源,如,"c3p0","druid","hikari"等等
		    {
		        "config": "c3p0",            // 表示由c3p0负责提供数据源
		        "dataSourceName": "xk-c3p0", // 数据源的名称
		        "basePackages": [
		            "org.fastquery.example",              // 包地址
		            "org.fastquery.dao.UserInfoDBService" // 完整类名称 
		            // 在这可以配置多个DB接口或包地址,以","号隔开
		            // 提醒:在json结构中,数组的最后一个元素的后面不能加"," 
		        ]
		    },
		    
		     /*
		      再配置一个数据源作用域
		     */
		     {
		        "config" : "mySQLDriver",      // 表示由mySQLDriver负责提供数据源
		        "dataSourceName": "shtest_db", // 数据源的名称
		        "basePackages": [
		            "org.fastquery.example.DataAcquireDbService"
		             // 在这可以配置多个DB接口,以","号隔开
		        ]
		     },
		    
		     {
		        "config": "c3p0",              // 表示由c3p0负责提供数据源
		        "basePackages": [
		             "org.fastquery.dao2.UserInfoDB"
		        ]
		     }
		  ]
}

```

--------------------------------

### 无查询到数据时的返回行为

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

在未查询到数据时，集合、JSON或数组类型的返回值将是空对象集合、空JSON或长度为0的数组，而非null，以避免NullPointerException。

```java
// 针对该方法,如果没有查询到数据,返回值的结果是一个长度为0的Student[]
@Query("sql statements")
Student[] find(Integer age,String sex);

// 针对该方法,如果没有查询到数据,返回值的结果是一个空Map(非null)
@Query("sql statements")
Map<String,Object> find(Integer id);

// 针对该方法,如果没有查询到数据,返回值的结果是一个空List<Map>(非null)
@Query("sql statements")
List<Map<String, Object>> find(String sex);
```

--------------------------------

### Define a Repository Interface

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Extend QueryRepository to enable built-in database operations.

```java
public interface StudentDBService extends QueryRepository {
   ... ...
}
```

--------------------------------

### Extend Connection Pool Support

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Implement custom connection pool providers and register them in the configuration.

```java
public class MyPoolProvider implements ConnectionPoolProvider {

	@Override
	public DataSource getDataSource(Resource resource, String dataSourceName) {
		// 读取配置文件
		InputStream inputStream = resource.getResourceAsStream(name);
		.... ...

		Properties props = new Properties();
		props.setProperty(k, v);
		... ...
		
		// 创建数据源实例
		return new MyDataSource(props);
	}

}
```

```xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xml>
<pools name="providers">
	<pool name="mypool" class="org.fastquery.<your.domain>.MyPoolProvider" />
</pools>
```

```js
{
    "scope": [
        {
            "config": "mypool", // 用这个池提供数据源 
            "dataSourceName": "hiworld",
            "basePackages": [
                "<your.domain>.XxxDBService"
            ]
        }
    ]
}
```

--------------------------------

### Configure Spring Component Scan for FastQuery

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Use either XML configuration or Java annotations to specify the base package for FastQuery service scanning within your Spring application context.

```xml
<context:component-scan base-package="org.fastquery.service" />
```

```java
@ComponentScan("org.fastquery.service")
```

--------------------------------

### Implement pagination with @QueryByNamed

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Use a named query template to handle pagination.

```java
@QueryByNamed("findPage") // 引用id为"findPage"的分页模板
Page<Student> findPage(Pageable pageable, @Param("name") String name,@Param("age") Integer age);
```

--------------------------------

### Parameter Binding

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Techniques for binding method parameters to SQL queries using colon expressions or template variables.

```APIDOC
## Parameter Binding

### Description
Bind method parameters to SQL queries using named parameters or template substitution.

### Methods
- **:name**: Named parameter binding (safe against SQL injection).
- **${name}**: Template substitution (replaces text directly, use with caution regarding SQL injection).

### Request Example
```java
@Query("select name,age from UserInfo u where u.name = :name or u.age = :age")
UserInfo[] findUserInfoByNameOrAge(@Param("name") String name, @Param("age") Integer age);
```
```

--------------------------------

### FastQuery Summary

Source: https://context7.com/xixifeng/fastquery/llms.txt

A summary of FastQuery's core advantages, design philosophy, and suitable application scenarios.

```APIDOC
## 总结

FastQuery 是一个功能强大且易于使用的 Java 数据持久层框架，其核心优势在于注解驱动的开发模式和零实现类的设计理念。开发者只需定义接口并通过 `@Query`、`@Modifying`、`@Condition`、`@Set` 等注解声明 SQL 和条件，框架会在项目初始化阶段自动生成实现类。这种设计不仅大幅减少了样板代码，还通过编译期检查最大限度地减少了运行时错误。框架内置的分页支持、动态条件查询、事务管理和多数据源切换功能，能够满足绝大多数企业级应用的数据访问需求。

FastQuery 特别适合以下应用场景：需要灵活动态查询条件的业务系统、多租户 SaaS 平台（通过 `@Source` 实现数据源隔离）、需要复杂 SQL 模板的数据密集型应用（使用 `@QueryByNamed` 配合 Velocity 模板）、以及追求开发效率和代码简洁性的敏捷开发团队。框架支持与 Spring 框架无缝集成，可通过 `@Resource` 注解直接注入 Repository 实例。同时，框架提供的 `FastQueryTestRule` 测试支持能够帮助开发者轻松验证生成的 SQL 语句和参数绑定，确保数据访问层的正确性和可靠性。
```

--------------------------------

### Define XML pagination template

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

XML configuration for a reusable pagination query template.

```xml
<query id="findPage">
	<!-- 查询主体语句 -->
	<value>
		select no, name, sex from Student #{#condition} #{#order}
	</value>

	<!-- count语句 -->
	<countQuery>
		select count(no) from Student #{#condition}
	</countQuery>

	<!-- 定义零件集,他们可以被value,countQuery节点引用,以达到复用的效果 -->
	<parts>
		<part name="condition">
	       <![CDATA[
		    <where>
			#if($no)
			no like :no
			#end

			#if($name)
			or name like :name
			#end

			#if($age)
			or age > :age
			#end
	        </where>
	        ]]>
		</part>

		<part name="order">
			order by age desc
		</part>
	</parts>
</query>
```

--------------------------------

### 配置数据库连接池

Source: https://context7.com/xixifeng/fastquery/llms.txt

展示 c3p0 和 hikari 的 XML 配置示例。

```xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="master-db">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb?useSSL=false</property>
        <property name="user">root</property>
        <property name="password">password</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">100</property>
    </named-config>
</c3p0-config>
```

```xml
<beans>
    <bean name="analytics-db">
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/analytics" />
        <property name="dataSource.user" value="root" />
        <property name="dataSource.password" value="password" />
        <property name="dataSource.cachePrepStmts" value="true" />
        <property name="dataSource.prepStmtCacheSize" value="250" />
    </bean>
</beans>
```

--------------------------------

### Dynamic Condition Queries

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Explains how to build dynamic SQL queries using the @Condition annotation to conditionally include or exclude SQL fragments based on parameter values.

```APIDOC
## Dynamic Condition Queries

### Description
Allows building dynamic SQL by conditionally appending fragments using @Condition. Conditions can be ignored based on null/empty values or custom Java scripts.

### Parameters
- **value** (String) - Required - The SQL fragment to append.
- **ignoreNull** (Boolean) - Optional - If false, condition is kept even if parameter is null.
- **ignoreEmpty** (Boolean) - Optional - If false, condition is kept even if parameter is empty string.
- **ignoreScript** (String) - Optional - A Java script expression that returns a boolean to decide if the condition should be removed.
```

--------------------------------

### Conditional Queries with if/else

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Demonstrates how to use if/else logic within @Condition annotations to dynamically include or exclude query conditions based on script evaluation.

```APIDOC
## @Condition with if...else

### Description
Control whether a condition is retained using an `if` condition. If the JAVA script bound to `if` evaluates to `true`, the `Condition` is retained. Otherwise, the value bound to `else` is used. If `else` is null or empty, the `Condition` is removed.

### Example

```java
@Query("select id,name,age from `userinfo` #{#where}")
@Condition(value="age > :age",if$=":age < 18", else$="name = :name")
Page<UserInfo> findPage(@Param("age")int age,@Param("name")String name,Pageable pageable);
```

If the actual value of `age` is less than 18, the `Condition` is retained. Otherwise, the `Condition`'s value becomes `name = :name`. The `else` part is optional; if the `if` evaluation is false and `else` is not provided, the SQL condition is simply removed.
```

--------------------------------

### Use DAO Interface with FastQuery

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Obtain a proxy instance of the DAO interface using FQuery.getRepository. It is recommended to assign this instance to a static class member for efficiency, as the instance is unique and immutable.

```java
public class StudentDBServiceTest {
	// 获取实现类
	private static StudentDBService studentDBService = FQuery.getRepository(StudentDBService.class);
	@Test
	public void test() {
		// 调用 findAll 方法
		JSONArray jsonArray = studentDBService.findAll();
		// 调用 find 方法
		Student[] students = studentDBService.find(); 
	}
}

```

--------------------------------

### 执行分页查询与获取信息

Source: https://context7.com/xixifeng/fastquery/llms.txt

展示如何创建分页参数、执行查询以及获取分页导航信息和数据内容。

```java
// 使用示例
UserInfoDBService db = FQuery.getRepository(UserInfoDBService.class);

// 创建分页参数
Pageable pageable = new PageableImpl(1, 10);  // 第1页，每页10条

// 执行分页查询
Page<UserInfo> page = db.findPage(20, pageable);

// 获取分页信息
List<UserInfo> content = page.getContent();           // 当前页数据
int size = page.getSize();                            // 每页条数
int numberOfElements = page.getNumberOfElements();    // 当前页实际条数
int number = page.getNumber();                        // 当前页码
long totalElements = page.getTotalElements();         // 总记录数
int totalPages = page.getTotalPages();                // 总页数

// 导航信息
boolean isFirst = page.isFirst();                     // 是否第一页
boolean isLast = page.isLast();                       // 是否最后一页
boolean hasContent = page.isHasContent();             // 是否有数据
boolean hasNext = page.isHasNext();                   // 是否有下一页
boolean hasPrevious = page.isHasPrevious();           // 是否有上一页

// 获取前后页信息
Slice nextSlice = page.getNextPageable();             // 下一页参数
Slice prevSlice = page.getPreviousPageable();         // 上一页参数

// 类型转换
Page<UserInfoVO> voPage = page.convert(UserInfoVO.class);
```

--------------------------------

### 定义分页查询接口

Source: https://context7.com/xixifeng/fastquery/llms.txt

通过继承 QueryRepository 定义分页接口，支持标准分页、跳过 count 查询以及参数化分页。

```java
public interface UserInfoDBService extends QueryRepository {

    // 标准分页
    @Query(value = "select * from userinfo where age > ?1", countField = "id")
    Page<UserInfo> findPage(Integer age, Pageable pageable);

    // 不统计总数的分页（提升性能）
    @NotCount
    @Query("select * from userinfo")
    Page<UserInfo> findPageNoCount(Pageable pageable);

    // 使用 @PageIndex 和 @PageSize 代替 Pageable
    @Query("select * from userinfo")
    Page<UserInfo> findPageByParams(@PageIndex int pageIndex, @PageSize int pageSize);
}
```

--------------------------------

### Conditional Query Execution

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

Demonstrates how to perform queries with parameters using positional (?) or named (@Param) placeholders and handle various return types.

```APIDOC
## Conditional Query Execution

### Description
Executes SQL queries defined in @Query annotations. Supports positional parameters (?N) and named parameters (:name).

### Method
N/A (Interface Method)

### Parameters
- **@Param** (Annotation) - Optional - Used to bind method arguments to named placeholders in SQL.

### Response
- **Return Types** (Array/List/Map/Entity) - Returns empty collections or arrays instead of null when no data is found.
```

--------------------------------

### 动态条件查询-注解实现

Source: https://github.com/xixifeng/fastquery/blob/master/README.md

使用@Query和@Condition注解实现简单的动态条件查询。默认情况下，null或空字符串的参数会移除对应条件，可通过ignoreNull和ignoreEmpty控制。

```java
@Query("select no, name, sex from Student #{#where} order by age desc")
// 增加若干个条件
@Condition("no like ?1")                            // ?1的值,如果是null, 该行条件将不参与运算
@Condition("and name like ?2")                      // 参数 ?2,如果接收到的值为null,该条件不参与运算
// 通过 ignoreNull=false 开启条件值即使是null也参与运算
@Condition(value = "and age > ?3",ignoreNull=false) // ?3接收到的值若为null,该条件将保留
@Condition("and name not like ?4") 
@Condition("or age between ?5 and ?6")
Student[] findAllStudent(... args ...);
```
Tokens:
Raw