Hello friends

Recently, I have used a framework called Mybatis to embed SQL commands for processing as well as create dynamic queries to run the program in (Java) Spring Boot, I would like to share my experience learning Mybatis through this article.

With the purpose of testing Mybatis, the problem will be directed so that we go through all the basic features of Mybatis, not related to any actual problem. Some of the features I would like to use are as follows:

• Write RawSQL to handle the incoming java calls and get the results
• Write SQL Builder form to handle incoming java calls and get the results
• Write SQL for basic syntax: SELECT, INSERT, UPDATE, DELETE
• The problem is to write a program to store user and items data.

So let me start.

0. Environment

My machine’s environment when doing the following:

• OS: MacOS
• IDE: SpringToolSuite4 (Eclipse)

1. Install

1.1. Project initialization

We use Spring Initializr to initialize, very convenient. https://start.spring.io/

The options are as follows

We will use Mybatis so we will choose a dependence called Mybatis.

Regarding the database, I choose Mysql because I am used to manipulating this DB.

Select Generate, we get the zip file, extract it and import it into the IDE, we have the following structure

In which note one parameter is that the mybatis we use has a version of 2.1.3 and this is a specially designed version used with Spring Boot, not the popular version (Currently 3.5.5 )

We also create 2 packages available to store the mybatis classes that will be used later:

• src / main / java / com / demo / mybatis / mapper
• src / main / java / com / demo / mybatis / model

1.2. Database structure

We’ll need a ready-to-use database for the queries to call and execute. We will structure the database as follows:

Create DB mysql with docker

docker-compose.yml

We build a database with the user, password, and port information as above. Start with docker-compose up -d

Table structure

(Access the database command line with mysql -h 0.0.0.0 -P 33061 -u demo demo -p )

In this post I tested with a basic user table with the user’s attributes as name (name) and date of birth (birthday). Then the items table contains the foreign key to the users table to test the relationship feature in mybatis.

1.3. Install plugin Mybatis Generator

1.3.1. Download from Marketplace

We can choose to write the necessary classes by ourselves, but another option is to use the Mybatis Generator , which is a very useful tool for the creation of necessary classes using Mybatis.

Homepage: http://mybatis.org/generator/

Mybatis Generator has the ability to look up specified tables and generate classes related to that database, thereby reducing implementation effort a lot.

You can download with the command line, in this article I download as an eclipse plugin as follows: look up from the Eclipse Marketplace (Help -> Eclipse Marketplace) with the keyword Mybatis, choose to download Mybatis Generator 1.4.0

After downloading, we create generatorConfig.xml file to specify the necessary settings for the Generator. I created with the following content:

src / main / resources / generatorConfig.xml

Inside :

• connectionURL : url containing information connecting to the database
• userId : the user name for the database
• password : password for the above user
• javaModelGenerator : specify generate Model classes (User, Item)
• javaClientGenerator : specify generate Mapper and Support classes
• table : specify the table to generate

1.3.2 Generator (Run Configuration)

Run -> Run Configuration -> Mybatis Generator (New)

Here we specify the Configuration File to the previous config file in 1.3.1.

2. Write the program

2.1.Run Generator

Try Run with the above configuration, we get the log in the Console as follows:

After finishing running, we get the following classes generated:

Mybatis Generator provides us with the following 3 basic classes:

• Models: Class that models the results as well as parameters of SQL statements, usually equivalent to a table in the database.
• Mapper: is the class that stores the queries that we use to query the database
• The Support: write handling support with Mapper by providing the instance variables corresponding to the table structure

We will look at the contents of these files:

2.1.1 Models (Users and Items)

src / main / java / com / demo / mybatis / model / Users.java

We can see that Mybatis Generator generated all the fields the users table contained (id, name, birthday) and mapped them to the nearest data type (Integer, Date, String).

Each field has an annotation that marks how well they are generated.

2.1.2. Mapper

Mybatis generate for us more than 2 dozen utility functions, we will go through some initial functions

