I. Project case introduction

1. Introduction to multi-data

In real projects, different databases are often used to meet the actual needs of the project. With the increasing concurrency of services, it is more and more common for a project to use multiple databases: master/slave replication, read/write separation, and distributed databases.

2. Introduction to MybatisPlus

MyBatis-Plus (MP for short) is a MyBatis enhancement tool, on the basis of MyBatis only do enhancement do not change, to simplify the development and improve efficiency.

Plug-in features

No code intrusion: only enhancements are made and no changes are made. Introducing it will not affect existing projects. Powerful CRUD operations: Most CRUD operations in a single table can be implemented with a few configurations to meet various requirements. Support Lambda form call: through Lambda expressions, it is convenient to write various query conditions. Supports automatic primary key generation: Can be configured freely to solve the primary key problem. Built-in code generator: Layers of code can be quickly generated using code or Maven plug-ins. Built-in paging plugin: Based on MyBatis physical paging, developers do not need to care about specific operations. Built-in performance analysis plug-in: output Sql statements and their execution time.Copy the code

2. Multi-data source cases

1. Project structure

Note: mapper layer and mapper. XML layer are placed in different directories for myBatis scan loading.

2. Configure multiple data sources

Spring: # data source configuration datasource: type: com. Alibaba. Druid. Pool. DruidDataSource admin - data: driverClassName: . Com. Mysql. JDBC Driver dbUrl: JDBC: mysql: / / 127.0.0.1:3306 / cloud - admin - data? useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false username: root password: 123 initialSize: 20 maxActive: 100 minIdle: 20 maxWait: 60000 poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 30 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 30000 maxEvictableIdleTimeMillis: 60000 validationQuery: SELECT 1 FROM DUAL testOnBorrow: false testOnReturn: false testWhileIdle: true connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000 filters: stat,wall user-data: driverClassName: com.mysql.jdbc.Driver dbUrl: JDBC: mysql: / / 127.0.0.1:3306 / cloud - the user - the data? useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false username: root password: 123 initialSize: 20 maxActive: 100 minIdle: 20 maxWait: 60000 poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 30 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 30000 maxEvictableIdleTimeMillis: 60000 validationQuery: SELECT 1 FROM DUAL testOnBorrow: false testOnReturn: false testWhileIdle: true connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000 filters: stat,wallCopy the code

The parameter forms are varied. You only need to configure parameter scanning.

3. Parameter scanning class

