SimpleJdbcMapper

CI SimpleJdbcMapper coverage maven-central License

A library that simplifies Spring JdbcTemplate/JdbcClient CRUD operations and relationship queries by making them less verbose. Use it as needed and keep using JdbcTemplate/JdbcClient for other functionality.

Just by annotating the models that you would use with JdbcTemplate/JdbcClient, you get single-line CRUD and a fluent API to populate relationships from your custom queries.

New Feature Alert: Relationships can be populated from custom queries.

github | Javadoc | Demo Application

Features

  1. One liners for CRUD
  2. Simple configuration similar to JdbcTemplate/JdbClient configuration.
  3. A fluent style API to populate relationships from mutli-table queries.
  4. Method to construct SQL for the mapped objects that can be used with Spring row mappers like BeanPropertyRowMapper, SimplePropertyRowMapper or the framework's EntityRowMapper which avoids writing custom row mappers.
  5. Auto assign properties
    • auto assign audited by (created by, updated by) by providing a Supplier
    • auto assign audited on (created on, updated on) by providing a Supplier
    • optimistic locking feature for updates using versioning.
  6. Transaction management is the same as in Spring applications since the library is using JdbcTemplate behind the scenes.
  7. To log the SQL statements use the same SQL logging configurations as Spring. See the logging section further below.
  8. Tests are run against PostgreSQL, MySQL, Oracle, SQLServer. Should work with other databases. CI SimpleJdbcMapper
  9. Only dependency is Spring JDBC libraries. No other external dependencies.

Maven coordinates

maven-central

Example code

 //@Table annotation is required
 @Table(name="product")
 public class Product {
   /*
    The @Id annotation is required. It can be of any type.
    @Id(type=IdType.AUTO_GENERATED) - Use for ids which are auto generated by the database. Id value will 
                                      be assigned to the object on insert.
    @Id - The id value will have to be manually set before invoking insert().                                                            
   */
   @Id(type=IdType.AUTO_GENERATED)
   private Integer id; 
           
   // The 'name' property will map to 'product_name' column in database table.
   @Column(name="product_name")            
   private String name;
   
   // will map to column 'sku'
   @Column
   private string sku;                 
   
   // will map to column 'available_date' by default using camel case to underscore case naming convention
   @Column
   private LocalDateTime availableDate;    
   
   // will map to 'price' column by default
   @Column
   private Double price;                   
   
   // No annotations for this property so excluded from inserts/updates/queries etc
   private String someNonDatabaseProperty; 
 
   ...
 }

 ...
 @Autowired
 private SimpleJdbcMapper sjm;
...

 Product product = new Product();
 product.setName("some product name");
 product.setSku("sku1");
 product.setPrice(10.25);
 product.setAvailableDate(LocalDateTime.now());
 
 // because id type is AUTO_GENERATED its value will be assigned on insert.
 sjm.insert(product); 

 // find by id
 product = sjm.findById(Product.class, product.getId());
 
 // update product
 product.setPrice(11.50);
 sjm.update(product);
 
 // updateSpecificProperties() updates only the specified properties passed as arguments.
 // Will issue an SQL update only for price.
 product.setPrice(12.50);
 sjm.updateSpecificProperties(product, "price");
 
 // find all
 List<Product> products = sjm.findAll(Product.class);
 
 // find all with sort (ORDER BY clause)
