[mybatis]细节用法

独家记忆shine

已于 2024-08-06 11:51:19 修改

阅读量226

点赞数 1

文章标签： mybatis

于 2024-06-13 15:53:50 首次发布

本文链接：https://blog.csdn.net/baiducheng/article/details/139656575

版权

1: include的用法：
<sql id="FIELDS">
    f_id, f_name, f_nickname, f_loginname,f_password,f_idnumber
</sql>

<select id="selectById" parameterType="Integer" resultMap="userMap">
    SELECT
    <include refid="FIELDS" />
    FROM tc_sys_user
    WHERE f_id = #{f_id}
    LIMIT 1
</select>

二：映射mapper-locations 映射多个路径

参考：https://www.cnblogs.com/zhangmingcheng/p/12671704.html

方式一：application.yml

# mybatis 配置内容
mybatis:
  config-location: classpath:mybatis-config.xml
  mapper-locations: classpath:mapper/**/*.xml
  type-aliases-package: com.oigit.entity

方式二：代码：

@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource, ApplicationContext applicationContext) throws Exception {

    SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();

    factoryBean.setDataSource(dataSource);

    factoryBean.setMapperLocations(applicationContext.getResources("classpath:mapper/**/*.xml"));

    return factoryBean.getObject();

}

方式三：

mybatis.mapper-locations=classpath:mappers/push/*.xml,classpath*:/mappers/*.xml
注意点：在classpath后面的 * 必不可少，缺少型号的话后面的通配符不起作用。

**表示可以表示任意多级目录。

三：@MapperScan注解

MapperScan注解详解-CSDN博客

四：sql中的where 条件< 和 >

<![CDATA[<]]> 或者使用 &gt

五：多数据源的配置

https://www.cnblogs.com/guozhiqiang/p/16943315.html

1：pom.xml

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.7</version>
</dependency>

2: application.yml

spring:
# 数据源配置
datasource:
ds1: #数据源1
driver-class-name: com.mysql.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
url: jdbc:mysql://ip:3306/db1 #数据源地址
username: root # 用户名
password: root # 密码
ds2: # 数据源2
driver-class-name: com.mysql.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
url: jdbc:mysql://ip:3307/db2#数据源地址
username: root # 用户名
password: root # 密码

3:Mybatis主数据源ds1配置：
/**
* Mybatis主数据源ds1配置
* 多数据源配置依赖数据源配置
* @see DataSourceConfig
*/
@Configuration
@MapperScan(basePackages ="com.web.ds1.**.dao", sqlSessionTemplateRef = "ds1SqlSessionTemplate")
public class MybatisPlusConfig4ds1 {

@Bean(name = "dataSource1")
@ConfigurationProperties(prefix = "spring.datasource.ds1")
@Primary
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}

//主数据源 ds1数据源
@Primary
@Bean("ds1SqlSessionFactory")
public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("ds1DataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource);
sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
getResources("classpath*:com/web/ds1/**/*.xml"));
return sqlSessionFactory.getObject();
}

@Primary
@Bean(name = "ds1TransactionManager")
public DataSourceTransactionManager ds1TransactionManager(@Qualifier("ds1DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}

@Primary
@Bean(name = "ds1SqlSessionTemplate")
public SqlSessionTemplate ds1SqlSessionTemplate(@Qualifier("ds1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}

}

4:Mybatis主数据源ds2配置：

/**
* Mybatis 第二个ds2数据源配置
* 多数据源配置依赖数据源配置
* @see DataSourceConfig
*/
@Configuration
@MapperScan(basePackages ="com.web.ds2.**.dao", sqlSessionTemplateRef = "ds2SqlSessionTemplate")
public class MybatisPlusConfig4ds2 {

@Bean(name = "dataSource2")
@ConfigurationProperties(prefix = "spring.datasource.ds2")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}

//ds2数据源
@Bean("ds2SqlSessionFactory")
public SqlSessionFactory ds2SqlSessionFactory(@Qualifier("ds2DataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource);
sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().
getResources("classpath*:com/web/ds2/**/*.xml"));
return sqlSessionFactory.getObject();
}

//事务支持
@Bean(name = "ds2TransactionManager")
public DataSourceTransactionManager ds2TransactionManager(@Qualifier("ds2DataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}

@Bean(name = "ds2SqlSessionTemplate")
public SqlSessionTemplate ds2SqlSessionTemplate(@Qualifier("ds2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}

}

5:使用的地方

@Service
public class TestService {

@Resource
private ClusterMapper clusterMapper;
@Resource
private MasterMapper masterMapper;

public List<HashMap<String, Object>> queryBooks() {
return masterMapper.queryBooks(); //指定的配置类扫描的是一个包
}

public List<HashMap<String, Object>> queryOrders() {
return clusterMapper.queryOrders(); //指定的配置类扫描的是另一个包
}
}

6: mybatis 批量插入

<insert id="insertBatchDetail">
    insert into ${tableName} (
    Id,
    Name,
    Liandate,
    Anno,
    ExecuteGov,
    Biaodi,
    PartyCardNum,
    SearchCompanyName,
    SearchCreditCode,
    etl_creator,
    etl_updator,
    etl_start_date,
    etl_end_date,
    etl_date,
    etl_flag,
    remark,
    data_source
    )
    values
    <foreach collection="entities" item="item" index="index" separator=",">
      ( #{item.Id},
        #{item.Name},
        #{item.Liandate},
        #{item.Anno},
        #{item.ExecuteGov},
        #{item.Biaodi},
        #{item.PartyCardNum},
        #{item.SearchCompanyName},
        #{item.SearchCreditCode},
        #{item.etl_creator},
        #{item.etl_updator},
        #{item.etl_start_date},
        #{item.etl_end_date},
        #{item.etl_date},
        #{item.etl_flag},
        #{item.remark},
        #{item.data_source}
        )
    </foreach>
</insert>

7：单条插入

<insert id="insertOne"   useGeneratedKeys="true" keyProperty="entities.id">
    insert into ${tableName} (key_no,credit_code,company_name,parent_company_key_no,parent_company_name,parent_company_oper_name,parent_company_regist_capi,parent_company_start_date,parent_company_status,etl_creator,etl_updator,etl_start_date,etl_end_date,etl_date,etl_flag,remark,data_source)
    values
        (
        #{entities.key_no},
        #{entities.credit_code},
        #{entities.company_name},
        #{entities.parent_company_key_no},
        #{entities.parent_company_name},
        #{entities.parent_company_oper_name},
        #{entities.parent_company_regist_capi},
        #{entities.parent_company_start_date},
        #{entities.parent_company_status},
        #{entities.etl_creator},
        #{entities.etl_updator},
        #{entities.etl_start_date},
        #{entities.etl_end_date},
        #{entities.etl_date},
        #{entities.etl_flag},
        #{entities.remark},
        #{entities.data_source}
        )
</insert>

<select id="selectIdStringList" resultType="java.lang.String">

    select ${KeyName} from ${tableName} where ${columnName1}=#{columnValue1} and   ${columnName2}=#{columnValue2}

</select>