8、Spring JDBCTemplate的使用-CSDN博客

本文链接：https://blog.csdn.net/chf_liu/article/details/130593402

1、构建maven工程，导入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.maliqiang</groupId>
    <artifactId>spring-jdbctemplete-test01</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

   <dependencies>
        <!--spring核心容器包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--spring切面包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--aop联盟包-->
        <dependency>
            <groupId>aopalliance</groupId>
            <artifactId>aopalliance</artifactId>
            <version>1.0</version>
        </dependency>
        <!--德鲁伊连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!--mysql驱动-->
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>8.0.22</version>
         </dependency>
        <!--springJDBC包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--spring事务控制包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--spring orm 映射依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.3.5</version>
        </dependency>
        <!--Apache Commons日志包-->
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <!--Junit单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
        <!--lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>


</project>

2、准备jdbc.properties

jdbc.username=root
jdbc.password=123456
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatisdb?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
jdbc.driver=com.mysql.cj.jdbc.Driver

3、准备applicationContext.xml

<?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:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd">

    <!--配置包扫描-->
    <context:component-scan base-package="com.maliqiang"></context:component-scan>

    <!--读取外部配置文件-->
    <context:property-placeholder location="jdbc.properties"></context:property-placeholder>

    <!--配置连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="driverClassName" value="${jdbc.driver}"></property>
    </bean>
    <!--配置JdbcTemplate 并向里面注入DruidDataSource-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

4、准备实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;
}

5、准备service层接口和实现类

public interface EmpService {
    int findEmpCount();

    Emp findByEmpno(Integer empno);

    List<Emp> findEmpListByDeptno(Integer deptno);

    int addEmp(Emp emp);

    int updateEmp(Emp emp);

    int deleteEmp(Integer empno);
}

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private EmpDAO empDAO;

    public int findEmpCount() {
        int empCount = empDAO.findEmpCount();
        return empCount;
    }

    public Emp findByEmpno(Integer empno) {
        return empDAO.findByEmpno(empno);
    }

    public List<Emp> findEmpListByDeptno(Integer deptno) {
        return empDAO.findEmpListByDeptno(deptno);
    }

    public int addEmp(Emp emp) {
        return empDAO.addEmp(emp);
    }

    public int updateEmp(Emp emp) {
        return empDAO.updateEmp(emp);
    }

    public int deleteEmp(Integer empno) {
        return empDAO.deleteEmp(empno);
    }
}

6、准备dao层接口和实现类

public interface EmpDAO {
    int findEmpCount();

    Emp findByEmpno(Integer empno);

    List<Emp> findEmpListByDeptno(Integer deptno);

    int addEmp(Emp emp);

    int updateEmp(Emp emp);

    int deleteEmp(Integer empno);
}

@Repository
public class EmpDAOImpl implements EmpDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public int findEmpCount() {
        String sql = "select count(1) from emp";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

    public Emp findByEmpno(Integer empno) {
        String sql = "select * from emp where empno=?";
        Emp emp = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Emp>(Emp.class), empno);
        return emp;
    }

    public List<Emp> findEmpListByDeptno(Integer deptno) {
        String sql = "select * from emp where deptno=?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class), deptno);
    }

    public int addEmp(Emp emp) {
        String sql = "insert into emp values(default,?,?,?,?,?,?,?)";
        Object[] args = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno()};
        return jdbcTemplate.update(sql, args);
    }

    public int updateEmp(Emp emp) {
        String sql = "update emp set ename =? , job =?, mgr=? , hiredate =?, sal=?, comm=?, deptno =? where empno =?";
        Object[] args = {emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHiredate(), emp.getSal(), emp.getComm(), emp.getDeptno(), emp.getEmpno()};
        return jdbcTemplate.update(sql, args);
    }

    public int deleteEmp(Integer empno) {
        String sql = "delete from emp where empno=?";
        return jdbcTemplate.update(sql, empno);
    }
}

7、测试代码



public class Test01 {

    // 查询员工个数
    @Test
    public void findEmpCount() {

        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        EmpService empService = applicationContext.getBean(EmpService.class);
        int empCount = empService.findEmpCount();
        System.out.println("empCount = " + empCount);
    }

    // 根据员工编号查询员工信息
    @Test
    public void findEmpByEmpno() {

        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        EmpService empService = applicationContext.getBean(EmpService.class);
        Emp emp = empService.findByEmpno(7369);
        System.out.println("emp = " + emp);
    }

    // 根据员工编号查询员工信息
    @Test
    public void findEmpListByDeptno() {

        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        EmpService empService = applicationContext.getBean(EmpService.class);
        List<Emp> empList = empService.findEmpListByDeptno(20);
        for (Emp emp : empList) {
            System.out.println("emp = " + emp);
        }
    }

    // 增加员工
    @Test
    public void addEmp() {

        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        EmpService empService = applicationContext.getBean(EmpService.class);

        int rows = empService.addEmp(new Emp(null, "TOM", "SALESMAN", 7521, new Date(), 2000.0, 100.0, 10));
        System.out.println("rows = " + rows);
    }

    // 修改员工
    @Test
    public void updateEmp() {

        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        EmpService empService = applicationContext.getBean(EmpService.class);

        int rows = empService.updateEmp(new Emp(7939, "JERRY", "MANAGER", 7839, new Date(), 3000.0, 0.0, 20));
        System.out.println("rows = " + rows);
    }

    // 删除员工
    @Test
    public void deleteEmp() {

        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        EmpService empService = applicationContext.getBean(EmpService.class);
        int rows = empService.deleteEmp(7939);
        System.out.println("rows = " + rows);
    }

}