[关闭]
@wangzhuanyun 2023-04-28T09:48:35.000000Z 字数 7688 阅读 922

SpringBoot jdbcTemplate（二）

springboot

JdbcTemplate简介

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

JdbcTemplate主要提供以下五类方法

JdbcTemplate使用（集成springboot）

  1. 引入jar
  1.        <dependency>
  2.             <groupId>org.springframework.boot</groupId>
  3.             <artifactId>spring-boot-starter-jdbc</artifactId>
  4.         </dependency>

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

  2. POJO实体类
  4. public class SfUser {
  5.     private Long user_id;
  6.     private String user_name;
  7.     private String user_password;
  8.     private String user_class;
  9.     private String user_addtime;
  10.     private int user_state;
  11.     ......
  12. }
  14. dao接口
  16. public interface SfUserDao {
  17.     //获取单个对象信息
  18.     public SfUser getSfUserByUserId(Long user_id);
  19. }
  21. dao实现
  22. @Repository("sfUserDao")
  23. public class SfUserDaoImpl implements SfUserDao {
  25.     @Autowired
  26.     private JdbcTemplate jdbcTemplate;//spring自动创建JdbcTemplate容器
  28.     @Override
  29.     public SfUser getSfUserByUserId(Long user_id) {
  30.         String sql = "select * from sf_user where user_id=?";
  31.         return  jdbcTemplate.queryForObject(sql,new Object[]{user_id},new BeanPropertyRowMapper<>(SfUser.class));
  32.     }
  33. }
  35. //扫描com.hz下的包，使用注解
  36. @SpringBootApplication(scanBasePackages ="com.hz")

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

  1. 在实体类中封装方法，用于返回对象集合
  3.  public static SfUser toObject(Map map) {
  4.         SfUser sfUser = new SfUser();
  5.         sfUser.setUser_id((Long)map.get("user_id"));
  6.         sfUser.setUser_name((String)map.get("user_name"));
  7.         sfUser.setUser_state((int)map.get("user_state"));
  8.         sfUser.setUser_addtime((Date)map.get("user_addtime"));
  9.         return sfUser;
  10.     }
  11.     public static List toObject(List<Map<String, Object>> lists){
  12.         List users = new ArrayList();
  13.         for (Map map : lists) {
  14.             SfUser userInfo = SfUser.toObject(map);
  15.             if (userInfo != null) {
  16.                 users.add(userInfo);
  17.             }
  18.         }
  19.         return users;
  20.     }
  23.   dao实现
  25.   @Override
  26.     public List<SfUser> getSfUserList() {
  27.         String sql = "select * from sf_user";
  28.         List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
  29.         return SfUser.toObject(maps);
  30.     }
  32. queryForList（sql，new Object[]{参数1，参数2}）
  34. queryForList：返回集合queryForList（sql，new Object[]{}），但集合为 List<Map<String, Object>>需要转换为对象

对象集合（二）

  1. 使用内置泛型对象BeanPropertyRowMapper
  3. dao实现
  4. List<SfUser> sfUserList = jdbcTemplate.query(sql,new Object[]{user_sex},new BeanPropertyRowMapper<SfUser>(SfUser.class));
  6. new BeanPropertyRowMapper<实体类>(实体类.class)来实现映射

获取统计数据

  1. String sql="select count(*) from sf_user";
  3. int count= jdbcTemplate.queryForObject(sql, Integer.class);
  4. System.out.println(count);

修改数据

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

添加数据

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

删除数据

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

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

  1. String sql="insert into user (name,deptid) values (?,?)";
  3. List<Object[]> batchArgs=new ArrayList<Object[]>();
  4. batchArgs.add(new Object[]{"caoyc",6});
  5. batchArgs.add(new Object[]{"zhh",8});
  6. batchArgs.add(new Object[]{"cjx",8});
  8. jdbcTemplate.batchUpdate(sql, batchArgs);

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

  1. public class SfUserRowMapper implements RowMapper<SfUser> {
  3.     @Override
  4.     public SfUser mapRow(ResultSet rs, int i) throws SQLException {
  5.         SfUser sfUser = new SfUser();
  6.         sfUser.setName(rs.getString("name"));
  7.         sfUser.setGender(rs.getString("gender"));
  8.         sfUser.setEmail(rs.getString("email"));
  9.         return sfUser;
  10.     }
  11. }
  14. 使用自定义SfUserRowMapper
  15. jdbcTemplate.queryForObject(sql,new Object[]{user_id},new SfUserRowMapper（）);

jdbcTemplate使用druid配置多数据源

pom.xml引入阿里巴巴druidjar

  1.  <!-- druid数据源驱动 -->
  2. <dependency>
  3.     <groupId>com.alibaba</groupId>
  4.     <artifactId>druid-spring-boot-starter</artifactId>
  5.     <version>1.1.10</version>
  6. </dependency>

