1. 接受到前端输入的一批ID ,然后去查询这批ID 的记录
ArrayList<String> getScopeId_FId(@Param(value = "textValue") String textValue);
select b.FId from ( select arrayJoin(splitByChar(',', ${textValue} )) as FId ) a join datahouse.basic_info b on a.FId = b.FId
2. 如果where 后面有可能为空 一般会加上 where 1=1
也可以用 where标签 : <where> </where>
3. #{} 是预编译处理,${}是字符串替换, 创建table 或者是批量insert数据时候用字符串替换,不然会自动加上引号
insert into ${tableName} (Cd,Ue) values <foreach collection="list" item="item" open="(" close=")" separator="),("> ${item[0]}, ${item[1]} </foreach>
4. 当实体类中的属性名字和表中字段名字不一致时:查询的时候给表中字段名取别名跟类属性名一直, 或者通过映射字段名和实体类属性名一一对应。
<resultMap id="BaseResultMap" type="domain.entity.PEntity"> <result column="FTeam" jdbcType="VARCHAR" property="fTeam"/> </resultMap>
5. 使用RowBounds 对象进行分页,是针对ResultSet结果执行的内存分页而非物理分页
6.mapper 中如何传递多个参数?searchUser(string userName, String userId) -> 可以用下标 #{0} ,#{1} , 也可以用注解 :searchUser(@Param string userName,@Param String userId) -> #{userName}, #{userId} . 还可以封装成一个map
7. mybatis 提供了9种动态sql标签: trim、where、set、foreach、if、choose、when、 otherwise、bind select、insert、update、delete
8. 多种数据库连接(sqlservice、clickhouse...)
需要分别配置 config 对应 basePackages :
package base.config;
import com.alibaba.druid.pool.DruidDataSource;
import utils.EnvUtil;
......
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "mapper.mc",
sqlSessionTemplateRef = "mcSessionTemplate")
public class mcDatasourceConfig {
@Value("${spring.datasource..driverClassName}")
private String driverClassName;mc
@Value("${spring.datasource.mc.url}")
private String url;
@Value("${spring.datasource.mc.username}")
private String userName;
@Value("${spring.datasource.mc.password}")
private String password;
@Value("${spring.datasource.mc.initialSize}")
private Integer initialSize;
@Value("${spring.datasource.mc.maxActive}")
private Integer maxActive;
@Value("${spring.datasource.mc.minIdle}")
private Integer minIdle;
@Value("${spring.datasource.mc.maxWait}")
private Integer maxWait;
@Value("${spring.datasource.mc.mapperLocation}")
private String mapperLocation;
@Value("${spring.datasource.mc.secretManegerName}")
private String secretManegerName;
@Bean("mc_database")
public DataSource dataSource() {
System.out.println("Call dataSource );
if(null==System.getenv("spring.datasource.mc.driverClassName")){
EnvUtil.setenv("spring.datasource.mc.driverClassName", driverClassName);
EnvUtil.setenv("spring.datasource.mc.url", url);
EnvUtil.setenv("spring.datasource.mc.username", userName);
EnvUtil.setenv("spring.datasource.mc.password", password);
}
System.out.println(secretManegerName);
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setDriverClassName(driverClassName);
if(System.getenv("spring.profiles.active").equals("local")){
datasource.setUsername(userName);
datasource.setPassword(password);
}else{
datasource.setUsername(System.getenv(userName));
datasource.setPassword(System.getenv(password));
}
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
return datasource;
}
@Bean(name="mcSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("mc_database") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocation));
return bean.getObject();
}
@Bean(name="mcTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("mc_database") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name="mcSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("mcSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}