JDBCRest turns any JDBC-accessible database directly into a RESTful API. Each table and view is exposed as an HTTP collection that you can query, filter, paginate, and aggregate over the query string — no per-endpoint code required. The API surface is heavily inspired by PostgREST, generalized from PostgreSQL-only to any database reachable through a JDBC driver.

This reference focuses on the HTTP API: the endpoints you call, the query-string mini-language for filtering and projection, and the JSON you get back. Each record is returned as a JSON object whose keys are column (or alias) names, and a collection is returned as a JSON array of such objects.

1. Base endpoint

JDBCRest exposes each table (and view) as an HTTP collection under the following path:

  • GET /api/data/{schema}/{table}

The response is a JSON array of records (objects with column names as keys).

Examples:

  • GET /api/data/public/users

  • GET /api/data/shop/products

A request such as GET /api/data/public/users returns:

[
  { "id": 1, "name": "Alice", "age": 30 },
  { "id": 2, "name": "Bob",   "age": 25 },
  { "id": 3, "name": "Carol", "age": 41 }
]

When no rows match, the response is an empty array:

[]

2. Filtering (query string)

You can filter rows by passing query string parameters. Each parameter name is a column, and the value encodes an operator and its operand(s) using a dot-separated mini-language.

Basic shape:

  • {column}={operator}.{value}

  • {column}=in.({value1},{value2},…​)

Multiple query parameters are combined with an implicit AND, i.e. col1=…​&col2=…​ becomes and(col1…​,col2…​).

Examples:

Description Request

users with name = 'Bob'

GET /api/data/public/users?name=eq.Bob

users with id > 1

GET /api/data/public/users?id=gt.1

users with 1 ≤ id < 20

GET /api/data/public/users?id=gte.1&id=lt.20

products with id IN (100,102,103)

GET /api/data/public/products?id=in.(100,102,103)

For example, GET /api/data/public/users?id=gt.1 returns only the matching records:

[
  { "id": 2, "name": "Bob",   "age": 25 },
  { "id": 3, "name": "Carol", "age": 41 }
]

2.1. Operators

Supported comparison operators (all are case-insensitive):

Operator Meaning

eq

equal to

neq

not equal to

gt

greater than

gte

greater than or equal

lt

less than

lte

less than or equal

in

value is among a list

2.2. Values and typing

Values can be:

  • Numbers: 1, 3.14, 1e6

  • Dates (ISO): 2024-12-31

  • Strings: either

    • Unquoted identifiers (letters, digits, underscore), e.g. Bob

    • Double-quoted strings for arbitrary text, e.g. "Bob Smith". To include a literal double quote inside a string, escape it by doubling it: "He said ""hi""" → He said "hi"

Examples:

  • name=eq."Alice Smith"

  • created_at=gte.2024-01-01

  • price=lt.10.5

  • category=in.("fresh fruit",vegetables)

Spaces are allowed inside the parentheses of in.(…​), but remember to URL-encode them in real HTTP calls if your tooling doesn’t do it automatically.

3. Boolean logic

JDBCRest supports explicit boolean expressions using the and(…​) and or(…​) functions, as well as negation with not. You can mix comparison terms and nested boolean groups.

  • or(expr1, expr2, …​)

  • and(expr1, expr2, …​)

  • Leaf negation: column=not.{op}.{value} (e.g., id=not.eq.100)

  • Group negation: not.or(expr1,expr2,…​), not.and(expr1,expr2,…​)

Where each expr is itself either a comparison like column.eq.42, an in-list like column.in.(1,2), or a nested boolean group.

Examples (expression syntax):

Description Expression

name is Alice or Bob

or(name.eq.Alice,name.eq.Bob)

age between 18 and 65 (inclusive)

and(age.gte.18,age.lte.65)

price ≥ 10 (i.e. not less than 10)

not.price.lt.10

(a = 1 and b = 2), or c > 0

or(and(a.eq.1,b.eq.2),c.gt.0)

How to use in a URL:

  • Pass boolean groups as their own query parameters named or, and, not.or, or not.and, with a parenthesized, comma-separated list as the value, e.g. ?or=(expr1,expr2).

  • For leaf negation, use a regular column parameter with the not. prefix before the operator, e.g. ?id=not.eq.100.

  • Multiple different parameters are implicitly combined with AND.

Examples (HTTP URLs):

  • GET /api/data/public/users?or=(name.eq.Alice,name.eq.Bob) → users named Alice or Bob

  • GET /api/data/public/users?not.and=(age.gte.11,age.lte.17) → users with age outside 11..17

The parser allows whitespace inside expressions; standard URL encoding rules apply.

