一.问题的产生:
通常我们的数据库字段命名会习惯使用下划线,如:user_type。而我们的java类的成员变量命名习惯性小驼峰,如:userType。
当我们使用mybatis操作数据库时,期望它能够自动帮我们注入返回结果类。此时我们会在mybatis配置中开启驼峰下划线转换。
# MyBatis配置
mybatis:
# 驼峰下划线转换
map-underscore-to-camel-case: true
只有开启了驼峰下划线转换转换之后,mybatis才可以知道数据库字段与java类成员变量的对应关系,才能帮我们自动注入。
但有时开启了之后,好像并不能起作用。
二.若依平台驼峰下划线转换失效的场景
在若依中已经开启了mybatis驼峰下划线转换,但依然不起作用。
例如有一张表是这样的
又有这样一个类,已经写了get、set方法。且没有集合的嵌套,所以处理mybatis的时候不需要手动结果映射。
public class TicketSend extends BaseEntity {
private static final long serialVersionUID = 1L;
/**
* 工单编码
*/
@Excel(name = "工单编码")
private Long id;
/**
* 工单名称
*/
@Excel(name = "工单名称")
private String ticketName;
/**
* 工单内容
*/
@Excel(name = "工单内容")
private String ticketContent;
/**
* 是否派单(0:否,1:是)
*/
@Excel(name = "是否派单", readConverterExp = "0=:否,1:是")
private Long isDispatch;
private String dispatchIs;
/**
* 接单人
*/
@Excel(name = "接单人")
private Long dispatchPreId;
private String dispatchPreName;
public String getDispatchPreName() {
return dispatchPreName;
}
public void setDispatchPreName(String dispatchPreName) {
this.dispatchPreName = dispatchPreName;
}
/**
* 是否回复(0:否,1:是)
*/
@Excel(name = "是否回复", readConverterExp = "0=:否,1:是")
private Long isReply;
private String replyIs;
public String getDispatchIs() {
return dispatchIs;
}
public void setDispatchIs(String dispatchIs) {
this.dispatchIs = dispatchIs;
}
public String getReplyIs() {
return replyIs;
}
public void setReplyIs(String replyIs) {
this.replyIs = replyIs;
}
/**
* 回复内容
*/
@Excel(name = "回复内容")
private String replyContent;
/**
* 回复图片
*/
@Excel(name = "回复图片")
private String replyImg;
public void setId(Long id) {
this.id = id;
}
public Long getId() {
return id;
}
public void setTicketName(String ticketName) {
this.ticketName = ticketName;
}
public String getTicketName() {
return ticketName;
}
public void setTicketContent(String ticketContent) {
this.ticketContent = ticketContent;
}
public String getTicketContent() {
return ticketContent;
}
public void setIsDispatch(Long isDispatch) {
this.isDispatch = isDispatch;
}
public Long getIsDispatch() {
return isDispatch;
}
public void setDispatchPreId(Long dispatchPreId) {
this.dispatchPreId = dispatchPreId;
}
public Long getDispatchPreId() {
return dispatchPreId;
}
public void setIsReply(Long isReply) {
this.isReply = isReply;
}
public Long getIsReply() {
return isReply;
}
public void setReplyContent(String replyContent) {
this.replyContent = replyContent;
}
public String getReplyContent() {
return replyContent;
}
public void setReplyImg(String replyImg) {
this.replyImg = replyImg;
}
public String getReplyImg() {
return replyImg;
}
@Override
public String toString() {
return new ToStringBuilder(this, ToStringStyle.MULTI_LINE_STYLE)
.append("id", getId())
.append("ticketName", getTicketName())
.append("ticketContent", getTicketContent())
.append("isDispatch", getIsDispatch())
.append("dispatchPreId", getDispatchPreId())
.append("isReply", getIsReply())
.append("replyContent", getReplyContent())
.append("replyImg", getReplyImg())
.append("dispatchPreName", getDispatchPreName())
.append("dispatchIs", getDispatchIs())
.append("replyIs", getReplyIs())
.toString();
}
}
mapper.xml这样写
<select id="selectTicketSendListById" resultType="com.ruoyi.system.domain.TicketSend">
select ts.*,su.nick_name as nickName
from ticket_send ts
left join sys_user su on su.user_id=ts.dispatch_pre_id
<where>
ts.dispatch_pre_id=#{userId}
<if test="ticketSend.id != null "> and ts.id like concat('%', #{ticketSend.id},'%')</if>
<if test="ticketSend.ticketName != null and ticketSend.ticketName != ''"> and ts.ticket_name like concat('%', #{ticketSend.ticketName}, '%')</if>
<if test="ticketSend.isReply != null "> and ts.is_reply like concat('%', #{ticketSend.isReply}, '%')</if>
</where>
</select>
按道理已经开了驼峰下划线转换,并且java类的字段与数据库字段有驼峰与下划线的对应关系,虽然我查了*,但是mybatis应该是可以帮我自动注入返回结果的。
然而前端接收的数据几乎全为null。
显然驼峰转下划线失效了
三.失效原因分析
可以在配置文件里看到若依是支持多数据源的
# 数据源配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
druid:
# 主库数据源
master:
url: jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username:
password:
# 从库数据源
slave:
# 从数据源开关/默认关闭
enabled: false
url:
username:
password:
# 初始连接数
initialSize: 5
# 最小连接池数量
minIdle: 10
# 最大连接池数量
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置连接超时时间
connectTimeout: 30000
# 配置网络超时时间
socketTimeout: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis: 900000
# 配置检测连接是否有效
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
webStatFilter:
enabled: true
statViewServlet:
enabled: true
# 设置白名单,不填则允许所有访问
allow:
url-pattern: /druid/*
# 控制台管理用户名和密码
login-username: ruoyi
login-password: 123456
filter:
stat:
enabled: true
# 慢SQL记录
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
当在Spring Boot项目中配置多数据源时,驼峰下划线转换可能会失效,主要是由于多数据源配置和MyBatis的自动映射机制之间的交互问题。
如果使用了多数据源,我们通常会为每个数据源定义SqlSessionFactory
。这些自定义的SqlSessionFactory
有可能不会加载或者错误使用application.yml
文件中针对MyBatis的全局配置。因为全局配置它也不知道配置哪一个数据源。不同的数据源配置可能会相互覆盖或冲突。
以下是若依的多数据源配置类,在com.ruoyi.framework.config;包下
/**
* Mybatis支持*匹配扫描包
*
* @author ruoyi
*/
@Configuration
public class MyBatisConfig
{
@Autowired
private Environment env;
static final String DEFAULT_RESOURCE_PATTERN = "**/*.class";
public static String setTypeAliasesPackage(String typeAliasesPackage)
{
ResourcePatternResolver resolver = (ResourcePatternResolver) new PathMatchingResourcePatternResolver();
MetadataReaderFactory metadataReaderFactory = new CachingMetadataReaderFactory(resolver);
List<String> allResult = new ArrayList<String>();
try
{
for (String aliasesPackage : typeAliasesPackage.split(","))
{
List<String> result = new ArrayList<String>();
aliasesPackage = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX
+ ClassUtils.convertClassNameToResourcePath(aliasesPackage.trim()) + "/" + DEFAULT_RESOURCE_PATTERN;
Resource[] resources = resolver.getResources(aliasesPackage);
if (resources != null && resources.length > 0)
{
MetadataReader metadataReader = null;
for (Resource resource : resources)
{
if (resource.isReadable())
{
metadataReader = metadataReaderFactory.getMetadataReader(resource);
try
{
result.add(Class.forName(metadataReader.getClassMetadata().getClassName()).getPackage().getName());
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
}
}
}
if (result.size() > 0)
{
HashSet<String> hashResult = new HashSet<String>(result);
allResult.addAll(hashResult);
}
}
if (allResult.size() > 0)
{
typeAliasesPackage = String.join(",", (String[]) allResult.toArray(new String[0]));
}
else
{
throw new RuntimeException("mybatis typeAliasesPackage 路径扫描错误,参数typeAliasesPackage:" + typeAliasesPackage + "未找到任何包");
}
}
catch (IOException e)
{
e.printStackTrace();
}
return typeAliasesPackage;
}
public Resource[] resolveMapperLocations(String[] mapperLocations)
{
ResourcePatternResolver resourceResolver = new PathMatchingResourcePatternResolver();
List<Resource> resources = new ArrayList<Resource>();
if (mapperLocations != null)
{
for (String mapperLocation : mapperLocations)
{
try
{
Resource[] mappers = resourceResolver.getResources(mapperLocation);
resources.addAll(Arrays.asList(mappers));
}
catch (IOException e)
{
// ignore
}
}
}
return resources.toArray(new Resource[resources.size()]);
}
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception
{
String typeAliasesPackage = env.getProperty("mybatis.typeAliasesPackage");
String mapperLocations = env.getProperty("mybatis.mapperLocations");
String configLocation = env.getProperty("mybatis.configLocation");
typeAliasesPackage = setTypeAliasesPackage(typeAliasesPackage);
VFS.addImplClass(SpringBootVFS.class);
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setTypeAliasesPackage(typeAliasesPackage);
sessionFactory.setMapperLocations(resolveMapperLocations(StringUtils.split(mapperLocations, ",")));
sessionFactory.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));
return sessionFactory.getObject();
}
}
四.解决方案
方案一:
不嫌麻烦的话可以给每个字段起别名,这种方案通用性比较差
<select id="selectTicketSendListById" resultType="com.ruoyi.system.domain.TicketSend">
select
ts.id as id,
ts.ticket_name as ticketName,
ts.ticket_content as ticketContent,
ts.is_dispatch as isDispatch,
ts.dispatch_pre_id as dispatchPreId,
ts.is_reply AS isReply,
ts.reply_content as replyContent,
ts.reply_img as replyImg,
su.nick_name as dispatchPreName
from ticket_send ts
left join sys_user su on su.user_id=ts.dispatch_pre_id
<where>
ts.dispatch_pre_id=#{userId}
<if test="ticketSend.id != null "> and ts.id like concat('%', #{ticketSend.id}, '%')</if>
<if test="ticketSend.ticketName != null and ticketSend.ticketName != ''"> and ts.ticket_name like concat('%', #{ticketSend.ticketName}, '%')</if>
<if test="ticketSend.isReply != null "> and ts.is_reply like concat('%', #{ticketSend.isReply}, '%')</if>
</where>
</select>
方案二:
还不嫌麻烦的话,可以使用resultMap手动结果映射,这种方案通用性也比较差。
手动结果映射使用方法
<resultMap id="floorResultMapWithRoomAndBed" type="com.zzyl.vo.FloorVo">//id是这个resultMap的标识,type是使用这个resultMap将返回的结构类型
<result column="name" property="name"/>//column是数据库查询返回的字段,property是映射结果字段
<collection property="roomVoList" ofType="com.zzyl.vo.RoomVo">//collection标签表示嵌套的集合
<id property="id" column="rid"/>//id标签里映射id,其他字段用result标签映射
<collection property="bedVoList" ofType="com.zzyl.vo.BedVo">
<result column="bed_number" property="bedNumber"/>
</collection>
</collection>
</resultMap>
#sql语句标签不再写resultType,而是resultMap
<select id="selectAllRoomAndBed" resultMap="floorResultMapWithRoomAndBed">
select
f.name
r.id as rid,
b.bed_number,
from floor f
left join room r on r.floor_id = f.id
left join bed b on b.room_id = r.id
</select>
针对于刚才提到的数据库表和类即为
<resultMap id="ticketSendResultMap" type="com.ruoyi.system.domain.TicketSend">
<id property="id" column="id" />
<result property="ticketName" column="ticket_name" />
<result property="ticketContent" column="ticket_content"/>
<result property="isDispatch" column="is_dispatch" />
<result property="dispatchPreId" column="dispatch_pre_id" />
<result property="isReply" column="is_reply" />
<result property="replyContent" column="reply_content" />
<result property="replyImg" column="reply_img" />
</resultMap>
<select id="selectTicketSendListById" resultMap="ticketSendResultMap">
#sql省略
</select>
方案三:
①配置文件里的驼峰下划线转换依然开启
②在若依的多数据源配置类中新增配置
@Bean
@ConfigurationProperties(prefix = "mybatis")
public org.apache.ibatis.session.Configuration globalConfiguration() {
return new org.apache.ibatis.session.Configuration();
}
③把刚写的配置加入SqlSessionFactory
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception
{
String typeAliasesPackage = env.getProperty("mybatis.typeAliasesPackage");
String mapperLocations = env.getProperty("mybatis.mapperLocations");
String configLocation = env.getProperty("mybatis.configLocation");
typeAliasesPackage = setTypeAliasesPackage(typeAliasesPackage);
VFS.addImplClass(SpringBootVFS.class);
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setTypeAliasesPackage(typeAliasesPackage);
sessionFactory.setMapperLocations(resolveMapperLocations(StringUtils.split(mapperLocations, ",")));
//sessionFactory.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));注释掉若依的配置
sessionFactory.setConfiguration(globalConfiguration());//加入我们刚写的配置
return sessionFactory.getObject();
}
方案四:
在若依的多数据源配置类的sqlSessionFactory 返回的 bean 中设置驼峰
sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
设置驼峰后返回sqlSessionFactory
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception
{
String typeAliasesPackage = env.getProperty("mybatis.typeAliasesPackage");
String mapperLocations = env.getProperty("mybatis.mapperLocations");
String configLocation = env.getProperty("mybatis.configLocation");
typeAliasesPackage = setTypeAliasesPackage(typeAliasesPackage);
VFS.addImplClass(SpringBootVFS.class);
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setTypeAliasesPackage(typeAliasesPackage);
sessionFactory.setMapperLocations(resolveMapperLocations(StringUtils.split(mapperLocations, ",")));
sessionFactory.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));
sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);//设置驼峰
return sessionFactory.getObject();
}
参考: