Spring Boot and mybatis frameworks

This article describes how spring boot and mybatis work together.

Dependency and data source configuration

Springboot depends on spring4 and mybatis-spring. The latest version is 1.2.2.

Data source dependencies:

<! -- Datasource --> <dependency> <groupId> com. zaxxer </groupId> <artifactId> HikariCP-java6 </artifactId> <version >$ {HikariCP. version }</version> </dependency> <groupId> mysql </groupId> <artifactId> mysql-connector-java </artifactId> <version >$ {mysql-connector-java.version} </version> </dependency> <groupId> org. mybatis </groupId> <artifactId> mybatis </artifactId> <version >$ {mybatis. version }</version> </dependency> <groupId> org. mybatis </groupId> <artifactId> mybatis-spring </artifactId> <version >$ {mybatis-spring.version} </version> </dependency>

The first two are data source dependencies, including HikariCP and mysql drivers. The latter two are mybatis dependencies, including mybatis and mybatis-spring modules.

With these dependencies, you can configure the mybatis data source through the spring 4 configuration class.

@ Configuration @ PropertySource ("classpath: datasource. properties ") @ MapperScan (basePackages =" xxx. repository ", sqlSessionFactoryRef =" sqlSessionFactory ") public class performanceconfig {@ Autowired private Environment env; @ Bean public DataSource dataSource () {HikariConfig config = new HikariConfig (); config. setDriverClassName ("com. mysql. jdbc. driver "); config. setAutoCommit (false); config. setJdbcUrl (env. getProperty ("xxx. db. url "); config. setUsername (env. getProperty ("xxx. db. username "); config. setPassword (env. getProperty ("xxx. db. password "); return new HikariDataSource (config);} @ Bean public DataSourceTransactionManager transactionManager () {return new cetcetransactionmanager (dataSource ();} @ Bean public SqlSessionFactory sqlSessionFactory (DataSource dataSource) throws Exception {final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean (); sessionFactory. setDataSource (dataSource); sessionFactory. setTypeAliasesPackage ("xxx. mybatis "); return sessionFactory. getObject ();}}

First introduce the configuration file and inject it into the env object. Like the properties object of System, env encapsulates the key value in the configuration file.
Then, use the MapperScan annotation to define the mapper interface package path. SqlSessionFactoryRef is defined here because multiple data sources are required,
Prevents injection by spring, which will be mentioned later.

Then the code can start to define the output bean. One is datasource, which directly initializes a Hikari data source. springboot provides the builder class,
However, DataSourceBuilder cannot configure the autocommit attribute after you view the source code and api.

Next is transaction management. dataSource needs to be injected through the constructor. The last one is sqlSessionFactory of mybatis, which is mainly used to inject a data source.

Mapper (DAO) implementation

Dao implementation is similar to the original ibatis, but mybatis can directly generate dynamic SQL statements through annotations. Since springboot uses code to replace xml, xml is also removed from mybatis.

Insert

Note two points for the insert operation: how to return the inserted primary key (mysql) and how to use the data-type handler.
First, check the code:

@ Insert ("insert into aegis_cron_timer" +
"(Id, gmt_create, gmt_modified, name, expression, event_class_name, description, last_trigger_time, status, parameter)" +
"VALUES (NULL, now (), now (), # {name: VARCHAR}, # {expression: VARCHAR}," +
"# {EventClassName: VARCHAR}, # {description: VARCHAR}, now (), # {status: VARCHAR}," +
"# {Parameter, typeHandler = com. alibaba. aegis. seawater. cron. service. dao. mybatis. MapToJsonTypeHandler })")
@ SelectKey (before = false, statement = "SELECT LAST_INSERT_ID ()", keyProperty = "id", resultType = java. lang. Long. class)
Public Long insertCronTimer (CronTimer cronTimer );

For mysql, you can use the SelectKey annotation to set the primary key return after insertion. Because mysql is an auto-increment primary key, it is set to execute after insertion and The Returned type is defined as long (bigint is defined in the database ).

In addition, a field needs to be serialized from map into a json string and stored as a varchar type in the database. In the inserted SQL statement, typeHandler can be defined directly after the variable. The value is the complete class name corresponding to handler.

Update

The Update operation is relatively simple. You can directly use the Update annotation. Similar to insert, if you need to specify the type handler, simply add a parameter after the field. The update function returns an int value, indicating the number of rows in this update.

Query

The query is completed using the Select annotation. mybatis can be automatically associated with the query result's java bean directly through field names. If the name does not match, there are two methods: one is to convert the AS keyword to the field name in java bean by adding the AS keyword in SQL, and the other is to specify the ing relationship between the two through the @ Result annotation.

@ Select ("SELECT name, expression, event_class_name AS eventClassName, description, status, parameter" +
"FROM aegis_cron_timer" +
"WHERE status = 'enable '")
@ Results ({
@ Result (column = "parameter", jdbcType = JdbcType. VARCHAR, property = "parameter", typeHandler = MapToJsonTypeHandler. class)
})
Public List <CronTimer> listAllAvailableCronTimer ();

The type handler is configured through the Result annotation. Note that the Result annotation must be in the Results annotation, otherwise it will not take effect.

Custom type handler

The previous article mentioned how to use type handler in insert, update, and query statements. The implementation of type handler is also relatively simple. The built-in type handler of mybatis is implemented through extends BaseTypeHandler, but the TypeHandler interface is directly implemented in the example:

@ MappedTypes (Map. class) @ MappedJdbcTypes (JdbcType. VARCHAR) public class MapToJsonTypeHandler implements TypeHandler <Map <String, Object >{@ Override public void setParameter (PreparedStatement ps, int I, Map <String, Object> parameter, JdbcType jdbcType) throws SQLException {ps. setString (I, JSON. toJSONString (parameter);} @ Override public Map <String, Object> getResult (ResultSet rs, String columnName) throws SQLException {String value = rs. getString (columnName); return jsonToMap (value) ;}@ Override public Map <String, Object> getResult (ResultSet rs, int columnIndex) throws SQLException {String value = rs. getString (columnIndex); return jsonToMap (value) ;}@ Override public Map <String, Object> getResult (CallableStatement cs, int columnIndex) throws SQLException {String value = cs. getString (columnIndex); return jsonToMap (value);} private Map <String, Object> jsonToMap (String value) {if (StringUtils. isBlank (value) {return Collections. emptyMap ();} else {return JSON. parseObject (value, new TypeReference <Map <String, Object >> (){});}}}

The implementation is relatively simple. During serialization, map objects are directly converted into json strings through fastjson and placed in PreparedStatement. It can be returned to be converted to Map during deserialization.

Multi-data source implementation

Because the project needs to migrate from the old database to the new database, two data sources are required, and many pitfalls are also imposed when setting multiple data sources.

Another data source configuration class:

@ Configuration @ PropertySource ("classpath: amon-datasource.properties") @ MapperScan (basePackages = "com. alibaba. aegis. seawater. cron. migrate. repository ", region =" amonSqlSessionFactory ", region =" amonSqlSessionTemplate ") public class AmonDataSourceConfig {@ Autowired private Environment env; @ Bean (name =" amonDataSource ") public DataSource amonDataSource () {HikariConfig config = new HikariConfig (); config. setDriverClassName ("com. mysql. jdbc. driver "); config. setAutoCommit (true); config. setJdbcUrl (env. getProperty ("amon. db. url "); config. setUsername (env. getProperty ("amon. db. username "); config. setPassword (env. getProperty ("amon. db. password "); return new HikariDataSource (config);} @ Bean (name =" amonTransactionManager ") public cetcetransactionmanager amonTransactionManager (@ Qualifier (" amonDataSource ") DataSource) {return new cetcetransactionmanager (dataSource) ;}@ Bean (name = "amonSqlSessionFactory") public SqlSessionFactory amonSqlSessionFactory (@ Qualifier ("amonDataSource") DataSource dataSource) throws Exception {final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean (); sessionFactory. setDataSource (dataSource); return sessionFactory. getObject () ;}@ Bean (name = "amonSqlSessionTemplate") public SqlSessionTemplate amonSqlSessionTemplate (@ Qualifier ("separator") SqlSessionFactory sqlSessionFactory) throws Exception {return new SqlSessionTemplate (sqlSessionFactory );}}

A configuration file is also defined here. Note that it should not be the same as the previous key, otherwise it will be overwritten. You need to set the name when defining the bean, or change the function name.
The bean to be defined is the same as the previous one. Note that the MapperScan annotation must modify sqlSessionFactoryRef or sqlSessionTemplateRef. Both of them have been changed, but at startup
Tip:

Cannot use both: sqlSessionTemplate and sqlSessionFactory together. sqlSessionFactory is ignored.

After bean is defined here, it is no problem to use it directly. The only note is the @ Transactional annotation. Because two transactionmanagers are defined,
The transaction manager cannot be injected by type. It must be specified in the annotation. For example, to use the transaction manager of the data source defined earlier, you need to change it:

@ Transactional ("transactionManager ")

In this way, spring can inject bean by name.

DAO test

To facilitate the test, dataSource is overwritten in the corresponding Test class, and the memory database h2 is used to solve the interference of unit test data.

@ Configuration @ MapperScan (basePackages = "com. alibaba. aegis. seawater. cron. repository ") public class TestDatasourceConfig extends DatasourceConfig {@ Autowired private Environment env; @ Bean public DataSource dataSource () {return new EmbeddedDatabaseBuilder (). setType (EmbeddedDatabaseType. h2 ). setName ("cron "). addScript ("h2. SQL "). build ();}}

The following code uses the EmbeddedDatabaseBuilder provided by springboot to create an h2 database and add an SQL file that initializes the database schema.
Note that if the SQL file is called schema. SQL, the mysql data source will be executed before execution, so the default name is not used here.

Other Pitfalls

When Spring Boot is injected into the properties File for configuration, it also encounters a disgusting problem. Besides the properties file specified by the PropertySource annotation,
By default, spring also includes jvm variables and system environment variables. At the beginning, the key of the database username field was directly written as USERNAME. As the sudo command was used on the test server, sudo set the username environment variable to identify the original user while switching the user, as a result, springboot has been injecting this value for a long time.

How to configure Springboot + mybatis

Official Wood has springboot + mybatis configuration, only JPA, JPA does not want to use, table join query is too tangled.

Have you configured springboot + mybatis for reference. Or how to add native configuration methods under springboot, such as online.

Answer:Very simple ....

@ Configuration @ EnableTransactionManagement @ MapperScan ("com. *. *. mapper ") public class DataBaseConfig {private final Logger log = LoggerFactory. getLogger (DataBaseConfig. class); @ Bean @ Primary @ ConfigurationProperties (prefix = "datasource. primary ") public DataSource () {log. debug ("logging ing Datasource"); return new DruidDataSource () ;}@ Bean public PlatformTransactionManager txManager () {return new cetcetransactionmanager (dataSource ();} @ Bean public SqlSessionFactory sqlSessionFactoryBean () throws Exception {SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean (); sqlSessionFactoryBean. setDataSource (dataSource (); PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver (); sqlSessionFactoryBean. setMapperLocations (resolver. getResources ("classpath:/mapper /*. xml "); return sqlSessionFactoryBean. getObject ();}}