源码
-
引入 Druid +mybatis 等开发包
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <parent> <artifactId>springboot-integrate</artifactId> <groupId>springboot-integrate</groupId> <version>1.0-SNAPSHOT</version> </parent> <modelVersion>4.0.0</modelVersion> <artifactId>springboot-mybatis</artifactId> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!--mysql--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency> <!--druid(数据库连接池)--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency> <!--mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.2</version> <scope>provided</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <mainClass>com.integrate.mybatis.MybatisApplication</mainClass> </configuration> </plugin> </plugins> </build> </project>
-
application.yml 文件配置 数据源信息 及 数据源连接池信息
spring: application: name: springboot-mybatis #druid数据源相关配置配置 datasource: url: jdbc:mysql://101.37.152.195:3306/consumer?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&serverTimezone=Asia/Shanghai&allowMultiQueries=true username: root password: lzq199528 driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource #连接池的配置信息 Spring Boot 不支持 Druid 需要手动配置 initialSize: 5 minIdle: 10 maxActive: 20 maxWait: 6000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 30000 validationQuery: SELECT 1 validationQueryTimeout: 10000 testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 filters: stat,wall connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 useGlobalDataSourceStat: true #mybatis 配置信息 mybatis: type-aliases-package: com.integrate.mybatis.domain config-location: classpath:mybatis/mybatis-config.xml mapper-locations: classpath:mappers/*.xml server: port: 10004
-
数据源 配置 Druid 连接池信息
package com.integrate.mybatis.config.druid; import com.alibaba.druid.pool.DruidDataSource; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.core.env.Environment; import org.springframework.stereotype.Component; import javax.sql.DataSource; import java.util.Properties; /** * @author 刘志强 * @date 2020/11/27 14:02 * com.integrate.multiple.sources.mapper.consumer 生效范围 */ @Component @Slf4j public class DataSourceConfiguration implements CommandLineRunner { @Autowired private Environment env; @Autowired private DataSource dataSource; @Override public void run(String... args) throws Exception { // 配置链接池信息 log.info("数据源连接池配置"); String prefixPool = "spring.datasource."; Properties prop = new Properties(); prop.put("druid.initialSize", env.getProperty(prefixPool + "initialSize", String.class)); prop.put("druid.maxActive", env.getProperty(prefixPool + "maxActive", String.class)); prop.put("druid.minIdle", env.getProperty(prefixPool + "minIdle", String.class)); prop.put("druid.maxWait", env.getProperty(prefixPool + "maxWait", String.class)); prop.put("druid.poolPreparedStatements", env.getProperty(prefixPool + "poolPreparedStatements", String.class)); prop.put("druid.maxPoolPreparedStatementPerConnectionSize", env.getProperty(prefixPool + "maxPoolPreparedStatementPerConnectionSize", String.class)); prop.put("druid.validationQuery", env.getProperty(prefixPool + "validationQuery", String.class)); prop.put("druid.validationQueryTimeout", env.getProperty(prefixPool + "validationQueryTimeout", String.class)); prop.put("druid.testOnBorrow", env.getProperty(prefixPool + "testOnBorrow", String.class)); prop.put("druid.testOnReturn", env.getProperty(prefixPool + "testOnReturn", String.class)); prop.put("druid.testWhileIdle", env.getProperty(prefixPool + "testWhileIdle", String.class)); prop.put("druid.timeBetweenEvictionRunsMillis", env.getProperty(prefixPool + "timeBetweenEvictionRunsMillis", String.class)); prop.put("druid.minEvictableIdleTimeMillis", env.getProperty(prefixPool + "minEvictableIdleTimeMillis", String.class)); prop.put("druid.filters", env.getProperty(prefixPool + "filters", String.class)); DruidDataSource druidDataSource = (DruidDataSource) dataSource; druidDataSource.configFromPropety(prop); // 数据库初始化 druidDataSource.init(); log.info("数据源初始化结束"); } }
-
配置 Druid web 信息
package com.integrate.mybatis.config.druid; import com.alibaba.druid.filter.stat.StatFilter; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import com.alibaba.druid.wall.WallConfig; import com.alibaba.druid.wall.WallFilter; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; /** * @author 刘志强 * @date 2020/11/27 14:02 * @ServletComponentScan // 用于扫描所有的Servlet、filter、listener */ @Configuration public class DruidConfig { @Bean public ServletRegistrationBean<StatViewServlet> druidServlet() { ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*"); servletRegistrationBean.addInitParameter("loginUsername", "root"); servletRegistrationBean.addInitParameter("loginPassword", "1234"); return servletRegistrationBean; } @Bean public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() { FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>(); filterRegistrationBean.setFilter(new WebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); filterRegistrationBean.addInitParameter("profileEnable", "true"); return filterRegistrationBean; } @Bean public StatFilter statFilter() { StatFilter statFilter = new StatFilter(); //slowSqlMillis用来配置SQL慢的标准,执行时间超过slowSqlMillis的就是慢。 statFilter.setLogSlowSql(true); //SQL合并配置 statFilter.setMergeSql(true); //slowSqlMillis的缺省值为3000,也就是3秒。 statFilter.setSlowSqlMillis(1000); return statFilter; } @Bean public WallFilter wallFilter() { WallFilter wallFilter = new WallFilter(); //允许执行多条SQL WallConfig config = new WallConfig(); config.setMultiStatementAllow(true); wallFilter.setConfig(config); return wallFilter; } }
-
mybatis-config.xml mybatis配置信息
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 配置mybatis的缓存,延迟加载等等一系列属性 --> <settings> <setting name="autoMappingBehavior" value="FULL"/> <!--开启驼峰映射 xml文件中 resultType 为实体类时, 将 如 user_name数据库字段 映射为 实体类 userName字段--> <setting name="mapUnderscoreToCamelCase" value="true"/> <!-- 开启二级缓存 --> <setting name="cacheEnabled" value="true"/> <!-- 懒加载 --> <setting name="lazyLoadingEnabled" value="true"/> <setting name="aggressiveLazyLoading" value="false"/> </settings> </configuration>
-
依次创建 controller mapper service domain(数据库实体类) 目录
-
controller
package com.integrate.mybatis.controller; import com.integrate.mybatis.domain.ConsumerUser; import com.integrate.mybatis.service.ConsumerUserService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.CrossOrigin; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RestController; /** * @author 刘志强 * @date 2020/11/27 14:18 */ @RestController @Slf4j @CrossOrigin public class TestController { @Autowired private ConsumerUserService consumerUserService; /** * 获取Consumer来源的用户 * * @param id * @return */ @GetMapping("getConsumerUser") public ConsumerUser getConsumerUser(Long id) { return consumerUserService.getConsumerUser(id); } /** * 添加consumer用户 * * @param consumerUser * @return */ @PostMapping("addConsumerUser") public String addConsumerUser(ConsumerUser consumerUser) { return consumerUserService.addConsumerUser(consumerUser); } }
-
service
package com.integrate.mybatis.service; import com.integrate.mybatis.domain.ConsumerUser; /** * @author 刘志强 * @date 2020/11/27 14:20 */ public interface ConsumerUserService { /** * 获取消费库用户 * @param id * @return */ ConsumerUser getConsumerUser(Long id); /** * 添加消费库用户 * @param consumerUser * @return */ String addConsumerUser(ConsumerUser consumerUser); }
package com.integrate.mybatis.service.impl; import com.integrate.mybatis.domain.ConsumerUser; import com.integrate.mybatis.mapper.ConsumerUserMapper; import com.integrate.mybatis.service.ConsumerUserService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; /** * @author 刘志强 * @date 2020/11/27 14:20 */ @Service @Slf4j public class ConsumerUserImpl implements ConsumerUserService { @Autowired private ConsumerUserMapper consumerUserMapper; @Override public ConsumerUser getConsumerUser(Long id) { return consumerUserMapper.getConsumerUserById(id); } @Override @Transactional(rollbackFor = Exception.class) public String addConsumerUser(ConsumerUser consumerUser) { int i = consumerUserMapper.addConsumerUser(consumerUser); log.info(consumerUser.toString()); if (i > 0) { return "添加成功"; } else { return "添加失败"; } } }
-
mapper
package com.integrate.mybatis.mapper; import com.integrate.mybatis.domain.ConsumerUser; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; /** * @author 刘志强 * @date 2020/11/27 14:22 */ @Mapper public interface ConsumerUserMapper { /** * 获取消费库用户 * * @param id * @return */ ConsumerUser getConsumerUserById(@Param("id") Long id); /** * 添加消费库用户 * @param consumerUser * @return */ int addConsumerUser(ConsumerUser consumerUser); }
- @Param(该注解属于MyBatis)作为Dao层的注解,作用是用于传递参数,从而可以与SQL中的的字段名相对应,一般在2=<参数数<=5时使用最佳。
- 如果你的映射方法接受多个参数,就可以使用这个注解自定义每个参数的名字。否则在默认情况下,除RowBounds以外的参数会以 “param” 加参数位置被命名。例如 #{param1}, #{param2}。如果使用了 @Param(“person”),参数就会被命名为 #{person}。(你可以传递多个参数给一个映射器方法。在多个参数的情况下,默认它们将会以 param 加上它们在参数列表中的位置来命名,比如:#{param1}、#{param2}等。如果你想(在有多个参数时)自定义参数的名称,那么你可以在参数上使用 @Param(“paramName”) 注解)。
-
mapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.integrate.mybatis.mapper.ConsumerUserMapper"> <select id="getConsumerUserById" resultType="com.integrate.mybatis.domain.ConsumerUser"> select a.* from user a where a.id = #{id} </select> <!--useGeneratedKeys="true" keyProperty="id" 添加成功后 主键id set进入consumerUser对象 切记参数不要用@Param注解,否则失效--> <insert id="addConsumerUser" parameterType="com.integrate.mybatis.domain.ConsumerUser" useGeneratedKeys="true" keyProperty="id"> insert into user (user_name) values (#{userName}); </insert> </mapper>
-
namespace 命名空间 在编译后 xml会转称对应接口得实现类
-
id 和 Mapper 中的方法名对应,不可以重复
-
resultType 返回结果类型 对应 Mapper 中对应方法中的返回类型或泛型
-
parameterType 接受的参数类型。没有使用@Param 时使用
-
useGeneratedKeys=“true” keyProperty=“id” 添加成功后 主键id set进入consumerUser对象 切记参数不要用@Param注解,否则失效
-
-
mapper.xml 注意事项
- mybatis 标签中 0 等于false的问题
- 在if标签中 如果将 整形当做字符串比较会出现此问题 也就是 添加 != ‘’ 判断时。对于非字符串的类型无需添加 != ‘’
- 有问题的写法
<if test="state != null and state != ''">
- 没有问题的写法
<if test="state != null">
- 在使用 foreach 时。如果要遍历对像是个二维数组使用时 使用$ 否则会报未定义错误
- 示例
<foreach collection="indexScreenDto.memberLevel" item="arr" index="index" open="AND (" separator="or" close=")"> ( a.member_level = ${arr[0]} <if test="arr[1] != null"> AND a.beautiful_state = ${arr[1]} </if> ) </foreach>
- 示例
- mybatis 标签中 0 等于false的问题
-