• selectList : contains a list of fields corresponding to the corresponding model.
• long count () : function counts the list based on the select command given parameter. Then we can count the number of records that satisfy the condition by calling the ItemsMapper.count (…) command.
• int delete () : the function deletes the record based on a condition passed in a parameter
• int insert () : similar to the two above functions with the insert command

2.1.3. support

Inside each Support class there is a subclass representing the table that points to ( Users ), this subclass contains properties corresponding to the fields of the table it points to.

The parent Support class ( UsersDynamicSqlSupport ) contains an instance of that subclass, and each other property is a reference to the property of that instance.

We will actually use the above properties later.

2.2 Writing test treatments using mybatis

2.2.1.Write custom Model and Mapper for relationship between Items and Users

This paragraph can be a bit advanced, above I used Mybatis Generator to generate the models and mapper for each table in the database.

However, I want to use a mapper of the form that can be used with a relationship between the tables, and requires joining . This feature, Mybatis Generator still does not support creating a suitable class and mapper.

https://mybatis.org/mybatis-dynamic-sql/docs/select.html

Therefore, I created a new one

2.2.1.1.ItemsUsers (Model)

src / main / java / com / demo / mybatis / model / ItemsUsers

These are the 4 properties that I want to get out after joining the 2 tables together.

2.2.1.2.ItemsUsersMapper.java

Similarly, we will create a Mapper containing a call to handle the relationship between the two tables. src / main / java / com / demo / mybatis / mapper / ItemsUsersMapper

2.2.1.3.ItemsUsersMapper.xml

src / main / java / com / demo / mybatis / mapper / ItemsUsersMapper.xml

Here we have created a pure query containing the join command. Spring can detect and attach this xml with its corresponding java mapper.

Note that in the query we need to declare the correct id with the function name in the java mapper and the correct return type.

( Although the return type is List, mybatis still accepts the resultType declaration, which is not an explicit type of List, but an element. )

2.2.2 Create a playground

The Playground I mean here is the class used exclusively for testing, trying out new handlers for the first time. We will write basic handling with the model in this playground.

src / main / java / com / demo / mybatis / MybatisPlayground.java

We let the parameter pass in to initialize the mapper to be used, while the mapper will be initialized for Spring to handle outside.

For the purpose of testing using mybatis, I create functions that go through basic queries such as delete, select, update, insert

One interesting point when using mybatis is that I was able to use lambda type handling for these function calls.

At this point, the basic processing is complete, then we will fix it at the program’s initialization process to call these processes.

2.2.3 Editing MybatisApplication

This is the clue to start the program, currently only Spring startup. We will edit to call the mybatis handler tests above.

src / main / java / com / demo / mybatis / MybatisApplication.java

The plugin mybatis at the beginning of the load is not simply mybatis but the version that has been processed for the utility with Spring. With this utility, we just need to declare the Autowire mapper annotation to be detected by Spring and initialized at the start of the program.

With the Bean annotation declaration and the function template as above, Spring will detect and call by itself, so we do not need to fix at main ()

Above we declared to call all the handlers that created playgrounds.

2.2.4.Write JUnit

We will write unit test with JUnit to check if the functions are working properly, as well as test if the Mapper with the Join type works properly.

src / test / java / com / demo / mybatis / MybatisApplicationTests.java

3. Run the program

We can run the program with Run Configuration with Project as mybatis and Main class com.demo.mybatis.MybatisApplication as shown in the picture.

we get results

or run with maven: ./mvnw spring-boot: run

After running, we get the inserted database structure:

Users table

Table of items

Next, we will run the test to see if the join command has been modeled into xml and the call runs properly. This time I’ll run with Maven

Result

-> Running with 0 errors, call handling seems to work fine!

4.Conclusion

At this point, I have shared about how to use Mybatis and Mybatis Generator to embed SQL commands in processing as well as call dynamic SQL queries with Spring Boot framework.

Hope to be helpful for you with related professionalism.

Source code: https://github.com/mytv1/mybatis-sample

Reference: https://qiita.com/kazuki43zoo/items/ea79e206d7c2e990e478

Over.