第六章：Spring JdbcTemplate实例

一）JdbcTemplate简介

JdbcTemplate类提供了执行SQL查询、update更新语句和存储过程的调用，并执行迭代结果集和提取返回参数值。JdbcTemplate 类的实例是线程安全配置的。

 

二）JdbcTemplate实例

第一步：在oracle数据库中创建一个表，用于测试

--员工表
create table TAB_EMPLOYEE
(
    emp_id      NUMBER,
    emp_name    VARCHAR2(100),
    emp_no      VARCHAR2(100),
    create_date NUMBER
);

 

第二步：创建一个maven项目，并在pom.xml导入spring和jdbc的jar

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.2.5.RELEASE</version>
</dependency>
	
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aspects</artifactId>
    <version>5.2.5.RELEASE</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.5.RELEASE</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>5.2.5.RELEASE</version>
</dependency>
    
<!-- oracle jdbc jar-->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.1.0.6.0</version>
</dependency>

项目结构图：

 

第三步：创建一个员工实体类

package com.oysept.entity;

/**
 * 员工表
 * @author ouyangjun
 */
public class Employee {
	
    private Integer empId;
    private String empName;
    private String empNO;
    private Long createDate;

    public Integer getEmpId() {return empId;}
    public void setEmpId(Integer empId) {this.empId = empId;}

    public String getEmpName() {return empName;}
    public void setEmpName(String empName) {this.empName = empName;}

    public String getEmpNO() {return empNO;}
    public void setEmpNO(String empNO) {this.empNO = empNO;}

    public Long getCreateDate() {return createDate;}
    public void setCreateDate(Long createDate) {this.createDate = createDate;}
    
    public String toString() {
        return "Employee[empId:" + this.empId + ", empName:" + this.empName 
                + ", empNO:" + this.empNO + ", createDate:" + this.createDate + "]";
    }
}

 

第四步：在resource下创建一个jdbc.properties文件，引入数据源配置

jdbc.driverClass=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:ORCL
jdbc.username=oysept
jdbc.password=orcl

 

第五步：加载数据配置源信息

@PropertySource: 用于加载后缀为".properties"文件中的配置。

@Value: 获取配置文件中的属性值, 当值不存在时,提供一个默认值

package com.oysept.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

/**
 * @PropertySource: 用于加载后缀为".properties"文件中的配置
 * @author ouyangjun
 */
@PropertySource("classpath:jdbc.properties")
public class DataSourceConfig {

    /**
     * @Value: 获取配置文件中的属性值, 当值不存在时,提供一个默认值
     */
    @Value(value="${jdbc.driverClass:NaN}")
    private String driver;
	
    @Value(value="${jdbc.url:NaN}")
    private String url;
	
    @Value(value="${jdbc.username:NaN}")
    private String username;
	
    @Value(value="${jdbc.password:NaN}")
    private String password;
	
    @Bean(name="datasource_first")
    @Scope("singleton")
    public DataSource getDataSource() {
        try {
            DriverManagerDataSource ds = new DriverManagerDataSource();
            ds.setDriverClassName(driver);
            ds.setUrl(url);
            ds.setUsername(username);
            ds.setPassword(password);
            return ds;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
	
    @Bean(name="jdbcTemplate_first")
    public JdbcTemplate getJdbcTemplate() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.getDataSource());
        return jdbcTemplate;
    }
}

 

第六步：创建spring配置类

@Configuration: 相当于：spring-config.xml配置文件

@ComponentScan: 相当于：<context:component-scan base-package="com.oysept"/>

@Import：导入其它配置类

package com.oysept.config;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;

/**
 * @Configuration: 相当于：spring-config.xml配置文件
 * @ComponentScan: 相当于：<context:component-scan base-package="com.oysept"/>
 * @author ouyangjun
 */
@Configuration
@ComponentScan("com.oysept")
@Import(DataSourceConfig.class)
public class SpringConfigXML {
	
}

 

第七步：创建Dao和DaoImpl执行工具类

@Component: 相当于：<bean id="" class="">

@Autowired: 根据byType方式自动匹配

@Qualifier: 当存在多个bean时, 需指定具体的bean, 如多配置, 多数据源的场景

 

EmployeeDao.java

package com.oysept.dao;

import com.oysept.entity.Employee;

public interface EmployeeDao {
	
    // 根据ID查询数据
    Employee findEmployeeByEmpID(Integer empID);
	
    // 新增
    int addEmployee(Employee employee);
	
    // 删除
    int deleteEmployee(Integer empID);
}

EmployeeDaoImpl.java

package com.oysept.dao.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.oysept.dao.EmployeeDao;
import com.oysept.entity.Employee;

/**
 * @Component: 相当于：<bean id="" class="">
 * @author ouyangjun
 */
@Component
public class EmployeeDaoImpl implements EmployeeDao {

    /**
     * @Autowired: 根据byType方式自动匹配
     * @Qualifier: 当存在多个bean时, 需指定具体的bean, 如多配置, 多数据源的场景
     */
    @Autowired
    @Qualifier(value="jdbcTemplate_first")
    private JdbcTemplate jdbcTemplate;
	
    public Employee findEmployeeByEmpID(Integer empID) {
        List<Employee> employeeList = jdbcTemplate.query(
                "select "
                + "emp_id as empId, "
                + "emp_name as empName, "
                + "emp_no as empNO, "
                + "create_date as createDate "
                + "from tab_employee where emp_id = ?", 
                new BeanPropertyRowMapper<Employee>(Employee.class), empID);
        return employeeList.isEmpty() ? null : employeeList.get(0);
    }

    public int addEmployee(Employee employee) {
        int count = jdbcTemplate.update("insert into tab_employee(emp_id, emp_name, emp_no, create_date) values(?, ?, ?, ?)",
            employee.getEmpId(), employee.getEmpName(), employee.getEmpNO(), employee.getCreateDate());
    return count;
    }
	
    public int deleteEmployee(Integer empID) {
        int count = jdbcTemplate.update("delete from tab_employee where emp_id = ?", empID);
        return count;
    }
}

 

第八步：创建一个测试工具类

package com.oysept.test;

import java.util.Date;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.oysept.config.SpringConfigXML;
import com.oysept.dao.EmployeeDao;
import com.oysept.entity.Employee;

public class EmployeeTest {

    public static void main(String[] args) {
        // 方式1.获取容器
        ApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfigXML.class);
        
        // 2.得到业务层对象, 并执行方法
        EmployeeDao employeeDao = ac.getBean(EmployeeDao.class);
        
        // 新增一条数据
        Employee entity = new Employee();
        entity.setEmpId(100000);
        entity.setEmpName("ouyangjun");
        entity.setEmpNO("333333");
        entity.setCreateDate(new Date().getTime());
        int count = employeeDao.addEmployee(entity);
        System.out.println("count: " + count);
        
        // 查询一条数据
        Employee employee = employeeDao.findEmployeeByEmpID(100000);
        System.out.println(employee);
        
        // 删除一条数据
        employeeDao.deleteEmployee(100000);
    }
}

打印效果图：

 

识别二维码关注个人微信公众号

本章完结，待续，欢迎转载！
 
本文说明：该文章属于原创，如需转载，请标明文章转载来源！