1. 首先在application中配置数据源地址
my.datasource.koi.type=com.alibaba.druid.pool.DruidDataSource
my.datasource.koi.url=jdbc:mysql://localhost:3306/?useUnicode=true&characterEncoding=utf-8&useSSL=true
my.datasource.koi.username=root
my.datasource.koi.password=123456
my.datasource.koi.driverClassName=com.mysql.jdbc.Driver
2. 在@Bean中 new DataSource的地址空间
@Configuration
public class KOIDataSourceConfig { @Resource
private DataSourceFactory dataSourceFactory; @Bean(name = "koiDataSource")
@ConfigurationProperties(prefix = "my.datasource.koi")
public DruidDataSource createDataSource() {
return dataSourceFactory.create();
}
// dataSourceFactory.create() : DruidDataSource dataSource = new DruidDataSource(); @Bean(name = "jdbcTemplate")
public JdbcTemplate createSqlSessionTemplate(@Qualifier("koiDataSource") DruidDataSource druidDataSource) {
return new JdbcTemplate(druidDataSource);
}
}
3. 在Service中直接使用即可
@Service
public class KOISchedulerImpl implements KOIScheduler{ @Resource
private JdbcTemplate jdbcTemplate; @Transactional
@Override
public void set_task_and_run(SQLPool pool, String config_path) { ExecuteSQL.prepare_and_execut_sql(pool, config_path); } @Transactional
@Override
public void startSchedulerTaskByDruid(String configPath) {
jdbcTemplate.execute("INSERT INTO mydata.`test` (id) VALUE(1);");
}
}
读取application中的配置信息:
1. 在application中配置 spring.sql.path = E://z 2. 定义一个Component类 @Component
public class QualityRecode { @Value("${spring.sql.path}")
private String sqlPath; public QualityRecode() {} public String getSqlPath() {
return sqlPath;
} public void setSqlPath(String sqlPath) {
this.sqlPath = sqlPath;
}
} 3. 在Controller中实例化该类， 获取数据 @Resource
private QualityRecode recode; recode.getSqlPath()
4. 使用Mapper读取 写入数据
1. 先在application配置数据源信息 2. 配置数据源config类 @Configuration
@MapperScan(basePackages = "com.nio.mapper.dcs", sqlSessionTemplateRef = "dcsSqlSessionTemplate")
public class DcsDataSourceConfig { @Resource
private DataSourceFactory dataSourceFactory; @Bean(name = "dcsDataSource")
@ConfigurationProperties(prefix = "my.datasource.dcs")
public DruidDataSource createDataSource() {
return dataSourceFactory.create();
} @Bean(name = "dcsSqlSessionFactory")
public SqlSessionFactory createSqlSessionFactory(@Qualifier("dcsDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/dcs/*.xml"));
return bean.getObject();
} // @Bean(name = "cmsTransactionManager")
// public DataSourceTransactionManager createTransactionManager(@Qualifier("cmsDataSource") DataSource dataSource) {
// return new DataSourceTransactionManager(dataSource);
// } @Bean(name = "dcsSqlSessionTemplate")
public SqlSessionTemplate createSqlSessionTemplate(@Qualifier("dcsSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
} 3. 创建实体类 接收表传来的数据 4. 编辑Mapper类， 创建查询-插入方法 public interface CmsMapper {
List<PostEntity> getPostEntityList();
List<PostEntity> getLatestPostEntityList(@Param("yesterdayTimeStamp") Long yesterdayTimeStamp,
@Param("currentTimeStamp") Long currentTimeStamp); void insertError(@Param("TaskLogRecordEntity") List<TaskLogRecord> taskLogRecords);
} 5. 实现Mapper类 <select id="getPostEntityList" resultType="com.nio.entities.PostEntity">
SELECT
up.account_id AS accountId,
up.create_time AS updateTime,
up.content,
up.like_count AS likeCount,
up.comment_count AS commentCount,
lu.`name` AS nickName
FROM
lifestyle_prod.user_post up
LEFT JOIN lifestyle_prod.livestream_user lu ON up.account_id = lu.user_id
WHERE
up.STATUS = 1
AND up.resource_type = 'user_post'
AND lu.supplier = 'rongyun'
AND up.create_time >= #{yesterdayTimeStamp}
AND up.create_time <![CDATA[<=]]> #{currentTimeStamp}
</select> <insert id="insertError">
insert into `task_log_record`
(
id,task_type,execute_time,error_info
)
values
<foreach collection="TaskLogRecordEntity" item="error" separator=",">
(
#{error.id},
#{error.task_type},
#{error.execute_time},
#{error.error_info}
)
</foreach>
</insert>
6. 需要注意的问题:
1. 当查询一个原表中不存在， 但需要写在中间实体的， 指标查询表时：
例如， MySQL 该表只有一个字段 id , 在计算之后， 生成
public class TestEntity {
private long id2;
private long id3;
}
Mapper应写成:
<select id="getTest" resultType="com.nio.entities.TestEntity">
select t.id + 3 id2, t.id +1 id3 from
(select 2 id from test) t
</select>
## select 的值， 必须与实体类名一致， 而不是表中字段的名;