Spring中JdbcTemplate 类 对数据库操作

Spring JdbcTemplate类的使用

Spring 针对数据库开发提供了 JdbcTemplate 类，该类封装了 JDBC，支持对数据库的所有操作。

- 添加依赖

 <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <!-- jdbc连接池druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>
        <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>

数据源所用properties文件

driver:com.mysql.cj.jdbc.Driver
url:jdbc:mysql://localhost:3306/shopping_system?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
user:root
pwd:root

spring-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" xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"

       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">

    <!-- 开启扫描注解 -->
    <context:component-scan base-package="com.ljw"></context:component-scan>
    <!-- 引入外部properties文件 -->
    <context:property-placeholder location="classpath:druid.properties"></context:property-placeholder>
    <!-- 通过druid连接池获取数据源 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <!--数据库驱动 -->
        <property name="driverClassName" value="${driver}"></property>
        <!--连接数据库的url -->
        <property name="url" value="${url}"></property>
        <!--连接数据库的用户名 -->
        <property name="username" value="${user}"></property>
        <!--连接数据库的密码 -->
        <property name="password" value="${pwd}"></property>
    </bean>
    <!-- 配置JDBC模板,使用JdbcTemplate类 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <!--默认必须使用数据源 引用上面的数据源-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

基本bean

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Goods {
    private Integer goodId;
    private Integer typeId;
    private String goodName;
    private Integer goodNum;
    private Float goodPrice;


    public Goods(Integer typeId, String goodName, Integer goodNum, Float goodPrice) {
        this.typeId = typeId;
        this.goodName = goodName;
        this.goodNum = goodNum;
        this.goodPrice = goodPrice;
    }

dao层实现

@Controller
public class GoodsDaoImpl implements GoodsDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Override
    public List<Goods> findAll() {
        String sql = "select * from goods";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Goods.class));
    }

    @Override
    public Integer save(Goods goods) {
        String sql = "INSERT INTO goods VALUES(NULL,?,?,?,?)";
        return jdbcTemplate.update(sql,goods.getTypeId(),goods.getGoodName(),goods.getGoodNum(),goods.getGoodPrice());
    }

    @Override
    public Integer update(Goods goods) {
        String sql = "UPDATE goods SET type_id=?,good_name=?,good_num=?,good_price=? WHERE good_id=?";
        return jdbcTemplate.update(sql,goods.getTypeId(),goods.getGoodName(),goods.getGoodNum(),goods.getGoodPrice(),goods.getGoodId());
    }

    @Override
    public Integer delete(Integer id) {
        String sql = "delete from goods where good_id = ?";
        return jdbcTemplate.update(sql,id);
    }
}

service层实现

@Service
public class GoodsServiceImpl implements GoodsService {
    @Autowired
    GoodsDao goodsDao;
    @Override
    public List<Goods> findAll() {
        return goodsDao.findAll();
    }

    @Override
    public Integer saveAndUpdate(Goods goods,Goods goods2) {
        Integer rows = goodsDao.save(goods);
        Integer rows2 = goodsDao.update(goods2);
        return rows + rows;
    }


    @Override
    public Integer delete(Integer id) {
        return goodsDao.delete(id);
    }

}

测试

@Test
    public void findAll(){
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        GoodsService goodsService = context.getBean("goodsServiceImpl", GoodsServiceImpl.class);
        List<Goods> list = goodsService.findAll();
        for (Goods g :
             list) {
            System.out.println(g.getGoodId() + "\t" + g.getTypeId() + "\t" + g.getGoodName());
        }
    }

@Test
    public void save() {
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        GoodsService goodsService = context.getBean("goodsServiceImpl", GoodsServiceImpl.class);
        //添加的对象
        Goods goods = new Goods(3, "test77", 2, 2f);
        //修改的对象
        Goods goods2 = new Goods(1, 1, "test", 2, 2f);
        int rows = goodsService.saveAndUpdate(goods, goods2);
        System.out.println(rows);
    }