4.1SpringBoot之JdbcTemplate

依赖

    
        
            org.springframework.boot
            spring-boot-starter-web
        

        
            org.springframework.boot
            spring-boot-starter-jdbc
        

        
            mysql
            mysql-connector-java
            runtime
        

        
            org.springframework.boot
            spring-boot-starter-test
            test
        
    

User类

package com.example.jdbctemplate;


public class User {
    private Long id;
    private String username;
    private String address;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", address='" + address + ''' +
                '}';
    }
}

application.properties配置

spring.datasource.username=root
spring.datasource.password=111111
spring.datasource.url=jdbc:mysql://localhost:3306/test01?useUnicode=true&characterEncoding=UTF-8

增删改查

@Service
public class UserService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    
    //增
    public int addUser(User user) {
        int update = jdbcTemplate.update("insert into user (username,address,id) values (?,?,?)", user.getUsername(), user.getAddress(), user.getId());
        return update;
    }

//增+返回主键
    public int addUser2(User user) {
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        int result=jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement("insert into user (username,address) values (?,?)", Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getAddress());
                return ps;
        }

        }, keyHolder);
        user.setId( keyHolder.getKey().longValue());
        return result;
    }

//删
    public int delete(Long id) {
        return jdbcTemplate.update("delete from user where id=?",id);
    }

    //改
    public int update(String username,Long id) {

        return jdbcTemplate.update("update  user set username=? where id=?",username,id);

    }

    //查（实体类和数据库字段不一致）
    public List query(){
        List query= jdbcTemplate.query("select * from user", new RowMapper() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                String username=rs.getString("username");
                String address=rs.getString("address");
                long id=rs.getLong("id");
                User u=new User();
                u.setUsername(username);
                u.setAddress(address);
                u.setId(id);
                return u;
            }
        });
        return query;
    }

    //查(实体类和数据库字段一致)
    public List query2(){
        List query= jdbcTemplate.query("select * from user",new BeanPropertyRowMapper<>(User.class));
        return query;
    }

}

测试

 @Autowired
    UserService userService;

    @Test
    void contextLoads() {
        User user = new User();
        user.setUsername("xiaoxiao2");
        user.setAddress("123456");
        userService.addUser(user);
    }
    @Test
    void test2() {
        User user = new User();
        user.setUsername("xiaoxiao2");
        user.setAddress("123456");
        int i = userService.addUser2(user);
        System.out.println("i="+i);
        System.out.println("user.getId()="+user.getId());
    }

    @Test
    void test3() {

        int i=userService.delete(3L);

    }

    @Test
    void test4() {
        int i=userService.update("xiaojunfeng3",6L);
    }

    @Test
    void test5() {
        List users=userService.query();
        for (User i:users){
            System.out.println(i);
        }
    }

    @Test
    void test6() {
        List users=userService.query2();
        for (User i:users){
            System.out.println(i);
        }
    }

2.数据源配置

依赖

        
            org.springframework.boot
            spring-boot-starter-jdbc
        
        
            org.springframework.boot
            spring-boot-starter-web
        
        
            com.alibaba
            druid-spring-boot-starter
            1.1.10
        
        
            mysql
            mysql-connector-java
            runtime
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
    

application.properties配置

spring.datasource.one.url=jdbc:mysql:///test01?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.one.username=root
spring.datasource.one.password=111111
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource

spring.datasource.two.url=jdbc:mysql:///test02?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.two.username=root
spring.datasource.two.password=111111
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource

User类不变

DataSourceConfig

@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.one")
    DataSource dsOne() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.two")
    DataSource dsTwo() {
        return DruidDataSourceBuilder.create().build();
    }
}

JdbcTemplateConfig

@Configuration
public class JdbcTemplateConfig {
    @Bean
    JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne") DataSource dsOne) {
        return new JdbcTemplate(dsOne);
    }

    @Bean
    JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo") DataSource dsTwo) {
        return new JdbcTemplate(dsTwo);
    }
}

测试

@RunWith(SpringRunner.class)
@SpringBootTest
public class Jdbctemplate2ApplicationTests {

    @Autowired
    @Qualifier("jdbcTemplateOne")
    JdbcTemplate jdbcTemplateOne;

    @Resource(name = "jdbcTemplateTwo")
    JdbcTemplate jdbcTemplateTwo;

    @Test
    public void contextLoads() {
        List list1 = jdbcTemplateOne.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        System.out.println(list1);
        List list2 = jdbcTemplateTwo.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        System.out.println(list2);
    }

}