Hint

This article is generated by AI translation.

Datasource Differences

dbVisitor strives to use a unified API to operate all relational databases and non-relational databases. In practice, however, individual data sources still have some differences due to their inherent characteristics. dbVisitor handles these differences in two main areas:

• API Support — whether a called API is supported on a given data source.
• Database Dialect — when using the Fluent API, the same API generates different commands or syntax for different databases.

Highlights

If you want to change these differences, you are welcome to participate in the project and contribute your improvements.

Datasource Support Overview

dbVisitor has designed 4 types of APIs under a unified kernel architecture: Programmatic API, Declarative API, Fluent API, and Mapper File. Among them, JdbcTemplate, Annotations, and Mapper File are available on all data sources.

dbVisitor has intelligent dialect inference — it automatically identifies the target database type from the JDBC URL and configures the optimal dialect, typically requiring no manual configuration. When explicit specification is needed, both dialect aliases (e.g., mysql) and fully qualified dialect class names are supported.

The table below summarizes API support and dialect feature differences for each data source. Column meanings:

• Fluent API — includes LambdaTemplate, BaseMapper, Object Mapping, and ResultSet Mapping; all four have the same support status
• Duplicate Key — all data sources support standard insert (Into); this column only marks additionally supported conflict strategies (details)
• Pagination — whether the dialect implements the PageSqlDialect interface
• Sequence — whether the dialect implements the SeqSqlDialect interface
• Vector — whether the dialect implements the VectorSqlDialect interface (details)
• Null Ordering — whether the dialect overrides the orderByNulls method

Datasource	Config Key	Fluent API	Batch	Stored Proc	Auto-Increment	Pagination	Duplicate Key	Sequence	Vector	Null Ordering
MySQL	mysql	✅	✅	✅	✅	✅	Ignore Update			✅
MariaDB	mariadb	✅	✅	✅	✅	✅	Ignore Update			✅
PostgreSQL	postgresql	✅	✅	✅	✅	✅	Ignore Update¹	✅	✅
KingbaseES	kingbase	✅	✅	✅	✅	✅	Ignore Update¹	✅	✅
Oracle	oracle	✅	✅	✅	✅	✅	Ignore¹ Update¹
DM (Dameng)	dm	✅	✅	✅	✅	✅	Ignore¹
SQL Server	sqlserver/ jtds	✅	✅	✅	✅	✅
SQL Server (jTDS)	jtds	✅	✅	✅	✅	✅
DB2	db2	✅	✅	✅	✅	✅
H2	h2	✅	✅	✅	✅	✅		✅
Apache Derby	derby	✅	✅	✅	✅	✅
HSQL	hsql	✅	✅	✅	✅	✅
Hive	hive	✅	✅	✅	✅	⚠️
Apache Impala	impala	✅	✅	✅	✅	✅
IBM Informix	informix	✅	✅	✅	✅	✅
SQLite	sqlite	✅	✅	✅	✅	✅
XuGu	xugu	✅	✅	✅	✅	✅
Redis	—	❌	❌	❌	❌
MongoDB	mongo	✅	❌	❌	✅	✅
ElasticSearch 6	elastic6	✅	❌	❌	✅	✅
ElasticSearch 7	elastic7	✅	❌	❌	✅	✅			✅
ElasticSearch 8	elastic8	✅	❌	❌	✅	✅			✅
Milvus	milvus	✅	❌	❌	❌	✅			✅

✅ Supported   ❌ Not supported

¹ Requires primary key

⚠️ Hive: Although it implements PageSqlDialect, both countSql and pageSql throw UnsupportedOperationException, making it effectively unusable.

JDBC Feature Support

For non-relational database drivers (Mongo, Elastic), dbVisitor implements the Statement.RETURN_GENERATED_KEYS feature. This means when using JdbcTemplate or Statement for insert operations, the generated _id can be automatically retrieved.

Non-Relational Datasource Guides

• Redis — supports 140+ commands, 5 data type operations; does not support Fluent API or Object Mapping
• MongoDB — full CRUD support, automatic ObjectId mapping, paginated queries; does not support batch and stored procedures
• ElasticSearch — full CRUD support, based on REST DSL; does not support batch and stored procedures
• Milvus — SQL-style syntax for vector database operations, full CRUD support, KNN nearest-neighbor search and range search; does not support batch and stored procedures

---

Insert Strategy Details

For databases that support duplicate key strategies, each dialect uses different underlying implementations:

Dialect	Ignore Implementation	Update Implementation
MySQL	INSERT IGNORE INTO ...	INSERT INTO ... ON DUPLICATE KEY UPDATE
PostgreSQL	INSERT INTO ... ON CONFLICT DO NOTHING	INSERT INTO ... ON CONFLICT(pk) DO UPDATE SET ...
Oracle	MERGE INTO ... WHEN NOT MATCHED THEN INSERT	MERGE INTO ... WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT
DM (Dameng)	INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX */ INTO ...	Not supported

Vector Query Support Details

Different database vector dialects support different distance metric functions and query methods:

Dialect	Query Method	Supported Distance Metrics
PostgreSQL	pgvector operators	L2 (<->), Cosine (<=>), Inner Product (<#>), etc.
Elastic 7	script_score scripts	l2norm, cosineSimilarity, dotProduct, l1norm
Elastic 8	Native kNN + script query	L2, COSINE, IP (native); l2norm, etc. (script fallback)
Milvus	Native vector operators	L2 (<->), Cosine (<=>), Inner Product (<#>), etc.

Custom Dialect

If the built-in dialects don't meet your needs, you can create a custom dialect by extending AbstractDialect and implementing the required interfaces. There are 5 dialect-related interfaces, with SqlDialect being the common base:

Interface	Responsibility
SqlDialect	Base interface — manages keyword lists, generates table/column/order-column names
ConditionSqlDialect	Condition-related SQL generation (e.g., LIKE statements)
InsertSqlDialect	Advanced INSERT statement generation (e.g., duplicate key strategy)
PageSqlDialect	Pagination statement generation (countSql + pageSql)
SeqSqlDialect	Sequence query statement generation

Tip

Extend the AbstractDialect abstract class and implement the PageSqlDialect interface to create a custom pagination dialect.

• countSql — generates the count SQL statement
• pageSql — generates the paginated SQL statement

Register a custom dialect

SqlDialectRegister.registerDialectAlias(JdbcUtils.MYSQL, MyDialect.class);