List<Product> products = sjm.findAll(Product.class, new SortBy("availabeDate"), new SortBy("price", "DESC"));
 
 // delete by object
 sjm.delete(product);
 
 // delete by id
 sjm.deleteById(Product.class, 5);
 
 /*
For custom queries which retrieve mapped objects use getEntitySqlColumns() to get the columns for the sql 
to work with EntityRowMapper (see its javadoc). Note in this case the 'name' property is mapped to the 'product_name' column.
 */
 String sql = "SELECT " + sjm.getEntitySqlColumns(Product.class) +  " FROM product WHERE product_name = ?";
 
 // Using Spring's JdbcClient api for the above sql.
 List<Product> products = sjm.getJdbcClient().sql(sql)
                                           .param("someProductName")
                                           .query(sjm.newEntityRowMapper(Product.class))
                                           .list();
 // Using Spring's JdbcTemplate api for the above sql
 List<Product> products = sjm.getJdbcTemplate().query(sql, sjm.newEntityRowMapper(Product.class), "someProductName");
 
 // find by a property value
 List<Product> products = sjm.findByPropertyValue(Product.class, "sku", "some sku#");
 
 // find by multiple property values
 String[] skus = { "sku1", "sku2"};
 List<Product> products = sjm.findByPropertyValues(Product.class, "sku", Array.asList(skus));
 
 // Accessing the underlying JdbcClient, JdbcTemplate and NamedParameterJdbcTemplate. 
 JdbcClient jdbcClient = sjm.getJdbcClient();
 JdbcTemplate jdbcTemplate = sjm.getJdbcTemplate();
 NamedParameterJdbcTemplate namedParameterJdbcTemplate = sjm.getNamedParameterJdbcTemplate();
 
 // see relationships section on how to populate relationships with custom queries.
 
 // The demo application (link provided at top) is easy to install and run where you can see example code in action.
 
 // See logging section for details to view the SQL
 
 // See troubleshooting section if you have issues.
 
 // Thats all folks. Happy coding!!!

JDK and Spring version requirements

JDK 21+

SpringBoot 3.2.3+ or Spring framework 6.1.4+

Spring bean configuration for SimpleJdbcMapper

SimpleJdbcMapper should always be prepared in a Spring application context and given to services as a bean reference. It maintains state for example it caches insert/update SQL etc.

Note: An instance of SimpleJdbcMapper is thread safe once configured.

Examples for different databases below. Depending on the versions of springboot/database/driver, there could be some differences to the properties. The properties are same as datasource properties used to configure Spring JdbcClient/JdbcTemplate.

PostgreSQL

# application.properties
spring.datasource.jdbc-url=jdbc:postgresql://HOST:PORT/SCHEMA_NAME
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.Driver
...

  // DataSource properties are read from application.properties.
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource")
  public DataSource sqlDataSource() {
    return DataSourceBuilder.create().build();
  }

 @Bean
  public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
    return new SimpleJdbcMapper(dataSource, SCHEMA_NAME);
  }

MySQL

# application.properties
spring.datasource.jdbc-url=jdbc:mysql://HOST:PORT/DATABASE_NAME
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
...

  // DataSource properties are read from application.properties.
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource")
  public DataSource sqlDataSource() {
    return DataSourceBuilder.create().build();
  }

 @Bean
  public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
    return new SimpleJdbcMapper(dataSource, null, DATABASE_NAME); // For mysql, database is synonymous with catalog
  }

Oracle

# application.properties
spring.datasource.jdbc-url=jdbc:oracle:thin:@HOST:PORT/SERVICE_NAME
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
...

  // DataSource properties are read from application.properties.
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource")
  public DataSource sqlDataSource() {
    return DataSourceBuilder.create().build();
  }
  
 @Bean
  public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {   
   return new SimpleJdbcMapper(dataSource, SCHEMA_NAME);
  }

SQLServer

# application.properties
spring.datasource.jdbc-url=jdbc:sqlserver://HOST:PORT;databaseName=CATALOG_NAME;encrypt=true;trustServerCertificate=true;
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
...

  // DataSource properties are read from application.properties.
  @Bean
  @ConfigurationProperties(prefix = "spring.datasource")
  public DataSource sqlDataSource() {
    return DataSourceBuilder.create().build();
  }
  
 @Bean
  public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
    return new SimpleJdbcMapper(dataSource, SCHEMA_NAME);
  }

Annotations

@Table

Required class level annotation. The table or view should exist in database. The schema/catalog attributes set with @Table will override corresponding values on the SimpleJdbcMapper() constructor (if any). Note that table names with spaces are not supported.

Multiple classes can be mapped to the same table. For example if you have a table with large number of columns you could have 2 objects mapped to it. One with a few commonly used columns and another with all the columns.


@Table(name="product")
class Product {
  ...
}

@Table(name="product", schema="someSchemaName") 
class Product {
  ...
}

@Table(name="product", catalog="someCatalogName")  // for mysql, database name is synonymous with catalog name 
class Product {
  ...
}

@Table(name="product", catalog="someCatalogName", schema="someSchemaName")
class Product {
  ...
}

@Id

The id property can be of any java type. @Id can only be mapped to a single database column. Multi-column ids are not supported.

