Spring data JPA specification

Many users wrote to us saying that now most developers adopt Spring Data JPA specification to write repository or mapper interface, they want to let our plugin to help them generating SQL statement according to JPA specification.

We find that it's the thing we're looking for to help user using Mybatis more efficient. And here it comes, hopes you guys enjoying it.

Domain class

The central interface in the Spring Data abstraction is Repository, it takes the domain class to manage as well as the ID type of the domain class as type arguments. So does the plugin need to know the domain class and ID type to generate SQL statement, and the ID type is optional if the plugin determines that ID is not necessary to generate SQL statement.

The plugin does not enforce you guys to use any name convention on mapper class nor to extend any specified interface, as you may want to write methods for different domain classes in the a mapper class. So the plugin uses many built-in policies to discover domain class and ID automatically (such as discovering from mapper method name, discovering from the return type of mapper method, discovering from method argument, etc.)

Configure domain class

The built-in policies work well in most cases, it may fail to determine the right domain class and ID type sometimes. You can always change them each time you invoke Create new JPA statement intention action and the plugin will remember your decision, so you don't have to choose the right one each time you need to generate a new SQL statement. For domain class, it's recorded per method in each mapper interface. For ID type, it's recorded per domain class.

select domain 1

You can find a domain class quickly by type few words and press ⌃ + Space on Mac and Ctrl + Space on Windows:

find domain

Other options

As you can see, there are some other options available when configuring domain class, let's see what do they do.

Use annotation statement

This option is available when the plugin determines that generated SQL statement is friendly to Java annotation. For example, this option is disabled if generated SQL statement needs a <foreach/> loop.

SQL statement will be generated as an annotation on mapper method when this option is selected otherwise it's generated as an XML tag in mapper XML. This option selected as default because it's always a better idea to use annotation for a simple statement, furthermore, the plugin supports Annotation Folding to make annotation statement cleaner and more readable.

You can quickly switch it by pressing ⌥ + a on Mac and Alt + a on Windows.

Use explicit columns

The plugin uses * for select statement which requires table columns as default. You can select this option to enforce the plugin to use explicit column names instead of *.

You can quickly switch it by pressing ⌥ + e on Mac and Alt + e on Windows.

Use backquote to wrap column name

A table may contain column names which are conflict with SQL keywords, and you need to wrap these column names with a backquote. You can use this option to do that, and the decision will be remembered for each domain class so that you don't need to select it again and again.

This option is available only when 'Use explicit columns' is selected. This option is unselected as default to avoid unnecessary verbose information.

You can quickly switch it by pressing ⌥ + b on Mac and Alt + b on Windows.

CRUD methods

Spring data uses CrudRepository to provide generic CRUD operations on a repository for a specific type, and the plugin does generate SQL statement for CRUD methods as well.

MethodDescription
save(User user)Saves a given user
batchSave(Collection<User>)Saves a collection of users
exists(int id)Checks whether a user with the given id exists
findOne(int id)Finds a single user by id
find(int id)Finds a user(s) by id
batchFind(Collection<Integer> ids)Finds users by a collection of id
findAll()Finds all users
count(int id)Returns the number of users by matching the given id
batchCount(Collection<Integer> ids)Returns the number of users by matching a collection of id
countAll()Returns the number of users available
delete(int id)Deletes user(s) by id
batchDelete(Collection<Integer> id)Deletes users by matching a collection of id
deleteAll()Deletes all users
saveSelective(User user)Saves a user by the fields which are not null
findSelective(User query)Finds user(s) by matching the fields which are not null
deleteSelective(User query)Deletes user(s) by matching the fields which are not null
updateSelective(User query)Updates a user fields which are not null by matching id

You can name the CRUD methods like this if you want to write methods for different domain classes in the same mapper class:

Method
saveUser(User user)
batchSaveUser(Collection<User>)
existsUser(int id)
findOneUser(int id)
findUser(int id)
batchFindUser(Collection<Integer> ids)
findAllUser()
countUser(int id)
batchCountUser(Collection<Integer> ids)
countAllUser()
deleteUser(int id)
batchDeleteUser(Collection<Integer> id)
deleteAllUser()
saveUserSelective(User user)
findUserSelective(User query)
deleteUserSelective(User query)
updateUserSelective(User query)

Query creation

You can find how the query creation mechanism works at Query methods. The following table describes the keywords supported for JPA and what a method containing that keyword translates to:

KeywordSampleSQL snippet
AndfindByLastnameAndFirstname... WHERE lastname = #{lastname} AND firstname = #{firstname}
OrfindByLastnameOrFirstname… WHERE lastname = #{lastname} OR firstname = #{firstname}
Is,EqualsfindByFirstname,findByFirstnameIs… WHERE firstname = #{firstname}
BetweenfindByStartDateBetween… WHERE startDate BETWEEN #{minStartDate} AND #{maxStartDate}
LessThanfindByAgeLessThan… WHERE age < #{maxAge}
LessThanEqualfindByAgeLessThanEqual… WHERE age <= #{maxAge}
GreaterThanfindByAgeGreaterThan… WHERE age > #{minAge}
GreaterThanEqualfindByAgeGreaterThanEqual… WHERE age >= #{minAge}
AfterfindByStartDateAfter… WHERE startDate > #{minStartDate}
BeforefindByStartDateBefore… WHERE startDate < #{maxStartDate}
IsNullfindByAgeIsNull… WHERE age IS NULL
IsNotNull,NotNullfindByAge(Is)NotNull… WHERE age IS NOT NULL
LikefindByFirstnameLike… WHERE firstname LIKE CONCAT('%', #{firstnamePattern}, '%')"
NotLikefindByFirstnameNotLike… WHERE firstname NOT LIKE CONCAT('%', #{firstnamePattern}, '%')"
StartingWithfindByFirstnameStartingWith… WHERE firstname LIKE CONCAT(#{firstnamePrefix}, '%')
EndingWithfindByFirstnameEndingWith… WHERE firstname LIKE CONCAT('%', #{firstnameSuffix})
ContainingfindByFirstnameContaining… WHERE firstname LIKE CONCAT('%', #{firstnamePattern}, '%')
OrderByfindByAgeOrderByLastnameDesc… WHERE age = #{age} ORDER BY lastname DESC
NotfindByLastnameNot… WHERE lastname <> #{lastname}
InfindByAgeIn(Collection<Integer> ages)… WHERE age IN <foreach collection="collection" item="v" open="(" close=")" separator=","> #{v} </foreach>
NotInfindByAgeNotIn(Collection<Integer> ages)… WHERE age NOT IN <foreach collection="collection" item="v" open="(" close=")" separator=","> #{v} </foreach>
TruefindByActiveTrue… WHERE active = true
FalsefindByActiveFalse… WHERE active = false
IgnoreCasefindByFirstnameIgnoreCase… where UPPER(firstame) = UPPER(#{firstname})

All keywords are available for methods whose names start with find, read, get, query, count, exists, delete, remove.

You can also append domain class name to all the methods mentioned above if you want to write methods for different domain classes in the a mapper class. Such as deleteUserByActiveTrue, findUserByFirstnameNotLike, countUserByAgeGreaterThanEqual, etc.

Auto completion

Completion icon

The plugin uses different icons for auto-completion elements for CRUD methods and query creation to make it easier to identify them. The icon used for CRUD methods is Mybatis Logo and the icon used for query creation is Spring Data 1.

CRUD methods

You can get auto-completion for CRUD method like this:

CRUD Completion

You don't have to create the parameters corresponding to the CRUD method, it will be done automatically when you select one element from auto-completion and this is how it works:

generate-parameter-crud-2.gif

Query creation

You can get auto-completion for query creation like this:

Query Creation Completion

You don't have to create the parameters corresponding to the query creation too, it will be done automatically when you generate the SQL statement and this is how it works:

generate-parameter-creation-2.gif

Codes Magic © 2013-2018 · Terms