spring Boot使用Mybatis实践

spring Boot使用Mybatis实践

Mybatis作为一款优秀的持久层框架，具有简化SQL编写、灵活性、易于集成、等特点深受开发人员青睐，下文将为大家讲到在Spring Boot中如何使用Mybatis

1、依赖引入

数据库驱动

        <dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.26</version></dependency>

数据源管理

        <dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.8</version></dependency>

 Mybatis

        <dependency><groupId&batis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.4</version></dependency>

2、配置信息

数据源配置

spring.datasource.druid.url=jdbc:mysql://IP:PORT/库名?characterEncoding=UTF-8&allowMultiQueries=true&useSSL=true
spring.datasource.druid.username=用户名
spring.datasource.druid.password=密码
spring.datasource.druid.driver-class-name&#sql.jdbc.Driver
spring.pe=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.max-idle=10
spring.datasource.druid.max-active=20
spring.datasource.druid.max-wait=10000
spring.datasource.druid.min-idle=5
spring.datasource.druid.initial-size=5
#自动重连
spring.datasource.druid.autoReconnect=true
#连接空闲时是否执行健康检查
spring.st-while-idle=true
#连接池用于健康检查的 SQL 语句。在每次获取连接时都会执行该语句来判断连接是否有效
spring.datasource.druid.validation-query=SELECT 1
#健康检查间隔时间，单位毫秒
spring.datasource.druid.validation-interval=18000
#连接最大寿命，单位毫秒
spring.datasource.druid.max-lifetime=180000

 mybatis配置