There are 2 forms of usage for this.

  • auto generated id usage

@Table(name="product")
class Product {
 @Id(type=IdType.AUTO_GENERATED)
 private Integer productId;
  ...
}

After a successful insert() operation the productId property will be populated with the generated id.

  • NON auto generated id usage

@Table(name="customer")
class Customer {
 @Id
 private Integer id;
  ...
}

In this case you will have to manually set the id value before invoking insert()

@Column

Properties that need be persisted to the database will need @Column annotation unless the property is already annotated with one of the other annotations (@Id, @Version, @CreatedOn @CreatedBy @UpdatedOn @UpdatedBy). @Column can be used along with the other annotations to map a property to a non-default column name. The default column name is camel case property name converted to underscore case name (e.g., property 'lastName' maps to column 'last_name' by default).

@Column This will map the property to a column using the default naming convention of camel case to underscore case. For example property 'lastName' will map to column 'last_name' by default.

@Column(name="somecolumnname") This will map the property to the column specified by the 'name' attribute. Note that column names with spaces are not supported.

@Column(sqlType = somesqltype)
SimpleJdbcMapper tries to infer the correct SQL type from the Java types but some times it cannot (mostly byte[] and database driver specific java types). In these cases explicitly declaring the SQL type is a best practice to ensure correctness, improve performance, and correctly handle NULL values.

