四， Spring JDBC配置及CRUD操作

一，Spring JDBC 中配置jdbc Template对象实现增删改查操作：

一，数据准备：

在 Navicat 新建数据库，注意：字符集-->utf8mb4(4字节utf-8编码)

二，创建Maven工程，引入Spring 和 SpringJDBC、MySQL JDBC驱动的相关依赖：

1. Spring 框架的依赖

2. Spring-JDBC-->对数据库进行 增删改查操作（版本号和Spring框架依赖保持一致）

3. 引入MySQL-JDBC驱动-版本号与数据库版本号保持一致（因为底层是采用JDBC 操作数据库）

 <repositories>
        <repository>
            <id>aliyun</id>
            <name>aliyun</name>
            <url>https://maven.aliyun.com/repository/public</url>
        </repository>
    </repositories>

<dependencies>
    <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.16</version>
    </dependency>
</dependencies>

三，创建applicationContext.xml配置文件，引入xml文件标志和Schema，配置Spring-jdbc

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        https://www.springframework.org/schema/aop/spring-aop.xsd">
    <!--数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <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="123456"/>
    </bean>
    <!--jdbcTemplete提供数据crud的api-->
    <bean id="jdbcTemplete" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

四，创建实体类和Dao类：将 java实体bean 与数据库中的的列，按照排列的顺序一一对应

public class Employee {
    private int eno;
    private String ename;
    private int salary;
    private String dname;
    private Date hiredate;

    public int getEno() {
        return eno;
    }

    public void setEno(int eno) {
        this.eno = eno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public int getSalary() {
        return salary;
    }

    public void setSalary(int salary) {
        this.salary = salary;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "eno=" + eno +
                ", ename='" + ename + '\'' +
                ", salary=" + salary +
                ", dname='" + dname + '\'' +
                ", hiredate=" + hiredate +
                '}';
    }
}

public class EmployeeDao {
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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

    public Employee findByEno(int eno) {
        String sql = "select * from employee where eno=?";
        // jdbcTemplate.queryForObject：查询唯一的返回数据并封装成实体对象
        // new Object[]{eno}：查询的依据或给定的条件
        // new BeanPropertyRowMapper:将java实体bean与数据库中的列，按照排序的顺序一一对应，返回具体的对象
        Employee employee = jdbcTemplate.queryForObject(sql, new Object[]{eno}, new BeanPropertyRowMapper<Employee>(Employee.class));
        return employee;
    }
}

第五：在xml配置文件中，配置Dao类

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        https://www.springframework.org/schema/aop/spring-aop.xsd">
    <!--数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <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="123456"/>
    </bean>
    <!--jdbcTemplete提供数据crud的api-->
    <bean id="jdbcTemplete" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <!--为dao注入jdbcTemplete对象-->
    <bean id="employeeDao" class="com.imooc.spring.jdbc.dao.EmployeeDao">
        <property name="jdbcTemplate" ref="jdbcTemplete"/>
    </bean>
</beans>

六，创建SpringApplication类，完成员工查询工作：

public class SpringApplication {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
        EmployeeDao dao = context.getBean("employeeDao", EmployeeDao.class);
        Employee employee = dao.findByEno(3308);
        System.out.println(employee);
    }
}

二，数据查询的方法

  // 查询结果中单条记录转换为对应对象，我们可以使用queryForObjcet()进行查询，
    public Employee findByEno(int eno) {
        String sql = "select * from employee where eno=?";
        // jdbcTemplate.queryForObject：查询唯一的返回数据并封装成实体对象
        // new Object[]{eno}：查询的依据或给定的条件
        // new BeanPropertyRowMapper:将java实体bean与数据库中的列，按照排序的顺序一一对应，返回具体的对象
        // 查询单条数据
        Employee employee = jdbcTemplate.queryForObject(sql, new Object[]{eno}, new BeanPropertyRowMapper<Employee>(Employee.class));
        return employee;
    }
    
    // 查询结果中多条记录转换为对应对象，我们可以使用query()进行查询，
    public List<Employee> findByDname(String dname) {
        String sql = "select * from employee where dname=?";
        // 查询复合数据
        List<Employee> employees = jdbcTemplate.query(sql, new Object[]{dname}, new BeanPropertyRowMapper<Employee>(Employee.class));
        return employees;
    }

    // 很多字段名是没有实体属性的，无法进行实体类映射，我们可以使用queryForList()进行查询，这个结果会被封装成map对象
    public List<Map<String, Object>> findMapByDname(String dname) {
        String sql = "select dname as dn from employee where dname=?";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, new Object[]{dname});
        return maps;

    }

三，增删改操作

   // 新增
    public int insert(Employee employee) {
        String sql = "insert into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
        int update = jdbcTemplate.update(sql, new Object[]{
                employee.getEno(), employee.getEname(), employee.getSalary(), employee.getDname(), employee.getHiredate()
        });
        return update;
    }

    // 修改
    public int update(int salary) {
        String sql = "update employee set salary=? where eno=3308";
        int update = jdbcTemplate.update(sql, new Object[]{salary});
        return update;
    }

    //删除
    public int delete(int eno) {
        String sql = "delete from employee where eno=?";
        int update = jdbcTemplate.update(sql, new Object[]{eno});
        return update;
    }