#mybatis
#xml文件路径
mybatis.mapper-locations=classpath:mybatis/*.xml
logging.batisplus=DEBUG
#将mapper包下的日志级别设置为DEBUG
logging.level.jd.XXX.mapper=DEBUG
figuration.log-impl=org.apache.ibatis.logging.slf4j.Slf4jImpl

使Mybatis生效

在Spring Boot启动主类上添加

@MapperScan("com.jd.XX.mapper")//使用MapperScan批量扫描所有的Mapper接口；

3、操作数据

3.1、声明和数据表字段一一映射的简单对象

package XXX;import java.math.BigDecimal;
import java.util.Date;
import java.util.StringJoiner;/*** 企业商户* User* 数据库表：user*/
public class User {/*** 自增ID* 表字段 : user.id*/private Long id;private String merchantNo;private String registrationNo;/*** 组织结构代码*/private String organizationCode;/*** 统一社会信用代码*/private String companyUscc;private String companyName;private Integer companyType;private String role;/*** 企业经营范围*/private String companyScope;private String provinceName;/*** 企业所在城市* 表字段 : user.city_name*/private String cityName;private BigDecimal creditScore;/*** 企业地址* 表字段 : user.address*/private String address;/*** 企业联系人* 表字段 : act*/@Cryptprivate String contact;private String contactCardType;private String contactCardNo;/*** 企业法人证件正面图片地址*/private String contactFaceUrl;/*** 企业法人证件反面图片地址*/private String contactConUrl;/*** 手持证件照片地址*/private String contactUrl;/*** 企业联系人邮箱* 表字段 : act_tel*/private String contactTel;/*** 企业中征码* 表字段 : _code*/private String crcCode;/*** 银行类型，国股 三农 城商* 表字段 : user.is_bank_org*/private Integer bankType;private Integer supportCredit;private Integer isAuth;private String companyMold;/*** 企业营业执照图片地址*/private String companyUrl;/*** 邮寄地址*/private String mailingAddress;private Integer acceptProtocol;private Date startTime;/*** 逻辑删除字段：0正常，1删除*/private Integer ldeleteFlag;/*** 是否接受平台协议*/public boolean hasAcceptProtocol() {return this.acceptProtocol != null && this.acceptProtocol.equals(1);}/*** 创建时间* 表字段 : ated_date*/private Date createdDate;/*** 修改时间* 表字段 : dified_date*/private Date modifiedDate;private Integer channel;/*** 企业用户 id.*/private String companyUserId;public Long getId() {return id;}/*** 设置 自增ID 字段:user.id** @param id the value for user.id, 自增ID*/public void setId(Long id) {this.id = id;}public String getMerchantNo() {return merchantNo;}public void setMerchantNo(String merchantNo) {hantNo = merchantNo == null ? null : im();}public String getOrganizationCode() {return organizationCode;}public void setOrganizationCode(String organizationCode) {thisanizationCode = organizationCode;}public String getCompanyScope() {return companyScope;}public void setCompanyScope(String companyScope) {thispanyScope = companyScope;}public String getCompanyUscc() {return companyUscc;}public void setCompanyUscc(String companyUscc) {thispanyUscc = companyUscc;}public String getRegistrationNo() {return registrationNo;}public void setRegistrationNo(String registrationNo) {istrationNo = registrationNo == null ? null : im();}/*** 获取 企业名称 字段:userpany_name** @return userpany_name, 企业名称*/public String getCompanyName() {return companyName;}/*** 设置 企业名称 字段:userpany_name** @param companyName the value for userpany_name, 企业名称*/public void setCompanyName(String companyName) {thispanyName = companyName == null ? null : im();}public Integer getCompanyType() {return companyType;}public void setCompanyType(Integer companyType) {thispanyType = companyType;}public String getProvinceName() {return provinceName;}public void setProvinceName(String provinceName) {this.provinceName = provinceName == null ? null : im();}/*** 获取 企业所在城市 字段:user.city_name** @return user.city_name, 企业所在城市*/public String getCityName() {return cityName;}/*** 设置 企业所在城市 字段:user.city_name** @param cityName the value for user.city_name, 企业所在城市*/public void setCityName(String cityName) {this.cityName = cityName == null ? null : im();}public BigDecimal getCreditScore() {return creditScore;}public void setCreditScore(BigDecimal creditScore) {ditScore = creditScore;}/*** 获取 企业地址 字段:user.address** @return user.address, 企业地址*/public String getAddress() {return address;}/*** 设置 企业地址 字段:user.address** @param address the value for user.address, 企业地址*/public void setAddress(String address) {this.address = address == null ? null : im();}/*** 获取 企业联系人 字段:act** @act, 企业联系人*/public String getContact() {return contact;}/*** 设置 企业联系人 字段:act** @param contact the value act, 企业联系人*/public void setContact(String contact) {act = contact == null ? null : im();}/*** 获取 企业联系人邮箱 字段:act_tel** @act_tel, 企业联系人邮箱*/public String getContactTel() {return contactTel;}/*** 设置 企业联系人邮箱 字段:act_tel** @param contactTel the value act_tel, 企业联系人邮箱*/public void setContactTel(String contactTel) {actTel = contactTel == null ? null : im();}/*** 获取 企业中征码 字段:_code** @_code, 企业中征码*/public String getCrcCode() {return crcCode;}/*** 设置 企业中征码 字段:_code** @param crcCode the value _code, 企业中征码*/public void setCrcCode(String crcCode) {Code = crcCode == null ? null : im();}public Integer getBankType() {return bankType;}public User setBankType(Integer bankType) {this.bankType = bankType;return this;}/*** 获取 是否支持授信 字段:user.support_credit** @return user.support_credit, 是否支持授信*/public Integer getSupportCredit() {return supportCredit;}/*** 设置 是否支持授信 字段:user.support_credit** @param supportCredit the value for user.support_credit, 是否支持授信*/public void setSupportCredit(Integer supportCredit) {this.supportCredit = supportCredit;}public Integer getIsAuth() {return isAuth;}public void setIsAuth(Integer isAuth) {this.isAuth = isAuth;}/*** 获取 创建时间 字段:ated_date** @ated_date, 创建时间*/public Date getCreatedDate() {return createdDate;}/*** 设置 创建时间 字段:ated_date** @param createdDate the value ated_date, 创建时间*/public void setCreatedDate(Date createdDate) {atedDate = createdDate;}/*** 获取 修改时间 字段:dified_date** @dified_date, 修改时间*/public Date getModifiedDate() {return modifiedDate;}/*** 设置 修改时间 字段:dified_date** @param modifiedDate the value dified_date, 修改时间*/public void setModifiedDate(Date modifiedDate) {difiedDate = modifiedDate;}public String getContactCardType() {return contactCardType;}public void setContactCardType(String contactCardType) {actCardType = contactCardType;}public String getContactCardNo() {return contactCardNo;}public void setContactCardNo(String contactCardNo) {actCardNo = contactCardNo;}public String getContactFaceUrl() {return contactFaceUrl;}public void setContactFaceUrl(String contactFaceUrl) {actFaceUrl = contactFaceUrl;}public String getContactConUrl() {return contactConUrl;}public void setContactConUrl(String contactConUrl) {actConUrl = contactConUrl;}public String getContactUrl() {return contactUrl;}public void setContactUrl(String contactUrl) {actUrl = contactUrl;}public String getCompanyMold() {return companyMold;}public void setCompanyMold(String companyMold) {thispanyMold = companyMold;}public String getCompanyUrl() {return companyUrl;}public void setCompanyUrl(String companyUrl) {thispanyUrl = companyUrl;}public String getMailingAddress() {return mailingAddress;}public void setMailingAddress(String mailingAddress) {this.mailingAddress = mailingAddress;}public Integer getAcceptProtocol() {return acceptProtocol;}public String getRole() {return role;}public void setRole(String role) {le = role;}public User setAcceptProtocol(Integer acceptProtocol) {this.acceptProtocol = acceptProtocol;return this;}public Date getStartTime() {return startTime;}public User setStartTime(Date startTime) {this.startTime = startTime;return this;}public Integer getChannel() {return channel;}public void setChannel(Integer channel) {this.channel = channel;}public String getCompanyUserId() {return companyUserId;}public void setCompanyUserId(String companyUserId) {thispanyUserId = companyUserId;}public Integer getLdeleteFlag() {return ldeleteFlag;}public User setLdeleteFlag(Integer ldeleteFlag) {this.ldeleteFlag = ldeleteFlag;return this;}}

