SpringJDBC中配置JdbcTemplate对象，实现增删改查操作

创建Maven工程，导入对应依赖

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.23</version>
</dependency>

创建配置文件，对mysql数据源进行配置，并将数据源注入到JdbcTemplate对象中

    <!--数据源-->
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/imooc?useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false&amp;serverTimezone=GMT"/>
    <property name="username" value="root"/>
    <property name="password" value="NewPassword"/>
</bean>
<!--这个类是Spring 提供的,JdbcTemplate提供数据CURD的API-->
<bean class="org.springframework.jdbc.core.JdbcTemplate"  id="jdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>


<bean class="top.xiongmingcai.jdbc.dao.EmployeeDao" id="employeeDao">
    <property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>

创建实体类和Dao类

实体类与数据库中每条数据的字段保持驼峰命名，而Dao类则是要包含JdbcTemplate对象从而对数据源进行操作，操作时直接调用jdbcTemplate封装好的一系列方法即可。

private JdbcTemplate  jdbcTemplate;

public Employee findById(Integer eno){
    String sql = "select * from employee where eno = ?;";
    Employee employee = jdbcTemplate.queryForObject(sql,
            new Object[]{eno},
            new BeanPropertyRowMapper<Employee>(Employee.class));
    return employee;
};

这里的查询方法第一个参数为sql语句，第二个参数对应sql语句中的参数数组，第三个参数是设定实体类与数据表字段的一一对应关系，满足驼峰命名即可一一对应。

https://github.com/MingCaiXiong/spring-learn/commit/0dbbf4eda2af12cd2cc9417b24f2cfadb4b41e61

Spring 架构下引入test 单元测试

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>5.2.6.RELEASE</version>
</dependency>

在测试类中，使用注解完成测试类

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})

JdbcTemplate对象中三个查询方法：

public class EmployeeDao {
    private JdbcTemplate  jdbcTemplate;

    public Employee findById(Integer eno){
        String sql = "select * from employee where eno = ?;";
        Employee employee = jdbcTemplate.queryForObject(sql,
                new Object[]{eno},
                new BeanPropertyRowMapper<Employee>(Employee.class));
        return employee;
    };

    // 查询结果中多条记录转换为对应对象，我们可以使用query()进行查询，
    public List<Employee> queryByDname(String dname){
        String sql = "select * from employee where dname = ?;";
        return jdbcTemplate.query(sql,
                new Object[]{dname},
                new BeanPropertyRowMapper<>(Employee.class));
    }
    // 很多字段名是没有实体属性的，无法进行实体类映射，我们可以使用queryForList()进行查询，这个结果会被封装成map对象
    public List<Map<String, Object>>  queryMapByDname(String dname){
        String sql = "select dname as dnme,  salary as s  from employee where dname = ?";
        //没有实体类对应的情况下queryForList将查询结果作为map进行封装
        return jdbcTemplate.queryForList(sql, dname);
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}

new BeanPropertyRowMapper : 将java实体bean与数据库中的列，按照排序的顺序一一对应，返回具体的对象

https://github.com/MingCaiXiong/spring-learn/commit/e6d0c37a7e6c3ed9d38ee246bbf1daab1e1cc085

JdbcTemplate对象增加数据

  //新增加操作
    public int insert(Employee employee) {
        String sql = "INSERT INTO employee ( ename, salary, dname, hiredate) VALUES ( ?, ?, ?, ?)";
        int updateRows = jdbcTemplate.update(sql,
                employee.getEname(),
                employee.getSalary(),
                employee.getDname(),
                employee.getHiredate());

        return updateRows;
    }

    public int update(Employee employee) {
        String sql = "UPDATE employee  SET      ename = ?,     salary = ?,     dname = ?,     hiredate =? WHERE eno = ?";
        int update = jdbcTemplate.update(sql, employee.getEname(),
                employee.getSalary(),
                employee.getDname(),
                employee.getHiredate(), employee.getEno());
        return update;

    }
    public int delete(Integer eno){
        String sql = "DELETE FROM imooc.employee WHERE eno = ?";
        return jdbcTemplate.update(sql, eno);
    }

增删改查练习

https://github.com/MingCaiXiong/spring-learn/commit/d7245027aafe8d3a7102011bd965461e12fcea70