To identify which properties in the mappings the SQL type is unknown do the following:

  • Turn on sql logging. (See logging section)
  • Issue an insert/update for the entity.
  • Check for 'SQL type unknown' in the logs. Below is example of the logs. Line 1 has the SQL type while line 2 the SQL type is unknown: 
   Setting SQL statement parameter value: column index 6, parameter value [true], value class [java.lang.Boolean], SQL type 16  
   Setting SQL statement parameter value: column index 7, parameter value [[B@56d742ad], value class [[B], SQL type unknown
  • For your specific database and database column type find the corresponding SQL type and assign it using @Column(sqlType = somesqltype).

@Version

This annotation is used for optimistic locking. It has to be of type Integer. Will be set to 1 when record is created and will be incremented on updates. On updates if the version is stale an OptimisticLockingException will be thrown. @Column annotation can be used with the property to map to a non-default column name.

@CreatedOn

If a Supplier is configured using simpleJdbcMapper.setRecordAuditedOnSupplier(), it will be used to to set the value for the @CreatedOn property. The type of the Supplier should match the type of the property. @Column annotation can also be used with the property to map to a non-default column name.

@UpdatedOn

If a Supplier is configured using simpleJdbcMapper.setRecordAuditedOnSupplier(), it will be used to to set the value for the @UpdatedOn property. The type of the Supplier should match the type of the property. @Column annotation can also be used with the property to map to a non-default column name.

@CreatedBy

If a Supplier is configured using simpleJdbcMapper.setRecordAuditedBySupplier(), it will be used to to set the value for the @CreatedBy property. The type of the Supplier should match the type of the property. @Column annotation can also be used with the property to map to a non-default column name.

@UpdatedBy If a Supplier is configured using simpleJdbcMapper.setRecordAuditedBySupplier(), it will be used to to set the value for the @UpdatedBy property. The type of the Supplier should match the type of the property. @Column annotation can also be used with the property to map to a non-default column name.

Annotation examples:


@Table(name="product")
class Product {

 @Id(type=IdType.AUTO_GENERATED)
 private Integer productId; 
 
 @Column(name="product_name")
 private String name;              // maps to product_name column
 
 @Column
 private String productDescription // defaults to column product_description 
 
 @CreatedOn 
 private LocalDateTime createdTimestamp;  // defaults to column name created_timestamp. 
                                          // If a Supplier is configured it will use the value from Supplier to populate this property. 
                                          // Make sure Supplier type and property type match
  
 @CreatedBy
 private String createdByUser;     // defaults to column created_by_user. 
                                   // If a Supplier is configured it will use the value from Supplier to populate this property. 
                                   // Make sure Supplier type and property type match
  
 @UpdatedOn
 private LocalDateTime updatedAt;  // defaults to column name updated_at.
                                   // If a Supplier is configured it will use the value from Supplier to populate this property. 
                                   // Make sure Supplier type and property type match
 
 @Column(name="last_update_user")
 @UpdatedBy
 private String updatedBy;         // maps to column last_update_user. 
                                   // If a Supplier is configured it will use the value from Supplier to populate this property. 
                                   // Make sure Supplier type and property type match
                                  
   
 @Version
 private Integer version;          // defaults to column version, 
                                   // Property type should be Integer. Used for optimistic locking.
                                   // Gets incremented every successful update.
  
}

Populating relationships from custom queries

An implementation of the relationship examples below and other features of the framework are available in the Demo Application. It has an embedded H2 database with tables populated. There is no configuration required and is simple to install and run.

1.ToMany relationship:

  • Order has many OrderLine
  /* 
     Define the multiple mapped entities you want to select. Make sure the table aliases match that in query.
     Mapped Class    | Table      |Alias
     ------------------------------------
     Order.class     | orders     | "o"
     OrderLine.class | order_line | "ol"
  */
  MultiEntity multiEntity = new MultiEntity().add(Order.class, "o").add(OrderLine.class, "ol");
  
  // Get the columns for your 'SELECT' using getMultiEntitySqlColumns().
  // Using java String blocks makes the queries more readable.
  String sql = """
      SELECT %s
      FROM orders o
      LEFT JOIN order_line ol ON  o.id = ol.order_id
      WHERE o.total_amount >= ?
      ORDER BY o.order_date DESC, ol.id
   """.formatted(sjm.getMultiEntitySqlColumns(multiEntity));
   
   // Use the framework ResultSetExtractor with JdbcTemplate to extract the results.
   RelationshipMapper relationshipMapper = sjm.getJdbcTemplate().query(sql, sjm.resultSetExtractor(multiEntity), someAmount); 
   
   // populate() does the processing and populates Order.orderLines and getList() returns the orders
   List<Order> orders = relationshipMapper.type(Order.class)
                                          .toMany(OrderLine.class)
                                          .joinOn("id", "orderId")
                                          .populate("orderLines")
                                          .getList(Order.class);
  1. The sql columns generated by getMultiEntitySqlColumns() and the ResultSetExtractor work together. The extractor expects columns to be in a specific order, so do not modify the sql columns string from getMultiEntitySqlColumns().
  2. Relationship works with the query results and information provided by the API. It does not access the database or use SimpleJdbcMapper.

2.Multiple relationships with one query (toOne and toMany)

Use Multi-entity processing to populate multiple relationships:

  • Order has many OrderLine
  • OrderLine has one Product
  // define your entities. The aliases should exactly match the aliases used in the query.
  MultiEntity multiEntity = new MultiEntity().add(Order.class, "o").add(OrderLine.class, "ol").add(Product.class,"p");
  
   // build your custom sql using the sql columns from sjm.getMultiEntitySqlColumns(multiEntity)
  String sql = """
      SELECT %s
      FROM orders o
      LEFT JOIN order_line ol ON  o.id = ol.order_id
      LEFT JOIN product p ON ol.product_id = p.id
      WHERE o.total_amount >= ? 
      ORDER BY o.order_date DESC, ol.id
      """.formatted(sjm.getMultiEntitySqlColumns(multiEntity));
      
  // Use JdbcTemplate with the framework extractor to execute the query and extract results
  RelationshipMapper relationshipMapper = sjm.getJdbcTemplate().query(sql, sjm.resultSetExtractor(multiEntity), someAmount);
  
  // populate property OrderLine.product using toOne() since its a toOne relationship. populate() method does the processing.
  relationshipMapper.type(OrderLine.class)
                    .toOne(Product.class)
                    .joinOn("productId", "id")
                    .populate("product");
  
  // populate Order.orderLines property and use getList() to return the orders
  List<Order> orders = relationshipMapper.type(Order.class)
                                          .toMany(OrderLine.class)
                                          .joinOn("id", "orderId")
                                          .populate("orderLines")
                                          .getList(Order.class)

3.ToMany relationship through an intermediate table (one side of many to many)

  • Employee has many Skill through intermediate table 'employee_skill'
  // Define the entities. The intermediate table employe_skill (in this case corresponds to EmpolyeeSkill class) needs to be selected also.
  MultiEntity multiEntity = new MultiEntity().add(Employee.class, "emp").add(EmployeeSkill.class, "es").add(Skill.class, "s");
  
   // build your custom sql using the sql columns from sjm.getMultiEntitySqlColumns(multiEntity)
  String sql = """
      SELECT %s
      FROM employee emp
      LEFT JOIN  employee_skill es ON emp.id = es.employee_id
      LEFT JOIN skill s ON es.skill_id = s.id
      ORDER BY emp.id, s.id
  """.formatted(sjm.getMultiEntitySqlColumns(multiEntity));
 
    // Use JdbcTemplate with the framework extractor to extract results for the entities.
    RelationshipMapper relationshipMapper = sjm.getJdbcTemplate().query(sql, sjm.resultSetExtractor(multiEntity));
    
    // populate employee.skills property. Here we are using toMany() with through(). 
    List<Employee> employees = relationshipMapper.type(Employee.class)
                                                 .toMany(Skill.class)
                                                 .through(EmployeeSkill.class, "employeeId", "skillId")
                                                 .populate("skills")
                                                 .getList(Employee.class);

4.Using multiple queries to populate relationships.

The Relationship api is agnostic of the source of the data. Results from multiple queries can be used to populate relationships.

  • Order paginated list - 1st query
  • OrderLine has one Product - 2nd query to get the corresponding OrderLine and Product for the orders

From the results of these 2 queries the relationships can be built.

  // The paginated query. Note the PAGINATED SYNTAX is different for different databases. Use the one for your database.
  String orderSql = """
      SELECT %s
      FROM orders
      ORDER BY orders.id
      OFFSET %d ROWS FETCH NEXT %d ROWS ONLY
      """.formatted(sjm.getEntitySqlColumns(Order.class), 0, 10);

  List<Order> orders = sjm.getJdbcTemplate().query(orderSql, sjm.newEntityRowMapper(Order.class));

  // get the order id list
  List<Integer> orderIdList = orders.stream().map(Order::getId).toList();

  // 2nd query. For IN clauses we have to use a named parameter
  MultiEntity multiEntity = new MultiEntity().add(OrderLine.class, "ol").add(Product.class, "p");
  String sql = """
      SELECT %s
      FROM order_line ol
      LEFT JOIN product p ON ol.product_id = p.id
      WHERE ol.order_id IN (:orderIdList)
      ORDER BY ol.id
      """.formatted(sjm.getMultiEntitySqlColumns(multiEntity));

  MapSqlParameterSource param = new MapSqlParameterSource().addValue("orderIdList", orderIdList);
  // Since the query has a named parameter we are using NamedParameterJdbcTemplate for this query
  RelationshipMapper relationshipMapper = sjm.getNamedParameterJdbcTemplate().query(sql, param, sjm.resultSetExtractor(multiEntity));

  // add orders from the first query to the relationshipMapper so that we can build a relationship from it.
  relationshipMapper.addEntityResult(Order.class, orders, "id");

  // The toOne relationship populates orderLine.product.
  relationshipMapper.type(OrderLine.class).toOne(Product.class).joinOn("productId", "id").populate("product");

  // The toMany relationship populates order.orderLines and getList() returns the orders
  orders = relationshipMapper.type(Order.class).toMany(OrderLine.class).joinOn("id", "orderId").populate("orderLines").getList(Order.class);

BLOB CLOB mapping

  • Binary large object database columns should be mapped to java type byte[].
    No other java type is supported. The 'sqlType' attribute of the @Column annotation with the following values are considered as Binary Large Objects by SimpleJdbcMapper:
    Types.BLOB
    Types.ARRAY
    Types.LONGVARBINARY
    Types.VARBINARY

Use the pertinent SQL type for your database and database column type.

  • Character large object database columns should be mapped to java type String. No other java types are supported.
    The 'sqlType' attribute of the @Column annotation with the following values are considered as Character Large Objects by SimpeJdbcMapper:
    Types.CLOB
    Types.NCLOB
    Types.LONGVARCHAR
    Types.LONGNVARCHAR

Use the pertinent SQL type for your database and database column type.

In both the cases above the whole object (image files etc) will be read into memory. For very large objects this could create memory issues and you may want to use InputStream/Reader. To use InputStream/Reader you will have to use JdbcTemplate directly since SimpleJdbcMapper does not support these.

Some BLOB/CLOB examples below. Keep in mind depending on the versions of the databases and database column types these could be different.

Postgres:

@Column(sqlType = Types.ARRAY) //mapped to a 'bytea' database column type
private byte[] image;

@Column(sqlType = Types.LONGVARCHAR) // mapped to a 'text' database column type
private String clobData;

MySql:

@Column(sqlType = Types.BLOB) // mapped to a 'blob' database column type
private byte[] image;

@Column(sqlType = Types.LONGVARCHAR) // mapped to a 'text' database column type
private String textData;

Oracle:

@Column(sqlType = Types.BLOB) // mapped to a 'blob' database column type
private byte[] image;

@Column(sqlType = Types.CLOB) // mapped to a 'clob' database column type
private String clobData;

SQL Server:

@Column(sqlType = Types.LONGVARBINARY) // mapped to a 'VARBINARY(MAX)' database column type
private byte[] image;

@Column(sqlType = Types.LONGVARCHAR) // mapped to a 'VARCHAR(MAX)' database column type
private String clobData;

Enum mapping

Enums should be mapped to a database column which stores strings. It uses the enum.name() to get the string value.

public enum StatusEnum {
    OPEN, CLOSED;
}
...
@Column
private StatusEnum status;  // Mapped to a String column in the database (e.g., value 'OPEN')
...

Configuration for auto assigning @CreatedBy, @UpdateBy, @CreatedOn, @UpdatedOn

@Bean
public SimpleJdbcMapper simpleJdbcMapper(DataSource dataSource) {
    SimpleJdbcMapper simpleJdbcMapper = new SimpleJdbcMapper(dataSource);
    // Provide your own custom Supplier. Make Sure the type returned by Supplier matches the type 
    // of the Property you are annotating. Generally 'audited by' is got from a thread local variable 
    // for example when using spring security.
    simpleJdbcMapper.setRecordAuditedBySupplier(() -> "tester");
    simpleJdbcMapper.setRecordAuditedOnSupplier(() -> LocalDateTime.now());
    return simpleJdbcMapper;
}

Accessing JdbcClient JdbcTemplate

 JdbcClient jdbcClient = sjm.getJdbcClient();
 JdbcTemplate jdbcTemplate = sjm.getJdbcTemplate();
 NamedParameterJdbcTemplate namedParameterJdbcTemplate = sjm.getNamedParameterJdbcTemplate();

There is no requirement that you have to use the underlying JdbcClient/JdbcTemplate for your custom queries. You can create your own JdbcClient/JdbcTemplate and use it.

Logging

Uses the same logging configurations as Spring. In application.properties:


# log the SQL
logging.level.org.springframework.jdbc.core.JdbcTemplate=TRACE

# need this to log the INSERT statements
logging.level.org.springframework.jdbc.core.simple.SimpleJdbcInsert=TRACE

# log the parameters of SQL statement
logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE

Limitations

  1. @Id can only be mapped to a single database column. Multi-column ids are not supported.
  2. No support for table/column names with spaces in them.
  3. Primitives not supported. Use the corresponding java wrapper classes in your mappings.

Use JdbcTemplate/JdbcClient directly to handle these cases.

Upgrading to 2.x from 1.x

The 2.x release has removed the dependency on database table column meta-data for mapping totally.

Difference from 1.x:

  1. SimpleJdbcMapper 2.x tries to infer the correct SQL type from the Java types but some times it cannot (Mostly byte[] and database driver specific java types). In these cases explicitly declaring the SQL type is a best practice to ensure correctness, improve performance, and correctly handle NULL values. See documentation on @Column(sqlType="somesqltype") and BLOB/CLOB mapping further above on how to figure out and set the SQL type value.
  2. Since 2.x does not use the database table column meta data, it cannot provide detailed messages on what went wrong with a mapping. Mapping issues will surface through sql errors thrown, which is similar to what happens when using JdbcTemplate/JdbcClient directly.
  3. Primitives not supported. Use corresponding java wrapper classes in your mappings

Generally the upgrade should be straight forward since API remains the same.

TroubleShooting

1.Connection issues:

Try to connect to the database using Spring JdbcClient or JdbcTemplate without the SimpleJdbcMapper and issue a simple query. The datasource configuration parameters are exactly the same.