@Component@ConfigurationProperties(prefix = "spring.datasource.admin-data")public class DruidOneParam { private String dbUrl; private String username; private String password; private String driverClassName; private int initialSize; private int maxActive; private int minIdle; private int maxWait; private boolean poolPreparedStatements; private int maxPoolPreparedStatementPerConnectionSize; private int timeBetweenEvictionRunsMillis; private int minEvictableIdleTimeMillis; private int maxEvictableIdleTimeMillis; private String validationQuery; private boolean testWhileIdle; private boolean testOnBorrow; private boolean testOnReturn; private String filters; private String connectionProperties; // omit GET and SET}Copy the code

4. Configure a Druid connection pool

@Configuration@MapperScan(basePackages = {"com.data.source.mapper.one"},sqlSessionTemplateRef = "sqlSessionTemplateOne")public class DruidOneConfig {    private static final Logger LOGGER = LoggerFactory.getLogger(DruidOneConfig.class) ;    @Resource    private DruidOneParam druidOneParam ;    @Bean("dataSourceOne")    public DataSource dataSourceOne () {        DruidDataSource datasource = new DruidDataSource();        datasource.setUrl(druidOneParam.getDbUrl());        datasource.setUsername(druidOneParam.getUsername());        datasource.setPassword(druidOneParam.getPassword());        datasource.setDriverClassName(druidOneParam.getDriverClassName());        datasource.setInitialSize(druidOneParam.getInitialSize());        datasource.setMinIdle(druidOneParam.getMinIdle());        datasource.setMaxActive(druidOneParam.getMaxActive());        datasource.setMaxWait(druidOneParam.getMaxWait());        datasource.setTimeBetweenEvictionRunsMillis(druidOneParam.getTimeBetweenEvictionRunsMillis());        datasource.setMinEvictableIdleTimeMillis(druidOneParam.getMinEvictableIdleTimeMillis());        datasource.setMaxEvictableIdleTimeMillis(druidOneParam.getMaxEvictableIdleTimeMillis());        datasource.setValidationQuery(druidOneParam.getValidationQuery());        datasource.setTestWhileIdle(druidOneParam.isTestWhileIdle());        datasource.setTestOnBorrow(druidOneParam.isTestOnBorrow());        datasource.setTestOnReturn(druidOneParam.isTestOnReturn());        datasource.setPoolPreparedStatements(druidOneParam.isPoolPreparedStatements());        datasource.setMaxPoolPreparedStatementPerConnectionSize(druidOneParam.getMaxPoolPreparedStatementPerConnectionSize());        try {            datasource.setFilters(druidOneParam.getFilters());        } catch (Exception e) {            LOGGER.error("druid configuration initialization filter", e);        }        datasource.setConnectionProperties(druidOneParam.getConnectionProperties());        return datasource;    }    @Bean    public SqlSessionFactory sqlSessionFactoryOne() throws Exception{        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();        factory.setDataSource(dataSourceOne());        factory.setMapperLocations(resolver.getResources("classpath*:/dataOneMapper/*.xml"));        return factory.getObject();    }    @Bean(name="transactionManagerOne")    public DataSourceTransactionManager transactionManagerOne(){        return  new DataSourceTransactionManager(dataSourceOne());    }    @Bean(name = "sqlSessionTemplateOne")    public SqlSessionTemplate sqlSessionTemplateOne() throws Exception {        return new SqlSessionTemplate(sqlSessionFactoryOne());    }}Copy the code

Matters needing attention

  • MapperScan is configured on the specified data source.

  • SqlSessionFactory Configudes the scanned mapper. XML address;

  • DataSourceTransactionManager configure the data source of transaction;

  • The configuration methods of the two data sources are the same.

5. Operating cases

  • Data source 1: simple query

@Servicepublic class AdminUserServiceImpl implements AdminUserService {    @Resource    private AdminUserMapper adminUserMapper ;    @Override    public AdminUser selectByPrimaryKey (Integer id) {        return adminUserMapper.selectByPrimaryKey(id) ;    }}Copy the code

  • Data source two: transaction operations

@Servicepublic class UserBaseServiceImpl implements UserBaseService { @Resource private UserBaseMapper userBaseMapper ; @Override public UserBase selectByPrimaryKey(Integer id) { return userBaseMapper.selectByPrimaryKey(id); } @transactional (value = "transactionManagerTwo") @override public void insert(UserBase record) {// Userbasemapper.insert (record); // int i = 1/0 ; }}Copy the code

Note: Here you need to specify the transaction manager for the data source configuration.

Iii. MybatisPlus Case

1. Core dependencies

<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> < version > 3.0.7.1 < / version > < exclusions > < exclusion > < groupId > com. Baomidou < / groupId > <artifactId>mybatis-plus-generator</artifactId> </exclusion> </exclusions></dependency><dependency> < the groupId > com. Baomidou < / groupId > < artifactId > mybatis - plus < / artifactId > < version > 3.0.7.1 < / version > < / dependency >Copy the code

2. Configuration files

mybatis-plus:  mapper-locations: classpath*:/mapper/*.xml  typeAliasesPackage: com.digital.market.*.entity  global-config:    db-config:      id-type: AUTO      field-strategy: NOT_NULL      logic-delete-value: -1      logic-not-delete-value: 0    banner: false  configuration:    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl    map-underscore-to-camel-case: true    cache-enabled: false    call-setters-on-nulls: true    jdbc-type-for-null: 'null'Copy the code

3. Layered configuration

UserBaseMapper extends BaseMapper<UserBase> Extends ServiceImpl<UserBaseMapper,UserBase> UserBaseService extends IService<UserBase>Copy the code

Mapper. XML file

<mapper namespace="com.plus.batis.mapper.UserBaseMapper" > <resultMap id="BaseResultMap" type="com.plus.batis.entity.UserBase" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="user_name" property="userName" jdbcType="VARCHAR" /> <result column="pass_word" property="passWord" jdbcType="VARCHAR" /> <result column="phone" property="phone" jdbcType="VARCHAR" /> <result column="email" property="email" jdbcType="VARCHAR" /> <result column="create_time" property="createTime" jdbcType="TIMESTAMP" /> <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" /> <result column="state" property="state" jdbcType="INTEGER" /> </resultMap>  <sql id="Base_Column_List" > id, user_name, pass_word, phone, email, create_time, update_time, state </sql> <select id="selectByParam" parameterType="com.plus.batis.entity.QueryParam" resultMap="BaseResultMap"> select * from hc_user_base </select></mapper>Copy the code

Matters needing attention

Methods in BaseMapper are implemented by default; You can also customize your own methods here.

5. Demo interface

@RestController@RequestMapping("/user")public class UserBaseController { private static final Logger LOGGER = LoggerFactory.getLogger(UserBaseController.class) ; @Resource private UserBaseService userBaseService ; @RequestMapping("/info") public UserBase getUserBase (){ return userBaseService.getById(1) ; } @RequestMapping("/queryInfo") public String queryInfo (){ UserBase userBase1 = userBaseService.getOne(new QueryWrapper<UserBase>().orderByDesc("create_time")) ; Logger.info (" flashback value: {}", userbase1.getUsername ()); Integer count = userBaseService.count() ; Logger. info(" query total: {}",count); UserBase userBase2 = new UserBase() ; userBase2.setId(1); userBase2.setUserName("spring"); boolean resFlag = userBaseService.saveOrUpdate(userBase2) ; Logger. info(" Save updates: {}",resFlag); Map<String, Object> listByMap = new HashMap<>() ; listByMap.put("state","0") ; Collection<UserBase> listMap = userBaseService.listByMap(listByMap) ; Logger. info("ListByMap query: {}",listMap); boolean removeFlag = userBaseService.removeById(3) ; Logger. info(" Delete data: {}",removeFlag); return "success" ; } @RequestMapping("/queryPage") public IPage<UserBase> queryPage (){ QueryParam param = new QueryParam() ; param.setPage(1); param.setPageSize(10); param.setUserName("cicada"); param.setState(0); return userBaseService.queryPage(param) ; } @RequestMapping("/pageHelper") public PageInfo<UserBase> pageHelper (){ return userBaseService.pageHelper(new QueryParam()) ; }}Copy the code

The pageHelper method here is a custom method using the pageHelper plugin.

Source code address

Making address https://github.com/cicadasmile/middle-ware-parentGitEE, https://gitee.com/cicadasmile/middle-ware-parentCopy the code