Database Support

PojoQuery is designed to work with relational databases that support standard SQL and provide a JDBC driver.

Tested Databases

PojoQuery is regularly tested against:

  • MySQL: Versions 5.7 and 8.0+

  • PostgreSQL: Versions 10+

  • HSQLDB: Primarily for testing purposes.

General Compatibility

PojoQuery should work with most modern relational databases, including:

  • SQL Server

  • Oracle

  • MariaDB

  • SQLite (with appropriate JDBC driver)

The core query generation relies on standard SQL syntax for SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY, and LIMIT/OFFSET.

Database-Specific Considerations

  • SQL Functions (@Select): When using the @Select annotation with database functions (e.g., CONCAT, YEAR, MAX), ensure you use syntax compatible with your target database.

  • Quoting: PojoQuery attempts to quote identifiers (table names, column names, aliases) correctly based on the DbContext. The default context uses backticks (` ` `) suitable for MySQL. You can create a custom `DbContext for other quoting styles (e.g., double quotes " for PostgreSQL, SQL Server). [source,java] ---- // Example for PostgreSQL/Standard SQL quoting DbContext pgContext = new DbContext(DbContext.QuoteStyle.DOUBLE_QUOTES); PojoQuery<User> query = PojoQuery.build(pgContext, User.class); ----

  • Generated Keys: The mechanism for retrieving auto-generated keys after an INSERT relies on standard JDBC Statement.RETURN_GENERATED_KEYS. This is widely supported.

  • LIMIT/OFFSET Syntax: PojoQuery generates the LIMIT offset, rowCount syntax. While common, some databases might use different syntax (e.g., OFFSET offset FETCH NEXT rowCount ROWS ONLY in SQL Server/Oracle). For maximum compatibility across databases not using LIMIT offset, rowCount, you might need to handle pagination outside of PojoQuery’s setLimit() or use database-specific query modifications if necessary.

  • Data Types: Ensure that the Java types used in your POJOs map correctly to the corresponding data types in your database according to your JDBC driver’s behavior. PojoQuery performs basic handling for LocalDate and Enum types during parameter setting.

Configuration

PojoQuery interacts with the database solely through a standard javax.sql.DataSource. Configure your DataSource using your preferred connection pool (like HikariCP, c3p0, Tomcat JDBC Pool) or framework (like Spring Boot) and pass it to PojoQuery’s execution methods (execute, executeStreaming, findById, etc.) or the static DB helper methods.