2. Quick Lookup

Note

This page skips the API overview and organizes links by usage scenarios for quick lookup.

Insert,Delete,Update

• Use raw SQL Statements.
• Use annotations @Insert, @Delete, @Update.
• Use the Builder API to Insert, Delete, Update.
• Use the Common Mapper interface.
• In a Mapper file, use <insert> to insert data.
• In a Mapper file, use <selectKey> to handle auto-increment IDs during insert.
• In a Mapper file, use <update> / <delete> to update or delete data.

Basic Query

• Use SQL statements to Query Result Set, Query Object, Query Key-Value Pairs, Query Value/List, Stream Query
• Execute statements and Multiple ResultSet.
• Define queries on interfaces with @Query.
• Use the Builder API to Fetch one object, List, Count.
• Use the Builder API for Group by and Order by.
• In a Mapper file, use <select> to query and <sql> to define fragments.

Arguments

• In dynamic SQL, pass parameters via SQL text substitution (assess injection risks yourself).
• Pass parameters by Positional or using Named positional.
• Pass parameters by Named and further access values with OGNL.
• Use SqlArgSource or PreparedStatement for parameters.
• Use Rules to make parameters dynamic in SQL.

Pagination

• Use pagination in the Builder API.
• In Mapper interfaces, add paging params to @Query methods for pagination.
• Paginate via the Common Mapper (includes sorting and null ordering).
• Use Session queryStatement / pageStatement overloads for pagination.
• After binding Mapper interfaces to XML, use a Page object for pagination.

Dynamic SQL

• Rules Manual: Dynamic SQL Rules, Nested Rules, Result Processing, etc.
• Inject predefined SQL fragments via MACRO rules in SQL statements (assess SQL injection security risks yourself).
• Build SQL text via IFTEXT rules or ${...} syntax in SQL statements (assess SQL injection security risks yourself).
• Enhance SQL statements via AND, OR, SET, CASE rules.
• Use IN rule to automatically generate corresponding (?,?,?,?) in SQL statements based on collection parameter size.
• Use IFAND, IFOR, IFSET, IFIN rules to control rule validity via a condition parameter.
• Rules can also process a segment of SQL not just a parameter.
• Nested rules: @{case, ... @{and, ...}} enable logical processing capabilities in SQL via nesting.
• Use <if>, <choose>, <when>, <otherwise> tags in Mapper File for conditional judgment.
• Use <trim>, <where>, <set> tags in Mapper File to enhance specific SQL statement generation.
• Use <foreach> tag in Mapper File to handle loop requirements.

Object mapping

• Use @Table and @Column annotations for mapping.
• Use camel case to auto-map properties to columns.
• Handle case sensitivity and keyword columns.
• Control column participation via write policy (insert/update).
• Choose primary key strategies via @Column keyType (Key Generators).
• In Mapper files, use <entity> to describe mappings in XML.
• In Mapper files, <resultMap> is similar to <entity> but only for query result mapping.
• Use auto-mapping to simplify configuration.
• With the builder, Statement Templates decide generated SQL elements.

Stored procedures

• Use SQL to Stored Procedures Call.
• Set parameter mode to OUT.
• Use mode cursor to read cursor arguments.
• Use @Call to execute procedures.

Executing SQL

• Batches SQL statements.
• Load a Scripts file.
• Execute any statement with @Execute.
• In Mapper files, use <execute> for arbitrary SQL.

Results

• Use List/Map across APIs to collect query results.
• Use RowMapper to map each row:
  • ColumnMapRowMapper converts rows to Map and returns List/Map.
  • SingleColumnRowMapper handles single-column results into a List.
  • BeanMappingRowMapper maps rows to beans.
  • MapMappingRowMapper maps rows to maps.
• Use ResultSetExtractor to customize ResultSet handling.
  • dbVisitor ResultSetExtractor implementations used internally.
• Use RowCallbackHandler to process rows without collecting them.
  • Example: MySQL streaming large tables.

Type Handler

• Specify a type handler via the TypeHandler option in SQL parameters.
• Use the @Column TypeHandler option for abstract types, enums, JSON serialization.
• dbVisitor provides many handlers; check built-ins first:
  • Boolean, Numeric, Char/String, Datetime, Byte[]
• Map enums via EnumOfValue or EnumOfCode.
• The serialization auto-detects Jackson, Gson, Fastjson, Fastjson2 in that order.
• With JTS on the classpath, dbVisitor can handle WKB/WKT geospatial data.
• dbVisitor also supports InputStream/Reader and Array types.

Redis support

• See the 140+ Redis commands supported by dbVisitor: Commands.
• Learn how dbVisitor handles Redis data types (String, Hash, List, Set, Sorted Set).
• Use JdbcTemplate to Query Operations or Data Writes.
• Use @Insert, @Update, @Delete on Mapper interfaces for Annotation-driven Redis operations.
• Configure commands via tags in Mapper File.

MongoDB support

• See MongoDB commands supported by dbVisitor: Commands.
• Use JdbcTemplate to Query Operations or Data Writes.
• Use Builder API.
• Use Common Mapper.
• On Mapper interfaces, use @Insert, @Update, @Delete for Annotation-driven operations.
• Configure commands via tags in Mapper File.

ElasticSearch support

• See ElasticSearch commands supported by dbVisitor: Commands.
• Use JdbcTemplate to Query Operations or Data Writes.
• Use Builder API.
• Use Common Mapper.
• On Mapper interfaces, use @Insert, @Update, @Delete for Annotation-driven operations.
• Configure commands via tags in Mapper File.

Database transactions

• Spring projects: use Spring Annotations.
• Solon projects: use Solon Annotations.
• Guice and Hasor: use dbVisitor @Transactional.
• In plain Java programs without framework proxies, create a proxy with TransactionHelper.support() and then use @Transactional.
• To wrap a local code block, use transaction templates.
• To control begin/commit/rollBack manually, use programmatic transactions.

Framework integrations

• Use dbvisitor-guice with Google Guice.
• Use dbvisitor-spring with Spring / Spring Boot.
• Use dbvisitor-solon with Solon.
• Use dbvisitor-hasor with Hasor.