SpringBoot jdbcTemplate 多数据源

JAVA充电站

于 2024-09-11 11:48:38 发布

阅读量754

点赞数 17

文章标签： spring boot 后端 java

本文链接：https://blog.csdn.net/u011347171/article/details/142135897

版权

SpringBoot jdbcTemplate

JdbcTemplate简介

Spring对数据库的操作在jdbc上面做了深层次的封装，使用spring的注入功能，可以把DataSource注册到JdbcTemplate之中。JdbcTemplate位于spring-jdbc.jar中。其全限定命名为org.springframework.jdbc.core.JdbcTemplate。

JdbcTemplate主要提供以下五类方法

execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
query方法及queryForXXX方法：用于执行查询相关语句；
call方法：用于执行存储过程、函数相关语句。

JdbcTemplate使用（集成springboot）

引入jar

       <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

2.在yml配置文件中添加数据库连接
3.创建相关package包（pojo，mapper，dao，service，controller）
4.创建相关文件并编写代码

POJO实体类

public class SfUser {
    private Long user_id;
    private String user_name;
    private String user_password;
    private String user_class;
    private String user_addtime;
    private int user_state;
    ......
}

dao接口

public interface SfUserDao {
    //获取单个对象信息
    public SfUser getSfUserByUserId(Long user_id);
}

dao实现
@Repository("sfUserDao")
public class SfUserDaoImpl implements SfUserDao {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;//spring自动创建JdbcTemplate容器

    @Override
    public SfUser getSfUserByUserId(Long user_id) {
        String sql = "select * from sf_user where user_id=?";
        return  jdbcTemplate.queryForObject(sql,new Object[]{user_id},new BeanPropertyRowMapper<>(SfUser.class));
    }
}

//扫描com.hz下的包，使用注解
@SpringBootApplication(scanBasePackages ="com.hz")

获取list对象集合数据
对象集合（一）

在实体类中封装方法，用于返回对象集合
 
 public static SfUser toObject(Map map) {
        SfUser sfUser = new SfUser();
        sfUser.setUser_id((Long)map.get("user_id"));
        sfUser.setUser_name((String)map.get("user_name"));
        sfUser.setUser_state((int)map.get("user_state"));
        sfUser.setUser_addtime((Date)map.get("user_addtime"));
        return sfUser;
    }
    public static List toObject(List<Map<String, Object>> lists){
        List users = new ArrayList();
        for (Map map : lists) {
            SfUser userInfo = SfUser.toObject(map);
            if (userInfo != null) {
                users.add(userInfo);
            }
        }
        return users;
    }
    
    
  dao实现
  
  @Override
    public List<SfUser> getSfUserList() {
        String sql = "select * from sf_user";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return SfUser.toObject(maps);
    }
    
queryForList（sql，new Object[]{参数1，参数2}）

queryForList：返回集合queryForList（sql，new Object[]{}），但集合为 List<Map<String, Object>>需要转换为对象

对象集合（二）

使用内置泛型对象BeanPropertyRowMapper

dao实现
List<SfUser> sfUserList = jdbcTemplate.query(sql,new Object[]{user_sex},new BeanPropertyRowMapper<SfUser>(SfUser.class));

new BeanPropertyRowMapper<实体类>(实体类.class)来实现映射

获取统计数据

String sql="select count(*) from sf_user";

int count= jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(count);

修改数据

String sql="update sf_user set user_name=?,user_state=? where user_id=?";
jdbcTemplate.update(sql,new Object[]{"zhh",1,51});