4. How filtering maps to SQL

Internally, filters are transpiled to SQL WHERE clauses. For example:

Query parameter SQL WHERE clause

id=gt.1

WHERE id > 1

name=eq."Bob"

WHERE name = 'Bob'

id=in.(100,102)

WHERE id IN (100, 102)

and(age.gte.18,age.lte.65)

WHERE (age >= 18 AND age ⇐ 65)

or(a.eq.1,b.eq.2)

WHERE (a = 1 OR b = 2)

not.price.lt.10

WHERE NOT (price < 10)

5. Pagination (limit and offset)

You can paginate results using the following optional query parameters:

  • limit — maximum number of rows to return. Non-negative integer. If omitted, all matching rows are returned.

  • offset — number of rows to skip before starting to return rows. Non-negative integer. Defaults to 0.

Examples:

Description Request

first 10 users

GET /api/data/public/users?limit=10

25 users starting from the 51st row

GET /api/data/public/users?limit=25&offset=50

up to 5 users named Bob

GET /api/data/public/users?name=eq.Bob&limit=5

6. Selecting columns (select), aliases and aggregate functions

Use the select query parameter to return only specific columns, assign aliases to the output field names, or perform aggregate calculations.

Syntax:

  • select=col1,col2,…​

  • select=alias1:col1,alias2:col2,…​

  • select=count() - returns the total number of rows.

  • select=col.func() - applies an aggregate function to a column.

  • select=alias:col.func() - applies an aggregate function with an alias.

Supported aggregate functions:

Function Meaning

avg()

average value (cast to double precision)

count()

count of rows or non-null values

max()

maximum value

min()

minimum value

sum()

sum of values (cast to double precision)

Rules:

  • If select is omitted, all columns are returned (SELECT *).

  • Whitespace around commas and colons is ignored.

  • If an alias is not provided, the column name (or function name for count()) is used as the output key.

  • Automatic GROUP BY: If you mix aggregate functions and regular columns in the select parameter, JDBCRest automatically groups the results by all non-aggregate columns.

Examples:

  • GET /api/data/public/products?select=id,price → returns only id and price

  • GET /api/data/public/products?select=c1:id,c2:price → returns id as c1 and price as c2

  • GET /api/data/public/products?select=count() → returns the count of all products

  • GET /api/data/public/products?select=price.avg() → returns the average price

  • GET /api/data/public/products?select=category,total:price.sum() → returns total price per category (grouped by category)

The output keys follow the projection. For select=c1:id,c2:price:

[
  { "c1": 1, "c2": 9.99 },
  { "c1": 2, "c2": 19.50 }
]

A bare select=count() returns a single-row array:

[
  { "count": 42 }
]

And a grouped aggregate such as select=category,total:price.sum() returns one object per group:

[
  { "category": "fruit",     "total": 19.98 },
  { "category": "vegetables", "total": 7.50 }
]

How it maps to SQL (PostgreSQL):

select parameter SQL projection

select=id,price

SELECT "id", "price" FROM …​

select=c1:id,c2:price

SELECT "id" AS "c1", "price" AS "c2" FROM …​

select=count()

SELECT count(*) FROM …​

select=price.sum()

SELECT sum(CAST("price" AS double precision)) FROM …​

select=category,count:id.count()

SELECT "category", count("id") AS "count" FROM …​ GROUP BY "category"

7. Limitations and notes

  • Only GET is currently implemented.

  • Filters support eq, neq, gt, gte, lt, lte, in and boolean and/or/not.

  • Aggregate functions (avg, count, max, min, sum) are supported in the select parameter with automatic GROUP BY.

  • Multiple query parameters are implicitly combined with AND.

  • There is no support yet for: ordering, range headers, or write operations.

  • Identifiers are case-insensitive in the filter syntax and mapped as-is to SQL identifiers; use your actual column names.

8. Quick examples (curl)

Assuming your service is running on localhost:8080 and your data is in schema public.

What you want curl command

All users

curl "http://localhost:8080/api/data/public/users"

Users named Bob

curl "http://localhost:8080/api/data/public/users?name=eq.Bob"

Users with id in a set

curl "http://localhost:8080/api/data/public/users?id=in.(1,2,3)"

Teenagers (11..17)

curl "http://localhost:8080/api/data/public/users?and=(age.gte.11,age.lte.17)"

Not teenagers

curl "http://localhost:8080/api/data/public/users?not.and=(age.gte.11,age.lte.17)"

Products except id 100

curl "http://localhost:8080/api/data/public/products?id=not.eq.100"

Average price per category

curl "http://localhost:8080/api/data/public/products?select=category,avg_price:price.avg()"