Tips and Tricks

Owned by Samuel Githengi
Last updated: Apr 18, 2018
2 min read

This page covers a general section on how to perform some non-trivial functions

Use of Bind for like wildcard
Use bind for like statements with wildcards e.g to execute a query e.g  "select * from user where first_name like '%ken%'" use the below syntax where filter is the runtime property for filtering value

select * from user where 
 <bind name="name" value="'%' + filter + '%'" /> 
cm.first_name like #{name}




Avoid using ${condition} in mappers
Be careful with ${condition} as its not escaped. Instead use #{condition} which is escaped and uses prepared statements to execute. ${condition} should be escaped in code  before invoking myBatis mapper and should not used for user input as it can lead to sql injection.

Avoid using MyBatis row bound plugin 

Avoid using MyBatis row bound plugin, it limits data after retrieves all data from the database which is very expensive. Instead pass the limit and offset variables to the mapper and have the SQL query specify the limit and offsets to fetch a batch instead of fetching all records and filtering after retrieving all the records in application code.

Case insensitive search
Mybatis does not natively support case insensitive searches. For this you will have to implement custom mapper and cannot use generated Examples.
For case insensitive like match use postgres ilike instead of like in custom mappers.

For equals(=) operator the filter column clause and filter must be converted to the same case before comparison e.g select * from user where lower(name) =lower('Ken')

Update Selective and Update without selective
Update selective(by Example or primary key) updates only the properties that are not null. So only the fields in the model that are not null are updated. If you need to set some properties to null and save don't use updateSelective as the null properties will not be updated on the database. However update without selective updates all fields event even if they are null. Its also not good when you want to update only a few fields that are not null.


Query Postgres Jsonb/Json using ? operator

Postgres has ? operator that is used to search whether a string exist as a top-level key within a JSON object. However ? is used used in java JDBC to denote placeholder for a prepared statement, java JDBC will then inject the actual value for the placeholder at runtime when executing the statement.

So for one to use postgres ? operator one must use ?? so the clause is not parsed as placeholder for prepared statement. An example is shown below

select * from client where j.value ??  #{relationshipId,jdbcType=VARCHAR}