3.2、编写操作语句

在resources目录下，新建mybatis文件夹，新建表名.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis//DTD Mapper 3.0//EN" ".dtd">
<mapper namespace=&#apper.UserMapper">
<!--    type为上面定义的映射对象路径    --><resultMap id="BaseResultMap" type=&#User"><id column="id" jdbcType="BIGINT" property="id"/><result column="merchant_no" jdbcType="VARCHAR" property="merchantNo"/><result column="company_user_id" jdbcType="VARCHAR" property="companyUserId"/><result column="registration_no" jdbcType="VARCHAR" property="registrationNo"/><result column="organization_code" jdbcType="VARCHAR" property="organizationCode"/><result column="company_uscc" jdbcType="VARCHAR" property="companyUscc"/><result column="company_scope" jdbcType="VARCHAR" property="companyScope"/><result column="company_name" jdbcType="VARCHAR" property="companyName"/><result column="company_type" jdbcType="TINYINT" property="companyType"/><result column="role" jdbcType="VARCHAR" property="role"/><result column="company_mold" jdbcType="VARCHAR" property="companyMold"/><result column="company_url" jdbcType="VARCHAR" property="companyUrl"/><result column="province_name" jdbcType="VARCHAR" property="provinceName"/><result column="city_name" jdbcType="VARCHAR" property="cityName"/><result column="credit_score" jdbcType="DECIMAL" property="creditScore"/><result column="address" jdbcType="VARCHAR" property="address"/><result column="contact" jdbcType="VARCHAR" property="contact"/><result column="contact_tel" jdbcType="VARCHAR" property="contactTel"/><result column="contact_card_type" jdbcType="VARCHAR" property="contactCardType"/><result column="contact_card_no" jdbcType="VARCHAR" property="contactCardNo"/><result column="contact_face_url" jdbcType="VARCHAR" property="contactFaceUrl"/><result column="contact_con_url" jdbcType="VARCHAR" property="contactConUrl"/><result column="contact_url" jdbcType="VARCHAR" property="contactUrl"/><result column="crc_code" jdbcType="VARCHAR" property="crcCode"/><result column="bank_type" jdbcType="INTEGER" property="bankType"/><result column="support_credit" jdbcType="TINYINT" property="supportCredit"/><result column="is_auth" jdbcType="TINYINT" property="isAuth"/><result column="mailing_address" jdbcType="VARCHAR" property="mailingAddress"/><result column="accept_protocol" jdbcType="TINYINT" property="acceptProtocol"/><result column="start_time" jdbcType="TIMESTAMP" property="startTime"/><result column="created_date" jdbcType="TIMESTAMP" property="createdDate"/><result column="modified_date" jdbcType="TIMESTAMP" property="modifiedDate"/><result column="ldelete_flag" jdbcType="TINYINT" property="ldeleteFlag"/></resultMap><sql id="Base_Column_List">id, merchant_no, company_user_id, registration_no, company_name, company_type,role, province_name, city_name,address, contact, contact_tel, crc_code, bank_type, support_credit, is_auth, created_date,modified_date, company_mold, company_url,contact_card_type, contact_card_no,contact_face_url,contact_con_url,contact_url, organization_code,company_uscc,company_scope,mailing_address,accept_protocol,start_time,credit_score, ldelete_flag</sql><select id="findByMerchantNo" resultMap="BaseResultMap">select<include refid="Base_Column_List"/>from user where merchant_no = #{merchantNo} and ldelete_flag = 0</select></mapper>