application.properties文件配置

  1. #数据源1
  2. spring.datasource.druid.one.type=com.alibaba.druid.pool.DruidDataSource
  3. spring.datasource.druid.one.driver-class-name=com.mysql.cj.jdbc.Driver
  4. spring.datasource.druid.one.url=jdbc:mysql://localhost:3306/xxshop?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC
  5. spring.datasource.druid.one.username=root
  6. spring.datasource.druid.one.password=root
  8. #数据源2
  9. spring.datasource.druid.two.type=com.alibaba.druid.pool.DruidDataSource
  10. spring.datasource.druid.two.driver-class-name=com.mysql.cj.jdbc.Driver
  11. spring.datasource.druid.two.url=jdbc:mysql://localhost:3306/myschool?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC
  12. spring.datasource.druid.two.username=root
  13. spring.datasource.druid.two.password=root

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

  1. @Configuration
  2. public class DataSourceConfig {
  4.     @Bean(name = "mysqldatasource1")
  5.     @ConfigurationProperties("spring.datasource.druid.one")
  6.     public DataSource dataSourceOne(){
  7.         return DruidDataSourceBuilder.create().build();
  8.     }
  10.     @Bean(name = "mysqldatasource2")
  11.     @ConfigurationProperties("spring.datasource.druid.two")
  12.     public DataSource dataSourceTwo(){
  13.         return DruidDataSourceBuilder.create().build();
  14.     }
  16.     @Bean(name = "mysqlJdbcTemplateOne")
  17.     public JdbcTemplate primaryJdbcTemplate(
  18.             @Qualifier("mysqldatasource1") DataSource dataSource) {
  19.         return new JdbcTemplate(dataSource);
  20.     }
  22.    @Bean(name = "mysqlJdbcTemplateTwo")
  23.     public JdbcTemplate secondaryJdbcTemplate(
  24.             @Qualifier("mysqldatasource2") DataSource dataSource) {
  25.         return new JdbcTemplate(dataSource);
  26.     }
  28. }

使用数据源1和2

  1. 在dao实现分别注入mysqlJdbcTemplateOne与mysqlJdbcTemplateTwo
  3.     @Autowired
  4.     @Qualifier("mysqlJdbcTemplateOne") //使用第一个数据源
  5.     private JdbcTemplate jdbcTemplate;
  7.     @Autowired
  8.     @Qualifier("mysqlJdbcTemplateTwo") //使用第二个数据源
  9.     private JdbcTemplate jdbcTemplate;

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

  1. @Configuration
  2. @MapperScan(basePackages = MysqlDatasourceConfigOne.PACKAGE, sqlSessionFactoryRef = "mysqlSqlSessionFactory")
  3. public class MysqlDatasourceConfigOne {
  5.     //onedao扫描路径
  6.     static final String PACKAGE = "com.springboot.onedao";
  7.     // mybatis mapper扫描路径
  8.     static final String MAPPER_LOCATION = "classpath:mapper/mysql/*.xml";
  10.     @Primary
  11.     @Bean(name = "mysqldatasourceone")
  12.     @ConfigurationProperties("spring.datasource.druid.one")
  13.     public DataSource mysqlDataSource() {
  14.         return DruidDataSourceBuilder.create().build();
  15.     }
  17.     @Bean(name = "oneTransactionManager")
  18.     @Primary
  19.     public DataSourceTransactionManager mysqlTransactionManager() {
  20.         return new DataSourceTransactionManager(mysqlDataSource());
  21.     }
  23.     @Bean(name = "oneSqlSessionFactory")
  24.     @Primary
  25.     public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqldatasourceone") DataSource dataSource)
  26.             throws Exception {
  27.         final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
  28.         sessionFactory.setDataSource(dataSource);
  29.         //如果不使用xml的方式配置mapper，则可以省去下面这行mapper location的配置。
  30.         sessionFactory.setMapperLocations(
  31.                 new PathMatchingResourcePatternResolver().getResources(MysqlDatasourceConfigOne.MAPPER_LOCATION));
  32.         return sessionFactory.getObject();
  33.     }
  34. }

4.创建数据源2

  1. @Configuration
  2. @MapperScan(basePackages = MysqlDatasourceConfigTwo.PACKAGE, sqlSessionFactoryRef = "mysqlSqlSessionFactory")
  3. public class MysqlDatasourceConfigTwo {
  5.     // twodao扫描路径
  6.     static final String PACKAGE = "com.springboot.twodao";
  7.     // mybatis mapper扫描路径
  8.     static final String MAPPER_LOCATION = "classpath:mapper/mysql/*.xml";
  10.     @Primary
  11.     @Bean(name = "mysqldatasourcetwo")
  12.     @ConfigurationProperties("spring.datasource.druid.mysql")
  13.     public DataSource mysqlDataSource() {
  14.         return DruidDataSourceBuilder.create().build();
  15.     }
  17.     @Bean(name = "twoTransactionManager")
  18.     @Primary
  19.     public DataSourceTransactionManager mysqlTransactionManager() {
  20.         return new DataSourceTransactionManager(mysqlDataSource());
  21.     }
  23.     @Bean(name = "twoSqlSessionFactory")
  24.     @Primary
  25.     public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqldatasourcetwo") DataSource dataSource)
  26.             throws Exception {
  27.         final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
  28.         sessionFactory.setDataSource(dataSource);
  29.         //如果不使用xml的方式配置mapper，则可以省去下面这行mapper location的配置。
  30.         sessionFactory.setMapperLocations(
  31.                 new PathMatchingResourcePatternResolver().getResources(MysqlDatasourceConfigTwo.MAPPER_LOCATION));
  32.         return sessionFactory.getObject();
  33.     }
  34. }
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注