Shulou(Shulou.com)06/02 Report--

How to integrate and use MyBatis in SpringBoot? for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Mybatis.type-aliases-package=com.neo.modelspring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=truespring.datasource.username=rootspring.datasource.password=rootspring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Parameter transfer mode

@ Delete ("DELETE FROM users WHERE id = # {id}") void delete (Long id)

If your mapping method has multiple parameters, this annotation can be used on the parameters of the mapping method to give them a custom name. If the custom name is not given, the multi-parameters are prefixed with "param", followed by their parameter positions as parameter aliases. For example, # {param1}, # {param2}, this is the default value. If the annotation is @ Param ("person"), then the parameter will be named # {person}.

@ Select ("SELECT * FROM users WHERE user_sex = # {user_sex}") List getListByUserSex (@ Param ("user_sex") String userSex)

When you need to pass multiple parameters, consider using Map

@ Select ("SELECT * FROM users WHERE username=# {username} AND user_sex = # {user_sex}") List getListByNameAndSex (Map map)

The most common way to use it is to use objects directly:

@ Insert ("INSERT INTO users (userName,passWord,user_sex) VALUES (# {userName}, # {passWord}, # {userSex})") void insert (User user)

Working with object

Use Map

Use @ Param

Let's start with a look at how to pass parameters to SQL using the annotated version of MyBatis.

Direct use

Introduction to annotations

The biggest feature of the annotated version is that the specific SQL file needs to be written in the Mapper class, which cancels the XML configuration of Mapper. When introducing the parameters above, tags such as @ Select and @ Delete have been used. This is the annotation provided by MyBatis to replace its XML file configuration, which we will describe one by one below.

@ Select comment

/ / @ Select is mainly used when querying, which is used in all queries as follows: @ Select ("SELECT * FROM users WHERE user_sex = # {user_sex}") List getListByUserSex (@ Param ("user_sex") String userSex)

@ Insert comment

/ / @ Insert, used when inserting the database. Passing the entity class directly will automatically resolve the attribute to the corresponding value, as shown in the following example: @ Insert ("INSERT INTO users (userName,passWord,user_sex) VALUES (# {userName}, # {passWord}, # {userSex})") void insert (User user)

@ Update comment

/ / @ Update, all update operations SQL can use @ Update. @ Update ("UPDATE users SET userName=# {userName}, nick_name=# {nickName} WHERE id = # {id}") void update (UserEntity user)

@ Delete comment

/ / @ Delete handles data deletion. @ Delete ("DELETE FROM users WHERE id = # {id}") void delete (Long id)

The above are commonly used in the project to add, delete, change, check, but sometimes we have some special scenarios to deal with, such as the query object return value attribute name and field name are not consistent, or enumeration is used in the object properties. We expect that the returned results of the query will automatically convert this field to the corresponding type, and MyBatis provides two other annotations to support: @ Results and @ Result.

@ Results and @ Result annotations;-these two annotations are used together to convert the values queried in the database into specific fields, modify the returned result set, and correspond to the associated entity class attributes and database fields one by one. If the entity class attributes are consistent with the database attribute names, you do not need this attribute to modify them. Examples are as follows:

Select ("SELECT * FROM users") @ Results ({@ Result (property = "userSex", column = "user_sex", javaType = UserSexEnum.class), @ Result (property = "nickName", column = "nick_name")}) List getAll ()

Note the difference between using the # symbol and the $symbol: the following two examples show that using # preprocesses SQL, splicing SQL when using $, recommended using #, and using $has the possibility of SQL injection.

/ / This example creates a prepared statement, something like select * from teacher where name =?; @ Select ("Select * from teacher where name = # {name}") Teacher selectTeachForGivenName (@ Param ("name") String name); / / This example creates n inlined statement, something like select * from teacher where name = 'someName';@Select ("Select * from teacher where name =' ${name}'") Teacher selectTeachForGivenName (@ Param ("name") String name)

Dynamic SQL

The biggest feature of MyBatis is that it can flexibly support dynamic SQL. In the annotated version, there are two ways to support it. The first is to use annotations, and the other is to provide SQL classes to support it.

Use annotations to do this, surround it with script tags, and then write like XML syntax:

@ Update ("update users userName=# {userName}, nick_name=# {nickName}, where id=# {id}") void update (User user)

This approach is the mixed use of annotations + XML, which has the flexibility and convenience of XML, but there is also a disadvantage that it is not convenient to concatenate XML syntax in Java code, so MyBatis provides a more elegant way to support dynamic construction of SQL.

Use SQL to build classes to support, and take paging as an example to demonstrate. First, define a UserSql class that provides the SQL that needs to be executed for method stitching:

Public class UserSql {public String getList (UserParam userParam) {StringBuffer sql = new StringBuffer ("select id, userName, passWord, user_sex as userSex, nick_name as nickName"); sql.append ("from users where 1x1"); if (userParam! = null) {if (StringUtils.isNotBlank (userParam.getUserName () {sql.append ("and userName = # {userName}") } if (StringUtils.isNotBlank (userParam.getUserSex)) {sql.append ("and user_sex = # {userSex}");}} sql.append ("order by id desc"); sql.append ("limit" + userParam.getBeginLine () + "," + userParam.getPageSize ()); log.info ("getList sql is:" + sql.toString ()) Return sql.toString ();}}

You can see that there is a method getList in UserSql, which uses StringBuffer to concatenate SQL, builds SQL dynamically through if judgment, and finally returns the SQL statement to be executed.

Next, you only need to introduce this class and method into Mapper. Relative to @ SelectProvider, you can provide query SQL method import, and @ InsertProvider, @ UpdateProvider, and @ DeleteProvider are used when inserting, updating, and deleting.

Type: a class that dynamically generates SQL

Method: the specific method name in the class

@ SelectProvider (type = UserSql.class, method = "getList") List getList (UserParam userParam)

Structured SQL

You may find it troublesome to concatenate SQL in this way. SQL sentences are too complex and messy. Don't worry! MyBatis provides us with an upgrade solution: structured SQL.

Public String getCount (UserParam userParam) {String sql= new SQL () {{SELECT ("count (1)"); FROM ("users"); if (StringUtils.isNotBlank (userParam.getUserName () {WHERE ("userName = # {userName}");} if (StringUtils.isNotBlank (userParam.getUserSex () {WHERE ("user_sex = # {userSex}") } / / from this toString, we can see that it uses efficient StringBuilder to implement SQL stitching}} .toString (); log.info ("getCount sql is:" + sql); return sql;}

SELECT represents the fields to be queried. Multiple lines can be written, and multiple rows of SELECT will be intelligently merged without repetition. FROM and WHERE, like SELECT, can write multiple parameters or reuse them on multiple lines, eventually intelligently merging without reporting errors. This statement is suitable for long-written SQL, and can ensure that SQL has a clear structure, easy maintenance and high readability.

Use of multiple data sources

The use of multiple data sources in the annotated version is basically the same as that in the XML version.

First configure multiple data sources:

Mybatis.type-aliases-package=com.neo.modelspring.datasource.test1.jdbc-url=jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=truespring.datasource.test1.username=rootspring.datasource.test1.password=rootspring.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.test2.jdbc-url=jdbc:mysql://localhost:3306/test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=truespring.datasource.test2.username=rootspring.datasource.test2.password=rootspring.datasource.test2.driver-class-name=com.mysql.cj.jdbc.Driver

Build two different data sources respectively.

@ Configuration@MapperScan (basePackages = "com.neo.mapper.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate") public class DataSource1Config {@ Bean (name = "test1DataSource") @ ConfigurationProperties (prefix = "spring.datasource.test1") @ Primary public DataSource testDataSource () {return DataSourceBuilder.create (). Build ();} @ Bean (name = "test1SqlSessionFactory") @ Primary public SqlSessionFactory testSqlSessionFactory (@ Qualifier ("test1DataSource") DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean () Bean.setDataSource (dataSource); return bean.getObject ();} @ Bean (name = "test1TransactionManager") @ Primary public DataSourceTransactionManager testTransactionManager (@ Qualifier ("test1DataSource") DataSource dataSource) {return new DataSourceTransactionManager (dataSource);} @ Bean (name = "test1SqlSessionTemplate") @ Primary public SqlSessionTemplate testSqlSessionTemplate (@ Qualifier ("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {return new SqlSessionTemplate (sqlSessionFactory);}}

The DataSource2 configuration is basically the same as the DataSource1 configuration, except for @ Primary.

test

Inject two different Mapper respectively, and use the Mapper of which data source you want to operate.

@ RunWith (SpringRunner.class) @ SpringBootTestpublic class UserMapperTest {@ Autowired private User1Mapper user1Mapper; @ Autowired private User2Mapper user2Mapper; @ Testpublic void testInsert () throws Exception {user1Mapper.insert (new User ("aa111", "a123456", UserSexEnum.MAN)); user1Mapper.insert (new User ("bb111", "b123456", UserSexEnum.WOMAN)); user2Mapper.insert ("cc222", "b123456", UserSexEnum.MAN)) }} this is the answer to the question about how to integrate and use MyBatis in SpringBoot. I hope the above content can be of some help to you. If you still have a lot of questions to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.