3.3、定义Mapper接口

package xx.mapper;User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;
@Mapper
public interface UserMapper {/*** 根据商户号查询用户信息* @param merchantNo	商户号* @return	商户信息*/User findByMerchantNo(String merchantNo);}

 此处的接口名应与xml文件中select查询语句中的id一致。

3.4、测试

使用单元测试框架进行测试

@AutowiredUserMapper userMapper;
@Test
void test(){
userMapper.findByMerchantNo();
}

4、常见问题

4.1、spring Boot中mybatis是怎样自动获取DataSource的

在Spring Boot中，MyBatis的整合通常会依赖于Spring Boot的自动配置和注解驱动。MyBatis在Spring Boot中自动获取DataSource的过程主要涉及以下几个关键点：

依赖配置： 在（如果是Maven项目）中引入Spring Boot Starter和MyBatis的相关依赖。例如：

<dependencies><!-- Spring Boot Starter for MyBatis --><dependency><groupId&batis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.0</version> <!-- 版本号可能有更新 --></dependency><!-- 数据库驱动依赖 --><dependency><groupId>com.h2database</groupId><artifactId>h2</artifactId><scope>runtime</scope></dependency>
</dependencies>

数据源配置： 在application.properties或中配置数据源信息，Spring Boot会根据这些配置自动创建DataSource Bean。例如：

spring.datasource.url=jdbc:mysql://localhost:3306/mydatabase
spring.datasource.username=myuser
spring.datasource.password=mypassword
spring.datasource.driver-class-name&#sql.cj.jdbc.Driver

MyBatis配置： Spring Boot提供了自动配置，会根据依赖和配置自动创建SqlSessionFactory和SqlSessionTemplate。你可以在application.properties或中配置MyBatis相关属性，例如：

mybatis.mapper-locations=classpath:/mapper/*.xml

这里指定了MyBatis的Mapper文件所在的位置。

使用注解： 在MyBatis的Mapper接口上使用@Mapper注解，或者在Spring Boot的主应用程序类上使用@MapperScan注解扫描Mapper接口所在的包。

// 在主应用程序类上使用@MapperScan
@SpringBootApplication
@MapperScan(&#ample.mapper")
public class MyApplication {public static void main(String[] args) {SpringApplication.run(MyApplication.class, args);}
}

或者在Mapper接口上使用@Mapper注解：

@Mapper
public interface UserMapper {// ...
}

总的来说，Spring Boot会根据约定和配置自动创建数据源，并将其注入到MyBatis中，使得整合过程非常便捷。只需配置相关属性，使用注解标注Mapper接口，Spring Boot就能完成自动化的MyBatis整合