四、JdbcTemplate

一、概述

概述

它是spring框架中提供的一个对象，是对原始繁琐的JdbcAPI对象的简单封装。spring框架为我们提供了很多的操作模板类。例如：操作关系型数据的JdbcTemplate和HibernateTemplate，操作nosql数据库的RedisTemplate，操作消息队列的JmsTemplate等等

二、开发步骤

• 导入spring-jdbc和spring-tx坐标
• 创建数据库表和实体
• 创建JdbcTemplate对象
• 执行数据库操作

1、导入spring-jdbc和spring-tx坐标

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

2、创建数据库表和实体

数据表-account

create table account(
	id int not null primary key auto_increment comment '账户主键',
    name varchar(255) not null comment '姓名'
) comment '账户表';

实体类-Account

package com.hdq.pojo;

public class Account {
    private Integer id;
    private String name;

    public Account() {
    }

    public Account(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String toString() {
        return "Account{id = " + id + ", name = " + name + "}";
    }
}

3、创建JdbcTemplate对象，执行操作

测试

@Test
public void jdbcTest() throws PropertyVetoException {
  	ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
	JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
  	//执行操作
	int row = jdbcTemplate.update("insert into account (name) values (?)", "Alice");
}

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       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:property-placeholder location="jdbc.properties"/>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <bean class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring-jdbc
jdbc.user=root
jdbc.password=123456

三、CRUD

1、增加数据（create）

//插入数据
@Test
public void create() {
    int row = jdbcTemplate.update("insert into account (name) values (?)", "忽而狗");
    System.out.println(row);
}

2、读取数据（read）

（1）查询全部

//查询全部
@Test
public void queryAll() {
    List<Account> accounts = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
    System.out.println(accounts);
}

（2）条件查询单条数据

//根据条件查询单个
@Test
public void queryOne() {
   Account account = jdbcTemplate.queryForObject("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), 1);
   System.out.println(account);
}

（3）条件查询所有数据

//根据条件查询全部
@Test
public void queryAllByCondition() {
    List<Account> accountList = jdbcTemplate.query(
            "select * from account where name = ?",
            new BeanPropertyRowMapper<Account>(Account.class),
            "Alice");
    System.out.println(accountList);
}

（4）聚合函数查询

//聚合函数查询，例如count
@Test
public void queryByFun() {
   Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
   System.out.println(count);
}

提示

还有其他形式的查询语句

3、修改数据（update）

//更新数据
@Test
public void update() {
    int row = jdbcTemplate.update("update account set name= ? where name=?", "傻狗", "忽而狗");
    System.out.println(row);
}

4、删除数据（delete）

//删除数据
@Test
public void delete() {
    int row = jdbcTemplate.update("delete from account where id= ? ", 1);
    System.out.println(row);
}