添加数据

 String sql="delete from sf_user where user_id=?";
 jdbcTemplate.update(sql,51insert into user (name,deptid) values (?,?)";
int count= jdbcTemplate.update(sql, new Object[]{"caoyc",3});
System.out.println(count);

删除数据

 String sql="delete from sf_user where user_id=?";
 jdbcTemplate.update(sql,51);

对数据批量操作(添加，修改)

String sql="insert into user (name,deptid) values (?,?)";

List<Object[]> batchArgs=new ArrayList<Object[]>();
batchArgs.add(new Object[]{"caoyc",6});
batchArgs.add(new Object[]{"zhh",8});
batchArgs.add(new Object[]{"cjx",8});

jdbcTemplate.batchUpdate(sql, batchArgs);

自定义RowMapper
用于返回集合或返回单个对象时使用

public class SfUserRowMapper implements RowMapper<SfUser> {

    @Override
    public SfUser mapRow(ResultSet rs, int i) throws SQLException {
        SfUser sfUser = new SfUser();
        sfUser.setName(rs.getString("name"));
        sfUser.setGender(rs.getString("gender"));
        sfUser.setEmail(rs.getString("email"));
        return sfUser;
    }
}


使用自定义SfUserRowMapper
jdbcTemplate.queryForObject(sql,new Object[]{user_id},new SfUserRowMapper（）);

jdbcTemplate使用druid配置多数据源

pom.xml引入阿里巴巴druidjar

 <!-- druid数据源驱动 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>

application.properties文件配置

#数据源1
spring.datasource.druid.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.one.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.one.url=jdbc:mysql://localhost:3306/xxshop?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC
spring.datasource.druid.one.username=root
spring.datasource.druid.one.password=root

#数据源2
spring.datasource.druid.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.two.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.two.url=jdbc:mysql://localhost:3306/myschool?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC
spring.datasource.druid.two.username=root
spring.datasource.druid.two.password=root

创建DataSourceConfig类用于加载数据源（不需要spring自动加载）

@Configuration
public class DataSourceConfig {

    @Bean(name = "mysqldatasource1")
    @ConfigurationProperties("spring.datasource.druid.one")
    public DataSource dataSourceOne(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "mysqldatasource2")
    @ConfigurationProperties("spring.datasource.druid.two")
    public DataSource dataSourceTwo(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlJdbcTemplateOne")
    public JdbcTemplate primaryJdbcTemplate(
            @Qualifier("mysqldatasource1") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

   @Bean(name = "mysqlJdbcTemplateTwo")
    public JdbcTemplate secondaryJdbcTemplate(
            @Qualifier("mysqldatasource2") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

}

使用数据源1和2

在dao实现分别注入mysqlJdbcTemplateOne与mysqlJdbcTemplateTwo

    @Autowired
    @Qualifier("mysqlJdbcTemplateOne") //使用第一个数据源
    private JdbcTemplate jdbcTemplate;

    @Autowired
    @Qualifier("mysqlJdbcTemplateTwo") //使用第二个数据源
    private JdbcTemplate jdbcTemplate;

Mybatis引入多数据源与JdbcTemplate类似
1.引入jar
2.application.properties文件中编写数据源配置
3.创建数据源1

@Configuration
@MapperScan(basePackages = MysqlDatasourceConfigOne.PACKAGE, sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MysqlDatasourceConfigOne {

	//onedao扫描路径
	static final String PACKAGE = "com.springboot.onedao";
	// mybatis mapper扫描路径
	static final String MAPPER_LOCATION = "classpath:mapper/mysql/*.xml";

	@Primary
	@Bean(name = "mysqldatasourceone")
	@ConfigurationProperties("spring.datasource.druid.one")
	public DataSource mysqlDataSource() {
		return DruidDataSourceBuilder.create().build();
	}

	@Bean(name = "oneTransactionManager")
	@Primary
	public DataSourceTransactionManager mysqlTransactionManager() {
		return new DataSourceTransactionManager(mysqlDataSource());
	}

	@Bean(name = "oneSqlSessionFactory")
	@Primary
	public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqldatasourceone") DataSource dataSource)
			throws Exception {
		final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
		sessionFactory.setDataSource(dataSource);
		//如果不使用xml的方式配置mapper，则可以省去下面这行mapper location的配置。
		sessionFactory.setMapperLocations(
				new PathMatchingResourcePatternResolver().getResources(MysqlDatasourceConfigOne.MAPPER_LOCATION));
		return sessionFactory.getObject();
	}
}

4.创建数据源2

@Configuration
@MapperScan(basePackages = MysqlDatasourceConfigTwo.PACKAGE, sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MysqlDatasourceConfigTwo {

	// twodao扫描路径
	static final String PACKAGE = "com.springboot.twodao";
	// mybatis mapper扫描路径
	static final String MAPPER_LOCATION = "classpath:mapper/mysql/*.xml";

	@Primary
	@Bean(name = "mysqldatasourcetwo")
	@ConfigurationProperties("spring.datasource.druid.mysql")
	public DataSource mysqlDataSource() {
		return DruidDataSourceBuilder.create().build();
	}

	@Bean(name = "twoTransactionManager")
	@Primary
	public DataSourceTransactionManager mysqlTransactionManager() {
		return new DataSourceTransactionManager(mysqlDataSource());
	}

	@Bean(name = "twoSqlSessionFactory")
	@Primary
	public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqldatasourcetwo") DataSource dataSource)
			throws Exception {
		final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
		sessionFactory.setDataSource(dataSource);
		//如果不使用xml的方式配置mapper，则可以省去下面这行mapper location的配置。
		sessionFactory.setMapperLocations(
				new PathMatchingResourcePatternResolver().getResources(MysqlDatasourceConfigTwo.MAPPER_LOCATION));
		return sessionFactory.getObject();
	}
}